JDBC 程式優化實戰 (六)：DAO 模式與 PreparedStatement 封裝

JDBC 程式優化實戰 (六)：DAO 模式與 PreparedStatement 封裝

DTO (Data Transfer Object) 設計

純資料載體，含屬性、建構子、Getter/Setter，無業務邏輯。

public abstract class IdEntity {
    protected long id;
    public long getId() { return id; }
    public void setId(long id) { this.id = id; }
}

public class User extends IdEntity {
    private String name, password, email;
    // 建構子、Getter/Setter、toString 略
}

public class Address extends IdEntity {
    private String city, country, userid;
    // Getter/Setter、toString 略
}

DAO 介面與實作

使用 PreparedStatement 佔位符防注入、提升效能。

public interface UserDao {
    void save(Connection conn, User user) throws SQLException;
    void update(Connection conn, Long id, User user) throws SQLException;
    void delete(Connection conn, User user) throws SQLException;
}

public class UserDaoImpl implements UserDao {
    public void save(Connection conn, User user) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO tbl_user(name,password,email) VALUES (?,?,?)");
        ps.setString(1, user.getName());
        ps.setString(2, user.getPassword());
        ps.setString(3, user.getEmail());
        ps.execute();
    }

    public void update(Connection conn, Long id, User user) throws SQLException {
        String sql = "UPDATE tbl_user SET name=?,password=?,email=? WHERE id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, user.getName());
        ps.setString(2, user.getPassword());
        ps.setString(3, user.getEmail());
        ps.setLong(4, id);
        ps.execute();
    }

    public void delete(Connection conn, User user) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("DELETE FROM tbl_user WHERE id=?");
        ps.setLong(1, user.getId());
        ps.execute();
    }
}

測試類：事務控制

public class DaoTest {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = ConnectionFactory.getInstance().makeConnection();
            conn.setAutoCommit(false); // 開啟事務
            UserDao userDao = new UserDaoImpl();
            User tom = new User("Tom", "123456", "tom@163.com");
            userDao.save(conn, tom);
            conn.commit(); // 提交
        } catch (Exception e) {
            e.printStackTrace();
            try { conn.rollback(); } catch (Exception e2) { e2.printStackTrace(); }
        }
    }
}

關鍵優化點總結

• PreparedStatement：預編譯、參數化、防 SQL 注入、批次執行支援
• DAO 模式：資料存取邏輯封裝，業務層解耦
• 事務邊界：Service 層控制 commit/rollback，DAO 單純執行 SQL
• 連線管理：ConnectionFactory 單例模式，生產環境建議使用連線池 (HikariCP/Druid)