Why does my unit testing of retrieve a data show null when in main method, it shows an Integer?

Question

I am writing a unit test of a code that retrieves a price from a database using JDBC

public PPrice getPriceByZoneId(int zoneId) throws DatabaseLayerException {
    PPrice foundPrice = null;
    
    Calendar calendar = Calendar.getInstance();
    java.sql.Date dateNow = new java.sql.Date(calendar.getTime().getTime());
    
    Connection con = DBConnection.getInstance().getDBcon();

    String baseSelect = "select top 1 price, pZone_id from PPrice ";
    baseSelect += "where pZone_id = " + zoneId + " and starttime < '" + dateNow + "' ";
    baseSelect += "order by starttime desc";
    System.out.println(baseSelect);

    /*ResultSet rs = null; 
    int price, pZoneId;
    PZone pZone; 
    */
    try {
        Statement stmt = con.createStatement();
        stmt.setQueryTimeout(5);
        // Todo: Get PPrice object
        ResultSet rs = stmt.executeQuery(baseSelect);
        /*
         * Insert code 
         */
    
        while(rs.next()) {
            System.out.println("Price of zoneId  "  + zoneId  + " is " +  rs.getInt("price"));
        }
        stmt.close();
    } catch (SQLException ex) {
        foundPrice = null;
        DatabaseLayerException dle = new DatabaseLayerException("Error retrieving data");
        dle.setStackTrace(ex.getStackTrace());
        throw dle;
    } catch (NullPointerException ex) {
        foundPrice = null;
        DatabaseLayerException dle = new DatabaseLayerException("Null pointer exception - possibly Connection object");
        dle.setStackTrace(ex.getStackTrace());
        throw dle;
    } catch (Exception ex) {
        foundPrice = null;
        DatabaseLayerException dle = new DatabaseLayerException("Data not retrieved! Technical error");
        dle.setStackTrace(ex.getStackTrace());
        throw dle;
    } finally {
        DBConnection.closeConnection();
    }
            
            
    return foundPrice;
}


public static void main(String[] args) {
    DatabasePPrice a = new DatabasePPrice();
    try {
        a.getPriceByZoneId(1) ;
    }
    
    catch (DatabaseLayerException g) {
        System.out.println("Fail" + a);
    
    } finally {
        DBConnection.closeConnection();
    }
 }

The result is it return 35 which is correct.

The problem is when I write a unit test using JUnit. It shows that the actual result is null, but it should be 35. I am so confused. Can someone see the mistake?

This is the unit test I wrote

@Test
public void wasRetrievedPriceDatabaselayer() throws DatabaseLayerException {
    // Arrange
    Calendar calendar = Calendar.getInstance();
    java.sql.Date dateNow = new java.sql.Date(calendar.getTime().getTime());
    
    Connection con = DBConnection.getInstance().getDBcon();
    // Act

    DatabasePPrice a = new DatabasePPrice();
    try {
        a.getPriceByZoneId(1) ;
    }
    
    catch (DatabaseLayerException g) {
        System.out.println("Fail" + a);
    
    } finally {
        DBConnection.closeConnection();
    }

    // Assert 
    assertEquals(35, a.getPriceByZoneId(1));
}

Answer 1

You can call assertEquals inside try block, instead of at the end as the connection will be closed in finally block. Also, remove unnecessary call to a.getPriceByZoneId(1)