관리 메뉴

nkdk의 세상

자바 Language 24일째 문제(DB정렬) 본문

My Programing/JAVA

자바 Language 24일째 문제(DB정렬)

nkdk 2008. 3. 8. 01:18
package db;

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
import pac.DBConnectionMgr;

public class tabMon2 extends JFrame implements ActionListener{
Object data[][]=new String[0][4];
String title[]={"사번", "직원명", "연봉", "입사일"};
Object imsi[];
DefaultTableModel mod; // 테이블 데이타 모델을 클래스를 선언한다.
JTable tab;
JLabel lblCount;
Connection conn;
Statement stmt;
ResultSet rs;
DBConnectionMgr pool;
JLabel jl;
Choice cboBuser=new Choice();
Choice cboSabun=new Choice();
JPanel pn1=new JPanel();
JPanel pn2=new JPanel();
JPanel pn3=new JPanel();
JPanel pn4=new JPanel();
CheckboxGroup cbg;
JButton btnOk=new JButton("OK");
Checkbox rdoAsc;
Checkbox rdoDesc;
JLabel noBuser=new JLabel("전체",10), noGogekSu=new JLabel("0",10), noMax=new JLabel("0",10), noMin=new JLabel("0",10);

public tabMon2() {
cboSabun.add("사번");
cboSabun.add("직원명");
cboSabun.add("연봉");
cboSabun.add("입사일");
accDb();
accCsql();
display();
}

public void display() {
cbg=new CheckboxGroup();
rdoAsc=new Checkbox("오름", cbg, true);
rdoDesc=new Checkbox("내림", cbg, false);
mod=new DefaultTableModel(data,title); // 행과 열의 개수를 가져야 한다.
tab=new JTable(mod);
JScrollPane scroll=new JScrollPane(tab);
jl = new JLabel(" 부서명 ");
pn1.add(jl);
pn1.add(cboBuser);
jl = new JLabel(" 정렬 ");

pn1.add(jl);
pn1.add(cboSabun);
pn1.add(rdoAsc);
pn1.add(rdoDesc);
btnOk.addActionListener(this);
pn1.add(btnOk);

jl = new JLabel(" 부서명: ");
pn2.add(jl);
pn2.add(noBuser);
jl = new JLabel(" 관리고객수: ");
pn2.add(jl);
pn2.add(noGogekSu);

jl = new JLabel(" 최대급여자: ");
pn3.add(jl);
pn3.add(noMax);
jl = new JLabel(" 최소급여자: ");
pn3.add(jl);
pn3.add(noMin);
pn4.setLayout(new GridLayout(2,1));
pn4.add(pn2);
pn4.add(pn3);

this.getContentPane().add("Center",scroll);
this.getContentPane().add("North",pn1);
this.getContentPane().add("South",pn4);
this.setBounds(200,200,500,300);
this.setVisible(true);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}

public void accCsql() {
try {
stmt=conn.createStatement();
rs=stmt.executeQuery("select buser_name from buser");
cboBuser.add("전체");
noBuser.setText("전체");
while(rs.next())
cboBuser.add(rs.getString("buser_name"));
} catch (Exception e) {
JOptionPane.showMessageDialog(this, "SQL오류1:"+e);
}finally{
try{
rs.close(); stmt.close();
}catch (Exception e) {
JOptionPane.showMessageDialog(this, "SQL닫음오류1:"+e);
}
}
}

public void accDsql() {
try {
stmt=conn.createStatement();
if (cboBuser.getSelectedItem().equals("전체"))
rs=stmt.executeQuery("select count(gogek_no) css from gogek, sawon, buser where buser_no = buser_num and sawon_no = gogek_damsano");
else
rs=stmt.executeQuery("select count(gogek_no) css from gogek, sawon, buser where buser_no = buser_num and sawon_no = gogek_damsano and buser_name = '"+cboBuser.getSelectedItem()+"'");
rs.next();
noGogekSu.setText(rs.getString("css"));
} catch (Exception e) {
JOptionPane.showMessageDialog(this, "SQL오류1:"+e);
}
}

public void accSql() {
try {
stmt=conn.createStatement();
// mod.setnumrow(0) -> 초기화
String im1;
if (rdoAsc.getState()) im1=" asc"; else im1=" desc";

int ss = mod.getRowCount();
if (ss!=0) for(int i=ss;i>0;i--) mod.removeRow(i-1);
int minpay=2000000000, maxpay=0;

if (cboBuser.getSelectedItem().equals("전체"))
rs=stmt.executeQuery("select sawon_no 사번, sawon_name 직원명, sawon_pay 연봉, to_char(sawon_ibsail,'YYYY-MM-DD') 입사일 from sawon, buser where buser_no(+) " +
"= buser_num order by "+cboSabun.getSelectedItem()+im1);
else
rs=stmt.executeQuery("select sawon_no 사번, sawon_name 직원명, sawon_pay 연봉, to_char(sawon_ibsail,'YYYY-MM-DD') 입사일 from sawon, buser where buser_no(+) " +
"= buser_num and buser_name ='"+cboBuser.getSelectedItem()+"' order by "+cboSabun.getSelectedItem()+im1);

while(rs.next()) {
String s_no=rs.getString("사번");
String s_name=rs.getString("직원명");
String s_pay=rs.getString("연봉");
String s_ibsail=rs.getString("입사일");
String imsi[]={s_no, s_name, s_pay, s_ibsail};

if (minpay > Integer.parseInt(s_pay)) { noMin.setText(s_name); minpay=Integer.parseInt(s_pay); }
if (maxpay < Integer.parseInt(s_pay)) { noMax.setText(s_name); maxpay=Integer.parseInt(s_pay); }
mod.addRow(imsi);
}
noBuser.setText(cboBuser.getSelectedItem());
accDsql();
} catch (Exception e) {
JOptionPane.showMessageDialog(this, "SQL오류:"+e);
}finally{
try{
rs.close(); stmt.close();
}catch (Exception e) {
JOptionPane.showMessageDialog(this, "SQL닫음오류:"+e);
}
}
}

public void accDb() {
try {
pool = DBConnectionMgr.getInstance();
} catch (Exception e) {
System.out.println("커넥션 오류: " + e);
}
try {
conn = pool.getConnection(); // 이렇게 하면 연결 까지 완료 됨.
} catch (Exception e) {
System.out.println("객체 생성 오류: " + e);
}
}

public static void main(String[] args) {
new tabMon2();
}

public void actionPerformed(ActionEvent e) {
if (e.getSource().equals(btnOk)) {
accSql();
}
}
}