Java Return result of JOOQ query

Question

I have a question. Someone recently told me to use JOOQ if I interact with databases, so I have been reforming my code to work properly with JOOQ, but I am struggling with the way, how I select data from the database and return it. I have the following SqlConn class:

package com.company.database;

import org.jooq.DSLContext;
import org.jooq.Result;
import org.jooq.Record;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

import java.sql.*;
import java.util.HashMap;

public class SqlConn {

    private final String SERVER = "database.me:3306";
    private final String DATABASE = "testdb";
    private final String USERNAME = "test";
    private final String PASSWORD = "test";
    private final String URL = "jdbc:mysql://" + SERVER + "/" + DATABASE;
    

    public HashMap<String, String> getMarketCoins() {
        
        HashMap<String, String> returnValue = new HashMap<>();
        
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
            Result<Record> result = create.select().from("MarketCoins").fetch();

            for (Record r : result) {
                returnValue.put(r.getValue("Market").toString(), r.getValue("Coin").toString());
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        
        return returnValue;
    }
}

Now this code works like it should, but this is just a function that collects data from 1 specific table. My previous code was build that I could provide a Query and the code would execute the query and return the result, but with the new JOOQ method, it seems that I need to create a function for each different query and table. I watched this beginner tutorial about JOOQ: https://www.youtube.com/watch?v=4H3AGK_hNMA, but in this video he also creates multiple functions for different database calls. Is this correct, or is there a better way to easily get data from my database, where it doesn't matter for which table? I have a lot of tables in my database, so that would mean that I need a lot of function writing :(

Please let me know!

Lukas Eder Lukas Eder · Accepted Answer · 2021-03-10T15:25:26

As recommended in the tutorial you've linked, jOOQ is best used using the code generator. Your particular query can be implemented like this:

public Map<String, String> getMarketCoins() {
    return
    create().select(MARKET_COINS.MARKET, MARKET_COINS.COIN)
            .from(MARKET_COINS)
            .fetchMap(MARKET_COINS.MARKET, MARKET_COINS.COIN);
}

A few remarks:

MARKET_COINS is a generated class for your table by the same name. It contains type safe column information, such as MARKET_COINS.MARKET, of type Field<String>. Hence, the String type is already available to you. You don't have to do anything
I'm assuming you're going to use a connection pool (e.g hikari) and inject a DSLContext instance to your class (e.g. using Spring). You can still use DriverManager, but usually, you're better off with a connection pool. That is irrelevant to your jOOQ usage, though
Always specify explicit column lists in your SELECT clauses, never SELECT * if you're not going to process the extra columns.
jOOQ has a useful ResultQuery.fetchMap() method, so you don't have to loop your results manually
jOOQ is all about dynamic SQL. It should be simple to write queries in a way that you don't have to repeat these pattern, e.g. if you frequently have to fetch keys and values from a table:

public <R extends Record, K, V> Map<K, V> getKeyValue(
    TableField<R, K> key, TableField<R, V> value
) {
    return
    create().select(key, value)
            .from(key.getTable())
            .fetchMap(key, value);
}

Now, call this e.g. using:

Map<String, String> result = getKeyValue(MARKET_COINS.MARKET, MARKET_COINS.COIN);

This obviously greatly profits from the code generator usage, again.

Java Return result of JOOQ query

1 Answers