Static class for database access Java JDBC
up vote
0
down vote
favorite
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);
}
}
}
java sql jdbc
asked 2 mins ago
QuyNguyen2013
1387
add a comment |
up vote
0
down vote
favorite
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);
}
}
}
java sql jdbc
asked 2 mins ago
QuyNguyen2013
1387
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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);
}
}
}
java sql jdbc
asked 2 mins ago
QuyNguyen2013
1387
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);
}
}
}
java sql jdbc
java sql jdbc
asked 2 mins ago
QuyNguyen2013
1387
asked 2 mins ago
QuyNguyen2013
1387
asked 2 mins ago
QuyNguyen2013
1387
asked 2 mins ago
QuyNguyen2013
1387
asked 2 mins ago
QuyNguyen2013
1387
1387
add a comment |
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Code Review Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f209041%2fstatic-class-for-database-access-java-jdbc%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
