참고자료 : spring 프로젝트 생성 / mybatis연결 / spring+mybatis연결 / Bootstrap
구현내용
- 기존의 데이터 조회, 추가, 수정, 삭제를 포함
- 조회 페이지를 2개로 나누기
-> main.jsp : 성적의 총점, 평균을 구하고 순위를 보여줌
-> admin.jsp : 데이터 추가, 수정, 삭제를 처리- 각 페이지에는 이동할 수 있는 버튼 추가 (메인->관리자 / 관리자->메인)
- 부트스트랩 이용해서 스타일 적용하기
개발환경
언어 : JAVA (JDK 11)
서버 : Apache Tomcat 9.0
프레임워크 : Spring Framework 3.9.18, MyBatis 3.5.8, Bootstrap 5.2.3
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
ORDER BY num;
commit;
service는 stu-context.xml에서 빈으로 처리
model 패키지 - StuDTO클래스 생성
package model;
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
package dao;
import java.util.List;
import model.StuDTO;
public interface StuDAO {
//전체 조회(SELECT)
public List<StuDTO> list();
//추가(INSERT)
public void insertMethod(StuDTO dto);
//수정할 데이터의 num값 가져오기
public StuDTO one(int num);
//수정(UPDATE)
public void updateMethod(StuDTO dto);
//삭제(DELETE)
public void deleteMethod(int num);
}//interface StuDAO
package dao;
import java.util.List;
import org.mybatis.spring.SqlSessionTemplate;
import model.StuDTO;
public class StuDaoImp implements StuDAO{
private SqlSessionTemplate sqlSession;
public StuDaoImp() {
// TODO Auto-generated constructor stub
}
public void setSqlSession(SqlSessionTemplate sqlSession) {
this.sqlSession = sqlSession;
}
@Override
public List<StuDTO> list() {
// TODO Auto-generated method stub
return null;
}
@Override
public StuDTO one(int num) {
// TODO Auto-generated method stub
return null;
}
@Override
public void insertMethod(StuDTO dto) {
// TODO Auto-generated method stub
}
@Override
public void updateMethod(StuDTO dto) {
// TODO Auto-generated method stub
}
@Override
public void deleteMethod(int num) {
// TODO Auto-generated method stub
}
}//class StuDaoImp
package controller;
import org.springframework.stereotype.Controller;
import dao.StuDAO;
// http://localhost:8090/myapp/admin.do
@Controller
public class StuController {
private StuDAO stuDao;
public StuController() {
// TODO Auto-generated constructor stub
}
public void setStuDao(StuDAO stuDao) {
this.stuDao = stuDao;
}
}//class StuController
- mybatis에서 사용될 SQL 구문을 담고 있는 xml
<?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">
</mapper>
<mapper resource="mybatis/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>
<mappers>
<mapper resource="mybatis/StuMapper.xml"/>
</mappers>
</configuration>
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<!-- [1] DataSource 빈 선언 -->
<bean id = "dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.OracleDriver" />
<property name = "url" value="jdbc:oracle:thin:@127.0.0.1:1521:xe"/>
<property name="username" value="hr"></property>
<property name="password" value="a1234"></property>
</bean>
<!-- [2] SqlSessionFactoryBean 빈 선언 -->
<bean id= "sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name = "dataSource" ref="dataSource" />
<property name ="mapperLocations">
<list>
<value>classpath:mybatis/StuMapper.xml</value>
</list>
</property>
</bean>
<!-- [3] SqlSessionTemplate 빈 선언 -->
<bean id= "sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg ref = "sqlSessionFactoryBean" />
</bean>
<!-- [4] MemDaoImp 빈 선언 -->
<bean id= "dao" class="dao.StuDaoImp">
<property name="sqlSession" ref="sqlSession" />
</bean>
<!-- [5] Controller 빈 선언 -->
<bean class="controller.StuController">
<property name="stuDao" ref="dao" />
</bean>
<!-- [6] ViewResolver 빈 선언 -->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/WEB-INF/views/" />
<property name="suffix" value=".jsp" />
</bean>
</beans>
<select id="list" resultType="model.StuDTO">
SELECT * FROM studentlist
ORDER BY num
</select>
@Override
public List<StuDTO> list() {
return sqlSession.selectList("stu.list");
}
addObject()
: "list"에 stuDao.list()리턴값 넣어주기List<StuDTO>
로 받아옴setViewName()
: view로 보여줄 admin.jsp와 연결 @RequestMapping(value="/admin.do")
public ModelAndView process(ModelAndView mav) {
mav.addObject("list", stuDao.list());
mav.setViewName("stu/admin");
return mav;
}
<c:forEach items="${list}" var="stu">
: mav에서 받아온 list값을 stu변수에 담아서 뿌려줌<%@ 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>admin</title>
</head>
<body>
<p><a href="insert.do">추가</a></p>
<table>
<tr>
<th>번호</th> <th>이름</th> <th>국어</th> <th>영어</th> <th>수학</th>
<th>수정</th> <th>삭제</th>
</tr>
<c:forEach items="${list}" var="stu">
<tr>
<td>${stu.num}</td> <td>${stu.name}</td>
<td>${stu.kor}</td> <td>${stu.eng}</td> <td>${stu.math}</td>
<td><a href="update.do?num=${stu.num}">수정</a></td>
<td><a href="delete.do?num=${stu.num}">삭제</a></td>
</tr>
</c:forEach>
</table>
</body>
</html>
<%@ 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>admin</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-rbsA2VBKQhggwzxH7pPCaAqO46MgnOM80zW1RWuH61DGLwZJEdK2Kadq2F9CUG65" crossorigin="anonymous">
<style>
#ins {display: inline-block; width : 100px; text-align:center; margin-bottom:10px; margin-right:10px; float :right;}
table {clear : both;}
tr, td {text-align:center;}
#main {margin-left:10px;}
</style>
</head>
<body>
<p><a id="ins" class="list-group-item list-group-item-success" href="insert.do">학생 추가</a></p>
<table class="table">
<tr class="table-light">
<th>번호</th> <th>이름</th> <th>국어</th> <th>영어</th> <th>수학</th>
<th></th> <th></th>
</tr>
<c:forEach items="${list}" var="stu">
<tr>
<td>${stu.num}</td> <td>${stu.name}</td>
<td>${stu.kor}</td> <td>${stu.eng}</td> <td>${stu.math}</td>
<td><a class="list-group-item list-group-item-primary" href="update.do?num=${stu.num}">수정</a></td>
<td><a class="list-group-item list-group-item-warning" href="delete.do?num=${stu.num}">삭제</a></td>
</tr>
</c:forEach>
</table>
<p><a id="main" class="btn btn-primary" href="">< Main</a></p>
</body>
</html>