2024.06.11 Data Structure(자료구조) JDBC 데이터 기본 조작(CRUD)

class_27.sql

class_jdbc -->> src -->> ch03 

class_27.sql

0.00MB

 

SelectExample.java

package ch03;

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

public class SelectExample {
	
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/mydb2?serverTimezone=Asia/Seoul";
		String user = "root"; // 상용서비스에서 절대 root 계정으로 사용 금지
		String password = "asd123";
		
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			connection = DriverManager.getConnection(url, user, password);
			
			statement = connection.createStatement();
			
			resultSet = statement.executeQuery("select * from employee");
			
			while(resultSet.next()) {
				System.out.println("ID : " + resultSet.getInt("id"));
				System.out.println("Name : " + resultSet.getString("name"));
				System.out.println("department : " + resultSet.getString("department"));
				System.out.println("salary : " + resultSet.getInt("salary"));
				System.out.println("hire_date : " + resultSet.getDate("hire_date"));
				System.out.println();
			}
		
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} // 외우기 
 catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}

 

InsertExample

package ch03;

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 InsertExample {
	
	public static void main(String[] args) {
		
		// Connection 객체를 얻어서 insert 부문을 직접 만들어 보세요.
		// mydb2 사용, employee 테이블에 값을 넣는 코드를 작성하세요.
		
		String url;
		String user;
		String password;
		
		url = "jdbc:mysql://localhost:3306/mydb2?serverTimezone=Asia/Seoul";
		user = "root";
		password = "asd123";
		
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			connection = DriverManager.getConnection(url, user, password);
			
			String query = "insert into employee values (?, ?, ?, ?, now())";
			PreparedStatement preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, 10);
			preparedStatement.setString(2, "세종");
			preparedStatement.setString(3, "IT");
			preparedStatement.setString(4, "4500000.00");
			
			int rowCount = preparedStatement.executeUpdate();
			System.out.println("rountCount : " +rowCount);
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	} // end of main

} // end of class

 

UpdateExample

package ch03;

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 UpdateExample {
	
public static void main(String[] args) {
		
		String url = "jdbc:mysql://localhost:3306/mydb2?serverTimezone=Asia/Seoul";
		String user = "root";
		String password = "asd123";
		
		// Connection 객체를 얻어서 수정 부문을 직접 만들어 보세요.
		// mydb2 사용, employee 테이블에 값을 넣는 코드를 작성하세요. 
		
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			connection = DriverManager.getConnection(url, user, password);
			
			String query = "Update employee set department = ? where name = ?;";
			PreparedStatement preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, "기획부");
			preparedStatement.setString(2, "이순신");
			
			int rowCount = preparedStatement.executeUpdate();
			System.out.println("rowCount : " +rowCount);
			
			
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
	} // end of main
	

}

 

DeleteExample

package ch03;

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 DeleteExample {
	
	public static void main(String[] args) {
		
		String url = "jdbc:mysql://localhost:3306/mydb2?serverTimezone=Asia/Seoul";
		String user = "root";
		String password = "asd123";
		
		Connection connection = null; // 연결
		Statement statement = null; // 실행
		ResultSet resultSet = null; // 결과
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			connection = DriverManager.getConnection(url, user, password);
			
			String query = "delete from employee where name = ?";
			PreparedStatement preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, "세종");
			
			int rowCount = preparedStatement.executeUpdate();
			System.out.println("rountCount : " + rowCount);
			
			
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
	} // end of main

} // end of class