[자바/DB] JDBC - 메소드 이용하여 구현하기

---

SQL 실행 화면 보는 법

 

Windows -> show view -> others -> Data Source Explorer

 

 

---

 MemberVO

 

: 필드 , 생성자, getter/setter 구현 클래스

 

public class MemberVO {
	
	//필드 구성
	//필드 = DB 컬럼값
	private String id;
	private String pw;
	private String name;
	private int age;
	
	//생성자
	public MemberVO(String id, String pw, String name, int age) {
		super();
		this.id = id;
		this.pw = pw;
		this.name = name;
		this.age = age;
	}

	//getter. setter
	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getPw() {
		return pw;
	}

	public void setPw(String pw) {
		this.pw = pw;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}
	
	

}

 

 

 

 

 

 

---

DAO 

: 입력, 수정, 삭제, 조회 메소드 생성 클래스

 

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

public class DAO {
	//2번 Controller
	//DAO : Data Access Object
	//정의 : DB에 있는 data들에게 접근하기위한 객체
	//DAO를 쓰는 이유 : DB접근을 하기위한 로직과 비즈니스 로직을 분리하기 위해서
	
	private PreparedStatement psmt;
	private Connection conn;
	private ResultSet rs;
	
	private void getConnection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String db_url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
			String db_id = "hr";
			String db_pw = "hr";
		
			conn = DriverManager.getConnection(db_url, db_id, db_pw);
			 
			if(conn != null) {
				System.out.println("DB 연결 성공");
			}
			else {
				System.out.println("DB 연결 실패");
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void close() {
		try {
		if(rs!=null) {
				rs.close();
			} 
		if(psmt != null) {
			psmt.close();
		}
		if(conn!=null) {
			conn.close();
		}
		}catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
	
	public int insert(String id, String pw, String name, int age) {
		
		int cnt=0; //리턴받기 위한 변수
		try {
			getConnection();
			
			String sql = "INSERT INTO MEMBER values(? , ? , ? , ?)";
			psmt=conn.prepareStatement(sql);
			psmt.setString(1, id);
			psmt.setString(2, pw);
			psmt.setString(3, name);
			psmt.setInt(4, age);
			
			cnt= psmt.executeUpdate();
			
		}catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		
		return cnt;
		
	}
	
	public int update(String id, int age) {
		int cnt=0;
		try {
			
			getConnection();
			
			String sql = "UPDATE MEMBER SET age=? where id = ?";
			
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1,age);
			psmt.setString(2, id);
			
			cnt = psmt.executeUpdate();
			
			
		}catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		
		return cnt;
	}
	
	public int delete(String id) {
		int cnt=0;
		try {
			getConnection();
			
			String sql = "DELETE MEMBER where id = ?";
			
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, id);
			
			cnt = psmt.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		
		return cnt;
	}

	
	public ArrayList<MemberVO> select(){
		ArrayList<MemberVO> list = new ArrayList<MemberVO>();
		
		try {
			getConnection();
			
			String sql = "SELECT * FROM MEMBER";
			psmt = conn.prepareStatement(sql);
			rs=psmt.executeQuery();
		
			while(rs.next()) {
				String id = rs.getString(1);
				String pw = rs.getString(2);
				String name = rs.getString(3);
				int age = rs.getInt(4);
				
				MemberVO vo = new MemberVO(id,pw,name,age);
				list.add(vo);
			}
			
		}catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			close();
		}
		return list;
	}
	
}

 

 

 

 

 

 

 

 

---

Main클래스

 

import java.util.ArrayList;
import java.util.Scanner;

public class Main {

	public static void main(String[] args) {

		Scanner sc = new Scanner(System.in);
		
		//1. 등록 2. 조회 3. 수정 4. 삭제 5. 종료
		//DB와 관련된 작업 수행 객체 DAO
		DAO dao = new DAO();
		
		while(true) {
			System.out.print("1.등록 2.조회 3.수정 4.삭제 5.종료 >>");
			int choice = sc.nextInt();
			
			if(choice==1) {
				System.out.println("등록 단계");
				System.out.print("ID : ");
				String id = sc.next();
				System.out.print("PW : ");
				String pw = sc.next();
				System.out.print("Name : ");
				String name = sc.next();
				System.out.print("Age : ");
				int age = sc.nextInt();
			
		int cnt= dao.insert(id, pw, name, age);
			if(cnt > 0) {
				System.out.println("등록성공");
			}
			else {
				System.out.println("등록실패");
			}
			
			}
			
			else if(choice==2) {
				System.out.println("전체 조회");
				ArrayList<MemberVO> list = dao.select();
				
				for(int i=0;i<list.size();i++) {
					System.out.println(list.get(i).getId() + "\t" 
										+list.get(i).getPw() + "\t"
										+list.get(i).getName() + "\t" 
										+list.get(i).getAge() );
																
				}
			}
			else if(choice==3) {
				//수정 기능
				//ID : pluto age 19
				System.out.println("수정 단계");
				System.out.print("Age : ");
				int age = sc.nextInt();
				System.out.print("ID : ");
				String id = sc.next();
				
				
				int cnt=dao.update(id, age);
				if(cnt>0){
					System.out.println("수정성공");
				}
				else {
					System.out.println("수정실패");
				}
				
			}
			else if(choice==4) {
				System.out.println("삭제 단계");
				System.out.print("ID : ");
				String id = sc.next();
				
				
				int cnt=dao.delete(id);
				if(cnt>0){
					System.out.println("삭제성공");
				}
				else {
					System.out.println("삭제실패");
				}
			}
			
		}
		
	}

}