How can I print the column names from 3 tables joining JDBC?

Question

I have a 3 tables I have joined this query executes and prints out the tables data.

try {
    Connection conn = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);
    System.out.println("Connected");
    Statement st = conn.createStatement();

    String query = "SELECT s.*, sup.name as supplierName , p.name as partName "+
                    "FROM supplies s "+
                    "INNER JOIN supplier sup on s.supplierNum = sup.supplierNum "+
                    "INNER JOIN parts p on s.partNum = p.partNum";

    ResultSet rs = st.executeQuery(query);

    while(rs.next()) {
        System.out.println(rs.getString("supplierNum"));
        System.out.println(rs.getString("partNum"));
        System.out.println(rs.getString("quantity"));
        System.out.println(rs.getString("supplierName"));
        System.out.println(rs.getString("partName"));
        space();
    }

} catch(Exception ex) {
    System.out.println(ex);
}

But I was trying to add the column names so instead of the console printing:

It would to print the column names cascaded

supplierNum: S1

partNum: P1

quantity: 300

name: Smith

part: Nut

You can try to read the column names from the ResultSet: this Thread should be helpful: https://stackoverflow.com/questions/696782/retrieve-column-names-from-java-sql-resultset — eckad158, Nov 29 '19 at 14:43
You already are passing column names to your `println()` calls. So print these strings as well. — PM 77-1, Nov 29 '19 at 14:44

score 1 · Answer 1 · answered Nov 29 '19 at 14:43

while(rs.next()) {
    System.out.println("Supplier Name " + rs.getString("supplierNum"));
    System.out.println("Part Name "+rs.getString("partNum"));
    System.out.println("Quantity "+ rs.getString("quantity"));
    System.out.println("SupplierName "+rs.getString("supplierName"));
    System.out.println("PartName "+rs.getString("partName"));
    space();
    }

score 1 · Accepted Answer · answered Nov 29 '19 at 14:48

1

As suggested in https://stackoverflow.com/a/696794/11226302

You need to get the ResultSet meta data to programmatically get your column names from db.

Else, you can manually enter the names as suggested in other answers.

answered Nov 29 '19 at 14:48

Shivam Puri

1,578
12
25

score 0 · Answer 3 · answered Nov 29 '19 at 14:51

You can of course hard-code the column names because you already know them.

If you want to get them programmatically, then use the ResultSetMetaData similar to this:

Connection connection = ...

try (PreparedStatement ps = connection.prepareStatement(QUERY)) {
    ResultSet resultSet = ps.executeQuery();
    // get the meta data from the result set
    ResultSetMetaData rsMeta = resultSet.getMetaData();
    // receive the column count
    int columnCount = rsMeta.getColumnCount();

    // iterate them (their indexes start at 1, I think)
    for (int i = 1; i < columnCount + 1; i++) {
        // and print the column name, the type and the type name
        System.out.println(rsMeta.getColumnName(i) 
                + " (" 
                + rsMeta.getColumnType(i)
                + ", "
                + rsMeta.getColumnTypeName(i)
                + ")");
    }
} catch ...
    ...
}

If you want to directly output the column in your while loop, then get the meta data before that loop

ResultSetMetaData rsMeta = rs.getMetaData();

and then, inside the loop do

System.out.println(rsMeta.getColumnName(1) + ": " + rs.getString("supplierNum"));
System.out.println(rsMeta.getColumnName(2) + ": " + rs.getString("partNum"));
System.out.println(rsMeta.getColumnName(3) + ": " + rs.getString("quantity"));
System.out.println(rsMeta.getColumnName(4) + ": " + rs.getString("supplierName"));
System.out.println(rsMeta.getColumnName(5) + ": " + rs.getString("partName"));

@ArvindKumarAvinash sure it doesn't make sense like this, but it shows how to use the `ResultMetaData`... One would regularly loop through the column names, I think. — deHaar, Nov 29 '19 at 15:44

score 0 · Answer 4 · answered Nov 29 '19 at 14:58

From a resultSet you can optain his metadata

ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
for (int i = 1; i <= cols; i++) {
  String colName = meta.getColumnName(i);
  System.out.printf("%s=%s\n", colName, rs.getString(i);
  ...
}

How can I print the column names from 3 tables joining JDBC?

4 Answers4