JDBC, MySQL ์—ฐ๋™ 4 - DELETE, UPDATE

JOYยท2021๋…„ 12์›” 13์ผ
0

๏ปฟSQL & JDBC ํ”„๋กœ๊ทธ๋ž˜๋ฐ

๋ชฉ๋ก ๋ณด๊ธฐ
12/12
post-thumbnail

๐Ÿ“Œ JDBC, MySQL ์—ฐ๋™ 4 - DELETE, UPDATE


1. MySQL ์—ฐ๋™

RoleDao.java - deleteRole(), updateRole()

๐Ÿ“ RoleDao.java๋Š” MySQL๊ณผ Role.java ํด๋ž˜์Šค๋ฅผ ์ด์šฉํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฅผ ์ฒ˜๋ฆฌํ•˜๋Š” ํด๋ž˜์Šค ์ด๋‹ค.

1. JDBC ๋“œ๋ผ์ด๋ฒ„ ๋กœ๋”ฉ ๋ฐ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์—ฐ๊ฒฐ

  • JDBC API ์‚ฌ์šฉ์„ ์œ„ํ•ด Class.forName ๋ฉ”์„œ๋“œ๋ฅผ ์ด์šฉํ•˜์—ฌ JDBC ๋“œ๋ผ์ด๋ฒ„ ๋กœ๋”ฉ

  • DB ๋งˆ๋‹ค ํด๋ž˜์Šค ์ด๋ฆ„์ด ๋‹ค๋ฅด๋ฏ€๋กœ ํ•ด๋‹น DB ์ด๋ฆ„์„ ์ •ํ™•ํ•˜๊ฒŒ ๋„ฃ์–ด์•ผ ํ•œ๋‹ค.

  • ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์—ฐ๊ฒฐ ์„ค์ •

//๋“œ๋ผ์ด๋ฒ„ ๋กœ๋”ฉ
Class.forName("com.mysql.jdbc.Driver");
//์ปค๋„ฅ์…˜ ๊ฐ์ฒด
conn = DriverManager.getConnection(dburl, dbUser, dbpasswd);

2. JDBC - DELETE

RoleDao.java - deleteRole()

1. SQL๋ฌธ ์ž‘์„ฑ

//delete ๋œ ๊ฒฐ๊ณผ ๊ฑด์ˆ˜๋ฅผ ๋‚˜ํƒ€๋‚ผ int ํ˜• ๋ณ€์ˆ˜ ์„ ์–ธ
int deleteCount = 0;

String sql = "delete from role where role_id = ?";			
ps = conn.prepareStatement(sql);			
ps.setInt(1, roleId);			
deleteCount = ps.executeUpdate();

2. DELETE

๐Ÿ™ role ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ๋ฐ์ดํ„ฐ๋ฅผ ์ถœ๋ ฅํ•ด๋ณด์ž.

public class JDBCexam4 {

	public static void main(String[] args) {

		int roleId = 500;
		
		RoleDao dao = new RoleDao();	
		
		int deleteCount = dao.deleteRole(roleId);
		List<Role> list = dao.getRoles();	
		
		System.out.println(deleteCount);		
			
		for(Role role : list) {
			System.out.println(role);
		}	
	}

}
  • role_id = 500์ธ ๋ฐ์ดํ„ฐ๊ฐ€ ์‚ญ์ œ๋˜์—ˆ๋Š”์ง€ ์ถœ๋ ฅ
  • ๋ฐ์ดํ„ฐ๊ฐ€ ๋งž๊ฒŒ ์‚ญ์ œ๋˜์—ˆ๋Š”์ง€ MySQL์—์„œ ํ™•์ธ


3. JDBC - UPDATE

RoleDao.java - updateRole()

1. SQL๋ฌธ ์ž‘์„ฑ

//update ๋œ ๊ฒฐ๊ณผ ๊ฑด์ˆ˜๋ฅผ ๋‚˜ํƒ€๋‚ผ int ํ˜• ๋ณ€์ˆ˜ ์„ ์–ธ
int updateCount = 0;

String sql = "update role set description = ? where role_id = ?";
			
ps = conn.prepareStatement(sql);
			
ps.setString(1, role.getDescription());
ps.setInt(2, role.getRoleId());

updateCount = ps.executeUpdate();

2. UPDATE

๐Ÿ™ role = 500์ธ ๋ฐ์ดํ„ฐ์˜ description ๊ฐ’์„ "CEO"๋กœ update ํ•ด๋ณด์ž.

public class JDBCexam5 {

	public static void main(String[] args) {
		
		int roleId = 500;
		String description = "CEO";
		
		Role role = new Role(roleId, description);
		
		RoleDao dao = new RoleDao();
		int updateCount = dao.updateRole(role);
		
		System.out.println(updateCount);
				
		//์•Œ๋งž๊ฒŒ ๋ฐ์ดํ„ฐ ์ˆ˜์ •๋˜์—ˆ๋Š”์ง€ ํ™•์ธํ•˜๊ธฐ
		List<Role> list = dao.getRoles();
		
		for(Role rolelist : list) {
			System.out.println(rolelist);
		}
	}
}
  • update ์ „ role ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ ํ™•์ธ
  • role = 500์ธ ๋ฐ์ดํ„ฐ์˜ description ๊ฐ’์„ "CEO"๋กœ update ๋˜์—ˆ๋Š”์ง€ ํ•™์ธ
  • ๋ฐ์ดํ„ฐ๊ฐ€ ๋งž๊ฒŒ update๋˜์—ˆ๋Š”์ง€ MySQL์—์„œ ํ™•์ธ




๐Ÿ“• ์ „์ฒด ์ฝ”๋“œ

RoleDao.java - deleteRole()

//Delete
	public int deleteRole(Integer roleId) {
		int deleteCount = 0;
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			conn = DriverManager.getConnection(dburl, dbUser, dbpasswd);
			
			String sql = "delete from role where role_id = ?";
			
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, roleId);
			
			deleteCount = ps.executeUpdate();
			
		} catch (Exception e) {			
			e.printStackTrace();
		}finally {
			if(ps!=null) {
				try {
					ps.close();
				} catch (SQLException e) {					
				}
				
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
			}
		}
		
		return deleteCount;
	}

JDBCexam4.java

public class JDBCexam4 {

	public static void main(String[] args) {

		int roleId = 500;
		
		RoleDao dao = new RoleDao();	
		
		int deleteCount = dao.deleteRole(roleId);
		List<Role> list = dao.getRoles();	
		
		System.out.println(deleteCount);		
			
		for(Role role : list) {
			System.out.println(role);
		}	
	}
	
}

RoleDao.java - updateRole()

//Update
	public int updateRole(Role role) {
		int updateCount = 0;
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			conn = DriverManager.getConnection(dburl, dbUser, dbpasswd);
			
			String sql = "update role set description = ? where role_id = ?";
			
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, role.getDescription());
			ps.setInt(2, role.getRoleId());			
			
			updateCount = ps.executeUpdate();
			
		} catch (Exception e) {			
			e.printStackTrace();
		}finally {
			if(ps!=null) {
				try {
					ps.close();
				} catch (SQLException e) {					
				}
				
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
			}
		}
		
		return updateCount;
	}

JDBCexam5.java

public class JDBCexam5 {

	public static void main(String[] args) {
		
		int roleId = 500;
		String description = "CEO";
		
		Role role = new Role(roleId, description);
		
		RoleDao dao = new RoleDao();
		int updateCount = dao.updateRole(role);
		
		System.out.println(updateCount);
				
		//์•Œ๋งž๊ฒŒ ๋ฐ์ดํ„ฐ ์ˆ˜์ •๋˜์—ˆ๋Š”์ง€ ํ™•์ธํ•˜๊ธฐ
		List<Role> list = dao.getRoles();
		
		for(Role rolelist : list) {
			System.out.println(rolelist);
		}
	}
	
}

profile
Just Do IT ------- ๐Ÿƒโ€โ™€๏ธ

0๊ฐœ์˜ ๋Œ“๊ธ€