@Test public void testExecuteSqlCommand() throws Exception { sqlInputs.setDbType(ORACLE_DB_TYPE); sqlInputs.setDbPort(30); sqlInputs.setDbServer("localhost"); sqlInputs.setDbName("/dbName"); sqlInputs.setSqlCommand(SQL_COMMAND); sqlInputs.setTimeout(QUYERY_TIMEOUT); final String executeSqlCommand = SQLCommandService.executeSqlCommand(sqlInputs); assertEquals("Command completed successfully", executeSqlCommand); verify(connectionMock, Mockito.times(1)).setReadOnly(false); verify(resultSetMock, Mockito.times(1)).close(); verify(statementMock, Mockito.times(1)).setQueryTimeout(QUYERY_TIMEOUT); verify(statementMock, Mockito.times(1)).execute(SQL_COMMAND); }
public static String executeSqlCommand(final SQLInputs sqlInputs) throws Exception { final ConnectionService connectionService = new ConnectionService(); try (final Connection connection = connectionService.setUpConnection(sqlInputs)){ connection.setReadOnly(false); final String dbType = sqlInputs.getDbType(); if (ORACLE_DB_TYPE.equalsIgnoreCase(dbType) && sqlInputs.getSqlCommand().toLowerCase().contains(DBMS_OUTPUT)) { final PreparedStatement preparedStatement = connection.prepareStatement(sqlInputs.getSqlCommand()); preparedStatement.setQueryTimeout(sqlInputs.getTimeout()); OracleDbmsOutput oracleDbmsOutput = new OracleDbmsOutput(connection); preparedStatement.executeQuery(); sqlInputs.setIUpdateCount(preparedStatement.getUpdateCount()); preparedStatement.close(); final String output = oracleDbmsOutput.getOutput(); oracleDbmsOutput.close(); return output; } else { final Statement statement = connection.createStatement(sqlInputs.getResultSetType(), sqlInputs.getResultSetConcurrency()); statement.setQueryTimeout(sqlInputs.getTimeout()); try { statement.execute(sqlInputs.getSqlCommand()); } catch (SQLException e) { if (SYBASE_DB_TYPE.equalsIgnoreCase(dbType)) { if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dump")) { return SQLUtils.processDumpException(e); } else if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("load")) { return SQLUtils.processLoadException(e); } } else { throw e; } } ResultSet rs = statement.getResultSet(); if (rs != null) { ResultSetMetaData rsMtd = rs.getMetaData(); if (rsMtd != null) { sqlInputs.getLRows().clear(); int colCount = rsMtd.getColumnCount(); if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { while (rs.next()) { if (colCount >= 4) { sqlInputs.getLRows().add(rs.getString(4)); } } } else { String delimiter = (StringUtils.isNoneEmpty(sqlInputs.getStrDelim())) ? sqlInputs.getStrDelim() : ","; String strRowHolder; while (rs.next()) { strRowHolder = ""; for (int i = 1; i <= colCount; i++) { if (i > 1) { strRowHolder += delimiter; } strRowHolder += rs.getString(i); } sqlInputs.getLRows().add(strRowHolder); } } rs.close(); } } else if (dbType.equalsIgnoreCase(SYBASE_DB_TYPE) && sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { SQLWarning warning = statement.getWarnings(); while (warning != null) { sqlInputs.getLRows().add(warning.getMessage()); warning = warning.getNextWarning(); } } sqlInputs.setIUpdateCount(statement.getUpdateCount()); } } return "Command completed successfully"; }
SQLCommandService { public static String executeSqlCommand(final SQLInputs sqlInputs) throws Exception { final ConnectionService connectionService = new ConnectionService(); try (final Connection connection = connectionService.setUpConnection(sqlInputs)){ connection.setReadOnly(false); final String dbType = sqlInputs.getDbType(); if (ORACLE_DB_TYPE.equalsIgnoreCase(dbType) && sqlInputs.getSqlCommand().toLowerCase().contains(DBMS_OUTPUT)) { final PreparedStatement preparedStatement = connection.prepareStatement(sqlInputs.getSqlCommand()); preparedStatement.setQueryTimeout(sqlInputs.getTimeout()); OracleDbmsOutput oracleDbmsOutput = new OracleDbmsOutput(connection); preparedStatement.executeQuery(); sqlInputs.setIUpdateCount(preparedStatement.getUpdateCount()); preparedStatement.close(); final String output = oracleDbmsOutput.getOutput(); oracleDbmsOutput.close(); return output; } else { final Statement statement = connection.createStatement(sqlInputs.getResultSetType(), sqlInputs.getResultSetConcurrency()); statement.setQueryTimeout(sqlInputs.getTimeout()); try { statement.execute(sqlInputs.getSqlCommand()); } catch (SQLException e) { if (SYBASE_DB_TYPE.equalsIgnoreCase(dbType)) { if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dump")) { return SQLUtils.processDumpException(e); } else if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("load")) { return SQLUtils.processLoadException(e); } } else { throw e; } } ResultSet rs = statement.getResultSet(); if (rs != null) { ResultSetMetaData rsMtd = rs.getMetaData(); if (rsMtd != null) { sqlInputs.getLRows().clear(); int colCount = rsMtd.getColumnCount(); if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { while (rs.next()) { if (colCount >= 4) { sqlInputs.getLRows().add(rs.getString(4)); } } } else { String delimiter = (StringUtils.isNoneEmpty(sqlInputs.getStrDelim())) ? sqlInputs.getStrDelim() : ","; String strRowHolder; while (rs.next()) { strRowHolder = ""; for (int i = 1; i <= colCount; i++) { if (i > 1) { strRowHolder += delimiter; } strRowHolder += rs.getString(i); } sqlInputs.getLRows().add(strRowHolder); } } rs.close(); } } else if (dbType.equalsIgnoreCase(SYBASE_DB_TYPE) && sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { SQLWarning warning = statement.getWarnings(); while (warning != null) { sqlInputs.getLRows().add(warning.getMessage()); warning = warning.getNextWarning(); } } sqlInputs.setIUpdateCount(statement.getUpdateCount()); } } return "Command completed successfully"; } }
SQLCommandService { public static String executeSqlCommand(final SQLInputs sqlInputs) throws Exception { final ConnectionService connectionService = new ConnectionService(); try (final Connection connection = connectionService.setUpConnection(sqlInputs)){ connection.setReadOnly(false); final String dbType = sqlInputs.getDbType(); if (ORACLE_DB_TYPE.equalsIgnoreCase(dbType) && sqlInputs.getSqlCommand().toLowerCase().contains(DBMS_OUTPUT)) { final PreparedStatement preparedStatement = connection.prepareStatement(sqlInputs.getSqlCommand()); preparedStatement.setQueryTimeout(sqlInputs.getTimeout()); OracleDbmsOutput oracleDbmsOutput = new OracleDbmsOutput(connection); preparedStatement.executeQuery(); sqlInputs.setIUpdateCount(preparedStatement.getUpdateCount()); preparedStatement.close(); final String output = oracleDbmsOutput.getOutput(); oracleDbmsOutput.close(); return output; } else { final Statement statement = connection.createStatement(sqlInputs.getResultSetType(), sqlInputs.getResultSetConcurrency()); statement.setQueryTimeout(sqlInputs.getTimeout()); try { statement.execute(sqlInputs.getSqlCommand()); } catch (SQLException e) { if (SYBASE_DB_TYPE.equalsIgnoreCase(dbType)) { if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dump")) { return SQLUtils.processDumpException(e); } else if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("load")) { return SQLUtils.processLoadException(e); } } else { throw e; } } ResultSet rs = statement.getResultSet(); if (rs != null) { ResultSetMetaData rsMtd = rs.getMetaData(); if (rsMtd != null) { sqlInputs.getLRows().clear(); int colCount = rsMtd.getColumnCount(); if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { while (rs.next()) { if (colCount >= 4) { sqlInputs.getLRows().add(rs.getString(4)); } } } else { String delimiter = (StringUtils.isNoneEmpty(sqlInputs.getStrDelim())) ? sqlInputs.getStrDelim() : ","; String strRowHolder; while (rs.next()) { strRowHolder = ""; for (int i = 1; i <= colCount; i++) { if (i > 1) { strRowHolder += delimiter; } strRowHolder += rs.getString(i); } sqlInputs.getLRows().add(strRowHolder); } } rs.close(); } } else if (dbType.equalsIgnoreCase(SYBASE_DB_TYPE) && sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { SQLWarning warning = statement.getWarnings(); while (warning != null) { sqlInputs.getLRows().add(warning.getMessage()); warning = warning.getNextWarning(); } } sqlInputs.setIUpdateCount(statement.getUpdateCount()); } } return "Command completed successfully"; }  }
SQLCommandService { public static String executeSqlCommand(final SQLInputs sqlInputs) throws Exception { final ConnectionService connectionService = new ConnectionService(); try (final Connection connection = connectionService.setUpConnection(sqlInputs)){ connection.setReadOnly(false); final String dbType = sqlInputs.getDbType(); if (ORACLE_DB_TYPE.equalsIgnoreCase(dbType) && sqlInputs.getSqlCommand().toLowerCase().contains(DBMS_OUTPUT)) { final PreparedStatement preparedStatement = connection.prepareStatement(sqlInputs.getSqlCommand()); preparedStatement.setQueryTimeout(sqlInputs.getTimeout()); OracleDbmsOutput oracleDbmsOutput = new OracleDbmsOutput(connection); preparedStatement.executeQuery(); sqlInputs.setIUpdateCount(preparedStatement.getUpdateCount()); preparedStatement.close(); final String output = oracleDbmsOutput.getOutput(); oracleDbmsOutput.close(); return output; } else { final Statement statement = connection.createStatement(sqlInputs.getResultSetType(), sqlInputs.getResultSetConcurrency()); statement.setQueryTimeout(sqlInputs.getTimeout()); try { statement.execute(sqlInputs.getSqlCommand()); } catch (SQLException e) { if (SYBASE_DB_TYPE.equalsIgnoreCase(dbType)) { if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dump")) { return SQLUtils.processDumpException(e); } else if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("load")) { return SQLUtils.processLoadException(e); } } else { throw e; } } ResultSet rs = statement.getResultSet(); if (rs != null) { ResultSetMetaData rsMtd = rs.getMetaData(); if (rsMtd != null) { sqlInputs.getLRows().clear(); int colCount = rsMtd.getColumnCount(); if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { while (rs.next()) { if (colCount >= 4) { sqlInputs.getLRows().add(rs.getString(4)); } } } else { String delimiter = (StringUtils.isNoneEmpty(sqlInputs.getStrDelim())) ? sqlInputs.getStrDelim() : ","; String strRowHolder; while (rs.next()) { strRowHolder = ""; for (int i = 1; i <= colCount; i++) { if (i > 1) { strRowHolder += delimiter; } strRowHolder += rs.getString(i); } sqlInputs.getLRows().add(strRowHolder); } } rs.close(); } } else if (dbType.equalsIgnoreCase(SYBASE_DB_TYPE) && sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { SQLWarning warning = statement.getWarnings(); while (warning != null) { sqlInputs.getLRows().add(warning.getMessage()); warning = warning.getNextWarning(); } } sqlInputs.setIUpdateCount(statement.getUpdateCount()); } } return "Command completed successfully"; }  static String executeSqlCommand(final SQLInputs sqlInputs); }
SQLCommandService { public static String executeSqlCommand(final SQLInputs sqlInputs) throws Exception { final ConnectionService connectionService = new ConnectionService(); try (final Connection connection = connectionService.setUpConnection(sqlInputs)){ connection.setReadOnly(false); final String dbType = sqlInputs.getDbType(); if (ORACLE_DB_TYPE.equalsIgnoreCase(dbType) && sqlInputs.getSqlCommand().toLowerCase().contains(DBMS_OUTPUT)) { final PreparedStatement preparedStatement = connection.prepareStatement(sqlInputs.getSqlCommand()); preparedStatement.setQueryTimeout(sqlInputs.getTimeout()); OracleDbmsOutput oracleDbmsOutput = new OracleDbmsOutput(connection); preparedStatement.executeQuery(); sqlInputs.setIUpdateCount(preparedStatement.getUpdateCount()); preparedStatement.close(); final String output = oracleDbmsOutput.getOutput(); oracleDbmsOutput.close(); return output; } else { final Statement statement = connection.createStatement(sqlInputs.getResultSetType(), sqlInputs.getResultSetConcurrency()); statement.setQueryTimeout(sqlInputs.getTimeout()); try { statement.execute(sqlInputs.getSqlCommand()); } catch (SQLException e) { if (SYBASE_DB_TYPE.equalsIgnoreCase(dbType)) { if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dump")) { return SQLUtils.processDumpException(e); } else if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("load")) { return SQLUtils.processLoadException(e); } } else { throw e; } } ResultSet rs = statement.getResultSet(); if (rs != null) { ResultSetMetaData rsMtd = rs.getMetaData(); if (rsMtd != null) { sqlInputs.getLRows().clear(); int colCount = rsMtd.getColumnCount(); if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { while (rs.next()) { if (colCount >= 4) { sqlInputs.getLRows().add(rs.getString(4)); } } } else { String delimiter = (StringUtils.isNoneEmpty(sqlInputs.getStrDelim())) ? sqlInputs.getStrDelim() : ","; String strRowHolder; while (rs.next()) { strRowHolder = ""; for (int i = 1; i <= colCount; i++) { if (i > 1) { strRowHolder += delimiter; } strRowHolder += rs.getString(i); } sqlInputs.getLRows().add(strRowHolder); } } rs.close(); } } else if (dbType.equalsIgnoreCase(SYBASE_DB_TYPE) && sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { SQLWarning warning = statement.getWarnings(); while (warning != null) { sqlInputs.getLRows().add(warning.getMessage()); warning = warning.getNextWarning(); } } sqlInputs.setIUpdateCount(statement.getUpdateCount()); } } return "Command completed successfully"; }  static String executeSqlCommand(final SQLInputs sqlInputs);  }