[ 작업 순서 ]
1. lib : mybatis-3.2.3.jar
2. db.properties 파일 생성 → DB 속성
3. configuration.xml 생성 → db.properties 파일을 가지고 DB 환경설정
4. studentMapper.xml → 쿼리문을 id로 분류해서 모아둔 곳
5. studentMapper.java → 쿼리문이 실행될 수 있게 메서드 형태로 모아둔 곳
6. StudentController.java (또는 Servlet) → 주소값에 맞춰 실행될 메서드를 정의
1. 학생관리
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 아래 environments에서 사용할 DB정보가 여기에 저장되어있음(New>General>File) -->
<properties resource="student/mybatis/db.properties"/>
<!-- DTO 별칭 지정 -->
<typeAliases>
<typeAlias type="student.dto.StudentDTO" alias="studentDTO"/>
</typeAliases>
<!-- 공통부분 -->
<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>
<!-- 실제 쿼리문이 위치하는 곳 : mapper -->
<mappers>
<mapper resource="student/mybatis/studentMapper.xml"/>
</mappers>
</configuration>
<?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="student.mybatis.studentMapper">
<select id="listStudent" resultType="studentDTO">
<!-- configuration.xml에서 alias 줬기 때문에 student.dto.StudentDTO 안써도됨 -->
select * from student
</select>
<select id="findStudent" parameterType="String" resultType="studentDTO">
select * from student where cname = #{cname}
</select>
<insert id="insertStudent" parameterType="studentDTO">
<!-- int형의 resultType은 별도로 지정안해줘도됨. 알아서 1, 0으로 반환함 -->
insert into student values(#{id}, #{name}, #{cname})
</insert>
<delete id="deleteStudent" parameterType="String">
delete from student where id = #{id}
</delete>
</mapper>
package student.mybatis;
import java.io.IOException;
import java.io.Reader;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import student.dto.StudentDTO;
public class StudentMapper {
private static SqlSessionFactory sqlSessionFactory;
// SqlSessionFactory 객체 만들기
static {
try {
String resource = "configuration.xml"; // src폴더 안에 있음
Reader reader = Resources.getResourceAsReader(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
}catch(IOException e) {
throw new RuntimeException("configuration 인스턴스 생성 중 오류 발생!" + e.getMessage(), e);
}
}
// opensession 메소드를 통해 쿼리문 실행
public static List<StudentDTO> listStudent(){
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
List<StudentDTO> list = sqlSession.selectList("listStudent");
return list;
}finally {
sqlSession.close();
}
}
public static int insertStudent(StudentDTO dto) {
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
int res = sqlSession.insert("insertStudent", dto);
sqlSession.commit(); // insert, delete, update는 DB에 영향 주므로 commit 필수
return res;
} finally {
sqlSession.close();
}
}
public static int deleteStudent(String id) {
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
int res = sqlSession.delete("deleteStudent", id);
sqlSession.commit();
return res;
} finally {
sqlSession.close();
}
}
public static List<StudentDTO> findStudent(String cname){
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
List<StudentDTO> find = sqlSession.selectList("findStudent", cname);
//하나만 반환하는 거면 sqlSession.selectOne()
return find;
} finally {
sqlSession.close();
}
}
}
package student;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import student.dao.StudentDAO;
import student.dto.StudentDTO;
import student.mybatis.StudentMapper;
@Controller // annotation
public class StudentServlet {
// StudentDAO 주입
// @Autowired // springStudent-servlet.xml에 등록되어있는 여러 빈 중에 자동으로 가져와짐
// private StudentDAO studentDAO;
// 메서드
@RequestMapping("/student.do")
public String index() {
return "student";
}
@RequestMapping("/insert_student.do")
public String insertStudent(StudentDTO dto) { // @ModelAttribute가 생략되어있어서 알아서 값을 세팅해줌
//int res = studentDAO.insertStudent(dto);
int res = StudentMapper.insertStudent(dto);
return "redirect:list_student.do";
}
@RequestMapping("/list_student.do")
public String listStudent(HttpServletRequest req) { // 매개변수에 req 써주면
//List<StudentDTO> list = studentDAO.listStudent();
List<StudentDTO> list = StudentMapper.listStudent();
req.setAttribute("listStudent", list);
return "list";
}
@RequestMapping("/delete_student.do")
public String deleteStudent(@RequestParam String id) { // @Requestparam 생략되어있음. Reqest중에 id라는 parameter가 있는데 넣어줘
//int res = studentDAO.deleteStudent(id);
int res = StudentMapper.deleteStudent(id);
return "redirect:list_student.do";
}
@RequestMapping("/find_student.do")
public String findStudent(HttpServletRequest req, String cname) {
//List<StudentDTO> list = studentDAO.findStudent(cname);
List<StudentDTO> list = StudentMapper.findStudent(cname);
req.setAttribute("listStudent", list);
return "list";
}
}
2. 게시판 관리
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 아래 environments에서 사용할 DB정보가 여기에 저장되어있음(New>General>File) -->
<properties resource="board/mybatis/db.properties"/>
<!-- DTO 별칭 지정 -->
<typeAliases>
<typeAlias type="board.dto.BoardDTO" alias="boardDTO"/>
</typeAliases>
<!-- 공통부분 -->
<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>
<!-- 실제 쿼리문이 위치하는 곳 : mapper -->
<mappers>
<mapper resource="board/mybatis/boardMapper.xml"/>
</mappers>
</configuration>
<?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="board.mybatis.boardMapper">
<select id="listBoard" resultType="boardDTO"><!-- configuration.xml에서 alias 줬기 때문에 student.dto.StudentDTO 안써도됨 -->
select * from board1 order by num desc
</select>
<insert id="insertBoard" parameterType="boardDTO">
<!-- int형의 resultType은 별도로 지정안해줘도됨. 알아서 1, 0으로 반환함 -->
insert into board1 values(board1_seq.nextval, #{writer}, #{email}, #{subject}, #{passwd}, sysdate, 0, #{content},#{ip})
</insert>
<select id="getBoard" parameterType="int" resultType="boardDTO">
select * from board1 where num = #{num}
</select>
<select id="plusReadcount" parameterType="int">
update board1 set readcount = readcount + 1 where num = #{num}
</select>
<delete id="deleteBoard" parameterType="int">
delete from board1 where num = #{num}
</delete>
<update id="updateBoard" parameterType="boardDTO">
update board1 set subject=#{subject}, email=#{email}, content=#{content} where num=#{num}
</update>
</mapper>
package board.mybatis;
import java.io.IOException;
import java.io.Reader;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import board.dto.BoardDTO;
public class BoardMapper {
private static SqlSessionFactory sqlSessionFactory;
// SqlSessionFactory 객체 만들기
static {
try {
String resource = "configuration.xml"; // src폴더 안에 있음
Reader reader = Resources.getResourceAsReader(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
}catch(IOException e) {
throw new RuntimeException("configuration 인스턴스 생성 중 오류 발생!" + e.getMessage(), e);
}
}
// opensession 메소드를 통해 쿼리문 실행
public static List<BoardDTO> listBoard(){
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
List<BoardDTO> list = sqlSession.selectList("listBoard");
return list;
}finally{
sqlSession.close();
}
}
public static int insertBoard(BoardDTO dto) {
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
int res = sqlSession.insert("insertBoard", dto);
sqlSession.commit(); // insert, delete, update는 DB에 영향 주므로 commit 필수
return res;
} finally {
sqlSession.close();
}
}
public static BoardDTO getBoard(int num){
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
BoardDTO dto = sqlSession.selectOne("getBoard", num);
return dto;
}finally {
sqlSession.close();
}
}
public static int plusReadcount(int num) {
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
int res = sqlSession.update("plusReadcount", num);
sqlSession.commit();
return res;
}finally {
sqlSession.close();
}
}
public static int deleteBoard(int num) {
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
int res = sqlSession.delete("deleteBoard", num);
sqlSession.commit();
return res;
}finally {
sqlSession.close();
}
}
public static int updateBoard(BoardDTO dto) {
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
int res = sqlSession.update("updateBoard", dto);
sqlSession.commit(); // insert, delete, update는 DB에 영향 주므로 commit 필수
return res;
} finally {
sqlSession.close();
}
}
}
package board;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.validation.BindException;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import board.dao.BoardDAO;
import board.dto.BoardDTO;
import board.mybatis.BoardMapper;
@Controller // annotation
public class BoardController {
// DB : BoardDAO 주입
// @Autowired // springBoard-servlet.xml에 등록되어있는 여러 빈 중에 자동으로 가져와짐
// private BoardDAO boardDAO;
// 메서드
@RequestMapping("/list_board.do")
public String listBoard(HttpServletRequest req) {
//List<BoardDTO> list = boardDAO.listBoard();
List<BoardDTO> list = BoardMapper.listBoard();
req.setAttribute("listBoard", list);
return "list";
}
@RequestMapping(value="/write_board.do", method=RequestMethod.GET)
public String wirteFormBoard() {
return "writeForm";
}
@RequestMapping(value="/write_board.do", method=RequestMethod.POST)
public String wirteProBoard(HttpServletRequest req, BoardDTO dto) {
dto.setIp(req.getRemoteAddr()); // IP주소 세팅
//int res = boardDAO.insertBoard(dto);
int res = BoardMapper.insertBoard(dto);
return "redirect:list_board.do";
}
@RequestMapping("/content.do")
public String contentBoard(HttpServletRequest req, int num) {
//BoardDTO dto = boardDAO.getBoard(num, "content");
int res = BoardMapper.plusReadcount(num);
BoardDTO dto = BoardMapper.getBoard(num);
req.setAttribute("getBoard", dto);
return "content";
}
@RequestMapping(value="/update.do", method=RequestMethod.GET)
public String updateFormBoard(HttpServletRequest req, int num) {
//BoardDTO dto = boardDAO.getBoard(num, "update");
BoardDTO dto = BoardMapper.getBoard(num);
req.setAttribute("getBoard", dto);
return "updateForm";
}
@RequestMapping(value="/update.do", method=RequestMethod.POST)
public String updateProBoard(@ModelAttribute BoardDTO dto, BindingResult result) {
// 예측가능한(null 세팅) 에러가 날 것 같으면 미리 처리해둘 수 있음.
// 에러가 나면 BindingResult에 담아두겠다.
if(result.hasErrors()) { // dto객체를 만드는 중 에러가 난다면
dto.setNum(0); // 0으로 세팅한다. 내가 알아서 해결하는 코드
}
//int res = boardDAO.updateBoard(dto);
int res = BoardMapper.updateBoard(dto);
return "redirect:list_board.do";
}
@RequestMapping(value="/delete.do", method=RequestMethod.GET)
public String deleteFormBoard() {
return "deleteForm";
}
@RequestMapping(value="/delete.do", method=RequestMethod.POST)
public String deleteProBoard(@RequestParam Map<String, String> params) {
// @RequestParam : 파라미터명이 Key, 파라미터값이 Value로 저장.
// 인자가 많을 경우 Map타입으로 작성해볼 수 있음
// params에는 num : num값, passwd : passwd값이 들어가있음
//int res = boardDAO.deleteBoard(Integer.parseInt(params.get("num")), params.get("passwd")); // .get(Key) = value값 꺼내짐
BoardDTO dto = BoardMapper.getBoard(Integer.parseInt(params.get("num")));
if (dto.getPasswd().equals(params.get("passwd"))) {
int res = BoardMapper.deleteBoard(Integer.parseInt(params.get("num")));
}
return "redirect:list_board.do";
}
}