MyBatis

Programming/Java 2016.02.20 02:01 Posted by 파란크리스마스

출처 : 

환경설정

MyBatis 관련 환경파일 import

iot-servlet.xml 파일에 아래 내용 추가

	<!-- ========================= Import Definitions ========================= -->
	<import resource="iot-datasource.xml" />
	<import resource="iot-mybatis.xml" />

JDBC 설정

iot-datasource.xml 파일

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:jee="http://www.springframework.org/schema/jee"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:security="http://www.springframework.org/schema/security"
       xsi:schemaLocation="http://www.springframework.org/schema/aop       http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
                           http://www.springframework.org/schema/beans     http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
                           http://www.springframework.org/schema/context   http://www.springframework.org/schema/context/spring-context-2.5.xsd
                           http://www.springframework.org/schema/jee       http://www.springframework.org/schema/jee/spring-jee-2.5.xsd
                           http://www.springframework.org/schema/tx        http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
                           http://www.springframework.org/schema/security  http://www.springframework.org/schema/security/spring-security-2.0.xsd">

	<!-- ========================= Resource Definitions ========================= --> 
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
		<property name="driverClassName" value="org.gjt.mm.mysql.Driver"/>
		<property name="url" value="jdbc:mysql://localhost:5614/iot_db?user=HR&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull"/>
		<property name="username" value="HR"/>
		<property name="password" value="111111"/>
		<property name="maxActive" value="20"/>
		<property name="maxIdle" value="5"/>
		<property name="maxWait" value="2000"/>
		<!-- validationQuery:유효 검사용 쿼리( 1개 이상의 row를 반환하는 쿼리를 넣어주면 된다. ) -->
		<property name="validationQuery" value="select 1"/>
		<!-- testWhileIdle:컨넥션이 놀고 있을때 -_-; validationQuery 를 이용해서 유효성 검사를 할지 여부. -->
		<property name="testWhileIdle" value="true"/>
		<!-- timeBetweenEvictionRunsMillis:해당 밀리초마다 validationQuery 를 이용하여 유효성 검사 진행 -->
		<property name="timeBetweenEvictionRunsMillis" value="7200000"/>        
	</bean>
	
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource"/>
	</bean>	

	<tx:annotation-driven transaction-manager="transactionManager" />

	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="transactionFactory">
			<bean class="org.apache.ibatis.transaction.managed.ManagedTransactionFactory" />
		</property>
	</bean>

	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg index="0" ref="sqlSessionFactory" />
	</bean>
	
</beans>

MyBatis 설정

iot-mybatis.xml 파일

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xmlns:aop="http://www.springframework.org/schema/aop"
     xmlns:tx="http://www.springframework.org/schema/tx"
     xmlns:jdbc="http://www.springframework.org/schema/jdbc"
     xmlns:context="http://www.springframework.org/schema/context"
     xsi:schemaLocation="
     http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
     http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
     http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
     http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
     http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">

    <!-- enable component scanning (beware that this does not enable mapper scanning!) -->    
    <context:component-scan base-package="com.iot.service" />
 		
    <!-- enable autowire -->
    <context:annotation-config />

    <!-- enable transaction demarcation with annotations -->
    <tx:annotation-driven />

    <!-- define the SqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="typeAliasesPackage" value="com.iot.domain" />
    </bean>

    <!-- scan for mappers and let them be autowired -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.iot.persistence" />
    </bean>
</beans>

Domain 객체

출처 : 혹시 DTO(VO) 작성하시나요? - SLiPP

프레임워크마다 VO, Command, Domain, DTO, TO 여러가지 이름으로 부르지만, MyBatis에서는 Domain이라고 부르고, Domain객체는 데이터베이스에서 조회된 데이터를 객체에 담아서 jsp에 실어 사용자에게 전달합니다.

package com.iot.domain;

import java.sql.Timestamp;

public class TbluserDomain {

	// pk
	private int seq;

	private String user_id;
	private String pass;
	private String nick;
	private Timestamp joindate;
	private String email;
	private String sex;

	private String delete_flag = "N";

	public void setSeq(int seq) {
		this.seq = seq;
	}

	public int getSeq() {
		return this.seq;
	}

	public void setUser_id(String user_id) {
		this.user_id = user_id;
	}

	public String getUser_id() {
		return this.user_id;
	}

	public void setPass(String pass) {
		this.pass = pass;
	}

	public String getPass() {
		return this.pass;
	}

	public void setNick(String nick) {
		this.nick = nick;
	}

	public String getNick() {
		return this.nick;
	}

	public void setJoindate(Timestamp joindate) {
		this.joindate = joindate;
	}

	public Timestamp getJoindate() {
		return this.joindate;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getEmail() {
		return this.email;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getSex() {
		return this.sex;
	}

	public String getDelete_flag() {
		return delete_flag;
	}

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

Mapper 객체

출처 : mybatis-spring ? 마이바티스 스프링 연동모듈 | 매퍼 주입
MyBatis - MyBatis 3 | Mapper XML 파일

  • cache - 해당 명명공간을 위한 캐시 설정
  • cache-ref - 다른 명명공간의 캐시 설정에 대한 참조
  • resultMap - 데이터베이스 결과데이터를 객체에 로드하는 방법을 정의하는 요소
  • sql - 다른 구문에서 재사용하기 위한 SQL 조각
  • insert - 매핑된 INSERT 구문.
  • update - 매핑된 UPDATE 구문.
  • delete - 매핑된 DELEETE 구문.
  • select - 매핑된 SELECT 구문.

Mapper 인터페이스

package com.iot.persistence;

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

import com.iot.domain.TbluserDomain;

public interface TbluserMapper {

	public TbluserDomain selectTbluser(Map<String, Object> params);

	public void insertTbluser(TbluserDomain tbluser);

	public void updateTbluser(TbluserDomain tbluser);

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

	public int getCount();

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

	public List<TbluserDomain> listTbluser(Map<String, Object> map);

}

Mapper SQL Map 파일

  • cache - 해당 명명공간을 위한 캐시 설정
  • cache-ref - 다른 명명공간의 캐시 설정에 대한 참조
  • resultMap - 데이터베이스 결과데이터를 객체에 로드하는 방법을 정의하는 요소
  • sql - 다른 구문에서 재사용하기 위한 SQL 조각
  • insert - 매핑된 INSERT 구문.
  • update - 매핑된 UPDATE 구문.
  • delete - 매핑된 DELEETE 구문.
  • select - 매핑된 SELECT 구문.
<?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.TbluserMapper">

  <!-- selectTbluser -->
  <select id="selectTbluser" parameterType="map" resultType="com.iot.domain.TbluserDomain">
    select seq
           ,user_id
           ,pass
           ,nick
           ,joindate
           ,email
           ,sex
      from tbluser
     where seq = #{seq}
  </select>

  <!-- updateTbluser -->
  <update id="updateTbluser" parameterType="com.iot.domain.TbluserDomain" statementType="PREPARED">
      update tbluser
        <trim prefix="SET" suffixOverrides=",">
          <if test="user_id != null">user_id = #{user_id, jdbcType=VARCHAR} ,</if>
          <if test="pass != null">pass = #{pass, jdbcType=VARCHAR} ,</if>
          <if test="nick != null">nick = #{nick, jdbcType=VARCHAR} ,</if>
          <if test="joindate != null">joindate = #{joindate, jdbcType=TIMESTAMP} ,</if>
          <if test="email != null">email = #{email, jdbcType=VARCHAR} ,</if>
          <if test="sex != null">sex = #{sex, jdbcType=VARCHAR} ,</if>
        </trim>
     where seq = #{seq}
  </update>

  <!-- insertTbluser -->
  <insert id="insertTbluser" parameterType="com.iot.domain.TbluserDomain" statementType="PREPARED">
      insert into tbluser(
        <trim suffixOverrides=",">
          <if test="seq != null">seq ,</if>
          <if test="user_id != null">user_id ,</if>
          <if test="pass != null">pass ,</if>
          <if test="nick != null">nick ,</if>
          <if test="joindate != null">joindate ,</if>
          <if test="email != null">email ,</if>
          <if test="sex != null">sex ,</if>
        </trim>
        ) values	(
        <trim suffixOverrides=",">
          <if test="seq != null">#{seq, jdbcType=decimal} ,</if>
          <if test="user_id != null">#{user_id, jdbcType=VARCHAR} ,</if>
          <if test="pass != null">#{pass, jdbcType=VARCHAR} ,</if>
          <if test="nick != null">#{nick, jdbcType=VARCHAR} ,</if>
          <if test="joindate != null">#{joindate, jdbcType=TIMESTAMP} ,</if>
          <if test="email != null">#{email, jdbcType=VARCHAR} ,</if>
          <if test="sex != null">#{sex, jdbcType=VARCHAR} ,</if>
        </trim>
        )
  </insert>

  <!-- deleteTbluser -->
  <delete id="deleteTbluser" parameterType="map" statementType="PREPARED">
      delete from tbluser
     where seq = #{seq}
  </delete>

  <!-- getCount -->
  <select id="getCount" resultType="int">
    select count(*)
      from tbluser
  </select>

  <!-- getCountFormData -->
  <select id="getCountFormData" parameterType="map" resultType="int">
    select count(*)
      from tbluser
      <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="searchstr != null">and user_id like '%${searchstr}%'</if>
      </trim>
  </select>

  <!-- listTbluser -->
  <select id="listTbluser" parameterType="map" resultType="com.iot.domain.TbluserDomain">
    select * 
      from tbluser
      <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="searchstr != null">and user_id like '%${searchstr}%'</if>
      </trim>
     limit #{defaultSize} offset #{offset}
  </select>

</mapper>

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.TbluserDomain;
import com.iot.persistence.TbluserMapper;

@Service
public class TbluserService {

  public final static int pagerowcnt = 25;

  @Autowired
  private TbluserMapper tbluserMapper;

  public TbluserDomain selectTbluser(double seq) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("seq",seq);
    return tbluserMapper.selectTbluser(params);
  }

  public void insertTbluser(TbluserDomain tbluser) {
    tbluserMapper.insertTbluser(tbluser);
  }

  public void updateTbluser(TbluserDomain tbluser) {
    tbluserMapper.updateTbluser(tbluser);
  }

  public void deleteTbluser(double seq) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("seq",seq);
    tbluserMapper.deleteTbluser(params);
  }

  public void deleteTbluser(Map<String, Object> params) {
    tbluserMapper.deleteTbluser(params);
  }

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

  public List<TbluserDomain> listTbluser(int page) throws Exception {
  	Map<String, Object> params = new HashMap<String, Object>();
    params.put("defaultSize", pagerowcnt);
    params.put("offset", pagerowcnt * (page-1));
    return tbluserMapper.listTbluser(params);
  }

}

UserController 클래스

package com.iot.ui.controller;

import java.util.List;

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.TbluserDomain;
import com.iot.service.TbluserService;

@Controller
public class UserController {
	
	@Autowired
	private TbluserService tbluserService;
	
	@RequestMapping(value="/userlist.iot", method = RequestMethod.GET)
	public String userlist(ModelMap modelMap) throws Exception 
	{
		List<TbluserDomain> ausers = tbluserService.listTbluser(1);
		modelMap.addAttribute("ausers", ausers);
		return "/UserList";
	}

	@RequestMapping(value="/userinfo.iot", method = RequestMethod.GET)
	public String userinfo(
			@RequestParam("seq") int seq,
			ModelMap modelMap) throws Exception 
	{
		TbluserDomain cu = tbluserService.selectTbluser(seq);
		modelMap.addAttribute("cu", cu);
		return "/UserInfo";
	}
	
}

사용자 목록 조회 JSP

UserList.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<h3>현재 접속자 목록</h3>
<c:forEach items="${ausers}" var="u">
 <li><a href="<c:url value="/userinfo.iot"/>?seq=${u.seq}">${u.nick}</a></li>

</c:forEach>

</body>
</html>

사용자 조회 JSP

UserInfo.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>

	<table border="1" cellspacing="0" cellpadding="0" align="center" >
    <tr>
    <td>닉네임</td><td>이메일</td>
     </tr>
 

		<tr>
			<td>${cu.nick}</td>
			<td>${cu.email}</td>
			<td></td>
		</tr>

	</table>

</body>
</html>
저작자 표시
신고


 

티스토리 툴바