Select prefixed columns from multiple tables in Java with MySQL

Question

I have to deal with Java to create a restful API and I'm using it with MySQL to query the DB.

I'm quite new to Java and this might be a very basic question, but used to PHP frameworks where this problem doesn't exist and I was wondering what's the way to do it in Java.

I'm querying 3 tables joining them by their foreign keys and I found out that if two of those tables has the same column name, only one of them will be returned. The one from the latest table declared in the from statement. It seems they get override.

For example, if the tables type_application and type_leaves have both a name column, only the name column from type_leaves will be returned in this query:

select * from leaves, type_applications, type_leaves 
    where leaves.type_leave = type_leaves.id and 
    leaves.type_application = type_applications.id;

Now, I know this can easily be solved by specifying all the needed column names prefixed with the table name (in case they are duplicated) and using as to create an alias name:

select leaves.id, type_leaves.name as leaves_name, type_applications.name as application_name
    from leaves, type_applications, type_leaves 
    where leaves.type_leave = type_leaves.id and 
    leaves.type_application = type_applications.id;

But this doesn't sound like the best solution for me. I would rather keep using the * in order to get all fields (which is what I will always want).

This will also help to have an smaller query, easier to read and easier to maintain in case I keep adding or deleting columns in the table with the time.

Any solution for this? What's the way to deal with it in Java?

Answer 1

I am new to Java too, so I hear you ;). But before I try my best to give you an answer, I want to say a word if you don't mind. Your question is related to Java and databases/SQL. You provided some SQL which explains few things about your DB tables and what you may get from the query. But your main problem is with Java and you did not provide any Java code so that we can understand better what you are trying to accomplish and where exactly you are experiencing problems. That being said, I hope the following will give you an idea about what you can do:

First of all, the ResultSet from your query contains both name columns. They are not overwritten. Here is an example:

String sql = "select * from leaves, type_applications, type_leaves " +
                "where leaves.type_leave = type_leaves.id and " +
                "leaves.type_application = type_applications.id";

ResultSet rs = stmt.executeQuery(sql);
DBTablePrinter.printResultSet(rs);

This will print something like this:

Printing 1 rows from table(s) LEAVES, TYPE_APPLICATIONS, TYPE_LEAVES
+----+------------------------+------------+------------------+----+--------+
| ID | SOMETHING_ABOUT_LEAVES | ID |        NAME        | ID |     NAME     |
+----+------------------------+----+--------------------+----+--------------+
|  1 | green                  |  1 | application type 1 |  1 | leave type 1 |
+----+------------------------+----+--------------------+----+--------------+

As can be seen, both name columns are there. (I used DBTablePrinter utility class that I wrote. You can find it here if you are interested). I would also suggest, as Strawberry did in the comments, to consider using explicit join syntax.

I am using an other DB (H2) so I am not sure if this will work with MySQL but you can try (it worked for me):

// After executing the query
rs.next();
System.out.println(rs.getString("TYPE_LEAVES.NAME"));
System.out.println(rs.getString("TYPE_APPLICATIONS.NAME"));

// Prints:
// leave type 1
// application type 1

If this doesn't work or you really need to prefix the column names and access them with these new names, all I can think of is something like this:

ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();

// A HashMap with column names as key
Map<String, List<String>> columns = new HashMap<>(columnCount);

// Loop through columns, rename as you wish
for (int i = 1; i <= columnCount; i++) {
    if (rsmd.getColumnLabel(i).equals("NAME")) {
        if (rsmd.getTableName(i).equals("TYPE_LEAVES")) {
            columns.put("L_NAME", new ArrayList<>());
        } else {
            columns.put("APP_NAME", new ArrayList<>());
        }
    } else {
        columns.put(rsmd.getColumnLabel(i), new ArrayList<>());
    }
}

// Iterate over ResultSet rows, add values to columns
while (rs.next()) {
    for (int i = 1; i <= columnCount; i++) {
        String columnName = rsmd.getColumnLabel(i);
        String tableName = rsmd.getTableName(i);
        if (columnName.equals("NAME")) {
            if (tableName.equals("TYPE_LEAVES")) {
                columns.get("L_NAME").add(rs.getString(i));
            } else {
                columns.get("APP_NAME").add(rs.getString(i));
            }
        } else {
            columns.get(columnName).add(rs.getString(i))
        }
    }
}

// Print, for example, first row values for L_NAME and APP_NAME columns
System.out.println(columns.get("L_NAME").get(0));
System.out.println(columns.get("APP_NAME").get(0));

// Prints:
// leave type 1
// application type 1

Answer 2

you could write a function that will return the index of the column, based on both table name and column name

public int getIndex(ResultSet rs, String table, String column) throws SQLException
{
    for (int i=1; i < rs.getMetaData().getColumnCount(); i++)
    {
        if (rs.getMetaData().getTableName(i).equals(table) && rs.getMetaData().getColumnName(i).equals(column))
        {
            return i;
        }
    }
    return -1;
}

And use it like that :

// TODO check if getIndex returns -1
// You can use the appropriate getter off course
System.out.println(rs.getObject(getIndex(rs, "type_application", "name")));
System.out.println(rs.getObject(getIndex(rs, "type_leaves", "name")));

Answer 3

Try to get your missing column explicitly like below:

select *,type_applications.name as type_applications_name
  from leaves, type_applications, type_leaves 
    where leaves.type_leave = type_leaves.id and 
    leaves.type_application = type_applications.id;

1. Anyway, using cross-joins is not encouraged.
2. Using select * is not encouraged.

But if you want select * anyway, try to use a table to wrap the results:

select * from (
select leaves.*, type_applications.*, type_leaves.* ,type_applications.name as type_applications_name
  from leaves, type_applications, type_leaves 
    where leaves.type_leave = type_leaves.id and 
    leaves.type_application = type_applications.id;
)

PS: I haven't tested the code, so there can be typing mistakes, so try to get the idea only.

Answer 4

"select *" is really a bad practice in most serious cases because of performance, I think it is the same even in PHP.

And furthermore, the column is returned correctly even you use "select *", you can use result.getMetaData() to check it.

for (int i=1; i<rs.getMetaData().getColumnCount(); i++) {
    System.out.println(rs.getMetaData().getColumnName(i));
}

In java world, ResultSet has getXXXXX(String columnName) methods, so all column names must be unique in a result.But you can also use getXXXX(int columnIdx) to fetch all columns

Answer 5

In this scenario, one of the columns might be getting renamed.

To work around this issue, you can query list of columns from the Result Set returned by executeQuery (ResultSet.getMetaData method) . That will return an instance of http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html

1. getColumnCount() will return number of columns in result
2. getColumnLabel(int column) will return name of that column

If two columns have same name "name", labels might be different for those two columns.