자바 Language 21일째 1번

계속 연 이은 연결입니다.

package db;

import java.awt.event.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import java.sql.*;

public class oraDb3 extends JPanel implements ActionListener, ItemListener{
Connection conn;
Statement stmt;
ResultSet rs;

public oraDb3() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}catch (Exception e) {
System.out.println("로딩실패: "+e);
}finally {
System.out.println("로딩성공");
}
try {
conn=DriverManager.getConnection("jdbc:odbc:oradbs","SCOTT","TIGER");
stmt=conn.createStatement();
}catch (Exception e) {
System.out.println("연결실패: "+e);
}finally {
System.out.println("연결성공");
}
try {
boolean bb=stmt.execute("select * from sangdata"); // execute 메소드는 둘다 쓸 수 있다.
if (bb) {
rs=stmt.getResultSet();
while(rs.next()) {
String sang=rs.getString("sang");
System.out.println(sang);
}
}
rs.close(); stmt.close(); conn.close();
}catch (Exception e) {
System.out.println(e);
}
}

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

}

public void actionPerformed(ActionEvent e) {

}

public void itemStateChanged(ItemEvent e) {

}
}


package db;

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.Vector;

import javax.swing.*;
import javax.swing.event.*;

public class dbMonSe3 extends JPanel implements ListSelectionListener {
Connection conn;
PreparedStatement pstmt;
ResultSet rs;
int ss = 1;
private Checkbox rdoSel, rdoEtc;
JPanel pn = new JPanel();
JPanel pn1 = new JPanel();
JPanel pn2 = new JPanel();
JPanel pn3 = new JPanel();
TextField txtSet = new TextField(16);
TextField txtEtc = new TextField(16);
JLabel jl1 = new JLabel("부서선택");
JList list = new JList();
JTextArea jta = new JTextArea("",27,35);
JScrollPane jsc;
Vector<String> data=new Vector<String>();

public dbMonSe3() {
setFrames();
}

public void conn(int nana) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch (Exception e) {
System.out.println("로딩실패: " + e);
}

try {
conn = DriverManager.getConnection("jdbc:odbc:oradbs", "SCOTT",
"TIGER");
} catch (Exception e) {
System.out.println("연결실패: " + e);
}

if(nana==1) {
try {
pstmt=conn.prepareStatement("select buser_name from buser");
rs=pstmt.executeQuery();

while(rs.next()) {
String code=rs.getString("BUSER_NAME");
data.addElement(code);
}
list.setListData(data);
list.addListSelectionListener(this);
rs.close(); pstmt.close();
}catch (Exception e) {
System.out.println(e);
}
}

if(nana==2) {
try {
pstmt=conn.prepareStatement("select sawon_name, buser_name, sawon_pay, sawon_ibsail, (TO_CHAR(SYSDATE, 'YYYY'))-(TO_CHAR(sawon_ibsail, 'YYYY')) as year from sawon, buser where buser_num=buser_no and buser_name=?");
//pstmt.setString(1,data.get(ss));
pstmt.setString(1,data.get(ss));
rs=pstmt.executeQuery();
int sum=0, inwon=0;
jta.setText("");
while(rs.next()) {
String sname=rs.getString("sawon_name");
String bname=rs.getString("buser_name");
String pay=rs.getString("sawon_pay");
String ibsail=rs.getString("sawon_ibsail");
String year=rs.getString("year");
sum += Integer.parseInt(pay);
inwon++;
jta.append(""+sname+" "+bname+" "+pay+"만 "+ibsail+" "+year+"\n");
}
jta.append("\n 급여 평균 : "+sum/inwon+"만원");
jta.append("\n 인 원 : "+inwon+"명");
rs.close(); pstmt.close(); conn.close();
}catch (Exception e) {
jta.setText("");
jta.append("자료가 없습니다.");
System.out.println(e+"에 대한 에러 발생");
}

}
}

public void setFrames() {
pn1.setLayout(new BorderLayout());
pn1.add("North", jl1);
pn1.add(new JScrollPane(list), "Center");
pn2.setLayout(new BorderLayout());
pn2.add(pn1, "West");
pn2.add("Center", new JScrollPane(jta));
this.add("Center", pn2);
conn(1);
}

public void valueChanged(ListSelectionEvent e) {
if (e.getValueIsAdjusting()) {
ss = list.getSelectedIndex();
conn(2);
}
}
}

package db;

import java.awt.*;
import java.awt.event.*;
import java.sql.*;

import javax.swing.*;
import javax.swing.event.*;

public class dbMonSe2 extends JPanel implements ItemListener, ActionListener{
Connection conn;
Statement stmt;
ResultSet rs;
private Checkbox rdoSel, rdoEtc;
JPanel pn=new JPanel();
JPanel pn1=new JPanel();
JPanel pn2=new JPanel();
JPanel pn3=new JPanel();
TextField txtSet=new TextField(16);
TextField txtEtc=new TextField(16);
JLabel jl1 = new JLabel("SQL문");
JLabel jl2 = new JLabel("기타문");
CheckboxGroup group;
JTextArea jta=new JTextArea("");
JScrollPane jsc;
JButton ok1=new JButton("OK");
JButton ok2=new JButton("OK");

public dbMonSe2(){
setFrames();
}

public void conn(int nana) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}catch (Exception e) {
System.out.println("로딩실패: "+e);
}

try {
conn=DriverManager.getConnection("jdbc:odbc:oradbs","SCOTT","TIGER");
stmt=conn.createStatement();
}catch (Exception e) {
System.out.println("연결실패: "+e);
}

if (nana==2)
try { // insert into // update 문
String sql=txtEtc.getText();
// boolean bb=stmt.execute(sql); // execute 메소드는 둘다 쓸 수 있다.
String ii = sql.substring(0, 6);
ii = ii.toUpperCase();
if (ii.equals("UPDATE")) {
int re;
re=JOptionPane.showConfirmDialog(this,"수정확인","정말로 수정을 하시겠습니까?",JOptionPane.YES_NO_OPTION);
switch(re) {
case JOptionPane.YES_OPTION:
JOptionPane.showMessageDialog(this, "수정을 실행하였습니다.");
stmt.executeUpdate(sql);
conn.commit();
break;
case JOptionPane.NO_OPTION:
JOptionPane.showMessageDialog(this, "수정을 실행하지 않았습니다.");
break;
}
} else if (ii.equals("DELETE")) {
int re;
re=JOptionPane.showConfirmDialog(this,"삭제확인","정말로 삭제를 실행하시겠습니까?",JOptionPane.YES_NO_OPTION);
switch(re) {
case JOptionPane.YES_OPTION:
JOptionPane.showMessageDialog(this, "삭제를 실행하였습니다.");
stmt.executeUpdate(sql);
conn.commit();
break;
case JOptionPane.NO_OPTION:
JOptionPane.showMessageDialog(this, "삭제를 실행하지 않았습니다.");
break;
}
}else if (ii.equals("INSERT")) {
JOptionPane.showMessageDialog(this, "추가 시킵니다.");
stmt.executeUpdate(sql);
conn.commit();
} else JOptionPane.showMessageDialog(this, "INSERT, DELETE, UPDATE 문만 실행 가능하며,실행쿼리가 알맞지 않습니다.");
conn.commit();
}catch (Exception e) {
JOptionPane.showMessageDialog(this, e+"\n문장의 에러가 있어 실행하지 못했습니다.");
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
try {
conn.setAutoCommit(true);
stmt.close(); conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

if (nana==1)
try {
boolean bb=stmt.execute(txtSet.getText()); // execute 메소드는 둘다 쓸 수 있다.
if (bb) {
rs=stmt.getResultSet();
ResultSetMetaData rmeta=rs.getMetaData();
int count=rmeta.getColumnCount();
rs = stmt.executeQuery(txtSet.getText());
jta.setText("");
while(rs.next()) {
for (int i=1;count >= i;i++){
jta.append(rs.getString(i)+ " ");
}jta.append("\n");
}
} else JOptionPane.showMessageDialog(this, "Select 문장의 에러가 있습니다.");
rs.close(); stmt.close(); conn.close();
}catch (Exception e) {
JOptionPane.showMessageDialog(this, e+"\n문장의 에러가 있어 select문을 실행하지 못했습니다.");
}
// if (nana==2){}

}

public void setFrames() {
this.setLayout(new BorderLayout());
pn.setLayout(new GridLayout(3,1));
group = new CheckboxGroup(); // awt에서는 라디오 버튼 같은 것을 그룹화 시켜주어야 한다.
rdoSel = new Checkbox("select", group, true);
rdoEtc = new Checkbox("그 외", group, false);
txtEtc.setEnabled(false);
rdoEtc.addItemListener(this);
rdoSel.addItemListener(this);
ok1.addActionListener(this);
ok2.addActionListener(this);
ok2.setEnabled(false);
jsc = new JScrollPane(jta);

pn.add(pn1);
pn.add(pn2);
pn.add(pn3);
pn1.add(rdoSel);
pn1.add(rdoEtc);
pn2.add(jl1);
pn2.add(txtSet);
pn2.add(ok1);
pn3.add(jl2);
pn3.add(txtEtc);
pn3.add(ok2);

this.add("North",pn);
this.add("Center",jsc);
}

public void itemStateChanged(ItemEvent e) {
if (e.getSource() == rdoSel) {
txtSet.setEnabled(true);
txtEtc.setEnabled(false);
ok1.setEnabled(true);
ok2.setEnabled(false);
}
if (e.getSource() == rdoEtc) {
txtSet.setEnabled(false);
txtEtc.setEnabled(true);
ok1.setEnabled(false);
ok2.setEnabled(true);
}
}

public void actionPerformed(ActionEvent e) {
if(e.getSource().equals(ok1)) // 1번 OK버튼 선택시
if (txtSet.getText().equals("")) {
JOptionPane.showMessageDialog(this, "select 문장의 값을 넣어주세요.");
} else conn(1);

if(e.getSource().equals(ok2)) // 2번 OK버튼 선택시
if (txtEtc.getText().equals("")) {
JOptionPane.showMessageDialog(this, "기타문 문장의 값을 넣어주세요.");
}else conn(2);
}
}


dbmonse3는 부서 입력하는 화면이고..
dbmonse2는 sql 입력하는 것과 기타문에 대한 설명이다.
demonse1는 상품명과 수량 단가를 입력하는 화면입니다.