[웹 프로그래밍(풀스택)] JDBC - BE

1. JDBC란?

JDBC(Java Databse Connectivity) 정의

- 자바를 이용한 데이터베이스 접속과 SQL 문장의 실행, 그리고 실행 결과로 얻어진 데이터의 핸들링을 제공하는 방법과 절차에 관한 규악.

- 자바 프로그램 내에서 SQL을 실행시키기 위한 API라고 생각하면 되겠다. 

- SQL과 프로그래밍 언어의 통합 접근 중 한 형태

Java는 표준 인터페이스인 JDBC API를 제공한다. 그래서 사용하기 굉장히 편하다고 한다.

데이터베이스 벤더(프로그램 판매인 혹은 업자), 또는 기타 써드파티에서 구현한 드라이버를 제공해준다.

환경 구성

1. JDK 설치

2. JDBC 드라이버(데이터베이스 벤더가 제공한  라이브러리) 설치

   - Maven에 다음과 같은 의존성이 추가된다.

<dependency>   
  <groupId>mysql</groupId>   
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.45</version>
 </dependency>

앞에서 우리가 JSTL을 사용할 때 lib 디렉토리에 3개의 파일을 다운받아 저장한 것을 기억하는가? Maven은 이러한 과정을 생략하게 해주는 기능이라고 생각하면 되겠다. JSTL처럼 JDBC를 사용하려면 라이브러리를 다운받아야 하는데, Maven에 이러한 의존성을 추가하면 된다. 

앞서 mySQL을 사용했을 때 과정을 생각해보자. 

1. mysql -u이용자명 -p 데이터베이스명 -h호스트명 이런식으로 시행을 하였다. 이런 과정이 지나면 클라이언트와 데이터베이스가 연결이된다. 

2. 쿼리문을 작성하였다.

3. ;을 찍고 엔터를 눌렀다. 이는 쿼리문을 실행한 것으로 이제 우리가 DB가 우리한테 원하는 값을 돌려준다. 

JDBC 프로그램도 똑같이 작동한다. 아래는 JDBC로 원하는 데이터를 받는 방법이다. 

1. import java.sql.*;
2. 드라이버를 로드 한다.(벤더에서 제공하는 라이브러리를 로딩하는 것)
3. Connection 객체를 생성한다.(DB에 접속하는 부분이라고 생각하면 된다)
4. Statement 객체를 생성 및 질의 수행(select~처럼 쿼리문을 생성한다, 그리고 실행하는 코드가 필요하다)
5. SQL문에 결과물이 있다면 ResultSet 객체를 생성한다.(Select문만 결과가 있다)
6. 모든 객체를 닫는다.(연결 한 후에는 접속을 끊어줘야 한다. 끊어주지 않으면 클라가 접속할 수 있는 갯수가 정해져있기 때문에 어느 순간에는 클라이언트가 접속을 요구할 때 안될 수도 있다.)

JDBC 클래스의 생성관계

[출처] Naver Boostcourse

그림에서 나온 것처럼 생성되는 객체는 이러한 관계를 형성하고 있다. 기억해야 할 점은 닫아줄 때 열었던 순서의 반대 순서로 닫아주자. 

JDBC 사용- 단계별 설명

1. IMPORT(우리가 사용해야 할 모든 메서드가 java.sql

import java.sql.*

 

2. 드라이버 로드

여기서 데이터베이스 벤더에 따라 안에 들어가는 값이 다르다. 우리는 mysql을 사용하기 때문에 com.mysql.jdbc.Driver을 사용하는 것을 알 수 있다. Class.forName 메서드를 사용하게 되면 이 객체가 메모리에 올라가게 된다. 자바에서 객체 생성할 때 new와 비슷하게 동작한다고 생각하면 되겠다.

Class.forName( "com.mysql.jdbc.Driver" );

 

3. Connection 얻기

connection 객체를 얻어내기 위해서 사용되는 객체는 DriverManager이다. DriverManager 객체에서 getConnection 메소드를 통해 Connection 객체 con을 받아오면 되겠다. dburl은 말그대로 데이터베이스가 있는 URL이고, user이름, 비밀번호를 입력하면 된다. 앞서 선언된 dburl 객체는 벤더마다 다르다고 하지만, 각 벤더에서는 미리 알려주기 때문에 걱정할 필요는 없겠다.

String dburl  = "jdbc:mysql://localhost/dbName";

Connection con =  DriverManager.getConnection ( dburl, ID, PWD );

4. Statement 생성

이제 실제 쿼리를 작성하면 된다. 앞에서 관계도를 봤을 때 Connection을 통해 statement를 얻었다고 하였다. 밑을 보면 con.createStatement()메소드를 통해 Statement 객체를 선언한 것을 알 수 있다. 

Statement stmt = con.createStatement();

5. 질의 수행

쿼리에 대한 결과가 존재하면 담을 수 있는 객체인 ResultSet을 Statement 객체로 얻어올 수 있다고 말한 바 있다. 밑을 살펴보면 stmt.executeQuery()메소드로 만약 쿼리문 안에 있는 명령문이 SELECT라면 그에 상응하는 응답이 rs 객체에 담기게 된다. 참고를 살펴보면 executeQuery()는 SELECT문만, ExecuteUpdate는 반환값이 없는 INSERT, UPDATE, DELETE문에 사용하는 것을 알 수 있다.

ResultSet rs = stmt.executeQuery("select no from user" );

참고
stmt.execute(“query”);             //any SQL
stmt.executeQuery(“query”);     //SELECT
stmt.executeUpdate(“query”);   //INSERT, UPDATE, DELETE

6. ResultSet으로 결과 받기

앞에서 rs로 받은 결과값을 while문으로 출력할수 있다. 여기서 ResultSet은 데이터베이스에만 존재하고, rs는 결과셋을 가르킬 수 있는 레퍼런스 변수일 뿐이다. 이렇게 하는 이유는 만약 우리가 반환하는 값이 만 개의 데이터라고 하면, 우리 서버로 이러한 방대한 양의 데이터를 가져오면 문제가 발생 할 수 있다. 아래는 rs에서 데이터를 계속해서 뽑아오고, 칼럼명이 no인 정수형 데이터를 뽑아온 것을 출력하는 코드이다. 

ResultSet rs =  stmt.executeQuery( "select no from user" );
while ( rs.next() )
      System.out.println( rs.getInt( "no") );

7. Close

우리 잊지 말고 ResultSet 객체, Statement 객체, Connection 객체를 열어줬으면 닫아주자. 순서는 열어줬던 순서의 반대이다.

rs.close();

stmt.close();

con.close();

실제 JDBC 소스코드를 확인해보자.

public List<GuestBookVO> getGuestBookList(){
		List<GuestBookVO> list = new ArrayList<>();
		GuestBookVO vo = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = DBUtil.getConnection();
			String sql = "select * from guestbook";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				vo = new GuestBookVO();
				vo.setNo(rs.getInt(1));
				vo.setId(rs.getString(2));
				vo.setTitle(rs.getString(3));
				vo.setConetnt(rs.getString(4));
				vo.setRegDate(rs.getString(5));
				list.add(vo);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			DBUtil.close(conn, ps, rs);
		}		
		return list;		
	}

이처럼 JDBC를 사용하는 과정에서 반복되는 코드가 굉장히 많다. 예로 들어 Connection으로 드라이버를 로드한다던가, 쿼리를 불러온다던가, close를 해준다거나. 이러한 반복되는 코드들을 프레임워크에서 대신 해주기 때문에 우리가 직접 JDBC를 작성해보지는 않고, Spring JDBC를 이용할 것이라고 한다. 그럼에도 이러한 시간을 가진 이유는 나중에 Spring JDBC로 작성하다가 문제가 발생하면 JDBC를 미리 알면 고치기 쉽기  때문이라고 한다.

2. JDBC 실습 -1

이번에는 JDBC를 실습하는 시간을 가져보자. 먼저 Maven project를 만들어주자. Group id는 kr.or.connect로 하고 Artifact id는 jdbcexam으로 만들자. 필자는 8.0 버전을 사용하기 때문에 강의에서 알려준 방법이 아닌 다른 방법으로 pom.xml 파일에 설정을 추가하였다. 만약 8.0.x 버전을 사용중이라면 https://mvnrepository.com/artifact/mysql/mysql-connector-java 여기에서 현재 사용중인 버전을 클릭한 후 dependency를 dependencies에 복사하여 넣어주자.(10조twocloud님의 댓글에서 도움을 받았다).

이러한 설정이 끝나고 나면 항상 저장해준 후 프로젝트 우클릭-> Maven -> Update Project를 해줘야 한다. 

이제 데이터베이스에서 데이터를 가져오거나 넣어줄 때 이러한 정보를 저장할 객체가 필요한데, 이를 생성해보자. SQL을 실습할 때 사용했던 테이블 중에서 Role이라는 테이블을 가지고 실습을 진행해보자. 한번 DB에 접속해보자. connectuser로 connectdb에 접속하여 보자. Role이란 테이블을 다루기로 하였으니 desc role;으로 테이블의 구조를 살펴보자.

mysql> desc role;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| role_id     | int          | NO   | PRI | NULL    |       |
| description | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

두 개의 칼럼이 존재하고 int 타입, varchar(100) 타입인 것을 기억하자. 이제 저장할 객체가 필요하니 Class를 하나 만들어야 한다. src/main/java에서 Class를 생성해보자. 우리는 Role이라는 테이블에 대한 객체를 생성할 것이기 때문에 Role이라는 이름으로 만들면 되고, 위에 Package를 보면 jdbcexam으로 마무리되는 것을 볼 수 있다. 하지만 우리가 현재는 하나의 객체만 다루고 있지만 나중에 다양한 객체를 다룰 때 한 패키지에 모든 것을 저장하면 힘들 수 있으니 뒤에 패키지를 의미하는 .dto를 붙여주자. dto에 대한 설명은 다음에 자세히 한다고 한다.

앞서 우리는 두 개의 칼럼에 대한 객체를 담아와야 하기 때문에 Integer 변수 roleId랑 String형 변수 description을 private으로 선언하자. 여기서 알아야 할 점은 자바에서 필드명은 소문자로 시작하고 단어가 두개 이상이 연결될 때는 두 번째 단어의 첫 글자를 대문자로 쓴다. 이를 카멜 표기법이라고 한다.

private으로 변수를 선언한 후에는 getter와 setter를 해주고, toString 함수를 오버라이딩 해주자.

다음 만들어줄 클래스는 테이블에서 입력/조회/삭제 등을 할 수 있는 기능을 갖고 있는 클래스를 만들어보자. 이번에도 똑같이 클래스를 생성해주는데 이번에는 패키지를 .dao로 저장하고 이름을 RoleDao로 하자. dao dto는 다음 장에 자세히 배운다고 한다. 이제 한 건의 데이터를 가져오는 함수를 만들어보자.

public class RoleDao {
	public Role getRole(Integer roleId) {
		Role role = null;
		
		
		return role;
	}
}

우리가 정보를 가져오고 그 것을 Role 객체에 담아야 하기 때문에 Role 클래스로 선언해주고, 일단은 null로 초기화를 해주자. 앞서 JDBC 이론을 배웠을 때 connection Statement 등을 기억하면서 실습해보자. 먼저 연결, 명령, 결과 값 객체를 선언해주자.

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

이제 예외처리를 해줘야 하는데, 데이터베이스에 연결하거나 등의 작업들은 예외 상황이 굉장히 많다(중간에 접속이 끊기거나 등의 상황). 가장 중요한 부분은 우리가 열어놓은 모든 객체들을 닫아줘야 한다는 것이다. 왜 닫아야하는지는 앞서 설명한 바 있고, 어떤 경우에도 닫아줘야 하기 때문에 finally 블록에 가장 먼저 작성을 해주자. 이제 닫아주는 과정에서도 try catch문을 써야 하는데, 이는 어느 부분에서 오류가 발생할지 모르기 때문이다. 단순히 try catch문을 만들지 말고, rs, ps, conn이 null이 아닐 때 닫아주는 예외처리로 코드를 작성해보자.

finally {
			if(rs != null) {
				try {
					rs.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
			}
			if(ps != null) {
				try {
					ps.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
			}
		}

이제 닫아주는 부분을 처리했고 드라이버를 로딩해보자. 앞서 설명한 바와 같이 우리의 밴더는 mySQL이기 때문에 Class.forName("com.mysql.cj.jdbc.Driver");으로 드라이버를 로딩하고(여기 또한 8.0.x 버전을 사용하는 사람들의 경우이다. 이 코드가 되지 않으면 강의에서 제공하는 주소를 이용해보자), 연결하기 위하여 DriverManager.getConnection메소드를 사용해주자.

try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, password);
		}catch(Exception e) {
			e.printStackTrace();

DriverManager.getConnection에서는 db의 url, 사용자, 비밀번호가 필요한데, 우리가 접속을 할 때마다 필요하기 때문에 아예 getRole 바깥에서 상수로 선언을 해주자.

private static String dburl = "jdbc:mysql://localhost:3306/connectdb";
private static String dbUser = "connectuser";
private static String dbpasswd = "connect123!@#";

이제 DriverManager.getConnection 매개변수로 위 세 변수를 넣어주면 된다.

앞 장에서 말했듯이 Statement 객체는 conn 객체로부터 호출 할 수 있다고 했다. 이제 쿼리문이 필요한데, sql이라는 String형 변수를 선언하여 쿼리문을 작성하자. 

String sql = "SELECT description,role_id FROM role WHERE role_id = ?";
ps = conn.prepareStatement(sql);

sql에서 ?인 부분은 항상 바뀔 수 있는 부분이다. prepareStatement는 들어오는 인자 값에 따라 쿼리문이 바뀌는 필요 없이 ?를 치환해줄 수 있는 특징을 가지고 있다. 이렇듯 설정해주었으면 ?를 바꿔주는 부분이 필요한데 여기서는 ps의 set이라는 메소드를 이용하면 된다. 여기서 roleId는 int형이기 때문에 setInt를 사용한다.

ps.setInt(1, roleId);

첫 번째 매개변수는 물음표의 순서다. 물음표가 여러 개면 그 중 순서를 정하는 것이다. 두 번째가 ?를 대신해서 들어갈 값인데, 우리는 roleId를 인자로 받아올 것이기 때문에 그대로 넣어주면 된다.

이제 쿼리문을 executeQuery로 실행해주면 된다.

rs = ps.executeQuery();

이제 결과를 출력해야 하는데 만약 결과값이 없으면 출력이 당연히 안될 것이다. 그래서 if문으로 값이 존재할 경우에만 출력해주면 된다.

if (rs.next()) {
				String description = rs.getString(1);
				int id = rs.getInt("role_id");
				role = new Role(id, description);
			}

rs.next는 boolean을 반환하게 된다. 만약 값이 존재하면 true, 아니면 false이다. 꺼내오는 것은 rs 객체의 get메소드를 사용하면 된다. 위는 값을 가져오는 두 가지 방법인데, 먼저 위 쿼리문에서 description을 먼저 가져왔기 때문에 getString(1)로 description 칼럼의 정보를 가져왔고, 두 번쨰에 아예 칼럼의 이름을 매개변수로 가져올 수도 있다. 이 두 값을 우리가 선언한 Role 객체의 두 매개변수로 가져가고 싶기 때문에 new Role로 선언해준 후 매개변수로 넣어준다.

3. JDBC 실습-2

이번에는 입력/삭제/여러 건 SELECT하는 부분을 살펴보자.

먼저 입력하는 함수인  addRole함수를 만들어보자.

public class RoleDao {
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb";
	private static String dbUser = "connectuser";
	private static String dbpasswd = "connect123!@#";
	public int addRole(Role role) {
		int InsertCount = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(dburl, dbUser, dbpasswd);
			String sql = "INSWERT INTO role (role_id, description) VALUES (?, ?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,  role.getRoleId());
			ps.setString(2, role.getDescription());
			InsertCount = ps.executeUpdate();
	}catch (Exception e) {
		e.printStackTrace();
	} finally {
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
		return InsertCount;
	}

앞에서 살펴본 조회를 하는 기능과 다른 점을 중심적으로 살펴보자. 일단 앞에서 조회한 값을 반환하는 객체인 ResultSet이 없다. 입력/삭제/업데이트는 반환값이 없기 때문에 ResultSet을 호출하지 않아도 된다. 또한 mySQL 실습할 때 쿼리문이 실행되고 몇 건이 성공했는지 떴는데, 이 값이 InsertCount에 정수형으로 반환되고 이 함수는 그 정수를 반환하게 되어 있다. 그 외로는 예외처리 해주고 닫아주는 과정은 다 똑같다.

이제 새로 추가해줄 클라스 JDBCExam2를 만들어보자.

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam2 {
	public static void main(String[] args) {
		int roleId = 501;
		String description = "CTO";
		
		Role role = new Role(roleId, description);
		
		RoleDao dao = new RoleDao();
		int insertCount = dao.addRole(role);

		System.out.println(insertCount);
	}
}

먼저 추가해줄 role을 선언해주고, roleid는 501, description은 "CTO"를 매개변수로 넣어주자. 그 다음 객체가 RoleDao인 dao를 선언해준 후, dao.addRole로 roleid가 501, description이 "CTO"인 role을 넣어준 후, 그 결과값을 insertCount으로 반환하여 출력해주자. 그러면 한 건이 성공했다는 뜻으로 1이 반환이 될 것이다. 이제 앞서 만들었던 JDBCExam1에서 100이 아니라 501로 제대로 출력이 되는 것을 확인할 수 있다.

DELETE 및 UPDATE는 위 INSERT와 크게 다르지 않다. 쿼리문만 조금 손바주면 할 수 있다. 이 부분은 따로 기재하지 않겠다.

4. JDBC 실습-3

이번에는 단순히 한 건을 처리하는 것이 아니라 여러 건을 조회/입력/삭제/추가 하는 예제를 살펴보자.

먼저 SELECT로 모두 조회하는 방법을 살펴보자.

package kr.or.connect.jdbcexam.dao;

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 java.util.List;

import kr.or.connect.jdbcexam.dto.Role;

public class RoleDao {
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb";
	private static String dbUser = "connectuser";
	private static String dbpasswd = "connect123!@#";

	public List<Role> getRoles() {
		List<Role> list = new ArrayList<>();

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		String sql = "SELECT description, role_id FROM role order by role_id desc";
		try (Connection conn = DriverManager.getConnection(dburl, dbUser, dbpasswd);
				PreparedStatement ps = conn.prepareStatement(sql)) {

			try (ResultSet rs = ps.executeQuery()) {

				while (rs.next()) {
					String description = rs.getString(1);
					int id = rs.getInt("role_id");
					Role role = new Role(id, description);
					list.add(role); // list에 반복할때마다 Role인스턴스를 생성하여 list에 추가한다.
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return list;
	}
}

먼저 다른 점은 반환하는 형이 Role이 아니라 List<Role>이 됬다는 것이다. 이제 우리가 원하는 정보들은 한 건의 role이 아니라 복수의 role이기 때문에 리스트형으로 반환하는 것이다. 또한 가장 큰 차이점은 여기서는 try with resource라는 문법을 사용했다는 것이다. try (Connection conn = DriverManager.getConnection(dburl, dbUser, dbpasswd);
PreparedStatement ps = conn.prepareStatement(sql))을 선언하면 뒤에 finally문을 사용하지 않더라도 자동으로 close가 된다고 한다. 또한 ResultSet으로 가져온 후, 이번에는 if(rs.next())가 아니라 while문을 사용하였는데, 여러 개의 결과가 리스트에 담겼을 것이므로 rs.next()가 false일때까지 출력하게 되어있다.

package kr.or.connect.jdbcexam;

import java.util.List;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam1 {

	public static void main(String[] args) {
		RoleDao dao = new RoleDao();
		List<Role> list = dao.getRoles();
		for(Role role: list) {
			System.out.println(role);
		}
	}

}

이제 JDBCExam3로 여러 건의 정보를 가져오는 getRoles 메소드를 사용해보자. 반환해야 하는 형이 List이기 때문에 List<Role>로 선언을 하였고, 리스트에서 각 role을 가져와 출력하였다.

나중에 Web API를 학습할 떄 우리가 실습한 내용이 나온다고 한다. 잘 저장하고 있도록 해야겠다.