Spring session错误的SQL语法[DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME <？]

Question

我在Oracle19 c.Iwant中使用Spring Boot2.3.3、JDBC和Spring Session Jdbc来配置我的spring会话，但是我得到了这个错误。

ERROR 1424 --- [pool-1-thread-1] o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DELETE FROM SPRING_SESSION WHERE EXPIRY_TIME < ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: Table ou vue inexistante

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:917) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:927) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.session.jdbc.JdbcIndexedSessionRepository.lambda$cleanUpExpiredSessions$8(JdbcIndexedSessionRepository.java:553) ~[spring-session-jdbc-2.3.0.RELEASE.jar:2.3.0.RELEASE]
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.session.jdbc.JdbcIndexedSessionRepository.cleanUpExpiredSessions(JdbcIndexedSessionRepository.java:553) ~[spring-session-jdbc-2.3.0.RELEASE.jar:2.3.0.RELEASE]
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:93) ~[spring-context-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) ~[na:na]
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[na:na]
    at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
    at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: Table ou vue inexistante

我尝试查看是不是我的配置出错了，但我没有看到任何错误

我的application.properties

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=sokida
spring.datasource.password=sokida
jasypt.encryptor.password=aLPa@@sToRm++
server.port= 5001
#Session Store
spring.session.store-type=jdbc
# Session timeout. If a duration suffix is not specified, seconds are used.

spring.session.timeout.seconds=900

# Database schema initialization mode.
spring.session.jdbc.initialize-schema=always
# Path to the SQL file to use to initialize the database schema.
spring.session.jdbc.schema=classpath:org/springframework/session/jdbc/schema-@@platform@@.sql 
# Name of the database table used to store sessions.
spring.session.jdbc.table-name=SPRING_SESSION

你可以在这里找到我的pom.xml，我添加了spring session的依赖我的pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>APP</groupId>
    <artifactId>APP</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>app</name>
    <description>APPproject with spring boot</description>

    <properties>
        <java.version>11</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>



        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>

            <groupId>com.oracle.ojdbc</groupId>
            <artifactId>ojdbc10</artifactId>

        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>5.2.4.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.3.3.RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.session</groupId>
            <artifactId>spring-session-jdbc</artifactId>
        </dependency>


    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

任何帮助，请

Answer 1

我来这里是为了寻找解决这个问题的方法，评论对我帮助很大！

在我的例子中，问题是因为我正在升级我现有的工作应用程序，将现有的DataBase从Spring Boot1.5升级到2.4

新版本似乎需要一个不同的表定义，并且我当前的SPRING_SESSION表中不存在由旧的SpringBoot版本自动创建的EXPIRY_TIME列。

因此，我删除该表，重新启动应用程序，然后使用新的列名再次自动生成该表，这一次是EXPIRY_TIME。