Re: examples of SQL Arrays and jdbc?

On Thursday, February 6, 2003, at 06:18  PM, Richard Welty wrote:

> first, apologies for that blank message i just accidentally sent to the
> list.
>
> i'm looking for some decent examples of using the SQL array type with
> JDBC.
> i have a number of arrays of 12 integers which need to come in and
> out. my
> reference right now is the Sun _JDBC API Tutorial and Reference_, and
> it's
> leaving me thinking that the Array/JDBC API is incredibly badly thought

I couldn't agree more.  JDBC Array support sucks.

> out, e.g. i can extract an int [] easily but i have to convert it to an
> Integer [] in order to write it back out. it can't really be this bad,
> can
> it?

Postgres is cool in that you can do a stmt.setString(...) (if using
prepared statements) for any datatype, including arrays.

Postgres' string form of an array is (in its simplest form):
    {1, 2, 3, N}
        or
    {"a", "b", "c", "N"}

So you can convert your int[] into a String in the above form and just
do:
    stmt.setString(3, Helper.arrayToPostgresString(myIntArray));

And if you're creating INSERT/UPDATE statements yourself:

create table foo (bar int[]);
insert into foo (bar) values ('{"1","2","3"}');

I got fancy and stole Postgres' java.sql.Array implementation and added
a little factory method to it, so I can do things like this:
    java.sql.Array array = MyPostgresArray.create(new int[] { 1, 2, 3 });
    stmt.setArray(3, array);
            or
    java.sql.Array array = MyPostgresArray.create(new int[] { 1, 2, 3 });
    String sql = "insert into foo (bar) values (" + array.toString() + ")";

I know this class works great w/ Postgres 7.2.x.  I haven't tested it
with 7.3.  It's attached in case you find it useful.  Note that this
class doesn't support multidimensional arrays.

eric