07. 예제

Products 테이블을 가지고 Select, Insert, Update, Delete 해보기

 

1. Select

package sql_ex1;
 
import java.sql.*;
 
public class Select {
 
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "web";
        String password = "1234";
        
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, user, password);
            String sql = "select * from products order by pnum";
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                System.out.print(rs.getInt(1) + "\t");
                System.out.print(rs.getString(2) + "\t");
                System.out.print(rs.getString(3) + "\t");
                System.out.print(rs.getString(4) + "\t");
                System.out.print(rs.getInt(5) + "\t");
                System.out.print(rs.getInt(6) + "\t");
                System.out.print(rs.getInt(7) + "\t");
                System.out.print(rs.getInt(8) + "\t");
                System.out.println(rs.getString(9) + "\t");
                System.out.println("------------------------------------------------------------------------------------");
            }
                        
            rs.close(); pstmt.close(); con.close();
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }        
 
    }
 
}
 

2. Insert

package sql_ex1;
 
import java.sql.*;
import java.util.Scanner;
 
public class Insert {
 
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "web";
        String password = "1234";
        
        Scanner sc = new Scanner(System.in);
        
        System.out.print("카테고리 외래키 : ");
        String category_fk = sc.next();        
        System.out.print("상품명 : ");
        String product_name = sc.next();        
        System.out.print("EP 코드 외래키 : ");
        String ep_code_fk = sc.next();
        System.out.print("입고가격 : ");
        int i_p = sc.nextInt();
        System.out.print("출고가격 : ");        
        int o_p = sc.nextInt();
        System.out.print("배송비용 : ");
        int t_c = sc.nextInt();
        System.out.print("마일리지 : ");
        int mlg = sc.nextInt();
        System.out.print("회사명 : ");
        String co = sc.next();
        System.out.print("상품 상태 : ");
        int st = sc.nextInt();
        
        // count 변수를 선언해서 pk가 되는 pnum을 자동으로 최대값으로 
        // 설정하여 insert 할 있도록 하는 방법.
        int count = 0;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, user, password);
            String sql = "select max(pnum) as pnum from products";
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if(rs.next()) {
                count = rs.getInt("pnum") + 1;
            }                    
            
            sql = "insert into products values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, count);
            pstmt.setString(2, category_fk);
            pstmt.setString(3, product_name);
            pstmt.setString(4, ep_code_fk);
            pstmt.setInt(5, i_p);
            pstmt.setInt(6, o_p);
            pstmt.setInt(7, t_c);
            pstmt.setInt(8, mlg);
            pstmt.setString(9, co);    
            pstmt.setInt(10, st);
            
            int result = pstmt.executeUpdate();
            if (result > 0) {
                System.out.println("상품 정보 삽입 성공 !!");
            }else {
                System.out.println("상품 정보 삽입 실패...");
            }
            
            con.close(); pstmt.close(); sc.close();
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        
 
    }
 
}
 

 

3.Update

package sql_ex1;
 
import java.sql.*;
import java.util.Scanner;
 
public class Update {
 
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pstmt = null;
        //ResultSet rs = null;
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "web";
        String password = "1234";
        
        Scanner sc = new Scanner(System.in);
        System.out.print("수정할 상품 번호: ");
        int pnum = sc.nextInt();
        System.out.print("수정할 상품 입고가: ");
        int input_price = sc.nextInt();
        System.out.print("수정할 상품 배송비: ");
        int trans_cost = sc.nextInt();
        System.out.print("수정할 상품 마일리지: ");
        int mileage = sc.nextInt();
        
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, user, password);
            String sql = "update products set input_price = ?, trans_cost = ?, mileage = ? where pnum = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, input_price);
            pstmt.setInt(2, trans_cost);
            pstmt.setInt(3, mileage);
            pstmt.setInt(4, pnum);
            
            int result = pstmt.executeUpdate();
            if(result > 0) {
                System.out.println("제품 정보 수정 성공 !!");
            }else {
                System.out.println("제품 정보 수정 실패 ...");
            }
            pstmt.close(); con.close(); sc.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
 

4. Delete

package sql_ex1;
 
import java.sql.*;
import java.util.Scanner;
 
public class Delete {
 
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pstmt = null;
        //ResultSet rs = null;
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "web";
        String password = "1234";
        
        Scanner sc = new Scanner(System.in);
        System.out.print("삭제할 제품 번호: ");
        int pnum = sc.nextInt();
        
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, user, password);
            String sql = "delete from products where pnum = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, pnum);
            int result = pstmt.executeUpdate();
            if(result > 0) {
                System.out.println("제품 정보 삭제 성공 !!");
            }else {
                System.out.println("제품 정보 삭제 실패 ...");
            }
            
            //삭제 이후에 삭제한 행번호보다 큰 것들을 -1 하여 전체 숫자를 맞춰주는 방법.
            sql = "update products set pnum = pnum -1 where pnum > ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, pnum);
            pstmt.executeUpdate();
            
            sc.close(); con.close(); pstmt.close();
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
                
 
    }
 
}