PROGRAMING: 14day java


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class jdbcEx04 {

 public static void main(String[] args) {
  String url = "jdbc:oracle:thin:@192.168.0.19:1521:orcl";
  String user = "scott";
  String password = "tiger";
  
  Connection conn = null;
  Statement stmt = null;
  //select 문장을 사용하기 위해서 ResultSet만듦
  ResultSet rs = 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 = "select dname, loc, deptno + 10 deptno2 from dept";
   //executeUpdate 나머지 전부 처리함
   //executeQuery Select 만 처리함
   rs = stmt.executeQuery(sql);
   //Cursor를 프로그램화 시켜놓은게 ResultSet
   while(rs.next()) {
    //String deptno = rs.getString("deptno+10"); //컬럼을 계산해서 가져올 경우 바뀐 컬럼명을 적어야함
    //String deptno = rs.getString("deptno2");  //aliasing 된 컬럼명을 적어도 됨
    //String dname = rs.getString("dname");
    //String loc = rs.getString("loc");
    
    String deptno = rs.getString(3);  //컬럼 순서를 써도 됨
    String dname = rs.getString(1);
    String loc = rs.getString(2);
    
    System.out.printf("%s\t%s\t%s\n",deptno, dname, loc);
   }
   System.out.println("SQL 실행 성공");
   
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   System.out.println("[에러] : "+e.getMessage());
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   System.out.println("[에러] : "+e.getMessage());
  } finally {
   if(rs != null) try { rs.close(); } catch(SQLException e){}
   if(stmt != null) try { stmt.close(); } catch(SQLException e){}
   if(conn != null) try { conn.close(); } catch(SQLException e){}
  }
 }

}


public class jdbcEx05 {

 public static void main(String[] args) {
  String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; //127.0.0.1 자신의 아이피만 사용
  String user = "scott";
  String password = "tiger";
  
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  
  //10번 부서의 사원정보를 출력 프로그램
  //출력결과:
  //사원번호 사원이름 급여 입사일(xxxx-xx-xx)
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   System.out.println("데이터베이스 로딩 성공");
   
   conn = DriverManager.getConnection(url, user, password);
   System.out.println("데이터베이스 연결 성공");
   
   stmt = conn.createStatement();
   //String sql = "select empno, ename, sal, to_char(hiredate, 'YYYY-MM-DD') from emp";  
   //DB에서 처리
   String sql = "select empno, ename, sal, hiredate from emp"; 
   rs = stmt.executeQuery(sql);
   
   while(rs.next()) {
    
    String empno = rs.getString(1);  
    String ename = rs.getString(2);
    String sal = rs.getString(3);
    String hiredate = rs.getString(4);
    
    //System.out.printf("%s\t %s\t %s\t (%s)\n",empno, ename, sal, hiredate);
    System.out.printf("%s\t %s\t %s\t (%s)\n",empno, ename, sal, hiredate.substring(0, 10)); 
           //JAVA에서 처리
   }
   System.out.println("SQL 실행 성공");
   
  } 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(stmt != null) try { stmt.close(); } catch(SQLException e){}
   if(conn != null) try { conn.close(); } catch(SQLException e){}
  }
 }

}

cmd에서 클래스 파일을 실행시키려고 하는 경우 경로 지정을 잘 해주어야 한다.
이클립스는 자동으로 클래스 파일을 찾아서 실행시키지만 직접 실행 시키려고 하는 경우 경로 설정을 잘 해야함
복사해둔 ojdbc6.jar에 classpath를 설정해서 실행해야함

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class jdbcEx06 {

 public static void main(String[] args) {

  String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
  String user = "scott";
  String password = "tiger";
  
  Connection conn = null;
  PreparedStatement pstmt = null;

  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   System.out.println("데이터베이스 로딩 성공");
   
   conn = DriverManager.getConnection(url, user, password);
   System.out.println("데이터베이스 연결 성공");
   
   //문자열 = '' 없음 // 문자, 숫자 무조건 ?
   //String sql = "insert into dept values (?,?,?)"; 
   //pstmt = conn.prepareStatement(sql);  //미리 구문을 준비함
   //pstmt.setInt(1, 95); //문자열로 집어넣어도 자동 형변환
   //pstmt.setString(2, "영업");
   //pstmt.setString(3, "서울");
   String sql = "update dept set loc = ? where deptno=?";
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, "강남");
   pstmt.setString(2, "95");
   
   
   //sql 매개변수없이 실행됨
   pstmt.executeUpdate();
   System.out.println("SQL 실행 성공");
   
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   System.out.println("[에러] : "+e.getMessage());
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   System.out.println("[에러] : "+e.getMessage());
  } finally {
   if(pstmt != null) try { pstmt.close(); } catch(SQLException e){}
   if(conn != null) try { conn.close(); } catch(SQLException e){}
  }
 }

}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class jdbcEx07 {

 public static void main(String[] args) {

  String url = "jdbc:oracle:thin:@127.0.0.1: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("데이터베이스 연결 성공");
   
   //9로 시작하는 부서를 검색
   //String sql = "select * from dept where deptno like ?%"; //에러남 //물음표를 % 기호로 묶어 줄수 없음
   String sql = "select * from dept where deptno like ?";
   pstmt = conn.prepareStatement(sql);
   //pstmt.setString(1, "95");
   pstmt.setString(1, "9%");
   
   rs = pstmt.executeQuery();
   while(rs.next()){
    String deptno = rs.getString("deptno"); 
    String dname = rs.getString("dname");
    String loc = rs.getString("loc");
    
    System.out.printf("%s\t%s\t%s\n",deptno, dname, loc);
   }
   
   //sql 매개변수없이 실행됨
   pstmt.executeUpdate();
   System.out.println("SQL 실행 성공");
   
  } 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){}
  }
 }

}

//데이터베이스에 주소테이블이 있으면 데이터 삭제하고 없으면 테이블 생성하여 
//zipcode.scv 파일에있는 데이터를 읽어서 데이터베이스에 입력
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class jdbcZipcode {

 public static void main(String[] args) {

  String url = "jdbc:oracle:thin:@127.0.0.1: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 name = "ZIPCODE";
   
   //zipcode 테이블이 있는지 검사
   String sql = "select tname from tab where tname = ?";
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, name);
   
   rs = pstmt.executeQuery();
   //IF문에서 쓰기위해 while 문 밖에서 선언함
   String tname =null;
   while(rs.next()){
    tname = rs.getString("tname");  //sql문 조건이 만족하는 테이블 명이 있으면 tname 변수에 집어넣음
   }
   
   //테이블 있는지 검사
   if(tname != null){
    System.out.println("테이블이 있음");
    //테이블 내용 삭제
    String sql_2 = "delete from " +name;
    pstmt = conn.prepareStatement(sql_2);
    pstmt.executeUpdate();
    System.out.println("테이블 데이터 삭제완료");
   }else{
    System.out.println("테이블이 없음");
    String sql_2 = "create table " +name+"(zipcode char(7),sido varchar2(6), gugun varchar2(27),"
         + "dong varchar2(39),ri varchar(67),bunji varchar2(18), seq number(5))";
    pstmt = conn.prepareStatement(sql_2);
    pstmt.executeUpdate();
    System.out.println("테이블형태 생성완료");
   }
   
   //데이터 읽고 데이터베이스에 쓰기
         BufferedReader br = null;

         try {
    br = new BufferedReader(new FileReader("C:/java/workspace/ZipSearch/src/zipcode_seoul_utf8.csv"));
    String line = "";
    String sql_3 = "insert into " + name +" values(?,?,?,?,?,?,?)";
    pstmt = conn.prepareStatement(sql_3);
    while((line = br.readLine()) != null){
        String[] address = line.split(",");
     pstmt.setString(1, address[0]);
     pstmt.setString(2, address[1]);
     pstmt.setString(3, address[2]);
     pstmt.setString(4, address[3]);
     pstmt.setString(5, address[4]);
     pstmt.setString(6, address[5]);
     pstmt.setString(7, address[6]);
     pstmt.executeUpdate();
    }
    System.out.println("테이블 데이터 삽입완료");
   } catch (FileNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } finally {
    if(br != null) try { br.close(); } catch(IOException e){}
   }
   
   
   System.out.println("SQL 실행 성공");
   
  } 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){}
  }
 }

}


import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

public class jdbcEx08 {

 public static void main(String[] args) {
  String url = "jdbc:oracle:thin:@127.0.0.1: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());
   
   System.out.println(dmd.getURL());
   System.out.println(dmd.getUserName());
   
   
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   System.out.println("[에러] : "+e.getMessage());
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   System.out.println("[에러] : "+e.getMessage());
  } finally {
   if(conn != null) try { conn.close(); } catch(SQLException e){}
  }
 }

}

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 jdbcEx09 {

 public static void main(String[] args) {

  String url = "jdbc:oracle:thin:@127.0.0.1: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();
   System.out.println("컬럼갯수 :"+rsmd.getColumnCount()); //컬럼갯수 확인
   
   for(int i=1; i<=rsmd.getColumnCount();i++){
    /*System.out.println(rsmd.getColumnName(i));
    System.out.println(rsmd.getColumnTypeName(i));
    System.out.println(rsmd.getPrecision(i));   //몇자리 숫자
    System.out.println(rsmd.getScale(i));       // 소수점 몇자
    System.out.println(rsmd.isNullable(i));
    System.out.println();*/
    String columnName = rsmd.getColumnName(i);
    String isNull = rsmd.isNullable(i) ==0? "NOT NULL": "";
    String columnType = rsmd.getColumnTypeName(i);
    String columnSize = "("+rsmd.getPrecision(i) +
      (rsmd.getScale(i)==0?"":", " +rsmd.getScale(i))+")" ;
    
    System.out.printf("%-15s%-10s%s%s\n",columnName,isNull,columnType,columnSize);
    //%-15s : 15칸을 차지
   }
   
   System.out.println("SQL 실행 성공");
  } 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){}
  }
 }

}

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 := '16/01/01';
 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;
/


SQL> @c:\oracle\callable1
SQL> var g_result varchar2(4000)
SQL> exec callable1(:g_result)
SQL> print :g_result
SQL> select * from emp2;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import oracle.jdbc.internal.OracleTypes;

public class jdbcEx10 {

 public static void main(String[] args) {
  String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
  String user = "scott";
  String password = "tiger";
  
  Connection conn = null;
  CallableStatement cstmt = null; //프로시저를 실행하기위함
  
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   System.out.println("데이터베이스 로딩 성공");
   
   conn = DriverManager.getConnection(url, user, password);
   System.out.println("데이터베이스 연결 성공");
   
   cstmt = conn.prepareCall("call callable1(?)");
   cstmt.registerOutParameter(1, OracleTypes.VARCHAR);  //리턴타입 varchar
   cstmt.executeUpdate();
   
   System.out.println(cstmt.getString(1));
   System.out.println("SQL 실행성공");
   
   
  } 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){}
  }
 }

}

create or replace procedure callable2 (
 v_ename  out emp.ename%type,
 v_sal  out emp2.sal%type
)
is

begin
 select ename,sal
 into v_ename, v_sal
 from emp
 where empno=7788;

end;
/


SQL> @c:\oracle\callable2
SQL> var g_ename varchar2(10)
SQL> var g_sal number
SQL> exec callable2(:g_ename, :g_sal)
SQL> print :g_ename :g_sal


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import oracle.jdbc.internal.OracleTypes;

public class jdbcEx11 {

 public static void main(String[] args) {
  String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
  String user = "scott";
  String password = "tiger";
  
  Connection conn = null;
  CallableStatement cstmt = null; //프로시저를 실행하기위함
  
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   System.out.println("데이터베이스 로딩 성공");
   
   conn = DriverManager.getConnection(url, user, password);
   System.out.println("데이터베이스 연결 성공");
   
   cstmt = conn.prepareCall("call callable2(?,?)");
   cstmt.registerOutParameter(1, OracleTypes.VARCHAR);
   cstmt.registerOutParameter(2, OracleTypes.VARCHAR);
   
   cstmt.executeUpdate();
   
   String ename = cstmt.getString(1);
   String sal = cstmt.getString(2);
   
   System.out.println(ename);
   System.out.println(sal);
   
   System.out.println("SQL 실행성공");
   
  } 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){}
  }
 }

}

11. 주소 데이터베이스에 넣기 teacher version

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ZipSearch {
 private Connection conn = null;
 
 public ZipSearch() {
  String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
  String user = "scott";
  String password = "tiger";
  
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   System.out.println("데이터베이스 로딩 성공");

   this.conn = DriverManager.getConnection(url, user, password);
   System.out.println("데이터베이스 연결 성공");
   
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   System.out.println("[에러] : " + e.getMessage());
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   System.out.println("[에러] : " + e.getMessage());
  }
 }
 
 public boolean isTable(String tableName) {
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  boolean result = false;
  try {
   String sql = "select count(*) from tab where tabtype=? and tname=?";
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, "TABLE");
   pstmt.setString(2, tableName.toUpperCase());
   
   rs = pstmt.executeQuery();
   if(rs.next()) {
    if(rs.getInt(1) == 0) {
     result = true;
    }
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   System.out.println("[에러] : " + e.getMessage());
  } finally {
   if(rs != null) try { rs.close(); } catch(SQLException e) {}
   if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
  }
  return result;
 }
 
 public void close() {
  if(this.conn != null) try { this.conn.close(); } catch(SQLException e) {}
 }
}


public class ZipSearchMain {

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  ZipSearch zipSearch = new ZipSearch();
  
  if(zipSearch.isTable("emp1")) {
   System.out.println("테이블이 존재하지 않습니다.");
  } else {
   System.out.println("테이블이 존재합니다.");
  }
  
  zipSearch.close();
 }

}

PROGRAMING

2016년 7월 6일 수요일

14day java

댓글 없음:

댓글 쓰기

블로그 보관함