본문 바로가기
Programming/JSP

JSP 5일차 - MyBatis 연동

by yoon9i 2024. 5. 24.

공통)

 

webapp > WEB-INF > lib > jstl.jar 와 standard.jar 를 추가하게 되면 EL 방식으로 연동이 가능하다

 

 

방법1)

package com.servlet;

import java.io.IOException;

import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.dao.EmpDAO;
import com.dto.EmpDTO;
import com.service.EmpService;
import com.service.EmpServiceImpl;


@WebServlet("/list")
public class EmpListServlet extends HttpServlet {

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		System.out.println("doGet");
		
		// 순서: 서비스 연동 -> 저장 -> 위임.
		// 서비스 연동
		EmpService service = new EmpServiceImpl();	
		service.setDao(new EmpDAO());
		
		List<EmpDTO> list = service.findAll();
		
		// scope 에 저장
		request.setAttribute("list", list);
		
		// jsp 에 위임
		// redirect(x) - session scope 또는 application scope 에서 사용해야함.
		// forward
		request.getRequestDispatcher("list.jsp")
			   .forward(request, response);;
	}
}
<%@page import="com.dto.EmpDTO"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<% 
	List<EmpDTO> list = (List<EmpDTO>)request.getAttribute("list");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>Emp 목록</h1>
	<table border="1">
		<tr>
			<th>사원명</th>
			<th>사원명</th>
			<th>월급</th>
			<th>입사일</th>
			<th>부서번호</th>
		</tr>
<% 
if(list != null && list.size() != 0) {
	for (EmpDTO dto: list) {
		int empno = dto.getEmpno();
		String ename = dto.getEname();
		int sal = dto.getSal();
		String hiredate = dto.getHiredate();
		int deptno = dto.getDeptno();
%>		
		<tr>
			<td><%= empno %></td>
			<td><%= ename %></td>
			<td><%= sal %></td>
			<td><%= hiredate %></td>
			<td><%= dto.getDeptno() %></td>
		</tr>
<%
	} // end for
} // end if
%>
	</table>
</body>
</html>

 

방법2) EL 사용

package com.servlet;

import java.io.IOException;

import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.dao.EmpDAO;
import com.dto.EmpDTO;
import com.service.EmpService;
import com.service.EmpServiceImpl;


@WebServlet("/list")
public class EmpListServlet extends HttpServlet {

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		System.out.println("doGet");
		
		// 순서: 서비스 연동 -> 저장 -> 위임.
		// 서비스 연동
		EmpService service = new EmpServiceImpl();	
		service.setDao(new EmpDAO());
		
		List<EmpDTO> list = service.findAll();
		
		// scope 에 저장
		request.setAttribute("list", list);
		
		// jsp 에 위임
		// redirect(x) - session scope 또는 application scope 에서 사용해야함.
		// forward
		request.getRequestDispatcher("list2.jsp")
			   .forward(request, response);;
	}
}
<%@page import="com.dto.EmpDTO"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<% 
	List<EmpDTO> list = (List<EmpDTO>)request.getAttribute("list");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>Emp 목록</h1>
	<table border="1">
		<tr>
			<th>사원명</th>
			<th>사원명</th>
			<th>월급</th>
			<th>입사일</th>
			<th>부서번호</th>
		</tr>
		<c:if test="${!empty list}">
			<c:forEach var="dto" items="${list}">
				<tr>
					<td>${dto.empno}</td>
					<td>${dto.ename}</td>
					<td>${dto.sal}</td>
					<td>${dto.hiredate}</td>
					<td>${dto.deptno}</td>
				</tr>
			</c:forEach>
		
		</c:if>

	</table>
</body>
</html>

 

 

그외)

package com.service;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.config.MySqlSessionFactory;
import com.dao.EmpDAO;
import com.dto.EmpDTO;

public class EmpServiceImpl implements EmpService {
	
	private EmpDAO dao;
	
	public void setDao(EmpDAO dao) {
		this.dao = dao;
	}

	// findAll -----------------------------------------------------
	@Override
	public List<EmpDTO> findAll() {
		SqlSession session = null;
		List<EmpDTO> list = null;

		try {
			session = MySqlSessionFactory.getSession();
			// DAO 연동코드
			list = dao.findAll(session);
		} finally {
			session.close();
		}

		return list;
	}

	// save  -----------------------------------------------------
	@Override
	public int save(EmpDTO dto) {
		SqlSession session = null;
		int n = 0;
		
		try {
			session = MySqlSessionFactory.getSession();
			// DAO 연동코드
			n = dao.save(session, dto);
			session.commit();
			//
		} finally {
			session.close();
		}

		return n;
	}
	
	// remove --------------------------------------------

	@Override
	public int removeByEmpno(int empno) {
		SqlSession session = null;
		int n = 0;
		
		try {
			session = MySqlSessionFactory.getSession();
			// DAO 연동코드
			n = dao.removeByEmpno(session, empno);
			session.commit();
			//
		} finally {
			session.close();
		}

		return n;
	}

}
package com.service;

import java.util.List;

import com.dao.EmpDAO;
import com.dto.EmpDTO;

public interface EmpService {
	
	public List<EmpDTO> findAll();
	public int save(EmpDTO dto);
	public int removeByEmpno(int empno);
	
	public void setDao(EmpDAO dao);
}
package com.dto;

public class EmpDTO {
	
	// emp 테이블의 컬럼명과 일치 권장.
	int empno;
	String ename;
	String job;
	int mgr;
	String hiredate; // 날자는 연산이 필요없으면 그냥 String 으로 처리
	int sal;
	int comm;
	int deptno;
	
	// 기본생성자필수(*)
	public EmpDTO() {
		// TODO Auto-generated constructor stub
	}

	public EmpDTO(int empno, String ename, String job, int mgr, String hiredate, int sal, int comm, int deptno) {
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.deptno = deptno;
	}

	public int getEmpno() {
		return empno;
	}

	public void setEmpno(int empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public int getMgr() {
		return mgr;
	}

	public void setMgr(int mgr) {
		this.mgr = mgr;
	}

	public String getHiredate() {
		return hiredate;
	}

	public void setHiredate(String hiredate) {
		this.hiredate = hiredate;
	}

	public int getSal() {
		return sal;
	}

	public void setSal(int sal) {
		this.sal = sal;
	}

	public int getComm() {
		return comm;
	}

	public void setComm(int comm) {
		this.comm = comm;
	}

	public int getDeptno() {
		return deptno;
	}

	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	@Override
	public String toString() {
		return "EmpDTO [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate="
				+ hiredate + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
	}
	
	
	
	
}
package com.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.dto.EmpDTO;

// DB 연동
public class EmpDAO {
	
	// find
	public List<EmpDTO> findAll(SqlSession session) {
		List<EmpDTO> list = session.selectList("com.config.EmpMapper.findAll");
		return list;
	}
	
	// save
	public int save(SqlSession session, EmpDTO dto) {
		return session.insert("com.config.EmpMapper.save", dto);
		
	}
	
	// remove
	public int removeByEmpno(SqlSession session, int empno) {
		return session.delete("com.config.EmpMapper.removeByEmpno",empno);
	}
	
	
	
}
package com.config;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MySqlSessionFactory {

	static SqlSessionFactory sqlSessionFactory;
	static {
		String resource = "com/config/Configuration.xml";
		InputStream inputStream = null;
		try {
			inputStream = Resources.getResourceAsStream(resource);
		} catch (IOException e) {
			e.printStackTrace();
		}
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	}// end static 블럭

	// SqlSessionFactory 로부터 SqlSession 얻는 메서드
	public static SqlSession getSession() {
		// MyBatis는 명시적으로 commit 지정해야 된다.
		SqlSession session = sqlSessionFactory.openSession(); // openSession(false) 동일
		return session;
	}

}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

	<!-- jdbc.properties 등록 -->
	<properties resource="com/config/jdbc.properties"></properties>
	
	<!-- DTO 별칭 -->
	<typeAliases>
		<typeAlias type="com.dto.EmpDTO" alias="EmpDTO"/>
	</typeAliases>

	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<!-- jdbc.properties 등록된 값을 참조: ${key} -->
				<property name="driver" value="${jdbc.driver}" />
				<property name="url" value="${jdbc.url}" />
				<property name="username" value="${jdbc.userid}" />
				<property name="password" value="${jdbc.passwd}" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<!-- Mapper.xml 등록 -->		
		<mapper resource="com/config/EmpMapper.xml"/>
	</mappers>
</configuration>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd">


<mapper namespace="com.config.EmpMapper">
	<!-- 전체 조회 -->
	<select id="findAll" resultType="EmpDTO">
		SELECT	 empno, 
				 ename, 
				 sal, 
				 date_format(hiredate, '%y') AS hiredate, 
				 deptno
		FROM emp
		ORDER BY empno DESC	
	</select>
	
	<!-- 사원삭제 -->
	<delete id="removeByEmpno" parameterType="int">
		DELETE FROM emp
		WHERE empno = #{empno}
	</delete>
	
	<!-- 사원저장 -->
	<insert id="save" parameterType="EmpDTO">
		INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno )
		VALUES ( #{empno}, #{ename}, 'SALESMAN', 7698, now(), #{sal}, 0, 30 )
	</insert>
	
	
</mapper>
# 주석문
# jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/테이블명
jdbc.userid=id입력
jdbc.passwd=pw입력

 

방법1) 은 EL 을 사용하지 않고 JSP 태그로 사용한 방식이고

방법2) 는 EL 을 사용한 방식인데 두가지 방식다 아래의 결과를 나타낸다.

'Programming > JSP' 카테고리의 다른 글

JSP 5일차  (0) 2024.05.24
JSP 4일차  (0) 2024.05.23
JSP 3일차  (0) 2024.05.22
JSP - 2일차  (0) 2024.05.21
JSP 1일차  (0) 2024.05.20

관련글

티스토리툴바