From: | Eric B(dot)Ridge <ebr(at)tcdi(dot)com> |
---|---|
To: | Richard Welty <rwelty(at)averillpark(dot)net> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: examples of SQL Arrays and jdbc? |
Date: | 2003-02-07 00:11:15 |
Message-ID: | AC7A5B6A-3A30-11D7-9ECF-0003937E3354@tcdi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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
Attachment | Content-Type | Size |
---|---|---|
PostgresArray.java | text/plain | 14.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Welty | 2003-02-07 00:30:08 | Re: examples of SQL Arrays and jdbc? |
Previous Message | Richard Welty | 2003-02-06 23:18:19 | examples of SQL Arrays and jdbc? |