JDBC

JAVA ํ”„๋กœ๊ทธ๋žจ์—์„œ SQL๋ฌธ์„ ์‹คํ–‰ํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฅผ ๊ด€๋ฆฌํ•˜๊ธฐ ์œ„ํ•œ JAVA API์ด๋‹ค.

JDBC์˜ ํŠน์ง•์€ ๋‹ค์–‘ํ•œ ๋ฐ์ดํ„ฐ ๋ฒ ์ด์Šค์— ๋Œ€ํ•ด์„œ ๋ณ„๋„์˜ ํ”„๋กœ๊ทธ๋žจ์„ ๋งŒ๋“ค ํ•„์š” ์—†์ด, ํ•ด๋‹น ๋ฐ์ดํ„ฐ ๋ฒ ์ด์Šค์˜ JDBC๋ฅผ ์ด์šฉํ•˜๋ฉด ํ•˜๋‚˜์˜ ํ”„๋กœ๊ทธ๋žจ์œผ๋กœ ๋ฐ์ดํ„ฐ ๋ฒ ์ด์Šค๋ฅผ ๊ด€๋ฆฌํ•  ์ˆ˜ ์žˆ๋‹ค.

MySQL ์—ฐ๋™ํ•˜๊ธฐ

  1. https://dev.mysql.com/downloads/connector/j/5.1.html ์—์„œ Platform Independent (Architecture Independent), Compressed TAR Archive ๋ฅผ ๋‹ค์šด๋ฐ›๋Š”๋‹ค.

  2. ์••์ถ•์„ ํ‘ผ ํ›„ mysql-connector-java-5.1.46-bin.jar ํŒŒ์ผ์„ ๋ณต์‚ฌํ•œ๋‹ค.

  3. /Library/Java/JavaVirtualMachines/jdk1.8.0_121.jdk/Contents/Home/jre/lib/ext ๋กœ ์ด๋™ํ•œ ํ›„ ๋ณต์‚ฌํ•œ ํŒŒ์ผ์„ ๋ถ™์—ฌ๋„ฃ๋Š”๋‹ค.

  4. Eclipse ํ™˜๊ฒฝ์„ค์ • โ†’ Installed JREsโ†’ ์„ค์น˜ ๋ฒ„์ „ ์„ ํƒ โ†’ Edit โ†’ Add External JARs ๋กœ ์œ„์˜ ๊ฒฝ๋กœ์— ์ถ”๊ฐ€ํ•œ ํŒŒ์ผ์„ ์ถ”๊ฐ€ํ•ด์ค€๋‹ค.

  5. DataSource Explorer โ†’ Database Connection์„ ์šฐํด๋ฆญ โ†’ New โ†’ MySQL ์ถ”๊ฐ€

ํ•ด์ฃผ๋ฉด ์„ฑ๊ณต์ ์œผ๋กœ ์—ฐ๋™ ๋œ ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค.

JDBC ์‚ดํŽด๋ณด๊ธฐ

๋ฐ์ดํ„ฐ ๋ฒ ์ด์Šค ์—ฐ๊ฒฐ ์ˆœ์„œ

  • JDBC ๋“œ๋ผ์ด๋ฒ„ ๋กœ๋“œ(DriverManager) : ๋ฉ”๋ชจ๋ฆฌ์— MySQL Driver๊ฐ€ ๋กœ๋“œ๋œ๋‹ค.

Class.forName("com.mysql.jdbc.Driver");
  • ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์—ฐ๊ฒฐ(Connection) : Connection ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•œ๋‹ค.

Connection connection=DriverManager.getConnection(JDBC URL,๊ณ„์ •์•„์ด๋””,๋น„๋ฐ€๋ฒˆํ˜ธ);
  • SQL๋ฌธ ์‹คํ–‰(Statement) : State ๊ฐ์ฒด๋ฅผ ํ†ตํ•ด์„œ SQL๋ฌธ์ด ์‹คํ–‰๋œ๋‹ค.

Statement statement = connection.createStatement();
  • ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์—ฐ๊ฒฐ ํ•ด์ œ(ResultSet) : SQL๋ฌธ ๊ฒฐ๊ณผ ๊ฐ’์„ ResultSet๊ฐ’์œผ๋กœ ๋ฐ›๋Š”๋‹ค.

ResultSet rs = statement.executeQuery();
ResultSet rs = statement.executeUpdate();

Statement ๊ฐ์ฒด ์‚ดํŽด๋ณด๊ธฐ

executeQuery()

SQL๋ฌธ ์‹คํ–‰ ํ›„ ์—ฌ๋Ÿฌ ๊ฐœ์˜ ๊ฒฐ๊ณผ ๊ฐ’์ด ์ƒ๊ธฐ๋Š” ๊ฒฝ์šฐ์— ์‚ฌ์šฉํ•œ๋‹ค. (ex) select

ResultSet

executeQuery() ์‹คํ–‰ ํ›„ ๋ฐ˜ํ™˜๋˜๋Š” ๋ ˆ์ฝ”๋“œ ์…‹์ด๋‹ค.

๋ฉ”์†Œ๋“œ

์„ค๋ช…

next()

๋‹ค์Œ ๋ ˆ์ฝ”๋“œ ์ด๋™

previous()

์ด์ „ ๋ ˆ์ฝ”๋“œ๋กœ ์ด๋™

first()

์ฒ˜์Œ์œผ๋กœ ์ด๋™

last()

๋งˆ์ง€๋ง‰์œผ๋กœ ์ด๋™

get๋ฉ”์†Œ๋“œ (getString, getInt)

ํ•ด๋‹น๋˜๋Š” ๊ฐ’์„ ๊ฐ€์ ธ์˜จ๋‹ค.

executeUpdate()

SQL๋ฌธ ์‹คํ–‰ ํ›„ ํ…Œ์ด๋ธ”์˜ ๋‚ด์šฉ๋งŒ ๋ณ€๊ฒฝ๋˜๋Š” ๊ฒฝ์šฐ ์‚ฌ์šฉํ•œ๋‹ค. (ex) insert, delete, update

์˜ˆ์ œ ์ฝ”๋“œ

<%@page import="javax.servlet.jsp.tagext.TryCatchFinally"%>
<%@ page import="java.sql.*" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%!
    Connection connection;
    Statement statement;
    ResultSet resultSet;

    String driver = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://localhost:3306/javaproject";
    String uid = "root";
    String upw = "๋น„๋ฐ€๋ฒˆํ˜ธ";
    String query = "SELECT * FROM member";
%>    
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
    <%
    try{
        Class.forName(driver);
        connection = DriverManager.getConnection(url,uid,upw);
        statement = connection.createStatement();
        resultSet = statement.executeQuery(query);

        // ๋‹ค์Œ๋ฐ์ดํ„ฐ๊ฐ€ ์žˆ์œผ๋ฉด ๊ฐ’์„ ๋ฐ›์•„์˜จ๋‹ค.
        while(resultSet.next()){
            String id = resultSet.getString("id");
            String pw = resultSet.getString("pw");
            String name = resultSet.getString("name");
            String phone = resultSet.getString("phone");

            out.println("์•„์ด๋”” : "+id +", ๋น„๋ฐ€๋ฒˆํ˜ธ : "+pw+", ์ด๋ฆ„ : "+name+", ์ „ํ™”๋ฒˆํ˜ธ : "+phone+"<br />");
        }
    }catch(Exception e){
    }finally{
        //๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์—ฐ๊ฒฐํ•œ ์ž์›์„ ํ•ด์ œํ•œ๋‹ค.
        try{
            if(resultSet != null) resultSet.close();
            if(statement != null) statement.close();
            if(connection != null) connection.close();
        } catch(Exception e){}
    }
    %>
</body>
</html>

์—ฐ๊ฒฐ์ด ์ œ๋Œ€๋กœ ๋์œผ๋ฉด ์˜ฌ๋ฐ”๋ฅธ ๊ฐ’์ด ๋‚˜์˜ค๋Š” ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค.

Last updated

Was this helpful?