Easy database queries for Java
Jinq has some preliminary basic support for issuing queries using jOOQ.
To use Jinq with your jOOQ code, you should
Jinq's jOOQ API is designed to provide support for functional-style database queries to jOOQ. It's philosophy is to provide an API that feels similar to jOOQ. This is different from the Jinq's JPA API, which is designed to feel like the Java 8's Stream API or other functional APIs for working with lists of data.
To use Jinq's jOOQ API, you first need to create a jOOQ connection to your database. You can then wrap this connection with a JinqJooqContext object. This object allows you to use Jinq queries with jOOQ.
// Create a JDBC connection to a database Connection con = DriverManager.getConnection( ... /* database connection information */ ); // Use jOOQ to talk to the JDBC connection DSLContext context = DSL.using(con, ... /* the SQLDialect of your database */); // Use Jinq to talk to the jOOQ connection JinqJooqContext jinq = JinqJooqContext.using(context, Schema.SCHEMA /* Your generated schema class */ );
The constructor for JinqJooqContext requires a reference to the schema of your database. jOOQ generates a class containing this schema for your database when you ran the jOOQ code generator to set-up jOOQ. If your generated code was placed in the package com.example.test.generated then you should find a class in that package inheriting from the SchemaImpl class that contains the schema information for your database. This class has a static final variable in it which holds a singleton instance of the schema.
Once you've created a JinqJooqContext object, you can then use this context to write functional style queries for jOOQ.
Jinq currently only supports SELECT..FROM..WHERE queries. A simple query has this form:
List<CityRecord> results = jinq.from(CITY) .where( c -> c.getPopulation() > 1000 ) .selectAll() .toList();
Unlike a normal SQL SELECT..FROM..WHERE query, Jinq uses a different ordering for its statements. In Jinq, from() comes first, then where(), and then select(). In the from(), you list the table that you want to query. In the where(), you supply a function that filters the rows of that table. For its input parameter, this function takes the table record that corresponds to the table you are querying. These table records are normally automatically generated by the jOOQ code generator. If your generated code was placed in the package com.example.test.generated, then the table records can be found in com.example.test.generated.tables.records. Finally, selectAll() will simply return all the resulting records as a stream, and toList() will convert that stream to a list of table records.
Instead of using selectAll(), you can use the select() method to tell Jinq to record only some fields of the table:
List<String> results = jinq.from(CITY) .where( c -> c.getPopulation() > 1000 ) .select( c -> c.getName() ) .toList();
In the above code, only the name of the city is returned. Since the names of the cities are Strings, the result of running the query is a list of Strings.
You can also include more than one table in the from() method, creating a query that joins the two tables:
List<Pair<String, RestaurantRecord> results =
jinq.from(CITY, RESTAURANT)
.where((c, r) -> c.getCityid() == r.getCityid()
&& s.getPopulation() > 1000 )
.select((c, r) -> new Pair<>(c.getName(), r))
.toList();
If you are querying more than one table, then the where() and select() methods will take the corresponding table records as parameters. The above query looks at restaurants in cities. The query joins the CITY and RESTAURANT tables, so the where() method requires a function that takes two parameters: a CityRecord and RestaurantRecord.