SAStrutsでJOINを行なう際、どう書くか毎回忘れてしまう。
単純なテストを以下に記す。
■データベースのスキーマ
CREATE DATABASE db_test;
USE db_test;
CREATE TABLE channels (
channel_id INTEGER NOT NULL AUTO_INCREMENT
, title TEXT NOT NULL
, PRIMARY KEY (channel_id)
);
CREATE TABLE items (
item_id INTEGER NOT NULL AUTO_INCREMENT
, title TEXT NOT NULL
, url VARCHAR(255) NOT NULL
, channel_id INTEGER NOT NULL
, PRIMARY KEY (item_id)
, UNIQUE (url)
);
INSERT INTO channels (title) VALUES("情報考学 Passion For The Future");
INSERT INTO channels (title) VALUES("RwJ");
INSERT INTO items (title,url,channel_id) VALUES("無趣味のすすめ","http://www.ringolab.com/note/daiya/2009/07/post-1021.html",1);
INSERT INTO items (title,url,channel_id) VALUES("アイデア・スイッチ 次々と発想を生み出す装置","http://www.ringolab.com/note/daiya/2009/07/post-1023.html",1);
INSERT INTO items (title,url,channel_id) VALUES("ベガーズ・イン・スペイン","http://www.ringolab.com/note/daiya/2009/07/post-1022.html",1);
INSERT INTO items (title,url,channel_id) VALUES("psコマンドはオプションに「-」がいらないみたい","http://blog.mikuriya.biz/archives/310",2);
INSERT INTO items (title,url,channel_id) VALUES("[SAStruts]Tomcat起動時に初回のみ呼び出す独自のクラスでデータベース接続","http://blog.mikuriya.biz/archives/171",2);
INSERT INTO items (title,url,channel_id) VALUES("[PHP]フレームワーク「CakePHP」のインストールと設定","http://blog.mikuriya.biz/archives/7",2);
■jdbc.diconを編集
今回は、MySQLを使用するので、そのあたりを編集する。
<components namespace="jdbc"> ... <component name="xaDataSource" class="org.seasar.extension.dbcp.impl.XADataSourceImpl"> <property name="driverClassName"> "com.mysql.jdbc.Driver" </property> <property name="URL"> "jdbc:mysql://localhost:3306/db_test" </property> <property name="user">"root"</property> <property name="password">""</property> </component> ... </components>
■channelsテーブルのクラス
package biz.mikuriya.entity;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
@Entity
@Table(name = "channels")
public class Channel {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
public int channelId;
public String title;
@OneToMany(mappedBy = "parent") // Itemに定義する変数名がparentになる
public List<Item> childs;
}
■itemsテーブルのクラス
package biz.mikuriya.entity;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
@Entity
@Table(name = "items")
public class Item {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
public int itemId;
public String title;
public String url;
public int channelId;
@ManyToOne
@JoinColumn(name = "channel_id")
public Channel parent; // Channel.itemsのmappedByの値と変数名を合わせる
}
■動作テスト用実行クラス
package biz.mikuriya;
import java.util.List;
import org.seasar.extension.jdbc.JdbcManager;
import org.seasar.framework.container.S2Container;
import org.seasar.framework.container.factory.SingletonS2ContainerFactory;
import biz.mikuriya.entity.Channel;
import biz.mikuriya.entity.Item;
public class DBTest {
private JdbcManager jdbcManager;
/**
* データベース接続
*/
public DBTest() {
SingletonS2ContainerFactory.init();
S2Container container = SingletonS2ContainerFactory.getContainer();
this.jdbcManager = (JdbcManager) container
.getComponent(JdbcManager.class);
}
/**
* 普通にSELECT
*/
public void selectChannel() {
System.out.println("CHANNELS************************************");
List<Channel> channels = this.jdbcManager.from(Channel.class)
.getResultList();
for (Channel channel : channels) {
System.out.println("\t" + channel.channelId + "\t" + channel.title);
System.out.println("\t--------");
}
}
/**
* 普通にSELECT
*/
public void selectItem() {
System.out.println("ITEMS************************************");
List<Item> items = this.jdbcManager.from(Item.class).getResultList();
for (Item item : items) {
System.out.println("\t" + item.itemId + "\t" + item.channelId
+ "\t" + item.title + "\t" + item.url);
System.out.println("\t--------");
}
}
/**
* 一対多の構造で取得する場合
*/
public void joinOneToMany() {
System.out.println("JOIN************************************");
List<Channel> channels = this.jdbcManager.from(Channel.class)
.innerJoin("childs").getResultList(); // JOINには、変数名を指定する
for (Channel channel : channels) {
System.out.println("\t" + channel.channelId + "\t" + channel.title);
for (Item item : channel.childs) {
System.out.println("\t\t" + item.itemId + "\t" + item.channelId
+ "\t" + item.title + "\t" + item.url);
System.out.println("\t\t--------");
}
System.out.println("\t--------");
}
}
/**
* 一対一の構造で取得する場合
*/
public void joinOneToOne() {
System.out.println("JOIN************************************");
List<Item> items = this.jdbcManager.from(Item.class)
.innerJoin("parent").getResultList(); // JOINには、変数名を指定する
for (Item item : items) {
System.out.println("\t" + item.itemId + "\t" + item.channelId
+ "\t" + item.title + "\t" + item.url + "\t"
+ item.parent.channelId + "\t" + item.parent.title);
System.out.println("\t--------");
}
}
public static void main(String[] args) {
DBTest test = new DBTest();
test.selectChannel();
test.selectItem();
test.joinOneToMany();
test.joinOneToOne();
}
}
