[RDBMS / JDBC를 이용해 데이터 처리하기] JDBC 실습

Hyunjun Kim·2025년 4월 27일
0

Data_Engineering

목록 보기
54/153

4 JDBC 실습

4.1 실습 세팅

4.1.1 실습 세팅 - 실습용 scheam, Table 만들기

Workbench 또는 MySQL CLI 에서 다음 명령어로 실습에 필요한 schema, table, data 를 만든다.

CREATE SCHEMA `de-jdbc`;
USE `de-jdbc`;
CREATE TABLE `product` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `updated_at` datetime NOT NULL,
  `contents` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `product` (`name`, `updated_at`, `contents`, `price`) VALUES ('shoes1', '2022-08-01 01:00:00', 'This is shoes1', '10000');
INSERT INTO `product` (`name`, `updated_at`, `contents`, `price`) VALUES ('shoes2', '2022-08-01 02:00:00', 'This is shoes2', '20000');
INSERT INTO `product` (`name`, `updated_at`, `contents`, `price`) VALUES ('shoes3', '2022-08-01 03:00:00', 'This is shoes3', '30000');
INSERT INTO `product` (`name`, `updated_at`, `contents`, `price`) VALUES ('shoes4', '2022-08-01 04:00:00', 'This is shoes4', '40000');
INSERT INTO `product` (`name`, `updated_at`, `contents`, `price`) VALUES ('shoes5', '2022-08-01 05:00:00', 'This is shoes5', '50000');
INSERT INTO `product` (`name`, `updated_at`, `contents`, `price`) VALUES ('shoes6', '2022-08-01 06:00:00', 'This is shoes6', '60000');
INSERT INTO `product` (`name`, `updated_at`, `contents`, `price`) VALUES ('backpack', '2022-08-02 04:00:00', 'This is backpack', '15000');
INSERT INTO `product` (`name`, `updated_at`, `contents`, `price`) VALUES ('shirt', '2022-08-03 05:00:00', 'This is shirt', '20000');
INSERT INTO `product` (`name`, `updated_at`, `contents`, `price`) VALUES ('glasses', '2022-08-04 06:00:00', 'This is glasses', '10000');

4.1.2 실습 세팅 - MySQL, JDBC driver 의존성 추가

build.gradle 의 dependencies 에 mysql connnetor library를 추가한다.

dependencies {
	implementation 'mysql:mysql-connector-java:8.0.30'
}

4.1.3 실습 - CRUD 코드

다음 코드를 따라서 수행해보고, JDBC의 동작을 위해서는 무엇이 필요한지 추측해보자.

try {
  //here de-jdbc is database name, root is username and password is null. Fix them for your database settings.
  Connection con = DriverManager.getConnection(
  "jdbc:mysql://localhost:3306/de-jdbc", "root", null);

  Statement stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery("select * from product");
  while (rs.next()) {
    System.out.println(rs.getInt(1) + " " + rs.getString(2) + " "
    + rs.getDate(3) + " " + rs.getString(4)
    + " " + rs.getInt(5));
  }
  con.close();
} catch (Exception e) {System.out.println(e);}

4.2 DriverManager

User와 Driver 사이의 인터페이스를 담당. Driver의 이용가능 여부, Database와 Driver사이의 Connection을 관리한다. Driver의 등록/해제, Connection의 연결/해제를 관리한다. Driver의 Register가 반드시 성공해야 DB와 상호작용을 할 수 있다.

4.2.1 DriverManager 로 MySQL 접속하기

과거에는 클래스로딩을 위해서 Class.forName() 으로 해당 클래스를 클래스패스에서 찾아서 클래스로더가 로딩할 수 있도록 한다. 이렇게 클래스 로딩이 되어야 DriverManager가 표준인터페이스로 메소드를 호출할 때, 해당 JDBC드라이버의 구현체로 연결될 수 있었다.

하지만 최신 버전에서는 Java SPI(서비스 프로바이더 인터페이스)를 이용해서 자동으로 MySQL Driver 클래스를 표준 Driver 인터페이스에 등록 해줄 수 있게되었다.
Connection url에 mysql 정보가 있고, myql connector library 가 클래스패스에 있다면, get connection 단계에서 자동으로 찾아준다.

4.2.2 DriverManager 로 Connection 연결하기

Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/de-jdbc", "root", null);

null : password

DB와의 모든 동작은 getConnection 을 통해서 JDBC Driver 클래스를 찾고, 연결까지 성공한 뒤에 할 수 있다.

4.3 Connection

Connection 객체 하나는, DB와 Java Client 사이의 하나의 물리적인 연결을 의미한다.

이 연결은 DB입장에서는 하나의 Session을 의미한다. Session은 변경사항의 묶음을 관리하는 하나의 단위라고 이해하면 된다. Session에 대해 자세한 내용은 이후 Transaction, RDBMS이해하기 부분에서 더 다룬다.

해당 연결에서 사용할 Statement, PreparedStatement, and DatabaseMetaData 객체들을 얻어올 수 있다.

4.3.1 Connection의 주요 메소드

Statement createStatement()

  • Statement 객체를 생성한다.

PreparedStatement prepareStatement(String sql)

  • Parameterized된 sql과 함께 PreparedStatement 객체를 생성한다.

DatabaseMetaData getMetaData()

  • DB의 여러가지 메타정보를 제공하는 DatbaseMetaData 객체를 제공한다.
  • DatbaseMetaData 객체를 통해서 DB vendor 가 명시하는 DB의 스펙이나 설명을 프로그래밍으로 얻을 수있다.

Session이기 때문에 Session단위에서 이루어지는 동작을 메소드로 제공한다. 이 메소드들은 Transaction 실습에서 구체적으로 다룬다.

  • setAutoCommit(boolean status)

    • autocommit 을 사용할지 여부. 기본 값은 true. Auto Commit을 사용한다면, executeXXX 종류의 함수를 실행하면 바로 commit이 발생해서 변경사항이 최종 테이블에 반영이 된다.

  • commit()
    • 지금까지 해당 Connection(Session)에서 수행한 변경사항들을 Database의 원본 테이블에 반영한다. commit이 성공하면 해당 connection(session)을 가지지 않은 client들도 해당 commit에 의해 변경된 데이터를 볼 수 있다.

  • rollback()
    • 지금까지 해당 Connection에서 수행한 변경사항들을 Database의 원본 테이블에 반영하지 않고, 해당 Connection(Session)에서 바라보는 상태도 원래대로 돌린다.

  • void setTransactionIsolation(int level)
    • transaction isolation level 을 직접 지정할 수 있다. parameter는 int를 받도록 되어있지만, Connection에 미리 정의된 상수(constant)를 사용한다.
      • Connection.TRANSACTION_READ_UNCOMMITTED
      • Connection.TRANSACTION_READ_COMMITTED
      • Connection.TRANSACTION_REPEATABLE_READ
      • Connection.TRANSACTION_SERIALIZABLE

  • close()
    • 연결을 즉시 끊는다. 실행중인 자원의 처리여부는 상관 없다.


4.4 Statement

Statement를 이용해서 실제 수행할 쿼리를 작성할 수 있다. 수행 결과를 ResultSet 객체로 받아온다. 정적인 쿼리를 작성한다.

4.4.1 Statement의 주요 메소드

ResultSet [executeQuery](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#executeQuery-java.lang.String-) (String sql)

  • SQL을 수행하고 ResultSet 을 받아온다. 결과를 받아오므로 SELECT 문을 쓸 때 사용한다.

int [executeUpdate](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#executeUpdate-java.lang.String-) (String sql)

  • INSERT, UPDATE, DELETE 또는 DDL 등 결과를 받아오지 않는 SQL을 작성한다.
  • return 값은 변경사항이 적용된 row 의 수이다. 결과가 없으면 0이다.

boolean [execute](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#execute-java.lang.String-) (String sql)

  • 여러개의 결과를 얻는 SQL을 수행한다.
  • 첫 번째 결과가 ResultSet 이면 true
    • [getResultSet](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#getResultSet--)() 함수를 호출해서 결과를 얻는다.
  • 첫 번째 결과가 ResultSet이 아니면(결과가 없거나, 결과가 업데이트 count인 경우) false를 리턴한다.
    • [getUpdateCount](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#getUpdateCount--)() 를 호출해서 결과를 얻는다.
      • -1 은 현재 값이 없거나, ResultSet 인 경우이다.
  • 첫 번째 결과를 후속 함수로 얻었으면, 그 다음 결과는 [getMoreResults](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#getMoreResults--)() 함수로 execute()의 리턴값과 같은 방식으로 해석한다.
  • 더 이상 가져올 결과가 없다는 것은 다음과 같이 확인한다.
((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))

int[] [executeBatch](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#executeBatch--) ()

  • addBatch(String sql) 로 쌓인 SQL을 배치로 수행한다. 모든 커맨드가 성공하면 update된 count 를 배열로 리턴한다.

void [addBatch](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#addBatch-java.lang.String-) (String sql)

  • 현재 Statement 객체에 배치로 실행할 SQL 명령어를 추가한다. 추가만 할 뿐, 실행하지는 않는다.

위 함수말고도 많은 기능을 지원한다. Statement 클래스에는 실제 DB와의 동작을 수행할 수 있는 함수들이 제공되므로 Statement 클래스의 Javadoc을 한 번 쯤은 정독해두는 것을 추천한다. Java에서 DB를 이용하는 거의 모든 기능을 이 기능을 토대로 만든다고 생각하면
된다.

4.4.2 예제: executeQuery

Main.java

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {
  public static void main(String[] args) {
    try {
      // here de-jdbc is database name, root is username and password is null. Fix them for your
      // database settings.
      Connection con =
          DriverManager.getConnection("jdbc:mysql://localhost:3306/de-jdbc", "root", null);
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("select * from product");
      while (rs.next()) {
        System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getDate(3) + " "
            + rs.getString(4) + " " + rs.getInt(5));
      }
      con.close();
    } catch (Exception e) {
      System.out.println(e);
    }
  }
}

4.4.3 예제: executeUpdate

Main.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Main {
  public static void main(String[] args) {
    try {
      // here de-jdbc is database name, root is username and password is null. Fix them for your
      // database settings.
      Connection con =
          DriverManager.getConnection("jdbc:mysql://localhost:3306/de-jdbc", "root", null);
      Statement stmt = con.createStatement();
      int result = stmt.executeUpdate("UPDATE product SET `price` = `price`+10000 where `id` = 1");
      System.out.println("result of update one record: " + result);
      int multiResult = stmt
          .executeUpdate("UPDATE product SET `price` = `price`-1000 where `name` like 'shoes%'");
      System.out.println("result of update multiple record: " + multiResult);
      int noResult =
          stmt.executeUpdate("UPDATE product SET `price` = `price`+10000 where `id` = 999999");
      System.out.println("result of update nothing: " + noResult);
      con.close();
    } catch (Exception e) {
      System.out.println(e);
    }
  }
}

4.4.4 예제: execute

Main.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
  public static void main(String[] args) {
    try {
      // here de-jdbc is database name, root is username and password is null. Fix them for your
      // database settings.
      Connection con =
          DriverManager.getConnection("jdbc:mysql://localhost:3306/de-jdbc", "root", null);
      Statement stmt = con.createStatement();
      boolean selectResult = stmt.execute(
          "select `id`, `name`, `updated_at`, `contents`, `price` from product where id between 1 and 5;");
      if (selectResult) {
        ResultSet resultSet = stmt.getResultSet();
        while (resultSet.next()) {
          printRs(resultSet);
        }
      }

      boolean updateResult =
          stmt.execute("UPDATE product SET `price` = `price`+10000 where `id` = 1;");
      if (!updateResult) {
        System.out.println("result of update: " + stmt.getUpdateCount());
      }

      con.close();
    } catch (Exception e) {
      System.out.println(e);
    }
  }

  private static void printRs(ResultSet rs) throws SQLException {
    System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getDate(3) + " "
        + rs.getString(4) + " " + rs.getInt(5));
  }
}

4.4.5 예제: addBatch & executeBatch

Main.java

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
  public static void main(String[] args) {
		try {
			//here de-jdbc is database name, root is username and password is null. Fix them for your database settings.
			Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/de-jdbc", "root", null);
			Statement stmt = con.createStatement();
			stmt.addBatch(
"select `id`, `name`, `updated_at`, `contents`, `price` from product where id between 1 and 5;");
			stmt.addBatch("UPDATE product SET `price` = `price`+10000 where `id` = 1;");
			try {
				int[] results = stmt.executeBatch();
			} catch(BatchUpdateException batchUpdateException({
System.out.println(batchUpdateException.getMessage());
			}

			stmt.addBatch("UPDATE product SET `price` = `price`+10000 where `id` = 1;");
			stmt.addBatch("UPDATE product SET `price` = `price`+10000 where `id` = 2;");
			stmt.addBatch("UPDATE product SET `price` = `price`+10000 where `id` between 3 and 5;");
			int[] results = stmt.executeBatch();

			for (int result : results) {
				if (result >= 0) {
					System.out.println("result of update: " + result);
				}
			}

			con.close();
		} catch (Exception e) {System.out.println(e);}
	}

  private static void printRs(ResultSet rs) throws SQLException {
    System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getDate(3) + " "
        + rs.getString(4) + " " + rs.getInt(5));
  }
}

4.5 ResultSet

ResultSet은 쿼리의 결과를 cursor를 이용해서 다룰 수 있도록 하는 객체이다.
ResultSet은 쿼리의 결과를 테이블 형태로 상정하고, 특정 row(행)을 가리키고 있는 cursor를 가진다. 쿼리의 결과가 단건일수도, 여러건
일수도 있다. 결과 전체를 리스트나 배열로 주면 되지 않냐고 생각할 수도 있다. 하지만, 쿼리 결과가 많다면 그 결과를 한 번에 어플리케이
션의 메모리에 모두 로드할 수 없을 수도 있기 때문에 cursor를 사용한다.

4.5.1 ResultSet의 Cursor의 사용

cursor는 방향성이 있다. 시작은 첫 번째 row의 이전을 가리키고 있다. 커서가 가리키는 내용은 업데이트가 불가능하다.
단, 설정으로 방향을 반대로 바꿀 수도 있고, 내용이 업데이트 가능하게 만들 수도 있다.
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

커서의 동작을 그림으로 표현하면 다음과 같다.

4.5.2 ResultSet의 주요 메소드

ResultSet의 주요함수는 다음과 같다. Javadoc
public boolean next()

  • cursor 를 현재보다 한 row 다음으로 보낸다.
    public boolean previous()
  • cursor 를 현재보다 한 row 이전으로 보낸다.
    public boolean first()
  • result set 의 첫번째로 cursor를 이동한다.
    public boolean last()
  • result set 의 마지막으로 cursor를 이동한다.
    public boolean absolute(int row)
  • result set 의 특정 row로 cursor를 이동한다.
    public boolean relative(int row)
  • 현재 cursor 위치로부터 정해진 순번의 row로 cursor를 이동한다. 양,음 모두 가능
    public int getInt(int columnIndex)
  • 현재 cursor 가 가리키고 있는 데이터 행의 주어진 column index에 해당하는 컬럼의 값을 int로 가져온다. (1 부터 시작)
    public int getInt(String columnName)
  • 현재 cursor 가 가리키고 있는 데이터 행의 주어진 column 이름에 해당하는 컬럼의 값을 int로 가져온다.
    public String getString(int columnIndex)
  • 현재 cursor 가 가리키고 있는 데이터 행의 주어진 column index에 해당하는 컬럼의 값을 String으로 가져온다.
    public String getString(String columnName)
  • 현재 cursor 가 가리키고 있는 데이터 행의 주어진 column 이름에 해당하는 컬럼의 값을 String으로 가져온다.
    public Blob getBlob(int columnIndex)
  • BLOB 은 binary (이진수) 형태의 대형 객체를 저장할 때 쓴다. 이미지, 동영상, 파일 등 현재 cursor 가 가리키고 있는 데이터 행의 주어진 column index에 해당하는 컬럼의 값을 Blob으로 가져온다.
    public Blob getBlob(String columnName)
  • 현재 cursor 가 가리키고 있는 데이터 행의 주어진 column name에 해당하는 컬럼의 값을 Blob으로 가져온다.
    public Clob getClob(int columnIndex)
  • CLOB 은 character (문자) 형태의 대형 객체를 저장할 때 쓴다. 대용량 텍스트/문서 파일, 대용량 파일
  • 현재 cursor 가 가리키고 있는 데이터 행의 주어진 column index에 해당하는 컬럼의 값을 Clob으로 가져온다.
    public Clob getClob(String columnName)
  • 현재 cursor 가 가리키고 있는 데이터 행의 주어진 column name에 해당하는 컬럼의 값을 Clob으로 가져온다.

ResultSet은 결과물을 데이터로 가져오려면 꼭 사용해야하는 인터페이스이다. 이것도 역시 ResultSet의Javadoc을 한 번 읽어보면 앞으로 JDBC를 이용해서 데이터를 가져올 때 어떤식으로 활용할 수 있을지 스스로 고민할 수 있을 것이다.

4.5.3 예제: ResultSet의 주요 메소드

Main.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
  public static void main(String[] args) {
    try {
      // here de-jdbc is database name, root is username and password is null. Fix them for your
      // database settings.
      Connection con =
          DriverManager.getConnection("jdbc:mysql://localhost:3306/de-jdbc", "root", null);
      Statement stmt =
          con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet rs = stmt.executeQuery(
          "select `id`, `name`, `updated_at`, `contents`, `price` from product where id between 1 and 5 order by id asc");

      // cursor init
      if (rs.next()) {
        // cursor 1
        printRs(rs);
      }
      if (rs.last()) {
        // cursor 5
        printRs(rs);
      }
      if (!rs.next()) {
        // end of result set
        try {
          printRs(rs);
        } catch (SQLException sqlException) {
          System.out.println(sqlException.getErrorCode() + ", " + sqlException.getMessage());
        }
      }
      rs.last();
      if (rs.previous()) {
        // cursor 4
        printRs(rs);
      }
      if (rs.absolute(2)) {
        // cursor 2
        printRs(rs);
      }
      if (rs.relative(2)) {
        // cursor 4
        printRs(rs);
      }
      if (rs.relative(-2)) {
        // cursor 2
        printRs(rs);
      }
      if (rs.first()) {
        // cursor 1
        printRs(rs);
      }

      if (!rs.previous()) {
        // Before start of result set
        try {
          printRs(rs);
        } catch (SQLException sqlException) {
          System.out.println(sqlException.getErrorCode() + ", " + sqlException.getMessage());
        }
      }
      if (!rs.previous()) {
        // Before start of result set
        try {
          printRs(rs);
        } catch (SQLException sqlException) {
          System.out.println(sqlException.getErrorCode() + ", " + sqlException.getMessage());
        }
      }
      con.close();
    } catch (Exception e) {
      System.out.println(e);
    }
  }

  private static void printRs(ResultSet rs) throws SQLException {
    System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getDate(3) + " "
        + rs.getString(4) + " " + rs.getInt(5));
  }
}

4.5.4 ResultSet 의 데이터를 POJO class로 매핑하기

자바는 객체지향 언어이다. 매번 코드로 SQL의 쿼리 결과의 몇 번째 값이 어떤 타입의 데이터이다는 것을 코드로 짜려면 시간도 오래걸릴 뿐더러, 작성자가 아닌 다른 사람이 코드를 읽기도 불편하다.

ResultSet 의 결과를 Java Object로 만들면 타입 안정성과 객체지향 프로그래밍의 장점을 살려서 프로그램을 간결하게 만들 수 있다.

Product.java

package org.de.jdbc.mapper;

import java.time.LocalDateTime;

public class Product {
	int id;
	String name;
	LocalDateTime updated_at;
	String contents;
	int price;

	public Product(int id, String name, LocalDateTime updated_at, String contents, int price) {
		this.id = id;
		this.name = name;
		this.updated_at = updated_at;
		this.contents = contents;
		this.price = price;
	}

	@Override
	public String toString() {
		return "Product{" + "id=" + id + ", name='" + name + '\'' + ", updated_at=" + updated_at
				+ ", contents='" + contents + '\'' + ", price=" + price + '}';
	}
}

ResultSetMapper.java

package org.de.jdbc.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class ResultSetMapper {
	public static Product create(ResultSet rs) throws SQLException {
		return new Product(rs.getInt(1), rs.getString(2), rs.getTimestamp(3).toLocalDateTime(),
				rs.getString(4), rs.getInt(5));
	}
}

Main.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.de.jdbc.mappe.Product;
import org.de.jdbc.mappe.ResultSetMapper;

public class Main {
	public static void main(String[] args) throws SQLException {
		// here de-jdbc is database name, root is username and password is null. Fix them for your
		// database settings.
		Connection con =
				DriverManager.getConnection("jdbc:mysql://localhost:3306/de-jdbc", "root", null);
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(
				"select `id`, `name`, `updated_at`, `contents`, `price` from product");
		while (rs.next()) {
			System.out.println(ResultSetMapper.create(rs));
		}
		con.close();
	}
}

하지만 이런 방식은, 내가 수행하는 SELECT문의 모든 결과를 별도의 Java Class로 선언해놓아야 가능하다. 그리고 데이터를 변환하는 함수도 SELECT문의 결과에 따라 각각 만들어놓아야 한다. 실제로 업무에서 활용하려면 이런 코드를 짜는데 시간이 굉장히 많이 들어간다. 그리고 ResultSet은 index별로 타입을 구분해서 함수를 호출해야하므로 쿼리나 코드의 변경에도 버그가 발생할 확률이 높아진다. 즉, 변경에 취약하다.

이런 ResultSet과 POJO class 를 매핑하는 코드를 매번 짜는 것이 불편해서 만들어진 기술(Library)을 ORM(Object-Relational Mapping)이다. 이름에서 추론할 수 있는 바와 같이, 객체와 Relational Model(관계형 데이터베이스 모델)을 매핑할 수 있는 기능을 가지고 있다. 하나의 테이블이 하나의 Java Class에 해당하고, FK와 같은 부가적인 기능은 함수로 제공한다.

4.6 PreparedStatement

Statement의 subinterface 이다. parameterized query를 사용할 때 쓴다. 변수를 사용할 수 있어서 Statement 종류 중에 가장 활용도가 높다.

여러 종류의 쿼리를 사용하지만, 컴파일은 한 번만 하면 되므로 성능상 이점이 있다.

쿼리에서 parameterized 하고 싶은 부분을 ? (물음표)로 작성하고, 해당 부분에 들어갈 파라미터는 PreparedStatement 의 함수를 이용해서 세팅한다.

  • 예 "insert into table value(?, ?)"

4.6.1 PreparedStatement의 주요 메소드

PreparedStatement에는 Statement의 인터페이스를 모두 구현하고, 아래와 같은 함수들이 추가되었다. Javadoc을 보면 더 많은 함수를 확인할 수 있다.

public void setInt(int paramIndex, int value)

  • paramIndex 자리에 있는 파라미터에 int 값으로 세팅한다.
    public void setString(int paramIndex, String value)
  • paramIndex 자리에 있는 파라미터에 String 값으로 세팅한다.
    public void setFloat(int paramIndex, float value)
  • paramIndex 자리에 있는 파라미터에 float 값으로 세팅한다.
    public void setDouble(int paramIndex, double value)
  • paramIndex 자리에 있는 파라미터에 double 값으로 세팅한다.

4.6.2 예제: PreparedStatement

Product.java

package org.de.jdbc.mapper;

import java.time.LocalDateTime;

public class Product {
	int id;
	String name;
	LocalDateTime updated_at;
	String contents;
	int price;

	public Product(int id, String name, LocalDateTime updated_at, String contents, int price) {
		this.id = id;
		this.name = name;
		this.updated_at = updated_at;
		this.contents = contents;
		this.price = price;
	}

	public int getId() {
		return id;
	}

	public String getName() {
		return name;
	}

	public LocalDateTime getUpdated_at() {
		return updated_at;
	}

	public void setUpdated_at(LocalDateTime updated_at) {
		this.updated_at = updated_at;
	}

	public String getContents() {
		return contents;
	}

	public int getPrice() {
		return price;
	}

	public void setPrice(int price) {
		this.price = price;
	}

	@Override
	public String toString() {
		return "Product{" + "id=" + id + ", name='" + name + '\'' + ", updated_at=" + updated_at
				+ ", contents='" + contents + '\'' + ", price=" + price + '}';
	}
}

ResultSetMapper

package org.de.jdbc.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class ResultSetMapper {

	public static Product create(ResultSet rs) throws SQLException {
		return new Product(rs.getInt(1), rs.getString(2), rs.getTimestamp(3).toLocalDateTime(),
				rs.getString(4), rs.getInt(5));
	}

	public static void printRs(ResultSet rs) throws SQLException {
		System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getDate(3) + " "
				+ rs.getString(4) + " " + rs.getInt(5));

	}
}

Main.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import org.de.jdbc.mapper.Product; // can be different package name
import org.de.jdbc.mapper.ResultSetMapper; // can be different package name

public class Main {
	public static void main(String[] args) throws SQLException {
		Connection con =
				DriverManager.getConnection("jdbc:mysql://localhost:3306/de-jdbc", "root", null);
		PreparedStatement psUpdate =
				con.prepareStatement("UPDATE product SET `price` = price+? where `id` = ?;");
		psUpdate.setInt(1, 10000); // price + a
		psUpdate.setInt(2, 1); // id
		int updatedResult = psUpdate.executeUpdate();
		System.out.println("result of update: " + updatedResult);
		PreparedStatement psSelect = con.prepareStatement(
				"select `id`, `name`, `updated_at`, `contents`, `price` from product where id between ? and ?;");
		psSelect.setInt(1, 1);
		psSelect.setInt(2, 5);
		ResultSet rs = psSelect.executeQuery();
		while (rs.next()) {
			ResultSetMapper.printRs(rs);
		}
		psSelect.setInt(1, 6);
		psSelect.setInt(2, 10);
		ResultSet rs2 = psSelect.executeQuery();
		List<Product> productList = new ArrayList<>();
		while (rs2.next()) {
			productList.add(ResultSetMapper.create(rs2));
		}
		PreparedStatement psUpdateProduct = con.prepareStatement(
				"UPDATE product SET `id` = ?, `name` = ?, `updated_at` = ?, `contents` = ?, price = ? where id = ?");
		for (Product product : productList) {
			product.setPrice(product.getPrice() - 1000);
			product.setUpdated_at(LocalDateTime.now());
			psUpdateProduct.setInt(1, product.getId());
			psUpdateProduct.setString(2, product.getName());
			psUpdateProduct.setTimestamp(3, Timestamp.valueOf(product.getUpdated_at()));
			psUpdateProduct.setString(4, product.getContents());
			psUpdateProduct.setInt(5, product.getPrice());
			psUpdateProduct.setInt(6, product.getId());
			psUpdateProduct.addBatch();
			psUpdateProduct.clearParameters();
		}

		int[] results = psUpdateProduct.executeBatch();
		for (int result : results) {
			System.out.println("result of update: " + result);
		}
	}
}

4.7 CallableStatement

4.7.1 Stored Procedure, Function 의 용도와 사례

DB에는 SQL 말고도 stored procedure 또는 function이 있다. 이것들은 다음과 같은 경우에 사용한다.

  • SQL은 아닌데 자주 사용하는 여러 동작의 세트
  • SQL로 한다면 여러번 I/O를 해야하는데, 한 번에 DB에서 모두 처리한 뒤 최종 결과만 빨리 받고 싶을때
    • 보통 Application 서버보다, DB서버의 하드웨어 스펙이 월등히 좋으니까 이런 경우에 사용한다~

Stored Procedure는 SQL을 여러 개 써서 구성할 수 있다는 게 특징이고,
Function은 그 자체로도 호출할 수 있지만, SQL문 내에서도 호출할 수 있다는 점이 Stored Procedure와의 차이점이다.

Function의 사례

  • 복잡한 수학적 연산을 자동화하는 것을 Function으로 만들어 놓고 재사용한다.
  • 문자열 변환, 이미 입력된 데이터의 인코딩 오류 해결 등을 Function으로 만들어 놓고 재사용한다.

Stored Producedure의 사례

  • 여러 SQL문으로 수행되어야하는 데이터 집계
  • 복제 테이블을 만들거나 유효 기간이 지난 데이터를 삭제하는 등의 작업을 자동화 하고 싶을 때
  • DBA가 DB를 관리하기 위한 기능(용량 모니터링, 감사 자료 추출 등)을 의미 단위로 만들어 놓고 재사용 하고 싶을 때

JDBC에서는 이런 stored procedure 또는 function은 CallableStatement 클래스를 이용해서 다룬다.

4.7.2 Custom Procedure 정의하기

PROCEDURE 정의하기

DELIMITER //sql
CREATE PROCEDURE discount_price(IN _id int unsigned, IN percent int, OUT discounted_price int)
BEGIN
	update product set price = (price - (price * (percent/100))) where `id` = _id;
	select price into discounted_price from product where `id` = _id;
END //
DELIMITER ;

PRDECURE 삭제하기

DROP PROCEDURE discount_price

PROCEDURE 호출하기

call discount_price(1, 10, @discounted_price);
select @discounted_price;

4.7.3 CallableStatement로 procedure 호출하기

  1. Connection.prepareCall(String statement) : CallableStatement를 만든다. 파라미터는 ?로 처리한다.
  2. CallalbleStatement 를 사용하는 방법은 PreparedStatement를 사용하는 방법과 같은 방식으로 index/label 별로 타입에 맞는 값을 세팅할 수 있다..
  3. 단, out parameter는 CallalbleStatement.registerOutParameter(int index 또는 String label, java.sql.Type type) 로 세팅해야한다.
  4. execute() 함수로 실행한다.
  5. 결과는 excute() 함수를 해석하는 방식과 같다.
  6. 단, out parameter는 CallalbleStatement 객체에 바로 getXXX 함수를 통해서 얻을 수 있다.

4.7.4 예제: CallableStatement로 procedure 호출하기

Main.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class Main {
	public static void main(String[] args) throws SQLException {
		Connection con =
				DriverManager.getConnection("jdbc:mysql://localhost:3306/de-jdbc", "root", null);
		CallableStatement stmtProcedureCall = con.prepareCall("call discount_price(?, ?, ?);");
		stmtProcedureCall.setInt(1, 1);
		stmtProcedureCall.setInt(2, 10);
		stmtProcedureCall.registerOutParameter(3, Types.INTEGER);
		boolean result = stmtProcedureCall.execute();
		System.out.println("result: " + result);
		System.out.println("param: " + stmtProcedureCall.getInt(3));
		if (!result) {
			System.out.println("update count: " + stmtProcedureCall.getUpdateCount());
		}
	}
}

4.7.5 Function 정의하기

FUNCTION 정의하기

CREATE FUNCTION add_event_prefix (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('[특별 할인 이벤트] , ',s);

예를 들어 어떤 상품의 이름에 ~특가 블랙프라이데이 특가 이런 경우 갑자기 모든 상품 앞에 ~특가 n%할인, 이런 것들이 붙을 수도 있는데, 상품등록한 사람이 한 것일 수도 있는데, 플랫폼 쇼핑폴 차원에서 프로므션으로 붙여주는 경우가 있다. 그런 경우 원래 작성자가 사용한 상품의 이름을 바꾸지 않고, 강조 문구만 추가하고자 할 때, 해당함수 사용하면 유용함.

FUNCTION 삭제하기

DROP FUNCTION add_event_prefix

FUNCTION 호출하기

SELECT `id`, add_event_prefix(`name`) as `name`, `price` from product where id = 1;

4.7.6 CallableStatement로 Function 호출하기

Function은 CallableStatemnet를 이용해서 호출할 수도 있고, 다른 query statement 객체의 SQL 문에서 곧바로 사용할 수도 있다.
CallableStatement를 이용하는 방식의 위 Procedure Call 할 때 사용한 방식과 같다.

4.7.7 예제: CallableStatement로 Function 호출하기

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import org.de.jdbc.mapper.ResultSetMapper;

public class Main {
	public static void main(String[] args) throws SQLException {
		Connection con =
				DriverManager.getConnection("jdbc:mysql://localhost:3306/de-jdbc", "root", null);
		System.out.println("===== Direct Function Call =====");
		CallableStatement stmtFunctionCall = con.prepareCall("{?= call add_event_prefix(?)}");
		String originalContent = "original";
		System.out.println("original content: " + originalContent);
		stmtFunctionCall.setString(2, originalContent);
		stmtFunctionCall.registerOutParameter(1, Types.VARCHAR);
		boolean result2 = stmtFunctionCall.execute();
		System.out.println(result2);
		System.out.println("after prefix: " + stmtFunctionCall.getString(1));
		System.out.println("===== Function Call in Query =====");
		Statement statement = con.createStatement();
		ResultSet rs = statement.executeQuery(
				"SELECT `id`, add_event_prefix(`name`) as `name`, `updated_at`, `contents`, `price` from product where id = 1;");
		while (rs.next()) {
			ResultSetMapper.printRs(rs);
		}
	}
}
profile
Hyunjun Kim
Data Analytics Engineer 가 되
이전 포스트

[RDBMS / JDBC를 이용해 데이터 처리하기] JDBC

다음 포스트

[RDBMS / JDBC를 이용해 데이터 처리하기] Transaction

0개의 댓글