I'm having an issue with accessing field of generated Routine from PL/pgSQL user-defined function (which returns JSON data type as a result) already mentioned in this question.
This is my result of get_all_orders() function created in PL/pgSQL of JSON type:
{
"orders": [
{
"order_id": 1,
"total_price": 29.99,
"order_date": "2019-08-22T10:06:33",
"user": {
"user_id": 1,
"username": "test"
},
"order_items": [
{
"order_item_id": 1,
"amount": 1,
"book": {
"book_id": 1,
"title": "Harry Potter and the Philosopher's Stone",
"price": 29.99,
"amount": 400,
"is_deleted": false,
"authors": [
{
"author_id": 4,
"first_name": "JK",
"last_name": "Rowling"
}
],
"categories": [
{
"category_id": 2,
"name": "Lyric",
"is_deleted": false
}
]
},
"order_id": 1,
"total_order_item_price": 29.99
}
]
},
{
"order_id": 2,
"total_price": 29.99,
"order_date": "2019-08-22T10:10:13",
"user": {
"user_id": 1,
"username": "test"
},
"order_items": [
{
"order_item_id": 2,
"amount": 1,
"book": {
"book_id": 1,
"title": "Harry Potter and the Philosopher's Stone",
"price": 29.99,
"amount": 400,
"is_deleted": false,
"authors": [
{
"author_id": 4,
"first_name": "JK",
"last_name": "Rowling"
}
],
"categories": [
{
"category_id": 2,
"name": "Lyric",
"is_deleted": false
}
]
},
"order_id": 2,
"total_order_item_price": 29.99
}
]
}
]
}
I am trying to access Routine as a Field by following Custom data type bindings manual and what I've managed to do so far is to create custom Converter in order to convert org.jooq.JSON into io.vertx.core.json.JsonObject:
public class JSONJsonObjectConverter implements Converter<JSON, JsonObject>{
private static final long serialVersionUID = -4773701755042752633L;
@Override
public JsonObject from(JSON jooqJson) {
String strVal = (jooqJson == null ? null : jooqJson.toString());
return strVal == null ? null : JsonObject.mapFrom(strVal);
}
@Override
public JSON to(JsonObject vertxJson) {
String strVal = (vertxJson == null ? null : vertxJson.toString());
return strVal == null ? null : JSON.valueOf(strVal);
}
@Override
public Class<JSON> fromType() {
return JSON.class;
}
@Override
public Class<JsonObject> toType() {
return JsonObject.class;
}
}
...this is link to QueryResult soruce code and I'm using this method to invoke it (Custom created Converter):
public static JsonObject convertGetAllOrdersQRToJsonObject(QueryResult qr) {
//JsonArray ordersJA = qr.get("orders", JsonArray.class);
DataType<JsonObject> jsonObjectType = SQLDataType.JSON.asConvertedDataType(new JSONJsonObjectConverter());
//DataType<JsonArray> jsonArrayType = SQLDataType.JSONArray.asConvertedDataType(new JsonArrayConverter());
DataType<JsonObject> jsonObjectTypeDefault = SQLDataType.JSON.asConvertedDataType((Binding<? super JSON, JsonObject>) new JsonObjectConverter());
Field<JsonObject> ordersFieldDefault = DSL.field("get_all_orders", jsonObjectTypeDefault);
Field<JsonObject> ordersField = DSL.field("get_all_orders", jsonObjectType);
JsonObject orders = qr.get("orders", JsonObject.class);
// return new JsonObject().put("orders", orders);
return new JsonObject().put("orders", ordersField); // try ordersFieldDefault(.toString()) as value parameter
}
I'm invoking above mentioned methods inside of following one:
Future<QueryResult> ordersFuture = queryExecutor.transaction(qe -> qe
.query(dsl -> dsl
.select(Routines.getAllOrders())
));
LOGGER.info("Passed ordersFuture...");
ordersFuture.onComplete(handler -> {
if (handler.succeeded()) {
QueryResult qRes = handler.result();
JsonObject ordersJsonObject = OrderUtilHelper.convertGetAllOrdersQRToJsonObject(qRes);
LOGGER.info("ordersJsonObject.encodePrettily(): " + ordersJsonObject.encodePrettily());
resultHandler.handle(Future.succeededFuture(ordersJsonObject));
} else {
LOGGER.error("Error, something failed in retrivening ALL orders! handler.cause() = " + handler.cause());
queryExecutor.rollback();
resultHandler.handle(Future.failedFuture(handler.cause()));
}
});
...and this is generated method in Routines.java class which is being used in last mentioned code above in expression that returns value into dsl -> dsl.select(Routines.getAllOrders()) statement part:
/**
* Convenience access to all stored procedures and functions in public
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Routines {
/**
* Get <code>public.get_all_orders</code> as a field.
*/
public static Field<JSON> getAllOrders() {
GetAllOrders f = new GetAllOrders();
return f.asField();
}
}
...and (finally) here is my *.jooq.routines.GetAllOrders.java class:
/**
* This class is generated by jOOQ.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class GetAllOrders extends AbstractRoutine<JSON> {
private static final long serialVersionUID = 917599810;
/**
* The parameter <code>public.get_all_orders.RETURN_VALUE</code>.
*/
public static final Parameter<JSON> RETURN_VALUE = Internal.createParameter("RETURN_VALUE", org.jooq.impl.SQLDataType.JSON, false, false);
/**
* Create a new routine call instance
*/
public GetAllOrders() {
super("get_all_orders", Public.PUBLIC, org.jooq.impl.SQLDataType.JSON);
setReturnParameter(RETURN_VALUE);
}
}
BTW, Vertx library also uses JsonArray class which is used to work with arrays but I don't see a way to map ALREADY generated org.jooq.JSON into org.jooq.impl.JSONArray and then into io.vertx.core.json.JsonArray type.
Is there I am missing something (I know I am dealing with generated Routine, but example given in jOOQ's manual contains only Table field)...or maybe I should've created Custom Data Type Binding class?
Any suggestion/help is greatly appreciated.
UPDATE1:
I've followed instructions given in linked Q&A in comments and this is what I've added and have already of <forcedType>s in my pom.xml :
<!-- Convert varchar column with name 'someJsonObject' to a io.vertx.core.json.JsonObject -->
<forcedType>
<userType>io.vertx.core.json.JsonObject</userType>
<converter>io.github.jklingsporn.vertx.jooq.shared.JsonObjectConverter</converter>
<includeExpression>someJsonObject</includeExpression>
<includeTypes>.*</includeTypes>
<nullability>ALL</nullability>
<objectType>ALL</objectType>
</forcedType>
<!-- Convert varchar column with name 'someJsonArray' to a io.vertx.core.json.JsonArray -->
<forcedType>
<userType>io.vertx.core.json.JsonArray</userType>
<converter>
io.github.jklingsporn.vertx.jooq.shared.JsonArrayConverter</converter>
<includeExpression>someJsonArray</includeExpression>
<includeTypes>.*</includeTypes>
<nullability>ALL</nullability>
<objectType>ALL</objectType>
</forcedType>
<forcedType>
<userType>io.vertx.core.json.JsonObject</userType>>
<!-- also tried to use "org.jooq.Converter.ofNullable(Integer.class, String.class, Object::toString, Integer::valueOf)"
and it did NOT work so I gave this custom created Conveter a try and it ALSO did NOT work! -->
<converter>
com.ns.vertx.pg.converters.JSONJsonObjectConverter.ofNullable(JSON.class, JsonObject.class, JsonObject::toString, JSON::valueOf)
</converter>
<includeExpression>(?i:get_all_orders|return_value)</includeExpressio>
</forcedType>
...and when I do Maven> Update Project + check Force Update of Snapshots/Releases I get 32 overall following ERROR messages:
JsonObject cannot be resolved
...and
JsonObject cannot be resolved to a type
...and this is my generated *.jooq.routines.GetAllOrders.java class:
// This class is generated by jOOQ.
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class GetAllOrders extends AbstractRoutine<JsonObject> {
private static final long serialVersionUID = -431575258;
// The parameter <code>public.get_all_orders.RETURN_VALUE</code>.
public static final Parameter<JsonObject> RETURN_VALUE = Internal.createParameter("RETURN_VALUE", org.jooq.impl.SQLDataType.JSON, false, false, org.jooq.Converter.ofNullable(JSON.class, JsonObject.class, JsonObject::toString, JSON::valueOf));
//Create a new routine call instance
public GetAllOrders() {
super("get_all_orders", Public.PUBLIC, org.jooq.impl.SQLDataType.JSON, org.jooq.Converter.ofNullable(JSON.class, JsonObject.class, JsonObject::toString, JSON::valueOf));
setReturnParameter(RETURN_VALUE);
}
}
I already have these programmaticly created converter for this generator ClassicReactiveVertxGenerator (more info about it avaliable here) for io.vertx.core.json.JsonObject in 1st mentioned <forcedType>. Any suggestion how to resolve this issue?
UPDATE2:
I've also tried to employ this org.jooq.Converter converter like this (had to use qualified refernce for JSON class otherwise it didn't perform imports in Generated Routine clases):
<forcedType>
<userType>java.lang.String</userType>
<converter>
org.jooq.Converter.ofNullable(org.jooq.JSON.class, String.class, Object::toString,org.jooq.JSON.class::valueOf)
</converter>
<includeExpression>(?i:get_all_orders|return_value) </includeExpression>
</forcedType>
...and I get this in generated GetAllOrders.java class:
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class GetAllOrders extends AbstractRoutine<String> {
private static final long serialVersionUID = 1922028137;
// The parameter <code>public.get_all_orders.RETURN_VALUE</code>.
public static final Parameter<String> RETURN_VALUE = Internal.createParameter("RETURN_VALUE", org.jooq.impl.SQLDataType.JSON, false, false, org.jooq.Converter.ofNullable(org.jooq.JSON.class, String.class, Object::toString, org.jooq.JSON.class::valueOf));
// in above value I get ERROR "The method ofNullable(Class<T>, Class<U>, Function<? super T,? extends U>, Function<? super U,? extends T>) in the type Converter is not applicable for the arguments (Class<JSON>, Class<String>, Object::toString, org.jooq.JSON.class::valueOf)"
// ... for org.jooq.Converter.ofNullable(..) method + 23 same/similar ERRORS
// Create a new routine call instance
public GetAllOrders() {
super("get_all_orders", Public.PUBLIC, org.jooq.impl.SQLDataType.JSON, org.jooq.Converter.ofNullable(org.jooq.JSON.class, String.class, Object::toString, org.jooq.JSON.class::valueOf));
setReturnParameter(RETURN_VALUE);
}
}
Since that did NOT work, I've tried to solve that problem by creating JooqJsonConverter.java Custom Converter class like this:
public class JooqJsonConverter implements Converter<String, JSON>{
private static final long serialVersionUID = -4773701755042752633L;
@Override
public JSON from(String jooqJson) { return jooqJson == null ? null : JSON.valueOf(jooqJson); }
@Override
public String to(JSON jooqJson) { return jooqJson == null ? null : jooqJson.toString(); }
@Override
public Class<String> fromType() { return String.class; }
@Override
public Class<JSON> toType() { return JSON.class; }
}
...and changing Converter under tag:
<converter>
com.ns.vertx.pg.converters.JooqJsonConverter.ofNullable(org.jooq.JSON.class, String.class, Object::toString,org.jooq.JSON.class::valueOf)
</converter>
...and I get same code GetAllOrders.java class with diffrence small difference
public static final Parameter<String> RETURN_VALUE = Internal.createParameter("RETURN_VALUE", org.jooq.impl.SQLDataType.JSON, false, false, com.ns.vertx.pg.converters.JooqJsonConverter.ofNullable(org.jooq.JSON.class, String.class, Object::toString, org.jooq.JSON.class::valueOf));
public GetAllOrders() {
super("get_all_orders", Public.PUBLIC, org.jooq.impl.SQLDataType.JSON, com.ns.vertx.pg.converters.JooqJsonConverter.ofNullable(org.jooq.JSON.class, String.class, Object::toString, org.jooq.JSON.class::valueOf));
setReturnParameter(RETURN_VALUE);
}
...and ONLY this 8 errors (2 for each of 4 generated Routine classes):
The method ofNullable(Class, Class, Object::toString, org.jooq.JSON.class::valueOf) is undefined for the type JooqJsonConverter
Any idea what is missing/am I doing wrong? Thank you in advance.
