JooQ – The simplest ORM library in Java
jOOQ – Java Object Oriented Querying, is yet another Object Relation Mapping (ORM) library in java. Why does it really need a mention, when there exists other ORMs (like Hibernate, JPA) for over many years now?
The answer is simple: Because it’s the simplest ORM library in java.
How is jOOQ simple?
jOOQ generates classes from the database schema, which are then used to construct SQL queries in the Java application code. The best part is – if the database schema changes, it does not wait for throwing runtime errors. It rather shows errors at compile time so that those can be fixed immediately with no surprises at runtime and thereby reducing maintenance cost.
It also supports highly extensible SQL and handles the database differences very well. Also, it gives you low level control over the SQL queries, without compromising on the type safety interface.
Getting started with jOOQ
Enough of bragging about jOOQ now! Let’s get our hands dirty now. Follow the below steps and I bet, you will know jOOQ in 10 mins.
Before we start, for this code walk through, we will be using MySQL database.
Step 1: Download jOOQ jars
Go to http://www.jooq.org/download/versions and select the appropriate jOOQ download for you – depending on which database you are using and your purpose of using jOOQ.
For the illustration purpose, you can use “Open Source” and get the jOOQ zip from here after registration, of course.
For this code walk through, we will be using MySQL database.
Once you get the jOOQ-3.6.4.zip, extract these 3 jars – jooq-3.6.4.jar, jooq-codegen-3.6.4.jar and jooq-meta-3.6.4.jar to a folder say – “C:\work\jOOQ”
Step 2: Download SQL connector jar
You can download MySQL connector jar here. Once the download completes, extract the mysql-connector-java-5.0.8-bin to the same folder where jOOQ files were downloaded – “C:\work\jOOQ”
Step3: Create the mysql database
You can save below sql statements as .sql file and using mysqldump, import it.
|
1 2 3 4 5 6 7 8 9 10 |
create database student; use student; create table student_details( id integer auto_increment, name varchar(255), address varchar(255), primary key(id) ); |
Step 4: Create the jOOQ properties file
Save the below content in a .xml file, say jooq_student.xml in the directory c:\work\jOOQ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
<?xml version="1.0" encoding="UTF-8"?> <configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.6.0.xsd"> <!-- Configure the database connection here --> <jdbc> <driver>com.mysql.jdbc.Driver</driver> <url>jdbc:mysql://localhost:3306/student</url> <user>root</user> <password>root</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> <!-- The database type. The format here is: org.util.[database].[database]Database --> <name>org.jooq.util.mysql.MySQLDatabase</name> <!-- The database schema (or in the absence of schema support, in your RDBMS this can be the owner, user, database name) to be generated --> <inputSchema>student</inputSchema> <!-- All elements that are generated from your schema (A Java regular expression. Use the pipe to separate several expressions) Watch out for case-sensitivity. Depending on your database, this might be important! --> <includes>.*</includes> <!-- All elements that are excluded from your schema (A Java regular expression. Use the pipe to separate several expressions). Excludes match before includes --> <excludes /> </database> <generate> <!-- Do not reuse this property. It is deprecated as of jOOQ 3.3.0 --> <instanceFields>true</instanceFields> <daos>true</daos> <pojos>true</pojos> <interfaces>true</interfaces> </generate> <target> <!-- The destination package of your generated classes (within the destination directory) --> <packageName>com.student</packageName> <!-- The destination directory of your generated classes --> <directory>C:/studentdbclasses</directory> </target> </generator> </configuration> |
Step 5: Generate jOOQ classes
Then run the below command by going to c:\work\jOOQ directory :
|
1 2 |
c:\work\jOOQ>java -classpath jooq-3.6.2.jar;jooq-meta-3.6.2.jar;jooq-codegen-3.6 .2.jar;mysql-connector-java-5.1.35-bin.jar;. org.jooq.util.GenerationTool /jooq_ student.xml |
Note:
a) Note the “/” before the name of xml file in the above command. It is needed as per design.
b) The <input type=”text” /> tag in the above xml file corresponds to the name of the database. In our case, its student.
c) The tag corresponds to the directory where the jOOQ classes would be generated.
Step 6: Write the Java main class
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
import static com.student.Tables.STUDENT_DETAILS; import java.sql.Connection; import java.sql.DriverManager; import org.jooq.DSLContext; import org.jooq.Record; import org.jooq.SQLDialect; import org.jooq.impl.DSL; import com.student.tables.pojos.StudentDetails; public class JooqMainApp { public static void main(String[] args) { String userName = "root"; String password = "root"; String url = "jdbc:mysql://localhost:3306/student"; // Connection is the only JDBC resource that we need // PreparedStatement and ResultSet are handled by jOOQ, internally try (Connection conn = DriverManager.getConnection(url, userName, password)) { DSLContext dslContext = DSL.using(conn, SQLDialect.MYSQL); createUser(dslContext, "John Smith", "Street 1, Avenue 2A, Seattle"); }//let us keep exception handling simple catch (Exception e) { e.printStackTrace(); } } public static StudentDetails createUser(DSLContext dslContext, String name, String address) { //jOOQ SQL query Record studentDetailRecord = dslContext.insertInto(STUDENT_DETAILS) .set(STUDENT_DETAILS.NAME, name) .set(STUDENT_DETAILS.ADDRESS, address) .returning(STUDENT_DETAILS.ID).fetchOne(); StudentDetails studentDetails = new StudentDetails(); studentDetails.setId(studentDetailRecord.getValue(STUDENT_DETAILS.ID)); studentDetails.setName(name); studentDetails.setAddress(address); return studentDetails; } } |
You can verify using SQL command line, if the record was inserted into the database and I bet, it is inserted successfully!! (provided you followed above steps)
Yayy!, so there you are a jOOQ beginner who can write SQL queries in jOOQ very well!
