我浏览了许多链接,例如:如何使用 Spring 的 JDBCTemplate 有效执行 IN() SQL 查询?,但没有解决我的疑问。不确定如何在 jdbcTemplate 中执行此操作
有人可以指导吗?它现在对我来说是一个真正的障碍:)
下面的代码
private static final String GET_PAYMENT_DT_FOR_BANK =
"select a.crte_dt, a.crte_user_id, a.acc_num from cust_mstr a where a.acc_num in (:acc_numList) and a.actv = :actv";
这是调用它的方法?
public List<Map<String, Object>> getAccDetailsFromAccNum(List<Long> acc_numList) {
// Map<String, Object> params = new HashMap<String, Object>();
// params.put("acc_numList", acc_numList);
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("acc_numList", acc_numList);
parameters.addValue("actv", "Y");
return jdbcTemplate.queryForList(GET_PAYMENT_DT_FOR_BANK, parameters);
}
错误
java.lang.IllegalStateException: Failed to execute CommandLineRunner
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:781) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:335) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1255) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1243) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
at com.example.DemoApplication.main(DemoApplication.java:26) [classes/:na]
Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select a.crte_dt, a.crte_user_id, a.acc_num from from cust_mstr a where a.acc_num in (:acc_numList) and actv = :actv]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:657) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:751) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:832) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at com.example.MyBIllingService.getICADetailsFromIcaNum(MyBIllingService.java:44) ~[classes/:na]
at com.example.MyBIllingService$$FastClassBySpringCGLIB$$61c86857.invoke(<generated>) ~[classes/:na]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at com.example.MyBIllingService$$EnhancerBySpringCGLIB$$d1b6a291.getICADetailsFromIcaNum(<generated>) ~[classes/:na]
at com.example.DemoApplication.isIcaInTrailPeriod(DemoApplication.java:34) [classes/:na]
at com.example.DemoApplication.run(DemoApplication.java:61) [classes/:na]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:797) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
... 5 common frames omitted
Caused by: java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:10495) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9974) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:10799) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:10776) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:241) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java) ~[HikariCP-2.7.9.jar:na]
at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:411) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:232) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:163) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.doSetValue(ArgumentPreparedStatementSetter.java:69) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.setValues(ArgumentPreparedStatementSetter.java:50) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:664) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
... 22 common frames omitted
acc_num 的数据类型是 NUMBER(10,0)。如果我创建了 JPA,那么它的
@Column(name="ACC_NUM")
private BigDecimal accNum;
为什么它不适用于 BigDecimal、Integer 和 Long?Oracle 版本 12c (12.1.0.2)
我能够使用NamedParameterJdbcTemplate
. 我想知道这是否可以使用JdbcTemplate
.
下面工作正常。
@Override
public List<Map<String, Object>> getAccDetailsFromAccNum(List<Long> acc_numList) {
Map<String, Set<Integer>> singletonMap = Collections.singletonMap("acc_numList", acc_numList);
Map<String, Object> paramMap = new HashMap<>();
paramMap.putAll(singletonMap);
paramMap.put("isFlag", "Y");
List<Map<String, Object>> queryForMap = namedParameterJdbcTemplate.queryForList(sql, paramMap);
return queryForMap;
}
代码参考:http : //www.javarticles.com/2015/02/example-of-spring-namedparameterjdbctemplate.html
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句