Derby - java.sql.SQLException: Column 'table.column_name' not found

Question

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 1

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.

Answer 2

This is probably due to difference in name of key for column in result set. Try executing the query in your database and then copy paste the column name from there.

It should probably be BUSINESSID or businessid.

It is a good idea to use alias for your column names when you are joining multiple tables having same column name.