[공통] JDBC - DatabaseUtils 관련

DB연동

• DatabaseUtils

package com.rootgo.study_jdbc;

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

public class DatabaseUtils {
    public static Connection getConnection() throws
            ClassNotFoundException,
            SQLException {
        Class.forName("org.mariadb.jdbc.Driver");
        Connection connection = DriverManager.getConnection(
                "jdbc:mariadb://localhost:3306",
                "study",
                "test1234");
        return connection;
        // 공통되는 부분을 빼냄
    }
}

• INSERT

// 이름과 나이 삽입하기

package com.rootgo.study_jdbc;

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

public class Insert {
    public static void main(String[] args) throws
            ClassNotFoundException,
            SQLException {
        Class.forName("org.mariadb.jdbc.Driver");
        // forName 비정적임. -> Class가 타입이다.(대문자 이기 때문)
        // mariadb 접속 할 때 : org.mariadb.jdbc.Driver
        // mysql 접속 할 때 :
        Connection connection = DriverManager.getConnection(
                // new 키워드를 사용하지 않는 이유? Connection : 인터페이스이기 때문
                "jdbc:mariadb://localhost:3306",
                // url
                "study",
                // name
                "test1234");
                // password
        String name = "김병규";
        int age = 101;
        PreparedStatement preparedStatement = connection.prepareStatement(
                // PreparedStatement(인터페이스) : 쿼리문 작성하기에 준비된 상태
                //
                "INSERT INTO `study`.`jdbc` (`name`,`age`) VALUES (?, ?)");
        preparedStatement.setString(1, name);
        //String 형식으로 1번째에 name을 집어넣는다.
        preparedStatement.setInt(2, age);
        //Int 형식으로 age를 집어넣는다.

        // 문자열 합치기를 해서는 안됨
        int i = preparedStatement.executeUpdate();
        System.out.println( i );

        System.out.println(connection.isClosed());
        // false 출력
    }
}

package com.rootgo.study_jdbc;

import java.sql.*;

public class Insert {
    public static void main(String[] args) throws
            ClassNotFoundException,
            SQLException {
        try (Connection connection = DatabaseUtils.getConnection()) {
            String[] names = {"김병규", "김효만", "김희주", "김경문", "김호산"};
            int[] ages = {55, 63, 51, 59, 70};
            try (PreparedStatement preparedStatement = connection.prepareStatement(
                    // PreparedStatement(인터페이스) : 쿼리문 작성하기에 준비된 상태
                    "INSERT INTO `study`.`jdbc` (`name`,`age`) " +
                            "VALUES (?, ?)")) {
                    for (int i = 0; i < names.length; i++) {
                        preparedStatement.setString(1, names[i]);
                        preparedStatement.setInt(2, ages[i]);
                        int a = preparedStatement.executeUpdate();
                        System.out.println(a);
                    }
                }
                catch (SQLException ex) {
                    System.out.println("안에서 터짐 ");
                }
            } catch (SQLException ex) {
                System.out.println("밖에서 터짐 ");
            }
        }
    }

• UPDATE

package com.rootgo.study_jdbc;

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

public class Update {
    public static void main(String[] args) throws
            ClassNotFoundException, SQLException {
        try (Connection connection = DatabaseUtils.getConnection()) {
            String oldName = "박말자";
            int newAge = 100;
            try (PreparedStatement preparedStatement = connection.prepareStatement("" +
                    "UPDATE `study`.`jdbc` " +
                    "SET `age` = ? " +
                    "WHERE `name` = ? " +
                    "LIMIT 1")){
        }
    }
    }
}

• DELETE

// "김" 으로 시작하는 문자열 삭제하기

package com.rootgo.study_jdbc;

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

public class Delete {
    public static void main(String[] args) throws
            ClassNotFoundException, SQLException {
        Class.forName("org.mariadb.jdbc.Driver");
        Connection  connection = DriverManager.getConnection(
                "jdbc:mariadb://localhost:3306",
                "study",
                "test1234");
        String familyName = "김";
        PreparedStatement preparedStatement = connection.prepareStatement("" +
                "DELETE " +
                "FROM `study`.`jdbc` " +
                "WHERE name LIKE CONCAT(?, '%')");
        preparedStatement.setString(1, familyName);
        // Cannot resolve query parameter '1'
        int i = preparedStatement.executeUpdate();
        System.out.println( i );
    }
}

• SELECT

package com.rootgo.study_jdbc;

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

public class Select {
    public static void main(String[] args) throws
            ClassNotFoundException,
            SQLException {
        try (Connection connection = DatabaseUtils.getConnection()){
            try (PreparedStatement preparedStatement = connection.prepareStatement("" +
                    "SELECT `name` AS `name`, " +
                    "       `age`  AS `age`   " +
                    "FROM `study`.`jdbc`      " +
                    "ORDER BY `name`          ")) {
                try (ResultSet resultSet = preparedStatement.executeQuery()){
                    while (resultSet.next()) {
                        String name = resultSet.getString("name");
                        int age = resultSet.getInt("age");
                        if (name.startsWith("김")) {
                        // 성이 "김"으로 시작되는 경우
                            System.out.printf("%s, %d\n", name, age);
                        }
                    }
                }
            }
        }
    }
}