theonlygusti theonlygusti - 4 months ago 14
MySQL Question

JDCB 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 ResultSet object rs by yourself.

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

If you dont know the column names, you can retrieve them based on MetaData

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

Be aware 1st column name is starting from 1 other than 0

check Java8 ResultSetMetaData API