search

fluffy0301 / jay_jiwoo

1 stars 0 forks source link

ch17 #4

Open fluffy0301 opened 2 years ago

fluffy0301 commented 2 years ago 
package ch17;

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

public class JDBC20V {

    public static void main(String[] args) 
            throws ClassNotFoundException, SQLException {
        // 1단계  : 오라클이 제공한 접속 프로그램을 자바메모리에 로딩.
        // Class.forname : 이름으로 클래스 파을을 찾아서 메모리에 로딩하는 메소드. 
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 2단계 오라클이 제공한 방법으로 접속. (ip address/port. id/ pwd) 
        String id = "c##scott", pwd = "tiger";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        Connection con = DriverManager.getConnection(url, id, pwd);
        //step 3 : send SQL 
        //statement 는 sql을 담는 바구니 
        // NEVER put ! in sql 
        Statement stmt = con.createStatement();
        String sql = "select empno,ename,job,sal,deptno from emp";
        ResultSet rs = stmt.executeQuery(sql);
        //4단계 : 결과 확인 
          while (rs.next()) {
//            System.out.print(rs.getInt("empno")+"\t");
//              System.out.print(rs.getInt("sal")+"\t");
//              System.out.print(rs.getInt("deptno")+"\t");
//              System.out.print(rs.getString("ename")+"\t");
//              System.out.print(rs.getString("job")+"\t");

              System.out.print(rs.getInt(1));
              System.out.print(rs.getInt(4));
              System.out.print(rs.getInt(5));
              System.out.print(rs.getString(2));
              System.out.println(rs.getString(3));
          }//while
        //5단계 : 접속 해제 : close
          rs.close();
          stmt.close();
          con.close();

    }//main 

}//class

image

이 곳에 거의 모든 자료가 있음

image

오라클에서 제공해 준 프로그램이 이 곳에 있음!

이것을 오라클 프로젝트에 추가하는 법

image

project -> properties -> build path -> add exteral jars

image

ojdbc8.jar apply and close

이게 있어야 컴파일과 모든 것이 가능

  1. 오라클이 제공한 접속 프로그램을 자바 메모리에 로딩하기

Class.forName("oracle.jdbc.driver.OracleDriver");

  1. 오라클이 제공한 방법으로 접속. (ip address/port. id/ pwd) 
String id = "c##scott", pwd = "tiger";
        String url = "jdbc:oracle:thin@localhost:1521:xe";
Connection con = DriverManager.getConnection(url, id, pwd);
  1. send SQL
Statement stmt = con.createStatement();
String sql = "select,empo,emanme,job,sal,deptno from emp";
ResultSet rs = stmt.executeQuery(sql);

4단계 : 결과 확인 

          while (rs.next()) {
//            System.out.print(rs.getInt("empno")+"\t");
//              System.out.print(rs.getInt("sal")+"\t");
//              System.out.print(rs.getInt("deptno")+"\t");
//              System.out.print(rs.getString("ename")+"\t");
//              System.out.print(rs.getString("job")+"\t");

              System.out.print(rs.getInt(1));
              System.out.print(rs.getInt(4));
              System.out.print(rs.getInt(5));
              System.out.print(rs.getString(2));
              System.out.println(rs.getString(3));
          }//while

위의 주석을 삭제하고 어떤 것을 사용해도 결과는 나온다

  1. 접속 해제 : close
 rs.close();
 stmt.close();
 con.close();

image

결과는 이렇게 나온다 !

fluffy0301 commented 2 years ago 
package ch17;

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

public class JDBC3CreateTable {

    public static void main(String[] args)
            throws ClassNotFoundException, SQLException {

        Class.forName("oracle.jdbc.driver.OracleDriver");

        String user = "c##scott", password = "tiger";
        String url ="jdbc:oracle:thin:@127.0.0.1:1521:xe"; 
        Connection con = DriverManager.getConnection(url, user, password);
        //DriverManager : 같은 드라이버를 두 개 이상 로딩 하지 않도록 관리함 
        // ^ 3번 로딩해도 한 번만 생김 

        Statement stmt = con.createStatement();
                //꼭 sqld의 stmt를 import하기 
        String Sql = "create table testboard"
                + "(t_no number(5) primary key"
                + ", t_title varchar2(90)"
                + ", t_writer varchar2(90)"
                + ",t_cnts varchar2(900)"
                + ", t_date date)";

        int successCount= stmt.executeUpdate(Sql); // 1: 성공 0: 실패 
        //stmt.execute.. : select -> ResultSet. insert, update, create-> 성공 건수 
        //stmt.execute.. : insert, update, delete, create-> 성공 건수 

        stmt.close();
        con.close();

    }//main 

}//class

/*
 create sequence tno_seq
 start with 1
 increment by 1
 maxvalue 99999
 nocycle;
 */

package ch17;

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

public class JDBC3Insert {

    public static void main(String[] args)
            throws ClassNotFoundException, SQLException {

        Class.forName("oracle.jdbc.driver.OracleDriver");

        String user = "c##scott", password = "tiger";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        Connection con = DriverManager.getConnection(url, user, password);

        Statement stmt = con.createStatement();
        String sql = "insert into testboard"
                + " values (tno_seq.nextval, '제목', '작성자','내용',sysdate )";
        //띄어쓰기 주의! 

        int successCount = stmt.executeUpdate(sql);//1:성공, 0:실패.
        System.out.println("insert : " + successCount);
        //stmt.execute... : select->ResultSet.
        //stmt.execute... : insert/update/delete->성공 건수.

        stmt.close();
        con.close();

    }//main

}//class

/*
sql developer에서 실행.

create sequence tno_seq
start with 1
increment by 1
maxvalue 99999
nocycle;
*/

sql에서의 결과

Image

Image

fluffy0301 commented 2 years ago 
package ch17;

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

public class JDBC4Update {

    public static void main(String[] args)
            throws ClassNotFoundException, SQLException {

        Class.forName("oracle.jdbc.driver.OracleDriver");

        String user = "c##scott", password = "tiger";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        Connection con = DriverManager.getConnection(url, user, password);

        Statement stmt = con.createStatement();
        String sql = "update testboard "
                + "set t_title = '제목 수정'"
                + ",t_writer = '수정'"
                + ",t_cnts = '내용수정'"
                + "where t_no = 1 ";

        int successCount = stmt.executeUpdate(sql);//1:성공, 0:실패.
        System.out.println("insert : " + successCount);
        //stmt.execute... : select->ResultSet.
        //stmt.execute... : insert/update/delete->성공 건수.

        stmt.close();
        con.close();

    }//main

}//class

/*
sql developer에서 실행.

create sequence tno_seq
start with 1
increment by 1
maxvalue 99999
nocycle;
*/

Image

Image

fluffy0301 commented 2 years ago 

package ch17;

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

public class JDBC5Delete {

    public static void main(String[] args)
            throws ClassNotFoundException, SQLException {

        Class.forName("oracle.jdbc.driver.OracleDriver");

        String user = "c##scott", password = "tiger";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        Connection con = DriverManager.getConnection(url, user, password);

        Statement stmt = con.createStatement();
        String sql = "delete from testboard where t_no = 1";

        int successCount = stmt.executeUpdate(sql);//1:성공, 0:실패.
        System.out.println("insert : " + successCount);
        //stmt.execute... : select->ResultSet.
        //stmt.execute... : insert/update/delete->성공 건수.

        stmt.close();
        con.close();

    }//main

}//class

/*
sql developer에서 실행.

create sequence tno_seq
start with 1
increment by 1
maxvalue 99999
nocycle;
*/

Image

Image