3
\$\begingroup\$

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);
        }
    }
}
\$\endgroup\$
  • \$\begingroup\$ do you have the option to use libraries, like jooq? \$\endgroup\$ – Martin Frank Dec 5 '18 at 7:22
  • \$\begingroup\$ @frank Yes, but not too sure what is the best \$\endgroup\$ – QuyNguyen2013 Dec 5 '18 at 12:35
  • \$\begingroup\$ i used jooq for several times, since it is easy and under maintaince... maybe you'll have a look... i don't want to make any advertisment, i'm just very satisfied.... \$\endgroup\$ – Martin Frank Dec 6 '18 at 6:09
1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.