データアクセス：ネイティブなSQL文を実行する　STS3＋Spring Boot+ EntityManager＋MySQL

JpaRepositoryを使ったデータアクセスは非常に楽です。

でも、どこかで複雑なSQLが必要になることが全く無いとも思えません。

その時に困らないように、やり方を確認しておこうと思います。

なお、STS3(3.9.6)＋SpringBoot2.0＋tymeleaf3.0迄動作確認しています。

f:id:arakan_no_boku:20190222012501j:plain

ネイティブSQLを実行できる方法

方法は色々あります。が、あれもこれもと欲張らず、一番何でもできるネイティブSQLを実行できる方法だけ確認します。

確認に利用するテーブルはこんな感じです。

f:id:arakan_no_boku:20170228225449j:plain

このテーブルをJOINして、パーソンID（pid)、名前（name)、値（value）を取得するSQLを実行するテストを、JUNIT4で書くやり方を試してみます。

テストクラス

まず、テストクラスを新規作成します。

テストランナーを走らせるアノテーションを書き、EntityManagerがDIできるように、@Autowiredアノテーションを指定します。

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestPersonRepository {

@Autowired
public EntityManager entityManager;

取得用のデータをセットするところは、jparepositoryを使うので省略します。

arakan-pgm-ai.hatenablog.com

SELECT文

まず、SELECT文をQuery型オブジェクトに変換します。

SQLのパラメータは「?」でないとだめみたいです。

Query q = entityManager.createNativeQuery("select b.pid,b.name,a.value from person_entity a left join person_id_entity b on a.pid = b.pid where a.pid = ?");

SQLの？に対応するパラメータ値をセットします。

？の登場順に対応づければいいみたいですが、今回は１つだけです。

ただ、1から始まる点が注意です。

q.setParameter(1, "pid-key001");

実行してリストを取得します。

@SuppressWarnings("unchecked")はなくてもいいですけど、設定によっては、ワーニングがでるのでつけてます。

@SuppressWarnings("unchecked")
List<Object> result = q.getResultList();

あとは、取得用のBeanクラスのリストのオブジェクトを生成して、ループの中でキャストしながら、セットしていきます。

取得した値を受け取るクラス

取得した値を受け取るクラスを用意します。

SELECT文で取得する値にあわせて設定します。

今回はStringだけですが、数値はBigDecimalで受けないといけないとかあるみたいなので注意してください。（Getter、Setterは省略してますけど、あるものと思ってください）

public class PersonBean {

private String pid;
private String name;
private String value;

}

あとは、読めばわかりますって感じです。

List<PersonBean> pq = new ArrayList<PersonBean>();
for(Object it:result){
PersonBean b = new PersonBean();
b.setPid((String)it[0]);
b.setName((String)it[1]);
b.setValue((String)it[2]);
pq.add(b);
}

とりあえず、これができれば、大抵のことはできます。保守面を考えると必要最低限にとどめるようにしたほうがよさそうですけど。