CODEWITHSUNDEEP
javasqljdbc
benscabbia
benscabbia

Reputation: 18072

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.

Upvotes: 2

Views: 1773

Answers (3)

Sigi Janssens
Sigi Janssens

Reputation: 11

After a day of working on this, I achieved it by using resultSet.getMetaData().

private int getIndexFromMeta(String column) {
    try {
        ResultSetMetaData meta = resultSet.getMetaData();
        String[] subs = column.split("\\.", -1);
        String tableName = subs[0];
        String columnName = subs[1];

        for (int i = 1; i <= meta.getColumnCount(); i++) {
            if (meta.getTableName(i).equals(tableName) && meta.getColumnName(i).equals(columnName)) {
                return i;
            }
        }
    } catch (SQLException e) {
        Log.trace(e);
    }

    return 0;
}

Upvotes: 1

M A
M A

Reputation: 72874

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"));

Upvotes: 3

Ori Lentz
Ori Lentz

Reputation: 3688

It seems like the ResultSet you're getting back holds the following columns:

  • id
  • name
  • id
  • job
  • salary

You have two columns named "id" (none named "Person.id"), so when you try to get its' value you either

  • Ask for "id" which is ambiguous (which id?)
  • Ask for "Person.id" which does not exist

Simply try specifying in your query the columns you want and giving them unique aliases. For example:

ResultSet rs = statement.executeQuery("select Person.id AS 'personid', name from Person join workInfo on (Person.id=WorkInfo.id)");
while(rs.next()){ 
    System.out.println("id: " + rs.getInt("personid"));
    System.out.println("name: " + rs.getString("name"));

Upvotes: 0

Related Questions