출처 : 개발하는 씽매려기 :: 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>
