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

需积分: 10 38 浏览量更新于2024-07-17 收藏 1.73MB PDF 举报

《jOOQ用户手册》是一本详尽的指南，专为Java开发者设计，旨在帮助他们更高效地处理和操作SQL（Structured Query Language）数据库。该手册由DataGeekery GmbH于2009年至2018年期间出版，共分为六个核心部分。 1. **入门快速上手**：这部分为初学者提供了一个简洁的入门路径，解释了什么是jOOQ，它并非简单地抽象SQL，而是将SQL的力量和关系数据的优美性保持在可见范围内。它着重强调jOOQ作为一款工具的目的，即让开发者能直接使用SQL语法，而不是局限于传统ORM（Object-Relational Mapping）框架的局限性。 2. **SQL构建**：这是手册的核心内容，详细介绍jOOQ的查询构建语言（query DSL）和查询模型API。读者可以在这里学习如何使用工厂方法来构造复杂的SQL语句，涵盖了各种SQL语句支持，如SELECT、INSERT、UPDATE和DELETE等，以及语法元素的深入解析。 3. **代码生成**：这部分涉及如何配置和使用jOOQ内置的源代码生成器，使开发者能够自动化地从数据库元数据生成符合项目需求的Java代码，极大提高了开发效率和代码质量一致性。 4. **SQL执行与操作**：这一节指导用户如何在运行时利用jOOQ进行实际的数据库操作，包括执行查询、执行CRUD（Create, Read, Update, Delete）操作，数据导入导出，以及如何通过jOOQ的执行生命周期管理功能进行调试。 5. **工具支持**：手册的最后一部分介绍了相关的辅助工具，可能包括IDE插件、命令行工具或其他集成解决方案，这些工具能进一步提升开发者的开发体验和工作效率。《jOOQ用户手册》为开发者提供了一套完整的、直观的工具集，帮助他们在处理复杂数据库操作时保持SQL的灵活性和代码的可读性，从而优化了Java应用与数据库的交互。无论是初次接触jOOQ的开发者还是经验丰富的技术专家，都能从中找到适合自己的学习和实践资源。

The jOOQ User Manual 2. Copyright, License, and Trademarks

See the following website for details about contributing to jOOQ:

http://www.jooq.org/legal/contributions

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:

剩余318页未读，继续阅读

caofeng891102

粉丝: 172
资源: 1258

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

jooq入门教程

jocky使用说明

JOOQ数据库手册

jOOQ-manual-3.10.pdf

jooq-study:springboot jooq

quarkus-jooq:Quarkus Jooq扩展

JOOQ参考手册

JOOQ jar包

jOOQ.zip

jOOQ开发手册

最新资源