Getting a Native Query Typed in JPA

Recently I tried using a native query to return a result.

This worked fine, except that I had to cast the resulting list to the appropriate list. (which is ugly).

Like so:

List<Room> rooms = (List<Room>) getEntityManager().createNativeQuery(AdminRoom.GET_SEARCH_QUERY)
        .setParameter(1, "%" + description + "%")
        .setMaxResults(pageSize)
        .setFirstResult(offset)
        .getResultList();

I thought I could use the same thing I use daily in my createNamedQuery calls, namely add the appropriate class behind it.

Like so:

List<Room> rooms = (List<Room>) getEntityManager().createNativeQuery(AdminRoom.GET_SEARCH_QUERY, Room.class)...

Unfortunately, the return value of the createNativeQuery method is "Query" which is totally untyped, contrary to "TypedQuery", which I would have liked.

Of course, people have already found this out when I looked it up¹.

The API seems to be a little wonky in that area.

References

[1] StackOverflow - entityManager.createNativeQuery does not return a typed result

https://stackoverflow.com/questions/54109546/entitymanager-createnativequery-does-not-return-a-typed-result

Using SQL to generate JSON output

I recently read [1], and it had a very interesting notion.

The idea is to let the database generate JSON, and provide it straight into your client.

So I decided to find out if MariaDB had some support for this as well.

It does².

So, it basically was nothing more then calling a NativeQuery on the EntityManager, and returning a concatted resultset with a '['prefix and a ']'postfix and a comma-delimiter and away we go.

The native query looked like the following:

It worked flawlessly!

Caveat: of course, this is only in the case where your middleware (as in this example) really doesn't need to do anything with the result.

I mean, you are going to have to do all checks in the database query.

I think this example shows its strength when you just really want to read a lot of data, and do not need to process it.

References

[1] Stop Mapping Stuff in Your Middleware. Use SQL’s XML or JSON Operators Instead

https://blog.jooq.org/2019/11/13/stop-mapping-stuff-in-your-middleware-use-sqls-xml-or-json-operators-instead/

[2] MariaDB - starting with 10.2.3 - JSON Functions

https://mariadb.com/kb/en/library/json-functions/

MariaDB - JSON with MariaDB Platform: What Is JSON and Why Use It – With Examples

https://mariadb.com/resources/blog/json-with-mariadb-10-2/

MariaDB - Relational and Semi-structured Data

https://mariadb.com/database-topics/semi-structured-data/

MariaDB - Function Differences Between MariaDB 10.4 and MySQL 8.0

https://mariadb.com/kb/en/library/function-differences-between-mariadb-104-and-mysql-80/#present-in-mysql-only