Query about Count , Max

[Shekhar]

I have a mysql table "GAME" with structure as

CREATE TABLE GAME(
"GAME_ID" VARCHAR(20) PRIMARY KEY,
"GAME_START" TIMESTAMP,
"GAME_END" TIMESTAMP,
"PLAY_TIME" DECIMAL(20,0)
)

I am not able to perform 2 tasks

Now at the time of new record, i want GAME_ID to auto populate to
the max+1 i.e MAX(GAME_ID)+1 or COUNT(GAME_ID)+1 .


Also, i am creating the new record at first and then updating the
record at a later stage when the GAME is completed. At that time, i am
updating GAME_END TIMESTAMP. Is there anyway, i can also update the
PLAY_TIME = GAME_START-GAME_END (in seconds)--

Ravi.Shekhar
--------------------------------------------

[Lukas Eder]

Hi Shekhar,

I have answered this question here:
http://groups.google.com/group/jooq-user/browse_thread/thread/4b43fd7f23e3b7f2

Cheers
Lukas

[Shekhar]

I am really sorry if you get the wrong idea. I do not want you to make
SQL queries. I know how to do that. I explained the table just so you
can understand the table schema.

What i am having problem is with converting the SQL queries into Jooq

Here are the two queries

1. select count(GAME_ID)+1 AS 'MAXSCORE_ID' from GAME


2. update GAME set END_TIME=now(),
PLAY_TIME=TIMESTAMPDIFF(SECOND,START_TIME,END_TIME) where
GAME_ID="100001"

[Lukas Eder]

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

[Lukas Eder]

Besides, of course, you can also use the MAX function to get a more accurate behaviour:

int maxscoreID = create
  .select(GAME_ID.max().add(1).as("MAXSCORE_ID"))

  .from(GAME)
  .fetchOne()
  .getValueAsInteger(0);

[Shekhar]

Thank you Lukas...

I appreciate your quick and prompt reply.
I will surely check the jOOQ statements and get back to you id there
is still some doubt.

And yes, i will ask for help on this user-group or on stack-
overflow.com (as per what kind of help i need).

BTW, i think that jOOQ is a pretty good and lightweight as compare to
big-shots. Keep the good work going.

[Lukas Eder]

BTW, i think that jOOQ is a pretty good and lightweight as compare to
big-shots. Keep the good work going.

Thanks for your input Shekhar. The more feedback I get, the better I can make this tool.
If you want jOOQ to advance even more in a good direction, you can help by blogging about it. Also, if you add references to jOOQ in your stackoverflow questions (or use the jooq tag), this will attract some attention. Of course, I will also answer your questions on stackoverflow :-)

Cheers
Lukas

[Shekhar]

I am still having some doubts about the query -

update GAME set END_TIME=now(),
PLAY_TIME=TIMESTAMPDIFF(SECOND,START_TIME,END_TIME) where
GAME_ID="100001"

As you suggest,
UpdateQuery<GameRecord> updateSC =
factory.updateQuery(Game.GAME);
updateSC.addValue(Game.END_TIME, factory.currentTimestamp());
updateSC.addValue(Game.PLAY_TIME,
factory.plainSQLField("TIMESTAMPDIFF(SECOND,START_TIME, END_TIME)"));
updateSC.addValue(Game.SCORE,score);
updateSC.addConditions(Game.GAME_ID.equal(scoreCardId));
updateSC.execute();

OR here is what i want, since i want the record that is updated to
sent back to the user.

GameRecord =
factory.fetchOne(Game.GAME,Game.GAME_ID.equal(scoreCardId));
GameRecord.setValue(Game.END_TIME, factory.currentTimestamp());
GameRecord.addValue(Game.PLAY_TIME,
factory.plainSQLField("TIMESTAMPDIFF(SECOND,START_TIME, END_TIME)"));
GameRecord.setScore(score);
GameRecord.store();

Both are showing the error for addValue Game.PLAY_TIME line...
The method addValue(StoreQuery<GameRecord>, TableField<GameRecord,T>)
in the type UpdatableRecordImpl<GameRecord> is not
applicable for the arguments (TableField<GameRecord,BigDecimal>,
Field<capture#2-of ?>)

The second jooq query also shows an error in Game.END_TIME line..
The method setValue(Field<T>, T) in the type RecordImpl is not
applicable for the arguments (TableField<GameRecord,Timestamp>,
Field<Timestamp>)

Could you help?

I am sorry if i am wasting your precious time. I really don't have
much documentation about jOOQ

[Lukas Eder]

Hi Shekhar,

You're right, my example wasn't precise. jOOQ doesn't know the associated Java type of a plain SQL field, when you create it with Factory.plainSQLField(String). So that method will return Field<?>. On the other hand, if you know a reasonable type for your plain SQL field, then you can provide that type to jOOQ. Use this plainSQLField syntax instead:

create.plainSQLField("TIMESTAMPDIFF(SECOND, START_TIME, END_TIME)"), Integer.class);

Or replace the above Integer.class with BigInteger.class or BigDecimal.class, whatever matches Game.PLAY_TIME.getType().
I will update the documentation page to explain this:
https://sourceforge.net/apps/trac/jooq/wiki/Manual/DSL/SQL

Cheers
Lukas

[Shekhar]

Thanks for the reply...

The solution that u have given is accepted by eclipse compiler for

UpdateQuery<GameRecord> updateSC =
factory.updateQuery(Game.GAME);
updateSC.addValue(Game.END_TIME,
factory.currentTimestamp());
updateSC.addValue(Game.PLAY_TIME,
factory.plainSQLField("TIMESTAMPDIFF(SECOND,START_TIME,

END_TIME)",BigDecimal.class));
updateSC.addValue(Game.SCORE,score);

updateSC.addConditions(Game.GAME_ID.equal(scoreCardId));
updateSC.execute();

but this is yet not working... Is it not meant to work with this??

GameRecord =
factory.fetchOne(Game.GAME,Game.GAME_ID.equal(scoreCardId));
GameRecord.setValue(Game.END_TIME,
factory.currentTimestamp());
GameRecord.addValue(Game.PLAY_TIME,
factory.plainSQLField("TIMESTAMPDIFF(SECOND,START_TIME,

END_TIME)",BigDecimal.class));
GameRecord.setScore(score);
GameRecord.store();

The errors are..
-- for Game.END_TIME line
------------ The method addValue(StoreQuery<GameRecord>,

TableField<GameRecord,T>) in the type UpdatableRecordImpl<GameRecord>

is not applicable for the arguments (TableField<GameRecord,Timestamp>,
Field<Timestamp>)

-- for Game.PLAY_TIME line
------------ The method addValue(StoreQuery<GameRecord>,

TableField<GameRecord,T>) in the type UpdatableRecordImpl<GameRecord>
is not applicable for the arguments

(TableField<GameRecord,BigDecimal>, Field<BigDecimal>)

On Mar 26, 12:52 pm, Lukas Eder <lukas.e...@gmail.com> wrote:
> Hi Shekhar,
>
> You're right, my example wasn't precise. jOOQ doesn't know the associated
> Java type of a plain SQL field, when you create it with
> Factory.plainSQLField(String). So that method will return Field<?>. On the
> other hand, if you know a reasonable type for your plain SQL field, then you
> can provide that type to jOOQ. Use this plainSQLField syntax instead:
>
> create.plainSQLField("TIMESTAMPDIFF(SECOND, START_TIME, END_TIME)"),
> Integer.class);
>
> Or replace the above Integer.class with BigInteger.class or
> BigDecimal.class, whatever matches Game.PLAY_TIME.getType().
> I will update the documentation page to explain this:https://sourceforge.net/apps/trac/jooq/wiki/Manual/DSL/SQL
>
> Cheers
> Lukas
>

> 2011/3/26 Shekhar <rshekhar...@gmail.com>

[Lukas Eder]

My hint was meant only for the first syntax. You cannot set Field<?> as a value on a Record. You can see from the API that this is not possible.

[Ravi Shekhar]

Thanks again.