[Java] 115. 코드 예제: JDBC를 활용하여 CRUD 해보기

[Java] 115. 코드 예제: JDBC를 활용하여 CRUD 해보기

1. Insert

2. Update

3. Delete

4. Select

---

1. Insert

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) {
		
		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="insert into employee values(?,?,?,?,now())";
			
			
			PreparedStatement preparedStatement=connection.prepareStatement(query);
			preparedStatement.setInt(1, 8);
			preparedStatement.setString(2, "가츠");
			preparedStatement.setString(3, "IT");
			preparedStatement.setString(4,"600000.00");
			
			int rowCount=preparedStatement.executeUpdate();
			System.out.println("rowCount : "+rowCount);
		
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
				
				
		// Connection 객체를 얻어서, insert 구문을 직접 만들어보자.
		// mydb2 사용, employee 테이블에 값을 넣는 코드를 작성하세요.
		
	} // end of main

}

 

 

2. Update

package ch03;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

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;

		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			connection = DriverManager.getConnection(url, user, password);

			String query = "SELECT * FROM employee WHERE id = ?";

			PreparedStatement preparedStatement = connection.prepareStatement(query);

			preparedStatement.setInt(1, 3);

			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				System.out.println("User ID: " + resultSet.getInt("id"));
				System.out.println("User Name: " + resultSet.getString("name"));
			}

		} catch (Exception e) {
		}

	} // end of main

}

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 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 name =? where id = ?";


            PreparedStatement preparedStatement = connection.prepareStatement(query);

            preparedStatement.setString(1, "그리피스");
            preparedStatement.setInt(2,1);


            int rowCount = preparedStatement.executeUpdate();
            System.out.println("rowCount :" + rowCount);
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		// Connection 객체를 얻어서, 수정 구문을 직접 만들어보세요.
		// mydb2 사용, employee 테이블에 값을 넣는 코드를 작성하세요.
		
	} // end of main
}

 

 

3. Delete

package ch03;

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

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("rowcount: "+rowCount);
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		

		// Connection 객체를 얻어서, 삭제 구문을 직접 만들어보세요.
		// mydb2 사용, employee 테이블에 값을 넣는 코드를 작성하세요.

	} // end of main
}

 

 

4. Select

package ch03;

import com.mysql.cj.jdbc.ConnectionGroupManager;

import ch01.DTO.Client;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SelectExample {
public static void main(String[] args) throws SQLException {
		
		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= "SELECT * from employee";
			PreparedStatement preparedStatement=connection.prepareStatement(query);
		
			resultSet=preparedStatement.executeQuery();
			
			ArrayList<Employee> employeeList=new ArrayList<>();
			
			while(resultSet.next()) {
				Employee employee=new Employee();
				employee.setName(resultSet.getString("name"));
				employee.setDepartment(resultSet.getString("department"));
				employee.setSalary(resultSet.getInt("Salary"));
				employeeList.add(employee);
				
				System.out.println("name:"+resultSet.getString("name"));
				System.out.println("department: "+resultSet.getString("department"));	
				System.out.println("salary: "+resultSet.getInt("Salary"));	
				System.out.println("");
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		// Connection 객체를 얻어서, 삭제 구문을 직접 만들어보세요.
		// mydb2 사용, employee 테이블의 값을 수정하는 코드를 작성하세요.
		
	} // end of main
}