Home > Software engineering >  Why does HSQLDB and Spring Data JDBC claim my table does not exist?
Why does HSQLDB and Spring Data JDBC claim my table does not exist?

Time：04-01

I posted something similar of the same issue a few days ago but now I think I (with the help of some of the people who commented on the post) narrowed it down to know what I need help with exactly. I am trying to use the Spring Data JDBC in my application with the HSQLDB (am not trying to use a web server). But the driver doesn't support the get/set timeout for connections. Because of that, I cannot even use the database.

Here is the 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.6.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <scope>runtime</scope>
        </dependency>

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

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

</project>

Here is the schema.sql file:

CREATE TABLE Students (
    id INTEGER IDENTITY PRIMARY KEY,
    fName VARCHAR(50),
    lName VARCHAR(50),
    rank VARCHAR(50)
);

Here is the application.properties file:

logging.level.sql=debug
spring.datasource.generate-unique-name=false

Here is the main class:

package com.example.demo;

import org.springframework.boot.ApplicationRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

    @Bean
    ApplicationRunner applicationRunner(StudentRepo studentRepo) {
        return args -> {
            var s1 = Student.createStudent("John", "Doe");
            s1.setId(1l);
            var s2 = Student.createStudent("Jane", "Doe");
            s2.setId(2l);
            System.out.println(studentRepo.save(s1));
            System.out.println(studentRepo.save(s2));
            System.out.println(studentRepo.findByFName("John"));
        };
    }
}

Here is the StudentRepo interface:

package com.example.demo;

import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface StudentRepo extends CrudRepository<Student, Long> {

    @Query("SELECT * FROM Students WHERE fName = :name")
    List<Student> findByFName(@Param("name") String fName);
}

And here is the INFO log statement:

2022-03-26 19:28:32.692  INFO 25351 --- [main] com.zaxxer.hikari.pool.PoolBase :HikariPool-1 - Driver does not support get/set network timeout for connections(feature not supported)

Here is the error:

/Users/ismailajaz/Library/Java/JavaVirtualMachines/openjdk-17.0.1/Contents/Home/bin/java -javaagent:/Applications/IntelliJ IDEA CE.app/Contents/lib/idea_rt.jar=58091:/Applications/IntelliJ IDEA CE.app/Contents/bin -Dfile.encoding=UTF-8 -classpath /Users/ismailajaz/Downloads/demo/target/classes:/Users/ismailajaz/.m2/repository/org/springframework/boot/spring-boot-starter-data-jdbc/2.6.4/spring-boot-starter-data-jdbc-2.6.4.jar:/Users/ismailajaz/.m2/repository/org/springframework/boot/spring-boot-starter-jdbc/2.6.4/spring-boot-starter-jdbc-2.6.4.jar:/Users/ismailajaz/.m2/repository/com/zaxxer/HikariCP/4.0.3/HikariCP-4.0.3.jar:/Users/ismailajaz/.m2/repository/org/springframework/spring-jdbc/5.3.16/spring-jdbc-5.3.16.jar:/Users/ismailajaz/.m2/repository/org/springframework/data/spring-data-jdbc/2.3.2/spring-data-jdbc-2.3.2.jar:/Users/ismailajaz/.m2/repository/org/springframework/data/spring-data-relational/2.3.2/spring-data-relational-2.3.2.jar:/Users/ismailajaz/.m2/repository/org/springframework/data/spring-data-commons/2.6.2/spring-data-commons-2.6.2.jar:/Users/ismailajaz/.m2/repository/org/springframework/spring-tx/5.3.16/spring-tx-5.3.16.jar:/Users/ismailajaz/.m2/repository/org/springframework/spring-context/5.3.16/spring-context-5.3.16.jar:/Users/ismailajaz/.m2/repository/org/springframework/spring-aop/5.3.16/spring-aop-5.3.16.jar:/Users/ismailajaz/.m2/repository/org/springframework/spring-expression/5.3.16/spring-expression-5.3.16.jar:/Users/ismailajaz/.m2/repository/org/springframework/spring-beans/5.3.16/spring-beans-5.3.16.jar:/Users/ismailajaz/.m2/repository/org/slf4j/slf4j-api/1.7.36/slf4j-api-1.7.36.jar:/Users/ismailajaz/.m2/repository/org/hsqldb/hsqldb/2.5.2/hsqldb-2.5.2.jar:/Users/ismailajaz/.m2/repository/org/springframework/boot/spring-boot-starter/2.6.4/spring-boot-starter-2.6.4.jar:/Users/ismailajaz/.m2/repository/org/springframework/boot/spring-boot/2.6.4/spring-boot-2.6.4.jar:/Users/ismailajaz/.m2/repository/org/springframework/boot/spring-boot-autoconfigure/2.6.4/spring-boot-autoconfigure-2.6.4.jar:/Users/ismailajaz/.m2/repository/org/springframework/boot/spring-boot-starter-logging/2.6.4/spring-boot-starter-logging-2.6.4.jar:/Users/ismailajaz/.m2/repository/ch/qos/logback/logback-classic/1.2.10/logback-classic-1.2.10.jar:/Users/ismailajaz/.m2/repository/ch/qos/logback/logback-core/1.2.10/logback-core-1.2.10.jar:/Users/ismailajaz/.m2/repository/org/apache/logging/log4j/log4j-to-slf4j/2.17.1/log4j-to-slf4j-2.17.1.jar:/Users/ismailajaz/.m2/repository/org/apache/logging/log4j/log4j-api/2.17.1/log4j-api-2.17.1.jar:/Users/ismailajaz/.m2/repository/org/slf4j/jul-to-slf4j/1.7.36/jul-to-slf4j-1.7.36.jar:/Users/ismailajaz/.m2/repository/jakarta/annotation/jakarta.annotation-api/1.3.5/jakarta.annotation-api-1.3.5.jar:/Users/ismailajaz/.m2/repository/org/yaml/snakeyaml/1.29/snakeyaml-1.29.jar:/Users/ismailajaz/.m2/repository/org/springframework/spring-core/5.3.16/spring-core-5.3.16.jar:/Users/ismailajaz/.m2/repository/org/springframework/spring-jcl/5.3.16/spring-jcl-5.3.16.jar com.example.demo.DemoApplication
2022-03-27 08:45:30.629  INFO 26628 --- [           main] com.example.demo.DemoApplication         : Starting DemoApplication using Java 17.0.1 on MacBook-Pro.home with PID 26628 (/Users/ismailajaz/Downloads/demo/target/classes started by ismailajaz in /Users/ismailajaz/Downloads/demo)
2022-03-27 08:45:30.632  INFO 26628 --- [           main] com.example.demo.DemoApplication         : No active profile set, falling back to 1 default profile: "default"
2022-03-27 08:45:31.102  INFO 26628 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JDBC repositories in DEFAULT mode.
2022-03-27 08:45:31.145  INFO 26628 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 38 ms. Found 1 JDBC repository interfaces.
2022-03-27 08:45:31.431  INFO 26628 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2022-03-27 08:45:31.732  INFO 26628 --- [           main] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Driver does not support get/set network timeout for connections. (feature not supported)
2022-03-27 08:45:31.735  INFO 26628 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2022-03-27 08:45:32.083  INFO 26628 --- [           main] com.example.demo.DemoApplication         : Started DemoApplication in 1.952 seconds (JVM running for 2.352)
2022-03-27 08:45:32.159 DEBUG 26628 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL update
2022-03-27 08:45:32.160 DEBUG 26628 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [UPDATE "STUDENT" SET "F_NAME" = ?, "L_NAME" = ?, "RANK" = ? WHERE "STUDENT"."ID" = ?]
2022-03-27 08:45:32.245  INFO 26628 --- [           main] ConditionEvaluationReportLoggingListener : 

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2022-03-27 08:45:32.271 ERROR 26628 --- [           main] o.s.boot.SpringApplication               : Application run failed

java.lang.IllegalStateException: Failed to execute ApplicationRunner
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:771) ~[spring-boot-2.6.4.jar:2.6.4]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:758) ~[spring-boot-2.6.4.jar:2.6.4]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:310) ~[spring-boot-2.6.4.jar:2.6.4]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1312) ~[spring-boot-2.6.4.jar:2.6.4]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1301) ~[spring-boot-2.6.4.jar:2.6.4]
    at com.example.demo.DemoApplication.main(DemoApplication.java:12) ~[classes/:na]
Caused by: org.springframework.data.relational.core.conversion.DbActionExecutionException: Failed to execute DbAction.UpdateRoot(entity=Student{id='1', fName='John', lName='Doe', rank='WHITE'})
    at org.springframework.data.jdbc.core.AggregateChangeExecutor.execute(AggregateChangeExecutor.java:89) ~[spring-data-jdbc-2.3.2.jar:2.3.2]
    at org.springframework.data.jdbc.core.AggregateChangeExecutor.lambda$execute$0(AggregateChangeExecutor.java:50) ~[spring-data-jdbc-2.3.2.jar:2.3.2]
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
    at org.springframework.data.relational.core.conversion.DefaultAggregateChange.forEachAction(DefaultAggregateChange.java:116) ~[spring-data-relational-2.3.2.jar:2.3.2]
    at org.springframework.data.jdbc.core.AggregateChangeExecutor.execute(AggregateChangeExecutor.java:50) ~[spring-data-jdbc-2.3.2.jar:2.3.2]
    at org.springframework.data.jdbc.core.JdbcAggregateTemplate.store(JdbcAggregateTemplate.java:339) ~[spring-data-jdbc-2.3.2.jar:2.3.2]
    at org.springframework.data.jdbc.core.JdbcAggregateTemplate.save(JdbcAggregateTemplate.java:150) ~[spring-data-jdbc-2.3.2.jar:2.3.2]
    at org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.save(SimpleJdbcRepository.java:60) ~[spring-data-jdbc-2.3.2.jar:2.3.2]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:289) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:529) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:639) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.16.jar:5.3.16]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:163) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.16.jar:5.3.16]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.16.jar:5.3.16]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.16.jar:5.3.16]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.16.jar:5.3.16]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.16.jar:5.3.16]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.16.jar:5.3.16]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.16.jar:5.3.16]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.16.jar:5.3.16]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.16.jar:5.3.16]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.16.jar:5.3.16]
    at jdk.proxy2/jdk.proxy2.$Proxy58.save(Unknown Source) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.3.16.jar:5.3.16]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.3.16.jar:5.3.16]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.16.jar:5.3.16]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.16.jar:5.3.16]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.16.jar:5.3.16]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.16.jar:5.3.16]
    at jdk.proxy2/jdk.proxy2.$Proxy58.save(Unknown Source) ~[na:na]
    at com.example.demo.DemoApplication.lambda$applicationRunner$0(DemoApplication.java:23) ~[classes/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:768) ~[spring-boot-2.6.4.jar:2.6.4]
    ... 5 common frames omitted
Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE "STUDENT" SET "F_NAME" = ?, "L_NAME" = ?, "RANK" = ? WHERE "STUDENT"."ID" = ?]; nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: STUDENT in statement [UPDATE "STUDENT" SET "F_NAME" = ?, "L_NAME" = ?, "RANK" = ? WHERE "STUDENT"."ID" = ?]
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:981) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:328) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.update(DefaultDataAccessStrategy.java:165) ~[spring-data-jdbc-2.3.2.jar:2.3.2]
    at org.springframework.data.jdbc.core.JdbcAggregateChangeExecutionContext.updateWithoutVersion(JdbcAggregateChangeExecutionContext.java:367) ~[spring-data-jdbc-2.3.2.jar:2.3.2]
    at org.springframework.data.jdbc.core.JdbcAggregateChangeExecutionContext.executeUpdateRoot(JdbcAggregateChangeExecutionContext.java:115) ~[spring-data-jdbc-2.3.2.jar:2.3.2]
    at org.springframework.data.jdbc.core.AggregateChangeExecutor.execute(AggregateChangeExecutor.java:70) ~[spring-data-jdbc-2.3.2.jar:2.3.2]
    ... 49 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: STUDENT in statement [UPDATE "STUDENT" SET "F_NAME" = ?, "L_NAME" = ?, "RANK" = ? WHERE "STUDENT"."ID" = ?]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-4.0.3.jar:na]
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:235) ~[spring-jdbc-5.3.16.jar:5.3.16]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) ~[spring-jdbc-5.3.16.jar:5.3.16]
    ... 56 common frames omitted
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: STUDENT
    at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.ParserDQL.readTableName(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.ParserDQL.readRangeVariableForDataChange(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.ParserDML.compileUpdateStatement(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.ParserCommand.compileStatement(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.Session.compileStatement(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.StatementManager.compile(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.5.2.jar:2.5.2]
    ... 62 common frames omitted

2022-03-27 08:45:32.274  INFO 26628 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-03-27 08:45:32.276  INFO 26628 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 1

CodePudding user response：

So after a few tries to reproduce and make it work, i could get it done with the following changes:

In schema.sql:

CREATE TABLE "Student"
(
    "id"    INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "fName" VARCHAR(50),
    "lName" VARCHAR(50),
    "rank"  VARCHAR(50)
);

The first change is to remove the s at the end of Student, the second change is to make the id column autogenerated. I also added double quotes to all names, so that HSQLDB is using case isensitivity here.

In the Student class:

@Data
@Table("Student")
public class Student {

    @Id
    @Column("id")
    private long id;

    @Column("fName")
    private String fName;

    @Column("lName")
    private String lName;

    @Column("rank")
    private String rank;
}

Basically i specified the column and table names, otherwise Spring Data will use the default naming strategy, which will e.g. convert camel case field names using underscore (e.g. for the field name fName the mapped column name would be F_NAME).
I also added the Id annotation to the Id field, so that Spring Data can properly handle the checks if it's a new entity or not (based on that Spring Data will either use Insert or Update statements). Without that annotation, you would have to implement another interface to provide that functionality (i found an explanation here: https://prog.world/how-spring-data-jdbc-determines-when-an-object-is-new/)

In the StudentRepo:

public interface StudentRepo extends CrudRepository<Student, Long> {

    @Query("SELECT * FROM \"Student\" WHERE \"fName\" = :name")
    List<Student> findByFName(@Param("name") String fName);
}

I also removed the suffix s from the table name here and added the double quotes

CodePudding user response：

dunni provided a good answer how to make it work. I try to explain a little why it works, which also allows for other solutions.

First thing to realise is that Spring Data JDBC quotes all table and column names in statements it generates. This has two effects:

  1. It allows names like user and order and spaces in names. The first two are normally forbidden because they are keywords, the third because it contains spaces.

  2. This also makes the names case sensitive. You normally don't have to worry about this, as long as you use the generated names of Spring Data JDBC, because they use whatever letter casing your database uses by default. The SQL standard says this should be upper case but we all know what database vendors think about the SQL standard.

If you specify a name though in a @Column or @Table annotation that name is used verbatim. So @Table("Student") is different from @Table("student") and from @Table("STUDENT") and your schema creation script has to match that exactly. So you have to either use the default letter casing that your database use or you have to use quotes in your script, including custom queries in annotations.

Page link：https//www.codepudding.com/Softwareengineering/355892.html

Prev:Maven no print output when compiling
Next:Middleware Firebase authentication clarification
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding