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