jOOQ 3.10 SQL开发指南：从入门到实战

需积分: 9 10 浏览量更新于2024-07-16 收藏 1.64MB PDF 举报

"jOOQ-manual-3.10.pdf" 是一份详细的jOOQ使用手册，针对Java开发者提供了关于如何在不局限于ORM（对象关系映射）框架的情况下，利用jOOQ进行高级SQL编程的全面指南。该手册由DataGeekery GmbH于2009年至2017年期间发布，共分为六个主要部分： 1. **入门快速指南**：这部分旨在帮助读者快速上手jOOQ。它包括对jOOQ基本概念的介绍，比如它不是ORM工具，而是强调SQL本身的表达力和灵活性。此外，还介绍了如何初次设置和配置jOOQ环境。 2. **SQL构建**：这一章节是手册的核心内容，讲解了jOOQ的查询构造器（query DSL）和查询模型API的使用。它详细阐述了核心工厂的用法，涵盖了各种SQL语句，如SELECT、INSERT、UPDATE和DELETE等，以及如何构建复杂的SQL表达式和条件。 3. **代码生成**：这部分教程指导用户如何配置和使用jOOQ内置的源代码生成器。通过这种方式，开发者可以自动生成符合特定数据库模式的Java代码，简化数据库操作和表操作类的编写，提高开发效率。 4. **SQL执行**：这部分深入讨论了在运行时使用jOOQ的实际操作，例如执行查询、执行CRUD（Create, Read, Update, Delete）操作，数据导入导出，以及如何监控和调试jOOQ的执行流程，这对于理解和优化性能至关重要。 5. **工具**：这部分可能涉及与jOOQ相关的辅助工具，如IDE插件、命令行工具或可视化工具，这些工具能够提升开发者的开发体验和工作流程。 6. **概览**：最后的概述总结了整个手册的主要内容，帮助读者快速回顾并找到所需的信息。通过阅读这份手册，Java开发者能够深入了解如何利用jOOQ的强大功能来处理SQL任务，提高代码质量，同时保持与底层数据库的紧密联系，避免了ORM框架可能导致的抽象过度。无论是初学者还是经验丰富的开发者，都能从中受益匪浅。

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:

The jOOQ User Manual 3.3.4. jOOQ for CRUD

// Use your favourite tool to construct SQL strings:

String sql = "SELECT title, first_name, last_name FROM book JOIN author ON book.author_id = author.id " +

"WHERE book.published_in = 1984";

// Fetch results using jOOQ

Result<Record> result = create.fetch(sql);

// Or execute that SQL with JDBC, fetching the ResultSet with jOOQ:

ResultSet rs = connection.createStatement().executeQuery(sql);

Result<Record> result = create.fetch(rs);

If you wish to use jOOQ as a SQL executor with (or without) 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

- SQL execution: This section contains a lot of information about executing SQL statements using

the jOOQ API

- Fetching: This section contains some useful information about the various ways of fetching data

with jOOQ

3.3.4. jOOQ for CRUD

This is probably the most complete use-case for jOOQ: Use all of jOOQ's features. Apart from jOOQ's

fluent API for query construction, jOOQ can also help you execute everyday CRUD operations. An

example is given here:

// Fetch all authors

for (AuthorRecord author : create.fetch(AUTHOR)) {

// Skip previously distinguished authors

if ((int) author.getDistinguished() == 1)

continue;

// Check if the author has written more than 5 books

if (author.fetchChildren(Keys.FK_BOOK_AUTHOR).size() > 5) {

// Mark the author as a "distinguished" author

author.setDistinguished(1);

author.store();

}

If you wish to use all of jOOQ's features, the following sections of the manual will be of interest to you

(including all sub-sections):

- 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

- SQL execution: This section contains a lot of information about executing SQL statements using

the jOOQ API

剩余298页未读，继续阅读

chenclqqrj

粉丝: 1
资源: 1

jOOQ 3.10 SQL开发指南：从入门到实战

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

jooq-meta-extensions-3.10.7.jar

jooq-codegen-2.5.1.zip

jooq-codegen-maven-3.10.7.jar

jooq-cdi-codegen-2.2.zip

jooq-codegen-maven-3.12.4.jar

comsat-jooq-0.2.0.zip

jooq-cache-demo:关于 jooq-extended 和 jooq-cache 如何协同工作的展示

Java开发案例-springboot-45-整合JOOQ操作SQL-源代码+文档.rar

jooq-with-spring-examples

最新资源