본문 바로가기
Database

4/29 - Java 데이터 베이스 입출력

by Jiwon_Loopy 2025. 5. 2.
반응형

Java JDBC

  • 자바는 데이터베이스와 연결해서 데이터 입출력 작업을 할 수 있도록 JDBC를 제공
  • DriverManager (최상위 클래스)
    • JDBC, Driver를 관리
  • Connection
    • 트랜잭션 및 하위 객체 관리
  • Statement
    • SQL의 DDL, DML을 실행할 때 사용
  • PreparedStatement
    • Statement와 동일하게 DDL, DML사용, 매개변수화 된 SQL사용
  • CallbackStatement
    • 프로시저와 함수를 읽어온다.
  • ResultSet
    • 결과를 읽어온다.
  • db 불러오기
jdbc:oracle:thin:@localhost:1521/orcl

오라클 데이터 베이스 연결하기

package ch20.oracle.sec05;

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

public class ConnectionExample {

	public static void main(String[] args) {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521/xe",
					"testuser",
					"test1234"
					);
			System.out.println("연결 성공");		
	
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn!=null) {
				try {
					conn.close();
					System.out.println("연결 끊기");
				} catch (SQLException e) {
					
				}
			}
		}

	}

}

출력 결과

연결 성공
연결 끊기

데이터 베이스 쓰기

  • ?는 입력 값 매개변수를 대체할 수 있다.
String val1 = "";
			String val2 = "";
			
			String sql = "insert into user (aaa,bbb) values ('"+val1+"','"+val2+"')"; //statement
			String sql2 = "insert into user (aaa,bbb) values (?, ?)";		// preparedstatement
			
			PreparedStatement pstmt = conn.prepareStatement(sql)
			
			
			pstmt.setString(1, "winter")
			...
			(? 갯수만큼)
			
			int rows = pstmt,excuteUpdate(); // 쿼리 실행
			
			pstmt.close(); // 닫기
  • 값 저장
package ch20.oracle.sec05;

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

public class ConnectionExample {

	public static void main(String[] args) {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521/xe",
					"testuser",
					"test1234"
					);
			System.out.println("연결 성공");		
			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");
			
			int rows = pstmt.executeUpdate();
			System.out.println("저장된 행 수: "+ rows);
			pstmt.close();
			
				
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn!=null) {
				try {
					conn.close();
					System.out.println("연결 끊기");
				} catch (SQLException e) {
					
				}
			}
		}

	}
	

}

출력 결과

연결 성공
저장된 행 수: 1
연결 끊기
  • 한 번 더 실행 시
    • 유니크 제약 조건 위배
    • 같은 데이터 삽입 시 primary key가 중복
연결 성공
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (TESTUSER.SYS_C007018) violated

<https://docs.oracle.com/error-help/db/ora-00001/>
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:715)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:615)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1372)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:969)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:237)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:524)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:298)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1508)
	at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:2009)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1622)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3973)
	at oracle.jdbc.driver.OraclePreparedStatement.doExecuteLargeUpdate(OraclePreparedStatement.java:4339)
	at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:4316)
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4291)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1004)
	at ch20.oracle.sec05.ConnectionExample.main(ConnectionExample.java:30)
Caused by: Error : 1, Position : 0, SQL = INSERT INTO users (userid, username, userpassword, userage, useremail) VALUES (:1 , :2 , :3 , :4 , :5 ), Original SQL = INSERT INTO users (userid, username, userpassword, userage, useremail) VALUES (?, ?, ?, ?, ?), Error Message = ORA-00001: unique constraint (TESTUSER.SYS_C007018) violated

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:723)
	... 15 more
연결 끊기
  • 연관 키가 있는 데이터 불러와서 삽입
package ch20.oracle.sec05;

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

public class BoardWithFileInsertExample {

	public static void main(String[] args) {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521/xe",
					"testuser",
					"test1234"
					);
			System.out.println("연결 성공");		
			
			String sql = "INSERT INTO boards (bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata) VALUES (SEQ_BNO.NEXTVAL, ?, ?, ?,SYSDATE ,?,?)";
			

			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("D:\\\\temp\\\\snow.jpg"));
			
			int rows = pstmt.executeUpdate();
			System.out.println("저장된 행 수: "+ rows);
			pstmt.close();
			
				
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn!=null) {
				try {
					conn.close();
					System.out.println("연결 끊기");
				} catch (SQLException e) {
					
				}
			}
		}

	}
	

}
  • 데이터 수정
package ch20.oracle.sec05;

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

public class BoardWithFileInsertExample {

	public static void main(String[] args) {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521/xe",
					"testuser",
					"test1234"
					);
			System.out.println("연결 성공");		
			
			String sql = "UPDATE boards SET btitle =?, bcontent=?, bfilename=?, bfiledata=? WHERE bno =?";
			

			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, "눈사람");
			pstmt.setString(2, "눈으로 만든 사람");
			pstmt.setString(3, "snowman.jpg");
			pstmt.setBlob(4, new FileInputStream("D:\\\\temp\\\\snowman.jpg"));
			pstmt.setInt(5, 1);
			
			int rows = pstmt.executeUpdate();
			System.out.println("수정된 행 수: "+ rows);
			pstmt.close();
			
				
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn!=null) {
				try {
					conn.close();
					System.out.println("연결 끊기");
				} catch (SQLException e) {
					
				}
			}
		}

	}
	

}
  • 데이터 삭제
package ch20.oracle.sec05;

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

public class BoardWithFileInsertExample {

	public static void main(String[] args) {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521/xe",
					"testuser",
					"test1234"
					);
			System.out.println("연결 성공");		
			
			String sql = "DELETE from boards WHERE bwriter=?";
			

			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, "winter");
			
			int rows = pstmt.executeUpdate();
			System.out.println("삭제된 행 수: "+ rows);
			pstmt.close();
			
				
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn!=null) {
				try {
					conn.close();
					System.out.println("연결 끊기");
				} catch (SQLException e) {
					
				}
			}
		}

	}
	

}

데이터 베이스 읽기

  • ResultSet
ResultSet rs = pstmt.executeQuery();
  • 1개 행 가져오기
if(re.next){
	
	}else{ }
  • n개 행 가져오기
while(re.next){
	
	}else{ }
  • 컬럼 이름으로 행 읽기
rs.getString("컬럼명")
rs.getInt()
...
  • 순서대로 읽기
rs.getString(1); // 1번째 컬럼
rs.getInt(2); // 2번째 컬럼
...
  • 읽어서 가저오기
package ch20.oracle.sec09.exam01;

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 UserSelcetExample {

	public static void main(String[] args) {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521/xe",
					"testuser",
					"test1234"
					);
			System.out.println("연결 성공");		
			
			String sql = "SELECT userid, username, userpassword, userage, useremail FROM users WHERE userid=?";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "winter");
			
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()) {
				User user = new User();
				user.setUserId(rs.getString("userid"));
				user.setUserName(rs.getString("username"));
				user.setUserPassword("userpassword");
				user.setUserAge(rs.getInt(4));
				user.setUserEmail(rs.getString(5));
				System.out.println(user);
				
			}else {
				System.out.println("사용자 아이디가 존재하지 않음");
			}
			rs.close();
			pstmt.close();
			
				
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn!=null) {
				try {
					conn.close();
					System.out.println("연결 끊기");
				} catch (SQLException e) {
					
				}
			}
		}

	}
	

}

출력 결과

연결 성공
User(userId=winter, userName=한겨울, userPassword=userpassword, userAge=25, userEmail=winter@mycompany.com)
연결 끊기
  • 유저 가져오기
package ch20.oracle.sec09.exam01;

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 UserSelcetExample {

	public static void main(String[] args) {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521/xe",
					"testuser",
					"test1234"
					);
			System.out.println("연결 성공");		
			
			String sql = "SELECT bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata FROM boards WHERE bwriter=?";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,"winter");
			
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()) {
				Board b = new Board();
				b.setBno(rs.getInt("bno"));
				b.setBtitle(rs.getString("btitle"));
				b.setBcontent(rs.getString("bcontent"));
				b.setBwrite(rs.getString("bwriter"));
				b.setBdate(rs.getDate("bdate"));
				b.setBfileName(rs.getString("bfilename"));
				b.setBfiledata(rs.getBlob("bfiledata"));
				System.out.println(b);
				
				
				Blob blob = b.getBfiledata();
				if(blob != null) {
					InputStream is = blob.getBinaryStream();
					OutputStream os = new FileOutputStream("D:/Temp/" + b.getBfileName());
					is.transferTo(os);
					os.flush();
					os.close();
					is.close();
				}
			}
			rs.close();
			pstmt.close();
			
				
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn!=null) {
				try {
					conn.close();
					System.out.println("연결 끊기");
				} catch (SQLException e) {
					
				}
			}
		}

	}
	

}

출력 결과

연결 성공
Board(bno=3, btitle=눈 오는 날, bcontent=함박눈이 내려요, bwrite=winter, bdate=2025-04-29, bfileName=snow.jpg, bfiledata=oracle.sql.BLOB@4efcf8a)
Board(bno=4, btitle=눈 오는 날, bcontent=함박눈이 내려요, bwrite=winter, bdate=2025-04-29, bfileName=snow.jpg, bfiledata=oracle.sql.BLOB@5db99216)
Board(bno=2, btitle=눈 오는 날, bcontent=함박눈이 내려요, bwrite=winter, bdate=2025-04-29, bfileName=snow.jpg, bfiledata=oracle.sql.BLOB@3ec11999)
연결 끊기

트랜잭션 처리

  • JDBC 제공 함수
    • Commit
    • Rollback
  • JDBC에서는 Insert, Update, Delete등을 사용하면 자동으로 커밋이 되므로 자동 커밋 기능을 꺼두어야 함
  • setAutoCommit(false)
    • 자동 커밋 기능을 끈다.
  • 트랜잭션 적용 입출금
package ch20.oracle.sec09.exam01;

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

public class TransactionExample {

	public static void main(String[] args) {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");

			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/xe", "testuser", "test1234");
			System.out.println("연결 성공");

			// 자동 커밋 기능 off
			conn.setAutoCommit(false);
			
			// 출금 작업
			String sql1 = "UPDATE accounts SET balance = balance-? WHERE ano=?";

			PreparedStatement pstmt1 = conn.prepareStatement(sql1);
			pstmt1.setInt(1, 10000);
			pstmt1.setString(2, "111-111-1111");

			int rows1 = pstmt1.executeUpdate();
			if (rows1 == 0)
				throw new Exception("입금되지 않았음");
			pstmt1.close();

			// 입금 작업
			String sql2 = "UPDATE accounts SET balance = balance+? WHERE ano=?";

			PreparedStatement pstmt2 = conn.prepareStatement(sql2);
			pstmt2.setInt(1, 10000);
			pstmt2.setString(2, "222-222-2222");

			int rows2 = pstmt2.executeUpdate();
			if (rows2 == 0)
				throw new Exception("입금되지 않았음");
			pstmt2.close();

			conn.commit();
			System.out.println("계좌 이체 성공");
			
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}
			System.out.println("계좌 이체 실패");
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.setAutoCommit(true);
					conn.close();
					System.out.println("연결 끊기");
				} catch (SQLException e) {
				}
			}
		}
	}

}

출력 결과

연결 성공
계좌 이체 성공
연결 끊기

메모

  • 정규화는 테이블을 쪼개는 것이 아닌 데이터를 나누는 것
  • 반 정규화는 테이블을 쪼개는 것
  • close()는 되도록 finally 안에 사용
  • 자원을 해제할 때는 역순으로
catch(Exeption e){
	e.printStackTrace();
	} finally {
		try {rs.close();} catch (Exception e) {}
		try {stmt.close();} catch (Exception e) {}
		
		if(conn != null){
			try{con.close();} catch (SQLException e) {}
		}
	}

 

728x90
반응형

'Database' 카테고리의 다른 글

4/30 - MariaDB  (0) 2025.05.03
4/28 - 시퀀스, 사용자 권한, 롤 관리, PL/SQL  (0) 2025.05.02
오라클로 배우는 데이터베이스 - 셋째 마당 01 - 1 ~ 5번 문제  (0) 2025.05.02
오라클로 배우는 데이터 베이스 - 둘째 마당 09 - 1 ~ 4번  (0) 2025.05.02
오라클로 배우는 데이터베이스 입문, 둘째 마당 08 - 1 ~ 4번  (0) 2025.04.27

관련글

티스토리툴바