HashMap and dynamic JDBC update

[Alessandro]

Hello,

I have an HashMap<String, Object> used to save names and values of
fields to be updated via JDBC update in a table. The type of data is
variable according to field name , so I'm using Object ... the idea to
produce update query is as follows but I believe there is a better
method to do it.

//START
public void updateQuery(Session session, String table,
HashMap<String,Object> updateH, String condition) throws Throwable{
try{
String sQuery="UPDATE " + table + " SET ";
String keys="";

Iterator iterator = updateH.keySet().iterator
(); //key set
while(iterator.hasNext()) //got the key
and then remove first char,
ie ","
keys= keys + "," + iterator.next() +
"= ?";

PreparedStatement ps = session.connection
().prepareStatement(sQuery
+ keys.substring(1) + " WHERE " + condition);

while(iterator.hasNext()){
Object myObj=updateH.get(iterator.next
());
if(myObj instanceof String){

ps.setString(....
}
else if (myObj.instanceof Date){
ps.setDate(.....
}
//etc also for Double, Integer, ..
}
ps.executeUpdate();
}
catch(Throwable ex){
throw ex;
}
}

//END

Any suggestions ?

Thanks in advance and best regards,
Paul

 

[Lew]

Please *DO NOT* multipost.
(Reply cross-posted to rectify.)

I have an HashMap<String, Object> used to save names and values of
fields to be updated via JDBC update in a table. The type of data is
variable according to field name , so I'm using Object ... the idea to
produce update query is as follows but I believe there is a better
method to do it.

//START
public void updateQuery(Session session, String table,
HashMap<String,Object> updateH, String condition) throws Throwable{
try{

Your indentation is far too wide for Usenet. Do not use TAB characters to
indent Usenet posts; use a maximum of four spaces per indent level.

String sQuery="UPDATE " + table + " SET ";
String keys="";

Iterator iterator = updateH.keySet().iterator
(); //key set
while(iterator.hasNext()) //got the key
and then remove first char,
ie ","
keys= keys + "," + iterator.next() +
"= ?";

PreparedStatement ps = session.connection
().prepareStatement(sQuery
+ keys.substring(1) + " WHERE " + condition);

while(iterator.hasNext()){
Object myObj=updateH.get(iterator.next
());
if(myObj instanceof String){

Anytime you are checking type with 'instanceof' to support varied behavior,
suspect that you've erred.

ps.setString(....
}
else if (myObj.instanceof Date){
ps.setDate(.....
}
//etc also for Double, Integer, ..
}
ps.executeUpdate();
}
catch(Throwable ex){
throw ex;
}
}

//END

I've built up queries and corresponding set logic, polymorphically, using tiny
little setters similar to

interface ParmSetter <T>
{
void setValue( PreparedStatement ps, T value );
}
class StringSetter implements ParmSetter <String>
{
public void setValue( PreparedStatement ps, String value )
{
ps.setString( value );
}
}

Then you build up a list of SQL WHERE clause pieces and corresponding
ParmSetters as you receive criteria. Then you iterate through, calling

nextSetter.setValue( ps, nextValue );

Yet another way is to skip 'PreparedStatement#setString()' and the rest and
just use 'setObject()'.

 

[Eric Sosman]

Hello,

I have an HashMap<String, Object> used to save names and values of
fields to be updated via JDBC update in a table. The type of data is
variable according to field name , so I'm using Object ... the idea to
produce update query is as follows but I believe there is a better
method to do it.

Although I don't know much about JDBC, I can see a few
possibilities for improvement in your code and the Javadoc
suggests a few others. I've reformatted the code because
the amount of indentation in
the original
is
excessive and impairs
legibility.

//START
public void updateQuery(Session session, String table,
HashMap<String,Object> updateH, String condition) throws Throwable{
try{
String sQuery="UPDATE " + table + " SET ";
String keys="";

Iterator iterator = updateH.keySet().iterator();
while(iterator.hasNext())
keys= keys + "," + iterator.next() + "= ?";

A "for each" loop would reduce the visual clutter a bit:

for (String key : updateH.keySet())
keys += "," + key + "= ?";

An efficiency fanatic might suggest appending the pieces to a
StringBuilder and only creating a String when all is finished,
but that's probably not worth while: The number of keys in a
query is most likely small, and compared to the amount of work
the database itself must do the time spent mangling strings is
unlikely to be important.

PreparedStatement ps = session.connection()
.prepareStatement(
Query keys.substring(1) + " WHERE " + condition);

while(iterator.hasNext()){

The first loop has already exhausted this Iterator, so the
code inside this `while' will never execute. You need to get
a fresh Iterator, or write a new "for each," or combine the two
loops into one. Also, don't you need to maintain a counter to
use in the ps.setXXX() calls?

Object myObj=updateH.get(iterator.next());
if(myObj instanceof String){
ps.setString(....
}
else if (myObj.instanceof Date){
ps.setDate(.....
}
//etc also for Double, Integer, ..
> }

The Javadoc describes a setObject() method; can you use
it instead of this long chain of `instanceof' tests? There's
apparently a standard mapping from Java classes to SQL data
types, and since the examples you show here appear to be of
a fairly "obvious" kind, perhaps the standard mapping will do.

If it won't, you might be able to use the three-argument
form of setObject(), along with a pre-initialized map of Java
classes to SQL type codes:

// while initializing ...
Map<Class,Integer> types = ...;
types.put(java.util.Date.class, java.sql.Types.DATE);
types.put(my.package.Thing.class, java.sql.Types.VARBINARY);
...

// later, when preparing a query ...
int index = 1;
for (String key : updateH.keySet()) {
Object value = updateH.get(key);
Integer type = types.get(value.getClass());
if (type == null) ... // unrecognizable value?
ps.setObject(index++, value, type);
}

ps.executeUpdate();
}
catch(Throwable ex){
throw ex;

What's the point of this? Why bother to catch it at all,
if all you're going to do is re-throw it unaltered?

}
}

//END

Any suggestions ?

As I mentioned, I'm not experienced with JDBC -- so view
my suggestions with healthy skepticism. "Trust, but verify."