Spring - 게시판 작성

출처 : 개발하는 씽매려기 :: MyBatis] 쿼리에서 특정 문자 제거 (if 사용시)
MyBatis – MyBatis 3 | 동적 SQL

Domain 객체

com.iot.domain.DepartmentsDomain 클래스

package com.iot.domain;

import net.sf.json.JSONObject;

public class DepartmentsDomain {

	// pk
	private int department_id;

	private String department_name;
	private int manager_id;
	private int location_id;

	private String delete_flag = "N";

	public void setDepartment_id(int department_id) {
		this.department_id = department_id;
	}

	public int getDepartment_id() {
		return this.department_id;
	}

	public void setDepartment_name(String department_name) {
		this.department_name = department_name;
	}

	public String getDepartment_name() {
		return this.department_name;
	}

	public void setManager_id(int manager_id) {
		this.manager_id = manager_id;
	}

	public int getManager_id() {
		return this.manager_id;
	}

	public void setLocation_id(int location_id) {
		this.location_id = location_id;
	}

	public int getLocation_id() {
		return this.location_id;
	}

	public String getDelete_flag() {
		return delete_flag;
	}

	public void setDelete_flag(String delete_flag) {
		this.delete_flag = delete_flag;
	}

	public JSONObject getJSONObject() {
		JSONObject jobj = new JSONObject();
		jobj.put("department_id", this.department_id);
		jobj.put("department_name", this.department_name);
		jobj.put("manager_id", this.manager_id);
		jobj.put("location_id", this.location_id);
		return jobj;
	}

}

Domain 목록 객체

com.iot.domain.DepartmentsList 클래스

package com.iot.domain;

import java.util.List;

import com.iot.util.FormBase;

public class DepartmentsList extends FormBase {

	public final static String CMD_SELECT = "select";

	public final static String CMD_DELETE = "delete";

	private String command = CMD_SELECT;

	private List<DepartmentsDomain> datas;

	public String getCommand() {
		return command;
	}

	public void setCommand(String command) {
		this.command = command;
	}

	public List<DepartmentsDomain> getDatas() {
		return datas;
	}

	public void setDatas(List<DepartmentsDomain> datas) {
		this.datas = datas;
	}
}

Mapper 인터페이스

package com.iot.persistence;

import java.util.List;
import java.util.Map;

import com.iot.domain.DepartmentsDomain;

public interface DepartmentsMapper {

	public DepartmentsDomain selectDepartments(Map<String, Object> params);

	public void insertDepartments(DepartmentsDomain departments);

	public void updateDepartments(DepartmentsDomain departments);

	public void deleteDepartments(Map<String, Object> params);

	public int getCount();

	public int getCountFormData(Map<String, Object> params);

	public List<DepartmentsDomain> listDepartments(Map<String, Object> map);

}

Mapper SQL Map 파일

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.iot.persistence.DepartmentsMapper">
	
	<!-- selectDepartments -->
	<select id="selectDepartments" parameterType="map" resultType="com.iot.domain.DepartmentsDomain">
		select department_id
		       ,department_name
		       ,manager_id
		       ,location_id
		  from departments
		 where department_id = #{department_id}
	</select>
	
	<!-- updateDepartments -->
	<update id="updateDepartments" parameterType="com.iot.domain.DepartmentsDomain" statementType="PREPARED">
		update departments
		  <trim prefix="SET" suffixOverrides=",">
		    <if test="department_name != null">department_name = #{department_name, jdbcType=VARCHAR} ,</if>
		    <if test="manager_id != null">manager_id = #{manager_id, jdbcType=INTEGER} ,</if>
		    <if test="location_id != null">location_id = #{location_id, jdbcType=INTEGER} ,</if>
		  </trim>
		where department_id = #{department_id}
	</update>
	
	<!-- insertDepartments -->
	<insert id="insertDepartments" parameterType="com.iot.domain.DepartmentsDomain" statementType="PREPARED">
		insert into departments(
		<trim suffixOverrides=",">
		  <if test="department_name != null">department_name ,</if>
		  <if test="manager_id != null">manager_id ,</if>
		  <if test="location_id != null">location_id ,</if>
		</trim>
		) values	(
		<trim suffixOverrides=",">
		  <if test="department_name != null">#{department_name, jdbcType=VARCHAR} ,</if>
		  <if test="manager_id != null">#{manager_id, jdbcType=INTEGER} ,</if>
		  <if test="location_id != null">#{location_id, jdbcType=INTEGER} ,</if>
		</trim>
		)
	</insert>
	
	<!-- deleteDepartments -->
	<delete id="deleteDepartments" parameterType="map" statementType="PREPARED">
		delete from departments
		 where department_id = #{department_id}
	</delete>
	
	<!-- getCount -->
	<select id="getCount" resultType="int">
		select count(*)
		  from departments
	</select>

	<!-- getCountFormData -->
	<select id="getCountFormData" parameterType="map" resultType="int">
		select count(*)
		  from departments
		  <trim prefix="WHERE" prefixOverrides="AND |OR ">
		    <if test="searchstr != null">and department_name like '%${searchstr}%'</if>
		  </trim>
	</select>
	
	<!-- listDepartments -->
	<select id="listDepartments" parameterType="map" resultType="com.iot.domain.DepartmentsDomain">
		select * 
		  from departments
		  <trim prefix="WHERE" prefixOverrides="AND |OR ">
		    <if test="searchstr != null">and department_name like '%${searchstr}%'</if>
		  </trim>
		  <if test="defaultSize != null">
		  	limit #{defaultSize} offset #{offset}
		  </if>
	</select>

</mapper>

MyBatis 동적SQL

맨 끝에 있는 콤마(,)를 제거하는 경우

문장의 마지막 단어가 suffixOverrides단어( 콤마(,) )인 경우 제거하고, 문장의 시작은 prefix단어(SET)를 붙입니다.

		  <trim prefix="SET" suffixOverrides=",">
		    <if test="department_name != null">department_name = #{department_name, jdbcType=VARCHAR} ,</if>
		    <if test="manager_id != null">manager_id = #{manager_id, jdbcType=INTEGER} ,</if>
		    <if test="location_id != null">location_id = #{location_id, jdbcType=INTEGER} ,</if>
		  </trim>

맨 앞에 있는 연산자를(AND 또는 OR) 제거하는 경우

문장의 시작 단어가 prefixOverrides 단어(AND 또는 OR)인 경우 prefixOverrides 단어를 제거하고 prefix단어(WHERE)를 붙입니다.

		  <trim prefix="WHERE" prefixOverrides="AND |OR ">
		    <if test="searchstr != null">and department_name like '%${searchstr}%'</if>
		  </trim>

Service 객체

package com.iot.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

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

import com.iot.domain.DepartmentsDomain;
import com.iot.domain.DepartmentsList;
import com.iot.persistence.DepartmentsMapper;

@Service
public class DepartmentsService {

	public final static int pagerowcnt = 10;

	@Autowired
	private DepartmentsMapper departmentsMapper;

	public DepartmentsDomain selectDepartments(int departmentId) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("department_id", departmentId);
		return departmentsMapper.selectDepartments(params);
	}

	public void insertDepartments(DepartmentsDomain departments) {
		departmentsMapper.insertDepartments(departments);
	}

	public void updateDepartments(DepartmentsDomain departments) {
		departmentsMapper.updateDepartments(departments);
	}

	public void deleteDepartments(int departmentId) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("department_id", departmentId);
		departmentsMapper.deleteDepartments(params);
	}

	public void deleteDepartments(Map<String, Object> params) {
		departmentsMapper.deleteDepartments(params);
	}

	public int getCount() {
		return departmentsMapper.getCount();
	}

	public int getCountFormData(DepartmentsList formData) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("searchstr", formData.getSearchString());
		return departmentsMapper.getCountFormData(params);
	}

	public List<DepartmentsDomain> listDepartments(int page) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		if (page>0) {
			params.put("defaultSize", pagerowcnt);
			params.put("offset", pagerowcnt * (page - 1));
		}
		return departmentsMapper.listDepartments(params);
	}

	public List<DepartmentsDomain> listDepartmentsFormData(DepartmentsList formData) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("defaultSize", pagerowcnt);
		params.put("offset", pagerowcnt * (formData.getPage() - 1));
		params.put("searchstr", formData.getSearchString());
		return departmentsMapper.listDepartments(params);
	}

}

Controller 클래스

package com.iot.ui.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import com.iot.domain.DepartmentsDomain;
import com.iot.domain.DepartmentsList;
import com.iot.service.DepartmentsService;

@Controller
public class DepartmentsController {

	@Autowired
	private DepartmentsService service;

	private void list(DepartmentsList formData, ModelMap modelMap) throws Exception {
		int record_count = 0;

		if (formData.getSearchString()!=null && formData.getSearchString().length()>0) {
			record_count = service.getCountFormData(formData);
		}

		//
		if (record_count==0) {
			record_count = service.getCount();
		}

		formData.setPageSize(DepartmentsService.pagerowcnt);
		formData.setTotalCount(record_count);

		System.out.println("record_count = " + record_count);

		List<DepartmentsDomain> list = service.listDepartmentsFormData(formData);

		formData.setDatas(list);
		modelMap.addObject("formData", formData);
		modelMap.addObject("page", formData.getPage());
	}

	@RequestMapping(value="/departments_list.iot", method=RequestMethod.GET)
	public String departments_list(ModelMap modelMap) throws Exception {
		DepartmentsList formData = new DepartmentsList();
		formData.setPage(1);
		formData.setSearchString("");

		list(formData, modelMap);

		return "/departments_list";
	}

	@RequestMapping(value="/departments_list.iot", method=RequestMethod.POST)
	public String departments_list(
			@RequestParam("command") String command, 
			DepartmentsList formData, 
			ModelMap modelMap) throws Exception 
	{

		if (command.equals("delete")) {
			List<DepartmentsDomain> list = formData.getDatas();
			for (int i=0; i<list.size(); i++) {
				DepartmentsDomain departments = list.get(i);
				if (departments.getDelete_flag()!=null && departments.getDelete_flag().equals("Y")) {
					Map<String, Object> params = new HashMap<String, Object>();
					params.put("department_id",departments.getDepartment_id());
					service.deleteDepartments(params);
				}
			}
		}

		list(formData, modelMap);

		return "/departments_list";
	}
	@RequestMapping(value="/departments_view.iot", method=RequestMethod.POST)
	public String departments_view(
			@RequestParam("department_id") int department_id, 
			ModelMap modelMap) throws Exception 
	{
		DepartmentsDomain data = service.selectDepartments(department_id);
		modelMap.addObject("data", data);
		modelMap.addObject("command", "view");
		return "/departments_add";
	}

	@RequestMapping(value="/departments_add.iot", method=RequestMethod.GET)
	public String departments_add(ModelMap modelMap) throws Exception {
		DepartmentsDomain data = new DepartmentsDomain();
		modelMap.addObject("data", data);
		modelMap.addObject("command", "add");
		return "/departments_add";
	}

	@RequestMapping(value="/departments_add.iot", method=RequestMethod.POST)
	public String departments_add_process(
			@RequestParam("command") String command, 
			DepartmentsDomain data, 
			ModelMap modelMap) throws Exception 
	{
		if (command.equals("add")) {
			service.insertDepartments(data);
		} else {
			service.updateDepartments(data);
		}
		return "redirect:/departments_list.iot";
	}

}

부서 목록 조회 JSP

departments_list.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>

<HTML>
<HEAD>
<TITLE>부서목록</TITLE>
<META http-equiv="Content-Type"  content="text/html; charset=UTF-8">
<LINK rel="stylesheet" href="<c:url value="/resources/css/iot.css"/>" type="text/css">
<SCRIPT type="text/javascript">
function listPage(goto_page) {
	var obj = document.selectForm;
	obj.page.value = goto_page;
	obj.action = '<c:url value="/departments_list.iot"/>';
	obj.method = 'post';
	obj.submit();
}

function dataAdd() {
	var obj = document.selectForm;
	obj.action = '<c:url value="/departments_add.iot"/>';
	obj.method = 'get';
	obj.submit();
}

function viewResultDetail(department_id) {
	var obj = document.detailForm;
	obj.department_id.value = department_id;
	obj.submit();
}

function deleteContract() {
	ret = confirm("정말로 삭제 하시겠습니까?");
	if (ret) {
	  var obj = document.selectForm;
	  obj.command.value = 'delete';
	  obj.submit();
	}
}

function switchCheckbox(checked, objCheckbox) {
	if (objCheckbox != null) {
		if (objCheckbox.length == null) {
			if (!objCheckbox.disabled) {
				objCheckbox.checked = checked;
			}
		} else {
			for (var i = 0; i < objCheckbox.length; i++) {
				if (!objCheckbox[i].disabled) {
					objCheckbox[i].checked = checked;
				}
			}
		}
	}
}
</SCRIPT>
</HEAD>

<body>
<!-- body style="margin:0"  -->

<form name="detailForm" action="<c:url value="/departments_view.iot"/>" method="post">
  <INPUT type="hidden" name="department_id">
</form>

<!-- div id="mailListDiv" class="division">
<div class="divList" id="divList" -->

<form:form modelAttribute="formData" name="selectForm" method="POST">
  <form:hidden path="command" value="select"/>
	<form:hidden path="pageSize"/>
	<form:hidden path="page"/>
					
  <table width="100%">
    <tr>
      <td><DIV style="font: bold 16pt">부서 목록</DIV></td>
      <td style="text-align: 'right';" align="right" valign="bottom">
        <input type="button" value="등록" onclick="javascript:dataAdd();"/> 
        <input type="button" onClick="javascript:deleteContract();" value="삭제"/>
      </td>
    </tr>
  </table>
  <br>

  <TABLE width="100%" cellspacing="1" cellpadding="3" class="table" style="table-layout: fixed;">
    <COLGROUP>
      <COL width="30px">
      <COL width="100px">
      <COL width="100px">
      <COL width="100px">
      <COL width="100px">
    </COLGROUP>
    <TR class="header_tr">
      <TD><INPUT type="checkbox" onclick="JavaScript:switchCheckbox(this.checked, document.selectForm.chk_id)"/></TD>
      <TD>부서ID</TD>
      <TD>부서명</TD>
      <TD>관리자ID</TD>
      <TD>위치ID</TD>
    </TR>
    <c:forEach items="${formData.datas}" var="data" varStatus="loop">
      <form:hidden path="datas[${loop.index}].department_id"/>
      <tr class="link_cell">
        <td align="center"><form:checkbox path="datas[${loop.index}].delete_flag" value="Y" id="chk_id"/></td>
        <td onclick="javascript:viewResultDetail(${data.department_id});">${data.getDepartment_id()}</td>
        <td onclick="javascript:viewResultDetail(${data.department_id});">${data.getDepartment_name()}</td>
        <td onclick="javascript:viewResultDetail(${data.department_id});">${data.getManager_id()}</td>
        <td onclick="javascript:viewResultDetail(${data.department_id});">${data.getLocation_id()}</td>
      </tr>
    </c:forEach>
  </TABLE>

	<div id="normalPagingNav" class="paginate">
		<div style="display:inline-block;width:100%;">
			<div class="pagination">
				${formData.pageLink}
			</div><!-- //paging01 -->
		</div>
		<table>
		  <tr>
		    <td><form:input path="searchString" size="35"/></td>
		    <td><input type="submit" value="조희" /></td>
		  </tr>
		</table>
	 </div>
</form:form>

</body>
</html>

부서 등록 및 조회, 수정 JSP

departments_add.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>

<c:url value="/departments_add.iot" var="form_url"/>

<HTML>
<HEAD>
<TITLE>부서</TITLE>
<META http-equiv="Content-Type"  content="text/html; charset=UTF-8">
<LINK rel="stylesheet" href="<c:url value="/resources/css/iot.css"/>"   type="text/css">
</HEAD>

<body>

<form:form id="dataForm" name="dataForm" modelAttribute="data" action="${form_url}" method="post">
<form:hidden path="department_id" />
<INPUT type="hidden" name="command" value="${command}">

<table width="710" border="0" cellspacing="0" cellpadding="0">
	<tr>
		<td align="center" style="padding-top:25px;">
			<table width="680" border="0" cellspacing="0" cellpadding="0">
				<tr>
					<td><DIV style="font: bold 16pt">부서등록</DIV></td>
					<td></td>
				</tr>
				<tr>
					<td height="10" colspan="2"></td>
				</tr>
				<tr>
					<td colspan="2" bgcolor="f5f5f5" style="padding:10px;">
						<table width="100%" border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
							<tr>
								<td style="padding:25px;">
									<table width="100%" border="0" cellpadding="0" cellspacing="1" bgcolor="eaeaea">
										<tr>
											<td width="25%" height="28" align="right" bgcolor="f8f8f8" class="dotum11px" style="padding:3px 5px 0 10px;">부서명</td>
											<td bgcolor="#FFFFFF" style="padding:0 0 0 10px;">
												<form:input path="department_name" class="border" size="50" maxlength="50" />
											</td>
										</tr>
										<tr>
											<td width="25%" height="28" align="right" bgcolor="f8f8f8" class="dotum11px" style="padding:3px 5px 0 10px;">관리자ID</td>
											<td bgcolor="#FFFFFF" style="padding:0 0 0 10px;">
												<form:input path="manager_id" class="border" size="50" maxlength="50" />
											</td>
										</tr>
										<tr>
											<td width="25%" height="28" align="right" bgcolor="f8f8f8" class="dotum11px" style="padding:3px 5px 0 10px;">위치ID</td>
											<td bgcolor="#FFFFFF" style="padding:0 0 0 10px;">
												<form:input path="location_id" class="border" size="50" maxlength="50" />
											</td>
										</tr>
									</table>
								</td>
							</tr>
						</table>
					</td>
				</tr>
				<tr>
					<td colspan="2"> </td>
				</tr>
				<tr>
					<td colspan="2" align="center">
						<input type="submit" value="등록" />
					</td>
				</tr>
			</table>
		</td>
	</tr>
</table>
</form:form>
</body>

</html>