close
프로필 배경
프로필 로고

JUST DO IT

강의 노트/웹 프로그래밍(풀스택) ・2023. 6. 20. fullscreen 넓게보기

[boostcourse] 2.10. JDBC - BE

1. JDBC란?

정의

- Java Database Connectivity

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

- 자바 프로그램 내에서 SQL문을 실행하기 위한 자바 API

- 자바는 표준 인터페이스인 JDBC API를 제공

환경 구성

- JDK 설치

- JDBC 드라이버 설치 (Maven에 다음과 같은 의존성을 추가함)

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

 

2. JDBC 사용 

JDBC를 이용한 프로그래밍 방법

1. imort java.sql.*;

2. 드라이버를 로드함

3. Connection 객체를 생성함 (DB에 접속)

4. Statement 객체를 생성 및 질의 수행

5. SQL문에 결과물이 있다면, ResultSet 객체를 생성함

6. 모든 객체를 닫음

 

JDBC 클래스의 생성 단계

1. Import

import java.sql.*;

 

2. 드라이버 로드

Class.forName("com.mysql.jdbc.Driver");

 

java api 중 java.lang.class에서 forName()으로 바로 클래스 객체를 가져올 수 있음

 

3. Connection 얻기

String dburl = "jdbc:mysql://localhost/dbName";

Connection con = DriverManager.getConnection(dburl, ID, PWD);

 

4. Statement 생성

Statement stmt = con.createStatement();

 

5. 질의 수행

ResultSet rs = stmt.executeQuery("select no from user");

 

6. ResultSet으로 결과 받기

while(rs.next()) {    // 한 번에 레코드를 가져오는게 아니라, 하나씩

  System.out.println(rs.getInt("no"));

}

 

7. Close

rs.close();

stmt.close();

con.close();

 

3. JDBC 실습 - SELECT

# 버전에 맞게 pom.xml에 dependency 및 plugin 추가

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>basic-jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>11</source>
                    <target>11</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

# 생성한 테이블 및 레코드

# Role.java

package jdbcexam.dto;

public class Role {

    private Integer roleId;
    private String description;

    public Role() {

    }

    public Role(Integer roleId, String description) {
        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 + '\'' +
                '}';
    }
}

# RoleDao.java

package jdbcexam.dao;

import jdbcexam.dto.Role;

import java.sql.*;

public class RoleDao {

    private static String dburl = "jdbc:mysql://localhost:3306/connectdb";
    private static String dbuser = "connectuser";
    private static String dbpasswd = "connectuser";

    public Role getRole(Integer roleId) {
        Role role = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(dburl, dbuser, dbpasswd);

            String query = "SELECT role_id, description FROM role WHERE role_id = ?";
            ps = conn.prepareStatement(query);
            ps.setInt(1, roleId);
            rs = ps.executeQuery();

            if(rs.next()) {     // 결과값이 없을 수도 있기에 boolean 타입
                String description = rs.getString("description");
                int id = rs.getInt("role_id");

                role = new Role(id, description);
            }

        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            if(rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


        return role;
    }
}

# JDBCExam1.java

package jdbcexam;

import jdbcexam.dao.RoleDao;
import jdbcexam.dto.Role;

public class JDBCExam1 {

    public static void main(String[] args) {

        RoleDao dao = new RoleDao();
        Role role = dao.getRole(100);
        System.out.println(role);
    }
}

id 100 을 요청하면, 해당 객체의 id 값과 description 값을 가져와 출력하기

 

4. JDBC 실습 - INSERT

// addRole() 함수

    public int addRole(Role role) {
        int insertCount = 0;

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(dburl, dbuser, dbpasswd);

            String query = "INSERT INTO role (role_id, description) VALUES (?, ?)";

            ps = conn.prepareStatement(query);
            ps.setInt(1, role.getRoleId());
            ps.setString(2, role.getDescription());

            insertCount = ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }

        return insertCount;
    }

 

// JDBCExam2.java

package jdbcexam;

import jdbcexam.dao.RoleDao;
import jdbcexam.dto.Role;

public class JDBCExam2 {

    public static void main(String[] args) {

        int roleId = 500;
        String description = "CTO";
        Role role = new Role(roleId, description);

        RoleDao dao = new RoleDao();
        int insertCount = dao.addRole(role);
        System.out.println(insertCount);
    }
}

id값과 description 값을 설정하여 table에 insert하기

 

5. JDBC 실습 - SELECT *

# getRoles() 함수

public List<Role> getRoles() {
        List<Role> list = new ArrayList<>();

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch(ClassNotFoundException e) {
            e.printStackTrace();
        }

        String query = "SELECT description, role_id FROM role order by role_id desc";

        // try-with-resources : try()에서 선언된 객체들에 대하여, try가 종료될 때 자동으로 리소스를 해제해주는 기능
        try(Connection coon = DriverManager.getConnection(dburl, dbuser, dbpasswd);
            PreparedStatement ps = coon.prepareStatement(query)) {

            try(ResultSet rs = ps.executeQuery()) {

                while(rs.next()) {
                    String description = rs.getString(1);
                    int id = rs.getInt("role_id");
                    Role role = new Role(id, description);
                    list.add(role);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

 

# JDBCExam3.java

package jdbcexam;

import jdbcexam.dao.RoleDao;
import jdbcexam.dto.Role;

import java.util.List;

public class JDBCExam3 {

    public static void main(String[] args) {

        RoleDao dao = new RoleDao();
        List<Role> list = dao.getRoles();

        for(Role role : list) {
            System.out.println(role);
        }
    }
}

role 테이블의 모든 레코드를 roleId 순으로 출력하기

 

# 최종 RoleDao.java

package jdbcexam.dao;

import jdbcexam.dto.Role;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class RoleDao {

    private static String dburl = "jdbc:mysql://localhost:3306/connectdb";
    private static String dbuser = "connectuser";
    private static String dbpasswd = "connectuser";

    public Role getRole(Integer roleId) {
        Role role = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(dburl, dbuser, dbpasswd);

            String query = "SELECT role_id, description FROM role WHERE role_id = ?";
            ps = conn.prepareStatement(query);
            ps.setInt(1, roleId);
            rs = ps.executeQuery();

            if (rs.next()) {     // 결과값이 없을 수도 있기에 boolean 타입
                String description = rs.getString("description");
                int id = rs.getInt("role_id");

                role = new Role(id, description);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


        return role;
    }

    public int addRole(Role role) {
        int insertCount = 0;

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(dburl, dbuser, dbpasswd);

            String query = "INSERT INTO role (role_id, description) VALUES (?, ?)";

            ps = conn.prepareStatement(query);
            ps.setInt(1, role.getRoleId());
            ps.setString(2, role.getDescription());

            insertCount = ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }

        return insertCount;
    }

    public List<Role> getRoles() {
        List<Role> list = new ArrayList<>();

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch(ClassNotFoundException e) {
            e.printStackTrace();
        }

        String query = "SELECT description, role_id FROM role order by role_id desc";

        // try-with-resources : try()에서 선언된 객체들에 대하여, try가 종료될 때 자동으로 리소스를 해제해주는 기능
        try(Connection coon = DriverManager.getConnection(dburl, dbuser, dbpasswd);
            PreparedStatement ps = coon.prepareStatement(query)) {

            try(ResultSet rs = ps.executeQuery()) {

                while(rs.next()) {
                    String description = rs.getString(1);
                    int id = rs.getInt("role_id");
                    Role role = new Role(id, description);
                    list.add(role);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}

'강의 노트 > 웹 프로그래밍(풀스택)' 카테고리의 다른 글

[boostcourse] 3.7. Spring Core - BE  (0) 2023.06.24
[boostcourse] 2.11. Web API - BE  (0) 2023.06.21
[boostcourse] 2.5. Scope - BE  (0) 2023.06.20
[boostcourse] 2.4. Redirect & Forward - BE  (0) 2023.06.20
[boostcourse] 2.3. JSP - BE  (0) 2023.06.20
강의 노트/웹 프로그래밍(풀스택) 관련 글
더 보기
댓글

티스토리툴바