#36 [ORACLE] JDBC, Statement, PreparedStatement, CallableStatement, ExecuteQuery, ExecuteUpdate

    ๐Ÿ“Œ JDBC (Java DataBase Connectivity)

    : Java์™€ Oracle์„ ์—ฐ๋™ํ•˜๊ธฐ ์œ„ํ•ด JDBC๊ฐ€ ํ•„์š”ํ•จ

    Java๊ฐ€ Database๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋„๋ก ์—ฐ๊ฒฐํ•ด์ฃผ๋Š” ์‘์šฉํ”„๋กœ๊ทธ๋žจ ์ธํ„ฐํŽ˜์ด์Šค 

    ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๊ด€๋ฆฌ ์‹œ์Šคํ…œ์— ๋„˜๊ฒจ์งˆ SQL ํ˜•ํƒœ์˜ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ ‘๊ทผ ์š”๊ตฌ ๋ฌธ์žฅ์„ ๊ฐ ์‹œ์Šคํ…œ์— ๋งž๋„๋ก ๋ฐ”๊พธ๋Š” ์—ญํ• ์„ ํ•จ

    ๐Ÿ‘‰ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์กฐ์ž‘ํ•˜๊ธฐ์œ„ํ•œ Java์˜ ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ

     

    ๐Ÿ’ก JDBC ์‚ฌ์šฉ๋ฒ•

    1. Class.forName()์„ ์ด์šฉํ•ด ๋“œ๋ผ์ด๋ฒ„ ๋กœ๋“œ

    2. DriverManager.getConnection() ์œผ๋กœ ์—ฐ๊ฒฐ

    3. Connection ์ธ์Šคํ„ด์Šค๋ฅผ ์ด์šฉํ•ด Statement ๊ฐ์ฒด ์ƒ์„ฑ

    4. Statement ๊ฐ์ฒด์˜ ๊ฒฐ๊ณผ๋ฅผ ResultSet์ด๋‚˜ int์— ๋ฐ›๊ธฐ

    // Driver ํด๋ž˜์Šค ๋กœ๋“œ
    Class.forName("oracle.jdbc.driver.OracleDriver"); 
    
    // DB ์—ฐ๊ฒฐ์„ ์œ„ํ•œ Connection ๊ฐ์ฒด ์ƒ์„ฑ
    Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "userid", "password");
    
    // PreparedStatement ๊ฐ์ฒด ์ƒ์„ฑ
    PreparedStatement preparedStatement = connection.prepareStatement
    ("INSERT INTO USER(id,name,password) values (?,?,?)");
    preparedStatement.setString(1,"id");
    preparedStatement.setString(2,"name");
    preparedStatement.setString(3,"password");
    
    // SQL ์‹คํ–‰
    preparedStatement.executeUpdate();
    
    // ๋ฆฌ์†Œ์Šค ์ •๋ฆฌ
    preparedStatement.close();
    connection.close();

    โœ… ResultSet

    : executeQuery()์—์„œ ์‹คํ–‰๋œ select๋ฌธ์˜ ๊ฒฐ๊ณผ๊ฐ’์„ ๊ฐ€์ง€๊ณ  ์žˆ๋Š” ๊ฐ์ฒด

     

    ๐Ÿ“Œ JDBC API ์ธํ„ฐํŽ˜์ด์Šค

    ๐Ÿ’ก Statement

    : ์ผ๋ฐ˜์ ์ธ sql ์ฟผ๋ฆฌ ์‹คํ–‰

    SQL๋ฌธ์„ DB๋กœ ์ „์†กํ• ๋•Œ ์‚ฌ์šฉ

    โœ… ์žฅ์ 

    : ํ•˜๋‚˜์˜ Statement ์ธ์Šคํ„ด์Šค๋กœ ๋‹ค์ˆ˜์˜ SQL๋ช…๋ น์„ ์ „๋‹ฌํ•˜์—ฌ ์‹คํ–‰ ๊ฐ€๋Šฅ

    โœ… ๋‹จ์ 

    : SQL ๋ช…๋ น Java ๋ณ€์ˆ˜๊ฐ’์„ ํฌํ•จํ•˜๊ณ ์ž ํ•  ๊ฒฝ์šฐ ๋ฌธ์ž์—ด ๊ฒฐํ•ฉ ๊ธฐ๋Šฅ์„ ์ด์šฉํ•ด์•ผํ•จ

      SQL injection ๊ณต๊ฒฉ์— ์ทจ์•ฝํ•จ

    String sql = "SELECT name, phone, address FROM mt";
    Statement s = conn.credateStatement();
    ResultSet rs = s.executeQuery(sql);

     

    ๐Ÿ’ก PreparedStatement

    : ๋™์  ๋˜๋Š” ๋งค๊ฐœ ๋ณ€์ˆ˜๊ฐ€ ํ•„์š”ํ•œ sql ์ฟผ๋ฆฌ๋ฅผ ์‹คํ–‰

    IN ๋งค๊ฐœ๋ณ€์ˆ˜๋ฅผ ๊ฐ€์ง€๊ฑฐ๋‚˜ ๊ฐ€์ง€์ง€ ์•Š๋Š” SQL๋ฌธ์„ ์‹คํ–‰ํ•˜๋Š”๋ฐ ์‚ฌ์šฉ

    ? (InParameter) ๊ธฐํ˜ธ๋ฅผ ์‚ฌ์šฉํ•จ

    โœ… ์žฅ์ 

    : Inparameter๋ฅผ ์ด์šฉํ•ด ๊ฐ’์„ ์ „๋‹ฌ๋ฐ›์•„ SQL ๋ช…๋ น์„ ์ž‘์„ฑํ•  ์ˆ˜ ์žˆ์Œ

    ๊ฐ€๋…์„ฑ๊ณผ ์œ ์ง€๋ณด์ˆ˜์˜ ํšจ์œจ์„ฑ ์ฆ๊ฐ€ (Inparameter์˜ ์‚ฌ์šฉ)

    SQL injection ๊ณต๊ฒฉ ๊ธฐ์ˆ ์„ ๋ฌดํšจํ™”์ฒ˜๋ฆฌํ•จ ๐Ÿ‘‰ ์‚ฌ์šฉ์ž ์ž…๋ ฅ ๊ฐ’์„ ๋ฌด์กฐ๊ฑด SQL ๋ช…๋ น์˜ ๊ฐ’์œผ๋กœ ์ฒ˜๋ฆฌํ•จ

    โœ… ๋‹จ์ 

    : ํ•˜๋‚˜์˜ SQL ๋ช…๋ น๋งŒ์„ ์ €์žฅํ•˜์—ฌ ์ „๋‹ฌ ๊ฐ€๋Šฅ

    String sql = "UPDATE mt SET name = ?, phone = ?, address = ?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setString(1, "์ด์˜ˆ๋ฆผ");
    ps.setString(2, "010-1234-5678");
    ps.setString(3, "์ธ์ฒœ์‹œ");
    ResultSet rs = ps.executeQuery();

    โœ… setString(int index,String value)

    : ๋ฌผ์Œํ‘œ๋ฅผ ๋Œ€์ฒดํ•  ๊ฐ’์„ ์ง€์ •ํ•ด์ฃผ๋Š” ๋ฉ”์†Œ๋“œ (index๋Š” 1๋ถ€ํ„ฐ ์‹œ์ž‘)

     

    โœ… setNString(int index, String value)

    : ๋ฌผ์Œํ‘œ๋ฅผ ๋Œ€์ฒดํ•  ๊ฐ’์„ ์ง€์ •ํ•ด์ฃผ๋Š” ๋ฉ”์†Œ๋“œ,

    ๊ฐ’์„ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋กœ ์ „์†กํ•  ๋•Œ SQL NCHAR ๋˜๋Š” NVCAR ๋˜๋Š” LONGNVARCHAR ๊ฐ’์œผ๋กœ ๋ณ€ํ™˜

     

    ๐Ÿ’ก CallableStatement

    : ์ €์žฅ๋œ ํ”„๋กœ์‹œ์ €๋ฅผ ์‹คํ–‰

    โœ… ์žฅ์ 

    : ์‹คํ–‰์†๋„๊ฐ€ ๋น ๋ฅด๊ณ  ์ฟผ๋ฆฌ์˜ ์–‘์ด ์ค„์–ด ๋ถ€ํ•˜๊ฐ€ ์ ์Œ

     

    ๐Ÿ“Œ ExecuteQuery

    : ์ˆ˜ํ–‰๊ฒฐ๊ณผ๋กœ ResultSet ๊ฐ์ฒด์˜ ๊ฐ’์„ ๋ฐ˜ํ™˜, Select ๊ตฌ๋ฌธ์„ ์ˆ˜ํ–‰ํ•  ๋•Œ ์‚ฌ์šฉ๋˜๋Š” ํ•จ์ˆ˜

    ๐Ÿ‘‰ ResultSet ๊ฐ์ฒด์— ๊ฒฐ๊ณผ ๊ฐ’์„ ๋ฐ˜ํ™˜

     

    ๐Ÿ’ก ResultSet

    : ์ €์žฅ๋œ ๊ฐ’์„ ํ•œ ํ–‰ ๋‹จ์œ„๋กœ ๋ถˆ๋Ÿฌ์˜ฌ ์ˆ˜ ์žˆ์Œ

      ํ•œ ํ–‰์—์„œ ๊ฐ’์„ ๊ฐ€์ ธ์˜ฌ ๋•Œ๋Š” ํƒ€์ž…์„ ์ง€์ •ํ•ด ๋ถˆ๋Ÿฌ์˜ฌ ์ˆ˜ ์žˆ์Œ

    ๐Ÿ‘‰ next() : ์„ ํƒ๋˜๋Š” ํ–‰์„ ๋ณ€๊ฒฝํ•  ์ˆ˜ ์žˆ์Œ

    ๐Ÿ‘‰ getํƒ€์ž…() : ๋ฐ์ดํ„ฐ๋ฅผ ๋ถˆ๋Ÿฌ์˜ฌ ์ˆ˜ ์žˆ์Œ

    rs = stmt.executeQuery("select * from member"); 
    while(rs.next()) { // next()๊ฐ€ ์—†์„ ๋•Œ ๊นŒ์ง€ ๋ฐ˜๋ณต
        System.out.println(rs.getInt(1) + "\t" + rs.getString(2));
        //์ปฌ๋Ÿผ์˜ 1๋ฒˆ์งธ ๊ฐ’์„ intํ˜•์œผ๋กœ, 2๋ฒˆ์งธ ๊ฐ’์„ Stringํ˜•์œผ๋กœ ๊ฐ€์ ธ์˜ด
    }

     

    ๐Ÿ“Œ ExecuteUpdate

    : ์ˆ˜ํ–‰๊ฒฐ๊ณผ๋กœ Int ํƒ€์ž…์˜ ๊ฐ’์„ ๋ฐ˜ํ™˜, SELECT ๊ตฌ๋ฌธ์„ ์ œ์™ธํ•œ ๋‹ค๋ฅธ ๊ตฌ๋ฌธ์„ ์ˆ˜ํ–‰ํ•  ๋•Œ ์‚ฌ์šฉ๋˜๋Š” ํ•จ์ˆ˜

    ๐Ÿ‘‰ INSERT / DELETE / UPDATE ์‹œ ๋ฐ˜์˜๋œ ๋ ˆ์ฝ”๋“œ์˜ ๊ฑด์ˆ˜๋ฅผ ๋ฐ˜ํ™˜ํ•จ

    ๐Ÿ‘‰ CREATE / DROP ์‹œ -1 ๋ฐ˜ํ™˜

    ๋ฐ˜์‘ํ˜•

    ๋Œ“๊ธ€