Day57 Java프로그래밍 기초(JDBC), 실습프로젝트(JDBC)

게시 2024/08/16

By 이건학

13 분읽는 시간

1. JDBC 기본 사용법

1.1 SQL문 보내기

1.1.1 Statement 생성

Java에서 SQL문을 sql서버에 보내기 위해서는 Statement 객체를 생성해야한다.
Data Definition Language 및 Data Maniipulation Language를 실행할 때 생성한다.
DriverManager을 통해 생성한 Connection객체를 호출하여 생성한다.

  
import java.sql.Connection
    
Connection con = DriverManager.getConnection(url, username, password);
Statement stmt = con.createStatement();

Statement로 보내는 쿼리문은 두 종류가 있다.
- executeUpdate
  - insert, update, delete Query문을 실행 할때 사용
  - 반환값 : 쿼리 결과로 인해 변경된 행(row)의 수를 반환한다.
- executeQuery
  - select Query문을 실행 할때 사용
  - 반환값 : 쿼리 결과를 포함한 ResultSet을 반환한다.

1.1.2 insert문 보내기

insert문은 SQL의 테이블 변경하기 때문에 return 값으로 변경된 행의수를 반환한다.

SQL 서버의 테이블 정보를 삽입 할때 사용하는 Query문이다.

  
Statement stmt = con.createStatement();
stmt.exceuteUpdate("insert into 테이블명(컬럼,컬럼...) values(값,값,....) ");

1.1.3 update문 보내기

update문은 SQL의 테이블 변경하기 때문에 return 값으로 변경된 행의수를 반환한다.

SQL 서버의 테이블 정보를 변경 할때 사용하는 Query문이다.

  
Statement stmt = con.createStatement();
stmt.exceuteUpdate("update 테이블명 set 컬럼=값, 컬럼=값,..... where 조건 ");

1.1.4 delete문 보내기

delete문은 SQL의 테이블 변경하기 때문에 return 값으로 변경된 행의수를 반환한다.
SQL 서버의 테이블 정보를 삭제 할때 사용하는 Query문이다.
1 2 Statement stmt = con.createStatement(); stmt.exceuteUpdate("delete from 테이블명 where 조건");
1.1.4.1) FK가 참조하는 데이터 delete

부모테이블의 데이터를 지우려면 데이터를 참조하는 자식테이블의 데이터를 먼저 지워야한다.

  
stmt.exceuteUpdate("delete from 자식_테이블명 where FK조건");
stmt.exceuteUpdate("delete from 부모_테이블명 where 조건");

1.1.5 select문 보내기

select문은 jvm에 반환된 컬럼을 받아와야 하기 때문에 ResultSet 타입으로 가져온다.

테이블의 정보를 Java변수에 대입 가능하다.

  
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select 컬럼명 from 테이블명")

1.2 ResultSet 생성

ResultSet은 select문에 기술된 컬럼으로 구성된 행의 집합이다.
ResultSet의 내부 구조는 다음과 같다.

ResultSet은 커서가 있는 행의 데이터만 읽을 수 있다.

  
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select 컬럼명 from 테이블명")
// 커서를 next로 이동(값이 있으면 true, 없으면 false) 
if(rs.next()){
rs.getXXXX(컬럼 번호)
}

1.2.1 rs.get(컬럼번호)옵션

return 타입
- getInt : int, number
- getString : char, varchar, text
- getDate/getTime : date, time, datetime
컬럼번호
- SQL테이블에서 SELECT한 컬럼 순서대로 1~n 까지 구성된다.
- 컬럼이름으로 대체 가능하다.

1.3 Statment의 문제점

Statment는 쿼리문을 완성해서 보내는 문법이다.
보완성이 취약한 문제점을 가지고 있다.
SQL삽입 공격 : 입력 문자열에 SQL 명령을 삽입하여 프로그램의 의도와 다르게 데이터를 조작하는 행위 이다.

단순 데이터 조작 뿐만아니라 SQL select문법을 이용해서 사용자의 정보를 탈취 할 수도 있다.

  
// 1. 여러개 삽입
// 원래 쿼리문
stmt.executeUpdate("insert into x_board(title, contents) " +
      "values('" + title + "','" + contents + "')");
// 공격 쿼리문
stmt.executeUpdate("insert into x_board(title, contents) " +
      "values('aaaa','bbbb'), ('haha', 'hoho'), ('hehe','puhul')");

1.3.1 PreparedStatement 생성

보완성을 강화하기 위해 쿼리문에 전송할 값들을 매개변수화하는 방법이다.
statement를 선언할 때 매개변수 값들을 ?으로 표시한다.
in-parameter : ? 값으로 들어갈 타입에 따라 set()으로 설정한다.

  
import java.sql.Connection

Connection con = DriverManager.getConnection(url, username, password);
PreparedStatement stmt = con.prepareStatement("select 컬럼명 from 테이블명 where 컬럼명= ? ");
stmt.setXXX(1,값);
ResultSet rs = stmt.executQuery(); //쿼리 전송 및 결과 리턴

PreparedStatement stmt = con.prepareStatement("update 테이블명 set 컬럼명=?, 컬럼명=?.. where 조건");
stmt.setXXX(1,값); //첫번째 ?
stmt.setXXX(2,값); //두번째 ?
int count = stmt.executUpdate(); //쿼리 전송 및 결과 리턴

1.4 Primary Key return

자동생성된 PK값을 리턴받을 수 있다.
Statement.RETURN_GENERATED_KEYS로 ResultSet에 생성한다.

Statment로 생성된 객체는 쿼리문을 전송(execute)할때 리턴 받는다.

  
Statement stmt = con.createStatement();
stmt.executeUpdate(쿼리문,Statement.RETURN_GENERATED_KEYS);
ResultSet keyRS = stmt.getGeneratedKeys();
keyRS.next(); //구조는 resultSet으로 next()로 다음 객체 여부를 판별한다.
int keyNo = keyRS.getInt(1); // 프로젝션 된 행의 컬럼순서 1~n

PreparedStatement로 생성된 객체는 statement를 생성(PrepareStatement)할 리턴 받는다.

  
PreparedStatement stmt = con.prepareStatement(쿼리문, Statement.RETURN_GENERATED_KEYS);
stmt.setXXX(n,value);
stmt.executeXXXXX();
ResultSet keyRS = stmt.getGeneratedKeys();
keyRS.next(); //구조는 resultSet으로 next()로 다음 객체 여부를 판별한다.
int keyNo = keyRS.getInt(1); // 프로젝션 된 행의 컬럼순서 1~n

1.5 AutoCommit옵션

SQL의 쿼리문을 전송하면 기본 옵션으로 SQL서버의 테이블을 변경한다.
SQL 쿼리문을 잘못 작성하거나 중간에 실행이 중단 되었을 때, 되돌리수가 없다.
오토커밋의 개념

1.5.1 Autocommit의 문제점

여러개의 쿼리문을 전송할때 문제가 발생하면 데이터에 오류가 생긴다.

  
한개의 프로세스{

executeQuery("쿼리문1");

executeQuery("쿼리문2");

executeQuery("쿼리문3");  // --------------여기 까지 수행완료

executeQuery("잘못된 쿼리문");

executeQuery("쿼리문4");

executeQuery("쿼리문5");
}

쿼리문이 1~3까지는 수행되어 데이터가 바뀌고 이후로는 데이터가 바뀌지 않는다.

1.5.2 explicit commit

수동으로 commit을 조정하면, rollback이 가능하여 한개의 프로세스 전체를 취소 할 수 있다.
수동커밋의 개념
수동으로 commit을 조정하면, 트렌젝션이 완료되면, commit 오류가 발생하면 rollback을 실행한다.

1.5.3 트랜잭션

트랜잭션(transaction) : 연속적으로 수행하는 여러 개 insert/update/delete 작업을 한 단위의 작업으로 묶는것이다.
트랜잭션 설정은 setAutoCommit(false) -> 각 작업수행 -> 모든 작업 완료시 commit()/ 오류 발생시 rollback(); -> 최종적으로 setAutoCommit(true)

  
import java.sql.DriverManager;

try{
  
  Connection con = DriverManager.getConnection(url,name,pwd);
  con.setAutoCommit(false);
  /* 한개의 단위 작업 시작 */
  DAO.insert(쿼리문을 전송하는 작업);
  DAO.update(쿼리문을 전송하는 작업);
  DAO.select(쿼리문을 전송하는 작업);
  /* 한개의 단위 작업 종료 */
  con.commit();
  
  } catch (Exception e) {
    try {
    con.rollback(); //실패시 롤백
    } catch (Exception e2) { }

  } finally {
    try {
    con.setAutoCommit(true);
    con.close();
  } catch (Exception e2) {}
}

2. 실습프로젝트

2.1 PreparedStatment 변경

Dao의 Statement의 구조는 다음과 같다.

  
//insert 쿼리문
boolean insert(Project project) throws Exception {
  try (Statement stmt = con.createStatement()) {
    stmt.executeUpdate(insert 쿼리문, Statement.RETURN_GENERATED_KEYS);
    ResultSet keyRS = stmt.getGeneratedKeys();
    keyRS.next();
    int projectNo = keyRS.getInt(1);
    project.setNo(projectNo);
    return true;
  }
}

List<Project> list() throws Exception{
  try (Statement stmt = con.createStatement();
          ResultSet rs = stmt.executeQuery(select 쿼리문)) {
    ArrayList<Project> list = new ArrayList<>();
    while (rs.next()) {
      Project project = new Project();
      project.setNo(rs.get(컬럼명));
      list.add(project);
    }
    return list;
  } 
}

Project findBy(int no) throws Exception{
  try (Statement stmt = con.createStatement();
          ResultSet rs = stmt.executeQuery(select 쿼리문)) {
    if (rs.next()) {
      Project project = new Project();
      project.setNo(rs.get(컬럼명));
      return project;
    }
    return null;
  } 
}

boolean update(Project project) throws Exception{
  try (Statement stmt = con.createStatement()) {
    int count = stmt.executeUpdate(update 쿼리문)
    return count > 0;
  } 
}

boolean delete(int no) throws Exception{
  try (Statement stmt = con.createStatement()) {
    int count = stmt.executeUpdate(update 쿼리문);
    return count > 0;
  } 
}

preparedStatment로 변경한다. ```java //insert 쿼리문 boolean insert(Project project) throws Exception { try (PreparedStatement stmt = con.prepareStatement(insert 쿼리문, Statement.RETURN_GENERATED_KEYS)) { stmt.set(?,values); stmt.executeUpdate();
ResultSet keyRS = stmt.getGeneratedKeys(); keyRS.next(); int projectNo = keyRS.getInt(1); project.setNo(projectNo); return true; } }

List list() throws Exception{ try (PreparedStatement stmt = con.prepareStatement(select 쿼리문); ResultSet rs = stmt.executeQuery()) { ArrayList list = new ArrayList<>(); while (rs.next()) { Project project = new Project(); project.setNo(rs.get(컬럼명)); list.add(project); } return list; } }

Project findBy(int no) throws Exception{ try (PreparedStatement stmt = con.prepareStatement()select 쿼리문){ stmt.setInt(1,no); try(ResultSet rs = stmt.executeQuery()){ if (rs.next()) { Project project = new Project(); project.setNo(rs.get(컬럼명)); return project; } return null; } } }

boolean update(Project project) throws Exception{ try (PreparedStatement stmt = con.prepareStatement(update 쿼리문)) { set(?,project.values); int count = stmt.executeUpdate() return count > 0; } }

boolean delete(int no) throws Exception{ try (PreparedStatement stmt = con.prepareStatement(delete 쿼리문)) { stmt.setInt(1,no); int count = stmt.executeUpdate(); return count > 0; } }

<br>

## 2.2 트랜젝션 설정 <br>
- 트랜젝션 설정은 다음과 같다.
```java
public void execute(String menuName) {
    System.out.printf("[%s]\n", menuName);

    try {
      Project project = new Project();
      project.setTitle(Prompt.input("프로젝트명?"));
      project.setDescription(Prompt.input("설명?"));
      project.setStartDate(Prompt.inputDate("시작일(예 : 2024-01-24)?"));
      project.setEndDate(Prompt.inputDate("종료일(예 : 2024-01-24)?"));

      System.out.println("팀원:");
      memberHandler.addMembers(project);
      //수동커밋 시작 
      con.setAutoCommit(false);
      projectDao.insert(project);
      projectDao.insertMembers(project.getNo(), project.getMembers());
      con.commit();
      //수동 커밋
      System.out.println("등록했습니다.");

    } catch (Exception e) {
      try {
        // 오류발생시 롤백
        con.rollback(); 
      } catch (Exception e2) {
      }
      System.out.println("등록 중 오류 발생!");
      e.printStackTrace();
    } finally {
      try {
        //자동커밋으로 전환
        con.setAutoCommit(true);
      } catch (Exception e2) {
      }
    }
  }
}

bitcamp

bitcamp,SQL, JAVA