Announcement Announcement Module
Collapse
No announcement yet.
JDBCTemplate and PreparedStatementCreatorFactory Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JDBCTemplate and PreparedStatementCreatorFactory

    I'm getting some odd behavior out of the PreparedStatementCreatorFactory class. Here's a pseduo code example of what I'm using to create the query:

    final JdbcTemplate template = new JdbcTemplate(getDataSource());
    final List declaredParameters = new ArrayList();
    final List bindParameters = new ArrayList();

    StringBuffer sqlQuery = new StringBuffer("SELECT COLUMN1, COLUMN2 from TABLE WHERE COLUMN3 = 'TEST' ");

    if (!StringUtils.isEmpty(column1)) {
    sqlQuery.append("AND UPPER(COLUMN1) LIKE ? ");
    declaredParameters.add(new SqlParameter("COLUMN1", Types.VARCHAR));
    bindParameters.add(column1.toUpperCase() + "%");
    }
    if (!StringUtils.isEmpty(column2)) {
    sqlQuery.append("AND UPPER(COLUMN2) LIKE ? ");
    declaredParameters.add(new SqlParameter("COLUMN2", Types.VARCHAR));
    bindParameters.add(column2.toUpperCase() + "%");
    }
    final PreparedStatementCreatorFactory psFactory = new PreparedStatementCreatorFactory(sqlQuery.toString( ), declaredParameters);
    final ArrayList results = new ArrayList();
    template.query(psFactory.newPreparedStatementCreat or(bindParameters.toArray()), new RowCallbackHandler() {

    /**
    * @param rs
    * @throws SQLException
    */
    public void processRow(ResultSet resultSet) throws SQLException {

    while (resultSet.next()) {
    //do something
    }
    }
    });
    return results;

    I've stepped through the code in the debugger and no result set is getting returned from the query. However, the following (edited) debug lines come out in the logs:

    12:54:28,727 DEBUG JdbcTemplate:378 - Executing SQL query [SELECT COLUMN1, COLUMN2 FROM TABLE WHERE UPPER(COLUMN1) LIKE ? AND UPPER(COLUMN2) LIKE ?]
    12:54:28,728 DEBUG DataSourceUtils:176 - Opening JDBC connection
    12:54:28,734 DEBUG StatementCreatorUtils:81 - Setting SQL statement parameter value; columnIndex=1, parameter value='BLAH%', valueClass=java.lang.String, sqlType=12
    12:54:28,735 DEBUG StatementCreatorUtils:81 - Setting SQL statement parameter value; columnIndex=2, parameter value='BLAH%', valueClass=java.lang.String, sqlType=12

    What is odd is that this works fine when there is only one parameter given (i.e., column1 is not null and column2 is). When I have values for both column1 and column2, the query does not return any results. What is odd is when I cut and paste the exact SQL from the log and execute it directly against the database, then it works without any problem. Any sugesstions would be appreciated. Thanks.
Working...
X