Spring BootでMySQLのInsert文を実行する

Spring BootでMySQLのInsert文を実行する
1. 関連

Spring BootでMySQLのInsert文を実行する

Spring BootでMySQLのInsert文を実行してみます。

エントリポイントのクラスです。

package com.sql;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;

import com.sql.domain.Customer;
import com.sql.repository.CustomerRepository;

@EnableAutoConfiguration
@ComponentScan
public class App implements CommandLineRunner{
  @Autowired
  private CustomerRepository customerRepository;

  @Override
  public void run(String... strings){
    Customer created = customerRepository.save(new Customer(null,"nakanishi","kenta"));
  }

  public static void main(String[] args) {
    SpringApplication.run(App.class, args);
  }
}

「src/main/resources/config」配下にapplication.propertiesを定義します。

spring.datasource.url=jdbc:mysql://192.168.1.4:3306/testdb
spring.datasource.username=test
spring.datasource.password=test
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

データソースを取得します。

package com.sql.config;
import javax.sql.DataSource;

import net.sf.log4jdbc.Log4jdbcProxyDataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class AppConfig{
  @Autowired
  DataSourceProperties dsp;
  DataSource ds;

  @Bean
  DataSource getDataSource() {
    DataSourceBuilder factory = DataSourceBuilder
      .create(dsp.getClassLoader())
      .url(dsp.getUrl())
      .username(dsp.getUsername())
      .password(dsp.getPassword());
    ds = factory.build();
    return ds;
  }

  @Bean
  DataSource dataSource(){
    return new Log4jdbcProxyDataSource(ds);
  }
}

アクションフォームは以下です。Lombokを使用しています。

package com.sql.domain;

import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
public class Customer {
  private Integer id;
  private String firstname;
  private String lastname;
}

最後にリポジトリクラスです。プライマリキーのIDはexecuteAndReturnKeyメソッドで自動採番されたキーが返ってきて、DUPLICATEエラーが発生しないようになっています。

package com.sql.repository;

import java.util.List;

import javax.annotation.PostConstruct;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.sql.domain.Customer;
@Repository
@Transactional
public class CustomerRepository {
  @Autowired
  NamedParameterJdbcTemplate jdbcTemplate;
  SimpleJdbcInsert insert;

  @PostConstruct
  public void init(){
    insert = new SimpleJdbcInsert((JdbcTemplate)jdbcTemplate.getJdbcOperations()).withTableName("tbl_customer").usingGeneratedKeyColumns("id");
  }

  private static final RowMapper<Customer> customerRowMapper = (rs,i) -> {
    Integer id = rs.getInt("ID");
    String firstname = rs.getString("firstname");
    String lastname = rs.getString("lastname");
    String age = rs.getString("age");

    return new Customer(id,firstname,lastname,age);
  };

  public List<Customer> findAll(){
    List<Customer> customers = jdbcTemplate.query("SELECT ID,FIRSTNAME,LASTNAME,AGE FROM TBL_CUSTOMER",customerRowMapper);
    return customers;
  }

  public Customer save(Customer customer){
    SqlParameterSource param = new BeanPropertySqlParameterSource(customer);
    if(customer.getId() == null){
      Number key = insert.executeAndReturnKey(param);
      customer.setId(key.intValue());
    }else{
      jdbcTemplate.update("UPDATE TBL_CUSTOMER SET ID=:id,FIRSTNAME=:firstname,LASTNAME=:lastname", param);
    }
    return customer;
  }
}