[JAVA] JDBC MySQL 연동 및 CRUD 테이블 작성

 

1. Java 프로젝트 MySQL 연동

1. JDBC 드라이버 설치 : https://dev.mysql.com/downloads/connector/j/

MySQL :: Download Connector/J

 

2. JDBC 파일 압축 해제 후 Java 프로젝트 우클릭 - Properties - Java Build Path - Libraries 에 mysql-connector-java-버전.jar 추가

 

3. Java 프로젝트 실행

 

 

2. CRUD 프로그램 작성 (Insert, Select, Update, Delete)

DB명 : DB01

테이블명 : TABLE01

VALUE	TYPE
fullname	varchar
age	int
salary	int

---

 

1. 숫자를 입력하면 각 숫자에 맞는 함수가 실행되도록 프로그램 작성

package testdb;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
import java.sql.SQLException;
import java.sql.PreparedStatement;

public class Main {
	static String jdbc_driver = "com.mysql.cj.jdbc.Driver";
	static String jdbc_url = "jdbc:mysql://localhost:3306/DB01";

	public static void main(String[] args) throws ClassNotFoundException {
		while(true) {
			System.out.print("숫자를 입력하세요 : ");
			Scanner scanner = new Scanner(System.in);
			int n = scanner.nextInt();
			System.out.println(n);
			if(n==5) {
				System.out.println("종료되었습니다.");
				break;
			}
			switch(n) {
			case 1:
				insert();
				break;
			case 2:
				select();
				break;
			case 3:
				update();
				break;
			case 4:
				delete();
				break;
			default:
				break;
			}
		}
	}

}

 

2. INSERT

public static void insert() {
		Connection con = null;
		PreparedStatement pstmt = null;
		
		System.out.println("Insert 쿼리");
		
		try {
			Class.forName(jdbc_driver);
			con = DriverManager.getConnection(jdbc_url, "root", "1234");
			String sql = "INSERT INTO TABLE01 VALUES (?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "하마");
			pstmt.setInt(2, 10);
			pstmt.setInt(3, 2000);
			pstmt.executeUpdate();
			int count = pstmt.executeUpdate();

			if(count == 0){
				System.out.println("입력 실패");
			}
			else{
				System.out.println("입력 성공");
			}
		} 
		catch( ClassNotFoundException e){
			System.out.println("오류");
		}

		catch( SQLException e){
			System.out.println("오류" + e);
		}

		finally{
			try{
				if(con != null && !con.isClosed()){
					con.close();
				}
				if(pstmt != null && !pstmt.isClosed()){
					pstmt.close();
				}
			}
			catch( SQLException e){
				e.printStackTrace();
			}
		}

	}

• Connection : 데이터베이스와 연결하는 객체
• DriverManager : JDBC 드라이버를 통해 Connection을 만드는 역할
• PreparedStatement : SQL 쿼리문을 데이터베이스에 보내기 위한 객체
• Statement : SQL 쿼리문을 데이터베이스에 보내기 위한 객체

PreparedStatement	Statement
- SQL 쿼리문을 데이터베이스에 보내기 위한 객체 - try catch 문으로 예외 처리해줘야 함
- 동적인 쿼리문에 적합 - 값 변경하거나 파라미터가 많은 경우에 적합 - INSERT, UPDATE, DELETE	- 정적인 쿼리문에 적합 - SELECT

 

3. SELECT

public static void select() throws ClassNotFoundException {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		System.out.println("Select 쿼리");
		

		try {
			Class.forName(jdbc_driver);
			con = DriverManager.getConnection(jdbc_url, "root", "1234");
			String sql = "SELECT * FROM TABLE01";
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);

			while(rs.next()) {
				String name = rs.getString("FULLNAME");
				int age = rs.getInt("AGE");
				int salary = rs.getInt("SALARY");

				System.out.println(name + " " + age + " " + salary);
			}
		} 
		catch (SQLException e) { 
			System.out.println("오류 : " + e.getMessage()); 
		}

		catch (ClassNotFoundException e1) { 
			System.out.println("오류 : " + e1.getMessage()); 
		}
		finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

 

4. UPDATE

public static void update() {
		Connection con = null;
		PreparedStatement pstmt = null;

		System.out.println("Update 쿼리");
		
		try {
			Class.forName(jdbc_driver);
			con = DriverManager.getConnection(jdbc_url, "root", "1234");
			String sql = "UPDATE TABLE01 SET FULLNAME=?, AGE=? WHERE SALARY=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "메뚜기");
			pstmt.setInt(2, 20);
			pstmt.setInt(3, 4000);
			int count = pstmt.executeUpdate();
			System.out.println(pstmt);
			System.out.println("변경된 row : " + count);

		} 
		catch (SQLException e) { 
			System.out.println("오류 : " + e.getMessage()); 
		}

		catch (ClassNotFoundException e1) { 
			System.out.println("오류 : " + e1.getMessage()); 
		}
		finally {
			if(pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

 

5. DELETE

public static void delete() {
		Connection con = null;
		PreparedStatement pstmt = null;
		
		System.out.println("Delete 쿼리");
		
		try {
			Class.forName(jdbc_driver);
			con = DriverManager.getConnection(jdbc_url, "root", "1234");
			String sql = "DELETE FROM TABLE01 WHERE FULLNAME=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "메뚜기");
			int count = pstmt.executeUpdate();
			System.out.println(pstmt);
			System.out.println("변경된 row : " + count);

		} 
		catch (SQLException e) { 
			System.out.println("오류 : " + e.getMessage()); 
		}

		catch (ClassNotFoundException e1) { 
			System.out.println("오류 : " + e1.getMessage()); 
		}
		finally {
			if(pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}