SQL

COMMIT 및 ROLLBACK 문의 장점

롤백 커밋을 이용하면
데이터가 변경이 됐을 때 데이터가 어떻게 변하는지 확인할 수 있다.
그래서 내가 원하는 변경이 됐으면 커밋을 하면 된다.

트랜잭션 제어

savepoint : 완료를 하거나 취소를 하는 기준점을 심어놓는 것
그래서 구분할 수 있는 이름이 필요하다.

ex) savepoint a, savepoint b ...

rollback + savepoint

쓰기 작업을 완료해야 (커밋을 해야) 데이터의 변경사항이 다른 사람에게도 보여진다.
즉, 커밋을 하지 않으면 내 컴퓨터에서만 작동하는 것과 같으므로 다른 연결에서는 보이지 않는다.
커밋을 해야 다른 연결에도 그 내용이 반영된다.
커밋을 하면 삭제할 수가 없다.
(커밋을 하면 그 전으로 되돌아갈 수 없다.)

동시작업 쓰레드 ex) 화장실

동시에 작업하는 것처럼 보이는 것

같은 데이터를 동시에 같이 수정을 할 수도 있다.
하지만 그러면 데이터가 망가지게 되는데
망가지는 걸 막으려면 같이 사용하는 데이터에 화장실 칸을 만들어 그 공간을 공유할 수 없게 잠궈버린다.
그리고 쓰기를 완료하면 화장실 문을 열어 놓는다.

그리고 데이터베이스는 사람들이 접속해서 사용하는 것이기 때문에 읽기 일관성을 고려하는게 중요하다

베타적 접근

데이터 읽고 있을 때 다른 사람이 쓰지 못하게 막을건지 그 범위를 정하는것
오라클은 전체 잠금이 아니라 행잠금만 한다
수정하고 있는 행부분만 잠근다는 것

그래야 다른 사람들이 읽고 싶은 부분이 있으면 읽을 수 있으므로
그렇게 하지 않으면 수정할 때까지 기다려야 하니까

select * from emp order by emp_id;

insert into emp values (4, 'im', 8000, sysdate, 40);
savepoint a;
insert into emp values (5, 'kil', 9000, sysdate, 50);
savepoint b;
insert into emp values (6, 'eun', 8800, sysdate, 60);

rollback to a;
rollback;
commit;

default 값 설정

다른 사람의 테이블을 읽고 싶을 때 스키마를 지정해줘야 한다.
default : null이 아니라 지정한 값이 들어가진다.

CREATE TABLE dept(
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(20) NOT NULL,
loc VARCHAR2(20) DEFAULT '서울' 
-- 기본 값 설정 
);

insert into dept values(10,'부서1','경기');
insert into dept(dept_id, dept_name) values(20,'부서2');

alter

select * from dept;

alter table dept80
add (job_id varchar2(9));

alter table dept80
modify (last_name varchar2(30));

// drop은 하나에 하나씩만 삭제가 가능하다 
// set unused는 여러개 삭제가 가능하다 


alter table dept 
add (manager_id number);

alter table dept
modify (manager_id varchar2(10)); 
-- 데이터가 없어서 에러나지 않음

alter table dept
modify (dept_name number); 
-- 에러남 : 값이 있어서

그리고 글자 크기 30 → 20으로 되지만 그 반대는 어렵다.
왜냐하면 데이터 유실이 발생할 수 있기 때문이다.

truncate

테이블의 전체 데이터를 삭제할 때 사용한다.
이 명령어를 실행하면 commit이 되면서 취소가 불가해진다.

truncate table dept; 
-- 모든 행 삭제, 취소 불가 

drop table dept;

select * from tabs;

🎈 TIP 인덱스

유니크나 프라이머리키를 설정하면 자동으로 인덱스가 생성된다.
인덱스는 트리구조로 되어 있다.
그래서 너무 많이 설정하면 속도가 느려질 수 있다.

select * from member;

create table board(
num number primary key,
writer varchar2(20) references member(id), -- 약식으로 간단하게 정의 fk
w_date date,
title varchar2(50),
content varchar2(100)
);
-- default sysdate는 날마다 값이 다르기 때문에 설정 불가능

insert into member values ('ccc3333','1234567','whang','whang@naver.com');

sequence

자동 넘버링 = 시퀀스?
시퀀스 객체를 이용한다.

create sequence seq_board;  -- 글번호로 사용

-- 어떻게 자동으로 만드느냐?
select seq_board.nextval from dual;

한 번 사용된 번호는 날라간 것이다.
번호를 소비하는 것
생성해서 주고 그 다음거 생성해서 주고

현재 값을 읽고 싶다?, 어디까지 생성되었는지? 현재 값 보기

select seq_board.currval from dual;

insert into board values(seq_board.nextval, 'abc', sysdate, 'title1', 'content1');
// 가입이 안 되어 있는 사람이라 오류가 난다

insert into board values(seq_board.nextval, 'aaa', sysdate, 'title1', 'content1');
insert into board values(seq_board.nextval, 'ccc', sysdate, 'title2', 'content2');
insert into board values(seq_board.nextval, 'aaa', sysdate, 'title3', 'content3');

on delete

on delete cascade
테이블 생성할 때 foregin key 지정을 할 때 해준다.
자식(board)이 참조하는 부모(member) 테이블의 행이 삭제될 때 자식행까지 같이 삭제

create table board(
num number primary key,
writer varchar2(20) references member(id) on delete cascade, -- 약식으로 간단하게 정의 fk
w_date date,
title varchar2(50),
content varchar2(100)
);

insert into member values('aaa','1234567','namea','aaa@email.com');
insert into member values('bbb','1234567','nameb','bbb@email.com');
insert into member values('ccc','1234567','namec','ccc@email.com');

delete member where id='aaa';
-- on delete cascade를 설정해놔서 글을 썼어도 탈퇴가 가능하다. 

on delete set null
자식(board)이 참조하는 부모(member) 테이블의 행이 삭제될 때
참조하는 자식 컬럼을 null로 셋

on delete set null :
탈퇴한 사람이 쓴 글을 삭제하진 않고 걔를 널로 변환

create table board(
num number primary key,
writer varchar2(20) references member(id) on delete set null, -- 약식으로 간단하게 정의 fk
w_date date,
title varchar2(50),
content varchar2(100)
);

사용자 정의로 제약 설정 → check
currval, nextval, level 및 rownum(검색한 줄 번호)

💕

시퀀스 값 어떻게 가져와야 하지?

String sql = "insert into board values(seq_board.nextval,?,sysdate,?,?)";

🎈 TIP oracle from 생략 가능 🎈

delete from board where num=1;
delete board where num=1;

JDBC

1. 드라이버 로드. api 사용하기 위해
   
   
2. db에 커넥트(로그인)
   
   
3. sql 작성

   String sql = "insert into test values(1, 'aaa')";

4. PreparedStatement 객체 생성
   자바에서 sql문을 실행하려면 이를 처리할 객체 필요
   => PreparedStatement pstmt = conn.prepareStatement(sql);
   => sql문에 사용시 매칭
   
   
5. 검색/수정/삭제....등의 sql문 실행
   
   
6. 쓰기(추가(insert)/수정(update)/삭제(delete))>

    int num = pstmt.excuteUpdate(); //sql실행. 적용된 줄수

7. 검색(select)
   (1) 한 줄 검색
   
   ResultSet rs = pstmt.executeQuery();
   
   검색한 결과를 반환
   → ResultSet 객체에 담아서 반환한다.
   → ResultSet에서 데이터를 한 줄씩 꺼내 처리
   → rs.next() : 다음줄로 이동.
   
   이동한 줄에 데이터가 있으면 true, 없으면 false 반환
   
   <한 줄 검색 : pk로 검색>
   
   if(rs.next()){
   // 각 컬럼에 있는 값 꺼내서 vo에 담기
   칼럼값 꺼내는 메서드 : rs.get타입 (컬럼순서)
   String writer = rs.getString(1);
   int num=rs.getInt(2);
   return new Vo(writer,num...);
   }
   return null;
   
   
   (2) 여러줄 검색
   
   while(rs.next()){
   // 데이터가 있는 동안 반복
   근데 이 vo에 담는데 여러줄이면 vo가 줄 수만큼 만들어지겠지
   그러면 arraylist에 담아야겠지요
   arraylist 하나 만들기
   }
   
   

8. conn을 닫는다. conn.close(); => db 연결 끊음
   
   
   
   
   
   
   

🎈오늘의 숙제

전체검색
작성자로 검색 (여러개 검색됨)
제목으로 검색(제목에 단어 포함된 것 여러 개 검색)

🎈 놓친거!!

ArrayList li=new ArrayList<>();
li=dao.selectAll();
난 이렇게 썼는데

ArrayList li = dao.selectAll();
이렇게 써도 된다.

🎈 dao

databases access object. db작업 구현

오늘 배운 JDBC

EmpDao.java

package emp;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import conn.DBConnect;

//DAO: Databases Access object. DB 작업 구현
public class EmpDao {
	private DBConnect dbconn; // 연결할 거 만들기

	public EmpDao() {
		dbconn = DBConnect.getInstance();
	}

	// emp 테이블에 한 줄 추가
	// insert()의 파라미터에 vo에 있는 컬럼들의 정보를 넣기
	public void insert(EmpVo vo) {
		Connection conn = dbconn.conn();
		// 모든 db 작업은 Connection 객체로 실행
		// conn을 하면 hr/hr로 로그인을 한 것이다 
		// 그 다음엔 sql문을 작성하면 된다.

		// String sql = "insert into emp values(1, 'minhyun', 7777, sysdate, 30)";
		// 위처럼 쓰지 않고 밑처럼 쓴다. 밑에 있는 ?는 변수값이 들어갈 위치이다.
		String sql = "insert into emp values(?, ?, ?, sysdate, ?)";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			// sql문을 실행할 PreparedStatement 객체 생성
			
			pstmt.setInt(1, vo.getEmpId());
			// 매칭 메서드 : set 타입(물음표순서, 매칭할 값)
			pstmt.setString(2, vo.getName());
			pstmt.setInt(3, vo.getSalary());
			pstmt.setInt(4, vo.getDeptId()); 
			// 물음표만 카운트 
			
			int num = pstmt.executeUpdate();
			// num : DB에서 추가된 줄수. insert는 보통 1
			System.out.println(num + "줄이 추가되었습니다.");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void update(EmpVo vo) {
		// SQL 사번으로 검색해서 salary와 dept_id를 새 값으로 변경하는 메서드
		Connection conn = dbconn.conn();
		String sql = "update emp set salary=?, dept_id=? where emp_id=?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setInt(1, vo.getSalary());
			pstmt.setInt(2, vo.getDeptId());
			pstmt.setInt(3, vo.getEmpId());

			int num = pstmt.executeUpdate();
			System.out.println(num + "줄이 수정되었습니다.");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	// 사번 기준으로 삭제하기
	public void delete(EmpVo vo) {
		Connection conn = dbconn.conn();
		String sql = "delete from emp where emp_id=?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setInt(1, vo.getEmpId());
			int num = pstmt.executeUpdate();
			System.out.println(num + "줄이 삭제되었습니다.");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	// empId로 검색
	public EmpVo search(int empId) {
		EmpVo vo = null; 
		// 검색 결과 담을 변수
		Connection conn = dbconn.conn();
		String sql = "select * from emp where emp_id=?";

		try {
			PreparedStatement pstmt = conn.prepareCall(sql);
			pstmt.setInt(1, empId);
			ResultSet rs = pstmt.executeQuery();
			// executeQuery() : select문 실행 메서드, 검색 결과를 ResultSet에 담아서 반환
			if (rs.next()) {
				// rs.next()는 true와 false를 반환한다.
				// 이동한 줄의 각 컬럼 값을 변수에 옮김
				int empId1 = rs.getInt(1);
				String name = rs.getString(2);
				int salary = rs.getInt(3);
				Date hiredate = rs.getDate(4);
				int depId = rs.getInt(5);
				vo = new EmpVo(empId1, name, salary, hiredate, depId);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return vo;
	}

	// 전체 출력
	public ArrayList<EmpVo> selectAll() {
		ArrayList<EmpVo> list = new ArrayList<>();

		Connection conn = dbconn.conn();
		String sql = "select * from emp";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new EmpVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getInt(5)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	public ArrayList<EmpVo> selectDeptId(int deptId) {
		ArrayList<EmpVo> list = new ArrayList<>();
		Connection conn = dbconn.conn();

		String sql = "select * from emp where dept_id=?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setInt(1, deptId);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				list.add(new EmpVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getInt(5)));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
}

EmpVo.java

package emp;

import java.sql.Date;
// util 말고 sql을 import 해야 한다.


public class EmpVo {
	private int empId;
	private String name;
	private int salary;
	private Date hireDate;
	private int deptId;
	
	public EmpVo() {	
	}

	public EmpVo(int empId, String name, int salary, Date hireDate, int deptId) {
		super();
		this.empId = empId;
		this.name = name;
		this.salary = salary;
		this.hireDate = hireDate;
		this.deptId = deptId;
	}

	public int getEmpId() {
		return empId;
	}

	public void setEmpId(int empId) {
		this.empId = empId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getSalary() {
		return salary;
	}

	public void setSalary(int salary) {
		this.salary = salary;
	}

	public Date getHireDate() {
		return hireDate;
	}

	public void setHireDate(Date hireDate) {
		this.hireDate = hireDate;
	}

	public int getDeptId() {
		return deptId;
	}

	public void setDeptId(int deptId) {
		this.deptId = deptId;
	}

	@Override
	public String toString() {
		return "EmpVo [empId=" + empId + ", name=" + name + ", salary=" + salary + ", hireDate=" + hireDate
				+ ", deptId=" + deptId + "]";
	}

}

Main.java

package emp;

import java.util.ArrayList;

public class Main {

	public static void main(String[] args) {
		EmpDao dao = new EmpDao();
		// 객체 추가
		//dao.insert(new EmpVo(3, "whan2g", 3000, null, 10));
		// dao.update(new EmpVo(1,"",4000, null, 80));
		//dao.delete(new EmpVo(1, "", 0, null, 0));

		
		// id로 검색
//		EmpVo vo=dao.search(6);
//		if(vo==null) {
//			System.out.println("없는 사번");
//		} else {
//			System.out.println(vo);
//		}

		
		System.out.println("전체검색");
		ArrayList<EmpVo> listAll=dao.selectAll();
		for(EmpVo vo:listAll) {
			System.out.println(vo);
		}
		
		System.out.println("부서 10번 검색");
		ArrayList<EmpVo> listDep1=dao.selectDeptId(10);
		if(listDep1.isEmpty()) {
			System.out.println("검색결과 없음");
		} else {
			System.out.println(listDep1);
		}
		
		System.out.println("부서 100번 검색");
		ArrayList<EmpVo> listDep2=dao.selectDeptId(100);
		if(listDep2.isEmpty()) {
			System.out.println("검색결과 없음");
		} else {
			System.out.println(listDep2);
		}
	}
}

에러

java.sql.SQLException: 부적합한 열 인덱스

public ArrayList<BoardVo> selectTitle(String title){
		ArrayList<BoardVo> list=new ArrayList<>();
		Connection conn=dbconn.conn();
		
		String sql="select * from board where title like '%?%'";
		try {
			PreparedStatement pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, title);
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()) {
				list.add(new BoardVo(rs.getInt(1),rs.getString(2),rs.getDate(3),rs.getString(4),rs.getString(5)));
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	
String sql="select * from board where title like '%?%'";
try {
	PreparedStatement pstmt=conn.prepareStatement(sql);
	pstmt.setString(1, title);
	ResultSet rs=pstmt.executeQuery();
	while(rs.next()) {
		list.add(new BoardVo(rs.getInt(1),rs.getString(2),rs.getDate(3),rs.getString(4),rs.getString(5)));
	}

위 부분을

String sql="select * from board where title like ?";
try {
	PreparedStatement pstmt=conn.prepareStatement(sql);
	pstmt.setString(1, "%"+title+"%");
	ResultSet rs=pstmt.executeQuery();
	while(rs.next()) {
		list.add(new BoardVo(rs.getInt(1),rs.getString(2),rs.getDate(3),rs.getString(4),rs.getString(5)));
	}

이렇게 바꾸면 된다

sql 변수에는 ? 하나만 넣어주고
pstmt.setString(1, "%"+title+"%")
에 %을 넣어줘야 에러가 나지 않는다.