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) {}
	}
}

Comments are closed.