학원 day29. 데이터 변경, 삭제, JDBC

★ 데이터 조회 시 실행 순서

select 컬럼명, 컬럼명, ...    --- 3

from 테이블명                    --- 1

[where 조건식]                   --- 2 (컬럼의 별칭을 사용할 수 없음)

[order by 정렬기준]            --- 4 (컬럼의 별칭을 사용할 수 있음)

 

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, SALARY*12 AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE ANNUAL_SALARY >= 150000;   // 오류 발생

 

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, SALARY*12 AS ANNUAL_SALARY

FROM EMPLOYEES

WHERE DEPARTMENT_ID = 80

ORDER BY ANNUAL_SALARY ASC;  // 오류 아님

 

데이터 변경

- 테이블에 저장된 데이터를 변경한다.

- UPDATE 명령어 사용

-- WHERE 조건식이 없으면 모든 행에서 해당 컬럼의 값을 변경한다.

-- WHERE 조건식이 있으면 조건식을 만족하는 행에서만 해당 컬럼의 값을 변경한다.

 

UPDATE 테이블명

SET

      컬럼명 = 값,

      컬럼명 = 값, 

      컬럼명 = 값,

       ...

[WHERE 조건식]

 

 

데이터  삭제

- 테이블에 저장된 데이터를 삭제한다.

- DELETE FROM 명령어 사용

- WHERE 조건식이 없으면 테이블의 모든 행이 삭제된다.
- WHERE 조건식이 있으면 조건식을 만족하는 행만 삭제한다.

 

DELETE FROM SAMPLE_USERS;

- 테이블에서 모든 행의 데이터를 삭제한다.

- 데이터가 저장되었던 영역은 남아있다.

 

TRUNCATE TABLE SAMPLE_USERS;

- 테이블에서 모든 행을 잘라낸다.

- 데이터가 저장되었던 영역도 사라진다.

- 테이블을 처음 생성했던 상태로 초기화한다.

 

* 단, 삭제나 변경할 때 자식 레코드가 참조하고 있는 경우는 삭제, 변경 불가! (프라이머키 변경 불가)

  자식레코드가 없는 레코드는 삭제 가능

 

 

SQL 오류

-- SQL 오류 : ORA-00942 : table or view does not exist
-- 테이블이나 뷰의 이름이 올바르지 않을 때 발생한다.
SELECT *
FROM DEPARTMENT;       -- DEPARTMENTS가 올바른 테이블 이름이다.

-- ORA-00923 : FROM keyword not found where expected
-- FROM 키워드가 없습니다.
SELECT *
FORM EMPLOYEES;   -- 철자 오류

SELECT EMPLOYEE_ID, FIRST_NAME NAME LAST_NAME SALARY  -- SELECT 절에서 컬럼명을 적을 때 ,가 누락되었을 때
FROM EMPLOYEES;

-- SQL 오류 : ORA-00926 : missing VALUES keyword
-- insert 문에 values 키워드가 누락되었다.
INSERT INTO SAMPLE_USERS
(USER_ID, USER_PASSWORD, USER_EMAIL, USER_NAME, USER_TEL)
VALUE  -- VALUES라고 적어야 한다..
('sejong', 'zxcv1324', 'sejong@naver.com', '세종대왕', '010-1234-5678');

-- SQL 오류 : ORA-00913 : too many values
-- INSERT 문에서 컬럼의 갯수가 값의 갯수가 많다.
INSERT INTO SAMPLE_USERS
(USER_ID, USER_PASSWORD, USER_EMAIL, USER_NAME, USER_TEL)
VALUES
('sejong', 'zxcv1234', 'sejong@naver.com', '세종대왕', '010-1234-5678', 100);

-- SQL 오류: ORA-00947: not enough values
-- INSERT 문에서 컬럼의 갯수보다 값의 갯수가 적다.
INSERT INTO SAMPLE_USERS
(USER_ID, USER_PASSWORD, USER_EMAIL, USER_NAME, USER_TEL)
VALUES
('sejong', 'zxcv1234', 'sejong@naver.com', '세종대왕');

-- ORA-02292: integrity constraint (HR.JHIST_DEPT_FK) violated - child record found
-- 이 행을 참조하는 자식 레코드가 있기 때문에 이 행을 삭제할 수 없다.
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 60;   -- EMPLOYEES테이블에서 DEPARTMENT_ID가 60번이 행이 존재하기 때문에 60번 부서는 삭제할 수 없다.

UPDATE DEPARTMENTS
SET
    DEPARTMENT_NAME = 300
WHERE
    DEPARTMENT_ID = 60;     -- EMPLOYEES테이블에서 DEPARTMENT_ID가 60번이 행이 존재하기 때문에 60번 부서의 아이디를 변경할 수 없다.

 

---

JDBC

- Java DataBase Connectivity

- 자바가 데이터베이스에 접속해서 SQL을 실행할 수 있도록 하는 표준이다.

- 자바 애플리케이션에서 데이터베이스 엑세스에 필요한 객체들의 표준을 정의하고 있다.

- java.sql 패키지와 javax.sql 패키지에 관련 인터페이스와 클래스가 존재한다.

- 각 데이터베이스 제조사가 java.sql과 javax.sql에 정의된 인터페이스를 구현해서 자사의 데이터베이스에 엑세스할 수 있는 구현클래스를 제공하고 있다. (jdbc드라이버)

주요 API 

• Connection 인터페이스
  • 자바프로그램과 데이터베이스간의 연결을 담당하는 객체다. SQL의 전송을 담당하는 메소드가 있다.
  • Statement createStatement()
    • SQL전송을 담당하는 Statement객체를 반환한다.
  • PreparedStatement prepareStatement(String sql)
    • SQL전송을 담당하는 Statement객체를 반환한다.
  • void commit()
    • SQL실행결과를 DB에 반영시킨다.
  • void rollback()
    • SQL실행결과의 DB 반영을 취소시킨다.
  • void close()
    • DB와의 접속을 해제한다.
• PreparedStatement 인터페이스
  • SQL의 전송을 담당하는 객체다.
  • int executeUpdate()
    • INSERT, UPDATE, DELETE 쿼리를 DB로 전송하고, 실행결과를 반환한다.
  • ResultSet executeQuery()
    • SELECT 쿼리를 DB로 전송하고, 조회결과를 반환한다.
  • void setXXX(int index, XXX value)
    • ?위치에 실제값(파라미터값)을 설정한다.
    • void setString(int index, String value)
    • void setInt(int index, int value)
    • void setLong(int index, long value)
    • void setDouble(int index, double value)
    • void setDate(int index, Date value)
  • void close()
    • DB 연결 자원을 해제한다.
• ResultSet 인터페이스
  • SELECT 쿼리문의 조회결과를 담당하는 객체다.
  • boolean next()
    • 커서를 다음행으로 이동시킨다. 데이터행이 존재하면 true를 반환한다.
  • XXX getXXX(String columnName)
    • 컬럼명에 해당하는 값을 반환한다.
    • String getString(String columnName);
    • int getInt(String columnName);
    • long getLong(String columnName);
    • double getDouble(String columnName);
    • Date getDate(String columnName);
  • void close()
    • DB 연결 자원을 해제한다.
• DriverManager 클래스
  • JDCB 표준을 구현한 JDBC드라이버를 관리하는 클래스다.
  • JDBC드라이버는 각 DBMS제작사가 자사의 DBMS에 엑세스할 수 있도록 위에 언급한 인터페이스를 구현한 클래스들의 모음이다.
  • Connection getConnection(String url, String username, String password)
    • url이 가르키는 데이터베이스에 지정된 계정정보로 연결을 시도한 후 연결을 담당하는 Connection객체를 반환한다.

 

+) 외부 라이브러리 자바에 넣는 방법

jdbc폴더 - 오른쪽버튼 buildpath - Configure buildpath - 라이브러리 classpath - Add External Jars 클릭 - c 밑에 app 밑에 자기이름폴더 밑에 product밑에 21c밑에 dbhomexe밑에 jdbc밑에 lib밑에 ojdbc11.jar선택하고 열기 - apply

 

JDBC API를 사용해서 자바와 데이터베이스 연동하기

INSERT, UPDATE, DELETE 작업
- 테이블의 데이터를 변경(추가, 변경, 삭제)하는 작업
- 결과값은 항상 변경된 행의 갯수다.

0. SQL 작성하기
String sql = "insert into sample_products" + "(product_no, product_name, product_price, product_discount_rate, product_stock) " + "values " + "(?, ?, ?, ?, ?)";

1. Oracle JDBC 드라이버를 메모리에 로딩시킨다.
Class.forName("oracle.jdbc.OracleDriver");

2. DBMS와 연결을 담당하는 Connection 구현객체를 획득한다.
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "hr";
String password = "zxcv1234";
Connection connection = DriverManager.getConnection(url, user, password);

3. SQL을 데이터베이스로 전송/실행시키는 PreparedStatement 구현객체를 획득한다.
PreparedStatement pstmt = connection.prepareStatement(sql);

4. SQL에 ?가 있으면 값을 바인딩한다.
pstmt.setInt(1, 100);
pstmt.setString(2, "iphone 14 pro max");
pstmt.setInt(3, 1700000);
pstmt.setDouble(4, 0.05);
pstmt.setInt(5, 10);

5. SQL을 데이터베이스로 전송하고 실행시킨다.
int rowCount = pstmt.executeUpdate( );
System.out.println(rowCount + "개의 행이 추가/변경/삭제 되었습니다.");

6. 리소스를 해제한다.
pstmt.close( );
connection.close( );

* 1, 2, 3, 5, 6 수행문은 언제나 같은 코드다.
* 0, 4 수행문은 실행하는 작업마다 다른 코드다.

package day29;

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

public class DataBaseApp1 {

	public static void main(String[] args) throws ClassNotFoundException, SQLException{
		
		// 값이 들어갈 자리가 ?로 표시된 sql을 정의한다.
		String sql = "insert into sample_users " // 끝에 공백을 하나 포함시켜야 뒤에 문장이랑 붙어서 실행되지 않는다.
					+ "(user_id, user_password, user_email, user_name, user_tel, user_point) "
					+ "values "
					+ "(?, ?, ?, ?, ?, ?)";
		
		// 1. oracle.jdbc.OracleDriver를 자바가상머신의 드라이버 레지스트리에 "jdbc:oracle:thin"이라는 이름으로 등록시키기
		// Class.forName(클래스의 전체 경로)는 지정된 클래스를 메모리의 설계도 영역에 로딩시킨다.
		// OracleDriver는 메모리에 로딩이 완료되면 자바가상머신의 드라이버 레지스트리에 오라클 드라이버를 등록시킨다.
		Class.forName("oracle.jdbc.OracleDriver");
		
		// 2. 데이터베이스와 연결을 담당하는 Connection인터페이스의 구현객체를 획득하기
		// url의 "jdbc:oracle:thin"은 드라이버 레지스트리에 등록된 이름이다.
		// url의 "@localhost:1521:xe"에서 @localhost는 데이터베이스 설치된 컴퓨터의 이름이다.
		//								1521은 데이터베이스가 사용하는 포트번호다.
		//                              xe는 데이터베이스를 식별하는 고유한 이름이다.
		// username과 password는 데이터베이스 계정명과 비밀번호다.
		// DriverManager는 자바가상머신의 드라이버 레지스트리를 관리하는 클래스다.
		// DriverManager의 getConnection(url, username, password)는 지정된 url의 데이터베이스와 연결을 유지하는
		// java.sql.Connection인터페이스의 구현객체를 반환한다. (구현객체는 ojdbc11.jar에서 제공한다.)
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String username = "hr";
		String password = "zxcv1234";
		Connection connection = DriverManager.getConnection(url, username, password);
        System.out.println(connection);   // oracle.jdbc.driver.T4CConncection@226a82c4 커넥션 구현 객체가 출력됨 
        
        // 3. SQL을 데이터베이스로 전송/실행시키는 Preparedstatement인터페이스 구현객체 획득하기
        // pstmt는 oracle.jdbc.driver.OraclePreparedStatementWrapper객체의 PreparedStatement를 참조한다.
        PreparedStatement pstmt = connection.prepareStatement(sql);
        System.out.println(pstmt); 
        
        // 4. SQL의 ?자리에 실제 값을 바인딩하기
        pstmt.setString(1, "hong-gd");				// user_id (VARCHAR2) 컬럼의 값
        pstmt.setString(2, "hong_gd@naver.com");	// user_email (VARCHAR2) 컬럼의 값
        pstmt.setString(3,"zxcv1234");				// user_password (VARCHAR2) 컬럼의 값
        pstmt.setString(4, "홍길동");					// user_name (VARCHAR2) 컬럼의 값
        pstmt.setString(5, "010-1111-1111");		// user_tel (VARCHAR2) 컬럼의 값
        pstmt.setInt(6, 100);						// user_point (Number) 컬럼의 값
        
        // 5. SQL을 데이터베이스로 전송하고, 실행시킨다.
        int rowCount = pstmt.executeUpdate();
        System.out.println(rowCount + "개의 행이 추가되었습니다.");
        
        // 6. 데이터베이스 액세스 작업에 사용했던 모든 리소스 연결해제하기
        pstmt.close();
        connection.close();
        
	}

}

* ?를 바인딩 변수라고 한다. 값이 매번 바뀔 거니까 변수처럼 쓰인다. 

바인딩변수의 이점은 '홑따옴표'를 안적어도 된다.

* 데이터베이스마다 url이 다르다.

* jdbc:oracle:thin:@localhost:1521:xe

-> thin타입의 오라클 jdbc드라이버를 사용해서 내 컴퓨터의 1521번 포트를 사용하고 있는 xe라는 데이터베이스에 연결하라는 의미임.

* 데이터베이스의 시작은 1이다.

커밋을 하기 전까지는 테이터베이스에 올라가지 않고 update작업이 끝날때까지 행 잠금 상태로 있기 때문에 다음 작업을 하기위해서는 커밋을 해야 한다. 

update, delete문을 실행하면, 해당 행을 다른 프로세스가 수정할 수 없도록 행잠금이 발생한다. 

commit, rollback명령을 실행하면 행잠금이 해제된다. 

다른 행들은 영향을 받지 않는다.

 

package day29;

import java.sql.Connection;  
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.MessageFormat;
import java.util.Date;

public class DataBaseApp4 {

	public static void main(String[] args) throws Exception {
		
		String sql = "select * "
				   + "from sample_users "
				   + "order by user_id asc ";
		
		Class.forName("oracle.jdbc.OracleDriver");
		
		Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
		PreparedStatement pstmt = connection.prepareStatement(sql); // sql을 전송하는 객체
		ResultSet rs = pstmt.executeQuery();   // 조회결과를 담고 있는 객체 Resultset은 데이터가 하나도 없어도 null은 아님. 
	
		while (rs.next()) {
			String id = rs.getString("user_id");
			String password = rs.getString("user_password");
			String email = rs.getString("user_email");
			String name = rs.getString("user_name");
			String tel = rs.getString("user_tel");
			int point = rs.getInt("user_point");
			String disabled = rs.getString("user_disabled");
			Date createdDate = rs.getDate("user_created_date");
			Date updatedDate = rs.getDate("user_created_date");
			
			String text = MessageFormat.format("{0} {1} {2} {3} {4} {5} {6} {7} {8}",
					id, password, email, name, tel, point, disabled, createdDate, updatedDate);
			System.out.println(text);
		}
		
		rs.close();
		pstmt.close();
		connection.close();
	}

}

package day29;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.MessageFormat;

public class DatabaseApp5 {

	public static void main(String[] args) throws Exception {
		
		String sql = "select employee_id, first_name, job_id, salary, salary*12 as annual "
				   + "from employees "
				   + "where department_id = ? "
				   + "order by employee_id asc ";
		
		Class.forName("oracle.jdbc.OracleDriver");
		
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String user = "hr";
		String password = "zxcv1234";
		Connection connection = DriverManager.getConnection(url, user, password);
		
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, 60);
		
		ResultSet rs = pstmt.executeQuery();  // 실행결과를 ResultSet에 담는다.
		
		while (rs.next()) {
			int id = rs.getInt("employee_id");
			String name = rs.getString("first_name");
			String job = rs.getString("job_id");
			double salary = rs.getDouble("salary");
			double annualSalary = rs.getDouble("annual"); // 별칭 
			
			String text = MessageFormat.format("{0} {1} {2} {3} {4}", id, name, job, salary, annualSalary);
			System.out.println(text);
		}
	}
}

 

실행결과 :