Throwing custom exception on an empty ResultSet on JDBC

Question

I have an issue with a boolean statement while trying to use a custom exception. when using my code to check if a coupons exists in the db, it returns true when it exists. however, when coupon does not exist it throws me an sql exception: "Illegal operation on empty result set." instead of my custom one.

My boolean Method:

@Override
public boolean isCouponExist(int couponID) throws SQLException {
    Connection connection = pool.getConnection();

    try {
        PreparedStatement statement = connection.prepareStatement(CHECK_EXISTS);
        statement.setInt(1, couponID);
        ResultSet rs = statement.executeQuery();

        return rs.next();

    } finally {
        pool.restoreConnection(connection);

    }

}

Method to get One Coupon:

@Override
public Coupon getOneCoupon(int couponID) throws SQLException {
    Connection connection = pool.getConnection();

    Coupon result = null;

    try {

        PreparedStatement statement = connection.prepareStatement(GET_ONE_COUPON);
        statement.setInt(1, couponID);
        ResultSet resultSet = statement.executeQuery();
        resultSet.next();
        result = new Coupon(resultSet.getInt(1), resultSet.getInt(2), Category.categoryFor(resultSet.getInt(3)),
                resultSet.getString(4), resultSet.getString(5), resultSet.getDate(6), resultSet.getDate(7),
                resultSet.getInt(8), resultSet.getDouble(9), resultSet.getString(10));

    } finally {
        pool.restoreConnection(connection);
    }

    return result;

}

Method used in the facade for validation:

public void purchaseCoupon(Coupon coupon) throws CouponExceptions, SQLException {


        if(!coup.isCouponExist(coupon.getId())) {
            throw new CouponExceptions("ERROR: Coupon " + coupon.getId() + " does not exist.");
        } 
    
    }

Running the code:

    CustomerFacade customer = (CustomerFacade) login.login("[email protected]", "1378", ClientType.CUSTOMER);

    Coupon p1 = coupDAO.getOneCoupon(55);
    customer.purchaseCoupon(p1);

as you can see I gave it a non-existing ID number in order to get an exception.

StackTrace:

Exception in thread "main" java.sql.SQLException: Illegal operation on empty result set.
    at [email protected]/com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at [email protected]/com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at [email protected]/com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at [email protected]/com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at [email protected]/com.mysql.cj.jdbc.result.ResultSetImpl.checkRowPos(ResultSetImpl.java:484)
    at [email protected]/com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1283)
    at [email protected]/com.mysql.cj.jdbc.result.ResultSetImpl.getInt(ResultSetImpl.java:786)
    at coupon.dbdao.CouponsDBDAO.getOneCoupon(CouponsDBDAO.java:155)
    at coupon.Program.main(Program.java:49)

when trying to give a legal coupon ID I have no issues. when trying to give an illegal coupon ID instead of my custom exception I get SQLException: "Illegal operation on empty result set."

Still new to this.

Answer 1

You are looking for the problem at the wrong place it does not originate from isCouponExist but it originates from getOneCoupon method.

at coupon.dbdao.CouponsDBDAO.getOneCoupon(CouponsDBDAO.java:155)

The problem is that your code in the getOneCoupon does not take into account that the record may not exist into the database.

After reading through the code the problem in getOneMethod is that you dont take into account that the

resultSet.next(); may be false

it should be

if  (resultSet.next()) {
        result = new Coupon(resultSet.getInt(1), resultSet.getInt(2), Category.categoryFor(resultSet.getInt(3)),
                resultSet.getString(4), resultSet.getString(5), resultSet.getDate(6), resultSet.getDate(7),
                resultSet.getInt(8), resultSet.getDouble(9), resultSet.getString(10));
}

With regards to your original question about throwing custom exception you should wrap your code with try catch clause for SQLException.

Still in this particular case your code should take into account the possibility that there is no result.