Developers zone > Apps for CUBRID > jOOQ: Java Object Oriented Querying > jOOQ CUBRID Tutorial

jOOQ CUBRID Tutorial

Prerequisites

Alternatively, you can create a Maven dependency:

<dependency>
  <groupId>org.jooq</groupId>
  <!-- artefacts are jooq, jooq-meta, jooq-codegen -->
  <artifactId>jooq</artifactId>
  <version>2.0.0</version>
</dependency>

Step 1: Create a SQL database and a table

We're going to create a database called "guestbook" and a corresponding "posts" table. In a command line type the following commands:

cubrid createdb guestbook;
cubrid server start guestbook;
csql guestbook;

CREATE TABLE "posts"(
  "id" bigint,
  "body" character varying(255),
  "timestamp" datetime,
  "title" character varying(255),
  CONSTRAINT pk_posts_id PRIMARY KEY("id")
);

Alternatively, you can use CUBRID Manager to create the database and table.

Step 2: Generate classes

In this step, we're going to use jOOQ's command line tools to generate classes that map to the Posts table we just created. More detailed information about how to set up the jOOQ code generator can be found here: http://www.jooq.org/manual/META/Configuration/

The easiest way to generate a schema is to copy the jOOQ jar files (there should be 3) and the CUBRID jdbc jar file to a temporary directory. Then, create a guestbook.xml that looks like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-2.0.4.xsd">
  <!-- Configure the database connection here -->
  <jdbc>
    <driver>cubrid.jdbc.driver.CUBRIDDriver</driver>
    <url>jdbc:cubrid:localhost:30000:guestbook:::</url>
    <user>dba</user>
    <password></password>
  </jdbc>

  <generator>
    <!-- The default code generator. You can override this one, to generate your own code style
         Defaults to org.jooq.util.DefaultGenerator -->
    <name>org.jooq.util.DefaultGenerator</name>

    <database>
      <name>org.jooq.util.cubrid.CUBRIDDatabase</name>

      <!-- The database schema (or owner, user, database name) to be generated -->
      <inputSchema>DBA</inputSchema>

      <!-- All elements that are generated from your schema (several Java regular expressions, separated by comma)
           Watch out for case-sensitivity. Depending on your database, this might be important! -->
      <includes>.*</includes>

      <!-- All elements that are excluded from your schema (several Java regular expressions, separated by comma).
              Excludes match before includes -->
      <excludes>_?db_.*?</excludes>
    </database>

    <generate>
      <!-- Primary key / foreign key relations should be generated and used.
           This will be a prerequisite for various advanced features
           Defaults to false -->
      <relations>true</relations>
    </generate>

    <target>
      <!-- The destination package of your generated classes (within the destination directory) -->
      <packageName>test.generated</packageName>

      <!-- The destination directory of your generated classes -->
      <directory>C:/workspace/CUBRIDTest/src</directory>
    </target>
  </generator>
</configuration>

Replace the username with whatever user has the appropriate privileges. You'll want to look at the other values and replace as necessary. Here are the two interesting properties:

generator.target.package - set this to the parent package you want to create for the generated classes. The setting of test.generated will cause the test.generated.Posts and test.generated.PostsRecord to be created

generator.target.directory - the directory to output to.

Once you have the JAR files and guestbook.xml in your temp directory, type this (use colons instead of semi-colons on UNIX/Linux systems):

java -classpath jooq-2.2.0.jar;jooq-meta-2.2.0.jar;jooq-codegen-2.2.0.jar;CUBRID-8.4.1_jdbc.jar;. org.jooq.util.GenerationTool /guestbook.xml

Remember that you need to add jooq-2.2.0.jar;jooq-meta-2.2.0.jar;jooq-codegen-2.2.0.jar;CUBRID-8.4.1_jdbc.jar; to every java/javac command. We recommend copying these files to jre/lib/ext folder.

Note the prefix slash before guestbook.properies. Even though it's in our working directory, we need to prepend a slash, as it is loaded from the classpath. Replace the filenames with your filenames. In this example, jOOQ 2.2.0 is being used. If everything has worked, you should see this in your console output:

Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Initialising properties  : /guestbook.xml
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Database parameters      
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: ----------------------------------------------------------
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   dialect                : CUBRID
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   target dir             : C:/workspace/CUBRIDTest/src
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   target package         : test.generated
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: ----------------------------------------------------------
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:                          
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Generation parameters    
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: ----------------------------------------------------------
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   strategy               : class org.jooq.util.DefaultGeneratorStrategy
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   deprecated             : true
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   generated annotation   : true
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   instance fields        : true
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   JPA annotations        : false
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   navigation methods     : true
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   records                : true
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   pojos                  : false
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO:   relations              : true
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: ----------------------------------------------------------
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Emptying                 : C:\workspace\CUBRIDTest\src\test\generated
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Generating schema        : Dba.java
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: ----------------------------------------------------------
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Generating factory       : DbaFactory.java
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Schema generated         : Total: 257.137ms
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Sequences fetched        : 0 (0 included, 0 excluded)
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Masterdata tables fetched: 0 (0 included, 0 excluded)
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Tables fetched           : 40 (1 included, 39 excluded)
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Generating tables        
Apr 11, 2012 12:40:44 PM org.jooq.tools.JooqLogger info
INFO: Generating table         : Posts.java
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: ARRAYs fetched           : 0 (0 included, 0 excluded)
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: Enums fetched            : 0 (0 included, 0 excluded)
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: UDTs fetched             : 0 (0 included, 0 excluded)
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: Tables generated         : Total: 481.934ms, +224.796ms
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: Generating table references
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: Table references generated: Total: 488.677ms, +6.742ms
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: Generating Keys          
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: Keys generated           : Total: 490.734ms, +2.056ms
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: Generating records       
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: Generating record        : PostsRecord.java
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: Table records generated  : Total: 538.368ms, +47.633ms
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: Routines fetched         : 0 (0 included, 0 excluded)
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: Packages fetched         : 0 (0 included, 0 excluded)
Apr 11, 2012 12:40:45 PM org.jooq.tools.JooqLogger info
INFO: GENERATION FINISHED!     : Total: 539.201ms, +0.833ms

Step 3: Write a main class and establish a CUBRID connection

Let's just write a vanilla main class in the project containing the generated classes:

// For convenience, always static import your generated tables and
// jOOQ functions to decrease verbosity:
import static test.generated.Tables.*;
import static org.jooq.impl.Factory.*;

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

        String userName = "dba";
        String password = "";
        String url = "jdbc:cubrid:localhost:30000:guestbook";

        try {
            Class.forName("cubrid.jdbc.driver.CUBRIDDriver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }
}

The file should be named Main.java and located in the C:\workspace\CUBRIDTest\src\test folder. Of course, this folder is the one we used for this example. This is pretty standard code for establishing a CUBRID connection.

Step 4: Write a query using jOOQ's DSL

Let's add a simple query:

DbaFactory create = new DbaFactory(conn);
Result<?> result = create.select().from(POSTS).fetch();

First get an instance of DbaFactory so we can write a simple SELECT query. We pass an instance of the CUBRID connection to DbaFactory. Note that the factory doesn't close the connection. We'll have to do that ourselves.

We then use jOOQ's DSL to return an instance of Result. We'll be using this result in the next step.

Step 5: Iterate over results

After the line where we retrieve the results, let's iterate over the results and print out the data:

for (Record r : result) {
    Long id = r.getValue(POSTS.ID);
    String title = r.getValue(POSTS.TITLE);
    String description = r.getValue(POSTS.BODY);

    System.out.println("ID: " + id + " title: " + title + " desciption: " + description);
}

The full program should now look like this:

package test;

// For convenience, always static import your generated tables and
// jOOQ functions to decrease verbosity:
import static test.generated.Tables.*;
import static org.jooq.impl.Factory.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

import org.jooq.Record;
import org.jooq.Result;

import test.generated.DbaFactory;
import test.generated.tables.Posts;

public class Main {

    /**
     * @param args
     */
    public static void main(String[] args) throws SQLException {
        Connection conn = null;

        String userName = "dba";
        String password = "";
        String url = "jdbc:cubrid:localhost:30000:guestbook";

        try {
            Class.forName("cubrid.jdbc.driver.CUBRIDDriver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);

            DbaFactory create = new DbaFactory(conn);
            Result<?> result = create.select().from(POSTS).fetch();

            for (Record r : result) {
                Long id = r.getValue(POSTS.ID);
                String title = r.getValue(POSTS.TITLE);
                String description = r.getValue(POSTS.BODY);

                System.out.println("ID: " + id + " title: " + title + " description: " + description);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }
}