Spring Boot 2 testing repository fails because of a syntax error in SQL

Question

I am using spring-boot latest 2.1.3 release and want to start with testcases, but this is not working on my side.

I have a simple entity

    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    @Entity
    @Table(name = "foo.Account" )
    public class UserEntity {

        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        @Column(name = "uid")
        private Long id;
        @Column(name= "login")
        private String username;
        @Column(name = "password_hash")
        private String password;
    }

with this repository

  @Repository("UserRepository")
    public interface UserRepository extends JpaRepository<UserEntity, Long> {

    }

Now I want to write a test for this repository

    @RunWith(SpringRunner.class)
    @DataJpaTest
    @EnableJpaRepositories
    @TestPropertySource(properties = {
            "spring.jpa.hibernate.ddl-auto=create-drop"
    })
    public class UserRepositoryTest {

        @Autowired
        UserRepository repository;

        @Test
        public void insertTest(){

            UserEntity ue = new UserEntity();
            ue.setUsername("foo");
            ue.setPassword("bar");
        // ue.setRoles(new ArrayList<>());

            repository.save(ue);




        }

    }

In my test resources I have putted this schema.sql

CREATE SCHEMA IF NOT EXISTS FOO

But if fails because of a syntax error, I do not understand why this is happening. the hibernate_sequence table will be created because of the Annotation GenerationType.Auto

    2019-03-22 14:36:38.421 ERROR 9552 --- [           main] o.hibernate.id.enhanced.TableStructure   : could not read a hi value

    org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT NEXT_VAL AS ID_VAL FROM foo.HIBERNATE_SEQUENCE WITH[*] (UPDLOCK, ROWLOCK) "
    Syntax error in SQL statement "SELECT NEXT_VAL AS ID_VAL FROM foo.HIBERNATE_SEQUENCE WITH[*] (UPDLOCK, ROWLOCK) "; SQL statement:
select next_val as id_val from epls_dbo.hibernate_sequence with (updlock, rowlock) [42000-197]

I know that you can discuss why to test this repository and it would be better to do integration tests, but I want to be able to make a testcase for a repository, hopefully someone can help.

Viswanath Lekshmanan · Accepted Answer · 2019-03-22 13:55:16Z

1

Could you try using GenerationType.IDENTITY instead of GenerationType.AUTO in entity

answered Mar 22, 2019 at 13:55

Viswanath Lekshmanan

10.1k2 gold badges44 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Al Phaba Over a year ago

By changing that the error changed to this org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [insert into foo.foo.Account (password_hash, login) values (?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement I can approve that there is no table foo.foo.Account it should be named foo.Account. How can I reconfigure this. In the production database it is called foo.Account so I want to stick with it in my testcase

Viswanath Lekshmanan Over a year ago

change the @Table(name = "foo.Account" ) to @Table(name = "Account" )

Collectives™ on Stack Overflow

Spring Boot 2 testing repository fails because of a syntax error in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related