Java object oriented query

JOOQ Object Oriented Querying (jOOQ)

jOOQ is an ORM-alternative that is relational model centric rather than domain model centric like most ORMs. For example, while Hibernate lets you write Java code and then automatically translates it to SQL, jOOQ let’s you write relational objects in your database using SQL and then automatically generates Java code to map to those relational objects.

The writers of jOOQ believe in the power of SQL and assume that you want low level control of the SQL running for your application. This means that you can write your database and your schema without having to worry about how (and if!) it can be handled in Java.

Why Use jOOQ?

While JPA provides a huge framework with a great deal of flexibility and power, it can very quickly become quite complex. jOOQ provides a simpler interface for cases where the developer doesn’t really require all the intricacies and fine tuning tools available with JPA.

Because of the way jOOQ is designed, it becomes very easy to write Java applications on top of an existing database. jOOQ helps you generate all the required classes and object automatically and you are all set go (as demonstrated in the ‘Prominent Features’ section below).

Like Hibernate, database portability is a huge advantage of jOOQ. Again, like Hibernate, typesafety ensures you get to know about errors at compile time rather than at runtime (which is one of the main irritants of JDBC). As opposed to writing SQL in JDBC, you can also enjoy the auto-complete features of your favorite IDE.

And of course, jOOQ is free to use with PostgreSQL (and all other open source databases)!

Prominent Features

Comprehensive documentation around the feature set available with jOOQ is listed on their website. To illustrate a few prominent features here, let’s use the same ‘largecities’ table that we used for HQL in the previous section.

Before starting off, please make sure you have downloaded jOOQ from http://www.jooq.org/download . Also, please ensure that you have the table ‘largecities’ available and data loaded in it.

Generating the Classes

In order to generate the required classes using jOOQ’s command line tools automatically, you will first need to write an XML file that chalks out all the details required by jOOQ. Template for the XML file given below was taken from jOOQ’s website and then filled in for the example we are going to use. Notice the very helpful explanation in comments for each of the required fields.

    org.postgresql.Driver jdbc:postgresql://localhost:5432/postgres postgres     Defaults to org.jooq.util.DefaultGenerator --> org.jooq.util.DefaultGenerator    org.util.[database].[database]Database --> org.jooq.util.postgres.PostgresDatabase   can be the owner, user, database name) to be generated --> public   (A Java regular expression. Use the pipe to separate several expressions) Watch out for case-sensitivity. Depending on your database, this might be important! --> .*   (A Java regular expression. Use the pipe to separate several expressions). Excludes match before includes -->    test.generated  /Users/Shared/workspace   

In order to generate the required classes, you will need to run the org.jooq.util.GenerationTool against this XML file. In order to run this tool, you should have the following jar files in your classpath:

The first three should be a part of the jOOQ download package and you can download the PostgreSQL JDBC driver from http://jdbc.postgresql.org/download.html .

Assuming jOOQ version 3.6.2 and PostgreSQL JDBC version 9.4, and assuming all jar files are available in the current directory with the XML file named largecities.xml, the command to run the tool should look like the following on Linux / OSX:

java -classpath jooq-3.6.2.jar:jooq-meta-3.6.2.jar:jooq-codegen-3.6.2.jar:postgresql-9.4-1201.jdbc4.jar:. org.jooq.util.GenerationTool largecities.xml

and the following on Windows:

java -classpath jooq-3.6.2.jar;jooq-meta-3.6.2.jar;jooq-codegen-3.6.2.jar;postgresql-9.4-1201.jdbc4.jar:. org.jooq.util.GenerationTool largecities.xml

The only difference between the two being usage of ‘ : ’ on Linux/OSX as opposed to using ‘ ; ’ on Windows.

Note that, rather than doing this manually like described above, you can also integrate class generation to your IDE or to your Ant / Maven build configuration.

Once successful, the tool will generate all the required files to the target directory specified in your XML file. You can then include these files to your project.

Establishing the Connection

Establishing a connection to the database is exactly the way you would for standard JDBC connectivity. For our purposes, the following basic class serves the purpose:

import java.sql.*; public class Main  public static void main(String[] args)  String userName = "postgres"; String password = "Password123"; String url = "jdbc:postgresql://localhost:5432/postgres"; try  Connection conn = DriverManager.getConnection(url, userName, password); > catch (Exception e)  e.printStackTrace(); > > >

Reading from the Database

jOOQ has functions available for all basic SQL constructs. This means that a jOOQ read operations looks a lot like the standard SQL query execution. In order to read from the database, all you need to do is get an instance of DSLContext and write a ‘select’ query. Enhancing the code above, we end up with:

import java.sql.*; import org.jooq.*; import org.jooq.impl.DSL; import static test.generated.Tables.LARGECITIES; public class Main  public static void main(String[] args)  String userName = "postgres"; String password = "Password123"; String url = "jdbc:postgresql://localhost:5432/postgres"; try  Connection conn = DriverManager.getConnection(url, userName, password); DSLContext create = DSL.using(conn, SQLDialect.POSTGRES); Result result = create.select().from(LARGECITIES).fetch(); > catch (Exception e)  e.printStackTrace(); > > >

Note, that in order to use the table ‘largecities’ by its name, you will need to statically import the files that were auto-generated by jOOQ’s tool. The import in case of the example above is import static test.generated.Tables.LARGECITIES;

Using the Retrieved Data

Using the actual data is quite similar to the way JDBC handles it, with the added ease of being able to specify table names and columns with type-safety. The following code simply prints out the data to stdout.

import java.sql.*; import org.jooq.*; import org.jooq.impl.DSL; import static test.generated.Tables.*; public class Main  public static void main(String[] args)  String userName = "postgres"; String password = "Password123"; String url = "jdbc:postgresql://localhost:5432/postgres"; try  Connection conn = DriverManager.getConnection(url, userName, password); DSLContext create = DSL.using(conn, SQLDialect.POSTGRES); Result result = create.select().from(LARGECITIES).fetch(); for (Record r : result)  Integer rank = r.getValue(LARGECITIES.RANK); String name = r.getValue(LARGECITIES.NAME); System.out.println("Rank: " + rank + " Name: " + name ); > > catch (Exception e)  e.printStackTrace(); > > >

Output of this program is as follows:

Rank: 1 Name: Tokyo Rank: 2 Name: Seoul Rank: 3 Name: Shanghai Rank: 4 Name: Guangzhou Rank: 5 Name: Karachi Rank: 6 Name: Delhi Rank: 7 Name: Mexico City Rank: 8 Name: Beijing Rank: 9 Name: Lagos Rank: 10 Name: Sao Paulo

Specifying the WHERE Clause

In order to create a filter on the result set , you can specify a WHERE clause in much the same way as other SQL constructs were used in the previous section. To get the top 5 cities, the code becomes:

 Result result = create.select().from(LARGECITIES).where(LARGECITIES.RANK.lessThan(6)).fetch();

After this change, the output of the program is:

Rank: 1 Name: Tokyo Rank: 2 Name: Seoul Rank: 3 Name: Shanghai Rank: 4 Name: Guangzhou Rank: 5 Name: Karachi

Fetching Selected Columns Only

By default, select() captures all the columns from the table. You can pass the columns that you want to retrieve in the select() function in order to limit the scope of your query. As an example, the code below fetches and prints only the names of the cities:

 try  Connection conn = DriverManager.getConnection(url, userName, password); DSLContext create = DSL.using(conn, SQLDialect.POSTGRES); Result> result = create.select(LARGECITIES.NAME).from(LARGECITIES).fetch(); for (Record r : result)  String name = r.getValue(LARGECITIES.NAME); System.out.println(" Name: " + name ); > > catch (Exception e)  e.printStackTrace(); >

 Name: Tokyo Name: Seoul Name: Shanghai Name: Guangzhou Name: Karachi Name: Delhi Name: Mexico City Name: Beijing Name: Lagos Name: Sao Paulo

Drawbacks of Using jOOQ

jOOQ is a nice, lightweight, and easy to use framework when your approach is relational model centric. There are some things to watch out for, however:

• Because jOOQ is designed keeping in mind the relational model centric approach, if your database design is driven by your Java code, jOOQ is probably not the right tool for you. This is especially true if you are writing a new application and are yet to design the database.

• jOOQ expects you to design, optimize, and test your database yourself. It also expects you to modify the database layer as your application matures and changes. If you want to avoid working at the database layer, you should not be using jOOQ.

Share this entry

Источник

Java Object Oriented Querying

Java Object Oriented Querying, commonly known as jOOQ, is a light database-mapping software library in Java that implements the active record pattern. Its purpose is to be both relational and object oriented by providing a domain-specific language to construct queries from classes generated from a database schema.

Contents

Paradigm

jOOQ claims that SQL should come first in any database integration. Thus, it does not introduce a new textual query language, but rather allows for constructing plain SQL from jOOQ objects and code generated from a database schema. jOOQ uses JDBC to call the underlying SQL queries.

While it provides abstraction on top of JDBC, jOOQ does not have as much functionality and complexity as standard object-relational mapping libraries such as Hibernate and JPA.

jOOQ’s closeness to SQL has advantages over typical object-relational mapping libraries. SQL has many features that cannot be used in an object oriented programming paradigm; this set of differences is referred to as the object-relational impedance mismatch. By being close to SQL, jOOQ helps to prevent syntax errors and type mapping problems. Also, variable binding is taken care of. It is also possible in jOOQ to create very complex queries, that involve aliasing, unions, nested selects and complex joins. jOOQ also supports database-specific features, such as UDTs, enum types, stored procedures and native functions.

Example

A nested query selecting from an aliased table

-- Select authors with books that are sold out SELECT * FROM AUTHOR a WHERE EXISTS (SELECT 1 FROM BOOK WHERE BOOK.STATUS = 'SOLD OUT' AND BOOK.AUTHOR_ID = a.ID);

And its equivalent in jOOQ DSL:

// Use the aliased table in the select statement create.selectFrom(table("AUTHOR").as("a")) .where(exists(selectOne() .from(table("BOOK")) .where(field("BOOK.STATUS").equal(field("BOOK_STATUS.SOLD_OUT"))) .and(field("BOOK.AUTHOR_ID").equal(field("a.ID")))));

Or more simply, using code generation from the database metadata to generate constants:

// Use the aliased table in the select statement final Author a = AUTHOR.as("a"); create.selectFrom(a) .where(exists(selectOne() .from(BOOK) .where(BOOK.STATUS.equal(BOOK_STATUS.SOLD_OUT)) .and(BOOK.AUTHOR_ID.equal(a.ID))));

See also

Источник