SQL 및 JDBC 기본 구조와 API

학생정보 목록 생성하기

 

먼저 'H2 console' 을 실행하고 <연결> 버튼을 눌러서 메인 화면으로 이동한다.

 

 

※ DDL 실습

학생정보 테이블을 생성, 수정, 삭제하는 과정을 단계별로 해보도록 한다.

 

▶ CREATE TABLE

다음과 같이 SQL 문을 작성하여 TABLE을 생성하도록 한다.

 

 

실행버튼을 누르면 STUDENT 테이블이 생성된 것을 확인할 수 있다.

 

 

▶ ALTER TABLE

생성된 student 테이블에 tel 칼럼을 추가하고, username 크기를 20에서 10으로 조정한다.

그리고 id 칼럼에 지정된 기본키 제약 조건을 제거한다.

 

 

변경된 테이블 구조를 확인하기 위해 SHOW 구문을 실행해 비교해본다.

 

 

 

▶ DROP TABLE

DROP TABLE로 테이블을 삭제할 때 삭제하고자 하는 테이블과 연관된 테이블이 있다면, CASCADE를 함께 사용해야 삭제가 가능하다.

현재까지는 단일 테이블 student 을 삭제하기 때문에 CASCADE를 사용하지 않아도 무방하다.

 

 

 

※ DML 실습

앞에서 만든 학생정보 테이블에 데이터를 추가하고 수정, 삭제하는 DML을 살펴보도록 한다.

 

▶ INSERT

student 테이블의 id 칼럼에는 auto_increment 속성이 부여되어 있으므로 다음과 같이 칼럼을 지정한 다음, 데이터를 부분적으로 추가해야 한다.

 

 

▶ SELECT

DML 중에서 가장 많이 사용하는 명령으로 검색을 위한 조건도 다양하게 지정할 수 있지만 우선 가장 기본적인 구문을 살펴보도록 한다.

 

 

이번엔 WHERE 조건절을 사용하여 다양한 조건으로 데이터를 조회해 본다.

 

 

'김'으로 시작하는 모든 데이터를 조회한다.

 

 

다음은 정렬을 위해 ORDER BY를 사용한다.

이름을 내림차순으로 정렬하는 방법은 다음과 같다.

 

 

이름을 오름차순으로 하고 대학은 내림차순으로 정렬한다.

 

 

 

중복된 데이터는 하나만 표시하기 위한 DISTINCT를 사용한다.

중복된 이름은 하나만 가져오도록 설정한다.

 

 

이름과 대학의 조합으로 중복을 제거한다.

 

 

 

JDBC 기본 구조와 API의 이해

 

 

JDBC는 Java DataBase Connectivity 의 약어로 자바 프로그램에서 다른 기종 간의 데이터 베이스를 표준화된 방법으로 접속할 수 있도록 만든 API 규격이다.

 

※ JDBC의 개념

앞에서 살펴본 것처럼 데이터베이스는 종류가 매우 다양하다.

이로 인해 응용 프로그램에서 데이터베이스에 접속하고 데이터를 처리하는 방법이 제각각이기 때문에 개발에 많은 어려움이 있다.

이러한 문제를 해결해주는 JDBC는 자바 애플리케이션에서 표준화된 방법으로 다양한 데이터베이스에 접속할 수 있도록 설계된 인터페이스이다.

따라서 애플리케이션 개발자는 각 데이터베이스에 대해 자세히 알지 못해도 JDBC API만 알면 데이터베이스에서 동작할 수 있는 애플리케이션을 개발할 수 있다.

JDBC 구조는 다음과 같다.

 

 

※ JDBC 드라이버 설치

Maven 기반으로 프로젝트를 변경했기 때문에 다른 라이브러리와 마찬가지로 pom.xml에 의존성을 추가하면 자동으로 프로젝트에서 참조된다.

또한 톰캣을 통해 실행하는 과정에도 함께 패키징되기 때문에 별도의 과정 없이 사용할 수 있다.

 

이클립스로 먼저 Maven을 설정해보도록 한다.

1. 먼저 'jwbook' 프로젝트를 선택하고 마우스 오른쪽 버튼을 눌러 [Configure]->[Convert to Maven Project]를 선택한다.

 

2. Maven 프로젝트를 위해 몇 가지 필요한 사항을 등록하고, <Finish>를 클릭한다.

 

 

3. 자동으로 기존 프로젝트 구조에 몇몇 폴더 구조와 'pop.xml' 파일이 생성된 것을 확인할 수 있다.

기본적으로 'pom.xml' 파일이 오픈된 상태이며, 하단에 보면 여러 탭이 있어 다양한 형태로 설정 파일 정보를 보여준다.

 

 

4. 프로젝트에 필요한 라이브러리를 등록해본다.

우선 기존 [WEB-INF / lib]에 복사했던 JSTL 관련 라이브러리를 삭제한다.

 

5. 필요한 라이브버리는 리포지터리 사이트에서 검색한 버전에 맞게 선택한 화면에서 코드를 복사해서 사용한다.

https://mvnrepository.com 에 접속하여 'JSTL'을 검색하고 1.2 버전의 코드를 복사하여 사용한다.

 

 

6. 복사한 코드를 다음과 같이 'pom.xml'의 아래 부분에 <dependencies> ... </dependencies> 를 추가하고 이 사이에 붙여넣어 다음과 같이 JSTL 라이브러리 의존성을 추가한다.

다른 라이브러리를 추가할 경우 <dependencies> ... </dependencies> 사이에 넣어주어야 한다.

 

 

7. 파일을 저장하고 'pom.xml' 파일을 프로젝트 탐색기에서 선택한 다음 마우스 오른쪽 버튼을 눌러 [Maven] -> [Update Project ...] 를 선택하고 나오는 화면에서 <OK> 버튼을 클릭한다.

 

 

8. 이클립스는 해당 라이브러리를 로컬 리포지터리에 다운로드하고 프로젝트에서 참조할 수 있는 상태로 만든다.

다음과 같이 해당 라이브러리가 프로젝트의 [Maven Dependencies]에서 참조할 수 있도록 보인다.

 

 

 

이제 JDBC 드라이버 설치를 해보도록 한다.

'pom.xml'의 <dependencies> ... </dependencies> 사이에 다음과 같이 H2 데이터베이스 의존성을 추가한다.

<scope> ... </scope> 부분은 반드시 제거하고 'pom.xml' 에 복사해야 한다.

 

 

[Java Resources] -> [Libraries] -> [Maven Dependencies] 에 'h2-1.4.200.jar'이 추가되어 있으면 정상적으로 등록된 것이다.

 

 

※ JDBC 프로그래밍 과정

JDBC 프로그래밍의 기본 절차를 핵심 API 중심으로 살펴보도록 한다.

JDBC 프로그래밍의 기본 단계와 사용 클래스는 다음과 같다.

 

 

 

◆ 1단계 : JDBC 드라이버 로드

데이터베이스에 접속하려면 일단 해당 데이터베이스의 JDBC 드라이버를 로드해야 한다.

이러한 과정이 필요한 이유는 JDK에 포함된 JDBC는 API 규격을 위한 자바 인터페이스로 실제로 동작하려면 각각의 데이터베이스 공급 업체에서 구현한 드라이버 클래스가 사전에 로딩되어야 하기 때문이다.

대부분의 경우 JDBC 드라이버를 로드하는 방법으로 Class.forName() 메서드를 사용한다.

Class.forName("org.h2.Driver");

 

◆ 2단계 : 데이터베이스 연결

드라이버가 로드되면 해당 데이터베이스의 JDBC 드라이버를 이용해 프로그램을 작성할 수 있는 상태가 된 것을 의미한다.

실제 데이터베이스와 연결하려면 Connection 클래스의 인스턴스가 필요하며, DriverManager.getConnection() 메서드를 이용해서 레퍼런스를 가져올 수 있다.

또한 JDBC URL, DB 사용자 아이디/비밀번호가 필요하다.

 

JDBC URL은 데이터베이스에 대한 다양한 정보를 포함한다.

각 데이터베이스별로 JDBC URL이 다르므로 사용하는 데이터베이스 매뉴얼을 참고해서 작성해야 한다.

JDBC URL 구조는 다음과 같다.

jdbc:하위 프로토콜:데이터 원본 식별자

 

H2의 경우 DB 실행 방식에 따라 다음과 같은 형식을 취한다.

// 임베디드 모드(파일 직접 접속)
jdbc:h2:~/text    // 윈도우 사용자 홈디렉터리에 'test.mv.db' 파일 사용
jdbc:h2:e:/Dev/test   // e드라이브의 [Dev] 폴더에 'test.mv.db' 파일 사용

// 네트워브 서버 모드
jdbc:h2:tcp://localhost/~/test   // 네트워크로 접속 허용, 데이터 파일은 사용자 홈디렉터리에 위치

// 메모리 DB 모드(종료 시 데이터 소멸됨)
jdbc:h2:mem:test_mem

 

Connection 클래스 인스턴스 레퍼런스 얻기

JDBC 클래스 로딩과 URL이 준비되었으면 실제 데이터베이스와의 연결을 만들기 위한 코드를 작성한다.

DriverManager의 getConnection() 메서드를 사용한다.

Connection conn = DriverManager.getConnection(JDBC_URL, "아이디", "비밀번호");

 

◆ 3단계 : Statement 생성

데이터베이스와 연결을 한번 완료하면 이후 연동부터는 SQL 문을 통해 이루어진다.

이때 문자열로 이루어진 SQL 문을 JDBC에서 처리할 수 있는 객체로 변환해야 하는데, 이때 사용되는 것이 Statement 객체다.

하지만 보통 SQL 문과 데이터를 조합하기 때문에 일반 Statement 보다는 Statement를 상속받는 PreparedStatement 를 사용하는 것이 좋다.

PreparedStatement pstmt = conn.prepareStatement("insert into test values(?, ?)");
pstmt.setString(1, request.getParameter("username"));
pstmt.setString(2, request.getParameter("email"));

 

◆ 4단계 : SQL 문 전송

3단계 과정을 통해 PreparedStatement 객체가 준비되었고 실제 쿼리의 실행은 SQL 문 종류에 따라 executeQuery() 혹은 executeUpdate()를 사용하게 된다.

executeQuery() : SELECT 문을 수행할 때 사용한다. (반환값은 ResultSet 클래스 타입)

executeUpdate() : UPDATE, DELETE와 같은 문을 수행할 때 사용한다. (반환값은 INT 값)

 

◆ 5단계 : 결과 받기

데이터베이스에서 데이터 결과를 받으려면 Statement나 PreparedStatement의 executeQuery() 를 사용한다.

입력, 수정, 삭제와 달리 데이터를 갖고 오는 경우에는 가져온 결과 데이터를 처리하기 위한 ResultSet 객체가 필요하다.

ResultSet rs = pstmt.executeQuery();

 

◆ 6단계 : 연결 해제

데이터베이스 사용이 종료되면 기본적으로 연결을 해제해야 한다.

데이터베이스의 경우에도 동시에 여러 연결을 지원하지만 동시 연결 수에 따라 라이선스 비용이 증가하기도 하고, 동시 연결 가능 수가 적은 경우 대기 시간이 길어지는 문제가 발생하기도 한다.

따라서 사용이 끝난 데이터베이스와의 연결은 해제해주는 것이 좋다.

더불어 데이터베이스와의 연결뿐만 아니라 하나의 연결에서 발생하는 여러 Statement, ResultSet 같은 객체도 바로바로 종료하는 것이 좋다.

rs.close();
pstmt.close();
conn.close();

 

다만 응용 프로그램과 데이터베이스의 연결이 요청에 따라 이루어지고 종료되는 구조는 성능상 적합하지 않기 때문에 실제 시스템 구현시에는 DBCP를 사용하게 된다.

 

DBCP란?

중간에 미들웨어 혹은 컨테이너에서 데이터베이스와의 연결을 미리 만들어주고 요청 클라이언트에 전달하는 구조를 말한다.

또한 일정 수준의 연결을 유지하면서 요청이 증가하는 경우 미리 연결을 만들거나 하는 자동 관리 기능이 포함되어 있다.

보통 데이터베이스 회사에서 제공하는 구현체를 사용하거나 Apache Commons DBCP, Hikari CP, Tomcat JDBC CP 등의 오픈소스 구현체를 사용하면 된다.

 

 

JDBC 를 이용한 학생정보 조회와 등록

 

 

1. 모델 구현(DO)

Student.java 코드

package ch09;
import java.sql.Date;

public class Student {
	private int id;
	private String username;
	private String univ;
	private Date birth;
	private String email;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	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 Date getBirth() {
		return birth;
	}
	public void setBirth(Date birth) {
		this.birth = birth;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	
}

 

2. 모델 구현(DAO)

StudentDAO.java 코드

package ch09;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

public class StudentDAO {
	Connection conn = null;
	PreparedStatement pstmt;
	
	final String JDBC_DRIVER = "org.h2.Driver";
	final String JDBC_URL = "jdbc:h2:tcp://localhost/~/jwbookdb";
	
	public void open() {
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(JDBC_URL, "jwbook", "1234");
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public void close() {
		try {
			pstmt.close();
			conn.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public void insert(Student s) {
		open();
		String sql = "INSERT INTO student(username, univ, birth, email) values(?,?,?,?)";
		
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, s.getUsername());
			pstmt.setString(2, s.getUniv());
			pstmt.setDate(3, s.getBirth());
			pstmt.setString(4, s.getEmail());
			
			pstmt.executeUpdate();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			System.out.println(s.getUsername() + " " +s.getUniv() + " " +s.getBirth() + " " + s.getEmail());
			close();
		}
	}
	
	public List<Student> getAll(){
		open();
		List<Student> students = new ArrayList<>();
		
		try {
			pstmt = ((java.sql.Connection) conn).prepareStatement("select * from student");
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()) {
				Student s = new Student();
				s.setId(rs.getInt("id"));
				s.setUsername(rs.getString("username"));
				s.setUniv(rs.getString("univ"));
				s.setBirth(rs.getDate("birth"));
				s.setEmail(rs.getString("email"));
				
				students.add(s);
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		
		return students;
	}
	
	
}

 

3. 컨트롤러 구현

새로운 학생을 추가하는 insert 메서드는 request.getParameter()를 이용해 모든 입력값을 읽어와 Student 객체로 매핑한 다음 dao.insert() 메서드를 호출하고, 뷰 페이지로 되돌아가야 한다.

이때 입력값을 일일이 매핑하기보다는 Apache Commons Beaen Utils를 이용하는 것이 좋기 때문에 먼저 pom.xml에 다음과 같은 BeanUtils 라이브러리 의존성을 추가한다.

 

 

StudentController.java 코드

package ch09;

import java.io.IOException;
import javax.servlet.ServletConfig;
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 org.apache.commons.beanutils.BeanUtils;

/**
 * Servlet implementation class StudentController
 */
@WebServlet("/studentControl")
public class StudentController extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	StudentDAO dao;
	/**
	 * @see Servlet#init(ServletConfig)
	 */
	public void init(ServletConfig config) throws ServletException {
		// TODO Auto-generated method stub
		super.init(config);
		dao = new StudentDAO();
	}

	/**
	 * @see HttpServlet#service(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("utf-8");
		String action = request.getParameter("action");
		String view = "";
		
		if(request.getParameter("action") == null) {
			getServletContext().getRequestDispatcher("/studentControl?action=list").forward(request, response);
		} else {
			switch(action) {
				case "list" : view = list(request, response); break;
				case "insert" : view = insert(request, response);
			}
			getServletContext().getRequestDispatcher("/ch09/"+view).forward(request, response);
		}
	}

	public String list(HttpServletRequest request, HttpServletResponse response) {
		request.setAttribute("students", dao.getAll());
		return "studentInfo.jsp";
	}
	
	public String insert(HttpServletRequest request, HttpServletResponse response) {
		Student s = new Student();
		try {
			BeanUtils.populate(s, request.getParameterMap());
		} catch(Exception e) {
			e.printStackTrace();
		}
		dao.insert(s);
		return list(request, response);
	}
}

 

 

4. 뷰 구현

studentInfo.jsp 코드

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>학생정보</title>
</head>
<body>

	<h2>학생정보</h2>[<a href="/jwbook/studentControl">새로고침</a>]
	<hr>
	<table border="1">
		<tr><th>id</th><th>이름</th><th>대학</th><th>생일</th><th>이메일</th></tr>
		<c:forEach items="${students}" var="s">
			<tr>
				<td> ${s.id} </td>
				<td> ${s.username} </td>
				<td> ${s.univ} </td>
				<td> ${s.birth} </td>
				<td> ${s.email} </td>
			</tr>
		</c:forEach>
	</table>
	<hr>
	<h2>학생 추가</h2>
	<hr>
	<form method="post" action="/jwbook/studentControl?action=insert">
		<label>이름</label>
		<input type="text" name="username"><br>
		<label>대학</label>
		<input type="text" name="univ"><br>
		<label>생일</label>
		<input type="text" name="birth"><br>
		<label>이메일</label>
		<input type="text" name="email"><br>
		<button type="submit">등록</button>
	</form>

</body>
</html>