#멋쟁이사자처럼 #부트캠프 #백엔드 #JAVA

Useful URLs

• Part 1: Initializing the Project
• Maven Repository

Example: InsertTest

package org.example.jdbc;

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

public class InsertTest {
    public static void main(String[] args) {
        // 1. Declaration
        Connection conn = null;
        PreparedStatement ps = null;

        final String URL = "jdbc:mysql://localhost:3306/hr";
        final String USER = "like";
        final String PASSWORD = "lion";
        final String SQL = "INSERT INTO dept(deptno, dname, loc) values (?, ?, ?);";

        int deptno = 90;
        String dname = "";

        // try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
        // } catch (Exception e) {} finally {
        try {
            // Class.forName("com.mysql.cj.jdbc.Driver");

            // 3. Get connection.
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            // conn.setAutoCommit(false);

            // * 4. Write SQL (Using `Statement` object.)
            ps = conn.prepareStatement(SQL);
            ps.setInt(1, deptno);
            ps.setString(2, "교육부");
            ps.setString(3, "일산");

            // 5. Run Query
            int count = ps.executeUpdate();
            // 6. Get Result
            System.out.println(count + " 건 입력 했습니다.");
            // conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 2. Close
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}

Example: UpdateTest

package org.example.jdbc;

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

public class UpdateTest {
    public static void main(String[] args) {
        final String URL = "jdbc:mysql://localhost:3306/hr";
        final String USER = "like";
        final String PASSWORD = "lion";
        final String SQL = "UPDATE dept SET dname=? WHERE deptno = ?;";

        // try-with-resource
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement ps = conn.prepareStatement(SQL);) {
            ps.setString(1, "마케팅부");
            ps.setInt(2, 80);

            int count = ps.executeUpdate();
            System.out.println(count + "건 수정했어요.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Example: DeleteTest

package org.example.jdbc;

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

public class DeleteTest {
    public static void main(String[] args) {
        final String URL = "jdbc:mysql://localhost:3306/hr";
        final String USER = "like";
        final String PASSWORD = "lion";
        final String SQL = "DELETE FROM dept WHERE deptno=?";

        try (
                // 1. 접속
                Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
                // 2. SQL 문 작성
                PreparedStatement ps = conn.prepareStatement(SQL);
        ) {
            ps.setInt(1, 80);

            // 3. 실행
            int count = ps.executeUpdate();

            System.out.println(count + "건 삭제했습니다.");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

Example: SelectTest

package org.example.jdbc;

import org.example.jdbcproject.DBUtil;

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

public class SelectTest {
    public static void main(String[] args) {
        // 1. Declaration
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        final String SQL = "SELECT deptno, dname, loc FROM dept WHERE deptno=?";
        try {
            // 3. Get connection
            conn = DBUtil.getConnection();
            // 4. Write SQL (Using `Statement` object.)
            ps = conn.prepareStatement(SQL);
            ps.setInt(1, 10);
            // 5. Run Query
            rs = ps.executeQuery();
            // 6. Get Result
            if (rs.next()) {
                System.out.println(rs.getInt(1));
                System.out.println(rs.getString("dname"));
                System.out.println(rs.getString(3));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }
}

Data Access Object (DAO)

In software, a data access object (DAO) is a pattern that provides an abstract interface to some type of database or other persistence mechanism. By mapping application calls to the persistence layer, the DAO provides data operations without exposing database details.