Introducción a Jooq con Spring

1. Información general

Este artículo presentará las consultas orientadas a objetos de Jooq (Jooq) y una forma sencilla de configurarlo en colaboración con Spring Framework.

La mayoría de las aplicaciones Java tienen algún tipo de persistencia SQL y acceden a esa capa con la ayuda de herramientas de nivel superior como JPA. Y si bien eso es útil, en algunos casos realmente necesita una herramienta más fina y matizada para acceder a sus datos o para aprovechar todo lo que la base de datos subyacente tiene para ofrecer.

Jooq evita algunos patrones ORM típicos y genera código que nos permite crear consultas con seguridad de tipos y obtener un control completo del SQL generado a través de una API fluida, limpia y potente.

Este artículo se centra en Spring MVC. Nuestro artículo Spring Boot Support para jOOQ describe cómo usar jOOQ en Spring Boot.

2. Dependencias de Maven

Las siguientes dependencias son necesarias para ejecutar el código en este tutorial.

2.1. jooq

 org.jooq jooq 3.2.14 

2.2. Primavera

Hay varias dependencias de Spring necesarias para nuestro ejemplo; sin embargo, para simplificar las cosas, solo necesitamos incluir explícitamente dos de ellos en el archivo POM:

 org.springframework spring-context 5.2.2.RELEASE   org.springframework spring-jdbc 5.2.2.RELEASE 

2.3. Base de datos

Para facilitar las cosas con nuestro ejemplo, haremos uso de la base de datos incorporada H2:

 com.h2database h2 1.4.191 

3. Generación de código

3.1. Estructura de la base de datos

Presentemos la estructura de la base de datos con la que trabajaremos a lo largo de este artículo. Supongamos que necesitamos crear una base de datos para que un editor almacene la información de los libros y los autores que administra, donde un autor puede escribir muchos libros y un libro puede ser coescrito por muchos autores.

Para simplificarlo, generaremos solo tres tablas: el libro para libros, autor para autores y otra tabla llamada author_book para representar la relación de muchos a muchos entre autores y libros. La tabla de autor tiene tres columnas: id , first_name y last_name. La tabla del libro contiene solo una columna de título y la clave principal de id .

Las siguientes consultas SQL, almacenadas en el archivo de recursos intro_schema.sql , se ejecutarán en la base de datos que ya hemos configurado antes para crear las tablas necesarias y completarlas con datos de muestra:

DROP TABLE IF EXISTS author_book, author, book; CREATE TABLE author ( id INT NOT NULL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) NOT NULL ); CREATE TABLE book ( id INT NOT NULL PRIMARY KEY, title VARCHAR(100) NOT NULL ); CREATE TABLE author_book ( author_id INT NOT NULL, book_id INT NOT NULL, PRIMARY KEY (author_id, book_id), CONSTRAINT fk_ab_author FOREIGN KEY (author_id) REFERENCES author (id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ab_book FOREIGN KEY (book_id) REFERENCES book (id) ); INSERT INTO author VALUES (1, 'Kathy', 'Sierra'), (2, 'Bert', 'Bates'), (3, 'Bryan', 'Basham'); INSERT INTO book VALUES (1, 'Head First Java'), (2, 'Head First Servlets and JSP'), (3, 'OCA/OCP Java SE 7 Programmer'); INSERT INTO author_book VALUES (1, 1), (1, 3), (2, 1);

3.2. Plugin de propiedades Maven

Usaremos tres complementos de Maven diferentes para generar el código Jooq. El primero de ellos es el complemento Properties Maven.

Este complemento se utiliza para leer datos de configuración de un archivo de recursos. No es necesario ya que los datos se pueden agregar directamente al POM, pero es una buena idea administrar las propiedades externamente.

En esta sección, definiremos las propiedades para las conexiones de la base de datos, incluida la clase del controlador JDBC, la URL de la base de datos, el nombre de usuario y la contraseña, en un archivo llamado intro_config.properties . La externalización de estas propiedades facilita cambiar la base de datos o simplemente cambiar los datos de configuración.

El objetivo de leer propiedades del proyecto de este complemento debe estar vinculado a una fase inicial para que los datos de configuración puedan prepararse para su uso por otros complementos. En este caso, está vinculado a la fase de inicialización :

 org.codehaus.mojo properties-maven-plugin 1.0.0   initialize  read-project-properties    src/main/resources/intro_config.properties     

3.3. Complemento SQL Maven

El complemento SQL Maven se utiliza para ejecutar declaraciones SQL para crear y completar tablas de bases de datos. Hará uso de las propiedades que han sido extraídas del archivo intro_config.properties por el complemento de Propiedades Maven y tomará las declaraciones SQL del recurso intro_schema.sql .

El complemento SQL Maven se configura de la siguiente manera:

 org.codehaus.mojo sql-maven-plugin 1.5   initialize  execute   ${db.driver} ${db.url} ${db.username} ${db.password}  src/main/resources/intro_schema.sql       com.h2database h2 1.4.191   

Tenga en cuenta que este complemento debe colocarse después del complemento Propiedades Maven en el archivo POM, ya que sus objetivos de ejecución están vinculados a la misma fase, y Maven los ejecutará en el orden en que aparecen en la lista.

3.4. Complemento jOOQ Codegen

El complemento Jooq Codegen genera código Java a partir de una estructura de tabla de base de datos. Su objetivo de generación debe estar vinculado a la fase de generación de fuentes para garantizar el orden correcto de ejecución. Los metadatos del complemento se parecen a lo siguiente:

 org.jooq jooq-codegen-maven ${org.jooq.version}   generate-sources  generate    ${db.driver} ${db.url} ${db.username} ${db.password}    com.baeldung.jooq.introduction.db src/main/java      

3.5. Generando código

Para finalizar el proceso de generación de código fuente, necesitamos ejecutar la fase de generación de fuentes de Maven . En Eclipse, podemos hacer esto haciendo clic derecho en el proyecto y eligiendo Ejecutar como -> Maven generate-sources . Una vez completado el comando, se generan los archivos fuente correspondientes al autor , libro , tablas de autor_libro (y varios otros para las clases de apoyo).

Profundicemos en las clases de tablas para ver qué produjo Jooq. Cada clase tiene un campo estático del mismo nombre que la clase, excepto que todas las letras del nombre están en mayúscula. Los siguientes son fragmentos de código tomados de las definiciones de las clases generadas:

La clase de autor :

public class Author extends TableImpl { public static final Author AUTHOR = new Author(); // other class members }

The Book class:

public class Book extends TableImpl { public static final Book BOOK = new Book(); // other class members }

The AuthorBook class:

public class AuthorBook extends TableImpl { public static final AuthorBook AUTHOR_BOOK = new AuthorBook(); // other class members }

The instances referenced by those static fields will serve as data access objects to represent the corresponding tables when working with other layers in a project.

4. Spring Configuration

4.1. Translating jOOQ Exceptions to Spring

In order to make exceptions thrown from Jooq execution consistent with Spring support for database access, we need to translate them into subtypes of the DataAccessException class.

Let's define an implementation of the ExecuteListener interface to convert exceptions:

public class ExceptionTranslator extends DefaultExecuteListener { public void exception(ExecuteContext context) { SQLDialect dialect = context.configuration().dialect(); SQLExceptionTranslator translator = new SQLErrorCodeSQLExceptionTranslator(dialect.name()); context.exception(translator .translate("Access database using Jooq", context.sql(), context.sqlException())); } }

This class will be used by the Spring application context.

4.2. Configuring Spring

This section will go through steps to define a PersistenceContext that contains metadata and beans to be used in the Spring application context.

Let's get started by applying necessary annotations to the class:

  • @Configuration: Make the class to be recognized as a container for beans
  • @ComponentScan: Configure scanning directives, including the value option to declare an array of package names to search for components. In this tutorial, the package to be searched is the one generated by the Jooq Codegen Maven plugin
  • @EnableTransactionManagement: Enable transactions to be managed by Spring
  • @PropertySource: Indicate the locations of the properties files to be loaded. The value in this article points to the file containing configuration data and dialect of the database, which happens to be the same file mentioned in subsection 4.1.
@Configuration @ComponentScan({"com.baeldung.Jooq.introduction.db.public_.tables"}) @EnableTransactionManagement @PropertySource("classpath:intro_config.properties") public class PersistenceContext { // Other declarations }

Next, use an Environment object to get the configuration data, which is then used to configure the DataSource bean:

@Autowired private Environment environment; @Bean public DataSource dataSource() { JdbcDataSource dataSource = new JdbcDataSource(); dataSource.setUrl(environment.getRequiredProperty("db.url")); dataSource.setUser(environment.getRequiredProperty("db.username")); dataSource.setPassword(environment.getRequiredProperty("db.password"));
 return dataSource; }

Now we define several beans to work with database access operations:

@Bean public TransactionAwareDataSourceProxy transactionAwareDataSource() { return new TransactionAwareDataSourceProxy(dataSource()); } @Bean public DataSourceTransactionManager transactionManager() { return new DataSourceTransactionManager(dataSource()); } @Bean public DataSourceConnectionProvider connectionProvider() { return new DataSourceConnectionProvider(transactionAwareDataSource()); } @Bean public ExceptionTranslator exceptionTransformer() { return new ExceptionTranslator(); } @Bean public DefaultDSLContext dsl() { return new DefaultDSLContext(configuration()); }

Finally, we provide a Jooq Configuration implementation and declare it as a Spring bean to be used by the DSLContext class:

@Bean public DefaultConfiguration configuration() { DefaultConfiguration JooqConfiguration = new DefaultConfiguration(); jooqConfiguration.set(connectionProvider()); jooqConfiguration.set(new DefaultExecuteListenerProvider(exceptionTransformer())); String sqlDialectName = environment.getRequiredProperty("jooq.sql.dialect"); SQLDialect dialect = SQLDialect.valueOf(sqlDialectName); jooqConfiguration.set(dialect); return jooqConfiguration; }

5. Using jOOQ With Spring

This section demonstrates the use of Jooq in common database access queries. There are two tests, one for commit and one for rollback, for each type of “write” operation, including inserting, updating, and deleting data. The use of “read” operation is illustrated when selecting data to verify the “write” queries.

We will begin by declaring an auto-wired DSLContext object and instances of Jooq generated classes to be used by all testing methods:

@Autowired private DSLContext dsl; Author author = Author.AUTHOR; Book book = Book.BOOK; AuthorBook authorBook = AuthorBook.AUTHOR_BOOK;

5.1. Inserting Data

The first step is to insert data into tables:

dsl.insertInto(author) .set(author.ID, 4) .set(author.FIRST_NAME, "Herbert") .set(author.LAST_NAME, "Schildt") .execute(); dsl.insertInto(book) .set(book.ID, 4) .set(book.TITLE, "A Beginner's Guide") .execute(); dsl.insertInto(authorBook) .set(authorBook.AUTHOR_ID, 4) .set(authorBook.BOOK_ID, 4) .execute();

A SELECT query to extract data:

Result
    
      result = dsl .select(author.ID, author.LAST_NAME, DSL.count()) .from(author) .join(authorBook) .on(author.ID.equal(authorBook.AUTHOR_ID)) .join(book) .on(authorBook.BOOK_ID.equal(book.ID)) .groupBy(author.LAST_NAME) .fetch();
    

The above query produces the following output:

+----+---------+-----+ | ID|LAST_NAME|count| +----+---------+-----+ | 1|Sierra | 2| | 2|Bates | 1| | 4|Schildt | 1| +----+---------+-----+

The result is confirmed by the Assert API:

assertEquals(3, result.size()); assertEquals("Sierra", result.getValue(0, author.LAST_NAME)); assertEquals(Integer.valueOf(2), result.getValue(0, DSL.count())); assertEquals("Schildt", result.getValue(2, author.LAST_NAME)); assertEquals(Integer.valueOf(1), result.getValue(2, DSL.count()));

When a failure occurs due to an invalid query, an exception is thrown and the transaction rolls back. In the following example, the INSERT query violates a foreign key constraint, resulting in an exception:

@Test(expected = DataAccessException.class) public void givenInvalidData_whenInserting_thenFail() { dsl.insertInto(authorBook) .set(authorBook.AUTHOR_ID, 4) .set(authorBook.BOOK_ID, 5) .execute(); }

5.2. Updating Data

Now let's update the existing data:

dsl.update(author) .set(author.LAST_NAME, "Baeldung") .where(author.ID.equal(3)) .execute(); dsl.update(book) .set(book.TITLE, "Building your REST API with Spring") .where(book.ID.equal(3)) .execute(); dsl.insertInto(authorBook) .set(authorBook.AUTHOR_ID, 3) .set(authorBook.BOOK_ID, 3) .execute();

Get the necessary data:

Result
    
      result = dsl .select(author.ID, author.LAST_NAME, book.TITLE) .from(author) .join(authorBook) .on(author.ID.equal(authorBook.AUTHOR_ID)) .join(book) .on(authorBook.BOOK_ID.equal(book.ID)) .where(author.ID.equal(3)) .fetch();
    

The output should be:

+----+---------+----------------------------------+ | ID|LAST_NAME|TITLE | +----+---------+----------------------------------+ | 3|Baeldung |Building your REST API with Spring| +----+---------+----------------------------------+

The following test will verify that Jooq worked as expected:

assertEquals(1, result.size()); assertEquals(Integer.valueOf(3), result.getValue(0, author.ID)); assertEquals("Baeldung", result.getValue(0, author.LAST_NAME)); assertEquals("Building your REST API with Spring", result.getValue(0, book.TITLE));

In case of a failure, an exception is thrown and the transaction rolls back, which we confirm with a test:

@Test(expected = DataAccessException.class) public void givenInvalidData_whenUpdating_thenFail() { dsl.update(authorBook) .set(authorBook.AUTHOR_ID, 4) .set(authorBook.BOOK_ID, 5) .execute(); }

5.3. Deleting Data

The following method deletes some data:

dsl.delete(author) .where(author.ID.lt(3)) .execute();

Here is the query to read the affected table:

Result
    
      result = dsl .select(author.ID, author.FIRST_NAME, author.LAST_NAME) .from(author) .fetch();
    

The query output:

+----+----------+---------+ | ID|FIRST_NAME|LAST_NAME| +----+----------+---------+ | 3|Bryan |Basham | +----+----------+---------+

The following test verifies the deletion:

assertEquals(1, result.size()); assertEquals("Bryan", result.getValue(0, author.FIRST_NAME)); assertEquals("Basham", result.getValue(0, author.LAST_NAME));

On the other hand, if a query is invalid, it will throw an exception and the transaction rolls back. The following test will prove that:

@Test(expected = DataAccessException.class) public void givenInvalidData_whenDeleting_thenFail() { dsl.delete(book) .where(book.ID.equal(1)) .execute(); }

6. Conclusion

Este tutorial introdujo los conceptos básicos de Jooq, una biblioteca de Java para trabajar con bases de datos. Cubrió los pasos para generar código fuente a partir de una estructura de base de datos y cómo interactuar con esa base de datos utilizando las clases recién creadas.

La implementación de todos estos ejemplos y fragmentos de código se puede encontrar en un proyecto de GitHub.