2020.12.18 일지

PreparedStatement.java

package day08;

import java.sql.*;

//PreparedStatement : 준비된 스테이트먼트 
class A {
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:JAVA";
	String usr = "JDBC";
	String pwd = "JAVA";
	
	String tname = "JDBCT";
	Connection con;
	PreparedStatement pstmt1, pstmt2, pstmt3;
	String sql1 = "select * from "+tname+ " order by NO desc";
	String sql2 = "select * from "+tname+ " where NAME like ?";
	String sql3 = "insert into "+tname+" values(?, ?, ?, SYSDATE)";
	A(){
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(url, usr, pwd);
			pstmt1 = con.prepareStatement(sql1);
			pstmt2 = con.prepareStatement(sql2);
			pstmt3 = con.prepareStatement(sql3);
		}catch(ClassNotFoundException cnfe) {
			pln("드라이버가 클래스패스 걸리지 않았음");
		}catch(SQLException se) {
		}
	}
	void selectD() {
		ResultSet rs = null;
		try {
			rs = pstmt1.executeQuery();
			int count = 0;
    	    System.out.println("NO\tNAME\tPHONE\t\tRDATE");
    	    System.out.println("---------- ---------- ----------- --------");
    		while(rs.next()) {
	    		int no = rs.getInt(1);
	    		String name = rs.getString(2);
	    		String phone = rs.getString(3);
	    		Date rdate = rs.getDate(4);
	    		System.out.println(no+"\t"+name+"\t"+phone+"\t"+rdate);
	    		count++;
    		}
    		System.out.println("---------- ---------- ----------- --------");
    		System.out.println("총 "+count+"개 데이터 검색 완료");
		}catch(SQLException se) {
		}finally {
			try {
				rs.close();
			}catch(SQLException se) {}
		}
	}
	void selectD(String na) { //검색문: 속도 빠름
		ResultSet rs = null;
		try {
			pstmt2.setString(1, "%"+na+"%");
			rs = pstmt2.executeQuery();
			int count = 0;
    	    System.out.println("NO\tNAME\tPHONE\t\tRDATE");
    	    System.out.println("---------- ---------- ----------- --------");
    		while(rs.next()) {
	    		int no = rs.getInt(1);
	    		String name = rs.getString(2);
	    		String phone = rs.getString(3);
	    		Date rdate = rs.getDate(4);
	    		System.out.println(no+"\t"+name+"\t"+phone+"\t"+rdate);
	    		count++;
    		}
    		System.out.println("---------- ---------- ----------- --------");
    		System.out.println("총 "+count+"개 데이터 검색 완료");
		}catch(SQLException se) {
		}finally {
			try {
				rs.close();
			}catch(SQLException se) {}
		}
	}
	void insertD(int no, String name, String phone) { //가독성 
		try {
			pstmt3.setInt(1, no);
			pstmt3.setString(2, name);
			pstmt3.setString(3, phone);
			int i = pstmt3.executeUpdate();
			pln(i+"개의 행이 입력됨");
		}catch(SQLException se) {}
	}
	void pln(String str) {
		System.out.println(str);
	}
	void p(String str) {
		System.out.print(str);
	}
	public static void main(String[] args) {
		A a = new A();
		a.selectD();
	}
}

CallableStatement.java

create or replace procedure INCRE(N in EMP.EMPNO%TYPE, R in NUMBER)
is 
   newPay NUMBER;
begin
   select SAL into newPay from EMP where EMPNO=N;
   newPay := newPay + newPay * R;
   update EMP set SAL=newPay where EMPNO=N;
   commit;
end;
/

-- call incre(7369, 0.1);
-- select EMPNO, SAL from EMP2 where EMPNO=7369;

먼저 scott/tiger 계정으로 들어가서 위 procedure를 실행시켜준다.
그러고, 하단 java를 실행시켜주면 된다.

package day08;

import java.sql.*;

//CallableStatement 
class B {
	Connection con;
	CallableStatement cstmt;
	PreparedStatement pstmt;
	
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:JAVA";
	String usr = "scott";
	String pwd = "tiger";
	
	String sql = "call incre(?, ?)";
	String sqlS = "select EMPNO, SAL from EMP where EMPNO=?";
    B(){
    	try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(url, usr, pwd);
			cstmt = con.prepareCall(sql);
			pstmt = con.prepareStatement(sqlS);
		}catch(ClassNotFoundException cnfe) {
			pln("드라이버가 클래스패스 걸리지 않았음");
		}catch(SQLException se) {
		}
    }
    void exe(int empno, float rate) {
    	try {
    		cstmt.setInt(1, empno);
    		cstmt.setFloat(2, rate);
    		cstmt.execute();
    		pln("프로시져 호출 완료");
    	}catch(SQLException se) {
    	}
    	
    	showResult(empno);
    }
    void showResult(int empno) { //pstmt를 이용하여 구현하시오
    	ResultSet rs = null;
    	try {
    		pstmt.setInt(1, empno);
    		rs = pstmt.executeQuery();
    		double sal = 0.0;
    		pln("EMPNO \t SAL");
    		pln("-----------------");
    		if(rs.next()) {
    			//empno = rs.getInt(1);
    			sal = rs.getDouble(2);
    			pln(empno +"\t"+ sal);
    		}
    	}catch(SQLException se) { 
    	}finally {
    		try {
				rs.close();
			}catch(SQLException se) {}
    	}
    }
    void pln(String str) {
		System.out.println(str);
	}
	void p(String str) {
		System.out.print(str);
	}
	public static void main(String[] args) {
         B b = new B();
         b.exe(7902, 0.1f);
	}
}

DynamicCursor.java

동적 커서로 만들어주는 옵션과, rs.next(); rs.previous(); rs.beforeFirst(); //to BOF rs.afterLast(); //to EOF 커서의 위치를 이동시켜주는 메소드들을 이용한 자바 파일이다.

package day08;

import java.sql.*;

class C {
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:JAVA";
	String usr = "JDBC";
	String pwd = "JAVA";
	
	String tname = "JDBCT";
	String sql = "select * from "+tname+" order by NO";
	Connection con;
	Statement stmt;
	ResultSet rs;
	
	C(){
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(url, usr, pwd);
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			/*
			 ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE
			 ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
			 * */
		}catch(ClassNotFoundException cnfe) {
			pln("드라이버가 클래스패스 걸리지 않았음");
		}catch(SQLException se) {
			pln("se: " + se);
		}
	}
	void createRs() {
		try {
			rs = stmt.executeQuery(sql);
			
			forward(rs);
		}catch(SQLException se) {}
	}
	void forward(ResultSet rs) {
		try {
			pln("< 순방향 >");
			pln("NO \t NAME \t PHONE \t RDATE");
			while(rs.next()) {
				int no = rs.getInt(1);
				String name = rs.getString(2);
				String phone = rs.getString(3);
				Date rdate = rs.getDate(4);
				pln(no+"\t"+name+"\t"+phone+"\t"+rdate);
			}
			
			back(rs);
		}catch(SQLException se) {}
	}
	void back(ResultSet rs) { //구현하시오
		try {
			pln("< 역방향 >");
			pln("NO \t NAME \t PHONE \t RDATE");
			while(rs.previous()) {
				int no = rs.getInt(1);
				String name = rs.getString(2);
				String phone = rs.getString(3);
				Date rdate = rs.getDate(4);
				pln(no+"\t"+name+"\t"+phone+"\t"+rdate);
			}
		}catch(SQLException se) {}
	}
	void pln(String str) {
		System.out.println(str);
	}
	void p(String str) {
		System.out.print(str);
	}
	public static void main(String[] args) {
		C c =new C();
		c.createRs();
	}
}

JTableArray.java

package day08;

import java.awt.Container;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;

public class JT1 extends JFrame {
	JTable t;
	Object[] columnNames = {"번호", "이름", "연락처", "날짜"};
	Object rowData[][] = { 
		{"10", "홍길동", "01012341234", "20/12/18"}, 
		{"20", "이순신", "01012341235", "20/12/18"},
		{"30", "강감찬", "01012341236", "20/12/18"}
	};
    void init() {
    	Container cp = getContentPane();
    	t = new JTable(rowData, columnNames); // JTable(Object rowData[][],  Object columnNames[])
    	JScrollPane sp = new JScrollPane(t);
    	cp.add(sp);
    	
    	test();
    	setUI();
    }
    void test() {
        //(1) 임의의 데이터 ( get/set )
    	t.setValueAt("신촌", 1, 2);
    	Object data = t.getValueAt(1, 2); //1행 2열 
    	pln("data: " + data);
    	
    	//(2) 갯수 
        int rc = t.getRowCount();
        int cc = t.getColumnCount();
        pln("rc: " + rc + ", cc: " + cc);
        
        //(3) 컬럼 이름
        String cn = t.getColumnName(1);
        pln("cn: " + cn);
    }
    void setUI() {
    	setTitle("JTable Test1");
    	setSize(300, 200); 
    	setVisible(true);
    	setLocationRelativeTo(null);
		setResizable(false);
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }
    void pln(String str) {
    	System.out.println(str);
    }
	public static void main(String[] args) {
		JT1 j = new JT1();
		j.init();
	}
}

JTableCollection.java

package day08;

import java.awt.Container;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import java.util.*;

public class JT2 extends JFrame {
	JTable t;
	/*
	Object[] columnNames = {"번호", "이름", "연락처", "날짜"};
	Object rowData[][] = { 
		{"10", "홍길동", "01012341234", "20/12/18"}, 
		{"20", "이순신", "01012341235", "20/12/18"},
		{"30", "강감찬", "01012341236", "20/12/18"}
	};*/
	Vector<String> columnNames;
	Vector<Vector> rowData;
	
	JT2(){
		columnNames = new Vector<String>();
		columnNames.add("번호");
		columnNames.add("이름");
		columnNames.add("연락처");
		columnNames.add("날짜");

		rowData = new Vector<Vector>();
		Vector<String> v1 = new Vector<String>();
		v1.add("10"); v1.add("홍길동"); v1.add("01012341234"); v1.add("20/12/18");
		Vector<String> v2 = new Vector<String>();
		v2.add("20"); v2.add("이순신"); v2.add("01012341235"); v2.add("20/12/18");
		Vector<String> v3 = new Vector<String>();
		v3.add("30"); v3.add("강감찬"); v3.add("01012341236"); v3.add("20/12/18");
		
		rowData.add(v1); rowData.add(v2); rowData.add(v3);
	}
    void init() {
    	Container cp = getContentPane();
    	t = new JTable(rowData, columnNames);
    	JScrollPane sp = new JScrollPane(t);
    	cp.add(sp);
    	
    	test();
    	setUI();
    }
    void test() {
        //(1) 임의의 데이터 ( get/set )
    	t.setValueAt("신촌", 1, 2);
    	Object data = t.getValueAt(1, 2); //1행 2열 
    	pln("data: " + data);
    	
    	//(2) 갯수 
        int rc = t.getRowCount();
        int cc = t.getColumnCount();
        pln("rc: " + rc + ", cc: " + cc);
        
        //(3) 컬럼 이름
        String cn = t.getColumnName(1);
        pln("cn: " + cn);
    }
    void setUI() {
    	setTitle("JTable Test2");
    	setSize(300, 200); 
    	setVisible(true);
    	setLocationRelativeTo(null);
		setResizable(false);
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }
    void pln(String str) {
    	System.out.println(str);
    }
	public static void main(String[] args) {
		JT2 j = new JT2();
		j.init();
	}
}