JDBC 데이터 삽입, 수정, 삭제

JDBC 데이터 삽입, 수정, 삭제

• 테이블 생성

  package jdbcexam;
  
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.Statement;
  
  public class CreateTable {
  
  	public static void main(String[] args) throws Exception {
  		try {
  			Class.forName("oracle.jdbc.driver.OracleDriver"); 
  		} catch (ClassNotFoundException e) {
  			e.printStackTrace();
  		}
  		
  		try(
  				Connection conn = DriverManager.getConnection(
  						"jdbc:oracle:thin:@localhost:1521:XE",
  						"jdbctest", "jdbctest"
  						);
  				Statement stmt = conn.createStatement(); 
  				) {
  				if(args.length ==1 && args[0].equals("create")) {
  					stmt.executeUpdate("create table product (id char(5), classid char(2), name varchar(50), balance int, price float)");
  					System.out.println("테이블 생성");
  				}  else {
  					stmt.executeUpdate("drop table product");
  					System.out.println("테이블 삭제");
  				}
  		} catch (Exception e) {
  			System.err.println("오류발생 : " + e);
  		}
  	}
  }
  

   

• 데이터 삽입

  • Statement 객체 사용시

  package jdbcexam;
  
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.Statement;
  
  public class InsertTable {
  
  	public static void main(String[] args) throws Exception {
  		try	 {
  			Class.forName("oracle.jdbc.driver.OracleDriver"); 
  			Connection conn = DriverManager.getConnection(
  					"jdbc:oracle:thin:@localhost:1521:XE",
  					"jdbctest", "jdbctest"
  					);
  			Statement stmt = conn.createStatement(); 
  			stmt.executeUpdate("insert into product values ('" + args[0] + "','" + args[1]+ "','" +  args[2]+ "'," +  args[3] + "," + args[4] +")");
  			System.out.println("데이터들을 추가하였습니다.");
  			stmt.close();
  			conn.close();
  		} catch (Exception e) {
  			System.err.println("오류발생 : " + e);
  		}
  	}
  }
  

  • PreparedStatement 객체 사용시

  package jdbcexam;
  
  import java.sql.*;
  
  public class InsertTable3 {
  
  	public static void main(String[] args) throws Exception {
  		try	 {
  			Class.forName("oracle.jdbc.driver.OracleDriver"); 
  			Connection conn = DriverManager.getConnection(
  					"jdbc:oracle:thin:@localhost:1521:XE",
  					"jdbctest", "jdbctest"
  					);
  			PreparedStatement pstmt = conn.prepareStatement("insert into product values (?,?,?,?,?)"); 
  			pstmt.setString(1, args[0]);
  			pstmt.setString(2, args[1]);
  			pstmt.setString(3, args[2]);
  			pstmt.setInt(4, Integer.parseInt(args[3]));
  			pstmt.setFloat(5, Float.parseFloat(args[4]));
  			pstmt.executeUpdate(); 
  			conn.commit();
  			System.out.println("데이터들을 추가하였습니다.");
  			pstmt.close();
  			conn.close();
  		} catch (Exception e) {
  			System.err.println("오류발생 : " + e);
  		}
  	}
  }
  

   

• 데이터 수정

  package jdbcexam;
  
  import java.sql.*;
  
  public class UpdateTable {
  
  	public static void main(String[] args) throws Exception {
  		try	 {
  			Class.forName("oracle.jdbc.driver.OracleDriver"); 
  			Connection conn = DriverManager.getConnection(
  					"jdbc:oracle:thin:@localhost:1521:XE",
  					"jdbctest", "jdbctest"
  					);
  			PreparedStatement pstmt = conn.prepareStatement("update product set balance = 100 where id = ?");
  			pstmt.setString(1, args[0]);
  			int update_su = pstmt.executeUpdate();
  			System.out.println("수정된 레코드 수 : " + update_su);
  			System.out.println("상품번호가 " + args[0] + "인 상품의 balance 필드 수정완료");
  			pstmt.close();
  			conn.close();
  		} catch (Exception e) {
  			System.err.println("오류발생 : " + e);
  		}
  	}
  }
  

   

• 데이터 삭제

  package jdbcexam;
  
  import java.sql.*;
  
  public class DeleteTable {
  
  	public static void main(String[] args) throws Exception {
  		try	 {
  			Class.forName("oracle.jdbc.driver.OracleDriver"); 
  			Connection conn = DriverManager.getConnection(
  					"jdbc:oracle:thin:@localhost:1521:XE",
  					"jdbctest", "jdbctest"
  					);
  			PreparedStatement pstmt = conn.prepareStatement("delete from product where balance <  ?"); 
  			pstmt.setInt(1, Integer.parseInt(args[0]));
  			int del_su = pstmt.executeUpdate(); 
  			System.out.println("재고가 " + args[0] + "이하인 상품 정보를 " + del_su + "개 삭제하였습니다." );
  			pstmt.close();
  			conn.close();
  		} catch (Exception e) {
  			System.err.println("오류발생 : " + e);
  		}
  	}
  }