마카로닉스

627 No

[동영상강의] Hmk - java43_Oracle 연동(JCombobox에 부서목록 바인딩, JTable에 사원목록 출력)

2017-09-03 18:46:12 By 머나먼나라 0 1752 댓글수 4 포스트 하기

4월 2024
일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

오라클 실습용 데이터

http://braverokmc.dothome.co.kr/m04/oracle/view/410

-- 사원 테이블
select * from emp;
-- 사원 번호, 사원이름, 부서코드
select empno, ename, deptno from emp;
-- 부서테이블
select * from dept;


-- 사원테이블과 부서테이블을 join 시킴
-- 테이블이름 별칭(alias)
select empno, ename, dname from emp e, dept d
where e.deptno=d.deptno;

-- ANSI SQL (표준 SQL)
select empno, ename, dname from emp e 
join dept d on e.deptno=d.deptno;

--
select empno, ename, e.deptno, dname from emp e, dept d
where e.deptno =d.deptno and e.DEPTNO =30;

select * from dept;

select * from emp;

-- 사번, 이름, 직급, 입사일자, 급여, 부서이름
select empno, ename, job, hiredate, sal, dname
from emp e, dept d
where e.deptno=d.deptno ;

select empno, ename, job, hiredate, sal, dname
from emp e, dept d
where e.deptno=d.deptno and dname like '경리팀' ;

oracle.prop

url=jdbc:oracle:thin:@localhost:1521:xe
driver=oracle.jdbc.OracleDriver
id=java
password=1111

class DBConnectManager

package common.db;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class DBConnectManager {

	public static Connection dbConn(){
		Connection conn=null;
		try{
			//db 접속 정보가 저장되 db.prop 파일을 로딩
			FileInputStream fis =new FileInputStream("db.prop");
			Properties prop=new Properties();
			prop.load(fis);
			String url=prop.getProperty("url");
			String id=prop.getProperty("id");
			String password=prop.getProperty("password");

			//mysql에 접속 처리
			conn=DriverManager.getConnection(url, id, password);
		}catch(Exception e){
			e.printStackTrace();
		}
		return conn;
	}
	
	
	public static Connection oracleConn(){
		Connection conn=null;
		try{
			//db 접속 정보가 저장되 db.prop 파일을 로딩
			FileInputStream fis =new FileInputStream("oracle.prop");
			Properties prop=new Properties();
			prop.load(fis);
			String url=prop.getProperty("url");
			String id=prop.getProperty("id");
			String password=prop.getProperty("password");

			//mysql에 접속 처리
			conn=DriverManager.getConnection(url, id, password);
		}catch(Exception e){
			e.printStackTrace();
		}
		return conn;
	}
	
	
	
	public static void close(Connection conn){
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void close(PreparedStatement pstmt){
		try {
			if(pstmt!=null)pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void close(ResultSet rs){
		try {
			if(rs!=null)rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void close(Connection conn, PreparedStatement pstmt ){
				
		try{
			if(pstmt!=null)pstmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	
	}
	

	public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs){

		try{
			if(rs!=null)rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}
				
		try{
			if(pstmt!=null)pstmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	
	}
	
	
	
	public static void close(Connection conn, Statement stmt, ResultSet rs){

		try{
			if(rs!=null)rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}
				
		try{
			if(stmt!=null)stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	
	}
	
	
	
	
	
}

class DeptDTO

package java21_oracle.emp;

public class DeptDTO {

	private int deptno;
	private String dname;
	private String loc;
	
	
	public DeptDTO() {
		super();
	}

	public DeptDTO(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;
	}

	@Override
	public String toString() {
		return "DeptDTO [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
	}
	
	
		
}

class DeptDAO

package java21_oracle.emp;

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

import common.db.DBConnectManager;

public class DeptDAO {

	private static DeptDAO dao;
	private DeptDAO(){
	}
	public static DeptDAO getInstance(){
		if(dao==null){
			dao=new DeptDAO();
		}
		return dao;
	}
	
	
	// 부서 목록을 벡터로 리턴
	public ArrayList<DeptDTO> listDept(){
		ArrayList<DeptDTO> items=new ArrayList<>();
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			conn =DBConnectManager.oracleConn();//오라클 접속
			String sql ="select * from dept order by dname";
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();//sql 실행, 결과를 rs에 리턴
			while(rs.next()){ // 다음 레코드가 존재하면
				DeptDTO row=new DeptDTO(); // 벡터에 레코드 저장
				row.setDeptno(rs.getInt("deptno"));
				row.setDname(rs.getString("dname"));
				row.setLoc(rs.getString("loc"));
				items.add(row); //벡터에 레코드 1개 저장
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnectManager.close(conn, pstmt);
		}
		return items;
	}
	
	
	
	
}

class EmpDTO

package java21_oracle.emp;

import java.sql.Date;

public class EmpDTO {

	private Integer empno;
	private String ename;
	private String job;
	private Date hiredate;
	private Integer sal;
	private String dname;
	public EmpDTO() {
		super();
	}
	
	public EmpDTO(Integer empno, String ename, String job, Date hiredate, Integer sal, String dname) {
		super();
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.hiredate = hiredate;
		this.sal = sal;
		this.dname = dname;
	}

	public EmpDTO(Integer empno, String ename, String job, Integer sal, String dname) {
		super();
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.sal = sal;
		this.dname = dname;
	}

	public Integer getEmpno() {
		return empno;
	}

	public void setEmpno(Integer 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 Date getHiredate() {
		return hiredate;
	}

	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}

	public Integer getSal() {
		return sal;
	}

	public void setSal(Integer sal) {
		this.sal = sal;
	}

	public String getDname() {
		return dname;
	}

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

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

	
	
	
}

class EmpDAO

package java21_oracle.emp;

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

import common.db.DBConnectManager;

public class EmpDAO {

	private static EmpDAO dao;
	private EmpDAO(){
		
	}
	public static EmpDAO getInstance(){
		if(dao==null){
			dao=new EmpDAO();
		}
		return dao;
	}
	
	public Vector<Object> listEmp(String dname){
		Vector<Object> items =new Vector<>();
		Connection conn=null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		
		if(dname.equals("전체부서")){
			dname="%";
		}
		
		try{
			conn =DBConnectManager.oracleConn();
			String sql =" select empno, ename, job, hiredate, sal, dname "
					+ " from emp e, dept d where e.deptno=d.deptno"
					+ " and dname like ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, dname);
			rs=pstmt.executeQuery();
			while(rs.next()){
				Vector<Object> row=new Vector<>();
				row.add(rs.getInt("empno"));
				row.add(rs.getString("ename"));
				row.add(rs.getString("job"));
				row.add(rs.getDate("hiredate"));
				row.add(rs.getInt("sal"));
				row.add(rs.getString("dname"));
				items.add(row);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnectManager.close(conn, pstmt, rs);
		}
		
		return items;
	}
	
}

class EmpList

package java21_oracle.emp;

import java.awt.EventQueue;
import java.util.ArrayList;
import java.util.Vector;

import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.border.EmptyBorder;
import javax.swing.table.DefaultTableModel;
import java.awt.event.ItemListener;
import java.awt.event.ItemEvent;

public class EmpList extends JFrame {

	private JPanel contentPane;
	private JTable table;
	
	private DeptDAO deptDao;
	private EmpDAO empDao;
	DefaultTableModel model;
	
	private Vector<Object> data, col ; //JTable 에 입력될 데이터, 컬럼

	
	
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					EmpList frame = new EmpList();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public EmpList() {
		//DAO 객체 생성
		deptDao=DeptDAO.getInstance();
		empDao=EmpDAO.getInstance();
		
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 661, 529);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(12, 97, 621, 384);
		contentPane.add(scrollPane);
		
		//테이블의 컬럼 정의
		col=new Vector<Object>();
		col.add("사원번호");
		col.add("사원이름");
		col.add("직급");
		col.add("입사일자");
		col.add("급여");
		col.add("소속부서");
		//테이블 모델 생성
		model =new DefaultTableModel(empDao.listEmp("%"), col);
			
		table = new JTable(model);
		scrollPane.setViewportView(table);
			
		
		
		JComboBox cboDept = new JComboBox();
		cboDept.addItemListener(new ItemListener() {
			public void itemStateChanged(ItemEvent e) {
				
				//콤보박스.getSelectedItem() 선택한 아이템
				//System.out.println(cboDept.getSelectedItem());
				//콤보박스의 상태
				//System.out.println(e.getStateChange());
				//선택해제(2), 새로운 선택(1) 
				if(e.getStateChange()== ItemEvent.SELECTED){
					String dname=cboDept.getSelectedItem().toString();
	
					//테이블에 입력될 데이터 모델을 갱신
					model =new DefaultTableModel(empDao.listEmp(dname), col);
					//테이블에 모델을 적용시킴(데이터가 변경됨)
					table.setModel(model);
				}
								
			}
		});
		
		cboDept.setBounds(12, 22, 143, 21);
		contentPane.add(cboDept);

		//콤보막스에 바인딩(binding)
		ArrayList<DeptDTO> deptList=deptDao.listDept();
		cboDept.addItem("전체부서");
		for(DeptDTO dto : deptList){
			cboDept.addItem(dto.getDname());
		}
		
	}
	
	
	
	
}

class MenuExam

package java21_oracle.menu;

import java.awt.BorderLayout;
import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;

import java21_oracle.emp.EmpList;

import javax.swing.JMenuBar;
import javax.swing.JMenu;
import javax.swing.JMenuItem;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;

public class MenuExam extends JFrame {

	private JPanel contentPane;

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					MenuExam frame = new MenuExam();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public MenuExam() {
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 611, 446);
		
		JMenuBar menuBar = new JMenuBar();
		setJMenuBar(menuBar);
		
		JMenu mnNewMenu = new JMenu("사원관리");
		menuBar.add(mnNewMenu);
		
		JMenuItem mntmNewMenuItem = new JMenuItem("사원목록");
		mntmNewMenuItem.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				
				EmpList form =new EmpList();
				form.setVisible(true);
			}
		});
		mnNewMenu.add(mntmNewMenuItem);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
	}
}