New to JOOQ, Looking for guidance!
87 views
Skip to first unread message
xragons
Apr 28, 2018, 2:44:22 AM4/28/18
to jOOQ User Group
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.
within this here are two functions for a better example:
All these datas are for saving the character only...
I'm sorry I know this is a lot of code to read...
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); } }
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); } }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); } }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!
Lukas Eder
Apr 30, 2018, 3:33:19 AM4/30/18
to jooq...@googlegroups.com
Hello,
I'll comment inline.
2018-04-28 8:44 GMT+02:00 xragons <shabbar...@outlook.com>:
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.
Yes, everything you're doing with JDBC can be done with jOOQ.
So would I be able to achieve all this efficiently with JOOQ?
Yes
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.
Your code can be migrated very easily to jOOQ as your code is SQL centric and so is jOOQ (this is mostly the case with JDBC based applications). The main differences between JDBC and jOOQ are listed here:
The most immediate effect is that your code will be a bit less verbose and type safe.
From what I've seen, Hibernate could also be an option, but would require quite a bit of refactoring. If you've never used Hibernate, you'll have to invest a bit of time in getting it right as well, or your performance might degrade at first.
Can JOOQ work with netbeans?
I don't see why not.
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
I really don't think there's anything tough about transitioning from JDBC-based applications to jOOQ based ones. It's a routine piece of work.
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,
There are no such rules :) Be our guest and welcome to the community!
xragons
May 2, 2018, 10:59:13 PM5/2/18
to jOOQ User Group
Hey Lukas, thanks for the reply!
And this is my attempted conversion:
I've already implemented JOOQ with HikariCP and i'm loving it! Just a few concerns and guidance on a few things. Would love your input to see if i'm doing this the right and efficient way.
This is the original method:
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);
}
}
And this is my attempted conversion:
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?
Those are all the concerns I have for now haha, thanks a bunch!!!
Lukas Eder
May 3, 2018, 3:28:27 AM5/3/18
to jooq...@googlegroups.com
2018-05-03 4:59 GMT+02:00 xragons <shabbar...@outlook.com>:
Does this look right and efficient or Is there a better way to do this?
Yes, that looks about right
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?
jOOQ manages resources for you. For a list of differences between jOOQ and JDBC, see this page here:
I think I'm going to improve that page with actual examples:
Cheers,
Lukas
xragons
May 3, 2018, 4:48:24 AM5/3/18
to jOOQ User Group
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.
here is my BookClass that extends the abstract class posted above:
here is the loading values from the DB (which works fine), it returns the DB value for ID, Name to the variable.
But, when I try to save/update It's not performing any queries. here is the method:
It's not saving the newly assigned values to the DB. But if use this class instead without extending any abstract class:
It'll perform the update.
So I've been trying load custom POJOs per-say back into records to store them, but it's not working as expected for abstract classes for some reason.
Here is my abstract class :
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
}
here is the loading values from the DB (which works fine), it returns the DB value for ID, Name to the variable.
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));
}
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?
Thanks!
Lukas Eder
May 3, 2018, 4:54:12 AM5/3/18
to jooq...@googlegroups.com
2018-05-03 10:48 GMT+02:00 xragons <shabbar...@outlook.com>:
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.
If in doubt, create a new discussion. We won't run out of discussion IDs anytime soon :)
(it'll be easier for future visitors to track down what has really been discussed)
But, when I try to save/update It's not performing any queries. here is the method:
// 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 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!
Interesting. Will look into this soon. There shouldn't be any difference. Have you debug-stepped through the update to see what's going on?
Also on a side note, does JOOQ have a public community discord? If not any plans for one?
I don't know what that is - care to enlighten me?
xragons
May 3, 2018, 5:09:29 AM5/3/18
to jOOQ User Group
The updating isn't even going through.
when I replace this
BooksRecord 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.
Regards~
Lukas Eder
May 3, 2018, 5:18:11 AM5/3/18
to jooq...@googlegroups.com
2018-05-03 11:09 GMT+02:00 xragons <shabbar...@outlook.com>:
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?
The latter doesn't really make any sense - what would be the meaning of loading a Class reference into a jOOQ Record?
Do understand that when loading a POJO into a Record using the syntax you're using, behind the scenes, the DefaultRecordUnmapper is applied to your POJO to produce a Record:
It uses reflection to map between getters (and other convention) and record field names. The specification is in the Javadoc. After unmapping, all the Record.changed() flags are set to true, which influences the semantics of an UpdatableRecord.update() call, also according to the documentation.
Now, there might be a bug in the DefaultRecordUnmapper logic, which I will need to investigate...
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.
Oh I see - I wasn't aware that it is a product name, thanks for the suggestion.
I don't think that a chat (regardless of the vendor) is an appropriate channel for community support. We have enterprise support subscriptions for customers who wish more timely responses on a 1:1 email basis. Public community support on this list is for asynchronous, "offline" support, which is provided on a best effort basis.
xragons
May 3, 2018, 5:33:25 AM5/3/18
to jOOQ User Group
Yeap, I understand. It truly is great effort from your part. Working on the code base and helping others is truly humble of you. I've seen Stack overflow posts of you helping discussing with others years back. The dedication is fascinating.
Also, I forgot to mention its not only for the executeUpdate(), even store() and excuteInsert don't work in my case.
Thanks.
Lukas Eder
May 4, 2018, 5:14:24 AM5/4/18
to jooq...@googlegroups.com
I've tried reproducing this on my side, without any success. The following two integration tests work. Would it be possible to provide an MVCE (https://stackoverflow.com/help/mcve), preferably as a minimal Maven project that contains all the relevant classes. Perhaps, when you showed the examples in this discussion, some important detail got missing...
Working tests:
static class BaseAuthorNoGetters {
int id;
}
static class SubAuthorNoGetters extends BaseAuthorNoGetters {
String lastName;
}
public void testInsertWithRecordFromPojoWithInheritanceNoGetters() throws Exception {
jOOQAbstractTest.reset = false;
SubAuthorNoGetters author = new SubAuthorNoGetters();
author.id = 3;
author.lastName = "A";
assertEquals(1, create().newRecord(TAuthor(), author).store());
assertEquals("A", create()
.select(TAuthor_LAST_NAME())
.from(TAuthor())
.where(TAuthor_ID().eq(3))
.fetchOne(TAuthor_LAST_NAME()));
author.lastName = "B";
assertEquals(1, create().executeUpdate(create().newRecord(TAuthor(), author)));
assertEquals("B", create()
.select(TAuthor_LAST_NAME())
.from(TAuthor())
.where(TAuthor_ID().eq(3))
.fetchOne(TAuthor_LAST_NAME()));
}
static class BaseAuthorWithGetters {
int id;
public int getId() {
return id;
}
}
static class SubAuthorWithGetters extends BaseAuthorWithGetters {
String lastName;
public String getLastName() {
return lastName;
}
}
public void testInsertWithRecordFromPojoWithInheritanceWithGetters() throws Exception {
jOOQAbstractTest.reset = false;
SubAuthorWithGetters author = new SubAuthorWithGetters();
author.id = 3;
author.lastName = "A";
assertEquals(1, create().newRecord(TAuthor(), author).store());
assertEquals("A", create()
.select(TAuthor_LAST_NAME())
.from(TAuthor())
.where(TAuthor_ID().eq(3))
.fetchOne(TAuthor_LAST_NAME()));
author.lastName = "B";
assertEquals(1, create().executeUpdate(create().newRecord(TAuthor(), author)));
assertEquals("B", create()
.select(TAuthor_LAST_NAME())
.from(TAuthor())
.where(TAuthor_ID().eq(3))
.fetchOne(TAuthor_LAST_NAME()));
}
--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.
xragons
May 4, 2018, 6:43:45 AM5/4/18
to jOOQ User Group
I've created a mock project and was able to reproduce the issue, I've uploaded it here: http://www.mediafire.com/file/qye9c2hq6yee6lf/PojoMapping.rar this contains the generated classes from the DB. I also incuded the actual SQL file (with only 3 tables). the pom files has the required libs as well.
Thanks.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
xragons
May 8, 2018, 5:14:17 AM5/8/18
to jOOQ User Group
Hmm, I've also tried using another table and record, same issue seems to occur.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
Lukas Eder
May 9, 2018, 5:14:33 AM5/9/18
to jooq...@googlegroups.com
Thanks for those examples. I'm confused:
public static abstract class MyBook {
protected static int id;
protected static String name;
public static int getId() {
public static String getName() {
return name;
}
public void setName(String title) {
this.name = title;
}
}
Why are there any static members and methods in that class? That doesn't make too much sense. You seem to have omitted this detail in the previous emails, but given that you've added these modifiers in your example code, I do think there's simply a misunderstanding about how the Java language works...
xragons
May 9, 2018, 6:03:24 AM5/9/18
to jOOQ User Group
Oh wow... that's embarrassing.. Well that explain's and solves it.
Thanks for keepin up with me!
Lukas Eder
May 11, 2018, 4:04:25 AM5/11/18
to jooq...@googlegroups.com
Hey, no worries :) It happens to the best.
Reply all
Reply to author
Forward
0 new messages