Using Java classes as starting point for DB defintion

[Tim Dudgeon]

Its presumably possible to use JOOQ starting from a set of Java classes that represent a set of tables?

e.g. using a set of classes that could have been generated by reverse engineering a db, but instead using the Java classes to define the DB (including generating DDL for creating the database).

It seems that this should be possible, but I'm struggling to find docs/examples.

Am I not looking in the right place?

Tim

[Lukas Eder]

Hi Tim,

2015-11-13 19:31 GMT+01:00 Tim Dudgeon <tdud...@informaticsmatters.com>:

Its presumably possible to use JOOQ starting from a set of Java classes that represent a set of tables?

In principle, yes. You can implement your own org.jooq.util.Database in jOOQ-meta to specify the different types of objects, such as tables, keys, sequences, procedures, etc. Ideally, you would be extending the org.jooq.util.AbstractDatabase. You can then feed this class to the code generator configuration:

<configuration>

    <generator>

        <database>

            <name>com.example.MyDatabase</name>

        </database>

    </generator>

</configuration>

 

Let me know if you need any help with this task.

e.g. using a set of classes that could have been generated by reverse engineering a db, but instead using the Java classes to define the DB (including generating DDL for creating the database).

There's a pending feature request to generate the DDL based on all the knowledge we have in generated classes:

https://github.com/jOOQ/jOOQ/issues/3160

However, it hasn't been a priority thus far - I cannot promise any release date on this feature.

Hope this helps

Lukas

[Tim Dudgeon]

OK, I'm struggling here.

Firstly are the meta javadocs online anywhere? The main link to the javadocs doesn't seem to include classes from this module.

So I looked at extending AbstractDatabase and there a a whole lot of abstract methods that need implementing, and to implement some (e.g. getTables()) needs a TableDefinition implementation which also has a whole load of abstract methods), so this looks like a lot of hard work. Am I barking up the wrong tree here?

I also found something in the DSL class:

Query q = PostgresDSL.createTable("HELLO").column("FOO", SQLDataType.INTEGER);

System.out.println("SQL: " + q.getSQL());

which prints:

create table "HELLO"("FOO" integer null)

Is this a possible approach to generating a Database/Table definition?

Tim

[Lukas Eder]

Hi Tim,

2015-11-17 15:43 GMT+01:00 Tim Dudgeon <tdud...@informaticsmatters.com>:

OK, I'm struggling here.

Firstly are the meta javadocs online anywhere?

Huh, no they aren't. Which is bad. I've registered an issue to improve this:

https://github.com/jOOQ/jOOQ/issues/4750

(the reason why they aren't is because jOOQ-meta and jOOQ-codegen were rather instable APIs in the beginning of jOOQ. We're far beyond that stage, so there's no reason not to publish these)

 

The main link to the javadocs doesn't seem to include classes from this module.

So I looked at extending AbstractDatabase and there a a whole lot of abstract methods that need implementing, and to implement some (e.g. getTables()) needs a TableDefinition implementation which also has a whole load of abstract methods), so this looks like a lot of hard work. Am I barking up the wrong tree here?

AbstractDatabase is the right place to start with. But you should only implement the abstract methods. You don't have to implement them all, in fact. If you don't want to support Routines, for instance, just return an empty list.

A TableDefinition is indeed needed. Again, base your implementation on AbstractTableDefinition, and you'll be fine.

A good idea is to take inspiration from something like H2Database...

I also found something in the DSL class:

Query q = PostgresDSL.createTable("HELLO").column("FOO", SQLDataType.INTEGER);

System.out.println("SQL: " + q.getSQL());

which prints:

create table "HELLO"("FOO" integer null)

Is this a possible approach to generating a Database/Table definition?

Yes, it is. I'm curious, what are you trying to do? Are you trying to go from jOOQ-meta to creating DDL scripts? Would be very interesting to learn more about this use-case!

Cheers,

Lukas

[Tim Dudgeon]

Sorry for delay.

Yes, I'm looking to have a way to define a table (or set of tables) in Java and then have methods I can use to create, modify and query the table(s), and to do this in a way that avoids risks of SQL injection.

Tim