Java Types map to Java SQL Types Util

Question

I am using the java.sql.Connection.createArrayOf() method in order to create an array parameter for my SQL query.

For example:

Long[] arrayOfLongs = {1L, 2L, 3L};

would need to call conn.createArrayOf("bigint", arrayOfLongs);

while

String[] arrayOfStrings = {"a", "b", "c"};

would need to call

conn.createArrayOf("varchar", arrayOfStrings);

etc...

My Question is: Is there an Utility class out there that maps the Java Types to SQL types (as Strings and not java.sql.TYPES which are ints) ? Thanks very much

ForguesR · Accepted Answer · 2014-10-14 16:28:41Z

As far as I know, direct mapping (Java types => SQL types) is not possible since a single Java type can match many SQL types. You can confirm this with the Appendix B of the JBDC API Specification. For instance, a String could be mapped to a CHAR, VARCHAR, LONGVARCHAR, NCHAR, NVARCHAR or LONGNVARCHAR.

Basically, you have to map the Java type to the data type used in the table description of the database.

Update :

All JDBC database drivers must implement the setObject() setter which seems to be a sort of guessing function for the SQL data type. Unfortunetely it doesn't seems possible to reuse this function to achieve what you want but you can get a pretty good idea of what it is actually doing by looking at the source code.

Here is a sample of what MySQL is doing to guess the data type :

public void setObject(int parameterIndex, Object  parameterObj)
     throws SQLException  {
     if (parameterObj == null) {
         setNull(parameterIndex, java.sql.Types.OTHER);
     } else {
         if (parameterObj instanceof Byte ) {
             setInt(parameterIndex, ((Byte ) parameterObj).intValue());
         } else if (parameterObj instanceof String ) {
             setString(parameterIndex, (String ) parameterObj);
         } else if (parameterObj instanceof BigDecimal ) {
             setBigDecimal(parameterIndex, (BigDecimal ) parameterObj);
         } else if (parameterObj instanceof Short ) {
             setShort(parameterIndex, ((Short ) parameterObj).shortValue());
         } else if (parameterObj instanceof Integer ) {
             setInt(parameterIndex, ((Integer ) parameterObj).intValue());
         } else if (parameterObj instanceof Long ) {
             setLong(parameterIndex, ((Long ) parameterObj).longValue());
         } else if (parameterObj instanceof Float ) {
             setFloat(parameterIndex, ((Float ) parameterObj).floatValue());
         } else if (parameterObj instanceof Double ) {
             setDouble(parameterIndex, ((Double ) parameterObj).doubleValue());
         } else if (parameterObj instanceof byte[]) {
             setBytes(parameterIndex, (byte[]) parameterObj);
         } else if (parameterObj instanceof java.sql.Date ) {
             setDate(parameterIndex, (java.sql.Date ) parameterObj);
         } else if (parameterObj instanceof Time ) {
             setTime(parameterIndex, (Time ) parameterObj);
         } else if (parameterObj instanceof Timestamp ) {
             setTimestamp(parameterIndex, (Timestamp ) parameterObj);
         } else if (parameterObj instanceof Boolean ) {
             setBoolean(parameterIndex,
                 ((Boolean ) parameterObj).booleanValue());
         } else if (parameterObj instanceof InputStream ) {
             setBinaryStream(parameterIndex, (InputStream ) parameterObj, -1);
         } else if (parameterObj instanceof java.sql.Blob ) {
             setBlob(parameterIndex, (java.sql.Blob ) parameterObj);
         } else if (parameterObj instanceof java.sql.Clob ) {
             setClob(parameterIndex, (java.sql.Clob ) parameterObj);
         } else {
             setSerializableObject(parameterIndex, parameterObj);
         }
     }
 }

Yes that's true but if you consider JDBCTEMPLATE for example: insert.update("INSERT INTO PERSON (FIRSTNAME, LASTNAME) VALUES(?,?)", new Object[] { "MyName", "MyLastName" }); how does JdbcTemplate know that the Java.lang.String "MyName" maps to a VARCHAR?
Well it looks like JdbcTemplate is guessing the data type... and sometimes it makes a mistake.

Collectives™ on Stack Overflow

Java Types map to Java SQL Types Util

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related