개발환경
언어 : JAVA (JDK 11)
서버 : Apache Tomcat 9.0
프레임워크 : MyBatis 3.5.8
DB : OracleXE 11gR2
IDE : Eclipse 2020-12, SQL Developler
시퀀스없이 진행
DROP TABLE studentlist;
CREATE TABLE studentlist (
num number constraint stuli_num_pk primary key,
name varchar2(100),
kor number(3),
eng number(3),
math number(3)
);
INSERT INTO studentlist(num, name, kor, eng, math)
VALUES(1, '홍길동', 80, 75, 80);
INSERT INTO studentlist(num, name, kor, eng, math)
VALUES(2, '고수', 90, 90, 97);
--DELETE FROM studentlist
--WHERE num =5;
SELECT * FROM studentlist;
commit;
1-1. common.config 패키지 - configuration.xml 생성
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="org/mybatis/example/BlogMapper.xml"/>
</mappers>
</configuration>
1-2. common.config 패키지 - oracle.properties 생성
driver = oracle.jdbc.OracleDriver
url = jdbc:oracle:thin:@127.0.0.1:1521:xe
username = hr
password = a1234
1-3. 드라이버 연결 : configuration.xml 수정
<properties resource = "common/config/oracle.properties" />
SqlSession
객체 : Connection생성, SQL쿼리문 전달, 쿼리문 결과값 리턴SqlSessionFactory
: SqlSession을 만들어주는 객체 (DataSource를 참조하여 MyBatis와 DB서버를 연동)SqlSessionFactoryBuilder
: MyBatis 구성 파일을 읽어 SqlSessionFactory를 생성
2-1. common 패키지 - SqlSessionTemplate 클래스 생성
package common;
public class SqlSessionTemplate {
public SqlSessionTemplate() {
// TODO Auto-generated constructor stub
}
}//class SqlSessionTemplate
2-2. setSqlSessionFactory(), getSqlSession()
setSqlSessionFactory()
getSqlSession()
package common;
import java.io.IOException;
import java.io.Reader;
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 SqlSessionTemplate {
private static SqlSessionFactory factory = null;
public SqlSessionTemplate() {
// TODO Auto-generated constructor stub
}
public static SqlSessionFactory setSqlSessionFactory() {
//1. 트랜잭션이 설정되어 있는 파일의 경로를 정의
String resource = "common/config/configuration.xml";
//2. 설정파일(configuration.xml) 로딩을 위한 입출력 스트림과 연결
try(Reader reader = Resources.getResourceAsReader(resource)) {
//3. 설정파일값을 저장하기 위한 SqlSessionFactory를 설정해 줄 SqlSessionFactoryBuilder를 생성
SqlSessionFactoryBuilder sqlBuilder = new SqlSessionFactoryBuilder();
//4. sqlBuilder가 설정파일의 정보를 읽어와 SqlSessionFactory로 생성
factory = sqlBuilder.build(reader);
} catch(IOException e) {
e.printStackTrace();
}
return factory;
}//setSqlSessionFactory()
public static SqlSession getSqlSession() {
if(setSqlSessionFactory() == null) {
factory = setSqlSessionFactory();
}
//5. 쿼리문에 접근할 수 있도록 sqlSession객체를 리턴
return factory.openSession(false); //자동커밋 해제(false)
}//getSqlSession()
}//class SqlSessionTemplate
프로젝트 - 오른쪽버튼 - Properties - Targeted Runtimes - Tomcatv9.0 추가
dto패키지 - StuDTO클래스 생성
package dto;
public class StuDTO {
private int num;
private String name;
private int kor;
private int eng;
private int math;
public StuDTO() {
// TODO Auto-generated constructor stub
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getKor() {
return kor;
}
public void setKor(int kor) {
this.kor = kor;
}
public int getEng() {
return eng;
}
public void setEng(int eng) {
this.eng = eng;
}
public int getMath() {
return math;
}
public void setMath(int math) {
this.math = math;
}
}//class StuDTO
dao 패키지 - stuMapper.xml 생성
2-1. mybatis 태그 규칙을 정의한 DTD 선언
2-2. mapper 작성
<?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 = "stu">
<select id ="list" resultType="dto.StuDTO">
SELECT * FROM studentlist
ORDER BY num
</select>
</mapper>
<mapper resource="dao/stuMapper.xml"/>
<?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>
<properties resource = "common/config/oracle.properties" />
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="dao/stuMapper.xml"/>
</mappers>
</configuration>
dao 패키지 - StuDAO클래스 생성
selectList(query_id)
: id에 대한 select문을 실행한 후 레코드를 List로 반환package dao;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import dto.StuDTO;
public class StuDAO {
public List<StuDTO> getAllListMethod(SqlSession sqlSession) {
return sqlSession.selectList("stu.list");
}
}//class StuDAO
service 패키지 - StuService클래스 생성
getAllListProcess()
sqlSession
= (Connection) 오라클 드라이버 연결한 SqlSession객체 넣어주기 aList
= select문을 실행한 후 List로 반환된 레코드값 넣어주기package service;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import common.SqlSessionTemplate;
import dao.StuDAO;
import dto.StuDTO;
public class StuService {
private StuDAO dao;
private SqlSessionFactory factory;
public StuService() {
dao = new StuDAO();
factory = SqlSessionTemplate.setSqlSessionFactory();
}
public List<StuDTO> getAllListProcess() {
SqlSession sqlSession = null;
List<StuDTO> aList = null;
try {
sqlSession = SqlSessionTemplate.getSqlSession();
aList = dao.getAllListMethod(sqlSession);
sqlSession.commit();
} catch (Exception e) {
System.out.println(e.toString());
sqlSession.rollback();
} finally {
sqlSession.close();
}
return aList;
}//getAllListProcess()
}//class StuService
controller 패키지 - StuController클래스 생성
doGet() 오버라이드
req(요청받은 내용).setAttribute()
: aList에 select문을 실행한 후 List로 반환된 레코드값 가져오기package controller;
import java.io.IOException;
import javax.servlet.RequestDispatcher;
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 service.StuService;
@WebServlet("/stuList")
public class StuController extends HttpServlet {
private StuService service;
public StuController() {
service = new StuService();
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setAttribute("aList", service.getAllListProcess());
String path = "/views/stuList.jsp";
RequestDispatcher dis = req.getRequestDispatcher(path);
dis.forward(req, resp);
}//doGet()
}//class StuController
WebContent - views 폴더 - stuList.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>
<html>
<head>
<meta charset="UTF-8">
<title>stuList</title>
<style>
table { border: 2px solid black; border-collapse:collapse; text-align: center;}
th, td { border : 1px solid black; padding :12px;}
th {background-color: skyblue;}
a { display: block;
border: 1px solid black;
width: 100px;
padding : 0.3rem;
text-decoration: none;
text-align: center;
margin-top: 5px;
border-radius: 10px;
background-color: #FAED7D;
color: #333;}
</style>
</head>
<body>
<p><a href="stuInsert">학생 추가</a></p>
<table>
<tr>
<th>번호</th> <th>이름</th> <th>국어</th> <th>영어</th> <th>수학</th>
<th>수정</th> <th>삭제</th>
</tr>
<c:forEach items="${requestScope.aList}" var="dto">
<tr>
<td>${dto.num}</td> <td>${dto.name}</td> <td>${dto.kor}</td> <td>${dto.eng}</td> <td>${dto.math}</td>
<td> <form action ="stuUpdate"><input type="submit" value="수정" />
<input type ='hidden' name ='num' value='${dto.num}' />
<input type ='hidden' name ='name' value='${dto.name}' />
<input type ='hidden' name ='kor' value='${dto.kor}' />
<input type ='hidden' name ='eng' value='${dto.eng}' />
<input type ='hidden' name ='math' value='${dto.math}' />
</form> </td>
<td> <form action ="stuDelete">
<input type="hidden" name="num" value="${dto.num}" />
<input type="submit" value="삭제" /> </form> </td>
</tr>
</c:forEach>
</table>
</body>
</html>