외래키 제약조건으로 테이블 생성하기, prepareStatment

 

 

맨처음  포트번호가 열려있는지 확인 

포트가 열려있지 않으면 sql developer에 접속할 수 없다. 

 

 

hr 계정으로 접속

 

테이블 내용 확인

현재 유저 확인 

select * from tab;

show user;

 

 

 

coutries 테이블을 확인하고 값을넣어보자 

 

countr에서 region에 있는 테이블의 조건이 일치하지 않아서  오류가 뜬다. 

 

 

 

location _id 가 countries_id를 참조하고 있다. 

 

지역은 국가가 있어야 참조될 수 있다. 

 

 

 

---

 

 

외래키는 기본키와 고유키만 참조할 수 있다. 

 

 

sales 테이블과  product 테이블 참조 

 

 

 

기존에있던 sales테이블의 시퀀스와 외래키까지 삭제 

drop sequence sales_seq;
drop table sales cascade constraints purge;

 

sales 테이블에 대한 시퀀스 

create sequence sales_seq
    start with 1
    increment by 1
    maxvalue 9999
    nocycle
    nocache;
    
create table sales (
    idx         number      default sales_seq.nextval primary key,
    salesDate   date        default sysdate,
    product     number      not null,
    cnt         number      check (cnt> 0),
    
    constraint       sales_product_fk
    foreign key     (produc) --참조하는 테이블 
    references      product(idx)
);

 

sales 테이블과  product 테이블 참조 

sales의 제약조건 

상품의 idx를 적어줘야 sales에 값을 넣을 수 있다. 

 

 

 

 

 

insert into sales (salesDate, product,cnt) values ('2023-0701',3,10);

product의 idx번호를 참조하고있지만 

product의 상품번호는 3번이 없어서 부모키가 없어서 무결성 제약조건이 위배된다. 

 

 

 

 

product 에 idx 10번이 없으므로 무결성 제약조건이 위배된다. (부모키 x)
join = 다른테이블의 특정컬럼을 참조해서 조회할 수 있다. 

 

select 
    s.*,
    p.name
    from sales s
    join product P
    on s.product = p.idx;

sales 테이블의 모든 정보와
product 테이블의 이름을 sales 테이블의 s로부터 조회한다.
join으로 상품의 이름 을 idx를 통해 조회할 수 있다.  

 

 

group by를 이용해서 

날짜별 총액을 조회해보자

select salesDate, sum(s.cnt * p.price) as total
    from sales s
    join product p
    on s.product = p.idx
    group by salesDate
    order by s.salesDate;

salesDate와 total의 컬럼을 만들어주고 
sales의 s로부터
product의 p와 연결하여 
s의 상품을 product의 idx를 참조한다
group by 판매날짜로 묶어준다. 

 

자바에서 char는 한글자를 말하고 oracleDB 에서 char는 문자열을 말한다. 

 

이클립스

 

 

 

PreparedStatement
statement의 모든 기능을 상속받은후 
sql쿼리문을 먼저실행시키고 값을 넣는다. 

ojdbc8 라이브러리를 추가하자. 

 

package day23;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;


public class Ex01 {
	public static void main(String[] args) throws Exception {
		//preparedStatement 
		String url = "jdbc:oracle:thin:@192.168.1.100:1521:xe";
		String user = "c##itbank";
		String password = "123456";
		
		String sql = "insert into sales(product,cnt) values(?,?)";
		//String sql = insert into sales (product, cnt) values ("+5+", "+3+")";
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn = DriverManager.getConnection(url, user, password);
		PreparedStatement pstmt = conn.prepareStatement(sql);
		
		pstmt.setInt(1, 5);	//sql에서 첫번째 물음표에 int 5를 넣는다.
		pstmt.setInt(2, 3); //sql에서 두번째 물음표에 int 3를 넣는다. 
							//setString(3, "Hello") 을 수행한다면 자동으로 따옴표처리도 해준다. 
		
		int row = pstmt.executeUpdate();
		System.out.println(row + "행이 추가되었습니다" );
		
		pstmt.close();
		conn.close();
		
		
	}

}

 

스캐너를 이용해서 검색어를 입력해서 product 테이블 조회하기 

prepare 말고 그냥 statement 사용시 

package day23;

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

public class Ex02 {
	public static void main(String[] args) throws Exception {
		String url = "jdbc:oracle:thin:@192.168.1.100:1521:xe";
		String user = "c##itbank";
		String password = "123456";
		
		
	
		
		Scanner sc = new Scanner(System.in);
		String search;
		System.out.print("상품이름을 정확히 입력 : ");
		search = sc.nextLine();
		String sql = "select * from product where name = '" + search +"'";
		
		//String sql = insert into sales (product, cnt) values (" + 5 + ", " + 3 + ")";
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn = DriverManager.getConnection(url, user, password);
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		
		while(rs.next()) {
			System.out.println("상품이름 : " + rs.getString("name"));
			System.out.println("상품가격 : " + rs.getInt("price"));
		}
		rs.close();
		stmt.close();
		conn.close();
		
	}
}

 

 

 

 

전체목록을 한번에 띄우기  (연습으로만 이용 (불법 )) 

SQL INJECTION  1=1 모든 조건이 트루가 되면서 해당 테이블의 정보가 쉽게 노출된다. 

만약 테이블의 정보가  보안에 관련된 데이터 라면? 
기업의 개인정보가 테이블에 담겨있다면  쉽게 노출될 수 있다. 

 

 

 

이제 prepareStatement를 사용해보자 

 

package day23;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Ex03_preparedStatement {
	public static void main(String[] args) throws Exception {
		String url = "jdbc:oracle:thin:@192.168.1.100:1521:xe";
		String user = "c##itbank";
		String password = "123456";
		
		
	
		
		Scanner sc = new Scanner(System.in);
		String search;
		System.out.print("상품이름을 정확히 입력 : ");
		search = sc.nextLine();
//		String sql = "select * from product where name = '" + search +"'";
		String sql = "select * from product where name = ? ";
		
		//String sql = insert into sales (product, cnt) values (" + 5 + ", " + 3 + ")";
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn = DriverManager.getConnection(url, user, password);
//		Statement stmt = conn.createStatement();
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, search);
		ResultSet rs = pstmt.executeQuery();
		
		System.out.println("sql : " +sql );
		while(rs.next()) {
			System.out.println("상품이름 : " + rs.getString("name"));
			System.out.println("상품가격 : " + rs.getInt("price"));
		}
		rs.close();
		pstmt.close();
		conn.close();
		sc.close();
		
	}
}

 

쿼리문을  무력화시키는 코드를 작성해보면 

 

preparedStatement는
statement가 가지는 기능을 모두 상속받았기 때문에 statement의 모든 기능을 사용할 수 있고
따옴표를 굳이 넣지않아도 문자열까지 알아서  처리해준다. 
prepareStatement는 SQL INJECTION ( 쿼리문을 무력화시키는 해킹 ) 까지 방어할 수 있다.