Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySql array (workarounds?)

 
jay vas
Ranch Hand
Posts: 407
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Posts: 34837
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jay,
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.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65114
89
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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.

Thanks!
bear
JavaRanch Sheriff
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic