Column Not Found Error with H2 and R2DBC in Spring Boot with WebFlux

Question

I've created a Java Spring Boot service using the WebFlux reactive module, H2 in-memory database, and R2DBC reactive driver. This builds and runs fine on port 8081.

I've added a schema.sql file under main/resources, which contains the following:

CREATE TABLE contentitem ( contentItemId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, localizedName VARCHAR(100) NOT NULL);

I populate the table with a data.sql file in the same directory:

INSERT INTO contentitem (contentItemId, localizedName) VALUES (0, 'Zero');
INSERT INTO contentitem (contentItemId, localizedName) VALUES (1, 'One');
INSERT INTO contentitem (contentItemId, localizedName) VALUES (2, 'Two');

My ContentItem model is:

@Data
@AllArgsConstructor
@NoArgsConstructor
@Table("contentitem")
public class ContentItem {
    @Id
    private Integer contentItemId;
    private String localizedName;

My ContentItemController is:

@RestController
@RequestMapping("/contentItems")
public class ContentItemController {
    @Autowired
    private ContentItemService contentItemService;

    @GetMapping("/{contentItemId}")
    public Mono<ResponseEntity<ContentItem>> getContentItemByUserId(@PathVariable Integer contentItemId){
        Mono<ContentItem> contentItem = contentItemService.getContentItemById(contentItemId);
        return contentItem.map( u -> ResponseEntity.ok(u))
                .defaultIfEmpty(ResponseEntity.notFound().build());
    }

My ContentItemService is:

@Service
@Slf4j
@Transactional
public class ContentItemService {

    @Autowired
    private ContentItemRepository contentItemRepository;

    public Mono<ContentItem> getContentItemById(Integer contentItemId){
        return contentItemRepository.findByContentItemId(contentItemId);
    }

}

And my ContentItemRepository is:

public interface ContentItemRepository extends ReactiveCrudRepository<ContentItem,Integer> {
    Mono<ContentItem> findByContentItemId(Integer contentItemId);
}

When I call the running service with http://localhost:8081/contentItems/1, I get a 500 Server Error on the GET and the following in the log:

org.springframework.data.r2dbc.BadSqlGrammarException: executeMany; bad SQL grammar [SELECT contentitem.content_item_id, contentitem.localized_name FROM contentitem WHERE contentitem.content_item_id = $1]; nested exception is io.r2dbc.spi.R2dbcBadGrammarException: [42122] [42S22] Column "CONTENTITEM.CONTENT_ITEM_ID" not found; SQL statement:

Couple of questions:

1. Why are my simple, explicit, consistent column names (e.g. 'contentItemId') being mangled into underscored versions of themselves (e.g. 'CONTENT_ITEM_ID')? Just for giggles, I tried adding a '@Column("content_item_id")' annotation above the 'contentItemId' data member, but got the same result.
2. Why is the 'contentItemId' (or 'CONTENT_ITEM_ID') column not being found?

Complicating all this is that the H2 console, which I've enabled in the application.properties file with spring.h2.console.enabled=true is failing with a 404 Not Found error when I call it with http://localhost:8081/h2-console.

Answer 1

You need to use column annotation in your entity class

@Data
@AllArgsConstructor
@NoArgsConstructor
@Table("contentitem")
public class ContentItem {
    @Id
    @Column("contentItemId")
    private Integer contentItemId;
    @Column("localizedName")
    private String localizedName;
}

Or you should name columns with _

CREATE TABLE contentitem ( content_item_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, localized_name VARCHAR(100) NOT NULL);