[JDBC] days02 - 클래스 select

try-catch 밖에 쓰기 

ArrayList<DeptDTO> list=null; 

 

CRUD
쿼리를 수행할 객체= 일꾼 StateMent
결과물을 저장할 객체 ResultSet 

 

rs.next() 결과물 불러올것이 더 있는지 ? 

 

변수에 저장하는 방법 

1.String deptno=rs.getString(1);
2.String deptno=rs.getString("deptno");

3.DeptDTO dto=new DeptDTO();
dto.setDEPTNO(rs.getInt(1));

 

Date는 String 으로 받아와도 된다. 

 

 

DeptDTO 클래스 

package days02;

public class DeptDTO {
	 //필드
		private int DEPTNO;
		private String DNAME;
		private String LOC;
	 //getter, setter
		public int getDEPTNO() {
			return DEPTNO;
		}
		public void setDEPTNO(int dEPTNO) {
			DEPTNO = dEPTNO;
		}
		public String getDNAME() {
			return DNAME;
		}
		public void setDNAME(String dNAME) {
			DNAME = dNAME;
		}
		public String getLOC() {
			return LOC;
		}
		public void setLOC(String lOC) {
			LOC = lOC;
		}

		
	 //생성자
		public DeptDTO() { //디폴트 생성자
			super();
		}
		
		//이부분 추가한 거 
	 public DeptDTO(int dEPTNO, String dNAME, String lOC) {
		super();
		DEPTNO = dEPTNO;
		DNAME = dNAME;
		LOC = lOC;
	}
	@Override
	public String toString() {
		return "DeptDTO [DEPTNO=" + DEPTNO + ", DNAME=" + DNAME + ", LOC=" + LOC + "]";
	}

		
		
}

 

 

package days02;

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

import com.util.DBconn;

//DeptDTO 클래스 SELECT 작업
public class Ex03연습 {
private void name() {
	 //나중에 자바아닌것으로 할때는 프로시저명으로 처리하는게 속도가 빠르다
	String sql =" Select * from dept";
	//connection 객체얻어옴 
	Connection conn=DBconn.getConnection();
	//try-catch 밖에 쓰기 
	ArrayList<DeptDTO> list=null; 
	
//	CRUD
//	쿼리를 수행할 객체= 일꾼 StateMent
//	결과물을 저장할 객체 ResultSet 
	try {
		Statement stmt=conn.createStatement();
		//일꾼이 무슨일을 할지?  select 쿼리에선 executeQuery()를 사용 
		ResultSet rs=stmt.executeQuery(sql);
//		while(rs.next()) {
//			//위의 "SELECT deptno, dname, loc FROM DEPT"; 에서 1번째거 (0이아닌 1부터 시작)
//			int deptno=rs.getInt(1);
//			String dname=rs.getString("dname");
//			String loc =rs.getString(3);
//			 System.out.printf("%d\t%s\t%s\n", deptno, dname, loc);
//		}
		list =new ArrayList<DeptDTO>();
		while(rs.next()) {
			DeptDTO dto=new DeptDTO();
// 3가지 방법으로 가능 
//			String deptno=rs.getString(1);
//			String deptno=rs.getString("deptno");
			dto.setDEPTNO(rs.getInt(1));
			dto.setDNAME(rs.getString(2));
			dto.setLOC(rs.getString(3));
		//	ArrayList 에 담는 이유: 출력용에 담아서 넘겨줌
			list.add(dto);
			
			rs.close();
			stmt.close();
		}
	} catch (SQLException e) {
		// TODO: handle exception
	}
	
	DBconn.close();
	
	Iterator<DeptDTO> ir=list.iterator();
	while (ir.hasNext()) {
		DeptDTO dto = (DeptDTO) ir.next();
		System.out.println(dto);
	}
	System.out.println("end");
}//class
}

 

 

empDTO 클래스 

package days02;

import java.sql.Date;

public class EmpDTO {
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private Date hiredate;
	private double sal;
	private double comm;
	private int deptno;
	
	public EmpDTO() {super();}

	public EmpDTO(int empno, String ename, String job, int mgr, Date hiredate, double sal, double comm, int deptno) {
		super();
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.deptno = deptno;
	}
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public int getMgr() {
		return mgr;
	}
	public void setMgr(int mgr) {
		this.mgr = mgr;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public double getSal() {
		return sal;
	}
	public void setSal(double sal) {
		this.sal = sal;
	}
	public double getComm() {
		return comm;
	}
	public void setComm(double comm) {
		this.comm = comm;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	@Override
	public String toString() {
		return "EmpDTO [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate="
				+ hiredate + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
	}
	
   

}

//EMPNO    NOT NULL NUMBER(4)    
//ENAME             VARCHAR2(10) 
//JOB               VARCHAR2(9)  
//MGR               NUMBER(4)    
//HIREDATE          DATE         
//SAL               NUMBER(7,2)  
//COMM              NUMBER(7,2)  
//DEPTNO            NUMBER(2)

 

 

//사원이 존재하지 않을수도 있기 때문에 new ArrayList<EmpDTO>();를 하지 않고 null
ArrayList<EmpDTO> list=null;

package days02;

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 java.util.Scanner;

import com.util.DBconn;
//부서번호를 입력받아 해당 부서원의 정보출력 
//ArrayList <EmpDtO> list
public class Ex04 {
public static void main(String[] args) {
Connection conn=null;
	ResultSet rs=null;
	Statement stmt=null;
	int pdeptno=10;
	ArrayList<EmpDTO> list =null;
	
	Scanner sc=new Scanner(System.in);
	System.out.println("부서입력");
	pdeptno=sc.nextInt();
	String sql="select * "
			+ "from emp "
			+ "where deptno="+pdeptno;
	
	conn=DBconn.getConnection();
	try {
		stmt=conn.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");
				
				EmpDTO dto= new EmpDTO(empno, ename,job,mgr, hiredate, sal, comm,deptno);
						
				list.add(dto);
			} while (rs.next());
		}
	} catch (SQLException e1) {
		e1.printStackTrace();
	}
	try {
		rs.close();
		stmt.close();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}

	
	DBconn.close();
	printEmp(list);
	System.out.println("end");
}//main

private static void printEmp(ArrayList<EmpDTO> list) {
	if (list==null) {
		System.out.println("사원존재 x");
		return;
	}
	Iterator< EmpDTO> ir=list.iterator();
	while (ir.hasNext()) {
		EmpDTO dto = (EmpDTO) ir.next();
		System.out.println(dto);
	}
}//printEmp
}//class