The Backend Engineer’s Journal: jOOQ

Showing posts with label jOOQ. Show all posts

JOOQ Simple CRUD operation example

Environment Used

Java 1.7

JOOQ 3.2

Eclipse

MySql

1. What is JOOQ?

Java Object Oriented Querying is a light weight database mapping API in java that implements the active record pattern. The main objective of this API to provide domain specific language to construct queries and generating classes from database schema. See more about jooq

2. Setup JOOQ Project

Create a java project and add below jars in the class path (Click here to download jars)

3. Generate Code

jOOQ generates Java code from your database and lets you build typesafe SQL queries through its fluent API. See this example to Generate Jooq code

4. Create a java class to create connection

package com.pretech.utility;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JdbcConnection {
	
	public static Connection getConnection()
	{Connection conn = null;
	String userName = "root";
	String password = "root";
	String url = "jdbc:mysql://localhost:3306/studentdatabase";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, userName, password);
		} catch (InstantiationException | IllegalAccessException
				| ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
}

5. Create a main class for CRUD operation

package com.pretech.master;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import org.jooq.types.UInteger;
import com.pretech.jooq.tables.Student;
import com.pretech.jooq.tables.records.StudentRecord;
import com.pretech.utility.JdbcConnection;
public class StudentMaster {
	public static void main(String[] args) {
		try {
			DSLContext create = DSL.using(JdbcConnection.getConnection(),
					SQLDialect.MYSQL);
			// INSERTING STUDENT DETAILS
			create.insertInto(Student.STUDENT, Student.STUDENT.ID,
					Student.STUDENT.NAME, Student.STUDENT.STANDARD)
					.values(UInteger.valueOf(1), "Rajesh", "10th Standard")
					.execute();
			System.out.println("STUDENT RECORDS AFTER INSERTING");
			// SELECTING STUDENT RECORDS
			Result<Record> result0 = create.select().from(Student.STUDENT)
					.fetch();
			for (Record r : result0) {
				StudentRecord stud = (StudentRecord) r;
				System.out.println(stud);
			}
			create.update(Student.STUDENT)
					.set(Student.STUDENT.STANDARD, "9th standard")
					.where(Student.STUDENT.ID.equal(UInteger.valueOf(1))).execute();
			System.out.println("STUDENT RECORDS AFTER UPDATING");
			// SELECTING STUDENT RECORDS
			Result<Record> result1 = create.select().from(Student.STUDENT)
					.fetch();
			for (Record r : result1) {
				StudentRecord stud = (StudentRecord) r;
				System.out.println(stud);
			}
			create.delete(Student.STUDENT).execute();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

6. Final structure of the project

7. Output

8. Download this example

Download JOOQ Simple CRUD operation example

How to generate jOOQ code using eclipse ?

jOOQ generates Java code from your database and lets you build typesafe SQL queries through its fluent API. In this example we will see how to generate code using eclipse and MySql

1. Create a Java project and add below jars in the class path

1. jooq-xxxx.jar –This is the core library for jOOQ

2. jooq-meta-xxxx.jar- This jar includes utility class to navigate database schema for code generation

3. jooq-codegen-xxxx.jar- Utility to generate code for database schema

All these jars are available in http://www.jooq.org/download/

4. Java mysql connector jar- Available in http://www.mysql.com/products/connector/

2. Create a database schema and table in MySql database

3. Create below xml under project src folder (student.xml)

In this xml file we need to mention database connection and schema details to generate code

<?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.mysql.jdbc.Driver</driver>
    <url>jdbc:mysql://localhost:3306/studentdatabase</url>
    <user>root</user>
    <password>root</password>
  </jdbc>
  <generator>
    <name>org.jooq.util.DefaultGenerator</name>
    <database>
      <name>org.jooq.util.mysql.MySQLDatabase</name>
      <inputSchema>studentdatabase</inputSchema>
      <excludes></excludes>
    </database>
    <target>
      <packageName>com.pretech.jooq</packageName>
      <directory>D:\Projects\stsworkspace\JooqStudentExample\src</directory>
    </target>
  </generator>
</configuration>

4. Eclipse run configuration

Right click on the project and select ‘Run As’ Run configuration and Select main class org.jooq.util.GenerationTool

Argument parameter should be the xml file which we created in step 3

5. Run it

Run the org.jooq.util.GenerationTool and we can see the codes are generated in the target directory which we mentioned in the student.xml file
Final structure of the project

What are the Jars required for jOOQ code generation?

Below are the jars required for JOOQ based ORMing and code generation.