Why JDBI 3 @ColumnName annotation doesn't work?

Question

I want to use jdbi 3 @ColumnName annotations to map column name of the table and java bean field, but at runtime jdbi throws an exception. My question is why is the exception thrown? Is my usage incorrect? How to solve this problem? Thanks

Database table:

CREATE TABLE `department` (
  `id` bigint(20)  NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '' ,
  `business_system` tinyint(4)  NOT NULL ,
  `functional` tinyint(4)  NOT NULL ,
  `remark` varchar(100) NOT NULL DEFAULT ''
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Java Bean:

@Data
public class Department {
    private Long id;

    @NotNull
    @Size(min = 3, max = 20)
    private String name;

    @NotNull
    @ColumnName("business_system")
    private Integer businessSystem;

    @NotNull
    @ColumnName("functional")
    private Integer functional;

    @Max(value = 200)
    private String remark;
  }

JDBI Update:

@Override
public Long create(Department department) {
    String sql = "INSERT INTO department (name, business_system, functional, remark)"
        + "VALUES (:name, :business_system, :functional, :remark)";

return jdbi.withHandle(handle -> handle.createUpdate(sql)
    .bindBean(department)
    .executeAndReturnGeneratedKeys()
    .mapTo(Long.class)
    .findOnly());

}

Unit test running result：

org.jdbi.v3.core.statement.UnableToExecuteStatementException: Unable to 
execute, no named parameter matches 'business_system'. [statement:"INSERT INTO 
department (name, business_system, functional, remark)VALUES (:name, 
:business_system, :functional, :remark)", rewritten:"INSERT INTO department 
(name, business_system, functional, remark)VALUES (:name, :business_system, 
:functional, :remark)", parsed:"ParsedSql{sql='INSERT INTO department (name, 
business_system, functional, remark)VALUES (?, ?, ?, ?)', 
parameters=ParsedParameters{positional=false, parameterNames=[name, 
business_system, functional, remark]}}", 
arguments:{ positional:{}, named:{}, finder:[{lazy bean property arguments 
"Department(id=null, name=test, businessSystem=2, functional=2, 
remark=test)"]}]

score 12 · Answer 1 · answered Dec 13 '18 at 00:49

Jdbi maintainer here. Four misunderstandings are conspiring to make your code not work:

First, @ColumnName defines a one-way mapping used only for mapping column names to object properties/fields/constructor parameters. So BeanMapper will honor the @ColumnName annotation, but bindBean() ignores it.

Second, BeanMapper only detects @ColumnName on getters and setters--not fields. Since you are using Lombok's @Data annotation, the @ColumnName annotation is actually put on the field. Lombok has a super ugly way to specify annotations to the getter or setter:

@NotNull
@Getter(onMethod=@__(@ColumnName("business_system")))
private Integer businessSystem;

@NotNull
@Getter(onMethod=@__(@ColumnName("functional")))
private Integer functional;

Third, bindBean() binds named parameters based on bean property names, not column names. So your named parameters should be in :camelCase instead of :snake_case:

INSERT INTO department (name, business_system, functional, remark)
VALUES (:name, :businessSystem, :functional, :remark)
--                      ^ camel case property name

Finally, Jdbi 3 already matches up snake-case column names to camel-case property names out of the box. For example: a column foo_bar would automatically be mapped to a fooBar property, no @ColumnName annotation required.

Why JDBI 3 @ColumnName annotation doesn't work?

1 Answers1