Java object oriented querying

Object Oriented Querying (jOOQ) with Java and Spring Boot.

This time I want to try the same with Object Oriented Querying, commonly known as jOOQ.

Overview

jOOQ is a popular Java database library, that lets you write typesafe SQL queries in Java. How does it work?

1. You use jOOQ’s code generator to connect to your database and generate Java classes that model your database tables and columns.
2. Instead of writing SQL String statements with plain JDBC, you will use those generated Java classes to write your SQL queries.
3. jOOQ will conveniently turn these Java classes and queries into real SQL, execute them against the database and map the results back to Java code.

DB Preparation

First, we need to create a few tables in our database. We can use almost the same table structure as before to cover most specific cases. The script below will create three tables to represent some football data like team, player, and statistics.

We can insert any dummy data to our tables to be able to have something to query upon.

Library preparation

To be able to use the library we need to add dependency and generator parts to our build files. For the generator part, it is recommended to use the Gradle plugin by Etienne Studer (from Gradle Inc.). It provides a concise DSL that allows you to tune all configuration properties supported by each jOOQ version.

Our resulting build file will look something like this:

There is also an H2 (in memory DB) dependency to test our application without starting a real DB server.

The jOOQ’s code generator takes our database schema and reverse-engineers it into a set of Java classes modeling tables, records, sequences, POJOs, DAOs, stored procedures, user-defined types, and many more.

Simple queries

Let’s start with basic queries of inserting and fetching teams

Notice the DSLContext declaration. We can simply inject it as a bean in spring boot and use it to communicate with our DB layer using the JOOQ-generated classes. For the example above we declared two methods: one for getting all the teams from Table TEAM and mapping them to Team class which was generated by jOOQ and another to insert a new team into the database.

Joins

Here we have simple joins on two tables to fetch players and corresponding teams. The two syntaxes will produce the same SQL statement.

In the next snapshot, we are joining three tables to get the result.

One can already see the huge potential of using jOOQ as a stream and combining different filters. For example, to have multiple conditions on a query we can simply connect the predicates like:

Here we join three tables (player, team, and statistics) and get all the players whose team is from England and who scored more than 10 goals in less than 100 matches.

The benefits

The benefits of the jOOQ code generator and processing language are quite big and numerous to list a few:

• All tables and columns are types checked by your Java compiler.
• You can use IDE auto-completion on your schema objects.
• You never run into SQL injection problems or syntax errors.
• Your code stops compiling as soon as you rename a column, or change a data type, etc.
• Converters and data type bindings are super easy
• Fancy features like implicit joins are type-safe, too

The conclusion

I would recommend you give jOOQ a try if you are looking for a lightweight Java persistence framework. It lets Java Programmers write type-safe SQL queries by accessing the database. jOOQ generates the SQL statements from Java code with the help of their fluent API for building the queries. It brings a lot of benefits out of the box the only downside from my perspective (which could be biased as I dislike any code generators) is that to fully use all the cool features — the code generator is a must, but still, I would definitely use it in next projects as benefits are just too big to ignore.

Keep coding and have one.

Level Up Coding

Thanks for being a part of our community! Before you go:

• 👏 Clap for the story and follow the author 👉
• 📰 View more content in the Level Up Coding publication
• 💰 Free coding interview course ⇒ View Course
• 🔔 Follow us: Twitter | LinkedIn | Newsletter

Источник

Пишем автотесты с умом

JOOQ (Java Object Oriented Querying) — open source инструмент с кодогенерацией для работы с SQL в Java. Из коробки JOOQ предоставляет удобный DSL для составления запросов, а также генератор классов на основе метаданных ДБ.

Как это выглядит?

INSERT INTO Table (Field, AnotherField) VALUES ('value1', 'value2'); 

db.insertInto(TABLE) .set(TABLE.FIELD, "value1") .set(TABLE.ANOTHER_FIELD, "value2") .execute(); 

SELECT * FROM Table WHERE Field = 'value1' AND AnotherField = 'value2' 

Record result = db.select().from(TABLE) .where(TABLE.FIELD.equal("value1") .and(TABLE.ANOTHER_FIELD.equal("value2"))).fetchAny(); 

Мапинг

При этом JOOQ умеет мапить получившейся записи, например result из предыдущего класса можно замапить на Table.class

Table resultTable = result.into(Table.class); 

Еще

 TableAssert.assertThat(resultTable) .hasField(expectedField).hasAnotherField(expectedAnotherField); 

Чем нравится

Минусы

Из минусов можно отметить, что далеко не все БД поддерживаются, при этом поддержка большинства БД платно

Источник

jOOQ – Java SQL Generator

Overall, jOOQ is a powerful tool that can help developers write better SQL queries in Java. Its type safety, expressiveness, integration with Java, portability, and code generation capabilities make it a popular choice among Java developers.

2. jOOQ – Java SQL Generator

Let us take a look at the practical implementation of jOOQ.

2.1 Dependencies

Add the jOOQ dependency to your project: You can add it to your project using a build tool such as Maven or Gradle. Here is an example of Maven dependency:

2.2 Code generation

You need to generate jOOQ classes based on your database schema. You can use the jOOQ code generation tool to do this. The generated classes will be used to execute queries against your database. You can write jOOQ code to execute queries against your database using the generated jOOQ classes. Here is an example query:

DSLContext dslContext = DSL.using(connection, SQLDialect.MYSQL); Result result = dslContext.select().from(«mytable»).fetch(); for (Record r : result)

In this example, DSLContext is the entry point for executing jOOQ queries. connection is a JDBC connection to your database, and SQLDialect.MYSQL specifies the SQL dialect of your database. select() creates a SelectQuery object, and from(«mytable») specifies the table to select from. fetch() executes the query and returns the result as a Result
object. You can then iterate over the Result and retrieve values using the getValue() method.

2.3 Complete code

Here’s an example Java code for using jOOQ to execute a query against a PostgreSQL database:

import org.jooq.*; import org.jooq.impl.*; import java.sql.*; public class JooqExample < public static void main(String[] args) < String url = "jdbc:postgresql://localhost:5432/mydatabase"; String username = "postgres"; String password = "password"; Connection conn = null; try < conn = DriverManager.getConnection(url, username, password); DSLContext dsl = DSL.using(conn, SQLDialect.POSTGRES); Resultresult = dsl.select().from("mytable").fetch(); for (Record r : result) < int Integer.class); String name = r.getValue("name", String.class); System.out.println("id: " + id + ", name: " + name); >> catch (SQLException e) < e.printStackTrace(); >finally < if (conn != null) < try < conn.close(); >catch (SQLException e) < e.printStackTrace(); >> > > >

In this example, we first establish a connection to a PostgreSQL database using the DriverManager.getConnection() method. We then create a DSLContext object using the connection and specify the SQL dialect (in this case, PostgreSQL). We execute a simple SELECT query using the select() and from() methods and retrieve the result as a Result object. Finally, we iterate over the result and print out the values of each row. The output of this code will be the id and name columns data in the “mytable” table in the “mydatabase” PostgreSQL database.

id: 101, name: Raj id: 102, name: Shyam id: 103, name: Rakesh id: 103, name: Manoj

Note that you will need to include the appropriate jOOQ and PostgreSQL JDBC driver dependencies in your project’s build path. Additionally, you should use a connection pool and handle errors and exceptions appropriately.

This concludes our tutorial, and I trust that the article provided you with the information you sought. I wish you happy learning and encourage you to share your newfound knowledge with others!

3. Conclusion

In conclusion, using jOOQ in your Java code provides many benefits. By generating Java code from the database schema, it ensures type safety at compile-time, making it easier to catch errors in SQL queries before runtime. jOOQ’s DSL also provides a more concise and readable way of writing SQL queries, allowing developers to write complex queries without the need for verbose SQL syntax. In addition, it integrates seamlessly with Java, making it easy to use in existing Java projects without any additional dependencies or configuration. It also supports multiple SQL dialects, making it easier to write queries that work across different databases. By generating Java code from the database schema, jOOQ saves developers a lot of time and effort, eliminating the need to write boilerplate code for accessing database tables and columns, and ensuring that the generated code is always up-to-date with the database schema.

Overall, jOOQ is a powerful tool that can greatly simplify database access and make your Java code more maintainable, readable, and type-safe. You can download the source code from the Downloads section.

4. Download the Project

This tutorial aimed to provide an understanding of jOOQ and demonstrate how to implement it.

Источник