Creating SQL statements programmatically using Spring

I need to create SQL statement programmatically. I have val开发者_如何学运维ues in map in form <"column_name",value>. Now I have a base query, I add a where clause and as iterating through the map, if the value is not null I add this " " + key + "=" + value + " and ". Then I cut he last 5 characters and it's done. I would like to use something better than this. Note that I'm using Tapestry 5 + Spring JDBC Template (Hibernate is not an option).

Thanks, Ondrej

---

If you need to use dynamic condition I still recommend to generate where with ? like

" " + key + "=?"

and then iterate again to call preparedStatement.setXXX. Depending on the driver you can call setObject or check parameter type:

if (value instanceof String) 
    preparedStatement.setString((String)value)
else if ...

Using ? have it's advantages:

• You don't think about converting types like Date to string and quoting of special symbols.
• Database can cache execution plans more effectively when you use ? and not literal values.

---

It seems like you are looking for simething like squiggle-sql. And do not forget about prepared statements with it's 'set' methods.

---

You need to use the class PreparedStatementCreator

final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";

KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps =
                connection.prepareStatement(INSERT_SQL, new String[] {"id"});
            ps.setString(1, name);
            return ps;
        }
    },
    keyHolder);

// keyHolder.getKey() now contains the generated key

Reference: http://static.springsource.org/spring/docs/2.0.x/reference/jdbc.html

See section: 11.2.8