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();
	}
}