public String insertData(
String resultsetname,
String resultdesc,
String resultPermission,
String reportids,
String repFlag,
String queryID,
SimpleJdbcTemplate template,
Timestamp dstamp)
throws Exception {
log.info("insert----> ");
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
ApplicationContext ctx = AppContext.getApplicationContext();
com.nrg.lau.dao.SharedConnectionDAO shConnection =
(com.nrg.lau.dao.SharedConnectionDAO) ctx.getBean("getSharedConnection");
Connection con = shConnection.getConVerify();
String success = "";
// queryID = "";
try {
long queryRsltID = CommonDAO.getUniqueId(template);
log.info("Generated Primary Key for save resultset -> " + queryRsltID);
log.info("queryRsltID------------" + queryRsltID);
log.info("queryID----------------" + queryID);
log.info("resultsetname----------" + resultsetname);
log.info("resultdesc-------------" + resultdesc);
log.info("resultPermission-------------" + resultPermission);
log.info("userId-----------------" + userId);
log.info("dt---------------------" + dstamp);
log.info("queryRsltID------------" + queryRsltID);
ps1 = con.prepareStatement(sqlParent);
ps1.setLong(1, queryRsltID);
ps1.setString(2, "");
ps1.setString(3, resultsetname);
ps1.setString(4, resultdesc);
ps1.setString(5, userId);
ps1.setObject(6, dstamp);
ps1.setString(7, userId);
ps1.setObject(8, dstamp);
ps1.setString(9, resultPermission);
ps1.addBatch();
ps2 = con.prepareStatement(sqlChild);
String strReportIds = reportids;
String[] reportIdArray = strReportIds.split(",");
for (int i = 0; i < reportIdArray.length; i++) {
String rprtId = reportIdArray[i];
Long rptId = Long.valueOf(rprtId);
// log.info(" rptId-----------"+rptId);
ps2.setLong(1, queryRsltID);
ps2.setLong(2, rptId);
ps2.setString(3, repFlag);
ps2.setString(4, userId);
ps2.setObject(5, dstamp);
ps2.addBatch();
}
log.info(" Start Batch execute------------------------------------------------");
int[] rtn1 = ps1.executeBatch();
log.info(" rtn1---------" + rtn1);
ps1.close();
log.info(" END executeBatch 1------------------------------------------------:");
int[] rtn2 = ps2.executeBatch();
log.info(" rtn2 length---------" + rtn2.length);
log.info(" rtn2 data##### " + rtn2[0]);
success = Integer.toString(rtn2[0]);
ps2.close();
log.info(" END executeBatch 2------------------------------------------------:");
con.commit();
} catch (Exception e) {
log.error("IN --" + e);
con.rollback();
log.error("IN -- Rollback---");
throw new Exception(e);
} finally {
try {
try {
if (ps1 != null) ps1.close();
} catch (Exception e) {
log.error(e, e);
}
try {
if (ps2 != null) ps2.close();
} catch (Exception e) {
log.error(e, e);
}
if (con != null) con.close();
} catch (Exception e) {
log.error("Error -- " + e);
}
}
return success;
}
public String updateData(
String resultSetID,
String resultsetname,
String resultdesc,
String resultPermission,
String reportids,
String repFlag,
String queryID,
SimpleJdbcTemplate template,
Timestamp dstamp)
throws Exception {
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
ApplicationContext ctx = AppContext.getApplicationContext();
com.nrg.lau.dao.SharedConnectionDAO shConnection =
(com.nrg.lau.dao.SharedConnectionDAO) ctx.getBean("getSharedConnection");
Connection con = shConnection.getConVerify();
String success = "";
// queryID = "";
try {
int idchild = 0;
String sqlParent =
"UPDATE LAU_QUERY_RESULT_SETS SET QUERY_ID=?,RESULT_SET_NAME=?,"
+ "RESULT_SET_DESCRIPTION=?,CREATE_USER_ID=?, CREATE_TIMESTAMP=?, UPDATE_USER_ID=?, UPDATE_TIMESTAMP=?, RESULT_SET_PERMISSION=? WHERE QUERY_RESULT_SET_ID=?";
log.info("queryRsltID------------" + resultSetID);
log.info("queryID----------------" + queryID);
log.info("resultsetname----------" + resultsetname);
log.info("resultdesc-------------" + resultdesc);
log.info("resultPermission-------------" + resultPermission);
log.info("userId-----------------" + userId);
log.info("dt---------------------" + dstamp);
ps1 = con.prepareStatement(sqlParent);
ps1.setString(1, queryID);
ps1.setString(2, resultsetname);
ps1.setString(3, resultdesc);
ps1.setString(4, userId);
ps1.setObject(5, dstamp);
ps1.setString(6, userId);
ps1.setObject(7, dstamp);
ps1.setString(8, resultPermission);
ps1.setString(9, resultSetID);
ps1.addBatch();
CommonDAO.setDbmsClientInfo(template, userId);
log.info("******************DELETING Saved ResultSet");
idchild =
template.update(
"DELETE FROM LAU_SAVED_QUERY_RESULTS WHERE QUERY_RESULT_SET_ID = ?", resultSetID);
log.info("LauSaveResultSetDAO delete() ID Child -> " + idchild);
// INSERTING THE SavedResultSet Content
ps2 = con.prepareStatement(sqlChild);
String strReportIds = reportids;
String[] reportIdArray = strReportIds.split(",");
for (int i = 0; i < reportIdArray.length; i++) {
String rprtId = reportIdArray[i];
Long rptId = Long.valueOf(rprtId);
// log.info(" rptId-----------"+rptId);
ps2.setString(1, resultSetID);
ps2.setLong(2, rptId);
ps2.setString(3, repFlag);
ps2.setString(4, userId);
ps2.setObject(5, dstamp);
ps2.addBatch();
}
log.info(" Start Batch execute------------------------------------------------");
int[] rtn1 = ps1.executeBatch();
ps1.close();
log.info(" END executeBatch 1------------------------------------------------:");
log.info(" START executeBatch Child------------------------------------------------:");
int[] rtn2 = ps2.executeBatch();
log.info(" Update length---------" + rtn2.length);
log.info(" Update data##### " + rtn2[0]);
success = Integer.toString(rtn2[0]);
ps2.close();
log.info(" END executeBatch 2Child------------------------------------------------:");
} catch (Exception e) {
log.error("IN --" + e);
con.rollback();
log.error("IN -- Rollback---");
throw new Exception(e);
} finally {
try {
try {
if (ps2 != null) ps2.close();
} catch (Exception e) {
log.error(e, e);
}
} catch (Exception e) {
log.error("Error -- " + e);
}
}
return success;
}
private String insertChartData(
String resultSetName,
String resultdesc,
String resultPermission,
String repFlag,
String queryId,
SimpleJdbcTemplate template,
Timestamp dstamp)
throws Exception {
log.info("Insert Chart!!!!");
String success = "";
CallableStatement stmtPar = null;
CallableStatement stmtChild = null;
ApplicationContext ctx = AppContext.getApplicationContext();
com.nrg.lau.dao.SharedConnectionDAO shConnection =
(com.nrg.lau.dao.SharedConnectionDAO) ctx.getBean("getSharedConnection");
Connection con = shConnection.getConVerify();
try {
log.info("Chart QuerID: " + queryId);
String chartParentSql = "{call ? := LAU_LIST.saveResultSet(?,?,?,?,?)}";
stmtPar = con.prepareCall(chartParentSql);
stmtPar.registerOutParameter(1, OracleTypes.VARCHAR);
stmtPar.setString(2, queryId);
stmtPar.setString(3, resultSetName);
stmtPar.setString(4, resultPermission);
stmtPar.setString(5, resultdesc);
stmtPar.setString(6, userId);
log.info("parSQL " + chartParentSql);
Object s = stmtPar.execute(); // Function executed here
log.info("function executed successfully!!!");
if (s != null) success = "-2";
log.info("reached the end of chart insert = " + success);
stmtPar.close();
} catch (Exception e) {
log.error("IN --" + e);
con.rollback();
log.error("IN -- Rollback---");
throw new Exception(e);
} finally {
try {
try {
if (stmtPar != null) stmtPar.close();
} catch (Exception e) {
log.error(e, e);
}
try {
if (stmtChild != null) stmtChild.close();
} catch (Exception e) {
log.error(e, e);
}
} catch (Exception e) {
log.error("Error -- " + e);
}
}
return success;
}
