Re: HashMap and dynamic JDBC update
Alessandro wrote:
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."
--
Eric Sosman
esosman@ieee-dot-org.invalid