探索jOOQ：类型安全的数据库操作指南

需积分: 28 120 浏览量更新于2024-07-21 收藏 1.37MB PDF 举报

"JOOQ数据库手册" JOOQ（Java Object Oriented Querying）是一款针对Java的开源数据库操作库，它提供了一种类型安全的方式来构建和执行SQL查询，旨在提高开发人员在处理数据库时的效率和代码质量。JOOQ不试图完全抽象SQL，而是保留了SQL的美丽和简洁性，同时提供了面向对象的接口来更方便地进行数据库交互。在JOOQ用户手册中，主要分为六个部分： 1. **开始使用JOOQ** 这一章节为初次接触JOOQ的开发者提供了快速入门指南。它会介绍JOOQ的基本概念、其与其它ORM框架的区别，以及如何首次配置和设置JOOQ。这部分内容将帮助你理解JOOQ的核心价值，并能够快速在项目中集成使用。 2. **SQL构建** 这部分深入探讨了JOOQ的查询DSL（领域特定语言）和查询模型API。通过这些工具，你可以构建复杂的SQL语句，包括选择、连接、分组、排序等。它详细介绍了中央工厂类、支持的SQL语句以及各种语法元素，使你能够灵活地创建和定制查询。 3. **代码生成** JOOQ提供了内置的源代码生成器，可以自动生成与数据库模式对应的Java类。这部分将指导你如何配置和使用这个工具，以生成类型安全的Java对象，这些对象可以直接映射到数据库表和列，从而减少手动编写和维护数据库访问代码的工作量。 4. **SQL执行** 在这一章，你会了解到在运行时使用JOOQ执行查询、执行CRUD（创建、读取、更新、删除）操作、导入和导出数据的具体细节。JOOQ提供了丰富的API来处理数据库交互，并允许你在执行生命周期中插入调试点，以更好地控制和理解查询执行的过程。 5. **事务管理** JOOQ支持事务处理，使你能够在需要时轻松地开始、提交、回滚事务，确保数据的一致性和完整性。这部分会详细讲解如何在JOOQ中进行事务控制。 6. **错误处理和日志记录** 在实际开发中，错误处理和日志记录是必不可少的。JOOQ提供了相应的机制来捕获和处理数据库操作中的异常，以及配置日志输出，以帮助开发者追踪和解决可能出现的问题。 JOOQ数据库手册是一个全面的指南，无论你是初学者还是经验丰富的开发人员，都能从中受益。通过学习这个手册，你将能够熟练地使用JOOQ来提高数据库操作的效率和代码质量，同时保持对SQL的强大控制力。

The jOOQ User Manual 3.3.1. jOOQ as a SQL builder

- 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.

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").equal(field("AUTHOR.ID")))

.where(field("BOOK.PUBLISHED_IN").equal(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.

If you wish to use jOOQ only as a SQL builder, 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

- 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.equal(AUTHOR.ID))

.where(BOOK.PUBLISHED_IN.equal(1948))

.getSQL();

The jOOQ User Manual 3.3.3. jOOQ as a SQL executor

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.equal(AUTHOR.ID))

.where(BOOK.PUBLISHED_IN.equal(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:

// 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

The jOOQ User Manual 3.3.4. jOOQ for CRUD

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

3.3.5. jOOQ for PROs

jOOQ isn't just a library that helps you build and execute SQL against your generated, compilable

schema. jOOQ ships with a lot of tools. Here are some of the most important tools shipped with jOOQ:

- jOOQ's Execute Listeners: jOOQ allows you to hook your custom execute listeners into jOOQ's

SQL statement execution lifecycle in order to centrally coordinate any arbitrary operation

performed on SQL being executed. Use this for logging, identity generation, SQL tracing,

performance measurements, etc.

- Logging: jOOQ has a standard DEBUG logger built-in, for logging and tracing all your executed

SQL statements and fetched result sets

- Stored Procedures: jOOQ supports stored procedures and functions of your favourite database.

All routines and user-defined types are generated and can be included in jOOQ's SQL building

API as function references.

- Batch execution: Batch execution is important when executing a big load of SQL statements.

jOOQ simplifies these operations compared to JDBC

- Exporting and Importing: jOOQ ships with an API to easily export/import data in various formats

If you're a power user of your favourite, feature-rich database, jOOQ will help you access all of your

database's vendor-specific features, such as OLAP features, stored procedures, user-defined types,

vendor-specific SQL, functions, etc. Examples are given throughout this manual.

The jOOQ User Manual 3.4.1.3. Step 3: Code generation

CREATE DATABASE `library`;

USE `library`;

CREATE TABLE `author` (

`id` int NOT NULL,

`first_name` varchar(255) DEFAULT NULL,

`last_name` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

);

3.4.1.3. Step 3: Code generation

In this step, we're going to use jOOQ's command line tools to generate classes that map to the Author

table we just created. More detailed information about how to set up the jOOQ code generator can

be found here:

jOOQ manual pages about setting up the code generator

The easiest way to generate a schema is to copy the jOOQ jar files (there should be 3) and the MySQL

Connector jar file to a temporary directory. Then, create a library.xml that looks like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<jdbc>

<driver>com.mysql.jdbc.Driver</driver>

<url>jdbc:mysql://localhost:3306/library</url>

</jdbc>

<!-- The default code generator. You can override this one, to generate your own code style

Defaults to org.jooq.util.DefaultGenerator -->

<name>org.jooq.util.DefaultGenerator</name>

<!-- The database type. The format here is:

org.util.[database].[database]Database -->

<name>org.jooq.util.mysql.MySQLDatabase</name>

<!-- The database schema (or in the absence of schema support, in your RDBMS this

can be the owner, user, database name) to be generated -->

<inputSchema>library</inputSchema>

<!-- All elements that are generated from your schema

(A Java regular expression. Use the pipe to separate several expressions)

Watch out for case-sensitivity. Depending on your database, this might be important! -->

<!-- All elements that are excluded from your schema

(A Java regular expression. Use the pipe to separate several expressions).

Excludes match before includes -->

</database>

<packageName>test.generated</packageName>

<directory>C:/workspace/MySQLTest/src</directory>

</target>

</generator>

</configuration>

Replace the username with whatever user has the appropriate privileges to query the database meta

data. You'll also want to look at the other values and replace as necessary. Here are the two interesting

properties:

generator.target.package - set this to the parent package you want to create for the generated classes.

The setting of test.generated will cause the test.generated.Author and test.generated.AuthorRecord to

be created

generator.target.directory - the directory to output to.

剩余241页未读，继续阅读

clonegod1

粉丝: 0
资源: 1

探索jOOQ：类型安全的数据库操作指南

context-mapper-dsl：ContextMapper DSL：用于上下文映射和服务分解的特定于域的语言

quarkus-jooq:Quarkus Jooq扩展

JOOQ参考手册

使用jooq操作数据库，不需要close数据库连接吗

jooq 多数据库事务

使用jooq操作数据库，不需要close掉statement吗吗

jooq 多数据库事务回滚

介绍一下java jooq

jooq多数据源代码生成配置

springboot整合druid、jooq

最新资源