One of the main features of ORMs is M as in Mapping. Libraries like jOOQ help auto-mapping flat or nested database records onto Java classes that have the same structure as the SQL result set.
The following has always been possible in jOOQ, assuming PostgreSQL’s INFORMATION_SCHEMA (using the generated code from the jOOQ-meta module):
class Column {
String tableSchema;
String tableName;
String columnName;
}
for (Column c :
ctx.select(
COLUMNS.TABLE_SCHEMA,
COLUMNS.TABLE_NAME,
COLUMNS.COLUMN_NAME)
.from(COLUMNS)
.where(COLUMNS.TABLE_NAME.eq("t_author"))
.orderBy(COLUMNS.ORDINAL_POSITION)
.fetchInto(Column.class))
System.out.println(
c.tableSchema + "." + c.tableName + "." + c.columnName
);
The above resulting in something like:
public.t_author.id public.t_author.first_name public.t_author.last_name public.t_author.date_of_birth public.t_author.year_of_birth public.t_author.address
The mapping is straight forward, as explained in jOOQ’s DefaultRecordMapper.
Nested mappings
A lesser known feature that we’ve offered for a while was to use a dot notation to emulate nesting records into nested Java classes. Assuming you want to use a re-usable data type description in your columns and elsewhere:
class Type {
String name;
int precision;
int scale;
int length;
}
class Column {
String tableSchema;
String tableName;
String columnName;
Type type;
}
You can now write this query where you’ll alias some columns using the dot notation to type.name, for example (several nesting levels are possible):
for (Column c :
ctx.select(
COLUMNS.TABLE_SCHEMA,
COLUMNS.TABLE_NAME,
COLUMNS.COLUMN_NAME,
COLUMNS.DATA_TYPE.as("type.name"),
COLUMNS.NUMERIC_PRECISION.as("type.precision"),
COLUMNS.NUMERIC_SCALE.as("type.scale"),
COLUMNS.CHARACTER_MAXIMUM_LENGTH.as("type.length")
)
.from(COLUMNS)
.where(COLUMNS.TABLE_NAME.eq("t_author"))
.orderBy(COLUMNS.ORDINAL_POSITION)
.fetchInto(Column.class))
System.out.println(String.format(
"%1$-30s: %2$s",
c.tableSchema + "." + c.tableName + "." + c.columnName,
c.type.name + (c.type.precision != 0
? "(" + c.type.precision + ", " + c.type.scale + ")"
: c.type.length != 0
? "(" + c.type.length + ")"
: "")
));
The above will print:
public.t_author.id : integer(32, 0) public.t_author.first_name : character varying(50) public.t_author.last_name : character varying(50) public.t_author.date_of_birth : date public.t_author.year_of_birth : integer(32, 0) public.t_author.address : USER-DEFINED
Using XML or JSON
Using XML or JSON, starting from jOOQ 3.14, you can also nest collections in your result set mapping very easily. First, let’s look again at how to use a JSON query using jOOQ, e.g. to find all columns per table:
for (Record1<JSON> record :
ctx.select(
jsonObject(
jsonEntry("tableSchema", COLUMNS.TABLE_SCHEMA),
jsonEntry("tableName", COLUMNS.TABLE_NAME),
jsonEntry("columns", jsonArrayAgg(
jsonObject(
jsonEntry("columnName", COLUMNS.COLUMN_NAME),
jsonEntry("type", jsonObject(
"name", COLUMNS.DATA_TYPE)
)
)
).orderBy(COLUMNS.ORDINAL_POSITION))
)
)
.from(COLUMNS)
.where(COLUMNS.TABLE_NAME.in("t_author", "t_book"))
.groupBy(COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME)
.orderBy(COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME)
.fetch())
System.out.println(record.value1());
The following JSON documents are returned:
{
"tableSchema": "public",
"tableName": "t_author",
"columns": [{
"columnName": "id",
"type": {"name": "integer"}
}, {
"columnName": "first_name",
"type": {"name": "character varying"}
}, {...}]
}
{
"tableSchema": "public",
"tableName": "t_book",
"columns": [{...}, ...]
}
That’s already awesome, isn’t it? We’ve blogged about this previously here and here. Starting with jOOQ 3.14, you can remove all the other middleware and mapping and what not, and produce your XML or JSON documents directly from your database using standard SQL/XML or SQL/JSON API!
But that’s not all!
Maybe, you don’t actually need the JSON document, you just want to use JSON to allow for nesting data structures, mapping them back to Java.
What about these nested Java classes:
public static class Type {
public String name;
}
public static class Column {
public String columnName;
public Type type;
}
public static class Table {
public String tableSchema;
public String tableName;
public List<Column> columns;
}
Assuming you have gson or Jackson or JAXB on your classpath (or you configure them directly), you can write the exact same query as before, and use jOOQ’s DefaultRecordMapper using the fetchInto(Table.class) call:
for (Table t :
ctx.select(
jsonObject(
jsonEntry("tableSchema", COLUMNS.TABLE_SCHEMA),
jsonEntry("tableName", COLUMNS.TABLE_NAME),
jsonEntry("columns", jsonArrayAgg(
jsonObject(
jsonEntry("columnName", COLUMNS.COLUMN_NAME),
jsonEntry("type", jsonObject(
"name", COLUMNS.DATA_TYPE)
)
)
).orderBy(COLUMNS.ORDINAL_POSITION))
)
)
.from(COLUMNS)
.where(COLUMNS.TABLE_NAME.in("t_author", "t_book"))
.groupBy(COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME)
.orderBy(COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME)
.fetchInto(Table.class))
System.out.println(t.tableName + ":\n" + t.columns
.stream()
.map(c -> c.columnName + " (" + c.type.name + ")")
.collect(joining("\n ")));
The output being:
t_author: id (integer) first_name (character varying) last_name (character varying) date_of_birth (date) year_of_birth (integer) address (USER-DEFINED) t_book: id (integer) author_id (integer) co_author_id (integer) details_id (integer) title (character varying) published_in (integer) language_id (integer) content_text (text) content_pdf (bytea) status (USER-DEFINED) rec_version (integer) rec_timestamp (timestamp without time zone)
No join magic. No cartesian products. No data deduplication. Just SQL-native nested collections, using an intuitive, declarative approach to creating the document data structure, combined with the usual awesomeness of SQL.
Using this without the jOOQ DSL
Of course, this also works without the jOOQ API, e.g. using our parser. Check out our translator tool. Plug in this native SQL beauty:
SELECT
json_object(
KEY 'tableSchema' VALUE columns.table_schema,
KEY 'tableName' VALUE columns.table_name,
KEY 'columns' VALUE json_arrayagg(
json_object(
KEY 'columnName' VALUE columns.column_name,
KEY 'type' VALUE json_object(
KEY 'name' VALUE columns.data_type
)
)
)
)
FROM columns
WHERE columns.table_name IN ('t_author', 't_book')
GROUP BY columns.table_schema, columns.table_name
ORDER BY columns.table_schema, columns.table_name
And, because the devil of SQL agnosticity and translation is in the detail, take out the vendor-specific version, e.g. for PostgreSQL:
SELECT json_build_object(
'tableSchema', columns.table_schema,
'tableName', columns.table_name,
'columns', json_agg(json_build_object(
'columnName', columns.column_name,
'type', json_build_object('name', columns.data_type)
))
)
FROM columns
WHERE columns.table_name IN (
't_author', 't_book'
)
GROUP BY
columns.table_schema,
columns.table_name
ORDER BY
columns.table_schema,
columns.table_name
You might need to run this, before:
SET search_path = 'information_schema'
Conclusion
We’ve waited way too long with this game changing feature. I truly think this approach will change how we perceive ORMs in the future. The database first approach, where we can use SQL and only SQL to map SQL data onto any hierarchical data structure is very commpelling.
On the jOOQ side, we’re far from done yet. What if we can auto-generate some of the JSON document declaration from other types of meta data for you? What if you can do that, yourself? E.g. to map a GraphQL specification to jOOQ API based JSON queries? On all the SQL dialects that support these features!
The future of mapping nested data structures from SQL to any client, XML, JSON, objects is bright. jOOQ 3.14 is around the corner and will be released within the next 2 weeks. You can already build it from github: https://github.com/jOOQ/jOOQ, or if you have a license, download a nightly build from here: https://www.jooq.org/download/versions
Looking forward to your feedback.
from Java, SQL and jOOQ. https://ift.tt/2SHQfIs
via IFTTT
No comments:
Post a Comment