Static class for database access Java JDBC

Question

I'm writing a small program to store data in a database (a discord bot mainly). I know cleaner ways of using dependency injections and DAO exist, but I haven't written a DAO (or the user class) and don't know how to proceed.

With that, can anyone optimize my SQL calls? They look like a mess right now. I need to able to update the daily (a cash infusion once a day) and the rob calls (one theft once a day) in the same transaction. I also need to transfer money to savings as well as add or subtract money.

Here is ConnectionFactory, a class that manages connections through Hikari.

public class ConnectionFactory {
    private static HikariConfig config = new HikariConfig();
    private static HikariDataSource ds;

    static {
        config.setJdbcUrl("jdbc:h2:./data/database");
        config.setUsername("sa");
        config.setPassword("");
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        ds = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    private ConnectionFactory(){}
}

And here's my mess of a class - UserSQL. With lots of copy-paste code.

public class UserSQL {
    private static final String ADD_USER_SQL = "INSERT INTO Users(userid, money, savings, dailytime, rob) VALUES(?, ?, ?, ?, ?)";
    private static final String ADD_TICKETS_SQL = "INSERT INTO Lotto(userid, entries) VALUES(?)";

    private static final String GET_SQL = "SELECT * FROM Users WHERE userid = ?";
    private static final String GET_TICKETS_SQL = "SELECT * FROM Lotto WHERE userid = ?";
    private static final String UPDATE_MONEY_SQL = "UPDATE Users SET money = ? WHERE userid = ?";
    private static final String UPDATE_SAVINGS_SQL = "UPDATE Users SET savings = ? WHERE userid = ?";
    private static final String UPDATE_DAILY_SQL = "UPDATE Users SET dailytime = ? WHERE userid = ?";
    private static final String UPDATE_ROB_SQL = "UPDATE Users SET rob = ? WHERE userid = ?";

    public static boolean isUserInDatabase(String userID) {
        try (Connection c = ConnectionFactory.getConnection();
             PreparedStatement pstmt = c.prepareStatement(GET_SQL)) {
            pstmt.setString(1, userID);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            throw new AssertionError(e);
        }
    }

    public static long getMoney(String userID) {
        try (Connection c = ConnectionFactory.getConnection();
             PreparedStatement pstmt = c.prepareStatement(GET_SQL)) {
            pstmt.setString(1, userID);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong("money");
            } else {
                initUser(userID, c);
                return 0;
            }
        } catch (SQLException e) {
            throw new AssertionError(e);
        }
    }

    public static long getSavings(String userID) {
        try (Connection c = ConnectionFactory.getConnection();
             PreparedStatement pstmt = c.prepareStatement(GET_SQL)) {
            pstmt.setString(1, userID);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong("savings");
            } else {
                initUser(userID, c);
                return 0;
            }
        } catch (SQLException e) {
            throw new AssertionError(e);
        }
    }

    private static void initUser(String userID, Connection c) throws SQLException {
        try (PreparedStatement pstmt = c.prepareStatement(ADD_USER_SQL);
             PreparedStatement lstmt = c.prepareStatement(ADD_TICKETS_SQL)) {
            pstmt.setString(1, userID);
            pstmt.setLong(2, 1000);
            pstmt.setLong(3, 0);
            pstmt.setObject(4, LocalDateTime.MIN);
            pstmt.setObject(5, LocalDateTime.MIN);
            pstmt.executeUpdate();
            lstmt.setString(1, userID);
            lstmt.setInt(2, 0);
            lstmt.executeUpdate();
        }
    }

    public static void setMoney(String userID, long money) {
        try (Connection c = ConnectionFactory.getConnection();
             PreparedStatement pstmt = c.prepareStatement(UPDATE_MONEY_SQL)) {
            if (!isUserInDatabase(userID)) {
                initUser(userID, c);
            }
            pstmt.setLong(1, money);
            pstmt.setString(2, userID);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new AssertionError(e);
        }
    }

    public static void setMoneyAndDaily(String userID, long money) {
        try (Connection c = ConnectionFactory.getConnection();
             PreparedStatement mstmt = c.prepareStatement(UPDATE_MONEY_SQL);
             PreparedStatement dstmt = c.prepareStatement(UPDATE_DAILY_SQL)) {
            c.setAutoCommit(false);
            if (!isUserInDatabase(userID)) {
                initUser(userID, c);
            }
            mstmt.setLong(1, money);
            mstmt.setString(2, userID);
            mstmt.executeUpdate();

            dstmt.setObject(1, LocalDateTime.now());
            dstmt.setString(2, userID);
            dstmt.executeUpdate();
            c.commit();
        } catch (SQLException e) {
            throw new AssertionError(e);
        }
    }

    public static void transferSavings(String userID, long money) {
        try (Connection c = ConnectionFactory.getConnection();
             PreparedStatement mstmt = c.prepareStatement(UPDATE_MONEY_SQL);
             PreparedStatement sstmt = c.prepareStatement(UPDATE_SAVINGS_SQL)) {
            c.setAutoCommit(false);

            long orig = getMoney(userID);
            mstmt.setLong(1, orig - money);
            mstmt.setString(2, userID);
            mstmt.executeUpdate();

            long savings = getSavings(userID);
            sstmt.setLong(1, savings + money);
            sstmt.setString(2, userID);
            sstmt.executeUpdate();
            c.commit();
        } catch (SQLException e) {
            throw new AssertionError(e);
        }
    }

    public static void robMoney(String thiefID, String victimID, long robAmt) {
        try (Connection c = ConnectionFactory.getConnection();
             PreparedStatement tstmt = c.prepareStatement(UPDATE_MONEY_SQL);
             PreparedStatement vstmt = c.prepareStatement(UPDATE_MONEY_SQL);
             PreparedStatement rstmt = c.prepareStatement(UPDATE_ROB_SQL)) {
            c.setAutoCommit(false);

            long thiefOriginal = getMoney(thiefID);
            tstmt.setLong(1, thiefOriginal + robAmt);
            tstmt.setString(2, thiefID);
            tstmt.executeUpdate();

            long victimOriginal = getMoney(victimID);
            vstmt.setLong(1, victimOriginal + robAmt);
            vstmt.setString(2, victimID);
            vstmt.executeUpdate();

            rstmt.setObject(1, LocalDateTime.now());
            rstmt.setString(2, thiefID);
            rstmt.executeUpdate();
            c.commit();
        } catch (SQLException e) {
            throw new AssertionError(e);
        }
    }

    public static LocalDateTime getDailyTime(String userID) {
        try (Connection c = ConnectionFactory.getConnection();
             PreparedStatement pstmt = c.prepareStatement(GET_SQL);) {
            pstmt.setString(1, userID);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return ((Timestamp) rs.getObject("dailytime")).toLocalDateTime();
            } else {
                initUser(userID, c);
                return LocalDateTime.MIN;
            }

        } catch (SQLException e) {
            throw new AssertionError(e);
        }
    }

    public static LocalDateTime getRobTime(String userID) {
        try (Connection c = ConnectionFactory.getConnection();
             PreparedStatement pstmt = c.prepareStatement(GET_SQL)) {
            pstmt.setString(1, userID);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return ((Timestamp) rs.getObject("rob")).toLocalDateTime();
            } else {
                initUser(userID, c);
                return LocalDateTime.MIN;
            }

        } catch (SQLException e) {
            throw new AssertionError(e);
        }
    }

    public static int getTickets(String userID) {
        try (Connection c = ConnectionFactory.getConnection();
             PreparedStatement pstmt = c.prepareStatement(GET_TICKETS_SQL)) {
            pstmt.setString(1, userID);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("entries");
            } else {
                initUser(userID, c);
                return 0;
            }

        } catch (SQLException e) {
            throw new AssertionError(e);
        }
    }

    public static void setTickets(String userID, int tickets) {
        try (Connection c = ConnectionFactory.getConnection();
             PreparedStatement pstmt = c.prepareStatement(UPDATE_MONEY_SQL)) {
            if (!isUserInDatabase(userID)) {
                initUser(userID, c);
            }
            pstmt.setInt(1, tickets);
            pstmt.setString(2, userID);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new AssertionError(e);
        }
    }
}

Answer 1

The only repetition is the management of the connection and preparation of the statement. You can move the preparation and execution to some dedicated methods

ResultSet query(String statement, Object... parameters) {
    try {
        return prepare(statement, parameters).executeQuery();
    } catch (SQLException e) {
        throw new AssertionError(e);
    } 
}

PreparedStatement prepare(String statement, Object... parameters) {
    try (
        Connection c = ConnectionFactory.getConnection();
        PreparedStatement pstmt = c.prepareStatement(query)
    ) {
        for (int i=0; i<parameters.length; i++){
            setParameter(stmt, i+1, parameters[i]);
        }
        return pstmt;
    } catch (SQLException e) {
        throw new AssertionError(e);
    }        
}

void setParameter(PreparedStatement pst, int index, Object parameter) {
    Class<?> type = parameter.getClass();
    if ( Integer.class.equals(type) || Integer.TYPE.equals(type ) {
        pst.setInt(index, parameter);
    } else if ( .. ) {
      ...
    } else {
       pst.setString(index, String.valueOf(parameter); 
    }
}

But you still have to manage the SQLException that can be thrown by the ResultSet so you can create another method that map a ResultSet but take care of the exceptions :

<T> T doWith(ResultSet rs, ResultSetMapper<ResultSet, T> mapper) {
    try {
        return mapper.apply(rs);
    } catch (SQLException e) {
        throw new AssertionError(e);
    }
}

So that, at the end, you should be able to end up with something like :

public static boolean isUserInDatabase(String userID) {
    return doWith(query(GET_SQL, userId), rs -> rs.next());
}

Warn the code has not been tested and the exception handling can be greatly improved.