JDBC ์‹ค์Šต

Minkyeong Kimยท2021๋…„ 11์›” 10์ผ
0

[boostcourse] Web-Backend

๋ชฉ๋ก ๋ณด๊ธฐ
17/55

ํŒŒ์ผ๊ตฌ์กฐ

๐Ÿ“jdbcexam
|_ ๐Ÿ“kr.or.connect.jdbcexam

  • App.java
  • JDBCExam1.java -> ๋ฉ”์ธ ํ•จ์ˆ˜ ์กด์žฌ

|_ ๐Ÿ“kr.or.connect.jdbcexam.dao

  • RoleDao.java

|_ ๐Ÿ“kr.or.connect.jdbcexam.dto

  • Role.java

Role.java

Role ๊ฐ์ฒด์— ๋Œ€ํ•œ ์ •๋ณด๋ฅผ ๋‹ด๋Š”๋‹ค

package kr.or.connect.jdbcexam.dto;

public class Role {
	private Integer roleId;
	private String description;
	
	public Role(Integer roleId, String description) {
		super();
		this.roleId=roleId;
		this.description=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 + "]";
	}
}

RoleDao.java

DB์™€ connectํ•˜๋Š” ๋ถ€๋ถ„์„ ๋‹ด๋‹นํ•จ

์•„๋ž˜์™€ ๊ฐ™์€ ํ•จ์ˆ˜๋“ค์„ ์ •์˜ํ•˜์—ฌ DB๋ฅผ ์ฝ๊ณ  ์“ฐ๊ฑฐ๋‚˜ ์ˆ˜์ •ํ•  ์ˆ˜ ์žˆ๋‹ค!

  • getRole (์•„๋ž˜ ์†Œ์Šค์ฝ”๋“œ์—๋Š” ์˜ˆ์ œ๋กœ ์ด๊ฒƒ๋งŒ ์ž‘์„ฑ)
  • getRoles
  • addRole (==insert)
  • deleteRole
  • updateRole

๋Œ€๋žต์ ์ธ ์‹คํ–‰๊ณผ์ •
1. conn ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑ
2. prepare Statement ๋ณ€์ˆ˜๋ฅผ ์ƒ์„ฑํ•ด์„œ ์„ ์–ธ๋ฌธ์— ์›ํ•˜๋Š” ๊ฐ’ ๋™์ ์œผ๋กœ ์ž…๋ ฅํ•  ์ˆ˜ ์žˆ๋„๋ก ํ•จ
3. prepare Statement ๋ณ€์ˆ˜์— ์ €์žฅ๋œ ์ฟผ๋ฆฌ๋ฅผ ์‹คํ–‰์‹œ์ผœ next()ํ•จ์ˆ˜๋กœ ํ•œ์ค„์”ฉ ๊ฐ€์ ธ์˜ค๋„๋ก ํ•จ
4. rs๊ฐ€ null์ผ ๊ฒฝ์šฐ ์˜ˆ์™ธ ์ฒ˜๋ฆฌ -> close
5. ps๊ฐ€ null์ผ ๊ฒฝ์šฐ ์˜ˆ์™ธ์ฒ˜๋ฆฌ -> close
6. conn์ด null์ผ ๊ฒฝ์šฐ ์˜ˆ์™ธ์ฒ˜๋ฆฌ -> close

package kr.or.connect.jdbcexam.dao;

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

import kr.or.connect.jdbcexam.dto.Role;

public class RoleDao {
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb";
	private static String dbUser = "connectuser";
	private static String dbpasswd = "connect123!@#";
	
	public Role getRole(Integer roleId) {
		Role role = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(dburl, dbUser, dbpasswd);
			// preparedStatement๊ฐ€ ๊ฐ€์ง€๊ณ  ์žˆ๋Š” ๋ฌผ์Œํ‘œ์— ๋Œ€ํ•œ ๊ฐ’์„ ์ง€์ • ๊ฐ€๋Šฅ
			String sql = "SELECT description, role_id FROM role WHERE role_id = ?";
			ps = conn.prepareStatement(sql); // sql๋ฌธ์„ ์‹คํ–‰์‹œํ‚ค๋„๋ก ํ•จ
			ps.setInt(1, roleId);//1๋ฒˆ์งธ ๋ฌผ์Œํ‘œ์— roleId ๋ณ€์ˆ˜๋ฅผ ์ ์šฉ
			rs = ps.executeQuery();
			
			if(rs.next()) {
				String description = rs.getString(1); // ์ฒซ๋ฒˆ์งธ column์˜ ๊ฐ’ ๊ฐ€์ ธ์˜ด
				int id = rs.getInt(2); //column์ด ๊ฐ€์ง„ ๊ฐ’ ๊ฐ€์ ธ์˜ด
				role = new Role(id, description);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(rs != null) { // rs๊ฐ€ null์ผ ๋•Œ๋Š” ์ˆ˜ํ–‰๋˜์ง€ ์•Š๋„๋ก ํ•จ
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(ps != null) { // ps๊ฐ€ null์ผ ๋•Œ๋Š” ์ˆ˜ํ–‰๋˜์ง€ ์•Š๋„๋ก ํ•จ
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(conn != null) { // conn๊ฐ€ null์ผ ๋•Œ๋Š” ์ˆ˜ํ–‰๋˜์ง€ ์•Š๋„๋ก ํ•จ
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
		}
		
		return role;
	}
}

JDBCExam1.java

RoleDao์˜ getํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•ด DB์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ค๋Š” ๋ฉ”์ธ ํด๋ž˜์Šค

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam1 {
	public static void main(String[] args) {
		RoleDao dao = new RoleDao();
		Role role = dao.getRole(100); #role_id ๊ฐ€ 100์ธ ๋ ˆ์ฝ”๋“œ๋ฅผ ๊ฐ€์ ธ์˜ด
		System.out.println(role);
	}
}

๊ฒฐ๊ณผ

profile
๐Ÿ’

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