[days03] 사원 조회, 추가, 삭제

1. 모든 사원 정보를 출력 ( 모든 컬럼 )

package review;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import com.util.DBConn;

import days02.EmpDTO;

public class Re01 {

	public static void main(String[] args) {
		
		Connection con = null;
		String sql = "SELECT * FROM emp ";
		
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<EmpDTO> list = null;
		EmpDTO dto = null;
		con =DBConn.getConnection();
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			
			if(rs.next()) {
				list = new ArrayList<EmpDTO>();
				do {
					int empno = rs.getInt("empno");
					String ename = rs.getString("ename");
					String job = rs.getString("job");
					int mgr = rs.getInt("mgr");
					Date hiredate = rs.getDate("hiredate");
					double sal = rs.getDouble("sal");
					double comm = rs.getDouble("comm");
					int deptno = rs.getInt("deptno");
					dto = new EmpDTO(empno, ename, job, mgr, hiredate, sal, comm, deptno);
					
					list.add(dto);
				}while(rs.next());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				stmt.close();
				rs.close();
				DBConn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		Iterator<EmpDTO> ir  = list.iterator();
		while (ir.hasNext()) {
			dto =  ir.next();
			System.out.println(dto);
		}
	}

}

2. 사원 추가

package days03;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import com.util.DBConn;

public class Ex02 {

	public static void main(String[] args) {
//		 * 2. emp 테이블에 사원 추가
//	       *     ㄱ. 입사일자는 오늘날짜로 입력
//	       *     ㄴ. 그 외의 값은 임의대로 입력


		String sql = "INSERT INTO emp"
				+" values (9999, 'kenik', 'MANAGER', 7777, sysdate, null, null, null)";
		//자바에서는 auto commit 됨
	Connection con = null;
		Statement stmt = null;
		
		con = DBConn.getConnection();
		try {
			stmt = con.createStatement();
			int rowcount = stmt.executeUpdate(sql); //update문은 insert, update, delete 할떄
			//rowcount : 영향받은 레코드 수 돌려줌
			if(rowcount==1) {
				System.out.println("> 사원 추가 완료");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				stmt.close();
				DBConn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		System.out.println("=END=");
	}//main

}