spring data JPA query against H2 fails with schema not found

Question

I have an entity called user which looks like the following:

    @Entity
    @JsonSerialize(using = UserSerializer.class)
    @Table(uniqueConstraints={@UniqueConstraint(columnNames = {"username"})})
    public class User implements UserDetails {

        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;

        @Column(unique = true, updatable = false)
        private String username;

        private String password;

        @OneToOne(cascade={CascadeType.ALL})
        @JoinColumn(name = "person_id")
        private Person person;

        @OneToOne(cascade={CascadeType.PERSIST})
        @JoinColumn(name = "maintainer_id")
        private Maintainer maintainer;

        @ManyToMany(cascade={CascadeType.PERSIST, CascadeType.MERGE}, fetch = 
 FetchType.EAGER)
        @JoinTable(name = "user_role",
                joinColumns = @JoinColumn(name = "user_id",
                        nullable = false, updatable = false),
                inverseJoinColumns = @JoinColumn(name = "role_id",
                        nullable = false, updatable = false))
        private Set<Role> roles;

        public User() {
        }

        public User(String username, String password, Person person, Maintainer maintainer, Set<Role> roles) {
            this.username = username;
            this.password = password;
            this.person = person;
            this.maintainer = maintainer;
            this.roles = roles;
        }

        // builder inner class omitted 

        // getters and setters omitted

        // equals, hashCode and toString omitted    
    }

I also have a user summary class, which is designed to be returned for pagination and sorting:

public class UserRow {
    private Long id;
    private String username;
    private String firstName;
    private String lastName;
    private Set<Role> roles;
    private String role;
    private Long maintainerId;
    private String maintainerName;

    public static UserRow of(Long id, String username, String firstName, String lastName, Set<Role> roles, Long maintainerId, String maintainerName) {
        UserRow userRow = new UserRow();

        userRow.id = id;
        userRow.username = username;
        userRow.firstName = firstName;
        userRow.lastName = lastName;
        userRow.roles = roles;
        userRow.maintainerId = maintainerId;
        userRow.maintainerName = maintainerName;


        return userRow;
    }

// getters, setters, equals, hashCode and toString omitted
}

This, along with all the other entities appears to initialise in h2 nicely, from the console I see things like:

create table user (
   id bigint generated by default as identity,
    password varchar(255),
    username varchar(255),
    maintainer_id bigint,
    person_id bigint,
    primary key (id)
)

and

create table user_role (
   user_id bigint not null,
    role_id bigint not null,
    primary key (user_id, role_id)
)

and even:

alter table user 
   add constraint UK_sb8bbouer5wak8vyiiy4pf2bx unique (username)

Now, I have a controller, service and repository set up...

The Controller:

@Secured("ROLE_ADMIN")
@GetMapping(value = {"/users", "/users/maintainers/{mid}"}, produces = "application/json")
@ResponseStatus(value = HttpStatus.OK)
Response<List<User>> getPagedUsers(
        @PathVariable("mid") Optional<Long> maintainerId,
        @PageableDefault(page = DEFAULT_PAGE_NUMBER, size = DEFAULT_PAGE_SIZE)
        @SortDefault.SortDefaults({
                @SortDefault(sort = "username", direction = Sort.Direction.ASC),
                @SortDefault(sort = "p.lastName", direction = Sort.Direction.ASC)
        }) Pageable pageable) {

    Page<UserRow> users;
    if (maintainerId.isPresent()) {
        users = userService.findSortedSummaryByMaintainer(maintainerId.get(), pageable);
    } else {
        users = userService.findSortedSummary(pageable);
    }

    return Response.of(users);
}

The Service: public interface UserService extends UserDetailsService {

    Page<UserRow> findSortedSummary(Pageable pageable);

    @Service
    class UserServiceImpl implements UserService {

        private static final Logger LOG = LoggerFactory.getLogger(UserService.class);

        @Autowired
        BCryptPasswordEncoder bCryptPasswordEncoder;

        @Autowired
        UserRepository userRepository;

        @Autowired
        RoleRepository roleRepository;

        public Page<UserRow> findSortedSummary(Pageable pageable) {

            Page<UserRow> userPage = userRepository.findSortedSummary(pageable.next());

            userPage.forEach(us -> us.setRole(findMostPrivilegedRole(us.getRoles())));

            return userPage;
        }

    }

}

And the Repository: public interface UserRepository extends JpaRepository {

    @Query(value = "select u.username, p.firstName, p.lastName, u.roles, m.id, m.name "
                    + "from User u "
                    + "inner join u.person p "
                    + "inner join u.maintainer m",
            countQuery = "select count(*) "
                    + "from User u "
                    + "inner join u.person p "
                    + "inner join u.maintainer m",
            nativeQuery = true)
    Page<UserRow> findSortedSummary(Pageable pageable);

}

And finally, I'm testing all of this with:

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@ActiveProfiles("embedded")
@AutoConfigureMockMvc
public class UserListControllerFunctionalTest {

    @Autowired
    private WebApplicationContext context;

    @Autowired
    private MockMvc mvc;

    @Autowired
    UserService userService;

    List<User> franks;

    @Before
    public void setup() {
        mvc = MockMvcBuilders
                .webAppContextSetup(context)
                .apply(springSecurity())
                .build();

        franks = ModelFixtures.createFrankAndCohorts();
        franks = userService.createAll(franks);
    }

    @Test
    public void getListOfUsersWithRootSuccess() throws Exception {

        mvc.perform(get("/api/users").header(AUTHORIZATION_HEADER, "Bearer " + ModelFixtures.ROOT_JWT_TOKEN))
                .andDo(print())
                .andExpect(status().isOk());

    }

    @After
    public void tearDown() {
        userService.deleteAll(franks);
    }

}

All of the spring security stuff has been extensively tested and works.

When the test calls the controller, service and repository, I get a failure in the repository with:

Caused by: org.h2.jdbc.JdbcSQLException: Schema "U" not found; SQL statement: select u.username, p.firstName, p.lastName, u.roles, m.id, m.name from User u inner join u.person p inner join u.maintainer m order by u.username asc, p.lastName asc limit ? offset ? [90079-197] at org.h2.message.DbException.getJdbcSQLException(DbException.java:357) at org.h2.message.DbException.get(DbException.java:179) at org.h2.message.DbException.get(DbException.java:155)

As far as I have found so far, aliasing tables like "User" with letters like "u" is fine, The internal table in h2 (and MySQL) for users is "users", so "User" is not reserved. So I can't see what the problem is here. Can anyone help?

Relevant dependencies:

|    +--- org.hibernate:hibernate-core:5.2.17.Final
|    |    +--- org.jboss.logging:jboss-logging:3.3.1.Final -> 3.3.2.Final
|    |    +--- org.hibernate.javax.persistence:hibernate-jpa-2.1-api:1.0.0.Final
|    |    +--- org.javassist:javassist:3.22.0-GA
|    |    +--- antlr:antlr:2.7.7
|    |    +--- org.jboss:jandex:2.0.3.Final
|    |    +--- com.fasterxml:classmate:1.3.0 -> 1.3.4
|    |    +--- dom4j:dom4j:1.6.1
|    |    \--- org.hibernate.common:hibernate-commons-annotations:5.0.1.Final
|    |         \--- org.jboss.logging:jboss-logging:3.3.0.Final -> 3.3.2.Final
|    +--- javax.transaction:javax.transaction-api:1.2
|    +--- org.springframework.data:spring-data-jpa:2.0.7.RELEASE
|    |    +--- org.springframework.data:spring-data-commons:2.0.7.RELEASE
|    |    |    +--- org.springframework:spring-core:5.0.6.RELEASE (*)
|    |    |    +--- org.springframework:spring-beans:5.0.6.RELEASE (*)
|    |    |    \--- org.slf4j:slf4j-api:1.7.25
|    |    +--- org.springframework:spring-orm:5.0.6.RELEASE
|    |    |    +--- org.springframework:spring-beans:5.0.6.RELEASE (*)
|    |    |    +--- org.springframework:spring-core:5.0.6.RELEASE (*)
|    |    |    +--- org.springframework:spring-jdbc:5.0.6.RELEASE (*)
|    |    |    \--- org.springframework:spring-tx:5.0.6.RELEASE (*)
|    |    +--- org.springframework:spring-context:5.0.6.RELEASE (*)
|    |    +--- org.springframework:spring-aop:5.0.6.RELEASE (*)
|    |    +--- org.springframework:spring-tx:5.0.6.RELEASE (*)
|    |    +--- org.springframework:spring-beans:5.0.6.RELEASE (*)
|    |    +--- org.springframework:spring-core:5.0.6.RELEASE (*)
|    |    \--- org.slf4j:slf4j-api:1.7.25

EDIT - Removing the nativeQuery = true part fixed the "schema not found" issue - native query must mean all elements treated literally.

However the problem then became:

Caused by: 

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "

SELECT 
USER0_.USERNAME AS COL_0_0_, 
PERSON1_.FIRST_NAME AS COL_1_0_, 
PERSON1_.LAST_NAME AS COL_2_0_, 
.[*] AS COL_3_0_, 
MAINTAINER2_.ID AS COL_4_0_, 
MAINTAINER2_.NAME AS COL_5_0_, 
ROLE4_.ID AS ID1_17_, 
ROLE4_.DESCRIPTION AS DESCRIPT2_17_, 
ROLE4_.ROLE_NAME AS ROLE_NAM3_17_ 
FROM USER USER0_ 
INNER JOIN PERSON PERSON1_ ON USER0_.PERSON_ID=PERSON1_.ID 
INNER JOIN MAINTAINER MAINTAINER2_ ON USER0_.MAINTAINER_ID=MAINTAINER2_.ID 
INNER JOIN USER_ROLE ROLES3_ ON USER0_.ID=ROLES3_.USER_ID 
INNER JOIN ROLE ROLE4_ ON ROLES3_.ROLE_ID=ROLE4_.ID 
ORDER BY USER0_.USERNAME ASC, PERSON1_.LAST_NAME ASC LIMIT ? OFFSET ? "; 

expected "*, NOT, EXISTS, INTERSECTS, SELECT, FROM, WITH";

Not sure where the "expected" things are expected here...

Michael Coxon Michael Coxon · Accepted Answer · 2018-07-19T21:15:34

OK, I've solved it.

It was all in the query construction. So the correct way to do the jpql is:

@Query(value = "select new au.com.avmaint.api.access.model.UserRow(u.id, u.username, p.firstName, p.lastName, m.id, m.name) "
                + "from User u "
                + "inner join u.person p "
                + "inner join u.maintainer m",
        countQuery = "select count(*) "
                + "from User u "
                + "inner join u.person p "
                + "inner join u.maintainer m")
Page<UserRow> findSortedSummary(Pageable pageable);

If you intend to return a non-mapped class (UserRow), then you need to use the constructor notation. You also can't return a collection (user.roles) in the SELECT clause, so I had to get the roles in another query. This was easily done in the service layer with (see the first line in the forEach block):

    public Page<UserRow> findSortedSummary(Pageable pageable) {

        Page<UserRow> userPage = userRepository.findSortedSummary(pageable.next());

        userPage.forEach(row -> {
            User user = userRepository.findById(row.getId()).orElseThrow(() -> new UsernameNotFoundException("User not found: " + row.getUsername()));
            row.setRoles(user.getRoles());
            row.setRole(findMostPrivilegedRole(user.getRoles()));
        });

        return userPage;
    }

Here I used a standard Repository findById call to return the single user by id and get the roles that way. It certainly would have been nice to get the roles in the single query, but JPQL just doesn't have that level of feature.

I might say in closing that paging and sorting for anything but the most trivial domain examples (which are basically the ones that are blogged) is actually quite tricky and intricate. Once I really sort this stuff out, I'll blog something on this that's actually useful.

spring data JPA query against H2 fails with schema not found

1 Answers