diff options
| -rw-r--r-- | ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/DbUtils.java | 448 |
1 files changed, 218 insertions, 230 deletions
diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/DbUtils.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/DbUtils.java index 37d3612c..d2a97dfe 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/DbUtils.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/DbUtils.java @@ -59,254 +59,242 @@ import org.springframework.beans.factory.annotation.Autowired; public class DbUtils { - private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(DbUtils.class); - - private static DataSource dataSource; - - public DbUtils() { - } - - public static Connection getConnection() throws ReportSQLException { - try { - return AppUtils.getDatasource().getConnection(); - } catch(SQLException ex) { - ex.printStackTrace(); - } - return null; - } // getConnection - - public static void clearConnection(Connection con) throws ReportSQLException { - try { - if ((con != null) && !con.isClosed()) { - Globals.getDbUtils().clearConnection(con); - } - } catch (SQLException ex) { - throw new ReportSQLException(ex.getMessage(), ex.getCause()); - } catch (Exception ex2 ) { - throw new ReportSQLException (ex2.getMessage(), ex2.getCause()); - } - } // clearConnection - - public static Connection startTransaction() throws ReportSQLException { - Connection con = null; - try { - con = getConnection(); - con.setAutoCommit(false); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } catch (Exception ex2 ) { - throw new ReportSQLException (ex2.getMessage(), ex2.getCause()); - } - return con; - } // startTransaction - - public static void commitTransaction(Connection con) throws ReportSQLException { - try { - con.commit(); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } catch (Exception ex2 ) { - throw new ReportSQLException (ex2.getMessage(), ex2.getCause()); - } - } // commitTransaction - - public static void rollbackTransaction(Connection con) throws ReportSQLException { - try { - con.rollback(); - clearConnection(con); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } catch (Exception ex2 ) { - throw new ReportSQLException (ex2.getMessage(), ex2.getCause()); - } - } // rollbackTransaction - - public static String executeCall(Connection con, String sql, boolean expectResult) - throws ReportSQLException { - String result = null; - - try { - if(con.isClosed()) con = getConnection(); + private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(DbUtils.class); + + private static DataSource dataSource; + + public DbUtils() {} + + public static Connection getConnection() throws ReportSQLException { + try { + return AppUtils.getDatasource().getConnection(); + } catch (SQLException ex) { + logger.error(EELFLoggerDelegate.debugLogger, ("Error " + ex)); + } + return null; + } // getConnection + + public static void clearConnection(Connection con) throws ReportSQLException { + try { + if ((con != null) && !con.isClosed()) { + Globals.getDbUtils().clearConnection(con); + } + } catch (SQLException ex) { + throw new ReportSQLException(ex.getMessage(), ex.getCause()); + } catch (Exception ex2) { + throw new ReportSQLException(ex2.getMessage(), ex2.getCause()); + } + } // clearConnection + + public static Connection startTransaction() throws ReportSQLException { + Connection con = null; + try { + con = getConnection(); + con.setAutoCommit(false); + + } catch (SQLException ex) { + throw new ReportSQLException(ex.getMessage(), ex.getCause()); + } catch (Exception ex2) { + throw new ReportSQLException(ex2.getMessage(), ex2.getCause()); + } + return con; + } // startTransaction + + public static void commitTransaction(Connection con) throws ReportSQLException { + try { + con.commit(); + } catch (SQLException ex) { + throw new ReportSQLException(ex.getMessage(), ex.getCause()); + } catch (Exception ex2) { + throw new ReportSQLException(ex2.getMessage(), ex2.getCause()); + } + } // commitTransaction + + public static void rollbackTransaction(Connection con) throws ReportSQLException { + try { + con.rollback(); + clearConnection(con); + } catch (SQLException ex) { + throw new ReportSQLException(ex.getMessage(), ex.getCause()); + } catch (Exception ex2) { + throw new ReportSQLException(ex2.getMessage(), ex2.getCause()); + } + } // rollbackTransaction + + public static String executeCall(Connection con, String sql, boolean expectResult) throws ReportSQLException { + String result = null; + + try { + if (con.isClosed()) + con = getConnection(); logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL Call] " + sql)); - try(CallableStatement stmt = con.prepareCall(sql)){ - if (expectResult) - stmt.registerOutParameter(1, Types.CHAR); - stmt.executeUpdate(); - if (expectResult) - result = stmt.getString(1); + try (CallableStatement stmt = con.prepareCall(sql)) { + if (expectResult) + stmt.registerOutParameter(1, Types.CHAR); + stmt.executeUpdate(); + if (expectResult) + result = stmt.getString(1); con.commit(); - } - } catch (SQLException e) { - throw new ReportSQLException(e.getMessage(), sql); - } finally { + } + } catch (SQLException e) { + throw new ReportSQLException(e.getMessage(), sql); + } finally { clearConnection(con); } - return result; - } // executeCall + return result; + } // executeCall - public static String executeCall(String sql, boolean expectResult) - throws RaptorException { - Connection con = null; - con = getConnection(); - String result = executeCall(con, sql, expectResult); - //con.commit(); - return result; - } // executeCall + public static String executeCall(String sql, boolean expectResult) throws RaptorException { + Connection con = null; + con = getConnection(); + String result = executeCall(con, sql, expectResult); + return result; + } // executeCall - public static int executeUpdate(Connection con, String sql) throws ReportSQLException { + public static int executeUpdate(Connection con, String sql) throws ReportSQLException { int rcode = -1; - try(Statement stmt = con.createStatement()) { - logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL Update] " + sql)); - rcode = stmt.executeUpdate(sql); - //con.commit(); - } catch (SQLException e) { - //e.printStackTrace(); - throw new ReportSQLException(e.getMessage(), sql); - } + try (Statement stmt = con.createStatement()) { + logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL Update] " + sql)); + rcode = stmt.executeUpdate(sql); + } catch (SQLException e) { + throw new ReportSQLException(e.getMessage(), sql); + } return rcode; - } // executeUpdate + } // executeUpdate public static int executeUpdate(String sql) throws ReportSQLException { - Connection con = null; - try { - con = getConnection(); - int rcode = executeUpdate(con, sql); - if(Globals.getDBType().equals("oracle")) - con.commit(); - - return rcode; - } catch (SQLException e) { - throw new ReportSQLException(e.getMessage(), sql); - } finally { - clearConnection(con); - } - } // executeUpdate - - public static DataSet executeQuery(Connection con, String sql) throws ReportSQLException { - return executeQuery(con, sql, Integer.MAX_VALUE); - } // executeQuery - - public static DataSet executeQuery(Connection con, String sql, int maxRowLimit) - throws ReportSQLException { - try { - if(con.isClosed()) con = getConnection(); - //con. - try(Statement stmt = con.createStatement(); - ResultSet rs = stmt.executeQuery(sql)) - { - logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL] " + sql)); - return new DataSet(rs, maxRowLimit); - } - } catch (SQLException e) { - throw new ReportSQLException(e.getMessage(), sql); - } - } // executeQuery - - public static DataSet executeQuery(String sql, String reportName, String reportID) throws ReportSQLException { - Connection con = null; + Connection con = null; try { - con = getConnection(); - PreparedStatement preparedStatement = con.prepareStatement(sql); - if(StringUtils.isNotBlank(reportID)) { - preparedStatement.setString(1, reportID); - preparedStatement.setString(2, reportName); - }else { - preparedStatement.setString(1, reportName); - } - - try(ResultSet rs = preparedStatement.executeQuery();) - { - logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL] " + sql)); - return new DataSet(rs, Integer.MAX_VALUE); - } - } catch (SQLException e) { - throw new ReportSQLException(e.getMessage(), sql); - } catch (ReportSQLException ex) { - logger.error(EELFLoggerDelegate.debugLogger, ("Error " + sql)); - throw new ReportSQLException(ex.getMessage(), ex); - }catch(Exception ex1) { - throw new ReportSQLException(ex1.getMessage(), ex1.getCause()); + con = getConnection(); + int rcode = executeUpdate(con, sql); + if (Globals.getDBType().equals("oracle")) + con.commit(); + + return rcode; + } catch (SQLException e) { + throw new ReportSQLException(e.getMessage(), sql); } finally { clearConnection(con); - } - } // executeQuery + } + } // executeUpdate + + public static DataSet executeQuery(Connection con, String sql) throws ReportSQLException { + return executeQuery(con, sql, Integer.MAX_VALUE); + } // executeQuery + + public static DataSet executeQuery(Connection con, String sql, int maxRowLimit) throws ReportSQLException { + try { + if (con.isClosed()) + con = getConnection(); + // con. + try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql)) { + logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL] " + sql)); + return new DataSet(rs, maxRowLimit); + } + } catch (SQLException e) { + throw new ReportSQLException(e.getMessage(), sql); + } + } // executeQuery + + public static DataSet executeQuery(String sql, String reportName, String reportID) throws ReportSQLException { + try (Connection con = getConnection(); PreparedStatement preparedStatement = con.prepareStatement(sql);) { + if (StringUtils.isNotBlank(reportID)) { + preparedStatement.setString(1, reportID); + preparedStatement.setString(2, reportName); + } else { + preparedStatement.setString(1, reportName); + } - public static DataSet executeQuery(String sql) throws ReportSQLException { - return executeQuery(sql, Integer.MAX_VALUE); - } // executeQuery + try (ResultSet rs = preparedStatement.executeQuery();) { + logger.debug(EELFLoggerDelegate.debugLogger, ("[SQL CALL FROM RAPTOR] [SQL] " + sql)); + return new DataSet(rs, Integer.MAX_VALUE); + } + } catch (SQLException e) { + throw new ReportSQLException(e.getMessage(), sql); + } catch (ReportSQLException ex) { + logger.error(EELFLoggerDelegate.debugLogger, ("Error " + sql)); + throw new ReportSQLException(ex.getMessage(), ex); + } catch (Exception ex1) { + throw new ReportSQLException(ex1.getMessage(), ex1.getCause()); + } + } // executeQuery + + public static DataSet executeQuery(String sql) throws ReportSQLException { + return executeQuery(sql, Integer.MAX_VALUE); + } // executeQuery - public static DataSet executeQuery(String sql, int maxRowLimit) throws ReportSQLException { - Connection con = null; + public static DataSet executeQuery(String sql, int maxRowLimit) throws ReportSQLException { + Connection con = null; try { - con = getConnection(); - return executeQuery(con, sql, maxRowLimit); - }catch (ReportSQLException ex) { - logger.error(EELFLoggerDelegate.debugLogger, ("Error " + sql)); - throw new ReportSQLException(ex.getMessage(), ex); - }catch(Exception ex1) { - throw new ReportSQLException(ex1.getMessage(), ex1.getCause()); + con = getConnection(); + return executeQuery(con, sql, maxRowLimit); + } catch (ReportSQLException ex) { + logger.error(EELFLoggerDelegate.debugLogger, ("Error " + sql)); + throw new ReportSQLException(ex.getMessage(), ex); + } catch (Exception ex1) { + throw new ReportSQLException(ex1.getMessage(), ex1.getCause()); } finally { clearConnection(con); - } - } // executeQuery - - //For ZK Support - - public static int executeQuery(ReportRuntime rr, int dateOption) { - Connection con = null; - int rowCount = 0; - try { - con = ConnectionUtils.getConnection(rr.getDBInfo()); - String wholeSql = rr.getWholeSQL(); - - DataColumnType dc = rr.getColumnWhichNeedEnhancedPagination(); - String date_ColId = dc.getColId(); - String dataFormat = dc.getColFormat(); - if(dataFormat!=null && dataFormat.length()>0) - date_ColId = "to_date("+date_ColId+", '"+ dataFormat +"')"; - String sql = ""; - if(dateOption == 1) - sql = "select count(distinct to_char("+date_ColId+", 'YYYY/MM')) from ("+wholeSql+")"; - else if (dateOption == 3) - sql = "select count(distinct to_char("+date_ColId+", 'YYYY/MM/DD')) from ("+wholeSql+")"; - else if (dateOption == 2) - sql = "select count(distinct to_char("+date_ColId+", 'YYYY')) from ("+wholeSql+")"; - DataSet ds = executeQuery(con, sql.toString()); - rowCount = ds.getInt(0,0); - } catch (ReportSQLException ex) { - ex.printStackTrace(); - }catch(Exception ex1) { - ex1.printStackTrace(); + } + } // executeQuery + + // For ZK Support + + public static int executeQuery(ReportRuntime rr, int dateOption) { + Connection con = null; + int rowCount = 0; + try { + con = ConnectionUtils.getConnection(rr.getDBInfo()); + String wholeSql = rr.getWholeSQL(); + + DataColumnType dc = rr.getColumnWhichNeedEnhancedPagination(); + String date_ColId = dc.getColId(); + String dataFormat = dc.getColFormat(); + if (dataFormat != null && dataFormat.length() > 0) + date_ColId = "to_date(" + date_ColId + ", '" + dataFormat + "')"; + String sql = ""; + if (dateOption == 1) + sql = "select count(distinct to_char(" + date_ColId + ", 'YYYY/MM')) from (" + wholeSql + ")"; + else if (dateOption == 3) + sql = "select count(distinct to_char(" + date_ColId + ", 'YYYY/MM/DD')) from (" + wholeSql + ")"; + else if (dateOption == 2) + sql = "select count(distinct to_char(" + date_ColId + ", 'YYYY')) from (" + wholeSql + ")"; + DataSet ds = executeQuery(con, sql.toString()); + rowCount = ds.getInt(0, 0); + } catch (ReportSQLException ex) { + logger.error(EELFLoggerDelegate.debugLogger, ("Error " + ex)); + } catch (Exception ex1) { + logger.error(EELFLoggerDelegate.debugLogger, ("Error " + ex1)); } finally { - try { - clearConnection(con); - } catch (ReportSQLException ex2) { - ex2.printStackTrace(); - } - } - return rowCount; - } - - public String nvl(String s) { - return (s == null) ? "" : s; - } - - public static String nvls(String s) { - return (s == null) ? "" : s; - } - - public static String nvl(String s, String sDefault) { - return nvls(s).equals("") ? sDefault : s; - } - - public static DataSource getDataSource() { - return dataSource; - } - - @Autowired - public void setDataSource(DataSource dataSource) { - dataSource = dataSource; - } - + try { + clearConnection(con); + } catch (ReportSQLException ex2) { + logger.error(EELFLoggerDelegate.debugLogger, ("Error " + ex2)); + } + } + return rowCount; + } + + public String nvl(String s) { + return (s == null) ? "" : s; + } + + public static String nvls(String s) { + return (s == null) ? "" : s; + } + + public static String nvl(String s, String sDefault) { + return nvls(s).equals("") ? sDefault : s; + } + + public static DataSource getDataSource() { + return dataSource; + } + + @Autowired + public void setDataSource(DataSource dataSource) { + dataSource = dataSource; + } + } // DbUtils |