theonlygusti theonlygusti - 4 months ago 19
Java Question

JDBC format ResultSet as tabular string?

When I query a database in the MySQL command-line interface I get some nice prettified tabular output:

mysql> select * from users;
+------------+--------------------------------------+------------+
| IGN | UUID | AdminLevel |
+------------+--------------------------------------+------------+
| GamerBoy | 0fcba4a5-f039-4771-ae22-bf203ed89c1c | 3 |
+------------+--------------------------------------+------------+


Now, in java I have to use a
ResultSet
to store the result of a query, and here is the code I currently have:

String query = String.join(" ", Arrays.copyOfRange(args, 2, args.length));
System.out.println("Querying the database with: " + query);
try {
Statement stmt = dbConnection.createStatement();
ResultSet rs = stmt.executeQuery(query);
System.out.println("Query parsed successfully!");
/* What need to go here? I want to output the result of the query
* in the same way that the MySQL tool would - some prettified String.
*/
} catch (SQLException exception) {
System.out.println("Query failed: " + exception.getMessage()) ;
}


How do I get a string containing the sort of output that MySQL would give me?




Basically, I want to make a java program which can query a database - the query can be different every time, it is user-chosen - and then outputs the result of the query in the neat tabular format that MySQL uses.

How can I do this?

Answer

You need to process the ResultSet object rs by yourself.

while(rs.next()){
  String ign = rs.getString("IGN");
  // ... get uuid and admin level
  System.out.println(ign + uuid ...);
}

Use ResultSet#getMetaData() if you dont know the column names:

ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount() ; // returns the number of columns
for(int i = 1; i < columnCount + 1; i++){
    rsmd.getColumnName(i);
}

Be aware that the columns are 1-indexed, that is, the first column name would be: rsmd.getColumnName(1);.

check Java8 ResultSetMetaData API