jOOQ用户手册：探索SQL构建与代码生成

需积分: 50 163 浏览量更新于2024-07-19 3 收藏 1.65MB PDF 举报

"jOOQ开发手册" jOOQ是一款用于Java的开源ORM工具，它提供了一种声明式、类型安全的方式来构建SQL查询，同时保留了SQL的灵活性和强大功能。与传统的ORM框架如Hibernate不同，jOOQ并不试图完全抽象掉SQL，而是选择将SQL与Java代码紧密结合，提供一种面向对象的查询DSL（领域特定语言），使得开发者能够更加直观地操作数据库。手册的概览部分分为六个主要章节： 1. **开始使用jOOQ** 这一章为初学者提供了快速入门指南，解释了jOOQ的基本概念、它的设计理念以及如何首次配置和设置jOOQ。它将帮助你理解jOOQ如何与数据库交互，以及如何在项目中集成jOOQ。 2. **SQL构建** 在这一部分，jOOQ的查询DSL和查询模型API被详细介绍。这里涵盖了如何使用jOOQ的语法来构建各种查询，包括选择、投影、连接、分组、排序等。此外，还讲解了支持的SQL语句和各种其他语法元素，如子查询、窗口函数和动态SQL。 3. **代码生成** jOOQ的代码生成器是其一大特色，能自动生成与数据库模式对应的Java类。这部分将教你如何配置代码生成器，包括数据库连接设置、生成策略选择以及如何自定义生成模板，以确保生成的代码符合你的项目需求。 4. **SQL执行** 这一章节深入讨论了在运行时使用jOOQ执行查询、进行CRUD操作（创建、读取、更新、删除）的具体细节。还涉及数据导入导出、事务管理以及如何利用jOOQ的执行生命周期进行调试和日志记录。 5. **工具** 这部分介绍了一些辅助工具和实用程序，可能包括jOOQ的命令行工具、集成到IDE的插件和其他可以帮助提高开发效率的特性。 6. **高级主题** 高级主题通常涵盖更复杂或特定的用例，如性能优化、自定义类型映射、多数据库支持、安全性、分布式事务等。这部分内容旨在帮助有经验的开发者充分利用jOOQ的所有功能。通过这个用户手册，开发者不仅可以掌握jOOQ的基本用法，还能深入理解如何利用jOOQ实现高效、可维护的数据库应用。jOOQ的设计理念是使SQL更加易于理解和使用，同时避免了传统ORM框架可能导致的性能问题和SQL晦涩难懂的问题。

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:

剩余301页未读，继续阅读

v?1993

粉丝: 0
资源: 1

jOOQ用户手册：探索SQL构建与代码生成

JOOQ-3.9.0 JAR分享........

jOOQ-manual-3.10.pdf

JOOQ框架详解(文档+代码)

Python异常处理详解-jooq开发手册

Python3.2.3 文件读写教程-jooq开发手册

Python3.2.3官方文档翻译：jooq开发手册中的列表工具解析

JOOQ数据库手册

JOOQ参考手册

jOOQ用户手册：从入门到实战

jOOQ使用手册：SQL构建与执行解析

最新资源