JDBC 입문

java로 DB 이용하기

July 17, 2023

Tech Stack :
github :https://github.com/d-mario24/TeamGen/pull/28

목표 : 이전 과제의 결과물을 aws 서버에 1000번 계산하여 올리기
테이블 만들기

1
2
3
4
5
6
CREATE TABLE `jhy_team` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `team_member` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9296 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
cs

aws 서버에 연결 :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 private void insertDB(String Team) {
        Connection con = null;                                   // 데이터 베이스와 연결을 위한 객체
        PreparedStatement pstmt = null;                          // SQL 문을 데이터베이스에 보내기위한 객체
        // 1. JDBC Driver Class - com.mysql.jdbc.Driver
        String driver = "com.mysql.jdbc.Driver";
        // 2. 데이터베이스에 연결하기 위한 정보
        String db_name = "jhy";
        String db_url = "database-pd24.cqyjdb9okqzl.ap-northeast-2.rds.amazonaws.com";
        String db_port = "63306";
        String db_option = "useSSL=false&serverTimezone=Asia/Seoul&useUnicode=true&character_set_server=utf8mb4";
        String db_user = "pd24";                                     // 데이터베이스 ID
        String db_passwd = "dmario24.store";                                  // 데이터베이스 PW
        String db_conn_str = String.format("jdbc:mysql://%s:%s/%s?%s",db_url, db_port, db_name, db_option);
        String inset_sql = "insert into jhy.jhy_team(team_member) values(?)";
        try {
            // 1. JDBC 드라이버 로딩 - MySQL JDBC 드라이버의 Driver Class 로딩
            Class.forName(driver);
            // 2. Connection 생성 - .getConnection(연결문자열, DB-ID, DB-PW)
            con = DriverManager.getConnection(db_conn_str, db_user, db_passwd);
            // 3. PreParedStatement 객체 생성, 객체 생성시 SQL 문장 저장
            pstmt = con.prepareStatement(inset_sql);
            // 4. Pstmt.set<데이터타입>(? 순서, 값) ex).setString(), .setInt ...
            pstmt.setString(1, Team);
            // 5. SQL 문장을 실행하고 결과를 리턴 - SQL 문장 실행 후, 변경된 row 수 int type 리턴
            int r = pstmt.executeUpdate();
            // pstmt.excuteQuery() : select
            // pstmt.excuteUpdate() : insert, update, delete ..
            System.out.println("변경된 row : " + r);
        } catch (SQLException e) {
            System.out.println("[SQL Error : " + e.getMessage() + "]");
        } catch (ClassNotFoundException e1) {
            System.out.println("[JDBC Connector Driver 오류 : " + e1.getMessage() + "]");
        } finally {
            //사용순서와 반대로 close 함
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
cs

SQLite DB에 연결>
1
2
3
String driver = "org.sqlite.JDBC";
 
con = DriverManager.getConnection("jdbc:sqlite:" + "/home/tom/wsl/db/testdb/test.sqlite");
cs

결과:



코드 융합 : aws 와 sqlite DB에 선택적으로 접근 가능 하도록 수정

Go Back