If this is already there and I am missing it, sorry...
I am going to make a few different suggestions related to how queries are specified... just something to think about.
I freqently build dynamic queries where the number of parameters change each time. Currently I am saving all of the parameters up in a HashMap where the key is the parameter name and the value is the parameter. Then after I create the query object (from session.createQuery(sHql)) I add the parameters. It would be cool if I could just hand a query.setParametersList(HashMap) and it reads the keys and values to build up the parameter list.
Alternatively, have a seperate object which is a parameter list which behaves like query with respect to .setString(key,value), and the others types then hand the parameter list to query. Make sense?
The final suggestion would be to be more like a sql interface class that I wrote which makes passing parameters very handy. Syntax is like:
Code:
SqlRunner sql = null;
try {
sql = new SqlRunner();
sql.setSql("INSERT INTO aces_equipment (");
sql.addSql("site_undo,");
sql.addSql("status_undo,");
sql.addSql("uniqueNum,");
sql.addSql("id,", v.getNewId());
sql.addSql("type,", v.getType());
sql.addSql("home,", v.getHome());
sql.addSql("site,", v.getSite());
sql.addSql("status,", v.getStatus());
sql.addSql("retired", v.getRetired());
sql.addSql(") VALUES (null,null,aces_seq_equipment_id.nextval,?,?,?,?,?,?)");
sql.executeUpdate();
sql.setSql("DELETE FROM aces_comments");
sql.addSql("WHERE equipmentid=?", v.getNewId());
sql.executeUpdate();
} finally {
if (sql != null)
sql.close();
}
///-------
SqlRunner sql = null;
try {
sql.setSql("SELECT ");
sql.addSql("e.uniqueNum,e.id,e.type,e.home,e.site");
sql.addSql("FROM aces_equipment e");
sql.addSql("WHERE e.id=? AND e.home=?",sLookup);
sql.addParameter(sHome);
ResultSet rs = sql.executeQuery();
while(rs.next()) {
//Do something
}
} finally {
if (sql != null)
sql.close();
}
The keys here is that the sql can be easily built on multiple lines, the first part of the sql is added with setSql(), subsequent with addSql().
You can add parameters with the optional second parameter of setSql or addSql. The second param can take all of the primitive types and other specific object types (Long, Integer, Timestamp, etc.) and it detects the exact parameter type. Nice for all statements, you can easily see which parameter goes where, but especially nice with regards to INSERT because the parameters line up nicely with where they are attached to.
This method also makes building the statement up dynamically (some parts are added sometimes, some are omitted) and having the parameters line up easy to do.
Just some thoughts.
Kevin
Login
FAQ
Search