doma2で複数のテーブルを結合した結果を取得する
複数のテーブルを連結したい
1つのテーブルの取得はできるけど複数テーブルの場合はどうするか確認
コレクト検索とかストリーム検索とかわからん
DDL
drop table if exists item; create table if not exists item ( id int not null auto_increment, name varchar(255) not null, price int not null, create_at datetime, update_at datetime, primary key(id) ); drop table if exists keiyaku; create table if not exists keiyaku ( id int not null auto_increment, name varchar(255) not null, item_id int not null, item_count int not null, create_at datetime, update_at datetime, primary key(id) ); insert into item(id, name, price, create_at, update_at) values(1, 'apple' , 120, current_timestamp, current_timestamp); insert into item(id, name, price, create_at, update_at) values(2, 'orange', 150, current_timestamp, current_timestamp); insert into item(id, name, price, create_at, update_at) values(3, 'grape' , 200, current_timestamp, current_timestamp); insert into keiyaku(name, item_id, item_count, create_at, update_at) values('tanaka', 1, 10, current_timestamp, current_timestamp); insert into keiyaku(name, item_id, item_count, create_at, update_at) values('satou', 2, 30, current_timestamp, current_timestamp); insert into keiyaku(name, item_id, item_count, create_at, update_at) values('inoue', 3, 4, current_timestamp, current_timestamp); insert into keiyaku(name, item_id, item_count, create_at, update_at) values('iizuka', 4, 21, current_timestamp, current_timestamp);
接続情報
AppConfig.java
package com.example.config; import javax.sql.DataSource; import org.seasar.doma.SingletonConfig; import org.seasar.doma.jdbc.Config; import org.seasar.doma.jdbc.dialect.Dialect; import org.seasar.doma.jdbc.dialect.MysqlDialect; import org.seasar.doma.jdbc.tx.LocalTransactionDataSource; import org.seasar.doma.jdbc.tx.LocalTransactionManager; import org.seasar.doma.jdbc.tx.TransactionManager; @SingletonConfig public class AppConfig implements Config { private static final AppConfig INSTANCE = new AppConfig(); private final Dialect dialect; private final LocalTransactionDataSource dataSource; private final TransactionManager transactionManager; private AppConfig() { dialect = new MysqlDialect(); dataSource = new LocalTransactionDataSource("jdbc:mysql://192.168.33.10:3306/myapp?useSSL=false", "app", "Password123@"); transactionManager = new LocalTransactionManager(dataSource.getLocalTransaction(getJdbcLogger())); } @Override public DataSource getDataSource() { return dataSource; } @Override public Dialect getDialect() { return dialect; } @Override public TransactionManager getTransactionManager() { return transactionManager; } public static AppConfig singleton() { return INSTANCE; } }
エンティティ
Item.java
package com.example.entity; import java.time.LocalDateTime; import org.seasar.doma.Column; import org.seasar.doma.Entity; import org.seasar.doma.GeneratedValue; import org.seasar.doma.GenerationType; import org.seasar.doma.Id; import org.seasar.doma.Table; @Entity @Table(name = "item") public class Item { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @Column(name = "name") private String name; @Column(name = "price") private Integer price; @Column(name = "create_at") private LocalDateTime createAt; @Column(name = "update_at") private LocalDateTime updateAt; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getPrice() { return price; } public void setPrice(Integer price) { this.price = price; } public LocalDateTime getCreateAt() { return createAt; } public void setCreateAt(LocalDateTime createAt) { this.createAt = createAt; } public LocalDateTime getUpdateAt() { return updateAt; } public void setUpdateAt(LocalDateTime updateAt) { this.updateAt = updateAt; } }
Keiyaku.java
package com.example.entity; import java.time.LocalDateTime; import org.seasar.doma.Column; import org.seasar.doma.Entity; import org.seasar.doma.GeneratedValue; import org.seasar.doma.GenerationType; import org.seasar.doma.Id; import org.seasar.doma.Table; @Entity @Table(name = "keiyaku") public class Keiyaku { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @Column(name = "name") private String name; @Column(name = "item_id") private Integer itemId; @Column(name = "item_count") private Integer itemCount; @Column(name = "create_at") private LocalDateTime createAt; @Column(name = "update_at") private LocalDateTime updateAt; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getItemId() { return itemId; } public void setItemId(Integer itemId) { this.itemId = itemId; } public Integer getItemCount() { return itemCount; } public void setItemCount(Integer itemCount) { this.itemCount = itemCount; } public LocalDateTime getCreateAt() { return createAt; } public void setCreateAt(LocalDateTime createAt) { this.createAt = createAt; } public LocalDateTime getUpdateAt() { return updateAt; } public void setUpdateAt(LocalDateTime updateAt) { this.updateAt = updateAt; } }
Dao
KeiyakuDao.java
package com.example.dao; import java.util.List; import org.seasar.doma.Dao; import org.seasar.doma.Select; import com.example.config.AppConfig; import com.example.entity.KeiyakuItem; @Dao(config = AppConfig.class) public interface KeiyakuDao { @Select List<KeiyakuItem> findKeiyakuMeisaiAll(); }
SQL
findKeiyakuMeisaiAll.sql
select keiyaku.id as id , keiyaku.name as name , keiyaku.item_id as item_id , item.name as item_name , item.price as item_price , keiyaku.item_count as item_count from keiyaku left outer join item on item.id = keiyaku.item_id
1つのSQLに複数のテーブルを設定すること自体は可能だが、設定するものが必要なので別途エンティティを追加する
カスタムエンティティ
KeiyakuItem.java
package com.example.entity; import org.seasar.doma.Column; import org.seasar.doma.Entity; @Entity public class KeiyakuItem { @Column(name = "id") private Integer id; @Column(name = "name") private String name; @Column(name = "item_id") private Integer itemId; @Column(name = "item_name") private String itemName; @Column(name = "item_price") private Integer itemPrice; @Column(name = "item_count") private Integer itemCount; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getItemId() { return itemId; } public void setItemId(Integer itemId) { this.itemId = itemId; } public String getItemName() { return itemName; } public void setItemName(String itemName) { this.itemName = itemName; } public Integer getItemPrice() { return itemPrice; } public void setItemPrice(Integer itemPrice) { this.itemPrice = itemPrice; } public Integer getItemCount() { return itemCount; } public void setItemCount(Integer itemCount) { this.itemCount = itemCount; } public String toString() { String res = ""; res += this.getId() + " "; res += this.getName() + " "; res += this.getItemId() + " "; res += this.getItemName() + " "; res += this.getItemPrice() + " "; res += this.getItemCount() + " "; return res; } }
これでSQLの結果フィールドに値が設定される
実行プログラム
Sample01.java
package com.example; import java.util.List; import org.seasar.doma.jdbc.tx.TransactionManager; import com.example.config.AppConfig; import com.example.dao.KeiyakuDao; import com.example.dao.KeiyakuDaoImpl; import com.example.entity.KeiyakuItem; public class Sample01 { public static void main(String[] args) { TransactionManager tm = AppConfig.singleton().getTransactionManager(); tm.required(new Runnable(){ @Override public void run() { KeiyakuDao dao = new KeiyakuDaoImpl(); List<KeiyakuItem> list = dao.findKeiyakuMeisaiAll(); for(KeiyakuItem entity : list) { System.out.println(entity.toString()); } } }); } }
↓
1 04, 2017 10:06:26 午前 org.seasar.doma.jdbc.tx.LocalTransaction begin 情報: [DOMA2063] ローカルトランザクション[1190654826]を開始しました。 1 04, 2017 10:06:26 午前 com.example.dao.KeiyakuDaoImpl findKeiyakuMeisaiAll 情報: [DOMA2220] ENTER : クラス=[com.example.dao.KeiyakuDaoImpl], メソッド=[findKeiyakuMeisaiAll] 1 04, 2017 10:06:27 午前 com.example.dao.KeiyakuDaoImpl findKeiyakuMeisaiAll 情報: [DOMA2076] SQLログ : SQLファイル=[META-INF/com/example/dao/KeiyakuDao/findKeiyakuMeisaiAll.sql], select keiyaku.id as id , keiyaku.name as name , keiyaku.item_id as item_id , item.name as item_name , item.price as item_price , keiyaku.item_count as item_count from keiyaku left outer join item on item.id = keiyaku.item_id 1 04, 2017 10:06:27 午前 com.example.dao.KeiyakuDaoImpl findKeiyakuMeisaiAll 情報: [DOMA2221] EXIT : クラス=[com.example.dao.KeiyakuDaoImpl], メソッド=[findKeiyakuMeisaiAll] 1 tanaka 1 apple 120 10 2 satou 2 orange 150 30 3 inoue 3 grape 200 4 4 inoue 4 null null 21 1 04, 2017 10:06:27 午前 org.seasar.doma.jdbc.tx.LocalTransaction commit 情報: [DOMA2067] ローカルトランザクション[1190654826]をコミットしました。 1 04, 2017 10:06:27 午前 org.seasar.doma.jdbc.tx.LocalTransaction commit 情報: [DOMA2064] ローカルトランザクション[1190654826]を終了しました。
関連がないところはnullが設定される。