BASHA TECH
ch09 본문
728x90
ORACLE DB
CREATE TABLE student(
stuid NUMBER PRIMARY KEY
, username VARCHAR2(20)
, univ VARCHAR2(40)
, birth VARCHAR2(6)
, email VARCHAR2(40)
);
INSERT INTO student
VALUES(
(SELECT NVL(MAX(stuid)+1 , 1) FROM student)
, '김길동'
, 'AA대학교'
, '991021'
, 'kim@aa.com'
);
SELECT * FROM student;
--SELECT NVL(MAX(stuid)+1 , 1) FROM student;
COMMIT;
C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib
StudentDto 생성 후 => StudentDao 생성 => DBTest
개발 순서 기억해~
package 생성
kr.co.big15 => package 재생성 student
package 재재생성 controller, dto, model, service
- dto에 StudentDto class 생성
- model에 StudenDao class 생성 / DbTest class 생성 (DbTest엔 실행 돌려봐야하니까 main class 첨부)
- controller에 StudentController 서블릿 생성 (요청 받아야하니까)
java단> web단>
jwbook/kr.co.big15/student/dto/StudentDto.java (jwbook/src/main/java/kr.co.big15/student/dto/StudentDto.java ) => 클래스
package kr.co.big15.student.dto;
public class StudentDto { //컬럼명과 동일한 변수 명 쓰기
private int stuid;
private String username;
private String univ;
private String birth;
private String email;
public StudentDto() {
}
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getUniv() {
return univ;
}
public void setUniv(String univ) {
this.univ = univ;
}
public String getBirth() {
return birth;
}
public void setBirth(String birth) {
this.birth = birth;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "StudentDto [stuid=" + stuid + ", username=" + username + ", univ=" + univ + ", birth=" + birth
+ ", email=" + email + "]";
}
}
jwbook/kr.co.big15/student/model/DbTest.java => 클래스
package kr.co.big15.student.model;
import kr.co.big15.student.dto.StudentDto;
public class DbTest {
public static void main(String[] args) {
StudentDao studentDao = new StudentDao();
StudentDto studentDto = new StudentDto();
studentDto.setUsername("고길동");
studentDto.setUniv("BB대학교");
studentDto.setBirth("880101");
studentDto.setEmail("go@aa.com");
// studentDao.insert(studentDto);
studentDao.selectAll();
}
}jwbook/kr.co.big15/student/model/StudentDao.java => 클래스
package kr.co.big15.student.model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import kr.co.big15.student.dto.StudentDto;
public class StudentDao { //Dao단이 가장 어렵다. Dao단이 끝나면 개발의 50프로는 끝이 난 것.
public static Connection getConnection() {
Connection conn = null;
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String userid = "ora_user";
String userpassword = "hong";
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, userid, userpassword);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn, PreparedStatement pstmt) {
try {
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
// 학생 등록
public void insert(StudentDto studentDto) {
Connection conn = getConnection();
PreparedStatement pstmt = null;
String sql = "INSERT INTO student "
+ "VALUES( "
+ " (SELECT NVL(MAX(stuid)+1 , 1) FROM student)"
+ " , ?, ?, ?, ?) ";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, studentDto.getUsername());
pstmt.setString(2, studentDto.getUniv());
pstmt.setString(3, studentDto.getBirth());
pstmt.setString(4, studentDto.getEmail());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(conn, pstmt);
}
}
// 학생 목록
public ArrayList<StudentDto> selectAll(){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select * from student";
ArrayList<StudentDto> students = new ArrayList<>();
conn = getConnection();
try {
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
StudentDto s = new StudentDto();
s.setStuid(rs.getInt("stuid"));
s.setUsername(rs.getString("username"));
s.setUniv(rs.getString("univ"));
s.setEmail(rs.getString("email"));
s.setBirth(rs.getString("birth"));
System.out.println("==================================");
System.out.println(s);
System.out.println("==================================");
students.add(s);
}
}catch (SQLException e) {
e.printStackTrace();
}
return students;
}
// 쓰면 안됨
// String sql = "insert into student values("
// + "(SELECT NVL(MAX(stuid)+1 , 1) FROM student)"
// + ", " + studentDto.getUsername()
// + ", " + studentDto.getUniv()
// + ", " + studentDto.getBirth()
// + ", " + studentDto.getEmail()
// + ")";
}jwbook/kr.co.big15/student/controller/StudentController.java => 얘는 서블릿임
package kr.co.big15.student.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 kr.co.big15.student.model.StudentDao;
@WebServlet("/student")
public class StudentController extends HttpServlet {
private static final long serialVersionUID = 1L;
public StudentController() {
}
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String action = req.getParameter("action");
String view = "";
if(action == null) {
RequestDispatcher disp = req.getRequestDispatcher("/student?action=list");
disp.forward(req, resp);
} else {
switch(action) {
case "list" : view = list(req, resp); break;
case "insert": view = insert(req, resp); break;
}
RequestDispatcher disp = req.getRequestDispatcher("/student/" + view);
disp.forward(req, resp);
}
}
private String insert(HttpServletRequest req, HttpServletResponse resp) {
// 학생 추가 루틴
return "이동할 화면";
}
private String list(HttpServletRequest req, HttpServletResponse resp) {
// 목록 => req 저장
StudentDao dao = new StudentDao();
req.setAttribute("students", dao.selectAll());
return "studentList.jsp";
}
}web단>
jwbook/src/main/wepapp/student/studentList.jsp => 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>학생 목록</title>
</head>
<body>
<h2>학생 목록</h2>
<hr>
<table border="1">
<tr>
<th>순번</th>
<th>이름</th>
<th>대학</th>
<th>생일</th>
<th>이메일</th>
</tr>
<c:forEach items="${students}" var="student">
<tr>
<td>${student.stuid}</td>
<td>${student.username}</td>
<td>${student.univ}</td>
<td>${student.birth}</td>
<td>${student.email}</td>
</tr>
</c:forEach>
</table>
</body>
</html>
728x90
반응형
'Computer/JSP' Related Articles
more
Comments