Viết SQL trong Java với JOOQ

Mình dự định viết một loạt bài về JOOQ, và đây là bài đầu tiên với nội dung chủ yếu giới thiệu cơ bản về JOOQ. Nội dung của bài đầu tiên này gồm.

• JOOQ là gì?
• Các bước cài đặt để viết SQL trong Java với JOOQ.
• JOOQ làm việc thế nào?

JOOQ là tên viết tắt của Java Object Oriented Querying. JOOQ là framework, nó impliment các ActiveRecord pattern. JOOQ cho phép ta làm việc với database thông qua các lớp Record ở tầng Data access.

2.1. Chuẩn bị môi trường

Ta dựng môi trường với MySQL (5.5), JDK (>=7), Eclipse với Meven (>=3.2.1). Tạo Maven project (có tên first_jooq). Bạn cài đặt JOOQ bản free bằng cách thêm vào Maven dependence (file pom.xml).

<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq</artifactId>
  <version>3.2.7</version>
</dependency>
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-meta</artifactId>
  <version>3.2.7</version>
</dependency>
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen</artifactId>
  <version>3.2.7</version>
</dependency>

2.2 Tạo và kết nối database test

Trong local MySQL ta tạo cơ sở dữ liệu test như sau.

CREATE DATABASE `test`;

USE `test`;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `date_of_birdth` date DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `lesson` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject_name` varchar(255) NOT NULL,
  `unit` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `lesson_id` int(11) DEFAULT NULL,
  `start_year_and_month` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Kết nối với database Ta dùng JDBC kết nối với MySQL database. Ta thêm khai báo vào Maven dependence như sau.

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.22</version>
    </dependency>
    <dependency>
      <groupId>commons-lang</groupId>
      <artifactId>commons-lang</artifactId>
      <version>2.6</version>
    </dependency>

Trong Java ta sử dụng thư viện DSLContext của JOOQ và jdbc connection.

import java.sql.DriverManager;

import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import org.jooq.SQLDialect;

import com.mysql.jdbc.Connection;

public class App {
    public static void main( String[] args ) {
    	String userName = "system";
        String password = "system";
        String url = "jdbc:mysql://10.0.1.24:3308/test";

        try (Connection conn = (Connection) DriverManager.getConnection(url, userName, password)) {
        	DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2.3 Gen tầng Data access

Tạo file confix xml như sau.

<?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/test</url>
    <user>system</user>
    <password>system</password>
  </jdbc>

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

    <database>
       
      <name>org.jooq.util.mysql.MySQLDatabase</name>

       
      <inputSchema>test</inputSchema>

       
      <includes>student</includes>

       
 
    </database>

    <target>
       
      <packageName>com.db.test_jooq</packageName>

       
      <directory>src/main/java-gen</directory>
    </target>
  </generator>
</configuration>

• Thẻ <inputSchema>schema_name<inputSchema/>: config tên của shema (tên database).
• Thẻ <includes>table1,table2<includes/>: config các table cần được gen class. Nếu để trống (<includes></includes>) tức gen toàn bộ table của schema.
• Thẻ <excludes>table3<excludes>: config những table cần loại bỏ khỏi tầng data access của project. Tức là khi ta gen lần 2 trở đi mà ta muốn loại bỏ những table đã được gen từ những lần trước đó. Nếu trống thì không có table nào được loại bỏ.

Ta có thể thực hiện lệnh gen tầng data access bằng hai cách.

• Dùng lệnh từ cửa sổ terminal:

java -classpath jooq-3.2.7.jar:jooq-meta-3.2.7.jar:jooq-codegen-3.2.7.jar:mysql-connector-java-5.1.18-bin.jar:.
  org.jooq.util.GenerationTool /library.xml

• Từ cửa sổ Run Configuration của Eclipse.

Với config như trên sau hi thực thi lệnh gen ta có tầng data access như sau.

2.4 Viết SQL trong Java

Giờ ta sẽ thực hành viết SQL chuy xuất dữ liệu studen từ shema test.

import static com.db.test_jooq.Tables.STUDENT;

import java.sql.DriverManager;

import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;

public class App {
    public static void main( String[] args ) {
    	String userName = "system";
        String password = "system";
        String url = "jdbc:mysql://10.0.1.24:3308/test";

        try (Connection conn = (Connection) DriverManager.getConnection(url, userName, password)) {
        	DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
            Result<Record> result = create.select().from(STUDENT).fetch();

            for (Record r : result) {
                Integer id = r.getValue(STUDENT.ID);
                String firstName = r.getValue(STUDENT.FIRST_NAME);
                String lastName = r.getValue(STUDENT.LAST_NAME);

                System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

• SELECT query.

select * from sutudent;
select count(*) from student;
select distinct fisrt_name from student;

Result<Record> result = create.select().from(STUDENT).fetch();
Result<?> = create.selectCount().from(STUDENT).fetch();
Result<?> = create.selectDistinct(STUDENT.FIRST_NAME).from(STUDENT).fetch();

• SELECT JOIN query.

select * from teacher A
join lesson B on A.lesson_id = B.id;

Result<Record> result = create.select().from(TEACHER)
    .join(LESSON).on(TEACHER.LESON_ID.equal(LESSON.ID)).fetch();

Result<Record> result = create.select().from(A)
    .join(B).using(A.COLUMN_X).fetch();
// Với column_x là key liên kiết giữa bảng A và bảng B.

• SELECT GROUP BY query.

select * from teacher A
group by lesson_id;

Result<Record> result = create.select().from(TEACHER)
    .groupBy(TEACHER.LESSON_ID).fetch();

• SELECT HAVING query.

select * from lesson A
join teacher B on (A.id = B.lesson_id)
group by B.lesson_id
having count(*) >= 2;

Result<Record> result = create.select().from(LESSON)
    .join(TEACHER).on(TEACHER.LESON_ID.equal(LESSON.ID))
    .groupBy(TEACHER.LESON_ID)
    .having(count().geq(2)).fetch();

• SELECT LIMIT OFFSET query.

select * from lesson limit 10 offset 2;

Result<Record> result = create.selectFrom(LESSON)
    limit(10).offset(2).fetch();

Để hiểu cặn kẽ JOOQ làm việc ra sao sẽ rất phức tạp. Ta có thể hiểu một cách đơn giản như sau.

• JOOQ là một DSL nên chình biên dịch Java có thể kiểm tra tính chính xác cú pháp câu truy vấn SQL dựa vào các từ khóa.
• Sử dụng lớp data access tức là các Java object (table, record, field-columns) được gen từ database nên trình biên dịch Java có thể kiểm tra tính đúng đắn của meta data (tên bảng, cột, kiểu dữ liệu)
• Nó chuyển đổi (build) SQL của chính nó sang cú pháp SQL chuẩn của một API Java cụ thể (ở đây ta đang sử dụng là MySQL - SQLDialect.MYSQ) rồi thực thi SQL chuẩn đó.

Ta có thể xem câu SQL chuẩn được JOOQ build bằng câu lệnh print của Java như sau.

System.out.println(
	create.select().from(STUDENT)
	.where(STUDENT.FIRST_NAME.like("TRINH"))
	.orderBy(STUDENT.DATE_OF_BIRDTH.desc())
);

và kết quả là

select `test`.`student`.`id`, `test`.`student`.`first_name`, `test`.`student`.`last_name`, `test`.`student`.`date_of_birdth`

from `test`.`student`

where `test`.`student`.`first_name` like 'TRINH' order by `test`.`student`.`date_of_birdth` desc