Hi Shekhar,
Sorry about the misunderstanding, my bad. So here are some examples
about how to do these things. They're examples, I didn't check for
syntax correctness:
> 1. select count(GAME_ID)+1 AS 'MAXSCORE_ID' from GAME
// On a single line
int maxscoreID = create
// Note, you don't need to alias this field...
.select(GAME_ID.count().add(1).as("MAXSCORE_ID"))
.from(GAME)
.fetchOne()
// Access by index or field name:
.getValueAsInteger(0);
// On several lines
// Create a reference to the count field
Field<Integer> count = GAME_ID.count().add(1).as("MAXSCORE_ID");
Record record = create
.select(count)
.from(GAME)
.fetchOne();
// Use the count reference to get data from the record
int maxscoreID = record.getValue(count);
There are many more ways to do this...
I will add some more examples to the documentation page. The object-
oriented syntax (GAME_ID.count() instead of count(GAME_ID)) might not
be very intuitive at first...
https://sourceforge.net/apps/trac/jooq/ticket/335
> 2. update GAME set END_TIME=now(),
> PLAY_TIME=TIMESTAMPDIFF(SECOND,START_TIME,END_TIME) where
> GAME_ID="100001"
Currently, jOOQ does not support all of MySQL's (or other RDBMS's non-
standard functions). Right now, you will have to use the
Factory.plainSQLField() methods to achieve what you're doing.
Check out https://sourceforge.net/apps/trac/jooq/wiki/Manual/DSL/SQL
It will look like this:
UpdateQuery<Game> update = create.updateQuery(GAME);
// equivalent to MySQL's NOW()
update.addValue(END_TIME, create.currentTimestamp());
// You can always create plain SQL
update.addValue(PLAY_TIME, create.plainSQLField("TIMESTAMPDIFF(SECOND,
START_TIME, END_TIME)"));
update.addConditions(GAME_ID.equal(100001));
update.execute();
Your use case is an interesting one, though. I will schedule adding
more datetime manipulation functions for the next release:
https://sourceforge.net/apps/trac/jooq/ticket/336
If you see other functionality missing from jOOQ, from the MySQL
perspective, feel free to tell me!
Cheers
Lukas
