jOOQ用户指南：快速入门与高级特性

需积分: 50 75 浏览量更新于2024-07-18 1 收藏 1.61MB PDF 举报

JOOQ参考手册是一本全面的指南，专为那些希望深入理解和利用JOOQ这一强大的Java库进行SQL操作的人设计。JOOQ的目标是提供一个直观且灵活的方式来编写和执行SQL查询，同时保持与原始SQL语法的高度兼容性，避免了传统ORM工具中常见的抽象和复杂性。手册分为六个主要部分： 1. **入门与快速启动**：这部分旨在帮助初学者快速上手，介绍了JOOQ的基本概念，包括它不是什么（例如，不是将SQL封装为对象的沉重映射器），以及如何初次安装和配置JOOQ。通过简单的示例和步骤，新用户可以了解如何在项目中集成JOOQ并开始构建查询。 2. **SQL构建**：这是手册的核心内容，详细讲解了JOOQ的查询构建器（query DSL）和查询模型API。这部分涵盖了核心工厂的使用，展示了如何构造各种SQL语句，如SELECT、INSERT、UPDATE和DELETE等。此外，还包括对查询语法元素的深入解析，如条件表达式、连接、聚合函数等。 3. **代码生成**：JOOQ的一大特点是可以自动生成数据库访问代码，这部分指导用户如何配置和利用内置的代码生成器。用户可以根据需要定制生成的代码，以适应特定的数据库结构和项目需求，提升开发效率。 4. **SQL执行与操作**：这部分深入讲解了在运行时如何使用JOOQ执行查询、执行CRUD操作（Create、Read、Update、Delete）、数据导入导出，以及如何利用JOOQ的执行生命周期进行调试。这部分内容实用性强，帮助开发者在实际应用中优化性能和解决问题。 5. **工具与辅助功能**：这部分介绍了与JOOQ相关的辅助工具，如IDE插件、数据库建模工具等，以提升开发者的开发环境体验。这些工具可以帮助用户更方便地管理和维护JOOQ项目，提高开发过程中的便利性和效率。 6. **总结与扩展**：最后，手册可能会提供总结性的概述，强调JOOQ的优势，如性能、灵活性和代码质量，以及与其他SQL库的比较。此外，还会介绍JOOQ的最新特性和发展方向，供读者进一步探索和学习。 JOOQ参考手册是一部综合而详尽的文档，无论是初学者还是经验丰富的开发人员，都能从中找到所需的教程、示例和深入的理论支持，以充分利用JOOQ进行高效、灵活的数据库操作。

The jOOQ User Manual 3. Getting started with jOOQ

3. Getting started with jOOQ

These chapters contain a quick overview of how to get started with this manual and with jOOQ. While

the subsequent chapters contain a lot of reference information, this chapter here just wraps up the

essentials.

3.1. How to read this manual

This section helps you correctly interpret this manual in the context of jOOQ.

Code blocks

The following are code blocks:

-- A SQL code block

SELECT 1 FROM DUAL

// A Java code block

for (int i = 0; i < 10; i++);

# A config file code block

org.jooq.property=value

These are useful to provide examples in code. Often, with jOOQ, it is even more useful to compare SQL

code with its corresponding Java/jOOQ code. When this is done, the blocks are aligned side-by-side,

with SQL usually being on the left, and an equivalent jOOQ DSL query in Java usually being on the right:

-- In SQL:

SELECT 1 FROM DUAL

// Using jOOQ:

create.selectOne().fetch()

Code block contents

The contents of code blocks follow conventions, too. If nothing else is mentioned next to any given code

block, then the following can be assumed:

-- SQL assumptions

------------------

-- If nothing else is specified, assume that the Oracle syntax is used

SELECT 1 FROM DUAL

The jOOQ User Manual 3.2. The sample database used in this manual

// Java assumptions

// ----------------

// Whenever you see "standalone functions", assume they were static imported from org.jooq.impl.DSL

// "DSL" is the entry point of the static query DSL

exists(); max(); min(); val(); inline(); // correspond to DSL.exists(); DSL.max(); DSL.min(); etc...

// Whenever you see BOOK/Book, AUTHOR/Author and similar entities, assume they were (static) imported from the generated schema

BOOK.TITLE, AUTHOR.LAST_NAME // correspond to com.example.generated.Tables.BOOK.TITLE, com.example.generated.Tables.BOOK.TITLE

FK_BOOK_AUTHOR // corresponds to com.example.generated.Keys.FK_BOOK_AUTHOR

// Whenever you see "create" being used in Java code, assume that this is an instance of org.jooq.DSLContext.

// The reason why it is called "create" is the fact, that a jOOQ QueryPart is being created from the DSL object.

// "create" is thus the entry point of the non-static query DSL

DSLContext create = DSL.using(connection, SQLDialect.ORACLE);

Your naming may differ, of course. For instance, you could name the "create" instance "db", instead.

Execution

When you're coding PL/SQL, T-SQL or some other procedural SQL language, SQL statements are always

executed immediately at the semi-colon. This is not the case in jOOQ, because as an internal DSL, jOOQ

can never be sure that your statement is complete until you call fetch() or execute(). The manual tries

to apply fetch() and execute() as thoroughly as possible. If not, it is implied:

SELECT 1 FROM DUAL

UPDATE t SET v = 1

create.selectOne().fetch();

create.update(T).set(T.V, 1).execute();

Degree (arity)

jOOQ records (and many other API elements) have a degree N between 1 and 22. The variable degree

of an API element is denoted as [N], e.g. Row[N] or Record[N]. The term "degree" is preferred over arity,

as "degree" is the term used in the SQL standard, whereas "arity" is used more often in mathematics

and relational theory.

Settings

jOOQ allows to override runtime behaviour using org.jooq.conf.Settings. If nothing is specified, the

default runtime settings are assumed.

Sample database

jOOQ query examples run against the sample database. See the manual's section about the sample

database used in this manual to learn more about the sample database.

3.2. The sample database used in this manual

For the examples in this manual, the same database will always be referred to. It essentially consists of

these entities created using the Oracle dialect

The jOOQ User Manual 3.3. Different use cases for jOOQ

CREATE TABLE language (

id NUMBER(7) NOT NULL PRIMARY KEY,

cd CHAR(2) NOT NULL,

description VARCHAR2(50)

);

CREATE TABLE author (

id NUMBER(7) NOT NULL PRIMARY KEY,

first_name VARCHAR2(50),

last_name VARCHAR2(50) NOT NULL,

date_of_birth DATE,

year_of_birth NUMBER(7),

distinguished NUMBER(1)

);

CREATE TABLE book (

id NUMBER(7) NOT NULL PRIMARY KEY,

author_id NUMBER(7) NOT NULL,

title VARCHAR2(400) NOT NULL,

published_in NUMBER(7) NOT NULL,

language_id NUMBER(7) NOT NULL,

CONSTRAINT fk_book_author FOREIGN KEY (author_id) REFERENCES author(id),

CONSTRAINT fk_book_language FOREIGN KEY (language_id) REFERENCES language(id)

);

CREATE TABLE book_store (

name VARCHAR2(400) NOT NULL UNIQUE

);

CREATE TABLE book_to_book_store (

name VARCHAR2(400) NOT NULL,

book_id INTEGER NOT NULL,

stock INTEGER,

PRIMARY KEY(name, book_id),

CONSTRAINT fk_b2bs_book_store FOREIGN KEY (name) REFERENCES book_store (name) ON DELETE CASCADE,

CONSTRAINT fk_b2bs_book FOREIGN KEY (book_id) REFERENCES book (id) ON DELETE CASCADE

);

More entities, types (e.g. UDT's, ARRAY types, ENUM types, etc), stored procedures and packages are

introduced for specific examples

In addition to the above, you may assume the following sample data:

INSERT INTO language (id, cd, description) VALUES (1, 'en', 'English');

INSERT INTO language (id, cd, description) VALUES (2, 'de', 'Deutsch');

INSERT INTO language (id, cd, description) VALUES (3, 'fr', 'Français');

INSERT INTO language (id, cd, description) VALUES (4, 'pt', 'Português');

INSERT INTO author (id, first_name, last_name, date_of_birth , year_of_birth)

VALUES (1 , 'George' , 'Orwell' , DATE '1903-06-26', 1903 );

INSERT INTO author (id, first_name, last_name, date_of_birth , year_of_birth)

VALUES (2 , 'Paulo' , 'Coelho' , DATE '1947-08-24', 1947 );

INSERT INTO book (id, author_id, title , published_in, language_id)

VALUES (1 , 1 , '1984' , 1948 , 1 );

INSERT INTO book (id, author_id, title , published_in, language_id)

VALUES (2 , 1 , 'Animal Farm' , 1945 , 1 );

INSERT INTO book (id, author_id, title , published_in, language_id)

VALUES (3 , 2 , 'O Alquimista', 1988 , 4 );

INSERT INTO book (id, author_id, title , published_in, language_id)

VALUES (4 , 2 , 'Brida' , 1990 , 2 );

INSERT INTO book_store VALUES ('Orell Füssli');

INSERT INTO book_store VALUES ('Ex Libris');

INSERT INTO book_store VALUES ('Buchhandlung im Volkshaus');

INSERT INTO book_to_book_store VALUES ('Orell Füssli' , 1, 10);

INSERT INTO book_to_book_store VALUES ('Orell Füssli' , 2, 10);

INSERT INTO book_to_book_store VALUES ('Orell Füssli' , 3, 10);

INSERT INTO book_to_book_store VALUES ('Ex Libris' , 1, 1 );

INSERT INTO book_to_book_store VALUES ('Ex Libris' , 3, 2 );

INSERT INTO book_to_book_store VALUES ('Buchhandlung im Volkshaus', 3, 1 );

3.3. Different use cases for jOOQ

jOOQ has originally been created as a library for complete abstraction of JDBC and all database

interaction. Various best practices that are frequently encountered in pre-existing software products

are applied to this library. This includes:

The jOOQ User Manual 3.3.1. jOOQ as a SQL builder

- Typesafe database object referencing through generated schema, table, column, record,

procedure, type, dao, pojo artefacts (see the chapter about code generation)

- Typesafe SQL construction / SQL building through a complete querying DSL API modelling SQL

as a domain specific language in Java (see the chapter about the query DSL API)

- Convenient query execution through an improved API for result fetching (see the chapters about

the various types of data fetching)

- SQL dialect abstraction and SQL clause emulation to improve cross-database compatibility and

to enable missing features in simpler databases (see the chapter about SQL dialects)

- SQL logging and debugging using jOOQ as an integral part of your development process (see the

chapters about logging)

Effectively, jOOQ was originally designed to replace any other database abstraction framework short of

the ones handling connection pooling (and more sophisticated transaction management)

Use jOOQ the way you prefer

... but open source is community-driven. And the community has shown various ways of using jOOQ

that diverge from its original intent. Some use cases encountered are:

- Using Hibernate for 70% of the queries (i.e. CRUD) and jOOQ for the remaining 30% where SQL

is really needed

- Using jOOQ for SQL building and JDBC for SQL execution

- Using jOOQ for SQL building and Spring Data for SQL execution

- Using jOOQ without the source code generator to build the basis of a framework for dynamic

SQL execution.

The following sections explain about various use cases for using jOOQ in your application.

3.3.1. jOOQ as a SQL builder

This is the most simple of all use cases, allowing for construction of valid SQL for any database. In

this use case, you will not use jOOQ's code generator and probably not even jOOQ's query execution

facilities. Instead, you'll use jOOQ's query DSL API to wrap strings, literals and other user-defined objects

into an object-oriented, type-safe AST modelling your SQL statements. An example is given here:

// Fetch a SQL string from a jOOQ Query in order to manually execute it with another tool.

// For simplicity reasons, we're using the API to construct case-insensitive object references, here.

String sql = create.select(field("BOOK.TITLE"), field("AUTHOR.FIRST_NAME"), field("AUTHOR.LAST_NAME"))

.from(table("BOOK"))

.join(table("AUTHOR"))

.on(field("BOOK.AUTHOR_ID").eq(field("AUTHOR.ID")))

.where(field("BOOK.PUBLISHED_IN").eq(1948))

.getSQL();

The SQL string built with the jOOQ query DSL can then be executed using JDBC directly, using

Spring's JdbcTemplate, using Apache DbUtils and many other tools (note that since jOOQ uses

PreparedStatement by default, this will generate a bind variable for "1948". Read more about bind

variables here).

If you wish to use jOOQ only as a SQL builder, the following sections of the manual will be of interest

to you:

The jOOQ User Manual 3.3.2. jOOQ as a SQL builder with code generation

- SQL building: This section contains a lot of information about creating SQL statements using the

jOOQ API

- Plain SQL: This section contains information useful in particular to those that want to supply

table expressions, column expressions, etc. as plain SQL to jOOQ, rather than through

generated artefacts

3.3.2. jOOQ as a SQL builder with code generation

In addition to using jOOQ as a standalone SQL builder, you can also use jOOQ's code generation

features in order to compile your SQL statements using a Java compiler against an actual database

schema. This adds a lot of power and expressiveness to just simply constructing SQL using the query

DSL and custom strings and literals, as you can be sure that all database artefacts actually exist in the

database, and that their type is correct. An example is given here:

// Fetch a SQL string from a jOOQ Query in order to manually execute it with another tool.

String sql = create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)

.from(BOOK)

.join(AUTHOR)

.on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))

.where(BOOK.PUBLISHED_IN.eq(1948))

.getSQL();

The SQL string that you can generate as such can then be executed using JDBC directly, using Spring's

JdbcTemplate, using Apache DbUtils and many other tools.

If you wish to use jOOQ only as a SQL builder with code generation, the following sections of the manual

will be of interest to you:

- SQL building: This section contains a lot of information about creating SQL statements using the

jOOQ API

- Code generation: This section contains the necessary information to run jOOQ's code generator

against your developer database

3.3.3. jOOQ as a SQL executor

Instead of any tool mentioned in the previous chapters, you can also use jOOQ directly to execute your

jOOQ-generated SQL statements. This will add a lot of convenience on top of the previously discussed

API for typesafe SQL construction, when you can re-use the information from generated classes to fetch

records and custom data types. An example is given here:

// Typesafely execute the SQL statement directly with jOOQ

Result<Record3<String, String, String>> result =

create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)

.from(BOOK)

.join(AUTHOR)

.on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))

.where(BOOK.PUBLISHED_IN.eq(1948))

.fetch();

By having jOOQ execute your SQL, the jOOQ query DSL becomes truly embedded SQL.

jOOQ doesn't stop here, though! You can execute any SQL with jOOQ. In other words, you can use any

other SQL building tool and run the SQL statements with jOOQ. An example is given here:

剩余296页未读，继续阅读

超文本

粉丝: 4
资源: 14

jOOQ用户指南：快速入门与高级特性

JOOQ jar包

jOOQ, 在Java中，jOOQ是编写SQL的最佳方法.zip

jooq入门教程

JOOQ数据库手册

jOOQ开发手册

The jooq User Manual

jOOQ-manual-3.10.pdf

SpringBoot与数据库交互：jOOQ与NoSQL技术实战

高分技术项目源码集合：Chengfeng整合JOOQ实践指南

jooq多数据源代码生成配置

最新资源