TheData.KR > Java > jdbc

jdbc

JDBC(Java DataBase Connectivity)는 데이터베이스에 연결 및 작업을 하기 위한 자바 표준 인터페이스

JDBC 프로그래밍 코딩 순서

JDBC Driver 로드
DB 연결
DB에 데이터를 읽거나 쓰기 (SQL문)
DB 연결 종료

이미지출처 : https://sassun.tistory.com/39

JDBC 드라이버

자바 프로그램과 각 DBMS에 연결하기 위한 라이브러리(jar)
DBMS 회사에서 만들어 제공됨

드라이버 사용법

드라이버 동적로딩 : Class.forName("JDBC Driver 이름");
MySQL : com.mysql.jdbc.Driver(5.xxx), com.mysql.cj.jdbc.Driver(8.xxx)
Oracle : oracle.jdbc.driver.OracleDriver
MSSQL : com.microsoft.sqlserver.jdbc.SQLServerDriver

JDBC URL

DBMS와의 연결을 위한 주소
JDBC Driver에 따라 형식이 다름

DBMS별 URL

MySQL : jdbc:mysql://localhost:3306/dbname?serverTimezone=UTC
Oracle : jdbc:oracle:thin:@localhost:1521:SID값
MSSQL : jdbc:sqlserver://localhost:1433;DatabaseName=DB

JDBC 드라이버 Build Path 설정

Java Project > Properties > Java Build Path > Libraies > Add External JARS > 드라이버파일 등록

DBMS 테스트

Connection 생성

db 연결 확인
DriverManager 객체의 getConnection() 메서드에서 리턴 받음
url의 접속 dbname 확인

package dbtest;

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

public class ConnectionTest {

    public static void main(String[] args) {

        // 0.필요한 객체 및 변수 생성
        Connection conn = null; 

        try {
            // 1. driver loading
            String driver = "com.mysql.cj.jdbc.Driver";
            Class.forName(driver);

            // 2.connection 
            String url = "jdbc:mysql://localhost:3306/bigdata?ServerTimezone=UTC";
            String user = "root";
            String password = "rootpass";
            conn = DriverManager.getConnection(url,user,password);
            System.out.println("Conn OK!!");


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

PreparedStatement 생성 및 sql 입력

Connection 객체의 prepareStatement() 메서드로 생성
prepareStatement(sql)에 sql문 인자로 전달

package dbtest;

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

public class ConnectionTest {

    public static void main(String[] args) {

        // 0.필요한 객체 및 변수 생성
        Connection conn = null; // db연결 객체 
        PreparedStatement pstmt = null; // sql query 창

        try {
            // 1. driver loading
            String driver = "com.mysql.cj.jdbc.Driver";
            Class.forName(driver);

            // 2.connection 
            String url = "jdbc:mysql://localhost:3306/bigdata?ServerTimezone=UTC";
            String user = "root";
            String password = "rootpass";
            conn = DriverManager.getConnection(url,user,password);
            System.out.println("Conn OK!!");

            // 3. PreparedStatement
            String sql = "select id,pw,name,age from member";
            pstmt = conn.prepareStatement(sql);


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

ResultSet : select 결과 저장

PreparedStatement객체의 executeQuery() 메서드 사용

package dbtest;

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

public class ConnectionTest {

    public static void main(String[] args) {

        // 0.필요한 객체 및 변수 생성
        Connection conn = null; // db연결 객체 
        PreparedStatement pstmt = null; // sql query 창
        ResultSet rs = null; // select 결과 저장

        try {
            // 1. driver loading
            String driver = "com.mysql.cj.jdbc.Driver";
            Class.forName(driver);

            // 2.connection 
            String url = "jdbc:mysql://localhost:3306/bigdata?ServerTimezone=UTC";
            String user = "root";
            String password = "rootpass";
            conn = DriverManager.getConnection(url,user,password);
            System.out.println("Conn OK!!");

            // 3. PreparedStatement
            String sql = "select id,pw,name,age from member";
            pstmt = conn.prepareStatement(sql);

            // 4. ResultSet : select 결과 저장
            rs = pstmt.executeQuery();

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

ResultSet에서 데이터 꺼내기

ResultSet : getXXX(컬럼명)
스트링컬럼 : getString("컬럼명")
정수형컬럼 : getInt("컬럼명")
날짜형컬럼 : getDate("컬럼명")
List 에 담기

package dbtest;

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

public class ConnectionTest {

    public static void main(String[] args) {

        // 0.필요한 객체 및 변수 생성
        Connection conn = null; // db연결 객체 
        PreparedStatement pstmt = null; // sql query 창
        ResultSet rs = null; // select 결과 저장

        try {
            // 1. driver loading
            String driver = "com.mysql.cj.jdbc.Driver";
            Class.forName(driver);

            // 2.connection 
            String url = "jdbc:mysql://localhost:3306/bigdata?ServerTimezone=UTC";
            String user = "root";
            String password = "rootpass";
            conn = DriverManager.getConnection(url,user,password);
            System.out.println("Conn OK!!");

            // 3. PreparedStatement
            String sql = "select id,pw,name,age from member";
            pstmt = conn.prepareStatement(sql);

            // 4. ResultSet : select 결과 저장
            rs = pstmt.executeQuery();

            // 5.  ResultSet : getXXX(컬럼명)
            List members = new ArrayList();
            MemberVo member = null;
            while(rs.next()) {
                String id = rs.getString("id");
                String pw = rs.getString("pw");
                String name = rs.getString("name");
                int age= rs.getInt("age");
                member = new MemberVo(id, pw, name, age);
                members.add(member);
            }

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

insert 작업

PreparedStatement객체의 executeUpdate() 메서드 사용
sql 문장에 값이 되는 부분은 ?로 처리
PreparedStatement객체의 setXXX(값)메서드로 값 셋팅
스트링값 : setString(값)
정수형값 : setInt(값)

package dbtest;

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

public class InsertTest {

    public static void main(String[] args) {
        // 1. driver loading
        String driver = "com.mysql.cj.jdbc.Driver";
        // 2. connection
        Connection conn = null;
        // 3. sql 쿼리창 
        PreparedStatement pstmt = null;
        // 4. 실행 - insert 
        int rs = 0;
        String id = "hong7";
        String pw = "7777";
        String name = "홍7";
        int age = 7;
        String sql = "insert into member(id,pw,name,age) values(?,?,?,?)";

        try {
            Class.forName(driver);
            String url = "jdbc:mysql://localhost:3306/bigdata?ServerTimezone=UTC";
            String user = "root";
            String password = "rootpass";
            conn = DriverManager.getConnection(url, user, password);
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            pstmt.setString(2, pw);
            pstmt.setString(3, name);
            pstmt.setInt(4, age);
            rs = pstmt.executeUpdate();
            System.out.println("rs="+rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt != null) pstmt.close();
                if(conn != null) conn.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }


    }

}

update 작업

PreparedStatement객체의 executeUpdate() 메서드 사용
sql 문장에 값이 되는 부분은 ?로 처리

package dbtest;

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

public class UpdateTest {

    public static void main(String[] args) {
        // 1. driver loading
        String driver = "com.mysql.cj.jdbc.Driver";
        // 2. connection
        Connection conn = null;
        // 3. sql 쿼리창 
        PreparedStatement pstmt = null;
        // 4. 실행 - update 
        int rs = 0;
        String id = "hong7";
        String pw = "7979";
        String name = "홍79";
        int age = 79;
        String sql = "update member set pw=?,name=?,age=? whdddere id=?";

        try {
            Class.forName(driver);
            String url = "jdbc:mysql://localhost:3306/bigdata?ServerTimezone=UTC";
            String user = "root";
            String password = "rootpass";
            conn = DriverManager.getConnection(url, user, password);
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, pw);
            pstmt.setString(2, name);
            pstmt.setInt(3, age);
            pstmt.setString(4, id);
            rs = pstmt.executeUpdate();
            System.out.println("rs="+rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt != null) pstmt.close();
                if(conn != null) conn.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }
}

delete 작업

PreparedStatement객체의 executeUpdate() 메서드 사용
sql 문장에 값이 되는 부분은 ?로 처리

package dbtest;

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

public class DeleteTest {

    public static void main(String[] args) {
        // 1. driver loading
        String driver = "com.mysql.cj.jdbc.Driver";
        // 2. connection
        Connection conn = null;
        // 3. sql 쿼리창 
        PreparedStatement pstmt = null;
        // 4. 실행 - delete
        int rs = 0;
        String id = "hong7";
        String sql = "delete from member where id=?";

        try {
            Class.forName(driver);
            String url = "jdbc:mysql://localhost:3306/bigdata?ServerTimezone=UTC";
            String user = "root";
            String password = "rootpass";
            conn = DriverManager.getConnection(url, user, password);
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeUpdate();
            System.out.println("rs="+rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt != null) pstmt.close();
                if(conn != null) conn.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }
}

DAO(Data Access Object) 만들기

데이터베이스 연동 처리
공통적으로 사용되는 Connection과 Close는 별도 클래스 생성
각각의 CRUD작업을 DAO 객체의 메서드로 생성

이미지출처 : https://gmlwjd9405.github.io/2018/05/15/setting-for-db-programming.html

JDBCUtil.java

package dbtest;

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

public class JDBCUtil {

    public static Connection getConnection() {

        String driver = "com.mysql.cj.jdbc.Driver";
        Connection conn = null;
        try {
            // 1. driver loading
            Class.forName(driver);
            // 2.conn
            String url = "jdbc:mysql://localhost:3306/bigdata?ServerTimezone=UTC";
            String user = "root";
            String password = "rootpass";
            conn = DriverManager.getConnection(url,user,password);
            System.out.println("Conn OK!!");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void close(PreparedStatement pstmt, Connection conn) {
        try {
            if(pstmt != null) pstmt.close();
            if(conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(ResultSet rs, PreparedStatement pstmt, Connection conn) {
        try {
            if(rs != null) rs.close();
            if(pstmt != null) pstmt.close();
            if(conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}

MemberDAO.java

package dbtest;

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

public class MemberDAO {
    // db 객체
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;

    //sql
    private final String MEMBER_LIST = "select id,pw,name,age from member order by idx desc";
    private final String MEMBER_GET_ID = "select id,pw,name,age from member where id = ?";
    private final String MEMBER_INSERT = "insert into member(id,pw,name,age) values (?,?,?,?)";
    private final String MEMBER_UPDATE = "update member set pw=?,name=?,age=? where id=?";
    private final String MEMBER_DELETE = "delete from member where id = ?";

    public List getMemberList(){
        List members = new ArrayList();
        MemberVo member = null;
        conn = JDBCUtil.getConnection();
        try {
            pstmt = conn.prepareStatement(MEMBER_LIST);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                String id = rs.getString("id");
                String pw = rs.getString("pw");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                //System.out.printf("%s,%s,%s,%s%n",id,pw,name,age);
                member = new MemberVo(id, pw, name, age);
                members.add(member);                
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(rs, pstmt, conn);
        }       
        return members;     
    }

    public MemberVo getMemberId(String id){
        MemberVo member = null;
        conn = JDBCUtil.getConnection();
        try {
            pstmt = conn.prepareStatement(MEMBER_GET_ID);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            if(rs.next()) {
                String pw = rs.getString("pw");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                //System.out.printf("%s,%s,%s,%s%n",id,pw,name,age);
                member = new MemberVo(id, pw, name, age);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(rs, pstmt, conn);
        }       
        return member;      
    }

    public int insertMember(MemberVo m){
        int rs = 0;
        conn = JDBCUtil.getConnection();
        try {
            pstmt = conn.prepareStatement(MEMBER_INSERT);
            pstmt.setString(1, m.getId());
            pstmt.setString(2, m.getPw());
            pstmt.setString(3, m.getName());
            pstmt.setInt(4, m.getAge());
            rs = pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(pstmt, conn);
        }       
        return rs;      
    }

    public int updateMember(MemberVo m){
        int rs = 0;
        conn = JDBCUtil.getConnection();
        try {
            pstmt = conn.prepareStatement(MEMBER_UPDATE);
            pstmt.setString(1, m.getPw());
            pstmt.setString(2, m.getName());
            pstmt.setInt(3, m.getAge());
            pstmt.setString(4, m.getId());
            rs = pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(pstmt, conn);
        }       
        return rs;      
    }

    public int deleteMember(String id){
        int rs = 0;
        conn = JDBCUtil.getConnection();
        try {
            pstmt = conn.prepareStatement(MEMBER_DELETE);
            pstmt.setString(1, id);
            rs = pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(pstmt, conn);
        }

        return rs;

    }   
}

DAO 사용

Select ALL

package dbtest;

import java.util.List;

public class SelectAllTest2 {

    public static void main(String[] args) {

        // member list
        MemberDAO dao = new MemberDAO();
        List members = dao.getMemberList();
        for(MemberVo m:members) {
            System.out.println(m);
        }

    }

}

Select One

package dbtest;

public class SelectTest2 {

    public static void main(String[] args) {
        String id = "hong1";
        MemberVo m = null;

        MemberDAO dao = new MemberDAO();
        m = dao.getMemberId(id);

        System.out.println(m.getId());

    }

}

Insert

package dbtest;

public class InsertTest2 {

    public static void main(String[] args) {

        int rs = 0;
        String id = "hong7";
        String pw = "7777";
        String name = "홍7";
        int age = 7;

        MemberDAO dao = new MemberDAO();
        MemberVo m = new  MemberVo(id, pw, name, age);
        rs = dao.insertMember(m);
        System.out.println("rs=" + rs);

    }

}

Update

package dbtest;

public class UpdateTest2 {

    public static void main(String[] args) {

        int rs = 0;
        String id = "hong7";
        String pw = "7979";
        String name = "홍79";
        int age = 79;

        MemberDAO dao = new MemberDAO();
        MemberVo m = new MemberVo(id, pw, name, age);
        rs = dao.updateMember(m);
        System.out.println("rs = "+ rs);


    }

}

Delete

package dbtest;

public class DeleteTest2 {

    public static void main(String[] args) {

        int rs = 0;
        String id = "hong7";

        MemberDAO dao = new MemberDAO();
        rs = dao.deleteMember(id);
        System.out.println("rs = " + rs);


    }

}