Re: Accepting Object[] as an acceptable input to setObject with Types.ARRAY? - Mailing list pgsql-jdbc

On Jun 3, 2011, at 3:02 PM, Samuel Gendler wrote:

> > If I go down this path, I end up having to write a special case for each
> > combination of database and pool supported, which is very painful to maintain.
>
> If you happen to be using Spring to manage the declaration of your data source, they have the NativeJdbcExtractor
interface,with an implementation for the most popular connection pools.  That will give you access to the native
Connectionobject of your driver.  I keep my code portable by declaring both my connection pool and an appropriate
NativeJdbcExtractortogether in an applicationContext file and then I just include the correct context file for the
runtimeenvironment I'm working in.  Since my code always interacts only with the nativeJdbcExtractor, so long as the
codeit executes on that native connection isn't actually database specific, the code remains fully portable with the
exceptionof the applicationContext file, which can be fed into it at run time. 
>
> If you're not using spring, you can model a similar system of your own.

Wow, that is very clever how they do it!  Basically they call
connection.getMetaData().getConnection() which most pools do not intercept, giving you access to
the connection via only the public APIs.  And you do not even need to use Spring if you just do that.

I can definitely do this for my uses, thank you.

That said I still think it is an interesting feature to consider, as ultimately this is still
a hack (even if an elegant one) and I have whipped up a small proof of concept:


diff -r e313d386bcf0 org/postgresql/jdbc2/AbstractJdbc2Statement.java
--- a/org/postgresql/jdbc2/AbstractJdbc2Statement.java  Fri Jun 03 13:21:43 2011 -0700
+++ b/org/postgresql/jdbc2/AbstractJdbc2Statement.java  Fri Jun 03 13:57:52 2011 -0700
@@ -1731,7 +1796,9 @@
                     throw new PSQLException(GT.tr("Cannot cast an instance of {0} to type {1}", new
Object[]{in.getClass().getName(),"Types.CLOB"}),PSQLState.INVALID_PARAMETER_TYPE); 
                 break;
             case Types.ARRAY:
-                if (in instanceof Array)
+                if (in instanceof Object[])
+                    setArray(parameterIndex, getPGConnection().createArrayOf(determineSQLType((Object[]) in),
(Object[])in)); 
+                else if (in instanceof Array)
                     setArray(parameterIndex, (Array)in);
                 else
                     throw new PSQLException(GT.tr("Cannot cast an instance of {0} to type {1}", new
Object[]{in.getClass().getName(),"Types.ARRAY"}),PSQLState.INVALID_PARAMETER_TYPE); 
@@ -1750,6 +1817,38 @@
         }
     }

+    private String determineSQLType(Object[] array) {
+        Class<?> componentType = array.getClass().getComponentType();
+        if (componentType.equals(byte.class) || componentType.equals(Byte.class))
+            return "tinyint";
+        if (componentType.equals(char.class) || componentType.equals(Character.class))
+            return "varchar";
+        if (componentType.equals(short.class) || componentType.equals(Short.class))
+            return "smallint";
+        if (componentType.equals(int.class) || componentType.equals(Integer.class))
+            return "int";
+        if (componentType.equals(long.class) || componentType.equals(Long.class))
+            return "bigint";
+        if (componentType.equals(float.class) || componentType.equals(Float.class))
+            return "float";
+        if (componentType.equals(double.class) || componentType.equals(Double.class))
+            return "double";
+        if (componentType.equals(boolean.class) || componentType.equals(Boolean.class))
+            return "bit";
+        if (componentType.equals(String.class))
+            return "varchar";
+        if (componentType.equals(byte[].class))
+            return "varbinary";
+        if (componentType.equals(Date.class))
+            return "date";
+        if (componentType.equals(Time.class))
+            return "time";
+        if (componentType.equals(Timestamp.class))
+            return "timestamp";
+        // Cross your fingers and hope the default works...
+        return "varchar";
+    }
+