JDBC 설명 및 예시 2)

 

※ executeUpdate() 메소드 : DB에 변경을 가하는 조작

※ 레코드 추가
stmt.executeUpdate("insert into Book values( "+17+", \'AAAA\', \'BBBB\', "+1000+")");
-SQL문 수행으로 영향을 받은 행의 개수 반환

※ 데이터 수정 및 삭제는 인자의 String 내용을 포맷에 맞게 변경할 것 
데이터 수정
stmt.executeUpdate("update book set bookname = 'CCCC' where bookid=17")

 

데이터 삭제 
stmt.executeUpdate("delete from book where name='CCC'");

※ executeQuery(String sql) : 주어진 SQL문을 실행하고 결과를 ResultSet 객체에 반환

※ PreparedStatement
- SQL문 실행 시 필요한 인자들을 동적으로 설정
- 실행이 빠르고 가독성이 좋으며 코드 재사용 가능
EX) String sql = "insert into Book values(?, ?, ?)";  →  ? 부분에 값을 넣는 것
     PreparedStatement pstmt = conn.prepareStatement("select * from Book");
     ResultSet rs = pstmt.executeQuery();

 

PreparedStatement문(예시)	Statement문(예시)
PreparedStatement pstmt = conn.prepareStatement("select * from Book"); ResultSet rs = pstmt	Statement stmt = conn.createStatement(); stmt.executeQuery("select * from book");

※  PreparedStatement 주요 메소드
1) void setInt(int index, int value) : 인덱스 위치에 정수값 지정
2) void setString(int index, String value) : 인덱스 위치에 문자열 지정
3) int executeUpdate : SQL문 실행
4) ResultSet executeQuery() : SELECT문을 실행하고 ResultSet 객체 반환

※ ResultSetMetaData 
- 메타데이터 : 데이터에 대한 데이터

※ ResultSetMetaData 메소드
1) int getColumnCount() : ResultSet 객체의 컬럼의 수 반환
2) String getColumnName(int index) : 컬럼 인덱스에 해당하는 이름 반환
3) String getColumnTypeName(int index) : 인덱스에 해당하는 컬럼 타입 반환

※ BLOB(Binary Large Object)
- 이미지와 같은 대용량 데이터를 위한 타입
1) long length : BLOB의 길이 반환
2) byte[] getBytes(long start, long length) : BLOB에서 주어진 범위 내의 데이터를 반환
3) InputStream getBinaryStream() : BLOB에 저장된 데이터를 읽어서 Stream으로 반환
4) OutputStream setBinaryStream(long start) : BLOB에 저장될 OutputStream 반환

※ 트랜잭션 관리 및 메소드
1) void setAutoCommit(boolean status) : 트랜잭션마다 커밋 완료, 기본값 true
2) void commit() : 작업 내용 커밋 실행
3) void rollback() : 작업 내용 철회

 

[SQL MUSIC 데이터]

 

[예제문제1] -PreparedStatement :  2, IU, bbb, 30 값 넣기

package May_Four_05_Review;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
public class PreparedStatementEx_1 {
 
    public static void main(String[] args) {
        
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
            
            //넣고자 하는 값 입력
            String sql = "Insert into music values(?, ?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, 2);
            pstmt.setString(2, "IU");
            pstmt.setString(3,  "bbb");
            pstmt.setInt(4, 30);
            pstmt.executeQuery();
            
            //넣고자 하는 값 값 출력위함
            pstmt = conn.prepareStatement("Select * from music where mnumber = ?");
            pstmt.setInt(1,2);
            
            
            ResultSet rs = pstmt.executeQuery();
            
            while(rs.next()) {
                System.out.printf("%s\t%s\t%s\t%s\t\n", 
                                    rs.getString(1), //mnumber
                                    rs.getString(2), //singer
                                    rs.getString(3), //agency
                                    rs.getString(4)); //sell
            }
                    
        } catch(ClassNotFoundException e) {
            System.out.println("JDBC 드라이버 로드 에러");
        } catch (SQLException e) {
            System.out.println("DB 연결 오류");
        }
        
    }
 
}
 

[예제 2] - ResultSetMetaData

 

결과값 :

컬럼의 개수 : 4
2번째 컬럼 이름 : BOOKNAME
2번째 컬럼 타입 : VARCHAR2

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
 
public class ResultSetMetaDataEx {
 
    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
                    
            String sql = "Select * from book where bookname = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "축구의 역사");
            
            
            ResultSet rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            
            
            while(rs.next()) {
                System.out.println("컬럼의 개수 : " + rsmd.getColumnCount());
                System.out.println("2번째 컬럼 이름 : " + rsmd.getColumnName(2));
                System.out.println("2번째 컬럼 타입 : " + rsmd.getColumnTypeName(2));
                
            }
                    
        } catch(ClassNotFoundException e) {
            System.out.println("JDBC 드라이버 로드 에러");
        } catch (SQLException e) {
            System.out.println("DB 연결 오류");
        }
 
    }
 
 
}