Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I thought I knew everything about UDTs and JDBC until someone on SO pointed out some details of the Javadoc of java.sql.SQLInput and java.sql.SQLData JavaDoc to me. The essence of that hint was (from SQLInput):

An input stream that contains a stream of values representing an instance of an SQL structured type or an SQL distinct type. This interface, used only for custom mapping, is used by the driver behind the scenes, and a programmer never directly invokes SQLInput methods.

This is quite the opposite of what I am used to do (which is also used and stable in productive systems, when used with the Oracle JDBC driver): Implement SQLData and provide this implementation in a custom mapping to

ResultSet.getObject(int index, Map mapping)

The JDBC driver will then call-back on my custom type using the

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 correctly initialised instance of my SQLData implementation holding all data from the UDT.

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

  • I can use the standard API, instead of using vendor-specific classes such as oracle.sql.STRUCT, etc.
  • I can generate source code from my UDTs, with appropriate getters/setters and other properties

My questions:

  • What do you think about my approach, implementing SQLData? Is it viable, even if the Javadoc states otherwise?
  • What other ways of reading UDT's in Java do you know of? E.g. what does Spring do? what does Hibernate do? What does JPA do? What do you do?

Addendum:

UDT support and integration with stored procedures is one of the major features of jOOQ. jOOQ aims at hiding the more complex "JDBC facts" from client code, without hiding the underlying database architecture. If you have similar questions like the above, jOOQ might provide an answer to you.

like image 231
Lukas Eder Avatar asked Dec 19 '10 23:12

Lukas Eder


People also ask

How do you query a database in Java?

STEP 1: Allocate a Connection object, for connecting to the database server. STEP 2: Allocate a Statement object, under the Connection created earlier, for holding a SQL command. STEP 3: Write a SQL query and execute the query, via the Statement and Connection created. STEP 4: Process the query result.

How fetch data from database in Java and display in table?

Start-Control Panel- Administrative Tools- Data Sources (ODBC)-go to system DSN tab-click add button-select a driver for which you want to set up a data source (for Oracle- Oracle in XE)-select it and click finish-give any name in data source name textbox-then click ok button. Class. forName("sun. jdbc.

How do I query a JDBC database?

Querying a SQL database with JDBC is typically a three-step process: Create a JDBC ResultSet object. Execute the SQL SELECT query you want to run. Read the results.

What is UDT in Java?

A user defined type (UDT) is a Java class whose instances (objects) are stored in database table columns. UDTs are defined as column data type and UDT instances are stored as column values. UDTs can be created and used in a Java DB database.


1 Answers

The advantage of configuring the driver so that it works behind the scenes is that the programmer does not need to pass the type map into ResultSet.getObject(...) and therefore has one less detail to remember (most of the time). The driver can also be configured at runtime using properties to define the mappings, so the application code can be kept independent of the details of the SQL type to object mappings. If the application could support several different databases, this allows different mappings to be supported for each database.

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

In the behind the scenes approach the ResultSet.getObject(int) method will use the type mappings defined on the connection rather than those passed by the application code in ResultSet.getObject(int index, Map mapping). Otherwise the approaches are the same.

Other Approaches

I have seen another 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 (it probably pre-dates the version of the JDBC standard defining SQLData/SQLInput).

like image 59
richj Avatar answered Oct 20 '22 01:10

richj