Java Category/Java

[Java] 데이터베이스에 저장(쓰기, 수정, 삭제) 및 읽기

ReBugs 2023. 8. 18.

이 게시글은 이것이 자바다(저자 : 신용권, 임경균)의 책과 동영상 강의를 참고하여 개인적으로 정리하는 글임을 알립니다.

데이터 베이스 구성

Account Table

Boards

Users

데이터 저장

데이터 쓰기(INSERT문)

User 테이블

users 테이블에 새로운 사용자 정보를 저장하는 INSERT 문은 아래와 같다.

INSERT INTO users (userid, username, userpassword, userage, useremail)
VALUES ('winter', '한겨울', '12345', '25', 'winter@mycompany.com')

값을 ?로 대체한 매개변수화된 INSERT 문으로 변경하면 아래와 같다.

INSERT INTO users (userid, username, userpassword, userage, useremail)
VALUES (?, ?, ?, ?, ?)

그리고 INSERT 문을 String 타입 변수 sql에 문자열로 대입한다.

String sql = new StringBuilder()
        .append("INSERT INTO users (userid, username, userpassword, userage, useremail) ")//끝에 띄어쓰기를 꼭 해줘야한다.
        .append("VALUES (?, ?, ?, ?, ?)")
        .toString();
		
//또는

String sql = "INSERT INTO users(userid, username, userpassword, userage, useremail) " + "VALUES(?, ?, ?, ?, ?)";

매개변수화된 SQL 문을 실행하려면 PreparedStatement가 필요하다.

아래와 같이 Connection의 prepareStatement() 메소드로부터 PreparedStatement를 얻는다.

PreparedStatement pstmt = conn.prepareStatement(sql);

그리고 ?에 들어갈 값을 지정해주는데, ?는 순서에 따라 1번부터 번호가 부여된다.

값의 타입에 따라 setter 메소드를 선택한 후 첫 번째 매개값에는 ?순번, 두 번째 매개값에는 값을 지정한다.

//userid, username, userpassword, userage, useremail
//VALUES (?, ?, ?, ?, ?)
pstmt.setString(1, "winter2");
pstmt.setString(2, "한겨울");
pstmt.setString(3, "12345");
pstmt.setInt(4, 25);
pstmt.setString(5, "winter@mycompany.com");

값을 지정한 후 executeUpdate() 메소드를 호출하면 SQL 문이 실행되면서 users 테이블에 1개의 행이 저장된다.

executeUpdate() 메소드가 리턴하는 값은 실행된 명령문 수인데, 정상적으로 실행되었을 경우 1을 리턴한다.

int rows = pstmt.executeUpdate();

PreparedStatement를 더 이상 사용하지 않을 경우에는 close() 메소드를 호출해서 메모리를 해제한다.

pstmt.close();

사용 예제(Oracle)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UserInsertExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521/orcl", 
				"java", 
				"oracle"
			);	
			
			//매개변수화된 SQL 문 작성
			String sql = "" +
				"INSERT INTO users(userid, username, userpassword, userage, useremail)" + "VALUES(?, ?, ?, ?, ?)";
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "winter2");
			pstmt.setString(2, "한겨울");
			pstmt.setString(3, "12345");
			pstmt.setInt(4, 25);
			pstmt.setString(5, "winter@mycompany.com");
			
			//SQL 문 실행
			int rows = pstmt.executeUpdate();
			System.out.println("저장된 행 수: " + rows);
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}
/*
저장된 행 수: 1
*/

정상적으로 DB에 추가된 것을 볼 수 있다.

사용 예제(MySQL)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UserInsertExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/thisisjava", 
				"java", 
				"mysql"
			);	
			
			//매개변수화된 SQL문 작성
			String sql = "" +
				"INSERT INTO users (userid, username, userpassword, userage, useremail) " +
				"VALUES (?, ?, ?, ?, ?)";
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "winter");
			pstmt.setString(2, "한겨울");
			pstmt.setString(3, "12345");
			pstmt.setInt(4, 25);
			pstmt.setString(5, "winter@mycompany.com");
			
			//SQL문 실행
			int rows = pstmt.executeUpdate();
			System.out.println("저장된 행 수: " + rows);
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}

boards 테이블

새로운 게시물 정보를 저장하는 INSERT 문은 아래와 같다.

SEQ_BNO.NEXTVAL은 SEQ_BNO 시퀀스에서 가져올 번호이고, SYSDATE는 현재 시간이다.

SEQ_BNO 시퀀스
SEQ_BNO라는 이름을 가진 시퀀스이다.
중간에 게시글이 삭제되면 누락된 인덱스 없이 자동으로 순서를 잡아준다.

INSERT INTO boards (bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata)
VALUES (SEQ_BNO.NEXTVAL, '눈 오는 날', '함박눈이 내려요', 'winter', SYSDATE, 'snow.jpg', 'binaryData')

SEQ_BNO.NEXTVAL와 SYSDATE를 제외하고 나머지는 ?로 대체한 매개변수화된 INSERT 문으로 만들고 String 타입 변수 sql에 저장한다.

String sql = "" +
    "INSERT INTO boards (bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata) " +
    "VALUES (SEQ_BNO.NEXTVAL, ?, ?, ?, SYSDATE, ?, ?)";

매개변수화된 INSERT 문을 실행하기 위해 아래와 같이 prepareStatement() 메소드로부터 PreparedStatement를 얻는데, 두 번째 매개값은 INSERT 문이 실행된 후 가져올 컬럼 값이다.

PreparedStatement pstmt = conn.prepareStatement(sql, new String[] {"bno"});

위 코드는 bno 컬럼 값을 가져온다.

SQL문이 실행되기 전까지는 SEQ_BNO.NEXTVAL로 얻은 번호를 모르기 때문에 SQL 문이 실행된 후에 bno 컬럼에 실제로 저장된 값을 얻는 것이다.

bfiledata 컬럼은 바이너리 타입(blob)이므로 ?에 값을 지정하려면 setBinaryStream(), setBlob(), setByte() 메소드 중 하나를 이용해야 한다.

pstmt.setString(1, "눈오는 날");
pstmt.setString(2, "함박눈이 내려요.");
pstmt.setString(3, "winter");
pstmt.setString(4, "snow.jpg");
pstmt.setBlob(5, new FileInputStream("src/ch20/oracle/sec06/snow.jpg"));

INSERT 문을 실행하고 저장된 bno 값을 얻는 방법은 아래와 같다.

게시물 정보가 저장되었을 경우(row가 1일 경우) getGeneratedKeys() 메소드로 resultSet을 얻고, getInt() 메소드로 bno를 얻는다.

//{"bno"}
int rows = pstmt.executeUpdate();
if(rows == 1) {
    ResultSet rs = pstmt.getGeneratedKeys();
    if(rs.next()) {
        int bno = rs.getInt(1);
    }
    rs.close();
}

만약 btitle을 얻고싶다면 아래처럼 하면 된다.

PreparedStatement pstmt = conn.prepareStatement(sql, new String[] {"bno", "btitle"});
int rows = pstmt.executeUpdate();
if(rows == 1) {
    ResultSet rs = pstmt.getGeneratedKeys();
    if(rs.next()) {
        string btitle = rs.getInt(2);
    }
    rs.close();
}

실행 예제(oracle)

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BoardWithFileInsertExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521/orcl", 
				"java", 
				"oracle"
			);	
			
			//매개변수화된 SQL 문 작성
			String sql = "" +
				"INSERT INTO boards (bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata) " +
				"VALUES (SEQ_BNO.NEXTVAL, ?, ?, ?, SYSDATE, ?, ?)";
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql, new String[] {"bno"});
			pstmt.setString(1, "눈오는 날");
			pstmt.setString(2, "함박눈이 내려요.");
			pstmt.setString(3, "winter");
			pstmt.setString(4, "snow.jpg");
			pstmt.setBlob(5, new FileInputStream("src/ch20/oracle/sec06/snow.jpg"));
			
			//SQL 문 실행
			int rows = pstmt.executeUpdate();
			System.out.println("저장된 행 수: " + rows);
			
			//bno 값 얻기
			if(rows == 1) {
				ResultSet rs = pstmt.getGeneratedKeys();
				if(rs.next()) {
					int bno = rs.getInt(1);
					System.out.println("저장된 bno: " + bno);
				}
				rs.close();
			}
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}
/*
저장된 행 수: 1
저장된 bno: 1
*/

정상적으로 DB에 저장된 것을 볼 수 있다.

실행 예제(MySQL)

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BoardInsertExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/thisisjava", 
				"java", 
				"mysql"
			);
			
			//매개변수화된 SQL문 작성
			String sql = "" +
				"INSERT INTO boards (btitle, bcontent, bwriter, bdate, bfilename, bfiledata) " +
				"VALUES (?, ?, ?, now(), ?, ?)";
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(
					sql, Statement.RETURN_GENERATED_KEYS);
			pstmt.setString(1, "눈오는 날");
			pstmt.setString(2, "함박눈이 내려요.");
			pstmt.setString(3, "winter");
			pstmt.setString(4, "snow.jpg");
			pstmt.setBlob(5, new FileInputStream("src/ch20/mysql/sec06/snow.jpg"));
			
			//SQL문 실행
			int rows = pstmt.executeUpdate();
			System.out.println("저장된 행 수: " + rows);
			
			//bno 값 얻기
			if(rows == 1) {
				ResultSet rs = pstmt.getGeneratedKeys();
				if(rs.next()) {
					int bno = rs.getInt(1);
					System.out.println("저장된 bno: " + bno);
				}
				rs.close();
			}
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}

데이터 수정

boards 테이블에 저장된 게시물 중에서 bno가 1인 게시물의 btitle, bcontent, bfilename, bfiledata를 변경하는 SQL문은 아래와 같다.

UPDATE boards SET
btitle = '눈사람',
bcontent = '눈으로 만든 사람',
bfilename = 'snowman.jpg',
bfiledata = binaryData
WHERE bno = 1

값을 ?로 대체한 매개변수화된 UPDATE 문으로 변경한다.

UPDATE boards SET
btitle = ?,
bcontent = ?,
bfilename = ?,
bfiledata = ?
WHERE bno = ?

String 타입 변수 sql에 매개변수화된 UPDATE 문을 저장한다.

String sql = new StringBuilder()
    .append("UPDATE boards SET ")
    .append("btitle=?, ")
    .append("bcontent=?, ")
    .append("bfilename=?, ")
    .append("bfiledata=? ")
    .append("WHERE bno=?")
    .toString();

매개변수화된 UPDATE 문을 실행하기 위해 아래와 같이 prepareStatement() 메소드로부터 PreparedStatement를 얻고, ?에 해당하는 값을 지정한다.

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "눈사람");
pstmt.setString(2, "눈으로 만든 사람");
pstmt.setString(3, "snowman.jpg");
pstmt.setBlob(4, new FileInputStream("src/ch20/mysql/sec07/snowman.jpg"));
pstmt.setInt(5, 3);

값을 모두 지정하였다면 UPDATE 문을 실행하기 위해 executeUpdate() 메소드를 호출한다.

성공적으로 실행되면 수정된 행의 수가 리턴된다.

int rows = pstmt.executeUpdate();

실행 예제(Oracle)

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BoardUpdateExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521/orcl", 
				"java", 
				"oracle"
			);	
			
			//매개변수화된 SQL 문 작성
			String sql = new StringBuilder()
					.append("UPDATE boards SET ")
					.append("btitle=?, ")
					.append("bcontent=?, ")
					.append("bfilename=?, ")
					.append("bfiledata=? ")
					.append("WHERE bno=?")
					.toString();
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "눈사람");
			pstmt.setString(2, "눈으로 만든 사람");
			pstmt.setString(3, "snowman.jpg");
			pstmt.setBlob(4, new FileInputStream("src/ch20/oracle/sec07/snowman.jpg"));
			pstmt.setInt(5, 3);  //boards 테이블에 있는 게시물 번호(bno) 지정
			
			//SQL 문 실행
			int rows = pstmt.executeUpdate();
			System.out.println("수정된 행 수: " + rows);
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}

실행 예제(MySQL)

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BoardUpdateExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/thisisjava", 
				"java", 
				"mysql"
			);
			
			//매개변수화된 SQL문 작성
			String sql = new StringBuilder()
					.append("UPDATE boards SET ")
					.append("btitle=?, ")
					.append("bcontent=?, ")
					.append("bfilename=?, ")
					.append("bfiledata=? ")
					.append("WHERE bno=?")
					.toString();
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "눈사람");
			pstmt.setString(2, "눈으로 만든 사람");
			pstmt.setString(3, "snowman.jpg");
			pstmt.setBlob(4, new FileInputStream("src/ch20/mysql/sec07/snowman.jpg"));
			pstmt.setInt(5, 3);  //boards 테이블에 있는 게시물 번호(bno) 지정
			
			//SQL문 실행
			int rows = pstmt.executeUpdate();
			System.out.println("수정된 행 수: " + rows);
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}

데이터 삭제

boards 테이블에서 bwriter가 winter인 모든 게시물을 삭제하는 DELETE 문은 아래와 같다.

DELETE FROM boards WHERE bwriter = 'winter'

조건절의 값을 ?로 대체한 매개변수화된 DELETE 문으로 변경한다.

String sql = "DELETE FROM boards WHERE bwriter=?";

매개변수화된 DLELTE 문을 실행하기 위해 아래와 같이 prepareStatement() 메소드로부터 PreparedStatement를 얻고 ?에 값을 지정한 후, executeUpdate로 SQL문을 실행한다.

리턴 값은 삭제된 행의 수이다.

String sql = "DELETE FROM boards WHERE bwriter=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "winter");
int rows = pstmt.executeUpdate();

실행 예제(Oracle)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BoardDeleteExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521/orcl", 
				"java", 
				"oracle"
			);	
			
			//매개변수화된 SQL 문 작성
			String sql = "DELETE FROM boards WHERE bwriter=?";
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "winter");
			
			//SQL 문 실행
			int rows = pstmt.executeUpdate();
			System.out.println("삭제된 행 수: " + rows);
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}

실행 예제(MySQL)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BoardDeleteExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/thisisjava", 
				"java", 
				"mysql"
			);
			
			//매개변수화된 SQL문 작성
			String sql = "DELETE FROM boards WHERE bwriter=?";
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "winter");
			
			//SQL문 실행
			int rows = pstmt.executeUpdate();
			System.out.println("삭제된 행 수: " + rows);
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}

데이터 읽기

DB에서 데이터를 가져오는 메소드

데이터를 가져오는 SELECT 문일 경우에는 executeQuery() 메소드를 호출해야 한다.

executeQuery() 메소드는 가져온 데이터를 ResultSet에 저장하고 리턴한다.

ResultSet rs = pstmt.executeQuery();

ResultSet 구조

ResultSet은 SELECT 문에 기술된 컬럼으로 구성된 행(row)의 집합이다.

예를 들어 아래의 SELECT문은 userid, username, userage 컬럼으로 구성된 ResultSet을 리턴한다.

SELECT userid, username, userage FROM user

위의 SELECT 문이 가져온 데이터 행이 4개라면 ResultSet의 내부 구조는 아래와 같다.

ResultSet의 특정인 커서가 있는 행의 데이터만 읽을 수 있다는 것이다.

커서는 행을 가리키는 포인터를 말한다.

ResultSet에는 실제 가져온 데이터 행의 앞과 뒤에 beforeFirst 행과 afterLast 행이 붙는데, 최초 커서는 beforeFirst를 가리킨다.

따라서 첫 번째 데이터 행인 first 행을 읽으려면 커서를 이동시켜야 한다.

커서를 한 칸 아래로 이동시킬 땐, next() 메소드를 사용한다.

booleat result = rs.next();

next() 메소드는 이동한 행에 데이터가 있으면 true를, 없으면 false를 리턴한다.

즉, last 행까지는 true를 리턴하고, afterLast 행으로 이동하면 false를 리턴한다.

1개의 데이터 행만 가져올 경우 if문 조건식에 next()으로 처리하고, 모든 데이터를 가져올 때는 while문 조건식에 next()를 이용한다.

커서를 자유자재로 이동하기
PreparedStatement pstmt = conn.prepareStatement(sql);
위 코드의 매개값에는 두 번째 매개값과 세 번째 매개값에 자동으로 노란색으로 칠한 매개값이 들어가 있다.
두 번째 매개값 : TYPE_FORWORD_ONLY
세 번째 매개값 : CONCUR_READ_ONLY
즉, 커서가 뒤로 이동할 수 없고 오직 앞으로만 이동 가능하며, 읽기 전용이라는 뜻이다.

두 번째 매개값과 세 번째 매개값에 초록색으로 칠한 매개값을 넣어주면 ResultSet 메소드를 통해 커서를 자유자재로 이동할 수 있고, 값 수정도 가능하다.
PreparedStatement pstmt = conn.prepareStatement(sql, 
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

SELECT 문에 따라 ResultSet에는 많은 데이터 행이 저장될 수 있기 때문에 ResultSet을 더 이상 사용하지 않는다면 close() 메소드를 호출해서 사용한 메모리를 해제하는 것이 좋다.

rs.close();

데이터 행 읽기

커서가 있는 데이터 행에서 각 컬럼의 값은 Getter 메소드로 읽을 수 있다.

컬럼의 데이터 타입에 따라 getXxx() 메소드가 사용되며, 매개값으로 컬럼의 이름 또는 컬럼 순번을 줄 수 있다.

ResultSet에서 컬럼 순번은 1부터 시작하기 때문에 userid = 1, username = 2, userage = 3이 된다.

만약 SELECT 문에 연산식이나 함수 호출이 포함되어 있다면 컬럼 이름 대신에 컬럼 순번으로 읽어야 한다.
예를 들어 아래와 같은 SELECT 문에서 userage -1 연산식이 사용되면 컬럼 순번으로만 읽을 수 있다.
userage -1은 컬럼명이 아니기 때문이다.
(userage -1) as userage와 같이 별명이 있다면 별명이 컬럼 이름이 된다.

데이터 추출

users 테이블

어떤 조건에 만족하는 데이터만 가져오려면 WHERE 에 조건을 명시한다.

예를 들어, userid가 winter인 사용자 정보를 가져오는 SELECT 문은 아래와 같다.

SELECT userid, username, userpassword, userage, useremail
FROM users
WHERE userid = 'winter';

조건절의 값을 ?로 대체한 매개변수화된 SQL 문을 String 타입 변수 sql에 대입한다.

String sql = "" +
    "SELECT userid, username, userpassword, userage, useremail " +
    "FROM users " +
    "WHERE userid=?";

매개변수화된 SELECT 문을 실행하기 위해 아래와 같이 prepareStatement() 메소드로부터 PreparedStatement를 얻고, ?에 값을 지정한다.

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "winter");

아래의 코드에서 User클래스는 DTO이다.

관련 데이터를 묶어서 하나의 객체로 즉, DTO로 만들어서 수신과 전송을 한다는 뜻이다.

사용 예제(Oracle)

User.java

public class User {
	private String userId;
	private String userName;
	private String userPassword;
	private int userAge;
	private String userEmail;
	public String getUserId() {
		return userId;
	}
	public void setUserId(String userId) {
		this.userId = userId;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getUserPassword() {
		return userPassword;
	}
	public void setUserPassword(String userPassword) {
		this.userPassword = userPassword;
	}
	public int getUserAge() {
		return userAge;
	}
	public void setUserAge(int userAge) {
		this.userAge = userAge;
	}
	public String getUserEmail() {
		return userEmail;
	}
	public void setUserEmail(String userEmail) {
		this.userEmail = userEmail;
	}
	@Override
	public String toString() {
		return "User [userId=" + userId + ", userName=" + userName + ", userPassword=" + userPassword + ", userAge="
				+ userAge + ", userEmail=" + userEmail + "]";
	}
}

UserSelectExample.java

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

public class UserSelectExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521/orcl", 
				"java", 
				"oracle"
			);	
			
			//매개변수화된 SQL 문 작성
			String sql = "" +
				"SELECT userid, username, userpassword, userage, useremail " +
				"FROM users " +
				"WHERE userid=?";
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "winter");
			
			//SQL 문 실행 후, ResultSet을 통해 데이터 읽기
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()) {						//1개의 데이터 행을 가져왔을 경우
				User user = new User();				
				user.setUserId(rs.getString("userid"));
				user.setUserName(rs.getString("username"));
				user.setUserPassword(rs.getString("userpassword"));
				user.setUserAge(rs.getInt(4));       //컬럼 순번을 이용
				user.setUserEmail(rs.getString(5));  //컬럼 순번을 이용				
				System.out.println(user);
			} else {                           //데이터 행을 가져오지 않았을 경우
				System.out.println("사용자 아이디가 존재하지 않음");
			}
			rs.close();
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}
/*
User [userId=winter, userName=한겨울, userPassword=12345, userAge=25, userEmail=winter@mycompany.com]
*/

사용 예제(MySQL)

User.java

public class User {
	private String userId;
	private String userName;
	private String userPassword;
	private int userAge;
	private String userEmail;
	public String getUserId() {
		return userId;
	}
	public void setUserId(String userId) {
		this.userId = userId;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getUserPassword() {
		return userPassword;
	}
	public void setUserPassword(String userPassword) {
		this.userPassword = userPassword;
	}
	public int getUserAge() {
		return userAge;
	}
	public void setUserAge(int userAge) {
		this.userAge = userAge;
	}
	public String getUserEmail() {
		return userEmail;
	}
	public void setUserEmail(String userEmail) {
		this.userEmail = userEmail;
	}
	@Override
	public String toString() {
		return "User [userId=" + userId + ", userName=" + userName + ", userPassword=" + userPassword + ", userAge="
				+ userAge + ", userEmail=" + userEmail + "]";
	}
}

UserSelectExample.java

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

public class UserSelectExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/thisisjava", 
				"java", 
				"mysql"
			);	
			
			//매개변수화된 SQL문 작성
			String sql = "" +
				"SELECT userid, username, userpassword, userage, useremail " +
				"FROM users " +
				"WHERE userid=?";
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "winter");
			
			//SQL문 실행 후, ResultSet을 통해 데이터 읽기
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()) {						//1개의 데이터 행을 가져왔을 경우
				User user = new User();				
				user.setUserId(rs.getString("userid"));
				user.setUserName(rs.getString("username"));
				user.setUserPassword(rs.getString("userpassword"));
				user.setUserAge(rs.getInt(4));       //컬럼 순번을 이용
				user.setUserEmail(rs.getString(5));  //컬럼 순번을 이용				
				System.out.println(user);
			} else {                           //데이터 행을 가져오지 않았을 경우
				System.out.println("사용자 아이디가 존재하지 않음");
			}
			rs.close();
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}

boards 테이블

현재 boards 테이블의 상태는 아래와 같다.

bwriter가 winter인 게시물 정보를 가져오는 SELECT 문은 아래와 같다.

SELECT bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata
FROM boards
WHERE bwriter = 'winter';

조건절의 값을 ?로 대체한 매개변수화된 SELECT 문을 String 타입 변수 sql에 대입한다.

String sql = "" +
    "SELECT bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata " +
    "FROM boards " +
    "WHERE bwriter=?";

매개변수화된 SELECT 문을 실행하기 위해 아래와 같이 prepareStatement() 메소드로부터 PreparedStatement를 얻고, ?에 값을 지정한다.

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "winter");

bfiledata는 Blob 객체이므로 콘솔에 출력하면 의미 없는 타입 정보만 출력된다.

Blob 객체에 저장된 바이너리 데이터를 얻기 위해서는 아래와 같이 입력 스트림 또는 바이트 배열을 얻어내야 한다.

입력스트림을 사용할 때 : 파일을 저장할 때 또는 전송할 때
바이트 배열을 사용할 때 : UI프로그램 등에서 화면상에서 그림을 그려야할 때

아래의 코드는 Blob 객체에서 InputStream을 얻고, 읽은 바이트를 파일로 저장하는 방법을 보여준다.

InputStream is = blob.getBinaryStream();
OutputStream os = new FileOutputStream("C:/Temp/" + board.getBfilename());
is.transferTo(os);
os.flush();
os.close();
is.close();

transferTo()메소드
기존에는 입력스트림을 출력스트림으로 전달하려면 아래와 같은 코드를 작성해야 했다.
byte[] data = new byte[1024];
while(true)
{
    int num = is.read(data);
    if (num == -1) break;
    os.write(data, 0, num);
}
하지만
transfer()메소드를 사용하면 아래와 같이 짧게 코드를 작성할 수 있다.
is.transferTo(os)

아래의 코드에서 Board 클래스는 DTO이다.

관련 데이터를 묶어서 하나의 객체로 즉, DTO로 만들어서 수신과 전송을 한다는 뜻이다.

사용 예제(Oracle)

Board.java

import java.sql.Blob;
import java.util.Date;
import lombok.Data;

public class Board {
	private int bno;
	private String btitle;
	private String bcontent;
	private String bwriter;
	private Date bdate;
	private String bfilename;
	private Blob bfiledata;
	public int getBno() {
		return bno;
	}
	public void setBno(int bno) {
		this.bno = bno;
	}
	public String getBtitle() {
		return btitle;
	}
	public void setBtitle(String btitle) {
		this.btitle = btitle;
	}
	public String getBcontent() {
		return bcontent;
	}
	public void setBcontent(String bcontent) {
		this.bcontent = bcontent;
	}
	public String getBwriter() {
		return bwriter;
	}
	public void setBwriter(String bwriter) {
		this.bwriter = bwriter;
	}
	public Date getBdate() {
		return bdate;
	}
	public void setBdate(Date bdate) {
		this.bdate = bdate;
	}
	public String getBfilename() {
		return bfilename;
	}
	public void setBfilename(String bfilename) {
		this.bfilename = bfilename;
	}
	public Blob getBfiledata() {
		return bfiledata;
	}
	public void setBfiledata(Blob bfiledata) {
		this.bfiledata = bfiledata;
	}
	@Override
	public String toString() {
		return "Board [bno=" + bno + ", btitle=" + btitle + ", bcontent=" + bcontent + ", bwriter=" + bwriter
				+ ", bdate=" + bdate + ", bfilename=" + bfilename + ", bfiledata=" + bfiledata + "]";
	}
}

BoardSelectExample.java

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BoardSelectExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521/orcl", 
				"java", 
				"oracle"
			);	
			
			//매개변수화된 SQL 문 작성
			String sql = "" +
				"SELECT bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata " +
				"FROM boards " +
				"WHERE bwriter=?";
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "winter");
			
			//SQL 문 실행 후, ResultSet을 통해 데이터 읽기
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {		
				//데이터 행을 읽고 Board 객체 생성
				Board board = new Board();
				board.setBno(rs.getInt("bno"));
				board.setBtitle(rs.getString("btitle"));
				board.setBcontent(rs.getString("bcontent"));
				board.setBwriter(rs.getString("bwriter"));
				board.setBdate(rs.getDate("bdate"));
				board.setBfilename(rs.getString("bfilename"));
				board.setBfiledata(rs.getBlob("bfiledata"));
				
				//콘솔에 출력
				System.out.println(board);
				
				//파일로 저장
				Blob blob = board.getBfiledata();
				if(blob != null) {
					InputStream is = blob.getBinaryStream();
					OutputStream os = new FileOutputStream("C:/Temp/" + board.getBfilename());
					is.transferTo(os);
					os.flush();
					os.close();
					is.close();
				}
			}
			rs.close();
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}
/*
Board [bno=2, btitle=눈오는 날, bcontent=함박눈이 내려요., bwriter=winter, bdate=2023-08-06, bfilename=snow.jpg, bfiledata=oracle.sql.BLOB@305ffe9e]
Board [bno=1, btitle=눈오는 날, bcontent=함박눈이 내려요., bwriter=winter, bdate=2023-08-05, bfilename=snow.jpg, bfiledata=oracle.sql.BLOB@35841320]
*/

사용 예제(MySQL)

Board.java

import java.sql.Blob;
import java.util.Date;
import lombok.Data;

public class Board {
	private int bno;
	private String btitle;
	private String bcontent;
	private String bwriter;
	private Date bdate;
	private String bfilename;
	private Blob bfiledata;
	public int getBno() {
		return bno;
	}
	public void setBno(int bno) {
		this.bno = bno;
	}
	public String getBtitle() {
		return btitle;
	}
	public void setBtitle(String btitle) {
		this.btitle = btitle;
	}
	public String getBcontent() {
		return bcontent;
	}
	public void setBcontent(String bcontent) {
		this.bcontent = bcontent;
	}
	public String getBwriter() {
		return bwriter;
	}
	public void setBwriter(String bwriter) {
		this.bwriter = bwriter;
	}
	public Date getBdate() {
		return bdate;
	}
	public void setBdate(Date bdate) {
		this.bdate = bdate;
	}
	public String getBfilename() {
		return bfilename;
	}
	public void setBfilename(String bfilename) {
		this.bfilename = bfilename;
	}
	public Blob getBfiledata() {
		return bfiledata;
	}
	public void setBfiledata(Blob bfiledata) {
		this.bfiledata = bfiledata;
	}
	@Override
	public String toString() {
		return "Board [bno=" + bno + ", btitle=" + btitle + ", bcontent=" + bcontent + ", bwriter=" + bwriter
				+ ", bdate=" + bdate + ", bfilename=" + bfilename + ", bfiledata=" + bfiledata + "]";
	}
}

.BoardSelectExample.java

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BoardSelectExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/thisisjava", 
				"java", 
				"mysql"
			);
			
			//매개변수화된 SQL문 작성
			String sql = "" +
				"SELECT bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata " +
				"FROM boards " +
				"WHERE bwriter=?";
			
			//PreparedStatement 얻기 및 값 지정
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "winter");
			
			//SQL문 실행 후, ResultSet을 통해 데이터 읽기
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {		
				//데이터 행을 읽고 Board 객체 생성
				Board board = new Board();
				board.setBno(rs.getInt("bno"));
				board.setBtitle(rs.getString("btitle"));
				board.setBcontent(rs.getString("bcontent"));
				board.setBwriter(rs.getString("bwriter"));
				board.setBdate(rs.getDate("bdate"));
				board.setBfilename(rs.getString("bfilename"));
				board.setBfiledata(rs.getBlob("bfiledata"));
				
				//콘솔에 출력
				System.out.println(board);
				
				//파일로 저장
				Blob blob = board.getBfiledata();
				if(blob != null) {
					InputStream is = blob.getBinaryStream();
					OutputStream os = new FileOutputStream("C:/Temp/" + board.getBfilename());
					is.transferTo(os);
					os.flush();
					os.close();
					is.close();
				}
			}
			rs.close();
			
			//PreparedStatement 닫기
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}

저작자표시 비영리 변경금지

'Java Category > Java' 카테고리의 다른 글

[Java] DB 트랜잭션 처리 (0)	2023.08.20
[Java] DB 프로시저와 함수 호출 (0)	2023.08.19
[Java] JDBC 개요 및 DB 연결하기 (0)	2023.08.17
[Java] TCP 채팅 프로그램 (0)	2023.08.16
[Java] JSON 데이터 형식 (0)	2023.08.15

[Java] 데이터베이스에 저장(쓰기, 수정, 삭제) 및 읽기

데이터 베이스 구성

Account Table

Boards

Users

데이터 저장

데이터 쓰기(INSERT문)

User 테이블

boards 테이블

데이터 수정

데이터 삭제

데이터 읽기

DB에서 데이터를 가져오는 메소드

ResultSet 구조

데이터 행 읽기

데이터 추출

users 테이블

boards 테이블

'Java Category > Java' 카테고리의 다른 글

댓글

티스토리툴바