SQL Developer
CREATE TABLE student (
num number constraint stu_num_pk primary key,
name varchar2(100),
kor number(3),
eng number(3),
math number(3)
);
--DROP SEQUENCE stu_num_seq;
CREATE SEQUENCE stu_num_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
INSERT INTO student(num, name, kor, eng, math)
VALUES(stu_num_seq.nextval, '홍길동', 80, 75, 80);
INSERT INTO student(num, name, kor, eng, math)
VALUES(stu_num_seq.nextval, '고수', 90, 90, 97);
--DELETE FROM mem
--WHERE num =5;
SELECT * FROM student;
commit;
package servletdemo.part04;
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;
}
}
package servletdemo.part04;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcTemplate {
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
String username = "hr";
String password = "a1234";
conn = DriverManager.getConnection(url,username, password);
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}//getConnection()
public static boolean isConnection(Connection conn) throws SQLException {
boolean valid = true;
if(conn == null || conn.isClosed())
valid = false;
return valid;
}//isConnection()
public static void commit(Connection conn) {
try {
if(isConnection(conn))
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}//commit()
public static void rollback(Connection conn) {
try {
if(isConnection(conn))
conn.rollback();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}//rollback()
public static void close(ResultSet rs) {
try {
if(rs != null)
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}//close()
//Statement 또는 PreparedStatement 객체 닫기
public static void close(Statement stmt) {
try {
if(stmt != null)
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}//close() - stmt
public static void close(Connection conn) {
try {
if(isConnection(conn))
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}//close() - conn
}//JdbcTemplate
DB에 접근해서 데이터 조회, 삽입, 삭제 처리
4-1. 기본세팅
package servletdemo.part04;
import java.util.ArrayList;
import java.util.List;
public class StuDAO {
public StuDAO() {
// TODO Auto-generated constructor stub
}
private static StuDAO dao = new StuDAO();
public static StuDAO getInstance() {
return dao;
}
}//StuDAO
4-2. getListMethod() : 테이블 전체 데이터 조회
List<StuDTO> aList
객체 생성 : 데이터를 담을 List Statment객체 stmt
생성ResultSet객체 rs
생성SELECT * FROM student ORDER BY num DESC
: Student테이블의 전체(*) 값while(rs.next())
: 테이블의 한 행씩 읽어옴 / StuDTO 객체 dto생성 / dto에 읽어온 값(num, name, kor, eng, math) 넣기 / aList에 읽어온 한 행의 값을 넣기 / 반복finally
- close(rs), close(stmt)
: DB 사용 후 연결 끊기public List<StuDTO> getistMethod(Connection conn) {
List<StuDTO> aList = new ArrayList<StuDTO>();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
String sql = "SELECT * FROM student ORDER BY num DESC";
rs = stmt.executeQuery(sql);
while(rs.next()) {
StuDTO dto = new StuDTO();
dto.setNum(rs.getInt("num"));
dto.setName(rs.getString("name"));
dto.setKor(rs.getInt("kor"));
dto.setEng(rs.getInt("eng"));
dto.setMath(rs.getInt("math"));
aList.add(dto);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcTemplate.close(rs);
JdbcTemplate.close(stmt);
}
return aList;
} //getListMethod()
5. StuService 클래스 생성
트랜잭션 처리
5-1. 기본생성자
StuDAO dao = StuDAO.getInstance();
: DAO객체 dao생성package servletdemo.part04;
public class StuService {
StuDAO dao = null;
public StuService() {
StuDAO dao = StuDAO.getInstance();
}//기본생성자
}//StuService
5-2. getStuList() 메서드
테이블 전체데이터 조회처리 (Connection연결 및 종료)
Connection conn = JdbcTemplate.getConnection();
: 드라이버 연결conn.setAutoCommit(false);
: 자동커밋해제aList = dao.getistMethod(conn);
: List<StuDTO> aList
에 MemDAO에서 만든 aList 넣어주기 public List<StuDTO> getStuList() {
Connection conn = JdbcTemplate.getConnection();
List<StuDTO> aList = null;
try {
conn.setAutoCommit(false); //자동커밋해제
aList = dao.getistMethod(conn);
JdbcTemplate.commit(conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
JdbcTemplate.rollback(conn);
} finally {
JdbcTemplate.close(conn);
}
return aList;
}//getStuList()
클라이언트 요청받아서 응답처리 (view화면)
package servletdemo.part04;
import java.io.IOException;
import java.util.List;
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;
@WebServlet("/stulist")
public class ListStuController extends HttpServlet{
private StuService service;
public ListStuController() {
service = new StuService();
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setAttribute("aList", service.getStuList());
String path = "/servletview/part04/list.jsp";
RequestDispatcher dis = req.getRequestDispatcher(path);
dis.forward(req, resp);
}
}//ListStuController
[WebContent]-[part04]폴더 안에 생성
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ 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>Student List</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;}
</style>
</head>
<body>
<table>
<tr>
<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>
</tr>
</c:forEach>
</table>
</body>
</html>
8. ListStuController 클래스에서 ctrl+F11(실행)