JDBC 1일차 : 오라클, Mysql 연동

오라클과 연동하기

일단 ojdbc14.jar를 받자

연동 순서

① jdbc 드라이버

Class.forName(“드라이버이름”); 

② 커넥션 얻기

conn=DriverManager.getConnection(url,id,password);

③ statement 얻기

Statement stmt=conn.createStatement(); 

④ 질의문 실행

ResultSet rs=stmt.executeQuery("Select * from ......"); //얘는 반환값이 나온다 (셀렉트는 데이타를 얻어오는 것이므로...)
Int n=stmt.executeUpdate(delete or update or insert 문); // 이건 몇개 데이터가 수정되엇는지 정도만 반환 

⑤ ResultSet 해제

rs.close(); 

⑥ Statement 해제

stmt.close(); 

⑦ DB연결해제

conn.close(); 

package test.day0418;

import java.sql.DriverManager;
import java.sql.SQLException;

public class OracleDBEx8 {

  String driver="oracle.jdbc.driver.OracleDriver";
  String username = "acorn03";
  String password = "a1234";
  String url = "jdbc:oracle:thin:@192.168.0.201:1521:orcl";

  //드라이버는 보통 한번만 실행하면 되므로 생성자에 코드를 넣어준다.
  public OracleDBEx8() {
    try {
      Class.forName(driver);
      System.out.println("오라클 드라이버를 찾았어요!");

    } catch (ClassNotFoundException e) {
      System.out.println("오라클 드라이버를 찾을 수 없어요." + e.getMessage());
    }

    try {
      DriverManager.getConnection(url, username, password);
      System.out.println("연결 성공!");
    } catch (SQLException e) {
      System.out.println("로컬 오라클 연결 실패.." + e.getMessage());
    }
  }

  public static void main(String[] args) {
    // TODO Auto-generated method stub
    OracleDBEx8 ex = new OracleDBEx8();
  }
}

JRE 없어졌을 때 대처법

아무 패키지나 우클릭 -> 빌드패스 -> configure build path -> library -> add library -> JRE system library

JRE 삭제해보기

위랑 똑같은데 library에서 원하는 라이브러리 선택 후 remove

JRE 추가하기

위랑 똑같이 들어가서 library -> add external JARs

JAR 경로가 달라지면 못읽으므로 안전한곳! 에 보관한 뒤 불러올것…

Mysql과 연동하기

mysql-connector-java-5.0.5-bin.jar 를 다운받는다

package test.day0419;

import java.sql.DriverManager;
import java.sql.SQLException;

public class MysqlConnectEx1 {
  String driver ="com.mysql.jdbc.Driver"; 
  String url="jdbc:mysql://localhost:3306/mydb";
  String user="root";
  String password="a1234b5678";
  
  public MysqlConnectEx1() {
    try {
      Class.forName(driver);
      System.out.println("mysql 드라이버 찾았어요!");
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      System.out.println("mysql 드라이버 못찾겠음.. " + e.getMessage());
    }
    
    //로칼 mysql에 연결을 시도해보자!
    try {
      DriverManager.getConnection(url, user, password);
      System.out.println("연결 성공!!");
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      System.out.println("연결 실패.." +e.getMessage());
    }
  }

  public static void main(String[] args) {
    // TODO Auto-generated method stub
    MysqlConnectEx1 my = new MysqlConnectEx1();
  }
}

오라클 DB에 연결 & DML

ackage test.day0419;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;

public class JdbcStuEx2 {
  String url="jdbc:oracle:thin:@192.168.0.49:1521:xe";
  String driver ="oracle.jdbc.driver.OracleDriver";
  String user = "acorn03";
  String password="a1234";
  public JdbcStuEx2() {
    try {
      Class.forName(driver);
    } catch (ClassNotFoundException e) {
      System.out.println("드라이버 실패.." + e.getMessage());
    } 
  }
  public Connection getConnection() {
    Connection conn = null;
    try{
      conn = DriverManager.getConnection(url, user, password);
    }catch(SQLException e) {
      System.out.println("커넥션실패 " +e.getMessage());
    }
    return conn;
  }
  public void insertStu(String name, String blood) {
    String sql = "insert into stujh values (seq1jh.nextval,'" + name + "', '" + blood + "',sysdate)" ;
    System.out.println(sql);
    //1. connection 2. statement 3. execute 4. close => 항상 이 순서!!!
    //필요한 변수 선언
    Connection conn = null;
    Statement stmt = null;
    conn = this.getConnection();
    try {
      stmt = conn.createStatement();
      //sql문 실행  
      //stmt.execute(sql); //반환값을 받지 않는 execute. select는 executeQuery로 ...
      int n = stmt.executeUpdate(sql);
      System.out.println(n + "개의 데이타 추가 성공!");
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally {
      try {
        stmt.close();
        conn.close();
      }catch(SQLException e) {
      }
    }
  }
  //db 출력해보자~!
  public void writeAll() {
    String sql = "select * from stujh order by num asc";
    //1. connection 2. statement 3. executeQuery 4. get data 5. close => 항상 이 순서!!!
    //필요한 변수 선언
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null; //ResultSet 인터페이스에서는 주로 next()메서드나 get~~~ 메서드를 쓴다.쿼리결과를 담는 곳.
    conn = this.getConnection();
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      //해당 쿼리문을 실행 한 뒤 나오는 결과값을 rs 인스턴스에 저장한다.
      //select문을 통해 얻은 결과는 ResultSet 의 형태로 된 변수에만 담을 수 있다.
      //제목 출력
      System.out.println("번호\t이름\t혈액형\t날짜");
      System.out.println("======================================");
      while(rs.next()) { //next()메서드는 true/false를 반환함. 다음 데이타로 이동 후 데이타가 존재할경우 트루임 
        // int num = rs.getInt("num");
        // String name = rs.getString("name");
        // String blood = rs.getString("blood");
        // //연월일 뿐 아니라 시분초까지 얻고싶다면 타임스탬프.
        // Timestamp birth = rs.getTimestamp("birth");
        // //근데 이대로 하면 초단위까지 들어가서 지저분하기 때문에 Dataformat을 이용해 이쁘게 다듬어주자 
        // 
        //인덱스로 출력하는 예
        int num = rs.getInt(1);
        String name = rs.getString(2);
        String blood = rs.getString(3);
        Timestamp birth = rs.getTimestamp(4);
        System.out.println(num +"\t"+ name +"\t"+ blood +"\t"+ birth);
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally {
      try {
        rs.close();
        stmt.close();
        conn.close();
      }catch(SQLException e) {
      }
    }
  }
  public void writeBlood(String theBlood) { //writeAll의 응용예제 : 해당혈액형만 출력하기~!
    String sql = "select * from stujh where blood='" + theBlood + "' order by num asc";
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null; 
    conn = this.getConnection();
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      System.out.println(theBlood + "형만 출력하기 ");
      System.out.println("번호\t이름\t혈액형\t날짜");
      System.out.println("======================================");
      while(rs.next()) { 

        //인덱스로 출력하는 예
        int num = rs.getInt(1);
        String name = rs.getString(2);
        String blood = rs.getString(3);
        Timestamp birth = rs.getTimestamp(4);
        System.out.println(num +"\t"+ name +"\t"+ blood +"\t"+ birth);
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally {
      try {
        rs.close();
        stmt.close();
        conn.close();
      }catch(SQLException e) {
      }
    }
  }
  public static void main(String[] args) {
    // TODO Auto-generated method stub
    JdbcStuEx2 ex = new JdbcStuEx2();
    //ex.insertStu("캐서린", "AB");
    //ex.insertStu("alice", "B");
    //ex.insertStu("김나영", "O");
    //ex.writeAll();
    ex.writeBlood("AB");
  }
}

package test.day0419;

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

public class JdbcStuEx3 {
  String url="jdbc:oracle:thin:@192.168.0.49:1521:xe";
  String driver ="oracle.jdbc.driver.OracleDriver";
  String user = "acorn03";
  String password="a1234";
  public JdbcStuEx3() {
    // TODO Auto-generated constructor stub
    try {
      Class.forName(driver);
    } catch (ClassNotFoundException e) {
      System.out.println("드라이버 연결 실패" + e.getMessage());
    }
  }
  public Connection getConnection() {
    Connection conn = null;
    try {
      conn = DriverManager.getConnection(url, user, password);
    } catch (SQLException e) {
      System.out.println("커넥션 실패 " + e.getMessage());
    }
    return conn;
  }
  public void countStu() {
    String sql = "select count(*) cnt from stujh";
    Connection conn = this.getConnection();
    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      rs.next();
      System.out.println("총 " + rs.getInt("cnt") + "개의 데이타가 있습니다.");
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally {
      try {
        rs.close();
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
  public void deleteStu(String theName) {
    String sql = "delete from stujh where name='" + theName + "'";
    Connection conn = this.getConnection();
    Statement stmt = null;
    try {
      stmt =conn.createStatement();
      int n = stmt.executeUpdate(sql);
      if(n==0) {
        System.out.println("해당 이름을 가진 데이타가 존재하지 않습니다.");
      }else {
        System.out.println(n + "개의 데이타가 삭제되었습니다.");
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally {
      try {
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }

  public static void main(String[] args) {
    // TODO Auto-generated method stub
    JdbcStuEx3 ex = new JdbcStuEx3();
    ex.countStu();
    //이름으로 조건을줘서 삭제하는 메서드 , 이름이 있으면 name이 삭제되었습니다 출력. 없으면 그런이름 없다고 출력.
    ex.deleteStu("캐서린");
  }
}

DTO, DAO 클래스를 이용하여 DB 처리하기

DTO : 데이타클래스. 생성자+ 게터+ 세터로구성.

package test.day0419;
  
import java.sql.Timestamp;
  
public class StuDTO {
  private int num;
  private String name;
  private String blood;
  private Timestamp birth;
  public StuDTO() {
  }
  
  public StuDTO(int num, String name, String blood, Timestamp birth) {
    super();
    this.num = num;
    this.name = name;
    this.blood = blood;
    this.birth = birth;
  }
  
  public int getNum() {
    return num;
  }
  
  public void setNum(int num) {
    this.num = num;
  }
  
  public String getName() {
    return name;
  }
  
  public void setName(String name) {
    this.name = name;
  }
  
  public String getBlood() {
    return blood;
  }
  
  public void setBlood(String blood) {
    this.blood = blood;
  }
  
  public Timestamp getBirth() {
    return birth;
  }
  
  public void setBirth(Timestamp birth) {
    this.birth = birth;
  }
}

DAO : 데이타를처리하는클래스. DML 명령어들…을 메서드로!

package test.day0419;
  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
  
public class StuDAO {
  
  String url="jdbc:oracle:thin:@192.168.0.49:1521:xe";
  String driver ="oracle.jdbc.driver.OracleDriver";
  String user = "acorn03";
  String password="a1234";
  public StuDAO() {
    try {
      Class.forName(driver);
    } catch (ClassNotFoundException e) {
      System.out.println("driver failed" + e.getMessage());
    }
  }
  public Connection getConnection() {
    Connection conn = null;
    try {
      conn = DriverManager.getConnection(url,user,password);
    } catch (SQLException e) {
      System.out.println("conn failed" +e.getMessage());
    }
    return conn;
  }
  public Vector<StuDTO> getAllDatas(){
    Vector<StuDTO> list = new Vector<StuDTO>();
    String sql = "select * from stujh order by num asc";
    Connection conn= null;
    Statement stmt = null;
    ResultSet rs = null;
    conn = this.getConnection();
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      while(rs.next()) {
        list.add(new StuDTO(rs.getInt("num"), rs.getString("name"), rs.getString("blood"), rs.getTimestamp("birth"))); 
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally {
      try {
        rs.close();
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
    return list;
  }
  public void insert(String name, String blood) {
    String sql = "insert into stujh values(seq1jh.nextval, '" + name + "', '" + blood + "', sysdate)";
    Connection conn = null;
    Statement stmt = null;
    conn = this.getConnection();
    try {
      stmt = conn.createStatement();
      int n = stmt.executeUpdate(sql);
      System.out.println("총 " + n + "개의 행이 추가되었습니다.");
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally {
      try {
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
  public void delete(String name) {
    String sql = "delete from stujh where name='" + name + "'";
    Connection conn = null;
    Statement stmt = null;
    conn = this.getConnection();
    try {
      stmt = conn.createStatement();
      int n = stmt.executeUpdate(sql);
      if(n==0)
        System.out.println("해당하는 이름과 일치하는 데이타가 없습니다.");
      else
        System.out.println("총 " + n + "개의 행이 삭제되었습니다.");
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally {
      try {
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
  public void modify(String name, int attrnum, String changeTo) {
    String attr = "";
    switch(attrnum) {
      case 1:
        attr = "name";
        break;
      case 2:
        attr = "blood";
        break;
    }
    String sql = "update stujh set " +attr+ "='" + changeTo + "' where name='" +name + "'";
    Connection conn = null;
    Statement stmt = null;
    conn = this.getConnection();
    try {
      stmt = conn.createStatement();
      int n = stmt.executeUpdate(sql);
      if(n==0)
        System.out.println("해당하는 이름과 일치하는 데이타가 없습니다.");
      else
        System.out.println("총 " + n + "개의 행이 수정되었습니다.");
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally {
      try {
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
}

만들어 둔 DAO, DTO 활용하기

package test.day0419;
  
import java.text.SimpleDateFormat;
import java.util.Scanner;
import java.util.Vector;
  
public class JdbcStuEx4 {
  
  StuDAO db;
  Scanner sc;
  public JdbcStuEx4() {
    // TODO Auto-generated constructor stub
    db = new StuDAO();
    sc = new Scanner(System.in);
  }
  public int getMenu() {
    int n = 0;
    System.out.println("============ DB 메뉴 =============");
    System.out.println("1. 추가 2. 삭제 3. 수정 4. 출력 5. 종료");
    n = Integer.parseInt(sc.nextLine());
    return n;
  }
  public void process() {
    while(true) {
      switch(this.getMenu()) {
        case 1:
          this.insert();
          break;
        case 2:
          this.delete();
          break;
        case 3:
          this.modify();
          break;
        case 4:
          this.write();
          break;
        case 5:
          System.out.println("프로그램을 종료합니다. ");
          System.exit(0);
      }
    }
  }
  public void modify() {
    String name;
    int attr;
    String attrName ="";
    String changeTo = "";
    System.out.println("수정을 원하는 데이타의 이름을 입력하세요.");
    name = sc.nextLine();
    System.out.println(name + "의 무엇을 변경하고 싶으세요? 1. 이름 2. 혈액형");
    attr = sc.nextInt();
    sc.nextLine();
    switch(attr) {
      case 1:
        attrName = "이름";
        break;
      case 2:
        attrName = "혈액형";
        break;
    }
    System.out.println(attrName + "을(를) 어떻게 바꿀까요?");
    changeTo = sc.nextLine();
    db.modify(name,attr,changeTo);
  }
  public void delete() {
    String name;
    System.out.println("삭제할 데이터의 이름을 입력하세요.");
    name = sc.nextLine();
    db.delete(name);
  }
  public void insert() {
    String name;
    String blood;
    System.out.println("이름을 입력하세요.");
    name = sc.nextLine();
    System.out.println("혈액형을 입력하세요.");
    blood = sc.nextLine();
    db.insert(name,blood);
  }
  public void write() {
    Vector<StuDTO> list = db.getAllDatas();
    System.out.println("번호\t이름\t혈액형\t생년월일");
    System.out.println("--------------------------------------");
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd HH:mm");
    for(StuDTO dto:list) {
      System.out.println(dto.getNum() + "\t" + dto.getName() + "\t" + dto.getBlood() + "\t" + sdf.format(dto.getBirth()) );
    }
    System.out.println("--------------------------------------");
  
  }
  public static void main(String[] args) {
    // TODO Auto-generated method stub
    JdbcStuEx4 ex = new JdbcStuEx4();
    ex.process();
  }
}
  

Twitter Facebook LinkedIn

Jenny Lee

JDBC 1일차 : 오라클, Mysql 연동

오라클과 연동하기

Mysql과 연동하기

오라클 DB에 연결 & DML

DTO, DAO 클래스를 이용하여 DB 처리하기

Comments

You May Also Enjoy

[LeetCode] 1074 Number of Submatrices That Sum to Target

[CodeJam] Qualification Round 2020

[LeetCode] 4 Median of Two Sorted Arrays

[LeetCode] 3 Longest Substring Without Repeating Characters