티스토리 뷰

Language/Java

JDBC 설정

Seogineer 2020. 12. 21. 23:23

JDBC(Java Database Connectivity)

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

pom.xml에 JDBC 의존성 추가

<!-- mariaDB JDBC 드라이버를 추가 -->
<dependency>
  <groupId>org.mariadb.jdbc</groupId>
  <artifactId>mariadb-java-client</artifactId>
  <version>2.7.0</version>
</dependency>

JDBC 사용 설명

package kr.or.connect.jdbcexam.dao;

// 1. import java.sql.*
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import kr.or.connect.jdbcexam.dto.Role;

public class RoleDao {

    private static String dburl = "jdbc:mariadb://localhost:3306/connectdb";
    private static String dbUser = "connectuser";
    private static String dbPasswd = "connect123!@#";

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

        try {
            Class.forName("org.mariadb.jdbc.Driver");    // 2. 드라이버 로드
            conn = DriverManager.getConnection(dburl, dbUser, dbPasswd);    // 3. Connection 얻기
            String sql = "SELECT role_id,description FROM role WHERE role_id = ?";
            ps = conn.prepareStatement(sql);    // 4. Statement 생성
            ps.setInt(1, roleId);
            rs = ps.executeQuery();    // 5. 질의 수행
            if(rs.next()) {
                // 6. ResultSet으로 결과 받기
                int id = rs.getInt("role_id");
                String description = rs.getString("description");
                role = new Role(id, description);
            }

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

        return role;
    }
}

질의 수행

ps.execute("query");        // SQL(구분없음)
ps.executeQuery("query");    // SELECT
ps.executeUpdate("query");    // INSERT, UPDATE, DELETE

예제 소스코드

DTO

package kr.or.connect.jdbcexam.dto;

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 + "]";
    }
}

DAO

package kr.or.connect.jdbcexam.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import kr.or.connect.jdbcexam.dto.Role;

public class RoleDao {

    private static String dburl = "jdbc:mariadb://localhost:3306/connectdb";
    private static String dbUser = "connectuser";
    private static String dbPasswd = "connect123!@#";

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

        try {
            Class.forName("org.mariadb.jdbc.Driver");
            conn = DriverManager.getConnection(dburl, dbUser, dbPasswd);
            String sql = "SELECT role_id,description FROM role WHERE role_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, roleId);
            rs = ps.executeQuery();
            if(rs.next()) {
                int id = rs.getInt("role_id");
                String description = rs.getString("description");
                //String description = rs.getString(2);
                role = new Role(id, description);
            }

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

        return role;
    }

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

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

        String sql = "SELECT description, role_id FROM role ORDER BY role_id DESC";

        try (
            Connection conn = DriverManager.getConnection(dburl, dbUser, dbPasswd);
            PreparedStatement ps = conn.prepareStatement(sql)
        ) {
            try (
                ResultSet rs = ps.executeQuery()
            ){
                while(rs.next()) {
                    //String description = rs.getString(1);
                    String description = rs.getString("description");
                    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;
    }

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

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            Class.forName("org.mariadb.jdbc.Driver");

            conn = DriverManager.getConnection(dburl, dbUser, dbPasswd);

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

            ps = conn.prepareStatement(sql);

            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 (SQLException e) {}
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

        return insertCount;
    }

    public int updateRole(Integer roleId, String description) {
        int insertCount = 0;

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            Class.forName("org.mariadb.jdbc.Driver");

            conn = DriverManager.getConnection(dburl, dbUser, dbPasswd);

            String sql = "UPDATE role SET description = ? WHERE role_id = ?";

            ps = conn.prepareStatement(sql);

            ps.setString(1, description);
            ps.setInt(2, roleId);

            insertCount = ps.executeUpdate();

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

        return insertCount;
    }

    public int deleteRole(Integer roleId) {
        int insertCount = 0;

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            Class.forName("org.mariadb.jdbc.Driver");

            conn = DriverManager.getConnection(dburl, dbUser, dbPasswd);

            String sql = "DELETE FROM role WHERE role_id = ?";

            ps = conn.prepareStatement(sql);

            ps.setInt(1, roleId);

            insertCount = ps.executeUpdate();

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

        return insertCount;
    }
}

단건 조회

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.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);
    }
}

다건 조회

package kr.or.connect.jdbcexam;

import java.util.List;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam5 {

    public static void main(String[] args) {
        RoleDao dao = new RoleDao();

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

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

등록

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.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);
    }

}

수정

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;

public class JDBCExam3 {

    public static void main(String[] args) {
        int roleId = 500;
        String description = "CEO";

        RoleDao dao = new RoleDao();
        int insertCount = dao.updateRole(roleId, description);

        System.out.println(insertCount);
    }

}

삭제

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;

public class JDBCExam4 {

    public static void main(String[] args) {
        int roleId = 500;

        RoleDao dao = new RoleDao();
        int insertCount = dao.deleteRole(roleId);

        System.out.println(insertCount);
    }

}

참조

https://www.boostcourse.org/web316/lecture/16734
https://www.boostcourse.org/web316/lecture/254309/
https://www.boostcourse.org/web316/lecture/254310/
https://www.boostcourse.org/web316/lecture/254312/

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

Servlet 버전별 작성 방법  (0) 2021.01.05
try - with - resources 문을 이용한 예외 처리  (0) 2020.12.21
Maven 환경설정  (0) 2020.12.21
redirect와 forward  (0) 2020.12.19
JSP(JavaServer Pages)  (0) 2020.12.17
댓글
Total
Today
Yesterday
링크
Apple 2023 맥북 프로 14 M3, 스페이스 그레이, M3 8코어, 10코어 GPU, 512GB, 8GB, 한글