File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes MySql array (workarounds?) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "MySql array (workarounds?)" Watch "MySql array (workarounds?)" New topic

MySql array (workarounds?)

jay vas
Ranch Hand

Joined: Aug 30, 2005
Posts: 407
Hi Guys : I wanted to insert a hashtable of name value pairs into mysql.

I know one way of doing this is to do

insert into myTable values (1,'jay'), (2,'jim') , etc...

but Id rather have a mysql stored procedure handle the creation
of this statement (so that
I can avoid having sql in my java code).

Any ideas for a work around ?

Ideally, Id like to insert as a String and have an SP handle the parsing...

call SP_param_insert('1=jay;2=jim;')
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

If you really want to use a stored procedure, you could pass two arrays to the stored proc: one with the names and one with the values. Not sure if mySql supports this though.

I think it would be clearer to do this in Java though. You could use a PreparedStatement:
nsert into myTable values (?, ?)

If you use JDBC's batch update, you can still do all the updates in one server roundtrip. Also, you can get the SQL out of the Java code by a putting it in a property file.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 63867

"jay v",

There aren't many rules that you need to worry about here on the Ranch, but one that we take very seriously regards the use of proper names. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

JavaRanch Sheriff

[Asking smart questions] [About Bear] [Books by Bear]
I agree. Here's the link:
subject: MySql array (workarounds?)
It's not a secret anymore!