private Connection getConn() throws Exception {
Class.forName(driver).newInstance();
Connection conn = null;
if (user == null || user.equals("")) {
conn = java.sql.DriverManager.getConnection(url);
} else {
conn = java.sql.DriverManager.getConnection(url, user, pwd);
}
return conn;
}
private List<Object[]> getCustomStgUsers(boolean usedb2) {
List<Object[]> listData = new ArrayList<Object[]>();
String ServerName = "";
int PortNumber;
String DatabaseName = "";
java.util.Properties properties;
String url = "";
java.sql.Connection con = null;
if (usedb2) {
ServerName = synDB.getConfigProperties("SERVER_NAME");
PortNumber = Integer.parseInt(synDB.getConfigProperties("PORT_NUMBER"));
DatabaseName = synDB.getConfigProperties("DATABASE");
properties = new java.util.Properties();
properties.put("user", synDB.getConfigProperties("USER"));
properties.put("password", synDB.getConfigProperties("PASSWORD"));
properties.put("sslConnection", "true");
System.setProperty("javax.net.ssl.trustStore", synDB.getConfigProperties("CACERTS_PATH"));
System.setProperty("javax.net.ssl.trustStorePassword", "changeit");
System.setProperty("db2.jcc.charsetDecoderEncoder", "3");
url = "jdbc:db2://" + ServerName + ":" + PortNumber + "/" + DatabaseName;
try {
new DB2Driver();
} catch (Exception e) {
System.out.println("Error: failed to load Db2 jcc driver.");
}
} else {
ServerName = synDB.getConfigProperties("SERVER_NAME");
PortNumber = Integer.parseInt(synDB.getConfigProperties("PORT_NUMBER"));
DatabaseName = synDB.getConfigProperties("DATABASE");
properties = new java.util.Properties();
properties.put("user", synDB.getConfigProperties("USER"));
properties.put("password", synDB.getConfigProperties("PASSWORD"));
url = "jdbc:postgresql://" + ServerName + ":" + PortNumber + "/" + DatabaseName;
}
try {
System.out.println("url: " + url);
con = java.sql.DriverManager.getConnection(url, properties);
try {
String sql =
"SELECT a.ID_NRIC, FIRST_NAME, LAST_NAME, MOBILE, EMAIL, ACCOUNT_STATUS, AGENT_CODE, AGENCY, NEED2FA, NEEDTNC, USER_TYPE, USER__SUB_TYPE, a.CREATED_DATE, b.ROLE_NAME "
+ "FROM CUSTOM_STG_USER a INNER JOIN CUSTOM_STG_USER_ROLE b ON (b.ID_NRIC=a.ID_NRIC) ";
StringBuilder sbSQLSTGUser = new StringBuilder();
sbSQLSTGUser.append(sql);
System.out.println("Select from PSE.CUSTOM_STG_USER DB2: " + sbSQLSTGUser.toString());
java.sql.Statement ps = con.createStatement();
java.sql.ResultSet rs = ps.executeQuery(sbSQLSTGUser.toString());
System.out.println("get data....");
Object[] objArrays = new Object[14];
while (rs.next()) {
objArrays = new Object[14];
objArrays[0] = rs.getString(1);
objArrays[1] = rs.getString(2);
objArrays[2] = rs.getString(3);
objArrays[3] = rs.getString(4);
objArrays[4] = rs.getString(5);
objArrays[5] = rs.getString(6);
objArrays[6] = rs.getString(7);
objArrays[7] = rs.getString(8);
objArrays[8] = rs.getString(9);
objArrays[9] = rs.getString(10);
objArrays[10] = rs.getString(11);
objArrays[11] = rs.getString(12);
objArrays[12] = rs.getString(13);
objArrays[13] = rs.getString(14);
listData.add(objArrays);
System.out.println(
"GET FROM DB2 | ID_NRIC:"
+ objArrays[0]
+ ", FIRST_NAME:"
+ objArrays[1]
+ ", LAST_NAME:"
+ objArrays[2]
+ ", MOBILE:"
+ objArrays[3]
+ ", EMAIL:"
+ objArrays[4]
+ ", ACCOUNT_STATUS:"
+ objArrays[5]
+ ", AGENT_CODE:"
+ objArrays[6]
+ ", AGENCY:"
+ objArrays[7]
+ ", NEED2FA:"
+ objArrays[8]
+ ", NEEDTNC:"
+ objArrays[9]
+ ", USER_TYPE:"
+ objArrays[10]
+ ", USER_SUB_TYPE:"
+ objArrays[11]
+ ", CREATED_DATE :"
+ objArrays[12]
+ ", ROLE_NAME :"
+ objArrays[13]);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("select is failing1");
}
if (con != null) {
try {
con.close();
con = null;
} catch (Exception e) {
}
}
} catch (Exception e) {
e.printStackTrace();
}
return listData;
}