Apache Commons DbUtilsでJOINしたデータを扱ってみる

www.businessinsider.jp

⇧ amazing...

Apache Commons DbUtilsでJOINしたデータを扱ってみる

というわけで、

dk521123.hatenablog.com

⇧ 上記サイト様を参考に試してみます。

利用するプロジェクトなどは、

ts0818.hatenablog.com

⇧ 上記の記事のもので。

今回追加で使用するテーブルは、address_detailというテーブルです。

一応、テーブルを作成するSQLを記載。

CREATE TABLE "TS0818"."ADDRESS_DETAIL" 
   (
    "ADDRESS_ID" CHAR(20) NOT NULL ENABLE, 
	"POST_CODE" CHAR(7) NOT NULL ENABLE, 
	"PREFECTURE" VARCHAR2(50) NOT NULL ENABLE, 
	"CITIES" VARCHAR2(50) NOT NULL ENABLE, 
	"ADDRESS_01" VARCHAR2(255) NOT NULL ENABLE, 
	"ADDRESS_02" VARCHAR2(255), 
	"ROOM_NUMBER" NUMBER, 
	"FLOOR" NUMBER, 
	"USER_ID" CHAR(13) NOT NULL ENABLE, 
	"INSERT_USER" VARCHAR2(50) NOT NULL ENABLE, 
	"INSERT_DATE" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATE_USER" VARCHAR2(50), 
	"UPDATE_DATE" TIMESTAMP (6), 
	 PRIMARY KEY ("ADDRESS_ID", "USER_ID")
   )
TABLESPACE "TEST";

⇧ スキーマ名とTABLESPACE名はご自分の環境に合わせてください。

TABLESPACEについては、以下のSQLで確認できるようです。

SELECT * FROM DBA_TABLESPACES;

そしたらば、データをINSERTしておきます。

INSERT ALL
INTO ADDRESS_DETAIL (address_id, post_code, prefecture, cities, address_01, user_id, insert_user, insert_date) VALUES ('1', '1780063', '東京都', '練馬区', '東大泉x-xx-xx', '1', 'admin', CURRENT_TIMESTAMP)
INTO ADDRESS_DETAIL (address_id, post_code, prefecture, cities, address_01, user_id, insert_user, insert_date) VALUES ('2', '1800001', '東京都', '武蔵野市', '吉祥寺北町x-xx-xx', '2', 'admin', CURRENT_TIMESTAMP)
INTO ADDRESS_DETAIL (address_id, post_code, prefecture, cities, address_01, user_id, insert_user, insert_date) VALUES ('3', '1500013', '東京都', '渋谷区', '恵比寿x-xx-xx', '3', 'admin', CURRENT_TIMESTAMP)
SELECT * FROM DUAL;

では、EclipseでプロジェクトにEntityクラスなどを追加で。

⇧ 上図の選択されてるものが今回追加したものになります。

では、ソースコードを記載。

■/spring-mvc-jsp/src/main/java/com/entity/AddressDetail.java

package com.entity;

import java.time.OffsetDateTime;

import lombok.Data;

@Data
public class AddressDetail {

	private String addressId;
	
	private String postCode;
	
	private String prefecture;
	
	private String cities;
	
	private String address01;
	
	private String address02;
	
	private Integer roomNumber;
	
	private Integer floor;
	
	private String userId;
	
	private String insertUser;
	
	private OffsetDateTime insertDate;
	
	private String updateUser;
	
	private OffsetDateTime updateDate;
}

■/spring-mvc-jsp/src/main/java/com/entity/ex/ExUserAndAddressDetail.java

package com.entity.ex;

import lombok.Data;

@Data
public class ExUserAndAddressDetail {
	private String userId;
	
	private String lastName;
	
	private String firstName;
	
	private String gender;
	
	private String birthday;

	private String addressId;
	
	private String postCode;
	
	private String prefecture;
	
	private String cities;
	
	private String address01;
	
	private String address02;
	
	private Integer roomNumber;
	
	private Integer floor;
}

■/spring-mvc-jsp/src/main/java/com/dao/ex/ExUserAndAddressDetailDao.java

package com.dao.ex;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import com.entity.ex.ExUserAndAddressDetail;

@Component
public class ExUserAndAddressDetailDao {
	@Autowired
	@Qualifier(value = "queryRunner")
	private QueryRunner queryRunner;
	
	public List<ExUserAndAddressDetail> findAll() throws SQLException {
		ResultSetHandler<List<ExUserAndAddressDetail>> resultSetHandler =
                new BeanListHandler<ExUserAndAddressDetail>(ExUserAndAddressDetail.class, new BasicRowProcessor(new GenerousBeanProcessor()));
		String query = new StringBuilder()
				.append("SELECT ")
				.append("ud.user_id ")
				.append(",ud.last_name ")
				.append(",ud.first_name ")
				.append(",ud.gender ")
				.append(",ud.birthday ")	
				.append(",ad.address_id ")
				.append(",ad.post_code ")
				.append(",ad.prefecture ")
				.append(",ad.cities ")
				.append(",ad.address_01 ")
				.append(",ad.address_02 ")
				.append(",ad.room_number ")
				.append(",ad.floor ")
				.append("FROM user_detail ud ")
				.append("LEFT JOIN address_detail ad ")
				.append("ON ud.user_id = ad.user_id")
				.toString();
		final List<ExUserAndAddressDetail> exUserAndAddressDetailList = queryRunner.query(query, resultSetHandler);
		return exUserAndAddressDetailList;
	}
}

■/spring-mvc-jsp/src/main/java/com/service/ex/ExUserAndAddressDetailServiceImpl.java

package com.service.ex;

import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.dao.ex.ExUserAndAddressDetailDao;
import com.entity.ex.ExUserAndAddressDetail;

@Service
public class ExUserAndAddressDetailServiceImpl {

	@Autowired
	private ExUserAndAddressDetailDao exUserAndAddressDetailDao;
	
	public List<ExUserAndAddressDetail> findAll() throws SQLException {
		return exUserAndAddressDetailDao.findAll();
	}
}

■/spring-mvc-jsp/src/main/java/com/controller/HomeController.java

package com.controller;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.servlet.ModelAndView;

import com.entity.UserDetail;
import com.entity.ex.ExUserAndAddressDetail;
import com.service.UserDetailServiceImpl;
import com.service.ex.ExUserAndAddressDetailServiceImpl;

@Controller
public class HomeController {
	
	@Autowired
	private UserDetailServiceImpl userDetailServiceImpl;

	@Autowired
	private ExUserAndAddressDetailServiceImpl exUserAndAddressDetailServiceImpl;
	
	@GetMapping("home")
	public String index(Model model) {
		List<String> list = new ArrayList<>();
		list.add("リスト1");
		list.add("リスト2");
		list.add("リスト3");
		model.addAttribute("strList", list);
		return "home/index";
	}
	
	@GetMapping("find-all")
	public ModelAndView findAll() throws SQLException {
		List<UserDetail> userDetailList = userDetailServiceImpl.findAll();
		ModelAndView mv = new ModelAndView("home/index");
		mv.addObject("userDetailList", userDetailList);
		return mv;
	}
	
	@GetMapping("find-all-ex")
	public ModelAndView findAllEx() throws SQLException {
		List<ExUserAndAddressDetail> exUserAndAddressDetailList = exUserAndAddressDetailServiceImpl.findAll();
		ModelAndView mv = new ModelAndView("home/index");
		mv.addObject("exUserAndAddressDetailList", exUserAndAddressDetailList);
		return mv;
	}
}

最後にjspファイルに追記。

■/spring-mvc-jsp/src/main/webapp/WEB-INF/views/home/index.jsp

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page language="java" contentType="text/html; charset=utf-8"%>
<html>
<head>
<title>Home</title>
</head>
<body>
	<c:forEach var="str" items="${strList}">
		<p>${str}</p>
	</c:forEach>
	<c:forEach var="userDetail" items="${userDetailList}">
		<p>
			<span>${userDetail.lastName}</span><span>${userDetail.firstName}
			</span>
		</p>
	</c:forEach>
	<table>
		<thead>
			<tr>
				<th>Name</th>
				<th>Gender</th>
				<th>Birthday</th>
				<th>postCode</th>
				<th>Address</th>
			</tr>
		</thead>
		<tbody>
			<c:forEach var="exUserAndAddressDetail"
				items="${exUserAndAddressDetailList}">
				<tr>
					<td>
					  <span>${exUserAndAddressDetail.lastName}</span>
					  <span>${exUserAndAddressDetail.firstName}</span>
					</td>
					<td>
					  <span>${exUserAndAddressDetail.gender}</span>
					</td>
					<td>
					  <span>${exUserAndAddressDetail.birthday}</span>
					</td>
					<td>
					  <span>${exUserAndAddressDetail.postCode}</span>
					</td>
					<td>
					  <span>${exUserAndAddressDetail.prefecture}</span>
					  <span>${exUserAndAddressDetail.cities}</span>
					  <span>${exUserAndAddressDetail.address01}</span>
					</td>
				</tr>
			</c:forEach>
		</tbody>
	</table>
</body>
</html>

で、編集と保存が終わったら、サーバーで実行。