Re: Accepting Object[] as an acceptable input to setObject with Types.ARRAY?

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 Connection object of your driver. I keep my code portable by declaring both my connection pool and an appropriate NativeJdbcExtractor together in an applicationContext file and then I just include the correct context file for the runtime environment I'm working in. Since my code always interacts only with the nativeJdbcExtractor, so long as the code it executes on that native connection isn't actually database specific, the code remains fully portable with the exception of 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";
+ }
+