What is the best way to read UDT from a database with Java?

Question

I thought I knew everything about UDT and JDBC until someone from /qaru.site / ... indicated some details of the Javadoc java.sql.SQLInput and java.sql.SQLData JavaDoc for me. The essence of this hint was (from SQLInput):

An input stream containing a stream of values ​​representing an instance of a structured SQL type or SQL of a different type. This interface, used only for user mapping, is used by the driver behind the scenes, and the programmer never directly calls SQLInput Methods.

This is the exact opposite of what I use (which is also used and stable on productive systems when used with the Oracle JDBC driver): SQLData and provide this implementation in a custom mapping

 ResultSet.getObject(int index, Map mapping) 

The JDBC driver will then access my custom type using

 SQLData.readSQL(SQLInput stream, String typeName) 

method. I implement this method and read each field from the SQLInput stream. In the end, getObject() will return a properly initialized instance of my SQLData implementation containing all the data from the UDT.

For me, this seems like the perfect way to implement such a custom mapping. Good reasons for this:

• I can use the standard API, instead of using vendor specific classes like oracle.sql.STRUCT etc.
• I can generate source code from my UDTs, with corresponding getters / seters and other properties

My questions are :

• What do you think of my approach implementing SQLData ? Is it viable even if Javadoc states otherwise?
• What other ways to read UDT in Java do you know? For example. what does spring do? what does hibernate do? What does JPA do? What are you doing?

Adding

UDT support and integration with stored procedures are one of the main functions of jOOQ . jOOQ seeks to hide the more complex "JDBC facts" from client code without hiding the underlying database architecture. If you have similar questions as above, jOOQ can provide you with an answer.

Answer 1

The advantage of setting up the driver so that it works behind the scenes is that the programmer does not need to pass a map of the type to ResultSet.getObject (...) and therefore has one smaller detail to remember (most of the time). The driver can also be configured at run time using properties to determine mappings, so application code can be stored regardless of the details of the SQL type mapping for object mappings. If the application can support several different databases, this allows you to maintain different mappings for each database.

Your method is viable, its main characteristic is that the application code uses explicit type mappings.

In the header approach, the ResultSet.getObject (int) method will use the type mappings defined in the connection, and not the ones passed by the application code to ResultSet.getObject (int index, Map mapping). Otherwise, the approaches will be the same.

Other approaches

I saw a different approach used with JBoss 4 based on these classes:

 org.jboss.ejb.plugins.cmp.jdbc.JDBCParameterSetter org.jboss.ejb.plugins.cmp.jdbc.JDBCResultSetReader.AbstractResultSetReader 

The idea is the same, but the implementation is non-standard (probably, it precedes the version of the JDBC standard that defines SQLData / SQLInput).

Answer 2

What other ways to read UDT in Java do you know? For example. what does spring do? what does hibernate do? What does JPA do? What are you doing?

An example of how something like this can be done in Hibernate / JPA is shown in this answer to another question:

Java enumerations, JPA and Postgres enumerations - How do I get them to work together?

Answer 3

I know what Spring does: you write implementations of your RowMapper interface. I have never used SQLData with Spring. Your post was the first time I ever heard or thought about this interface.