jdbctemplate query with UTF8 string as parameter but did not find any object

[canal]

Hi,
I am using Spring 3.1 with Oracle 11g. A simple query using queryForObject(queryString, mapper, valueString) does not return any objects if the valueString is a UTF8 string.

I have tried to quote the string with:

StringBuffer st = new StringBuffer("'");
st.append(valueString );
st.append("'");

but the result is still the same.

Any idea why?

The Oracle DB is configured with charset UTF8. I can run the query in Oracle's Application Developer using SQL command:

select * from users where username = 'some chinese char';

but when I use jdbcTemplate, it does not return any objects.

thanks in advance, really lost after couple of hours debugging ...
canal

[cmontemu]

Hi Canal,

as per JdbcTemplate javadoc, you can see all SQLs by enabling logger with debug level:

    <category name="org.springframework.jdbc.core.JdbcTemplate" additivity="false">
        <priority value="debug" />
        <appender-ref ref="stdout" />
    </category>

My suggestion is to first look what JdbcTemplate is exactly passing to the oracle driver.

Best,
Carlos

[Marten Deinum]

What also might be interesting is the way you construct your queries.

[canal]

What also might be interesting is the way you construct your queries.

thanks for all your advice.

the query I use is:

final String query = "select * from roles where roleName = ?";

I now change to preparedstatement, it is ok. I do not have this UTF8 issue anymore:

PreparedStatement st = connection.prepareStatement(query);
st.setString(1, name)

[Marten Deinum]

I now change to preparedstatement, it is ok. I do not have this UTF8 issue anymore:

Which is also what JdbcTemplate is internally doing. So not sure what you are doing different.

[canal]

That's very strange then. Here is the complete code I use that having the query problem:

public Role getRoleByName(String name) {
	String query = "select * from roles where roleName = ?";
	// this has problem supporting utf8 string query
	return getJdbcTemplate().queryForObject(query, new RoleMapper(), name);
}

this is the new one using preparedstatement, which works:

	public Role getRoleByName(final String name) {
		final String query = "select * from roles where roleName = ?";
		return getJdbcTemplate().query (new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement st = con.prepareStatement(query);
				st.setString(1, name);
				if (logger.isDebugEnabled()) {
					logger.debug(st.toString());
				}
				return st;
			}
			
			}, new ResultSetExtractor<Role>() {

			@Override
			public Role extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
			          return new Role (rs.getString("rolename"), rs.getString("description"));
			    }
				return null;
			}});
	}

[Marten Deinum]

You state 'it doesn't return any values' if the query would return 0 or more then 1 result the queryForObject method would throw an exception if it doesn't return anything the problem might be in your RowMapper implementation.

What you do yourself is basically also what spring does. The queryForObject method delegates the creation of the query to the SimplePreparedStatementCreator and the arguments are set using the ArgPreparedStatementSetter which calls setString when the input type is a String.

To get more information you might want to enable TRACE logging for the org.springframework.jdbc.core package.

[canal]

that's strange.

I try again using simple JDBC template call, and this time it is ok.

The only thing I did is to re-populate data into Oracle 11g. But this time I select UTF8 (last night I selected UTF-8) when importing csv files. But I do not think that makes any difference.

But really appreciate all your support!
many thanks.

canal