spring-session-jdbc 适配gbase8s

近期，在某现场使用spring-session-jdbc 2.3.1 RELEASE版本时出现数据转换错误（Data conversion error）

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO SPRING_SESSION_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) SELECT PRIMARY_ID, ?, ? FROM SPRING_SESSION WHERE SESSION_ID = ?]; SQL state [IX000]; error code [-415]; Data conversion error.; nested exception is java.sql.BatchUpdateException: Data conversion error.
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:647) ~[spring-jdbc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:936) ~[spring-jdbc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.session.jdbc.JdbcIndexedSessionRepository.insertSessionAttributes(JdbcIndexedSessionRepository.java:464) ~[spring-session-jdbc-2.3.1.RELEASE.jar:2.3.1.RELEASE]
    at org.springframework.session.jdbc.JdbcIndexedSessionRepository.access$1300(JdbcIndexedSessionRepository.java:130) ~[spring-session-jdbc-2.3.1.RELEASE.jar:2.3.1.RELEASE]
    at org.springframework.session.jdbc.JdbcIndexedSessionRepository$JdbcSession.lambda$save$5(JdbcIndexedSessionRepository.java:794) ~[spring-session-jdbc-2.3.1.RELEASE.jar:2.3.1.RELEASE]
    at org.springframework.transaction.support.TransactionOperations.lambda$executeWithoutResult$0(TransactionOperations.java:68) ~[spring-tx-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.transaction.support.TransactionOperations.executeWithoutResult(TransactionOperations.java:67) ~[spring-tx-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.session.jdbc.JdbcIndexedSessionRepository$JdbcSession.save(JdbcIndexedSessionRepository.java:779) ~[spring-session-jdbc-2.3.1.RELEASE.jar:2.3.1.RELEASE]
    at org.springframework.session.jdbc.JdbcIndexedSessionRepository$JdbcSession.access$200(JdbcIndexedSessionRepository.java:631) ~[spring-session-jdbc-2.3.1.RELEASE.jar:2.3.1.RELEASE]
    at org.springframework.session.jdbc.JdbcIndexedSessionRepository.save(JdbcIndexedSessionRepository.java:409) ~[spring-session-jdbc-2.3.1.RELEASE.jar:2.3.1.RELEASE]
    at org.springframework.session.jdbc.JdbcIndexedSessionRepository.save(JdbcIndexedSessionRepository.java:130) ~[spring-session-jdbc-2.3.1.RELEASE.jar:2.3.1.RELEASE]
    at org.springframework.session.web.http.SessionRepositoryFilter$SessionRepositoryRequestWrapper.commitSession(SessionRepositoryFilter.java:225) ~[spring-session-core-2.3.1.RELEASE.jar:2.3.1.RELEASE]
    at org.springframework.session.web.http.SessionRepositoryFilter$SessionRepositoryRequestWrapper.access$100(SessionRepositoryFilter.java:192) ~[spring-session-core-2.3.1.RELEASE.jar:2.3.1.RELEASE]
    at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:144) ~[spring-session-core-2.3.1.RELEASE.jar:2.3.1.RELEASE]
    at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:82) ~[spring-session-core-2.3.1.RELEASE.jar:2.3.1.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:103) ~[spring-web-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:712) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:461) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:384) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:312) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.core.StandardHostValve.custom(StandardHostValve.java:398) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.core.StandardHostValve.status(StandardHostValve.java:257) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.core.StandardHostValve.throwable(StandardHostValve.java:352) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:177) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_301]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_301]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.38.jar:9.0.38]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_301]
Caused by: java.sql.BatchUpdateException: Data conversion error.
    at com.gbasedbt.jdbc.IfxPreparedStatement.executeBatch(IfxPreparedStatement.java:3227) ~[gbasedbtjdbc_3.3.0_2_36477d.jar:3.3.0_2]
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeBatch(DruidPooledPreparedStatement.java:551) ~[druid-1.2.16.jar:na]
    at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$2(JdbcTemplate.java:950) ~[spring-jdbc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    ... 41 common frames omitted

一般这种数据转换错误主要发生在LOB 数据类型，查看相关表确实存在blob 数据类型

REATE TABLE SPRING_SESSION_ATTRIBUTES (
	SESSION_PRIMARY_ID CHAR(36) NOT NULL,
	ATTRIBUTE_NAME VARCHAR2(200 CHAR) NOT NULL,
	ATTRIBUTE_BYTES BLOB NOT NULL,
	CONSTRAINT SPRING_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_PRIMARY_ID, ATTRIBUTE_NAME),
	CONSTRAINT SPRING_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_PRIMARY_ID) REFERENCES SPRING_SESSION(PRIMARY_ID) ON DELETE CASCADE
);

debug 代码逐步查找发现异常出现在

			this.jdbcOperations.update(this.createSessionAttributeQuery, (ps) -> {
				String attributeName = attributeNames.get(0);
				ps.setString(1, attributeName);
				getLobHandler().getLobCreator().setBlobAsBytes(ps, 2, serialize(session.getAttribute(attributeName)));
				ps.setString(3, session.getId());
			});

其中 this.createSessionAttributeQuery 是异常的sql,那么这个类是在什么时候被初始化的饿呢，观察发现此类为 spring bean 管理

	public JdbcIndexedSessionRepository(JdbcOperations jdbcOperations, TransactionOperations transactionOperations) {
		Assert.notNull(jdbcOperations, "jdbcOperations must not be null");
		Assert.notNull(transactionOperations, "transactionOperations must not be null");
		this.jdbcOperations = jdbcOperations;
		this.transactionOperations = transactionOperations;
		prepareQueries();
	}

其中初始化时调用 prepareQueries 方法

内容如下

	private static final String CREATE_SESSION_ATTRIBUTE_QUERY = "INSERT INTO %TABLE_NAME%_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) "
			+ "SELECT PRIMARY_ID, ?, ? "
			+ "FROM %TABLE_NAME% "
			+ "WHERE SESSION_ID = ?";

只是一个简单的insert语句，后使用jdbc 验证 ，当绑定变量 包含 blob 类型是需要做一个显示的类转换才可以，此为早期数据库版本问题，后期均已处理

处理方案为

SELECT PRIMARY_ID, ?, ?  修改为 select PRIMARY_ID, ?, cast (? as blob)即可