DBUtilsTest.java
public class DBUtilsTest { public void getDBRecordsSQLTest() { DBUtils objDBUtils = new DBUtils(); ResultSet resultSet = objDBUtils.getDBRecordsSQL("SELECT 78985450.1245487986418648 decimal_val FROM dual"); double expectedValue = 1245.654618764; double dbValue = 0; double toleranceLimit = 0.000000001; try { while(resultSet.next()) { dbValue = resultSet.getDouble("decimal_val"); assertEquals(expectedValue, dbValue, toleranceLimit); } } catch (SQLException e) { e.printStackTrace(); } objDBUtils.closeConnection(); } @Test public void getDBRecordsProcTest() { DBUtils objDBUtils = new DBUtils(); ResultSet resultSet = objDBUtils.getDBRecordsProc("{call TESTJUNIT.junitproc(?)}"); double expectedValue = 7.89854501245488E7; double dbValue = 0; double toleranceLimit = 0.000000001; try { while(resultSet.next()) { dbValue = (double) resultSet.getDouble(1); assertEquals(expectedValue, dbValue, toleranceLimit); } } catch (SQLException e) { e.printStackTrace(); } objDBUtils.closeConnection(); } }
DBUtils.java
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import oracle.jdbc.OracleTypes; public class DBUtils { Connection conn = null; Statement stmt = null; ResultSet rs = null; public ResultSet getDBRecordsSQL(String pQuery) { try { conn = DriverManager.getConnection("hostname", "admin", "admin123"); stmt = (Statement)conn.createStatement(); rs = stmt.executeQuery(pQuery); } catch (SQLException e) { e.printStackTrace(); } return rs; } public ResultSet getDBRecordsProc(String pQuery) { CallableStatement stmt = null; try { conn = DriverManager.getConnection("hostname", "admin", "admin123"); stmt = conn.prepareCall(pQuery); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.execute(); rs = (ResultSet) stmt.getObject(1); } catch (SQLException e) { e.printStackTrace(); } return rs; } public void closeConnection() { if (rs != null) try { rs.close(); } catch (SQLException ignore) {} if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {} if (conn != null) try { conn.close(); } catch (SQLException ignore) {} } }