2. JDBC - Statement 사용

members 테이블

 drop table MEMBERS cascade constraints;

 CREATE TABLE MEMBERS(

    ID VARCHAR2(10) PRIMARY KEY,

    PWD VARCHAR2(10),

    EMAIL VARCHAR2(15),

    PHONE VARCHAR2(20),

    REGDATE DATE);

 

 

 

EX> DB접속 후 Members테이블에 입력받은 값을 입력, 수정, 삭제, 조회하기.

 

Test02.java

package test01.jdbc;

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

class MyJdbc02{

    Connection con;
    Statement stmt;

 

    Scanner scan=new Scanner(System.in);

    public MyJdbc02() {
        try{
            //드라이버로딩
            Class.forName("oracle.jdbc.OracleDriver");

            String url="jdbc:oracle:thin:@localhost:1521:XE";

 

            //db접속및 컨넥션객체 얻어오기
            con=DriverManager.getConnection(url,"scott","tiger");
            System.out.println("db접속성공!");

 

            //sql구문을 실행하기위한 객체 얻어오기
            stmt=con.createStatement();
        }catch(ClassNotFoundException ce){
            System.out.println(ce.getMessage());
        }catch(SQLException se){
            System.out.println(se.getMessage());
        }
    }

 

    //DB접속 해제
    public void disconnect() throws SQLException{
        if(stmt!=null) stmt.close();
        if(con!=null) con.close();
    }
   
    //키보드로 데이터 입력받아 추가하기
    public void insert() throws SQLException{

 

        //키보드로 데이터 입력받기
        System.out.println("아이디입력");
        String id=scan.next();
        System.out.println("비밀번호입력");
        String pwd=scan.next();
        System.out.println("이메일입력");
        String email=scan.next();
        System.out.println("전화번호입력");
        String phone=scan.next();

        //입력된 데이터 db에 저장하기
        //String sql="insert into members " +
        //           "values('id','pwd','email','phone',sysdate)";
        //변수는 ""밖으로 빠져야 한다.

 

        String sql="insert into members values('" +
                     id +"','" +
                     pwd +"','" +
                     email +"','" +
                     phone +"',sysdate)";

 

        int n=stmt.executeUpdate(sql)    ;

 

        System.out.println(n+"개의 데이터가 성공적으로 추가되었어요!");
    }

 

    public void delete() throws SQLException{

        //삭제할 아이디 키보드로 입력받기
        System.out.println("삭제할 아이디 입력");
        String id=scan.next();

        String sql="delete from members where id='" +id +"'";

 

        //sql구문 실행하기(삭제)
        int n=stmt.executeUpdate(sql);

 

        System.out.println(n+"개의 데이터가 성공적으로 삭제됨!");
    }

 

    //수정할 아이디를 입력받아 이멜과 전화번호를 수정해 보세요.
    public void update() throws SQLException{
        System.out.println("수정할 아이디 입력");
        String id=scan.next();
        System.out.println("새로운 이멜 입력");
        String email=scan.next();
        System.out.println("새로운 전화번호 입력");
        String phone=scan.next();

 

        String sql="update members "+
                   "set email='" +email +"',phone='" + phone +"' " +
                   "where id='" +id +"'";

        int n=stmt.executeUpdate(sql);

 

        System.out.println(n+"개의 데이터 수정성공!");
    }

 

    //전체 데이터를 출력하는 메소드 추가
    public void showMembers() throws SQLException{
       
    }
}

public class Test02 {
    public static void main(String[] args) {
        MyJdbc02 mj=new MyJdbc02();//생성되면서 생성자에서 db에 접속
        try{
            mj.insert();//데이터 추가하기
            mj.delete();//데이터 삭제하기
            mj.update();
            //mj.showMembers();
        }catch(SQLException se){
            System.out.println(se.getMessage());
        }finally{
            try{
                mj.disconnect();//db접속해제하기
            }catch(SQLException se){
                System.out.println(se.getMessage());
            }
        }
    }
}

 

 

 

ex> SELECT 후 화면에 출력하기.

Test03.java

package test01.jdbc;

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

class MyJdbc03{
    public MyJdbc03() {
        Connection con=null;
        Statement stmt=null;
        ResultSet rs=null;
        try{
            Class.forName("oracle.jdbc.OracleDriver");

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

            System.out.println("db접속성공!");

            stmt=con.createStatement();

 

            String sql="select * from members";
            rs=stmt.executeQuery(sql);

 

            //다음행으로 이동
            while(rs.next()){
                //현재행의 id컬럼의 값 얻어오기
                String id=rs.getString("id");

                //현재행의 pwd컬럼의 값 얻어오기
                String pwd=rs.getString("pwd");

                //현재행의 email컬럼의 값 얻어오기
                String email=rs.getString("email");

                //현재행의 phone컬럼의 값 얻어오기
                String phone=rs.getString("phone");

                //현재행의 regdate컬럼의 값 얻어오기
                Date regdate=rs.getDate("regdate");

 

                System.out.println("아이디:" + id);
                System.out.println("비밀번호:" + pwd);
                System.out.println("이메일:" + email);
                System.out.println("전화번호:" + phone);
                System.out.println("가입일:" + regdate);
            }
        }catch(ClassNotFoundException ce){
            System.out.println(ce.getMessage());
        }catch(SQLException se){
            System.out.println(se.getMessage());
        }finally{
            try{
                if(rs!=null) rs.close();
                if(stmt!=null) stmt.close();
                if(con!=null) con.close();
            }catch(SQLException se){
                System.out.println(se.getMessage());
            }
        }
    }
}
public class Test03 {
    public static void main(String[] args) {
        new MyJdbc03();
    }
}