[JDBC] 1. BankApp

Jan 22, 2024

Contents

제 작업 위치 입니다.

dbapp이라는 프로젝트, src - main - java 에 폴더 셋을 만들고. test에 동일한 폴더를 만듭니다. 프로젝트를 깃헙에 올리고 싶으면 git remote add orgin repository_add하기

Convention입니다. 💡개발자들 사이에서 코딩 스타일과 규칙을 일관되게 지키기 위해 정해진 약속이나 규약

클래스 뒤엔 Test, 메서드엔 _test를 붙여줍니다.

INCERT 쿼리 사용

DBConnection 클래스

DB를 쓰려면 먼저 연결부터 해야합니다. 루프백 (localhost or 127.0.0.1)을 이용하고, mariadb의 포트인 3306을 입력합니다.


package db;

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

public class DBConnection {

    public static Connection getInstance(){
        String username = "root";
        String password = "1234";
        String url = "jdbc:mariadb://127.0.0.1:3306/cosdb";
        // Protocal이 적용된 Socket
        try {
            Connection conn = DriverManager.getConnection(url, username, password);
            System.out.println("db connect success");
            return conn;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

BankApp 클래스 ※main 클래스입니다.


import db.DBConnection;

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

public class BankApp {
    public static void main(String[] args) {
        Connection conn = DBConnection.getInstance();

        // 얘가 buffer입니다.
        try {
            PreparedStatement pstmt = conn.prepareStatement(
							"INSERT INTO account_tb(password, balance, created_at) values(?, ?, now())");
										         		// number값은 AUTO_INCREMENT입니다   password, blance값을 넣습니다.
            pstmt.setString(1,"1234");
            pstmt.setInt(2,1000);

            int num = pstmt.executeUpdate(); 
            System.out.println(num);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

PreparedStatement는 매개변수화 시켜 쿼리를 실행합니다. JDBC API에서 제공하는 인터페이스입니다. executeUpdate() : 변경된 행의 수를 반환하는 코드입니다. 1 ⇒ 변경 ⭕ / 0 ⇒ 실행은 됐지만 변경 ❌ :

메인클래스를 실행시키고 database 확인

다른 Query 사용하기

DELETE


try {
		int number = 1;   
    PreparedStatement pstmt = conn.prepareStatement("DELETE FROM account_tb WHERE number = ?");
    pstmt.setInt(1);

    int num = pstmt.executeUpdate(); 
    System.out.println("삭제되었습니다.");

} catch (Exception e) {
    throw new RuntimeException(e);
}

UPDATE


try {
    PreparedStatement pstmt = conn.prepareStatement("UPDATE account_tb SET balance = ? WHERE account_id = ?");
    pstmt.setInt(1, newBalance);
    pstmt.setInt(2, accountId);

    int num = pstmt.executeUpdate(); 
    System.out.println(num + "개의 레코드가 업데이트되었습니다.");

} catch (Exception e) {
    throw new RuntimeException(e);
}

SELECT


try {
    PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM account_tb WHERE number = ?");
		pstmt.setInt(1, number);

    ResultSet rs = pstmt.executeQuery();

    while (rs.next()) {
        int number = rs.getInt("number");
        String password = rs.getString("password");
        int balance = rs.getInt("balance");
        Timestamp createdAt = rs.getDate("created_at");

        System.out.println(number + password + balance + createdAt);
    }

} catch (Exception e) {
    throw new RuntimeException(e);
}

package db; import java.sql.Connection; import java.sql.DriverManager; public class DBConnection { public static Connection getInstance(){ String username = "root"; String password = "1234"; String url = "jdbc:mariadb://127.0.0.1:3306/cosdb"; // Protocal이 적용된 Socket try { Connection conn = DriverManager.getConnection(url, username, password); System.out.println("db connect success"); return conn; } catch (Exception e) { throw new RuntimeException(e); } } }

import db.DBConnection; import java.sql.Connection; import java.sql.PreparedStatement; public class BankApp { public static void main(String[] args) { Connection conn = DBConnection.getInstance(); // 얘가 buffer입니다. try { PreparedStatement pstmt = conn.prepareStatement( "INSERT INTO account_tb(password, balance, created_at) values(?, ?, now())"); // number값은 AUTO_INCREMENT입니다 password, blance값을 넣습니다. pstmt.setString(1,"1234"); pstmt.setInt(2,1000); int num = pstmt.executeUpdate(); System.out.println(num); } catch (Exception e) { throw new RuntimeException(e); } } }

try { int number = 1; PreparedStatement pstmt = conn.prepareStatement("DELETE FROM account_tb WHERE number = ?"); pstmt.setInt(1); int num = pstmt.executeUpdate(); System.out.println("삭제되었습니다."); } catch (Exception e) { throw new RuntimeException(e); }

try { PreparedStatement pstmt = conn.prepareStatement("UPDATE account_tb SET balance = ? WHERE account_id = ?"); pstmt.setInt(1, newBalance); pstmt.setInt(2, accountId); int num = pstmt.executeUpdate(); System.out.println(num + "개의 레코드가 업데이트되었습니다."); } catch (Exception e) { throw new RuntimeException(e); }

try { PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM account_tb WHERE number = ?"); pstmt.setInt(1, number); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { int number = rs.getInt("number"); String password = rs.getString("password"); int balance = rs.getInt("balance"); Timestamp createdAt = rs.getDate("created_at"); System.out.println(number + password + balance + createdAt); } } catch (Exception e) { throw new RuntimeException(e); }