当用spring jdbcTemplate执行merge语句时,总会出现”非法列类型提示“,通过google找到了以下这篇文章,目前没有采用以下这种方式,先记录下以备日后参考
转载自:http://forum.springsource.org/showthread.php?129285-Problems-with-JDBCTemplate-and-CLOB-insertion
Problems with JDBCTemplate and CLOB insertion
jdbcTemplate.update("MERGE INTO app_role_data x USING (select ? name, ? xml FROM dual) d ON (x.app_name = d.name) WHEN MATCHED THEN UPDATE SET x.xml_blob = d.xml WHEN NOT MATCHED THEN INSERT(app_name, xml_blob) VALUES(d.name, d.xml)",
new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
ps.setString(1, appName);
lobCreator.setClobAsString(ps, 2, xmlBlob);
}
});
But note that this fails the same way with a simple INSERT on the clob column.
lobHandler is an instance of OracleLobHandler, injected via the context.
The table:
SQL> desc app_role_data
Name Null? Type
----------------------------------------- -------- ----------------------------
APP_NAME VARCHAR2(64)
XML_BLOB CLOB
The exception:
Caused by: java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9231)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8812)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9534)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9517)
at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
at org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
at org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:815)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
WTF?
Junior Member
-
Join Date
- Mar 2009 Location
- NH, USA Posts
- 3
以上为转载内容,本人采取的处理方式是将merge放入存储过程中,然后通过spring jdbctemplate直接调用存储过程