Hello, I've been reading a lot of about JOOQ as well as hibernate and was
wondering if I could get some information on whether JOOQ can do what my
current application is doing with the regular MySQL JDCB.
I picked up an old project I started years ago and want to update it with
these new projects.
Currently my application is of a game, where it's always
updating/deleting/creating data when a user logs in the game <performs
whatever action in the game> then logs off. So the data is saved.
This is an example of my current saving of a character:
public void saveCharacter() {
int prevTransactionIsolation =
Connection.TRANSACTION_REPEATABLE_READ;
boolean prevAutoCommit = true;
Connection con = null;
try {
con = DatabaseManager.getConnection(DatabaseType.STATE);
prevTransactionIsolation = con.getTransactionIsolation();
prevAutoCommit = con.getAutoCommit();
con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
con.setAutoCommit(false);
updateDbAccount(con);
updateDbStats(con);
updateDbMapMemory(con);
updateDbInventory(con);
updateDbSkills(con);
updateDbCooldowns(con);
updateDbBindings(con);
updateDbBuddies(con);
updateDbParty(con);
updateDbGuilds(con);
updateDbQuests(con);
updateDbMinigameStats(con);
updateDbFameLog(con);
saveRbReqToDb(con);
con.commit();
} catch (Throwable ex) {
LOG.log(Level.WARNING, "Could not save character " +
getDataId() + ". Rolling back all changes...", ex);
if (con != null) {
try {
con.rollback();
} catch (SQLException ex2) {
LOG.log(Level.WARNING, "Error rolling back character.",
ex2);
}
}
} finally {
if (con != null) {
try {
con.setAutoCommit(prevAutoCommit);
con.setTransactionIsolation(prevTransactionIsolation);
} catch (SQLException ex) {
LOG.log(Level.WARNING, "Could not reset Connection
config after saving character " + getDataId(), ex);
}
}
DatabaseManager.cleanup(DatabaseType.STATE, null, null, con);
}
}
within this here are two functions for a better example:
Updating the the account
private void updateDbAccount(Connection con) throws SQLException {
PreparedStatement ps = null;
try {
ps = con.prepareStatement("UPDATE `accounts` SET `slots` = ?,
`money` = ?, `points` = ?, `votes` = ? WHERE `id` = ?");
ps.setShort(1, storage.getSlots());
ps.setInt(2, storage.getMoney());
ps.setInt(3, points);
ps.setInt(4, votes);
ps.setInt(7, client.getAccountId());
ps.executeUpdate();
} catch (SQLException e) {
throw new SQLException("Failed to save account-info of
character " + name, e);
} finally {
DatabaseManager.cleanup(DatabaseType.STATE, null, ps, null);
}
}
and another one with batch statements and mapping
private void updateDbCooldowns(Connection con) throws SQLException {
PreparedStatement ps = null;
try {
ps = con.prepareStatement("DELETE FROM `cooldowns` WHERE
`characterid` = ?");
ps.setInt(1, getDataId());
ps.executeUpdate();
ps.close();
ps = con.prepareStatement("INSERT INTO `cooldowns`
(`characterid`,`skillid`,`remaining`) VALUES (?,?,?)");
ps.setInt(1, getDataId());
for (final Entry<Integer, Cooldown> cooling :
cooldowns.entrySet()) {
ps.setInt(2, cooling.getKey().intValue());
ps.setShort(3, cooling.getValue().getSecondsRemaining());
ps.addBatch();
}
ps.executeBatch();
} catch (SQLException e) {
throw new SQLException("Failed to save cooldowns of character "
+ name, e);
} finally {
DatabaseManager.cleanup(DatabaseType.STATE, null, ps, null);
}
}
All these datas are for saving the character only...
For loading the player, here is a snippet code (kinda)
public static Player load(Client c, int id) {
Connection con = null;
PreparedStatement ps = null, ips = null;
ResultSet rs = null, irs = null;
try {
con = DatabaseManager.getConnection(DatabaseType.STATE);
ps = con.prepareStatement("SELECT
`c`.*,`a`.`name`,`a`.`slots`,`a`.`money`,`a`.`points`,`a`.`votes`"
+ "FROM `characters` `c` LEFT JOIN `accounts` `a` ON
`c`.`accountid` = `a`.`id` "
+ "WHERE `c`.`id` = ?");
ps.setInt(1, id);
rs = ps.executeQuery();
if (!rs.next()) {
return null;
}
int accountid = rs.getInt(1);
c.setAccountId(accountid);
byte world = rs.getByte(2);
if (world != c.getWorld()) {
return null;
}
Player p = new Player();
p.client = c;
p.loadStats(rs, id);
p.maxHp = p.baseMaxHp;
p.maxMp = p.baseMaxMp;
p.mesos = rs.getInt(26);
p.buddies = new BuddyList(rs.getShort(32));
p.level = rs.getInt(42);
....
rs.close();
ps.close();
ps = con.prepareStatement("SELECT `key`,`value`,`spawnpoint`
FROM `mapmemory` WHERE `characterid` = ?");
ps.setInt(1, id);
rs = ps.executeQuery();
while (rs.next()) {
p.rememberedMaps.put(MapMemoryVariable.valueOf(rs.getString(1)), new
Pair<Integer, Byte>(Integer.valueOf(rs.getInt(2)),
Byte.valueOf(rs.getByte(3))));
}
rs.close();
ps.close();
.....
} catch (SQLException ex) {
LOG.log(Level.WARNING, "Could not load character " + id + "
from database", ex);
return null;
} finally {
DatabaseManager.cleanup(DatabaseType.STATE, irs, ips, null);
DatabaseManager.cleanup(DatabaseType.STATE, rs, ps, con);
}
}
I'm sorry I know this is a lot of code to read...
So would I be able to achieve all this efficiently with JOOQ?
Would it be best in this case to use both JOOQ and Hibernate? Or would
sticking with JOOQ be suffice enough for my needs based on the code above.
Can JOOQ work with netbeans?
I want to be able to easily write quries as well and I from what I read
JOOQ does this well. The transition will be tough but if it'll fit with the
code I posted as well as my other needs, i'd be really excited to implement
it!
Thanks a lot for those who made it down here! I appreciate it. Really hope
posting code on this post isn't against the rules or something, I just
wanted to give a general example of the kinda queries and data i'm working
with on my game. Thanks!
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
