728x90
출처 :
환경설정
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>728x90
