Derby – java.sql.SQLException: Column ‘table.column_name’ not found

I have these 2 tables: inventory and product_categories both tables have a common column called businessId.

Now I have 2 databases, one is hosted on MySQL and the other on Derby both databases have same table structures.

So I’ve been executing the following query on both databases:

SELECT * 
FROM INVENTORY
INNER JOIN PRODUCT_CATEGORIES
    ON INVENTORY.BUSINESSID = PRODUCT_CATEGORIES.BUSINESSID
    AND INVENTORY.CATEGORY = PRODUCT_CATEGORIES.CATEGORYNAME
WHERE INVENTORY.BUSINESSID = 1

When I execute the query above using the Java code below, I get a successful ResultSet in both databases, however Derby throws an exception when I attempt to get the businessId column from the product_categories table

try(Connection conn = dbConfig.getDatabaseConnection())
{
    PreparedStatement pst = conn.prepareStatement(sql);
    pst.setInt(1, businessId);
    List<Product> products = new ArrayList<>();
    ResultSet rs = pst.executeQuery();
    while(rs.next())
    {
        ...
        int businessId = rs.getInt("product_categories.businessId"); //<-- This lines throws an exception
        ...
    }
}

I am getting this error message:

java.sql.SQLException: Column ‘product_categories.businessId’ not found

Please what is going wrong here?

Answer

Columns in the resultset are not prefixed with table aliased.

Replace:

int businessId = rs.getInt("product_categories.businessId");

With:

int businessId = rs.getInt("businessId");

NB: using SELECT * is usually not a very good practice; it is better to explictly list the columns that you want the query to return. This makes the indent of the query clearer, and can help avoiding name conflicts when the same column name exists in different tables coming into play in the query.