데이터베이스 CRUD 생성

1. 오라클 연결

전에 실습으로 생략 

5.JAVA(이클립스)로 DB(오라클)연결하기

 

2.엔티티

package db;

public class Dept {
	private int deptno;
	private String dname;
	private String loc;
	
	public Dept() {
		
	}

	public Dept(int deptno, String dname, String loc) {
		super();
		this.deptno = deptno;
		this.dname = dname;
		this.loc = loc;
	}

	public int getDeptno() {
		return deptno;
	}

	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	public String getDname() {
		return dname;
	}

	public void setDname(String dname) {
		this.dname = dname;
	}

	public String getLoc() {
		return loc;
	}

	public void setLoc(String loc) {
		this.loc = loc;
	}
	
	
}

3.DAO

package db;

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

public class DeptDao {
	
	//목록
	public ArrayList<Dept> 부서목록보기(){
		ArrayList<Dept> depts = new ArrayList<>();
		try {
			//1.DB연결
			Connection conn = DBConnection.connection();
			
			//2. 문장완성
			PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM dept");

			//3. 문장전송
			ResultSet rs = pstmt.executeQuery();
			
			//4. 커서 false 때까지 내리기
			while (rs.next()) {
				Dept dept = new Dept();
				dept.setDeptno(rs.getInt("deptno"));
				dept.setDname(rs.getString("dname"));
				dept.setLoc(rs.getString("loc"));
				depts.add(dept);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
//		int size = depts.size();
//		for (int j =0; j <size; j++) {
//			System.out.println(depts.get(j).getDname());
//		}
		return depts;
	}// end of 부서목록보기
	
	//한건 확인
	public Dept 부서확인 (int deptno) {
		Dept dept = new Dept();
		try {
			Connection conn = DBConnection.connection();
			PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM dept WHERE deptno =" + deptno);
			ResultSet rs = pstmt.executeQuery();
			
		if(rs.next()) {
			dept.setDeptno(rs.getInt("deptno"));
			dept.setDname(rs.getString("dname"));
			dept.setLoc(rs.getString("loc"));
		}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		System.out.println(dept.getDname());
		return dept;
	}//end of 부서확인
	
	//추가
	public int 부서추가(Dept dept) {
		int result = -1;
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("INSERT INTO dept ");
			sql.append("VALUES(?,?,?)");
			
			Connection conn = DBConnection.connection();
			
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			
			pstmt.setInt(1, dept.getDeptno());
			pstmt.setString(2, dept.getDname());
			pstmt.setString(3, dept.getLoc());
			
			result = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}// end of 부서추가
	//삭제
	public int 부서삭제(int deptno) {
		int result = -1;
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("DELETE FROM dept WHERE deptno = ? ");
			
			Connection conn = DBConnection.connection();
			
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			
			pstmt.setInt(1, deptno);
			
			result = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}// end of 부서삭제
	//수정
	public int 부서수정( String dname, String loc,int deptno) {
		int result = -1;
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("UPDATE dept SET dname = ?, loc = ? WHERE deptno = ?");
			
			Connection conn = DBConnection.connection();
			
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			
			pstmt.setString(1, dname);
			pstmt.setString(2, loc);
			pstmt.setInt(3, deptno);
			
			result = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}// end of 부서수정

}

 

4.실행

package db;

import java.util.ArrayList;

public class DeptApp {
	public static void main(String[] args) {
		DeptDao deptDao = new DeptDao();
		
		//목록
		ArrayList<Dept> depts =  deptDao.부서목록보기();
		//한건 확인
		Dept dept = deptDao.부서확인(10);
		//추가
		Dept dept = new Dept(
				50,"new","busan");
		deptDao.부서추가(dept);
		//삭제
		int result = deptDao.부서삭제(50);
		//수정
		int result = deptDao.부서수정("save", "korea", 50);
	}
}