Ambiguous column using JDBC but query works fine in database

I am connecting to a SQLite database through java using JDBC.

Schema:

WorkInfo(id, job, salary)
Person(id, name)

This query below runs fine in my database, but when I try it with JDBC:

ResultSet rs = statement.executeQuery("select * from Person join workInfo on (Person.id=WorkInfo.id)");
while(rs.next()){ 
    System.out.println("id: " + rs.getInt("Person.id")); //column does not exist
    System.out.println("name: " + rs.getString("name")); //works fine

Output:

If using person.id: no such column: 'person.id'

Without specifying: ambiguous column name 'id'

I’ve tried using both WorkInfo and Person and using aliases but it keeps throwing the same ambigious column name (if left as id) or column does not exist.

Answer

It’s always a good practice to explicitly retrieve the columns you want. I would change the query to be:

ResultSet rs = statement.executeQuery("select info.id, info.job, info.salary, "
    + "person.id, person.name from Person person join workInfo info "
    + "on person.id=info.id");
while(rs.next()){ 
    System.out.println("id: " + rs.getInt(4));
    System.out.println("name: " + rs.getString(5));

In this case, you can use the column index instead of the label.

Or using the AS clause:

ResultSet rs = statement.executeQuery("select info.id, info.job, info.salary, "
    + "person.id as personId, person.name as personName "
    + "from Person person join workInfo info "
    + "on person.id=info.id");
while(rs.next()){ 
    System.out.println("id: " + rs.getInt("personId"));
    System.out.println("name: " + rs.getString("personName"));