JDBC 연습 ( SELECT + JSP )

 

 

 

 

1 .  연습용 테이블 작성.  

-- 연습용 테이블 SQL 문 필요하면 연락주세요 .

-- 계정생성
CREATE USER workbook IDENTIFIED BY 1234;

-- 접속, 기본객체생성 권한 부여
GRANT CONNECT, RESOURCE TO workbook;

-- 객체 생성될수있는 공간 할당량 지정
ALTER USER workbook DEFAULT TABLESPACE SYSTEM QUOTA UNLIMITED ON SYSTEM;

계정생성.

 

 

 

 

 

-- controller -> service -> dao -> db .

 

index.html 파일       (   href= " student/selectAll"  지정 )

		<li>
			<!-- 현재 페이지주소 : /JSPProject2/index.html
				 목표 페이지주소 : /JSPProject2/student/selectAll
			 -->
			<a href="student/selectAll"> 학생 전체 조회</a>
		</li>

 

 

student-sql .xml  파일     (  SELECT 쿼리문 저장. )

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
	<entry key="selectAll">
	SELECT STUDENT_NO,STUDENT_NAME,STUDENT_ADDRESS,DEPARTMENT_NAME
	FROM TB_STUDENT
	JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
	</entry>
</properties>

 

 

SelectAllServlet .class     (  (  href= " student/selectAll"  )받아온 서블릿 )

@WebServlet("/student/selectAll")
public class SelectAllServlet extends HttpServlet{
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		StudentService service = new StudentService();  // 서비스객체 생성

		try {
			//학생 전체 조회 서비스 호출 후 결과반환 받기
			List<Student> stdList= service.selectAll();
			
			// 조회결과 requsetScope 에 세팅 
			req.setAttribute("stdList", stdList);
			
			// .jsp 로 요청위임
			RequestDispatcher dispatcher = req.getRequestDispatcher("/WEB-INF/views/student/selectAll.jsp");
			dispatcher.forward(req, resp);
			
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

 

 

Student  클래스 .      기본 생성자 클래스 . 

public class Student {
	private String studentNo;  //학번
	private String studentName; //이름
	private String studentAddress; //주소
	private String departmentName; //학과명
	
	public Student() {	}
	
	public Student(String studentNo, String studentName, String studentAddress, String departmentName) {
		this.studentNo = studentNo;
		this.studentName = studentName;
		this.studentAddress = studentAddress;
		this.departmentName = departmentName;
	}

	public String getStudentNo() {
		return studentNo;
	}
	public void setStudentNo(String studentNo) {
		this.studentNo = studentNo;
	}
	public String getStudentName() {
		return studentName;
	}
	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}
	public String getStudentAddress() {
		return studentAddress;
	}
	public void setStudentAddress(String studentAddress) {
		this.studentAddress = studentAddress;
	}
	public String getDepartmentName() {
		return departmentName;
	}
	public void setDepartmentName(String departmentName) {
		this.departmentName = departmentName;
	}
	@Override
	public String toString() {
		return "Student [studentNo=" + studentNo + ", studentName=" + studentName + ", studentAddress=" + studentAddress
				+ ", departmentName=" + departmentName + "]";
	}
}

 

 

driver.xml 파일    ( Connection 정보들 저장. ) 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
	<comment>driver.xml file</comment>		
	<!-- entry : K:V 한 쌍 -->
	<entry key="driver">oracle.jdbc.driver.OracleDriver</entry>	
	<entry key="url">jdbc:oracle:thin:@localhost:1521:xe</entry>	
	<entry key="user">workbook</entry>
	<entry key="password">1234</entry>
</properties>

 

 

JDBCTemplate.class       (  DB 연결, 트랜잭션 제어 클래스 )

public class JDBCTemplate {

	// DB 연결 
	//( Connection 생성) , 자동커밋 off , 트랜잭션제어 , JDBC 객체 자동반환
	// 반복사용되는 코드를 모아둔 클래스
	//모든 필드, 메서드가 static ,  클래스명.메서드명 호출할수있도록.
	
	private static Connection conn = null;
	
	/** DB 연결정보를 담고있는 Connection 생성 및 반환메서드
	 * @return conn
	 */
	public static Connection getConnection() {	
		try {
			if(conn == null || conn.isClosed() ) {
				Properties prop = new Properties(); // Map<String,String> 형태. XML 입출력 특화
				
				String filePath = JDBCTemplate.class.getResource("/edu/kh/jsp/sql/driver.xml").getPath();
				// xml 위치 지정. 
				// driver.xml 파일 읽어오기
				prop.loadFromXML( new FileInputStream(filePath));   // XML 내용이 Properties에 저장됨.
				
				// XML 내용 모두 String 변수에 저장
				String driver = prop.getProperty("driver");
				String url = prop.getProperty("url");
				String user = prop.getProperty("user");
				String password = prop.getProperty("password");
				
				Class.forName(driver);  // 커넥션 생성
				conn = DriverManager.getConnection(url,user,password); // Connection 객체생성
				conn.setAutoCommit(false);          // 자동 커밋 비활성화
			}
		}catch(Exception e) {
			System.out.println("[Connection 생성중 예외 발생]");
			e.printStackTrace();
		}
		return conn;
	}

	
	
	/** Connection 객체 자원 반환메서드
	 * @param conn
	 */
	public static void close(Connection conn) {
		try {
			if(conn != null && !conn.isClosed())  conn.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	/** Statement 객체 자원 반환메서드 ( PreparedStatement(자식) 
	 * 다형성/ 동적바인딩
	 * @param stmt
	 */
	public static void close(Statement stmt) {
		try {
			if(stmt != null && !stmt.isClosed())  stmt.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	/** ResultSet 객체 자원 반환메서드
	 * @param rs
	 */
	public static void close(ResultSet rs) {
		try {
			if(rs != null && !rs.isClosed())  rs.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	/** 트랜잭션 Commit 메서드
	 * @param conn
	 */
	public static void commit(Connection conn) {
		try {
			if(conn != null && !conn.isClosed()) conn.commit();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	/** 트랜잭션 Rollback 메서드
	 * @param conn
	 */
	public static void rollback(Connection conn) {
		try {
			if(conn != null && !conn.isClosed()) conn.rollback();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

 

 

 

StudentService.class        ( Connection을 해줄 Service 클래스 )

public class StudentService {
	
	private StudentDAO dao = new StudentDAO();

	
	public List<Student> selectAll() throws Exception {
		
		Connection conn =getConnection();   // 커넥션 생성
		
		List<Student> stdList = dao.selectAll(conn); // DAO 메서드 호출 후 결과받기
		
		close(conn);    // Connection 객체반
		
		return stdList;
	}
}

 

 

StudentDAO.class         ( DB 와 만날 DAO 클래스 )

public class StudentDAO {

	private Statement stmt;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	private Properties prop;
	
	// 기본생성자 호출시 sql.xml 파일 읽기.
	public StudentDAO() {
		try {
			String filepath
			= StudentDAO.class.getResource("/edu/kh/jsp/sql/student-sql.xml").getPath();
		
			prop = new Properties();
			prop.loadFromXML( new FileInputStream(filepath) );
		
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	
	/** 학생 전체조회 DAO
	 * @param conn
	 * @return
	 */
	public List<Student> selectAll(Connection conn) throws Exception{

		// 결과 저장용 변수 선언
		List<Student> stdList = new ArrayList<>();
		
		try {
			String sql = prop.getProperty("selectAll");
			
			stmt = conn.createStatement();  //Statement 객체생성
			rs = stmt.executeQuery(sql);    // sql 수행수 결과(ResultSet)반환받기
		
			while(rs.next()) {
				String studentNo = rs.getString("STUDENT_NO");
				String studentName = rs.getString("STUDENT_NAME");
				String studentAddress = rs.getString("STUDENT_ADDRESS");
				String departmentName = rs.getString("DEPARTMENT_NAME");
				
				stdList.add (
						new Student(studentNo,studentName,studentAddress,departmentName)
						);
			}
		}finally {
			close(rs);     // 사용한객체 자원반환
			close(stmt);
		}
		return stdList;    // 결과반환 
	}
}

 

 

 

selectAll.jsp          ( DB 찍고온 stdList 출력 )

<%@ page language="java" contentType="text/html; cherset=UTF-8"
    pageEncoding ="UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> 

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>학생 전체 조회</title>
</head>
<body>
	<table border="1">
		<tr>
			<td>학번</td>
			<td>이름</td>
			<td>학과</td>
			<td>주소</td>
		</tr>
		<c:forEach var="student" items="${stdList}" >
			<tr>
				<td>${student.studentNo }</td>
				<td>${student.studentName }</td>
				<td>${student.departmentName }</td>
				<td>${student.studentAddress }</td>
			</tr>
		</c:forEach>
		
	</table>
</body>
</html>

 

 

 

 

 

 

누르면 ??

찾을값이 나온다 .