Java8版 簡易DAOサンプル

MemberDAO.java

/*
 * Java8版 簡易DAOサンプル
 */

import java.sql.*;
import java.util.*;

final class Member {
    int id;
    String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return String.format("Member(id=%s, name=%s)", id, name);
    }

}

public final class MemberDAO {

    static final String TABLE_ID = "mydb.table1";
    static final String FIND_ALL_SQL = "select id, name from " + TABLE_ID;
    static final String FIND_BY_ID_SQL = "select id, name from " + TABLE_ID + " where id=?";
    static final String INSERT_SQL = "insert into " + TABLE_ID + " (id, name) values (?, ?)";
    static final String UPDATE_NAME_SQL = "update " + TABLE_ID + " set name=? where id=?";
    static final String DELETE_SQL = "delete from " + TABLE_ID + " where id=?";

    public MemberDAO() {
        try {
            // static initで1回だけで良いけどエラーハンドリングが面倒なのでここで
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    public List<Member> findAll() throws SQLException {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(FIND_ALL_SQL)) {
            List<Member> a = new ArrayList<>();
            while (rs.next()) {
                Member member = new Member();
                int index = 0;
                member.setId(rs.getInt(++index));
                member.setName(rs.getString(++index));
                a.add(member);
            }
            return a;
        }
    }

    public Optional<Member> findById(int id) throws SQLException {
        try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(FIND_BY_ID_SQL)) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Member member = new Member();
                    int index = 0;
                    member.setId(rs.getInt(++index));
                    member.setName(rs.getString(++index));
                    return Optional.of(member);
                }
                return Optional.empty();
            }
        }
    }

    public boolean create(Member member) throws SQLException {
        try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(INSERT_SQL)) {
            int index = 0;
            stmt.setInt(++index, member.getId());
            stmt.setString(++index, member.getName());
            int updatedCount = stmt.executeUpdate();
            return updatedCount != 1;
        }
    }

    public boolean update(Member member) throws SQLException {
        try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(UPDATE_NAME_SQL)) {
            int index = 0;
            stmt.setString(++index, member.getName());
            stmt.setInt(++index, member.getId());
            int updatedCount = stmt.executeUpdate();
            return updatedCount != 1;
        }
    }

    public boolean delete(Member member) throws SQLException {
        try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(DELETE_SQL)) {
            int index = 0;
            stmt.setInt(++index, member.getId());
            int updatedCount = stmt.executeUpdate();
            return updatedCount != 1;
        }
    }

    private static Connection getConnection() throws SQLException {
        // 毎回接続切断する簡易仕様

        String url = "jdbc:mysql://hostname/yourdb?useUnicode=true&characterEncoding=utf8";
        String user = "user";
        String passwd = "passwd";
        return DriverManager.getConnection(url, user, passwd);
    }

}