JOOQ with SQL Server

Question

I am a big fan of ORM especially when it comes to .NET with Entity Framework and using LINQ makes your data access less tedious and more enjoyable.

However, I am working on Java at the moment and particularly looking at ORMs. I've tried nHybernate against our SQL Server database - and with the absence of keys (unique constraints) on some tables, I've started looking at JOOQ.

I've managed to generate my Java classes from the SQL Server database however - not without error - using JOOQ (v3.2.0).

According to this page JOOQ Connections it mentions a class org.jooq.util.sqlserver.SQLServerDatabase. However when I generated with this in the configuration, I got class not found exception and nothing was generated. I have had to use org.jooq.util.jdbc.JDBCDatabase to generate my code.

Here is my configuration file (myjooqdbconfig.xml):

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.2.0.xsd">
  <jdbc>
    <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
    <url>jdbc:sqlserver://MYSERVER:1433;DatabaseName=MYDB</url>
    <user>MYUSER</user>
    <password>PWD</password>
  </jdbc>

  <generator>
    <name>org.jooq.util.DefaultGenerator</name>

    <database>
      <name>org.jooq.util.jdbc.JDBCDatabase</name>

      <inputSchema>dbo</inputSchema>

      <includes>.*</includes>

      <excludes></excludes>
    </database>

    <target>
      <packageName>com.quorum.sentinel.dataAccess</packageName>

      <directory>F:\Libraries\jooq\jOOQ-3.2.0\lib\gen</directory>
    </target>
  </generator>
</configuration>

With the class not found exception I had a peek in the JAR file jooq-meta-3.2.0.jar using Java Decompiler where it would appear these classes are located. I cannot see the org.jooq.util.sqlserver.SQLServerDatabase class definition.

I am compiling with:

java -classpath jooq-3.2.0.jar;jooq-meta-3.2.0.jar;jooq-codegen-3.2.0.jar;sqljdbc4.jar;. org.jooq.util.GenerationTool /myjooqdbconfig.xml

Does this org.jooq.util.sqlserver.SQLServerDatabase definition exist in any JAR?

Additionally, having generated the code from the above configuration, it seemed to take a long time and appeared to be in some loop.

Looking in the generated files under dbo - Keys/Tables/Dbo, there were duplicate names of my tables which I noticed when I copied the files into a Netbeans project (lots of errors). I removed the duplicates and so far so good, I can read various tables from the database in my code below:

public static void main(String[] args) {
    Connection conn = null;

    String userName = "MYUSER";
    String password = "PWD";
    String url = "jdbc:sqlserver://MYSERVER:1433;DatabaseName=MYDB";

    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
        conn = DriverManager.getConnection(url, userName, password);

        DSLContext create = DSL.using(conn);
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        System.out.println(simpleDateFormat.format(new Date( new GregorianCalendar().getTimeInMillis())));
        SelectJoinStep<Record> from = create.select().from(Configuration.CONFIGURATION);
        Result<Record> result = from.fetch();
        System.out.println(simpleDateFormat.format(new Date( new GregorianCalendar().getTimeInMillis())));
        for (Record r : result) {
            Object id =  r.getValue(Configuration.CONFIGURATION.NAME);
            Object time = r.getValue(Configuration.CONFIGURATION.VALUE);
            System.out.println("Record:              : ID: " + id + " Time: " + time);
        }

        System.out.println(simpleDateFormat.format(new Date( new GregorianCalendar().getTimeInMillis())));

    } catch (Exception e) {
        // For the sake of this tutorial, let's keep exception handling simple
        e.printStackTrace();
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
        }
    }
}

So just to reiterate my questions amongst that mess:

1. Where is the SQL Server specific class org.jooq.util.sqlserver.SQLServerDatabase?
2. Is the above information how you would typically generate SQL Server object mappings?
3. Given that the above almost generated correctly, and that there was duplicate definitions generated in Keys/Tables/Dbo .java files, I don't want to immediately say it is a bug with Jooq - but could be bad database design/incorrect configuration/silly developer!

Answer 1

With jOOQ 3.2, jOOQ has become dual-licensed. The SQL Server integration is available only with a jOOQ Professional Edition license. You may, however, download a free 30 day trial version that works with SQL Server.

Note, there is also a fix for jOOQ 3.2.1 (to be released soon), to give community edition users more information rather than just a stack trace.

See also: jooq-3.2.0 and db2 database