[JAVA] SQL / JDBC

SQL

1. SQL(Structed Query Language)

관계형 데이터베이스에서 데이터를 조작하고 쿼리하는 표준 수단

• DML (Data Manipulation Language): 데이터를 조작하기 위해 사용. INSERT, UPDATE, DELETE, SELECT
• DDL (Data Definition Language): 데이터베이스의 스키마를 정의하거나 조작하기 위해 사용. CREATE, DROP, ALTER 
• DCL (Data Control Language) : 데이터를 제어하는 언어. 권한을 관리하고, 테이터의 보안, 무결성 등을 정의. GRANT, REVOKE

https://www.edwith.org/boostcourse-web/lecture/16720/

---

JDBC

1. JDBC(Java Database Connectivity)

Java가 DB와 통신할 수 있게 해주는 API

자바를 이용한 데이터베이스 접속과 SQL 문장의 실행, 그리고 실행 결과로 얻어진 데이터의 핸들링을 제공하는 방법과 절차에 관한 규약
자바 프로그램 내에서 SQL문을 실행하기 위한 자바 API

---

2. JDBC 실행 순서

https://minwan1.github.io/2017/04/08/2017-04-08-Datasource,JdbcTemplate/

 

[Feat. mysql]

1. Maven에 mysql 의존성 추가 

#Maven

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

 

2. JDBC 드라이버 로드

• 사용하는 데이터베이스 벤더에 따라 class 형식이 달라진다.

3. Connection 인터페이스 구현 = Connection 객체 생성 (DB에 접속)

• 사용하는 데이터베이스 벤더에 따라 url 형식이 달라진다.

4. Statement 인터페이스 구현 (쿼리문 만들기)

• connection 객체를 이용한다
• PreparedStatement를 이용할 경우, 쿼리문

5. 쿼리문 실행

• any SQL : stmt.execute("쿼리문");
• SELECT : stmt.executeQeury("쿼리문");
• INSERT, DELETE, UPDATE : stmt.executeUpdate("쿼리문");

6. ResultSet

• 결과 값이 있는 경우만(SELECT) statment 객체를 이용한다
  

7. Close

• resultset -> statement -> connection 순서로 close();

https://www.edwith.org/boostcourse-web/lecture/16734/

---

4. JDBC 실습

1. DTO 클래스 작성 (필드=컬럼 / 생성자 두개 / getter, setter / toString)

• DTO(Data Transfer Object): 계층 간 데이터 교환을 위한 자바빈즈
  • 계층: 프리젠테이션 레이어, 서비스 레이어, 퍼시스턴스 레이어 #레이어드 아키텍쳐
• 로직이 없는 순수한 데이터 객체(데이터 가방)

public class Role {
    private Integer roleId;
    private String description;
 
    public Role() {
 
    }
 
    public Role(Integer roleId, String description) {
        super();
        this.roleId = roleId;
        this.description = description;
    }
 
    public Integer getRoleId() {
        return roleId;
    }
 
    public void setRoleId(Integer roleId) {
        this.roleId = roleId;
    }
 
    public String getDescription() {
        return description;
    }
 
    public void setDescription(String description) {
        this.description = description;
    }
 
    @Override
    public String toString() {
        return "Role [roleId=" + roleId + ", description=" + description + "]";
    }
}

 

 

2. DAO 클래스 작성: 조회, 입력, 삭제, 수정 기능 묶어놓은 클래스

• DAO(Data Acess Object): 데이터베이스의 데이터를 조작하는 객체
  
• DB 접근 로직과 비즈니스 로직을 분리한다

public class RoleDao {
    private static String dburl = "uuuuuuu";
    private static String dbusername = "nnnnnnn";
    private static String dbpassword = "ppppppp";
 
    //SELECT 1: 한 건 조회
    public Role getRole(Integer roleId) {
 
    }
 
    //SELECT 2: 전체 조회
    public List<Role> getRoles() {
 
    }
 
    //INSERT
    public int addRole(Role role) {
 
    }
 
    //DELETE
    public int deleteRole(Integer roleId) {
 
    }
 
    //UPDATE
    public int updateRole(Role role) {
 
    }
}
 

 

1.SELECT 1: 한 건 조회

public Role getRole(Integer roleId) {
    Role role = null;
    Connection conn = null;
    PreparedStatement preStmt = null;
    ResultSet resultSet = null;
 
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(dburl, dbusername, dbpassword);
 
        String sql = "SELECT description, role_id FROM role WHERE role_id = ?";
        preStmt = conn.prepareStatement(sql);
        preStmt.setInt(1, roleId);
        resultSet = preStmt.executeQuery();
 
        if (resultSet.next()) { // 결과 값이 있을 때만 가져옴
            String description = resultSet.getString(1); //getString("description"); 
            int id = resultSet.getInt("role_id"); //getInt(2); 
            role = new Role(id, description);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            // resultSet이 존재해야 close() 사용 가능. 없으면 null point 예외 발생
            if (resultSet != null) resultSet.close();
            if (preStmt != null) preStmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
             e.printStackTrace();
        }
    }
    return role;
}
 
public class JDBCTestExam {
 
    public static void main(String[] args) {
        RoleDao dao = new RoleDao();
        Role role = dao.getRole(100);
        System.out.println(role);
    }
 
}

• line 1:  roleId를 받아서 데이터 한 건 가져옴. 데이터 리턴타입: Role
• line 2, 47: Role 선언 후 return
• line 3, 4, 5: 연결, 명령, 결과값 객체 선언
• line 7, 19, 21: try-catch-finally / close() 먼저 작성
• line 8: JDBC드라이버 로드 (mysql 사용중)
• line 9: 데이터 접속
• line 11: conn 객체 이용해서 statement 생성. 생성에 쿼리문(sql)필요 -> line 10
• line 12: sql의 1번 물음표에 값을 넣어주세요
• line 13: sql 실행
• line 15: resultSet.next(): 결과값이 있을때만 결과값 꺼내오기
• line 16: 내 sql문의 column 순서와 값
• line 18: 가져온 값들로 Role 객체 만든다

 

2. SELECT 2: 전체 조회

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-with-resources 구문: finally close()까지 한번에 처리
    try (Connection conn = DriverManager.getConnection(dburl, dbusername, dbpassword);
        PreparedStatement preStmt = conn.prepareStatement(sql)) {
 
        try (ResultSet resultSet = preStmt.executeQuery()) {
            while (resultSet.next()) {
                String description = resultSet.getString(1);
                int id = resultSet.getInt("role_id");
                Role role = new Role(id, description);
                list.add(role); // 반복할 때마다 Role인스턴스를 생성하여 list에 추가한다.
            }
        } catch (Exception e) {
                e.printStackTrace();
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return list;
}
 
public class JDBCTestExam {
    public static void main(String[] args) {
 
        RoleDao dao = new RoleDao();
        
        List<Role> list = dao.getRoles();
 
        for(Role role : list) {
            System.out.println(role);
        }
    } 
}

 

3. INSERT

public int addRole(Role role) {
    int insertCount = 0;
 
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(dburl, dbusername, dbpassword);
 
        String sql = "INSERT INTO role (role_id, description) VALUES ( ?, ? )";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, role.getRoleId());
        ps.setString(2, role.getDescription());
 
        insertCount = ps.executeUpdate();
 
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } finally {
        try {
            if (preStmt != null) preStmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
             e.printStackTrace();
         }
    }
    return insertCount;
}
 
public class JDBCTestExam {
    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);
    }
}

INSERT, DELETE, UPDATE

• 실행 메소드가 executeUpdate() / cf) SELECT: executeQuery()
• ResultSet 사용 X

4. DELETE

public int deleteRole(Integer roleId) {
    int deleteCount = 0;
        
    Connection conn = null;
    PreparedStatement ps = null;
        
    try {
        Class.forName( "com.mysql.jdbc.Driver" );
   } catch (Exception ex) {
        ex.printStackTrace();        
    }
 
    String sql = "DELETE FROM role WHERE role_id = ?";
 
    try (Connection conn = DriverManager.getConnection(dburl, dbusername, dbpassword);
        PreparedStatement preStmt = conn.prepareStatement(sql)) {
        
        ps.setInt(1, roleId);
        deleteCount = preStmt.executeUpdate();
 
    } catch (Exception ex) {
        ex.printStackTrate();
    }
    return deleteCount;
}
 
public class JDBCTestExam {
    public static void main(String[] args) {
        int roleId = 500;
 
        RoleDao dao = new RoleDao();
        int deleteCount = dao.deleteRole(roleId);
 
        System.out.println(deleteCount);
    }
}

 

5. UPDATE

public int updateRole(Role role) {
    int updateCount = 0;
    
    Connection conn = null;
    PreparedStatement ps = null;
        
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch(Exception ex) {
        ex.printStackTrace();
    }    
 
    String sql = "UPDATE role SET description = ? WHERE role_id = ?";
 
    try(Connection conn = DriverManager.getConnection(dburl, dbusername, dbpassword);
        PreparedStatement preStmt = conn.prepareStatement(sql)) {
 
        preStmt.setString(1, role.getDescription());
        preStmt.setInt(2, role.getRoleId());
        
        updateCount = ps.executeUpdate();
 
    } catch(Exception ex) {
        ex.printStackTrace();
    }
    return updateCount;
}
 
public class JDBCTestExam {
    public static void main(String[] args) {
 
        int roleId = 500;
        String description = "CEO";
        
        Role role = new Role(roleId, description);
        
        RoleDao dao = new RoleDao();
        int updateCount = dao.updateRole(role);
 
        System.out.println(updateCount);
    } 
}

 

https://www.edwith.org/boostcourse-web/lecture/20653/

 

---

SPRING에서 JDBC 사용하기 #Spring JDBC