반응형
SearchZipcode
동이름(2글자 이상의 부분) 입력받아서 => 우편번호 리스트 출력
select ~ from zipcode where dong like '입력%'
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class SearchZipcode {
public static void main(String[] args) {
// 입력 한번만
// 1자 이하로 입력 에러 ...
// 동만 ... 리 포함해서 검색
Scanner scan = new Scanner(System.in);
System.out.print("동이름을 입력 하세요 : ");
String strDong = scan.nextLine();
scan.close();
if(strDong.length() <= 1) {
System.out.println("동이름을 2자 이상으로 입력하셔야 합니다.");
System.exit(0);
}
if(!strDong.matches(".*[ㄱ-ㅎㅏ-ㅣ가-힣]+.*")) {
System.out.println("한글로 입력해 주시기 바랍니다");
}
// 조건 한글이 아니면 에러
//else if(!strDong.matches(".*[ㄱ-ㅎㅏ-ㅣ가-힣]+.*")) {
if (strDong.charAt(0)<0xAC00 || strDong.charAt(0)>0xD7A3) {
System.out.println("동이름의 첫자를 한글로 입력해 주시기 바랍니다");
System.exit(0);
}
String url = "jdbc:oracle:thin:@192.168.0.19:1521:orcl";
//String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
Statement stmt = null;
// select를 위한 구문 select는 resultSet에 저장해서 불러와야 하기 때문이다
ResultSet rs = null;
//PreparedStatement pstmt = null;
try {
// 요 아래 드라이버 불러오는 문법을 알면 안에 내용만 바뀌지 방법은 똑같다
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("드라이버 로딩 성공");
conn = DriverManager.getConnection(url, user, password);
System.out.println("연결 성공");
stmt = conn.createStatement();
String sql = String.format("select zipcode, sido, gugun, dong, ri, bunji from zipcode where dong || ' ' || ri like '%s'", "%" + strDong +"%");
rs = stmt.executeQuery(sql);
while(rs.next()) {
// 출력 구문
System.out.printf("[%s]\t%s\t%s\t%s\t%s\t%s\n",
rs.getString("zipcode"),
rs.getString("sido"),
rs.getString("gugun"),
rs.getString("dong"),
rs.getString("ri"),
rs.getString("bunji")
);
//break;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(conn != null) try{conn.close();} catch(SQLException e) {};
if(stmt != null) try{stmt.close();} catch(SQLException e) {};
if(rs != null) try{rs.close();} catch(SQLException e) {};
}
}
}
MetaEx01 - databaseMetadata 주변정보를 가져 올 수 있다(이름, 버전 등)
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
public class MetaEx01 {
public static void main(String[] args) {
// Database에 대한 정보
// 객체(Table)에 대한 정보
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("드라이버 로딩 성공");
conn = DriverManager.getConnection(url, user, password);
System.out.println("연결 성공");
DatabaseMetaData dmd = conn.getMetaData();
System.out.println(dmd.getDatabaseProductName());
System.out.println(dmd.getDatabaseProductVersion());
System.out.println(dmd.getDriverName());
System.out.println(dmd.getDriverVersion());
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(conn != null) {try {conn.close();} catch (SQLException e) {};
}
}
}
}
MetaEx02
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public class MetaEx02 {
public static void main(String[] args) {
// Database에 대한 정보
// 객체(Table)에 대한 정보
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("드라이버 로딩 성공");
conn = DriverManager.getConnection(url, user, password);
System.out.println("연결 성공");
String sql = "select * from emp";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
// column의 개수
//System.out.println(rsmd.getColumnCount());
// column의 이름 확인하는 법
for(int i = 1; i<= rsmd.getColumnCount(); i++) {
System.out.print(rsmd.getColumnName(i) + "\t");
System.out.print(rsmd.getColumnTypeName(i) + "\t");
System.out.print(rsmd.getPrecision(i) + "\t");
System.out.print(rsmd.getScale(i) + "\t");
System.out.print(rsmd.isNullable(i) + "\n");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(conn != null) try {conn.close();} catch (SQLException e) {};
if(pstmt != null) try {pstmt.close();} catch (SQLException e) {};
if(rs != null) try {rs.close();} catch (SQLException e) {};
}
}
}
Desc - 아래 cmd창과 같이 만들기
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public class Desc {
public static void main(String[] args) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
String sql = "select * from emp";
//String sql = "select * from " + args[0];
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs .getMetaData();
System.out.println("이름 널? 유형");
System.out.println("----------------------------------------- -------- ----------------------------");
for (int i=1 ; i <=rsmd.getColumnCount() ; i++) {
String columnName = rsmd.getColumnName(i);
String isNull = rsmd.isNullable(i) == 0 ? "NOT NULL" : "" ;
String columnType = rsmd.getColumnTypeName(i) ;
String columnSize = "";
if (rsmd.getPrecision(i) != 0) {
columnSize = "(" + rsmd.getPrecision(i) + (rsmd.getScale(i) == 0 ? "" : "," + rsmd.getScale(i)) + ")";
}
System.out.printf("%-41s %-8s %s%s\n", columnName, isNull, columnType, columnSize);
}
} catch (ClassNotFoundException e) {
System.out.println("[에러] : " + e.getMessage());
} catch (SQLException e) {
System.out.println("[에러] : " + e.getMessage());
} finally {
if (rs != null) try { rs.close(); } catch(SQLException e ) {}
if (pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
if (conn != null) try { conn.close(); } catch(SQLException e) {}
}
}
}
callable1 - 데이터를 컬럼별로 입력 후 테이블에 추가
create or replace procedure callable1 (
v_result out varchar2
)
is
v_empno emp2.empno%type := 7708;
v_ename emp2.ename%type := '홍길동';
v_job emp2.job%type := '개발';
v_mgr emp2.mgr%type := 1000;
v_hiredate emp2.hiredate%type := '18/06/06';
v_sal emp2.sal%type := 3000;
v_comm emp2.comm%type := 500;
v_deptno emp2.deptno%type := 10;
begin
insert into emp2 values (
v_empno, v_ename, v_job, v_mgr,
v_hiredate, v_sal, v_comm, v_deptno
);
v_result := sql%rowcount || ' 행이 입력되었습니다.';
commit;
end;
/
CallableEx01 - 위에 것을 자바에서 해보자
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
public class CallableEx01 {
public static void main(String[] args) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
// exec callable1(:g_result)
cstmt = conn.prepareCall("call callable1(?)");
cstmt.registerOutParameter(1, OracleTypes.VARCHAR);
cstmt.executeUpdate();
System.out.println(cstmt.getString(1));
} catch (ClassNotFoundException e) {
System.out.println("[에러] : " + e.getMessage());
} catch (SQLException e) {
System.out.println("[에러] : " + e.getMessage());
} finally {
if (conn != null) try { conn.close(); } catch(SQLException e) {}
if (cstmt != null) try { cstmt.close(); } catch(SQLException e) {}
}
}
}
callable2 - 7844의 ename과 sal을 출력
create or replace procedure callable2 (
v_ename out emp.ename%type,
v_sal out emp.sal%type
)
is
begin
select ename, sal
into v_ename, v_sal
from emp
where empno = 7844;
end;
/
CallableEx02- 7844의 ename과 sal을 출력을 자바로
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
public class CallableEx02 {
public static void main(String[] args) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
// exec callable1(:g_result)
cstmt = conn.prepareCall("call callable2(?, ?)");
cstmt.registerOutParameter(1, OracleTypes.VARCHAR);
cstmt.registerOutParameter(2, OracleTypes.VARCHAR);
cstmt.executeUpdate();
System.out.println(cstmt.getString(1));
System.out.println(cstmt.getString(2));
} catch (ClassNotFoundException e) {
System.out.println("[에러] : " + e.getMessage());
} catch (SQLException e) {
System.out.println("[에러] : " + e.getMessage());
} finally {
if (conn != null) try { conn.close(); } catch(SQLException e) {}
if (cstmt != null) try { cstmt.close(); } catch(SQLException e) {}
}
}
}
callable3 - empno를 입력 받아서 ename과 sal을 출력
create or replace procedure callable3 (
v_empno in emp.empno%type,
v_ename out emp.ename%type,
v_sal out emp.sal%type
)
is
begin
select ename, sal
into v_ename, v_sal
from emp
where empno = v_empno;
end;
/
CallableEx03 - empno를 입력 받아서 ename과 sal을 출력을 자바로
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
public class CallableEx03 {
public static void main(String[] args) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
// exec callable3(:g_result, :g_sal)
cstmt = conn.prepareCall("call callable3(?, ?, ?)");
cstmt.setString(1, "7844");
cstmt.registerOutParameter(2, OracleTypes.VARCHAR);
cstmt.registerOutParameter(3, OracleTypes.VARCHAR);
cstmt.executeUpdate();
System.out.println(cstmt.getString(2));
System.out.println(cstmt.getString(3));
} catch (ClassNotFoundException e) {
System.out.println("[에러] : " + e.getMessage());
} catch (SQLException e) {
System.out.println("[에러] : " + e.getMessage());
} finally {
if (conn != null) try { conn.close(); } catch(SQLException e) {}
if (cstmt != null) try { cstmt.close(); } catch(SQLException e) {}
}
}
}
7844
7369
Callable4
사원명을 입력받아서 사원번호, 사원명, 급여, 등급, 부서번호를 출력하는 프로시저를 작성하고 호출하는 자바프로그램을 작성
create or replace procedure callable4 (
v_ename in emp.ename%type,
v_empno out emp.empno%type,
v_sal out emp.sal%type,
v_salgrade out salgrade.grade%type,
v_deptno out emp.deptno%type
)
is
begin
-- 한행...
select e.empno, e.sal, s.grade, e.deptno
into v_empno, v_sal, v_salgrade, v_deptno
from emp e, salgrade s
where e.sal between s.losal and s.hisal
and lower(e.ename) = v_ename;
end;
/
CallableEx04
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
public class CallableEx04 {
public static void main(String[] args) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
// exec callable3(:g_result, :g_sal)
cstmt = conn.prepareCall("call callable4(?, ?, ?, ?, ?)");
cstmt.setString(1, "smith");
cstmt.registerOutParameter(2, OracleTypes.VARCHAR);
cstmt.registerOutParameter(3, OracleTypes.VARCHAR);
cstmt.registerOutParameter(4, OracleTypes.VARCHAR);
cstmt.registerOutParameter(5, OracleTypes.VARCHAR);
cstmt.executeUpdate();
System.out.println("사원번호 : " + cstmt.getString(2));
System.out.println("사원급여 : " + cstmt.getString(3));
System.out.println("사원등급 : " + cstmt.getString(4));
System.out.println("부서번호 : " + cstmt.getString(5));
} catch (ClassNotFoundException e) {
System.out.println("[에러] : " + e.getMessage());
} catch (SQLException e) {
System.out.println("[에러] : " + e.getMessage());
} finally {
if (conn != null) try { conn.close(); } catch(SQLException e) {}
if (cstmt != null) try { cstmt.close(); } catch(SQLException e) {}
}
}
}
refcursur
create or replace procedure refcursor (
v_result out sys_refcursor
)
is
begin
open v_result for
select *
from emp
where deptno = 10;
end;
/
여러 문장을 java로 가져올려면 sys_refcursor로 가져와야 한다.(select * 시)
CallableEx05
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class CallableEx05 {
public static void main(String[] args) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
CallableStatement cstmt = null;
OracleCallableStatement ocstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
cstmt = conn.prepareCall("call refcursor(?)");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.executeUpdate();
ocstmt = (OracleCallableStatement)cstmt;
rs = ocstmt.getCursor(1);
while(rs.next()) {
System.out.println(rs.getString("empno"));
}
} catch (ClassNotFoundException e) {
System.out.println("[에러] : " + e.getMessage());
} catch (SQLException e) {
System.out.println("[에러] : " + e.getMessage());
} finally {
if (conn != null) try { conn.close(); } catch(SQLException e) {}
if (cstmt != null) try { cstmt.close(); } catch(SQLException e) {}
if (ocstmt != null) try { ocstmt.close(); } catch(SQLException e) {}
if (rs != null) try { rs.close(); } catch(SQLException e) {}
}
}
}
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class CallableEx05 {
public static void main(String[] args) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
CallableStatement cstmt = null;
OracleCallableStatement ocstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
cstmt = conn.prepareCall("call refcursor(?)");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.executeUpdate();
ocstmt = (OracleCallableStatement)cstmt;
rs = ocstmt.getCursor(1);
while(rs.next()) {
System.out.println(rs.getString("empno"));
System.out.println(rs.getString("ename"));
System.out.println(rs.getString("job"));
}
} catch (ClassNotFoundException e) {
System.out.println("[에러] : " + e.getMessage());
} catch (SQLException e) {
System.out.println("[에러] : " + e.getMessage());
} finally {
if (conn != null) try { conn.close(); } catch(SQLException e) {}
if (cstmt != null) try { cstmt.close(); } catch(SQLException e) {}
if (ocstmt != null) try { ocstmt.close(); } catch(SQLException e) {}
if (rs != null) try { rs.close(); } catch(SQLException e) {}
}
}
}
refcursor2 - 10번 부서를 입력받아 사원번호, 사원명, 직급 출력하기
create or replace procedure refcursor (
v_deptno in emp.deptno%type,
v_result out sys_refcursor
)
is
begin
open v_result for
select *
from emp
where deptno = v_deptno;
end;
/
CallableEx06
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class CallableEx06 {
public static void main(String[] args) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
CallableStatement cstmt = null;
OracleCallableStatement ocstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
cstmt = conn.prepareCall("call refcursor2(?, ?)");
cstmt.setString(1, "10");
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.executeUpdate();
ocstmt = (OracleCallableStatement)cstmt;
rs = ocstmt.getCursor(2);
while(rs.next()) {
System.out.println(rs.getString("empno"));
System.out.println(rs.getString("ename"));
System.out.println(rs.getString("job"));
}
} catch (ClassNotFoundException e) {
System.out.println("[에러] : " + e.getMessage());
} catch (SQLException e) {
System.out.println("[에러] : " + e.getMessage());
} finally {
if (conn != null) try { conn.close(); } catch(SQLException e) {}
if (cstmt != null) try { cstmt.close(); } catch(SQLException e) {}
if (ocstmt != null) try { ocstmt.close(); } catch(SQLException e) {}
if (rs != null) try { rs.close(); } catch(SQLException e) {}
}
}
}
refcursor3 - 범용적인 활용 법
create or replace procedure refcursor3 (
v_sql in varchar2,
v_result out sys_refcursor
)
is
begin
open v_result for
v_sql;
end;
/
sql에 exec refcursor3('', :g_rc) 구문으로 작성해서 활용할 수 있다.
CallableEx07
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class CallableEx07 {
public static void main(String[] args) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
CallableStatement cstmt = null;
OracleCallableStatement ocstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
cstmt = conn.prepareCall("call refcursor3(?, ?)");
cstmt.setString(1, "select * from emp where deptno = 20");
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.executeUpdate();
ocstmt = (OracleCallableStatement)cstmt;
rs = ocstmt.getCursor(2);
while(rs.next()) {
System.out.println("사원번호 : " + rs.getString(1));
System.out.println("사원이름 : " + rs.getString(2));
System.out.println("부서번호 : " + rs.getString(3));
}
} catch (ClassNotFoundException e) {
System.out.println("[에러] : " + e.getMessage());
} catch (SQLException e) {
System.out.println("[에러] : " + e.getMessage());
} finally {
if (conn != null) try { conn.close(); } catch(SQLException e) {}
if (cstmt != null) try { cstmt.close(); } catch(SQLException e) {}
if (ocstmt != null) try { ocstmt.close(); } catch(SQLException e) {}
if (rs != null) try { rs.close(); } catch(SQLException e) {}
}
}
}
Sqlplus와 같이 만들어 보기
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class CallableEx07 {
public static void main(String[] args) {
Scanner scan = new Scanner(System.in);
System.out.print("SQL> ");
String strSql = scan.nextLine();
scan.close();
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
CallableStatement cstmt = null;
OracleCallableStatement ocstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
cstmt = conn.prepareCall("call refcursor3(?, ?)");
//cstmt.setString(1, "select * from emp where deptno = 20");
cstmt.setString(1, strSql);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.executeUpdate();
ocstmt = (OracleCallableStatement)cstmt;
rs = ocstmt.getCursor(2);
while(rs.next()) {
System.out.println("사원번호 : " + rs.getString(1));
System.out.println("사원이름 : " + rs.getString(2));
System.out.println("부서번호 : " + rs.getString(3));
}
} catch (ClassNotFoundException e) {
System.out.println("[에러] : " + e.getMessage());
} catch (SQLException e) {
System.out.println("[에러] : " + e.getMessage());
} finally {
if (conn != null) try { conn.close(); } catch(SQLException e) {}
if (cstmt != null) try { cstmt.close(); } catch(SQLException e) {}
if (ocstmt != null) try { ocstmt.close(); } catch(SQLException e) {}
if (rs != null) try { rs.close(); } catch(SQLException e) {}
}
}
}
이제 GUI만 배우면 sql developer까지 만들 수 있게 된다.
Assignment
TableList를 실행하면
1. emp
2. dept
3. salgrade
이런 목록(tab)이 나옴
내용을 보기 위한 번호를 입력 -> 1 -> emp 테이블의 데이터가 출력
이걸 무한루프로 하고 q를 누르면 종료
create or replace procedure tablelist (
v_sql in varchar2,
v_result out sys_refcursor
)
is
begin
open v_result for
v_sql;
end;
/import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class Assignment01 {
public static void main(String[] args) {
Scanner scan = new Scanner(System.in);
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
CallableStatement cstmt = null;
CallableStatement cstmt1 = null;
OracleCallableStatement ocstmt = null;
OracleCallableStatement ocstmt1 = null;
ResultSet rs = null;
ResultSet rs1 = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
cstmt = conn.prepareCall("call tablelist(?, ?)");
cstmt.setString(1, "select tname from tab");
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.executeUpdate();
ocstmt = (OracleCallableStatement)cstmt;
rs = ocstmt.getCursor(2);
ArrayList<String> data = new ArrayList<>();
while(rs.next()) {
data.add(rs.getString(1));
System.out.println(rs.getRow() + "." + rs.getString(1));
}
while(true) {
System.out.println("테이블명을 입력하시오. 입력을 마치려면 q를 입력하세요 : ");
System.out.print("SQL> ");
String Strsql = scan.nextLine();
if(Strsql.equals("q")) {
System.out.println("프로그램 종료");
System.exit(0);
}
int number = Integer.parseInt(Strsql)-1;
cstmt1 = conn.prepareCall("call tablelist(?, ?)");
cstmt1.setString(1, "select * from " + (data.get(number)));
cstmt1.registerOutParameter(2, OracleTypes.CURSOR);
cstmt1.executeUpdate();
ocstmt1 = (OracleCallableStatement)cstmt1;
rs1 = ocstmt1.getCursor(2);
ResultSetMetaData rsmd = rs1.getMetaData();
System.out.printf("%-8s\t%-8s\t%-10s\n", "이름","널?","유형");
System.out.print("--------------- --------------- ---------\n");
while(rs1.next()) {
for(int i=1; i<=rsmd.getColumnCount(); i++) {
System.out.print(rsmd.getColumnName(i)+"\t");
System.out.print(rs1.getString(i)+"\n");
}
System.out.println("");
}
for (int i=1 ; i <=rsmd.getColumnCount() ; i++) {
String columnName = rsmd.getColumnName(i);
String isNull = rsmd.isNullable(i) == 0 ? "NOT NULL" : "" ;
String columnType = rsmd.getColumnTypeName(i) ;
String columnSize = "";
if (rsmd.getPrecision(i) != 0) {
columnSize = "(" + rsmd.getPrecision(i) + (rsmd.getScale(i) == 0 ? "" : "," + rsmd.getScale(i)) + ")";
}
System.out.printf("%-8s %-8s %s%s\n", columnName, isNull, columnType, columnSize);
}
System.out.println("");
System.out.print("-----------------------------------------\n");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
//} catch (IOException e) {
// e.printStackTrace();
} finally {
if (conn != null) try { conn.close(); } catch(SQLException e) {};
if (cstmt != null) try { cstmt.close(); } catch(SQLException e) {};
if (ocstmt != null) try { ocstmt.close(); } catch(SQLException e) {};
if (rs != null) try { rs.close(); } catch(SQLException e) {};
if (cstmt1 != null) try { cstmt1.close(); } catch(SQLException e) {};
if (ocstmt1 != null) try { ocstmt1.close(); } catch(SQLException e) {};
if (rs1 != null) try { rs1.close(); } catch(SQLException e) {};
}
}
}
프로시져 하나를 가지고 처음엔 테이블을 나열하는 구문을 실행하고 resultSet에 저장한 값을 Arraylist에 집어넣은후에 scanner로 입력 받은 테이블명을 정수로 변환한 뒤 getMetaData화 한 뒤 getColumnCount로 배열 index를 입력 시 해당 배열이 출력되게 한다. 출력 되는 형식은 아래 에서 조정해주는 구문이 작성되어 있다.
반응형
'Web & Mobile > JSP' 카테고리의 다른 글
| Lecture 32 - JSP(1) JDBC API 사용법(Java와 SQL 연동) (0) | 2023.06.20 |
|---|---|
| Lecture 62 - JSP(20) MyBatis 이용한 게시판, 사진이 나오는 앨범 게시판 (0) | 2019.06.14 |
| Lecture 61 - JSP(19) ZipcodeController, MyBatis 설정법, Mapper (0) | 2019.06.13 |
| Lecture 60 - JSP(18) JSTL, 우편번호검색 (0) | 2019.06.12 |
| Lecture 59 - JSP(17) EL, <%= %>, EL을 Model1 게시판에 적용, JSTL (0) | 2019.06.11 |
댓글