230705 - JDBC

머홍머홍·2023년 7월 5일

내용정리

목록 보기

9/10

JDBC 개발환경 구축

workspace/db_lab에서 javadb 프로젝트 생성
코끼리 눌러야 다운받아서 반영됨.
롬복 설치

JDBC 실습

Gradle에서 Dependency 설정

dependencies {
    // https://mvnrepository.com/artifact/org.projectlombok/lombok
    compileOnly group: 'org.projectlombok', name: 'lombok', version: '1.18.28'
    annotationProcessor group: 'org.projectlombok', name: 'lombok', version: '1.18.28'
    implementation 'com.mysql:mysql-connector-j:8.0.32'
    testImplementation platform('org.junit:junit-bom:5.9.1')
    testImplementation 'org.junit.jupiter:junit-jupiter'
}

DBConnection.java 생성

MariaDB

MVN에서 MariaDB 검색후 Dependency에 설정.

DB연결하기 위한 작업(모델링)

DBConnection.java

package db;

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

public class DBConnection {
	
	public static Connection getInstance() {
		// MySQL 연결 정보
		String url = "jdbc:mysql://localhost:3306/metadb";
		String username = "root";
		String password = "root1234";
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			Connection connection = DriverManager.getConnection(url, username, password);
			System.out.println("DB Connection OK");
			return connection;
		} catch (Exception e) {
			System.out.println("DB Connection Fail" + e.getMessage());
		}
		
		return null;
	}
	
	public static void main(String[] args) {
		getInstance();
	}
}

AccountDAO.java 구현

practice 패키지

AccountInsert.java

public class AccountInsert {
	public static void main(String[] args) {
		// 1. DB 연결
		Connection conn = DBConnection.getInstance();
		
		// 2. 버퍼로 SQL 쓰기
		String sql = "insert into account_tb(account_number, account_password, account_created_at) values(?, ?, ?, now())";
		try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, 1111);
			statement.setString(2, "1234");
			statement.setInt(3, 1000);
			
			int result = statement.executeUpdate();  // flush (변경된 row 카운트를 응답)
			System.out.println("결과:" + result);
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		
	}
}

AccountUpdate.java

public class AccountUpdate {
	
	public static void main(String[] args) {
		// 1. DB 연결
		Connection conn = DBConnection.getInstance();
		
		// 2. 버퍼로 SQL 쓰기
		try {
			String sql = "update account_tb set account_balance = ? where account_number = ?";
			
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, 900);   // balance(잔액) 수정
			statement.setInt(2,  1111);

			int result = statement.executeUpdate(); // flush (변경된 row 카운트를 응답)
			System.out.println("결과 : " + result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

AccountDelete.java

public class AccountDelete {
	
	public static void main(String[] args) {
		// 1. DB 연결
		Connection conn = DBConnection.getInstance();
		
		// 2. 버퍼로 SQL 쓰기
		try {
			String sql = "delete from account_tb where account_number = ?";
			
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, 1111);   // account_number가 1111인 행(튜플) 삭제
			
			int result = statement.executeUpdate(); // flush (변경된 row 카운트를 응답)
			System.out.println("결과 : " + result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

AccountDAO


// DB 접근 미들웨어 (Java가 이 메소드를 통해서 DB로 간다)
// SRP (Single Responsibility Principle), 단일 책임의 원칙
// Data Access Object
public class AccountDAO {
	
	public void insert(int account_number, String account_password, int account_balance) {
		// 1. DB 연결
		Connection conn = DBConnection.getInstance();
		
		// 2. 버퍼로 SQL 쓰기
		try {
			String sql = "insert into account_tb(account_number, account_password, account_balance, account_created_at) values(?,?,?,now())";
			// StringBuilder sql = new StringBuilder();
			// sql.append("insert into account_tb");
			// sql.append("(account_number, account_password, account_balance, account_created_at) ");
			// sql.append("values(?,?,?,now())");
			// sql은 문자열이 아니므로 문자열로 바꿔주는 sql.toString()을 사용해야 함.
			
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, account_number);
			statement.setString(2, account_password);
			statement.setInt(3, account_balance);
			
			int result = statement.executeUpdate(); // flush (변경된 row 카운트를 응답)
			System.out.println("결과 : " + result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void update(int account_balance, int account_number) {
		Connection conn = DBConnection.getInstance();
		try {
			String sql = "update account_tb set account_balance = ? where account_number = ?";
			
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, account_balance);   // balance(잔액) 수정
			statement.setInt(2, account_number);
			
			int result = statement.executeUpdate(); // flush (변경된 row 카운트를 응답)
			System.out.println("결과 : " + result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void delete(int account_number) {
		// 1. DB 연결
		Connection conn = DBConnection.getInstance();
		
		// 2. 버퍼로 SQL 쓰기
		try {
			String sql = "delete from account_tb where account_number = ?";
			
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, account_number);   // account_number가 1111인 행(튜플) 삭제
			
			int result = statement.executeUpdate(); // flush (변경된 row 카운트를 응답)
			System.out.println("결과 : " + result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

AccountSelectOne.java

public class AccountSelectOne {
	
	public static void main(String[] args) {
		Account account = null;
		// DB 연결
		Connection conn = DBConnection.getInstance();
		
		try {
			String sql = "select * from account_tb where account_number = ?";
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, 2222);
			ResultSet rs = statement.executeQuery();
			
			if (rs.next()) {    // DBMS 결과창의 커서를 1칸 내린다.
				account = new Account(
					rs.getInt("account_number"),            // number
					rs.getString("account_password"),       // password
					rs.getInt("account_balance"),           // balance
					rs.getTimestamp("account_created_at")   // createdat
				);
			}
			System.out.println("계좌번호: " + account.getAccountNumber());
			System.out.println("계좌비번: " + account.getAccountPassword());
			System.out.println("계좌잔액: " + account.getAccountBalance());
			System.out.println("계좌생성일: " + account.getAccountCreatedAt());
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}