jOOQ用户手册：Java ORM与原生SQL的完美结合

需积分: 9 56 浏览量更新于2024-07-17 收藏 1.74MB PDF 举报

jOOQ是一个专为Java开发者设计的轻量级数据库访问工具包，它旨在提供一种既能保持SQL的灵活性又能利用面向对象编程的优雅方式来操作关系型数据库。相比于传统的ORM框架，jOOQ更像是一种SQL与ORM之间的桥梁，允许开发人员直接使用Java代码编写和执行SQL语句，从而满足那些喜欢直接操控SQL的程序员的需求，被誉为ORM的强有力替代选择。《jOOQ用户手册》共分为六个主要部分： 1. **快速入门**：这部分的目标是帮助新用户快速上手jOOQ。它会介绍jOOQ的基本概念，明确它不是什么（如不试图将SQL完全抽象），并提供初次使用jOOQ的设置指南。 2. **SQL构建**：这是手册的核心部分，详述了通过查询dsl（Query DSL）和查询模型API构建查询的方法。这部分详细讲解了核心工厂的使用，支持的各种SQL语句，以及其他关键语法元素，让读者掌握如何构建复杂的SQL查询。 3. **代码生成**：本章节解释了如何配置和使用内置的源代码生成器，使jOOQ能够自动生成与数据库表结构对应的Java类和方法，提高了开发效率和代码的可维护性。 4. **SQL执行**：这部分深入探讨了在运行时如何利用jOOQ执行查询、执行CRUD操作（Create, Read, Update, Delete）、数据导入导出，以及如何利用jOOQ执行生命周期管理进行调试。这部分内容对实际应用中的数据库操作至关重要。 5. **工具**：这一部分可能涵盖了与jOOQ相关的辅助工具，如图形界面工具、插件或是用于特定场景的扩展，帮助用户优化开发体验和提高工作效率。 6. **概述**：最后的概述总结了整个手册的主要内容和目标，强调了jOOQ如何平衡SQL的灵活性和面向对象编程的简洁性，以满足现代开发环境中对数据库操作的高效需求。通过阅读这本手册，无论是初学者还是经验丰富的开发人员，都能找到适应自己工作流的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页未读，继续阅读

般罗

粉丝: 0
资源: 2

jOOQ用户手册：Java ORM与原生SQL的完美结合

JOOQ参考手册

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

jOOQ-manual-3.10.pdf

springboot3 整合jooq

介绍一下java jooq

springboot整合druid、jooq

jooq where in 多个条件查询

jooq insert 批量操作

JOOQ的resultQuery

jooq 多数据库事务

最新资源