diff options
| -rw-r--r-- | ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportLoader.java | 311 |
1 files changed, 132 insertions, 179 deletions
diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportLoader.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportLoader.java index 66d6e965..03df7731 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportLoader.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportLoader.java @@ -103,23 +103,20 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { DbUtils.clearConnection(connection); } } // loadCustomReportXML - + public static String loadCustomReportXML(Connection connection, String reportID) throws RaptorException { StringBuffer sb = new StringBuffer(); - - PreparedStatement stmt = null; - - ResultSet rs = null; - - try { - String sql = Globals.getLoadCustomReportXml(); - stmt = connection.prepareStatement(sql); + String sql = Globals.getLoadCustomReportXml(); + + try(PreparedStatement stmt = connection.prepareStatement(sql)) { + stmt.setInt(1,Integer.parseInt(reportID)); - rs = stmt.executeQuery(); - if(Globals.isWeblogicServer()) { + + try(ResultSet rs = stmt.executeQuery()){ + if(Globals.isWeblogicServer()) { java.sql.Clob clob= null; Object obj = null; if (rs.next()) { @@ -127,7 +124,7 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { } else throw new RaptorException("Report " + reportID + " not found in the database"); - + int len = 0; char[] buffer = new char[512]; Reader in = null; @@ -162,103 +159,87 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { in.close();*/ throw new RaptorException("only maria db support for this "); } + } } catch (SQLException ex) { throw new ReportSQLException (ex.getMessage(), ex.getCause()); } catch (IOException ex) { throw new RaptorException (ex.getMessage(), ex.getCause()); - } finally { - try { - if(rs!=null) - rs.close(); - if(stmt!=null) - stmt.close(); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } } return sb.toString(); } // loadCustomReportXML private static void dbUpdateReportXML(Connection connection, String reportID, String reportXML) throws RaptorException { - PreparedStatement stmt = null; - ResultSet rs = null; - try { - String sql = ""; - if(!Globals.isMySQL()) - sql = Globals.getDBUpdateReportXml(); - else - sql = Globals.getDBUpdateReportXmlMySqlSelect(); - stmt = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE, - ResultSet.CONCUR_UPDATABLE); - stmt.setInt(1,Integer.parseInt(reportID)); - rs = stmt.executeQuery(); - Writer out = null; - /*if(Globals.isWeblogicServer()) { - java.sql.Clob clob = null; - if (rs.next()) - clob = rs.getClob(1); - else - throw new RaptorException("Report " + reportID + " not found in the database"); - - if (clob.length() > reportXML.length()) - clob.truncate(0); - //clob.trim(reportXML.length()); - out = ((weblogic.jdbc.vendor.oracle.OracleThinClob)clob).getCharacterOutputStream(); - } else*/ - if (Globals.isPostgreSQL()) { - if (rs.next()) { - rs.updateString("report_xml",reportXML); - rs.updateRow(); - connection.commit(); - //sb.append(rs.getString(1)); - } - else - throw new RaptorException("Report " + reportID + " not found in the database"); - } else if (Globals.isMySQL()) { - if(rs.next()) { - final InputStream stream = rs.getBinaryStream( "report_xml" ); - InputStream streamNew = new ByteArrayInputStream(reportXML.getBytes(StandardCharsets.UTF_8)); - final PreparedStatement update = connection.prepareStatement( Globals.getDBUpdateReportXmlMySql() ); - update.setBinaryStream( 1,streamNew ); - update.setInt( 2,Integer.parseInt(reportID) ); - update.execute(); - } else - throw new RaptorException("Report " + reportID + " not found in the database"); - - } else { - /*oracle.sql.CLOB clob = null; - if (rs.next()) - clob = (oracle.sql.CLOB) rs.getObject(2); - else - throw new RaptorException("Report " + reportID + " not found in the database"); - - if (clob.length() > reportXML.length()) - clob.trim(reportXML.length()); - out = clob.getCharacterOutputStream();*/ - throw new RaptorException("only maria db support for this "); - } - if(!(Globals.isPostgreSQL() || Globals.isMySQL())) { - out.write(reportXML); - out.flush(); - out.close(); + String sql = ""; + if(!Globals.isMySQL()) + sql = Globals.getDBUpdateReportXml(); + else + sql = Globals.getDBUpdateReportXmlMySqlSelect(); + + try (PreparedStatement stmt = connection.prepareStatement(sql, + ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)) { + stmt.setInt(1, Integer.parseInt(reportID)); + try (ResultSet rs = stmt.executeQuery()) { + // Writer out = null; + /* + * if(Globals.isWeblogicServer()) { java.sql.Clob clob = null; if (rs.next()) clob = + * rs.getClob(1); else throw new RaptorException("Report " + reportID + + * " not found in the database"); + * + * if (clob.length() > reportXML.length()) clob.truncate(0); + * //clob.trim(reportXML.length()); out = + * ((weblogic.jdbc.vendor.oracle.OracleThinClob)clob).getCharacterOutputStream(); } + * else + */ + if (Globals.isPostgreSQL()) { + if (rs.next()) { + rs.updateString("report_xml", reportXML); + rs.updateRow(); + connection.commit(); + // sb.append(rs.getString(1)); + } else { + throw new RaptorException( + "Report " + reportID + " not found in the database"); + } + } else if (Globals.isMySQL()) { + if (rs.next()) { + final InputStream stream = rs.getBinaryStream("report_xml"); + try (InputStream streamNew = new ByteArrayInputStream( + reportXML.getBytes(StandardCharsets.UTF_8)); + final PreparedStatement update = connection + .prepareStatement(Globals.getDBUpdateReportXmlMySql())) { + update.setBinaryStream(1, streamNew); + update.setInt(2, Integer.parseInt(reportID)); + update.execute(); + } + } else { + throw new RaptorException( + "Report " + reportID + " not found in the database"); + } + + } else { + /* + * oracle.sql.CLOB clob = null; if (rs.next()) clob = (oracle.sql.CLOB) + * rs.getObject(2); else throw new RaptorException("Report " + reportID + + * " not found in the database"); + * + * if (clob.length() > reportXML.length()) clob.trim(reportXML.length()); out = + * clob.getCharacterOutputStream(); + */ + throw new RaptorException("only maria db support for this "); + } + /* + * if(!(Globals.isPostgreSQL() || Globals.isMySQL())) { out.write(reportXML); + * out.flush(); out.close(); } + */ } - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } catch (IOException ex) { - throw new RaptorException (ex.getMessage(), ex.getCause()); - } finally { - try { - if(rs!=null) - rs.close(); - if(stmt!=null) - stmt.close(); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } - } - } // dbUpdateReportXML + } catch (SQLException ex) { + throw new ReportSQLException(ex.getMessage(), ex.getCause()); + } catch (IOException ex) { + throw new RaptorException(ex.getMessage(), ex.getCause()); + } + } // dbUpdateReportXML public static void updateCustomReportRec(Connection connection, ReportWrapper rw, String reportXML) throws RaptorException { @@ -319,41 +300,32 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { } return dashboardType;*/ } - - public static boolean isReportsAlreadyScheduled ( String reportID ) throws RaptorException { - //String sql = "select rep_id from cr_report_schedule where rep_id = ?"; - String sql = Globals.getIsReportAlreadyScheduled(); - - Connection connection = DbUtils.getConnection(); - PreparedStatement stmt = null; - ResultSet rs = null; - boolean isScheduled= false; - try { - stmt = connection.prepareStatement(sql); - stmt.setInt(1, Integer.parseInt(reportID)); - rs = stmt.executeQuery(); - if(rs.next()) { - isScheduled = true; - } - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } finally { - try { - if(rs!=null) - rs.close(); - if(stmt!=null) - stmt.close(); - DbUtils.clearConnection(connection); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } - } - return isScheduled; -} + + public static boolean isReportsAlreadyScheduled(String reportID) throws RaptorException { + // String sql = "select rep_id from cr_report_schedule where rep_id = ?"; + String sql = Globals.getIsReportAlreadyScheduled(); + + Connection connection = DbUtils.getConnection(); + + boolean isScheduled = false; + try (PreparedStatement stmt = connection.prepareStatement(sql)) { + stmt.setInt(1, Integer.parseInt(reportID)); + try (ResultSet rs = stmt.executeQuery()) { + if (rs.next()) { + isScheduled = true; + } + } + } catch (SQLException ex) { + throw new ReportSQLException(ex.getMessage(), ex.getCause()); + } finally { + DbUtils.clearConnection(connection); + } + return isScheduled; + } public static void createCustomReportRec(Connection connection, ReportWrapper rw, String reportXML) throws RaptorException { - + /*DbUtils .executeUpdate( connection, @@ -430,7 +402,7 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { //DataSet ds = DbUtils // .executeQuery("SELECT cr.rep_id, cr.title FROM cr_report cr WHERE nvl(cr.owner_id, cr.create_id) = " // + userID); - + for (int i = 0; i < ds.getRowCount(); i++) reportIdNames.add(new IdNameValue(ds.getString(i, 0), ds.getString(i, 1))); } catch (Exception e) { @@ -439,43 +411,35 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { return reportIdNames; } // getUserReportNames - public static String getReportOwnerID(String reportID) throws RaptorException { - - // String sql = "SELECT nvl(cr.owner_id, cr.create_id) owner FROM cr_report cr WHERE rep_id = ?"; - - String sql = Globals.getTheReportOwnerId(); - - Connection connection = DbUtils.getConnection(); - PreparedStatement stmt = null; - ResultSet rs = null; - String reportOwnerID = null; - try { - stmt = connection.prepareStatement(sql); - stmt.setInt(1, Integer.parseInt(reportID)); - rs = stmt.executeQuery(); - if(rs.next()) { - reportOwnerID = rs.getString(1); - } - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } finally { - try { - if(rs!=null) - rs.close(); - if(stmt!=null) - stmt.close(); - DbUtils.clearConnection(connection); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } - } - - return reportOwnerID; - } // getReportOwnerID + public static String getReportOwnerID(String reportID) throws RaptorException { + + // String sql = "SELECT nvl(cr.owner_id, cr.create_id) owner FROM cr_report cr WHERE rep_id + // = ?"; + + String sql = Globals.getTheReportOwnerId(); + + Connection connection = DbUtils.getConnection(); + + String reportOwnerID = null; + try (PreparedStatement stmt = connection.prepareStatement(sql)) { + stmt.setInt(1, Integer.parseInt(reportID)); + try (ResultSet rs = stmt.executeQuery()) { + if (rs.next()) { + reportOwnerID = rs.getString(1); + } + } + } catch (SQLException ex) { + throw new ReportSQLException(ex.getMessage(), ex.getCause()); + } finally { + DbUtils.clearConnection(connection); + } + + return reportOwnerID; + } // getReportOwnerID public static void deleteReportRecord(String reportID) throws RaptorException { Connection con = DbUtils.startTransaction(); - + /*try { DbUtils.executeUpdate(con, "DELETE cr_report_log WHERE rep_id = " + reportID); DbUtils.executeUpdate(con, "DELETE cr_report_schedule_users WHERE rep_id = " @@ -916,11 +880,8 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { public static void clearReportLogEntries(String reportId, String userId) throws RaptorException { String sql = Globals.getClearReportLogEntries(); Connection connection = DbUtils.getConnection(); - PreparedStatement stmt = null; - String reportOwnerID = null; int rowsAffected = 0; - try { - stmt = connection.prepareStatement(sql); + try(PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setInt(1, Integer.parseInt(reportId)); stmt.setInt(2, Integer.parseInt(userId)); rowsAffected = stmt.executeUpdate(); @@ -928,16 +889,8 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { } catch (SQLException ex) { throw new ReportSQLException (ex.getMessage(), ex.getCause()); } finally { - try { - if(stmt!=null) - stmt.close(); - if(connection!=null) - connection.close(); - DbUtils.clearConnection(connection); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } - } + DbUtils.clearConnection(connection); + } } // clearReportLogEntries public static Vector loadReportLogEntries(String reportId) throws RaptorException { @@ -945,7 +898,7 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { query.append(" (CASE WHEN x.action = 'Report Execution Time' THEN "); query.append(" '<a href=\"" + AppUtils.getRaptorActionURL() +"report.run.container&c_master='||x.rep_id||'&'||x.form_fields||'&fromReportLog=Y&display_content=Y&noFormFields=Y&refresh=Y\">'||x.action||'</a>'"); query.append(" ELSE x.action END) action, " ); - query.append(" (CASE WHEN x.action = 'Report Execution Time' THEN "); + query.append(" (CASE WHEN x.action = 'Report Execution Time' THEN "); query.append(" action_value " ); query.append(" ELSE 'N/A' END) time_taken, " ); query.append( " (CASE WHEN x.action = 'Report Execution Time' THEN '<a href=\"" + AppUtils.getRaptorActionURL() +"report.run.container&c_master='||x.rep_id||'&'||x.form_fields||'&fromReportLog=Y&display_content=Y&noFormFields=Y&refresh=Y\"><img src=\""+AppUtils.getImgFolderURL()+ "test_run.gif\" width=\"12\" height=\"12\" border=0 alt=\"Run report\"/></a>' ELSE 'N/A' END) run_image, " ); |