JDBC를 통해 데이터 입력, 삭제, 조회하기

 

JDBC란 무엇인가

JDBC란 무엇인가?

 

 

 

---

1. DB 구조

DB 구조

데이터 예시

 

 

Role.java

package kr.or.connect.jdbcexam.dto;

public class Role {
	private Integer roleId;
	private String description;
	
	public Integer getRoleId() {
		return roleId;
	}
	public void setRoleId(Integer roleId) {
		this.roleId = roleId;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	
	@Override
	public String toString() {
		return "Role [roleId=" + roleId + ", description=" + description + "]";
	}
	
	public Role(Integer roleId, String description) {
		super();
		this.roleId = roleId;
		this.description = description;
	}

}

 

---

1. 입력하기

 

RoleDao.java에서의 addRole 메서드

package kr.or.connect.jdbcexam.dao;

import kr.or.connect.jdbcexam.dto.Role;
import java.sql.*;


public class RoleDao {
	
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb?serverTimezone=Asia/Seoul&useSSL=false";
	private static String dbUser = "root";
	private static String dbPassword = "";
	
	public int addRole(Role role) {
		int insertCount = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(dburl, dbUser, dbPassword);
			String sql = "INSERT INTO role(role_id, description) VALUES (?,?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, role.getRoleId());
			ps.setString(2, role.getDescription());
			
			insertCount = ps.executeUpdate();
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		} finally {
			
			if(ps != null) {
				try {
					ps.close();
				} catch(Exception e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch(Exception e) {
					e.printStackTrace();
				}
			}
			
		}
		
		return insertCount;
	}
    
}

 

JDBCExam2.java

package kr.or.connect.jdbcexam;

import java.util.Scanner;
import kr.or.connect.jdbcexam.dto.Role;
import kr.or.connect.jdbcexam.dao.RoleDao;

public class JDBCExam2 {

	public static void main(String args[]) {
		
		Scanner scanner = new Scanner(System.in);
		System.out.printf("추가하고자 하는 역할 Id를 입력하시오: ");
		int role_id = scanner.nextInt();
		System.out.printf("추가하고자 하는 %d번 역할의 설명을 입력하시오: ", role_id);
		String description = scanner.next();
		
		Role role = new Role(role_id, description);
		
		RoleDao roleDao = new RoleDao();
		int insertCount = roleDao.addRole(role);
		System.out.println("실행결과: " + insertCount);
		
	}
	
}

 

 

 

---

2. 삭제하기

 

RoleDao.java에서의 deleteRole 메서드

package kr.or.connect.jdbcexam.dao;

import kr.or.connect.jdbcexam.dto.Role;
import java.sql.*;


public class RoleDao {
	
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb?serverTimezone=Asia/Seoul&useSSL=false";
	private static String dbUser = "root";
	private static String dbPassword = "";
	
	public int deleteRole(int role_id) {
		int deleteCount = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(dburl, dbUser, dbPassword);
			String sql = "DELETE FROM role WHERE role_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, role_id);
			
			deleteCount = ps.executeUpdate();
			
		}catch(Exception e) {
			
			e.printStackTrace();
			
		} finally {
			
			if(ps != null) {
				try {
					ps.close();
				} catch(Exception e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch(Exception e) {
					e.printStackTrace();
				}
			}
			
		}
		
		return deleteCount;
	}
	
}

 

JDBCExam3.java

package kr.or.connect.jdbcexam;

import java.util.Scanner;
import kr.or.connect.jdbcexam.dto.Role;
import kr.or.connect.jdbcexam.dao.RoleDao;

public class JDBCExam3 {

	public static void main(String args[]) {
		
		Scanner scanner = new Scanner(System.in);
		System.out.printf("삭제하고자 하는 역할의 Id를 입력하시오: ");
		int role_id = scanner.nextInt();
		
		RoleDao roleDao = new RoleDao();
		int insertCount = roleDao.deleteRole(role_id);
		System.out.println("실행결과: " + insertCount);
		
	}
	
}

 

 

 

---

3. 조회하기

 

RoleDao.java에서의 getRole 메서드

package kr.or.connect.jdbcexam.dao;

import kr.or.connect.jdbcexam.dto.Role;
import java.sql.*;


public class RoleDao {
	
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb?serverTimezone=Asia/Seoul&useSSL=false";
	private static String dbUser = "root";
	private static String dbPassword = "";
	
	public Role getRole(Integer role_id) {
		
		Role role = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try	{
			
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(dburl, dbUser, dbPassword);
			String sql = "SELECT role_id, description FROM role WHERE role_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, role_id);
			rs = ps.executeQuery();
			
			if(rs.next()) {
				Integer id = rs.getInt("role_id");
				String description = rs.getString("description");
				role = new Role(id, description);
			}
			
			
		} catch(Exception e) {
			
			e.printStackTrace();
			
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch(Exception e) {
					e.printStackTrace();
				}
			}
			if(ps != null) {
				try {
					ps.close();
				} catch(Exception e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch(Exception e) {
					e.printStackTrace();
				}
			}
		}
		
		return role;
	}
	
}

JDBCExam1.java

package kr.or.connect.jdbcexam;

import java.util.Scanner;
import kr.or.connect.jdbcexam.dto.Role;
import kr.or.connect.jdbcexam.dao.RoleDao;

public class JDBCExam1 {
	
	public static void main(String args[]) {
		System.out.printf("조회하고자 하는 Role id를 입력하시오: ");
		Scanner scanner = new Scanner(System.in);
		Integer role_id = scanner.nextInt();
		
		RoleDao roleDao = new RoleDao();
		Role role = roleDao.getRole(role_id);
		
		System.out.println(role);
	}
	

}