jdbc查询中带in范围查询的绑定

习惯直接使用JdbcTemplate，对于其他的jdbc工具类就没怎么了解，最近使用查询带in参数时，发现无论怎么传参数，都会抛出异常，感觉jdbcTemplate搞不定了。

查找了资料，发现NamedParameterJdbcTemplate可以解决问题。

下面是NamedParameterJdbcTemplate类测试及验证过程：

1、测试所用表及数据

create table test_abc (
id integer,
name varchar(64),
age long
);

insert into test_abc values (100, 't01', 23);
insert into test_abc values (101, 't02', 24);
insert into test_abc values (102, 't03', 25);

2、测试用例

@Test
public void testSpringJdbcByIn() {
	String sql = "select * from test_abc where id in (:param)";

	List<Long> ids = new ArrayList<Long>();
	ids.add(100l);
	ids.add(101l);
	
	Map<String, Object> paramMap = new HashMap<String, Object>();
	paramMap.put("param", ids);
	
	NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
	jdbc.query(sql, paramMap, new RowMapper<AlarmSheet>() {

		@Override
		public AlarmSheet mapRow(ResultSet rs, int index) throws SQLException {
			// 此处会抛出异常，便于观察最终拼接的sql
			System.out.println("----- id="+rs.getLong("name"));
//				System.out.println(index+"----- id="+rs.getString("name"));
			
			return null;
		}
		
	});
	
}

3、通过测试报错观察最终拼接的sql

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; 
uncategorized SQLException for SQL [select * from test_abc where id in (?, ?)]; 
SQL state [99999]; error code [17059]; 无法转换为内部表示; nested exception is java.sql.SQLException: 无法转换为内部表示
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:693)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:720)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:770)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:190)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:197)
	at com.boco.wnms.dal.complaint.dao.JdbcDaoTest.testSpringJdbcByIn(JdbcDaoTest.java:45)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:601)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:232)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:175)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.SQLException: 无法转换为内部表示
	at oracle.jdbc.driver.CharCommonAccessor.getLong(CharCommonAccessor.java:297)
	at oracle.jdbc.driver.T4CVarcharAccessor.getLong(T4CVarcharAccessor.java:849)
	at oracle.jdbc.driver.OracleResultSetImpl.getLong(OracleResultSetImpl.java:939)
	at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:440)
	at com.boco.wnms.dal.complaint.dao.JdbcDaoTest$1.mapRow(JdbcDaoTest.java:50)
	at com.boco.wnms.dal.complaint.dao.JdbcDaoTest$1.mapRow(JdbcDaoTest.java:1)
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:706)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:642)
	... 35 more

4、分析

最开始传入的sql为

select * from test_abc where id in (:param)

而经过spring的NamedParameterJdbcTemplate后转换后为

select * from test_abc where id in (?, ?)

在进行查询时传的参数是一个List，里面含有2个对象，最终sql拼接是根据传入参数List中对象个数来动态拼接的。

转载于:https://my.oschina.net/u/1997676/blog/647648