1. Introduction
We shorty want to check if we can insert a POJO directly to a database without mapping each row to an insert. Try to experiment in my repo with JDBC
Normally we could do the following, but it can be very tedious for large POJOs
@Autowired
NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void testInsert() {
Customer hans = new Customer("Hans", "Mueller");
namedParameterJdbcTemplate.update("INSERT INTO customers(first_name, last_name) VALUES (" + hans.getFirstName()" +," + hans.getLastName() +")";
}As you can see in Line 6, this can get out of control, when you have many values, which you need to map from the Pojo. In our case it is only two Strings, firstName and lastName.
But can we do it directly?
The answer is yes!
2. Automatically Map the parameters.
Luckily, we can do with BeanPropertySqlParameterSource, which maps our getters automatically.
Customer hans = new Customer("Hans", "Mueller");
namedParameterJdbcTemplate.update("Insert into Customers (first_name, last_name) Values(:firstName, :lastName)", new BeanPropertySqlParameterSource(hans));
@Getter
@Setter
public class Customer {
private long id;
private String firstName, lastName;
}So we just reference the values with „:firstName, :lastName“ and the BeanPropertySqlParameterSource will map from our POJO.
3. Enum Mapping with BeanPropertySqlParameterSource
Let’s now say, we have a Enum in our POJO. This will first lead to a error now. When we have a simple Enum like the following, where the Enum value is equal to the Enum itself.
enum YesNo{
Yes,
No
}If we now can accept the String value of Enum.Yes ==> „Yes“ to be inserted in the Database, we just have to register the enum type
@Getter
@Setter
public class Customer {
private long id;
private String firstName, lastName;
private YesNo yesNoEnum
}
BeanPropertySqlParameterSource beanPropertySqlParameterSource = new BeanPropertySqlParameterSource(hans);
beanPropertySqlParameterSource.registerSqlType("yesNoEnum", Types.VARCHAR);If we have a more complex Enum, like this
@Getter
@Setter
enum Muscular{
VERY_MUSCULAR("im very musuclar"),
SKINY("im very skinny");
private final String value;
Muscular(String value) {
this.value = value;
}
}Now the Enum has a String value. For example Muscular.Skinny represents „I’m very skinny“, which we want to insert into the database.
Now registering the enum doesn’t work, since else „SKINNY“ will be inserted into the database.
namedParameterJdbcTemplate.update("Insert into Customers (first_name, last_name, muscular) Values(:firstName, :lastName, :muscular.value)", new BeanPropertySqlParameterSource(hans));Now we just do :muscular.value, which will call then the value-method, which will then insert „I’m very skinny“
4. Automatically Map full POJO
To insert a Pojo without writing the SQL, we can use SimpleJdbcInsert
SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate);
insert.withTableName("Customers");
Customer hansi = new Customer("Louisa", "Mueller");
BeanPropertySqlParameterSource hansiSql = new BeanPropertySqlParameterSource(hansi);
insert.execute(hansiSql);