Same column names in JOIN getting "column not found" error in ResultSet

Question

I have three tables firm, shipp and supplier.

all three columns have same column names as id, name, city ... other.

And I have written a join like in java:

String Query = "SELECT s.name Sname,s.city Scity,f.name Fname,f.city Fcity,"
        +"su.name Suname,su.city Sucity "
        +"FROM `order_details` ot "
        +"INNER JOIN `order` o ON ot.odr_id = o.odr_id " 
        +"INNER JOIN `product` p ON ot.pro_id = p.id "
        +"INNER JOIN `firm` f ON o.firm_id = f.id "
        +"INNER JOIN `shipp` s ON o.shipp_id = s.id "
        +"INNER JOIN `supplier` su ON o.sup_id = su.id ";

product,order and order_details are the other tables that are used in join.

This query working in phpMyAdmin.

But in java ResultSet giving "column 'Sname' not found." error.

I have tried below solutions but these are not working for me:

1) java.sq.SQLException: Column not found

2) Strange SQLException: Column not found

3) Many other but no one working

Error Stack:

java.sql.SQLException: Column 'Sname' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)

And:

SEVERE: null

Please help me!

Thanks In advance.

Edit 1:

Problem Reproduce:

When I run it with fetching data only from one aliased table (e.g. shipp) then it fetches data fine:

SELECT s.name Sname,s.city Scity ... other query part

But as soon as I SELECT columns from other aliased tables (e.g. shipp, firm, and supplier) then it throws error exception written above (Column not found).

SELECT s.name Sname,s.city Scity,f.name Fname,f.city Fcity,
       su.name Suname,su.city Sucity ... other query part

Note: This query running fine in phpmyadmin but not running with ResultSet fetch.

And this is how I'm getting columns from ResultSet (rs2):

String firstColumn = "";
String secondColumn = "";

while(rs2.next()) {
    firstColumn = "Shipp to : ";
    firstColumn += rs2.getString("Sname") + rs2.getString("Scity") ;
    firstColumn += "Person : " + rs2.getString("Fname") +"+ "+ rs2.getString("Fcity");

    secondColumn = "Supplier : " + rs2.getString("Suname");
    secondColumn += rs2.getString("Suname");
    secondColumn += rs2.getString("Sucity");
}

Answer 1

Based on your error message I assume that you are using MySQL with the latest connector.

You can utilize the positional queries if necessary, this is using the number on which position on which the element could be find for example:

rs2.getString(1);

(Yes the numbering starts at 1...) It should be equivalent to this (of course without the capitalization problem):

rs2.getString("Sname");

This way you have to know the position only, but if you look at case sensitivity in the database/driver is pretty much the question of settings. To get around most of the problems I write my queries in capital if possible and without a backtick `.

To explore your ResultSet object try the following:

Statement statement = ...;
String queryString = "SELECT s.name sname,s.city scity,f.name fname, f.city fcity, su.name suname, su.city sucity "
        +" FROM `order_details` ot "
        +" INNER JOIN `order` o ON ot.odr_id = o.odr_id "
        +" INNER JOIN `product` p ON ot.pro_id = p.id "
        +" INNER JOIN `firm` f ON o.firm_id = f.id "
        +" INNER JOIN `shipp` s ON o.shipp_id = s.id "
        +" INNER JOIN `supplier` su ON o.sup_id = su.id ";
ResultSet resultSet = statement.executeQuery(queryString);
ResultSetMetaData metaData = resultSet.getMetaData();
int colCount = metaData.getColumnCount();
if (resultSet.next()) {
    for (int i = 1; i <= colCount; i++) {
        System.out.println("Col(" + i + ") '" + metaData.getColumnName(i) + "' value:" + resultSet.getString(i));
    }
}else{
   System.out.println("No row found.");
}

This snippet will print first results the columns and values which are returned by the driver you are using.

Answer 2

Please check you have imported the correct jdbc connection package com.mysql.jdbc.connection. You could have imported java.sql.connection instead as here