Expected behavior
Consider a query returning a jOOQ Record (e.g., CustomerRecord) and the following Spring Boot REST Controller:
@GetMapping("/customer")
public CustomerRecord findAnyCustomer() {
return {some_CustomerRecord};
}
Expecting that the default Spring Boot (Jackson based) serializer will output a JSON containing the CustomerRecord data.
Actual behavior
Initially, it throws an exception: com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.jooq.TableOptions and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) (through reference chain: jooq.generated.tables.records.CustomerRecord["table"]->jooq.generated.tables.Customer["options"])
After setting FAIL_ON_EMPTY_BEANS in application.properties I got a relatively big JSON that looks like this fragment:
{"table":{"name":"customer","comment":"","options":{},"identity":{"table":{"name":"customer","comment":"","options":{},"identity":{"table":{"name":"customer","comment":" ...
Versions
- Spring Boot: 2.3.6.RELEASE
- jOOQ: 3.14.4
- Java: 14 (Spring Boot 2.3.6)
Comment From: wilkinsona
I'm not sure that Spring Boot is in a position to have an opinion about this. Generally speaking, we can't assume that what comes back from the database can be mapped straight to json. That'll work in some cases, but in others there will be a need to rename things, omit things, or change the structure. In the case of JPA, people generally either use a DTO to map an entity to an appropriate representation for serialization to json or they add Jackson annotations to the entity classes.
I'm not sure that adding Jackson annotations to a Record implementation is an option with a jOOQ Record. I can't see any reason why a DTO cannot be used, however. If you are happy with jOOQ's standard JSON representation of a Record then, as @lukaseder alludes to in https://github.com/jOOQ/jOOQ/issues/11203, you could use Formattable.formatJSON to turn a Record into JSON in a custom HttpMessageCoverter:
import java.io.IOException;
import java.util.Collections;
import java.util.List;
import org.jooq.JSONFormat;
import org.jooq.Record;
import org.springframework.http.HttpInputMessage;
import org.springframework.http.HttpOutputMessage;
import org.springframework.http.MediaType;
import org.springframework.http.converter.AbstractHttpMessageConverter;
import org.springframework.http.converter.HttpMessageNotReadableException;
import org.springframework.http.converter.HttpMessageNotWritableException;
public class RecordHttpMessageConverter extends AbstractHttpMessageConverter<Record> {
private static final List<MediaType> SUPPORTED_MEDIA_TYPES = Collections.singletonList(MediaType.APPLICATION_JSON);
private final JSONFormat jsonFormat;
public RecordHttpMessageConverter(JSONFormat jsonFormat) {
this.jsonFormat = jsonFormat;
}
@Override
public boolean canRead(Class<?> clazz, MediaType mediaType) {
return false;
}
@Override
public List<MediaType> getSupportedMediaTypes() {
return SUPPORTED_MEDIA_TYPES;
}
@Override
protected boolean supports(Class<?> clazz) {
return Record.class.isAssignableFrom(clazz);
}
@Override
protected Record readInternal(Class<? extends Record> clazz, HttpInputMessage inputMessage)
throws IOException, HttpMessageNotReadableException {
throw new UnsupportedOperationException();
}
@Override
protected void writeInternal(Record record, HttpOutputMessage outputMessage)
throws IOException, HttpMessageNotWritableException {
record.formatJSON(outputMessage.getBody(), this.jsonFormat);
}
}
Declaring a RecordHttpMessageConverter @Bean should be sufficient to get your controller example above to work. My feeling is that this will be of limited use as it doesn't offer any control over the json so it isn't something that Spring Boot should provide out of the box. Providing control over the json requires knowledge of the application and the form that its json responses should take. Spring Boot doesn't have that knowledge so I think that's best achieved using a DTO that's part of your application. I'll flag this for team attention to see what the rest of the team think.
Comment From: lukaseder
The reason I thought it might be something that Spring Boot could be interested in doing is because it is already doing something. That something currently fails out of the box, and if that FAIL_ON_EMPTY_BEANS flag is specified, produces JSON that is hardly what users want, serialising some internals of jOOQ (or any other library, for that matter). This doesn't mean that it's Spring Boot's job to have an opinion here, of course, but it's interesting to discuss this.
Since jOOQ already offers out of the box JSON serialisation for Result and Record (or any other Formattable), using that could be a reasonable default (for any serialisation format, in fact). I would say it isn't unreasonable that a jOOQ user would expect this default serialisation to work and be able to override it using configuration only if needed.
The most jOOQ-idiomatic way to influence the JSON format is by structuring the jOOQ query projection. Not necessarily by introducing an auxiliary DTO just for that purpose (though that's obviously an option).
One way to do that is jOOQ 3.14's SQL/JSON support, in case of which the result is already JSON, and this issue here doesn't apply.
Another way, starting from jOOQ 3.15 will be to use the new ways of creating nested records and nested collections, which map to JSON intuitively via Formattable. I will push this approach very much once jOOQ 3.15 is out, because I believe this is what most people who are using jOOQ really want (whether they already know it or not): A 2-tier architecture where the server side logic is almost exclusively implemented in SQL via jOOQ, and the client side is consuming such JSON.
But I just thought of this: https://github.com/jOOQ/jOOQ/issues/11889. Jackson can automatically serialise Map and List types. In jOOQ, a Record is a kind of Map (there's already Record.intoMap()), and a Result is a List (there's Result.intoMaps()). So, instead of expecting users and/or Spring Boot to implement some glue code to call Formattable methods, if jOOQ only lets Record <: Map<String, Object>, then this would already work out of the box!
I'll prototype this
Comment From: lukaseder
Well, a quick draft of letting Record <: Map<String, Object> (full comments to be seen here: https://github.com/jOOQ/jOOQ/issues/11889#issuecomment-841134429) yields very promising results:
var r2 = DSL.using(connection)
.select(
T_BOOK.ID,
T_BOOK.fkTBookAuthorId().ID,
row(T_BOOK.fkTBookAuthorId().FIRST_NAME, T_BOOK.fkTBookAuthorId().LAST_NAME).as("author"),
array(select(row(T_BOOK_TO_BOOK_STORE.tBookStore().NAME, T_BOOK_TO_BOOK_STORE.STOCK))
.from(T_BOOK_TO_BOOK_STORE)
.where(T_BOOK_TO_BOOK_STORE.BOOK_ID.eq(T_BOOK.ID))).as("stock")
)
.from(T_BOOK)
.fetch();
System.out.println(new ObjectMapper().writeValueAsString(r2));
System.out.println(new Gson().toJson(r2));
Producing this jOOQ result structure:
+----+----+----------------+--------------------------------------------------+
| id| id|author |stock |
+----+----+----------------+--------------------------------------------------+
| 2| 1|(George, Orwell)|[(Orell Füssli, 10)] |
| 1| 1|(George, Orwell)|[(Orell Füssli, 10), (Ex Libris, 1)] |
| 4| 2|(Paulo, Coelho) |[] |
| 3| 2|(Paulo, Coelho) |[(Orell Füssli, 10), (Ex Libris, 2), (Buchhandl...|
+----+----+----------------+--------------------------------------------------+
And then getting auto serialised by both Jackson and Gson to something like this:
[
{
"id":2,
"id":1,
"author":{
"first_name":"George",
"last_name":"Orwell"
},
"stock":[
{
"v0":"Orell Füssli",
"v1":"10"
}
]
},
{
"id":1,
"author":{
"first_name":"George",
"last_name":"Orwell"
},
"stock":[
{
"v0":"Orell Füssli",
"v1":"10"
},
{
"v0":"Ex Libris",
"v1":"1"
}
]
},
{
"id":4,
"id":2,
"author":{
"first_name":"Paulo",
"last_name":"Coelho"
},
"stock":[
]
},
{
"id":3,
"id":2,
"author":{
"first_name":"Paulo",
"last_name":"Coelho"
},
"stock":[
{
"v0":"Orell Füssli",
"v1":"10"
},
{
"v0":"Ex Libris",
"v1":"2"
},
{
"v0":"Buchhandlung im Volkshaus",
"v1":"1"
}
]
}
]
We're onto something. I think that this is exactly what everyone wants from jOOQ, and Spring Boot doesn't need to change a thing (I think?) 😀
Comment From: morki
Why really just not use .intoMap() function in client code?
Now the Record will show up all of these extension functions in Kotlin which are (for me) confusing:
https://kotlinlang.org/api/latest/jvm/stdlib/kotlin.collections/-map/
Comment From: wilkinsona
Thanks for the update, @lukaseder. Given the prospect of https://github.com/jOOQ/jOOQ/issues/11889 or the possibility of using intoMap() in client code (thanks, @morki) I'm now convinced that Boot shouldn't do anything here.
Comment From: lukaseder
Yes, I'm not happy with useless new public API like clear() either. But assuming Jackson/Gson use instanceof Map, we don't have to expose this change in public API
Comment From: lukaseder
On the other hand, a Result is already a List (with all extension methods), so what makes Record / Map different (ignoring the unique key contract violation)?
Comment From: morki
@lukaseder for me, Result IS a list, so extension methods are really helpful, for mapping, filtering, first etc. On the other hand, I did not see Record as a map, but a helpful representation of row. This new methods and extensions will be available in UpdateableRecord too, which is for me confusing. But it is just matter of taste, I just wanted to write a user feedback.
Thank you for such a great library :)
Comment From: lukaseder
Sure, greatly appreciated. I still wonder why the perception is different. Habits? People have requested a Record representation (e.g. formatting) like this:
ID: 1
FIRST_NAME: John
LAST_NAME: Doe
Kinda like in the old days with Informix, etc.
With that representation, the Iterable<Entry<String, Object>> type seems more obvious, and all the Map goodies, like Map.forEach(BiConsumer<? super String, ? super Object>) do, too...
Anyway. This issue here is about Spring Boot, and I agree that Spring Boot doesn't need to take action. jOOQ might: https://github.com/jOOQ/jOOQ/issues/11889, and users already can, via intoMap()