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); } }
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); } }
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); } }
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); } }
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.
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
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,
private static void loadUser(Client c) {
ArrayList<LoginUser> players = new ArrayList<>(c.getCharacterSlots());
final String sql = "SELECT `id` FROM `characters` WHERE `accountid` = ? AND `world` = ?";
try (
Connection con = Database.connection(); // JDBC
PreparedStatement ps = con.prepareStatement(sql);) {
ps.setInt(1, c.getAccountId());
ps.setInt(2, c.getWorld());
try (ResultSet rs = ps.executeQuery();) {
while (rs.next()) {
players.add(LoginUser.load(c, rs.getInt(1)));
}
}
// .... further source code
} catch (SQLException ex) {
LOG.log(Level.WARNING, "Could not load characters of account " + c.getAccountId(), ex);
} finally {
con.dispose(con, ps, rs);
}
}
private static void loadUser(Client c) {
ArrayList<LoginUser> players = new ArrayList<>(c.getCharacterSlots());
final DataSource ds = connection(); // hikari connection pool
final DSLContext ctx = DSL.using(ds, SQLDialect.MYSQL_8_0);
for (Record record : ctx.select().from(CHARACTERS)
.where(CHARACTERS.ACCOUNTID.contains(c.getAccountId()))
.and(CHARACTERS.WORLD.contains(c.getWorld()))
.fetch())
{
players.add(LoginUser.load(c, record.get(CHARACTERS.ID)));
}
//... further source code
}
Does this look right and efficient or Is there a better way to do this?
My 2nd concern is, about try/catch and closing connections. As you can see in the first example, I'm using try catch blocks AND i'm also manually closing all the open statements. In the 2nd example, if I don't include a try/catch would it still close the opened connections?I think I was looking at one of your tutorials on github and saw something similar. Furthermore, is a try/catch/closing statements needed for other queries as well? (update, delete, etc) or does JOOQ handle those too?
public abstract class MyBook {
public int id;
protected String name;
public int getId() {
return id;
}
public void setId(int newId) {
this.id = newId;
}
public String getName() {
return name;
}
public void setName(String newName) {
this.name = newName;
}
}
public class BookClass extends MyBook {
// load, save here
}
public static void loadFromDB() {
// init connection and DSL context
DataSource dataSource = connection();
DSLContext create = DSL.using(dataSource, SQLDialect.MYSQL_8_0);
// check to see what current values for MyBook class are before mapping values from the db
print("un mapped id: " + BookClass.getId() + " un mapped name: " + BookClass.getName());
// The various "into()" methods allow for fetching records into your custom POJOs:
// MyBook myBook = create.select(BOOKS.ID, BOOKS.NAME).from(BOOKS).fetchAny().into(BookClass.class);
MyBook myBook = create.select().from(BOOKS).fetchAny().into(BookClass.class);
print("mapped id: " + BookClass.getId() + " mapped name: " + BookClass.getName());
}
public void save() {
// init connection and DSL context
DataSource dataSource = connection();
DSLContext create = DSL.using(dataSource, SQLDialect.MYSQL_8_0);
// create a new POJO instance
MyBook myBook = new BookClass();
myBook.id = 1;
myBook.name = "Tom";
// Loading a JOOQ-generated BookRecord from POJO
BooksRecord book = create.newRecord(BOOKS, myBook);
// we just want to update the already existing name
create.executeUpdate(book);
// check to see if changes were successful.
print("id = "+ BookClass.getId() + " name = " + BookClass.getName());
print(create.executeUpdate(book));
}
public class TestBook {
public int id;
public String name;
}
Awesome. Not sure if I should make a different post since it's kinda a different issue. But i'll post it here for now.
But, when I try to save/update It's not performing any queries. here is the method:
public void save() {
// init connection and DSL context
DataSource dataSource = connection();
DSLContext create = DSL.using(dataSource, SQLDialect.MYSQL_8_0);
// create a new POJO instance
MyBook myBook = new BookClass();
myBook.id = 1;
myBook.name = "Tom";
// Loading a JOOQ-generated BookRecord from POJO
BooksRecord book = create.newRecord(BOOKS, myBook);
// we just want to update the already existing name
create.executeUpdate(book);
// check to see if changes were successful.
print("id = "+ BookClass.getId() + " name = " + BookClass.getName());
print(create.executeUpdate(book));
}
It's not saving the newly assigned values to the DB. But if use this class instead without extending any abstract class:
public class TestBook {
public int id;
public String name;
}
It'll perform the update.Been spending a few hours trying to figure this out, but i'm lost~ Any help would be much appreciated!
Also on a side note, does JOOQ have a public community discord? If not any plans for one?
BooksRecord book = create.newRecord(BOOKS, myBook);
BooksRecord book = create.newRecord(BOOKS, BookClass.class);
The updating isn't even going through.when I replace thisBooksRecord book = create.newRecord(BOOKS, myBook);
with this:BooksRecord book = create.newRecord(BOOKS, BookClass.class);
it'll update in the DB but with just the (BookClass.class) in string format. So I think the issue lies somewhere here?
Discord is basically like skype / teamspeak / msn. But on a larger scale. Business use it for communication, while gamers use it to stay in touch. You should check it out. It's free and highly customizable. Basically visitors or staff can post/discuss in real time. But that might go against what this whole group is for, but you should still look into it and see if it fits your style.
--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 jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.