Problems with jooq sql?

Plugins Plugin Development
1

Hello,

Is their anyone out their that could help me figuring out how you create a table with jooq properly. I am using this now:

String PLAYER_SCHEME = "CREATE TABLE players (\n" +
            "\tid BIGINT NOT NULL AUTO_INCREMENT UNIQUE,\n" +
            "\tUUID VARCHAR(36) NOT NULL UNIQUE,\n" +
            "\tUserName CHAR(16) NOT NULL,\n" +
            "\tLastSeen DATETIME NOT NULL,\n" +
            "\tPRIMARY KEY(id),\n" +
            "\tKEY uuidname(UUID)\n" +
            ");";
create = DSL.using(conn, SQLDialect.MYSQL);
List<String> tables = create.fetch("SHOW TABLES").getValues(0, String.class);
if (!tables.contains("players"))
    create.fetch(PLAYER_SCHEME);

But I have the feeling this isn’t how I should use jooq.

2

this is the syntax :

CREATE TABLE `author` (
  `id` int NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

so I would suppose :

String PLAYER_SCHEME = "CREATE TABLE `players` (\n" +
            "\t`id` BIGINT NOT NULL AUTO_INCREMENT UNIQUE,\n" +
            "\t`UUID` VARCHAR(36) NOT NULL UNIQUE,\n" +
            "\t`UserName` CHAR(16) NOT NULL,\n" +
            "\t`LastSeen` DATETIME NOT NULL,\n" +
            "\tPRIMARY KEY(`id`),\n" +
            "\tKEY uuidname(`UUID`)\n" +
            ");";
3

JOOQ has no direct support for creating tables. You could do it as you would with JDBC, by executing the creation-SQL as a string on the raw connection or via DSLContext.execute(String).

The better way might be to use an additional library like FlyWay to handle the database scheme. You can even use Flyway to automatically generate JOOQ’s scheme mappings, so if you update the database scheme, the changes are automatically present in the mappings right when you build you plugin.

2 Likes
4

Oh, … . Well I am pretty new to this SQL :blush:. I just made my queries from SQLyog and copied them over.

FlyWay looks very promising :open_mouth:. I look in to that as the schematics are possibly going to change if my plugin develops. Wow after watching some videos its exactly what I was looking for :smiley:.

5

Wow my isue was fixed incredibly fast :smiley:.

Thanks for the suggestions.