RunSQLComponent extends CommandComponentSparkFlow implements Serializable { @Override public void execute() { Properties properties = new Properties(); Connection conn = null; Reader reader = null; if (((null != runSqlEntity.getDatabaseConnectionName()) && !(runSqlEntity.getDatabaseConnectionName().trim().isEmpty())) && ((null != runSqlEntity.getQueryCommand()) && !(runSqlEntity.getQueryCommand().trim().isEmpty()))) { if (runSqlEntity.getDatabaseConnectionName().equalsIgnoreCase("MYSQL")) { log.debug("Request received test connection " + runSqlEntity.getDatabaseConnectionName()); try { properties.setProperty("className", "com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql: conn.setAutoCommit(false); scriptBuilder(runSqlEntity.getQueryCommand()); ScriptRunner sr = new ScriptRunner(conn, false, true); reader = new BufferedReader(new FileReader(tempFile)); sr.runScript(reader); } catch (SQLException | IOException | ClassNotFoundException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); throw new RuntimeException(e); } finally { Exception ex = null; try { SafeResourceClose.safeReaderClose(reader); } catch (IOException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); ex = e; } try { tempFile.deleteOnExit(); } catch (Exception e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); ex = e; } try { SafeResourceClose.safeConnectionClose(conn); } catch (SQLException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); ex = e; } if(ex != null) { throw new RuntimeException(ex); } } } else if (runSqlEntity.getDatabaseConnectionName().equalsIgnoreCase("Oracle")) { log.debug("Request received test connection " + runSqlEntity.getDatabaseConnectionName()); try { properties.setProperty("className", "oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin: conn.setAutoCommit(false); scriptBuilder(runSqlEntity.getQueryCommand()); ScriptRunner sr = new ScriptRunner(conn, false, true); reader = new BufferedReader(new FileReader(tempFile)); sr.runScript(reader); } catch (SQLException | IOException | ClassNotFoundException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); throw new RuntimeException(e); } finally { Exception ex = null; try { SafeResourceClose.safeReaderClose(reader); } catch (IOException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); ex = e; } try { tempFile.deleteOnExit(); } catch (Exception e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); ex = e; } try { SafeResourceClose.safeConnectionClose(conn); } catch (SQLException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); ex = e; } if(ex != null) { throw new RuntimeException(ex); } } } else if (runSqlEntity.getDatabaseConnectionName().equalsIgnoreCase("Teradata")) { log.debug("Request received test connection " + runSqlEntity.getDatabaseConnectionName()); try { properties.setProperty("className", "com.teradata.jdbc.TeraDriver"); conn = DriverManager.getConnection("jdbc:teradata: conn.setAutoCommit(false); scriptBuilder(runSqlEntity.getQueryCommand()); ScriptRunner sr = new ScriptRunner(conn, false, true); reader = new BufferedReader(new FileReader(tempFile)); sr.runScript(reader); } catch (SQLException | IOException | ClassNotFoundException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); throw new RuntimeException(e); } finally { Exception ex = null; try { SafeResourceClose.safeReaderClose(reader); } catch (IOException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); ex = e; } try { tempFile.deleteOnExit(); } catch (Exception e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); ex = e; } try { SafeResourceClose.safeConnectionClose(conn); } catch (SQLException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); ex = e; } if(ex != null) { throw new RuntimeException(ex); } } } else if (runSqlEntity.getDatabaseConnectionName().equalsIgnoreCase("Redshift")) { log.debug("Request received test connection " + runSqlEntity.getDatabaseConnectionName()); try { properties.setProperty("className", "com.amazon.redshift.jdbc42.Driver"); conn = DriverManager.getConnection("jdbc:redshift: conn.setAutoCommit(false); scriptBuilder(runSqlEntity.getQueryCommand()); ScriptRunner sr = new ScriptRunner(conn, false, true); reader = new BufferedReader(new FileReader(tempFile)); sr.runScript(reader); } catch (SQLException | IOException | ClassNotFoundException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); throw new RuntimeException(e); } finally { try { SafeResourceClose.safeReaderClose(reader); } catch (IOException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); } try { tempFile.deleteOnExit(); } catch (Exception e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); } try { SafeResourceClose.safeConnectionClose(conn); } catch (SQLException e) { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + " The error is " + e.getMessage()); } } } } else { log.debug("Failed to Execute" + runSqlEntity.getQueryCommand() + "Required field is empty."); throw new DatabaseConnectionException("Required field is empty.",null); } } RunSQLComponent(AssemblyEntityBase assemblyEntityBase); @Override void execute();  }
@Test(expected = DatabaseConnectionException.class) public void throwException_IfDatabaseConnectionNameIsEmpty() throws IOException { runSqlEntity.setBatch("0"); runSqlEntity.setComponentId("1"); runSqlEntity.setComponentName("RunSQLComponent"); runSqlEntity.setDatabaseConnectionName(""); runSqlEntity.setDatabaseName("PRACTICE"); runSqlEntity.setDbPwd("Bitwise2017"); runSqlEntity.setPortNumber("1521"); runSqlEntity.setQueryCommand("insert into testsp values(1,1,1,1)"); runSqlEntity.setDbUserName("htcd"); runSqlEntity.setServerName("DBDEVSRV"); RunSQLComponent runSQLComponent = new RunSQLComponent(runSqlEntity); runSQLComponent.execute(); }
@Test(expected = Exception.class) public void throwException_IfDatabaseNameIsEmpty() throws IOException { runSqlEntity.setBatch("0"); runSqlEntity.setComponentId("1"); runSqlEntity.setComponentName("RunSQLComponent"); runSqlEntity.setDatabaseConnectionName("Oracle"); runSqlEntity.setDatabaseName(""); runSqlEntity.setDbPwd("Bitwise2017"); runSqlEntity.setPortNumber("1521"); runSqlEntity.setQueryCommand("insert into testsp values(1,1,1,1)"); runSqlEntity.setDbUserName("htcd"); runSqlEntity.setServerName("DBDEVSRV"); RunSQLComponent runSQLComponent = new RunSQLComponent(runSqlEntity); runSQLComponent.execute(); }
@Test public void testAllPropertiesValid() throws IOException { runSqlEntity.setBatch("0"); runSqlEntity.setComponentId("1"); runSqlEntity.setComponentName("RunSQLComponent"); runSqlEntity.setDatabaseConnectionName("Oracle"); runSqlEntity.setDatabaseName("PRACTICE"); runSqlEntity.setDbPwd("Bitwise2017"); runSqlEntity.setPortNumber("1521"); runSqlEntity.setDbUserName("htcd"); runSqlEntity.setServerName("DBDEVSRV"); runSqlEntity.setQueryCommand("insert into testsp values(123456,1,1,1)"); RunSQLComponent runSQLComponent = new RunSQLComponent(runSqlEntity); runSQLComponent.execute(); }
@Test(expected = Exception.class) public void throwException_IfDatabasePasswordIsEmpty() throws IOException { runSqlEntity.setBatch("0"); runSqlEntity.setComponentId("1"); runSqlEntity.setComponentName("RunSQLComponent"); runSqlEntity.setDatabaseConnectionName("Oracle"); runSqlEntity.setDatabaseName("PRACTICE"); runSqlEntity.setDbPwd(""); runSqlEntity.setPortNumber("1521"); runSqlEntity.setQueryCommand("insert into testsp values(1,1,1,1)"); runSqlEntity.setDbUserName("htcd"); runSqlEntity.setServerName("DBDEVSRV"); RunSQLComponent runSQLComponent = new RunSQLComponent(runSqlEntity); runSQLComponent.execute(); }
@Test(expected = Exception.class) public void throwException_IfDatabasePortNumberIsEmpty() throws IOException { runSqlEntity.setBatch("0"); runSqlEntity.setComponentId("1"); runSqlEntity.setComponentName("RunSQLComponent"); runSqlEntity.setDatabaseConnectionName("Oracle"); runSqlEntity.setDatabaseName("PRACTICE"); runSqlEntity.setDbPwd("Bitwise2017"); runSqlEntity.setPortNumber(""); runSqlEntity.setQueryCommand("insert into testsp values(1,1,1,1)"); runSqlEntity.setDbUserName("htcd"); runSqlEntity.setServerName("DBDEVSRV"); RunSQLComponent runSQLComponent = new RunSQLComponent(runSqlEntity); runSQLComponent.execute(); }
@Test(expected = DatabaseConnectionException.class) public void throwException_IfDatabaseQueryIsEmpty() throws Exception { runSqlEntity.setBatch("0"); runSqlEntity.setComponentId("1"); runSqlEntity.setComponentName("RunSQLComponent"); runSqlEntity.setDatabaseConnectionName("Oracle"); runSqlEntity.setDatabaseName("PRACTICE"); runSqlEntity.setDbPwd("Bitwise2017"); runSqlEntity.setPortNumber("1521"); runSqlEntity.setQueryCommand(""); runSqlEntity.setDbUserName("htcd"); runSqlEntity.setServerName("DBDEVSRV"); RunSQLComponent runSQLComponent = new RunSQLComponent(runSqlEntity); runSQLComponent.execute(); }
@Test(expected = RuntimeException.class) public void throwException_IfDatabaseUserNameIsEmpty() throws IOException { runSqlEntity.setBatch("0"); runSqlEntity.setComponentId("1"); runSqlEntity.setComponentName("RunSQLComponent"); runSqlEntity.setDatabaseConnectionName("Oracle"); runSqlEntity.setDatabaseName("PRACTICE"); runSqlEntity.setDbPwd("Bitwise2017"); runSqlEntity.setPortNumber("1521"); runSqlEntity.setQueryCommand("insert into testSP(1,1,1,1)"); runSqlEntity.setDbUserName(""); runSqlEntity.setServerName("DBDEVSRV"); RunSQLComponent runSQLComponent = new RunSQLComponent(runSqlEntity); runSQLComponent.execute(); }
@Test(expected = RuntimeException.class) public void throwException_IfDatabaseServerNameIsEmptyOrInvalid() throws IOException { runSqlEntity.setBatch("0"); runSqlEntity.setComponentId("1"); runSqlEntity.setComponentName("RunSQLComponent"); runSqlEntity.setDatabaseConnectionName("Oracle"); runSqlEntity.setDatabaseName("PRACTICE"); runSqlEntity.setDbPwd("Bitwise2017"); runSqlEntity.setPortNumber("1521"); runSqlEntity.setQueryCommand("insert into testSP(1,1,1,1)"); runSqlEntity.setDbUserName("htcd"); runSqlEntity.setServerName(""); RunSQLComponent runSQLComponent = new RunSQLComponent(runSqlEntity); runSQLComponent.execute(); }
@Test(expected = RuntimeException.class) public void throwException_IfDatabaseQueryIsInvalid() throws IOException { runSqlEntity.setBatch("0"); runSqlEntity.setComponentId("1"); runSqlEntity.setComponentName("RunSQLComponent"); runSqlEntity.setDatabaseConnectionName("Oracle"); runSqlEntity.setDatabaseName("PRACTICE"); runSqlEntity.setDbPwd("Bitwise2017"); runSqlEntity.setPortNumber("1521"); runSqlEntity.setQueryCommand("insert into testPassssssss(1,1,1,1)"); runSqlEntity.setDbUserName("htcd"); runSqlEntity.setServerName("DBDEVSRV"); RunSQLComponent runSQLComponent = new RunSQLComponent(runSqlEntity); runSQLComponent.execute(); }