您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

Java JDBC 連接 MySQL 實現 CRUD 完整教程：從環境搭建到分層架構最佳實踐

編輯：Java 相關知識

開發環境準備

JDK 8+ / 11+ / 17+ (LTS 推薦)
MySQL 5.7+ / 8.0+
IDE: IntelliJ IDEA / Eclipse / VS Code
建構工具: Maven / Gradle
資料庫客戶端: Navicat / DBeaver / DataGrip / MySQL Workbench

Maven 依賴

<dependencies>
    <!-- MySQL 驅動 (8.0+ 使用 com.mysql.cj.jdbc.Driver) -->
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.2.0</version>
    </dependency>
    
    <!-- 連接池 (生產必備) -->
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>5.0.1</version>
    </dependency>
    
    <!-- 工具庫 -->
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-dbcp2</artifactId>
        <version>2.9.0</version>
    </dependency>
</dependencies>

資料庫表結構

CREATE DATABASE IF NOT EXISTS `jdbc_demo` CHARSET utf8mb4;
USE `jdbc_demo`;

CREATE TABLE `goddess` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL COMMENT '姓名',
    `mobile` VARCHAR(16) COMMENT '手機',
    `email` VARCHAR(64) COMMENT '郵箱',
    `address` VARCHAR(128) COMMENT '地址',
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 初始數據
INSERT INTO `goddess` (`name`, `mobile`, `email`, `address`) VALUES
('林志玲', '13800001111', 'lin@example.com', '台北市信義區'),
('劉亦菲', '13900002222', 'liu@example.com', '北京市朝陽區');

分層架構代碼實現

1. 實體類

@Data @NoArgsConstructor @AllArgsConstructor
public class Goddess {
    private Integer id;
    private String name;
    private String mobile;
    private String email;
    private String address;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

2. 數據源配置類 (單例模式 + HikariCP)

public class DataSourceConfig {
    private static final String URL = "jdbc:mysql://localhost:3306/jdbc_demo?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASSWORD = "root";
    
    private static HikariDataSource dataSource;
    
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(URL);
        config.setUsername(USER);
        config.setPassword(PASSWORD);
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        dataSource = new HikariDataSource(config);
    }
    
    public static DataSource getDataSource() { return dataSource; }
    
    public static void close() { if (dataSource != null) dataSource.close(); }
}

3. DAO 接口與實現

public interface GoddessDao {
    List<Goddess> findAll();
    Goddess findById(Integer id);
    int insert(Goddess goddess);
    int update(Goddess goddess);
    int deleteById(Integer id);
    List<Goddess> findByCondition(GoddessQuery query); // 進階查詢
}

public class GoddessDaoImpl implements GoddessDao {
    private static final String TABLE = "goddess";
    private static final String COLUMNS = "id, name, mobile, email, address, create_time, update_time";
    
    private Connection getConn() throws SQLException {
        return DataSourceConfig.getDataSource().getConnection();
    }
    
    private Goddess mapRow(ResultSet rs) throws SQLException {
        Goddess g = new Goddess();
        g.setId(rs.getInt("id"));
        g.setName(rs.getString("name"));
        g.setMobile(rs.getString("mobile"));
        g.setEmail(rs.getString("email"));
        g.setAddress(rs.getString("address"));
        g.setCreateTime(rs.getTimestamp("create_time").toLocalDateTime());
        g.setUpdateTime(rs.getTimestamp("update_time").toLocalDateTime());
        return g;
    }
    
    @Override
    public List<Goddess> findAll() {
        String sql = "SELECT " + COLUMNS + " FROM " + TABLE;
        return executeQuery(sql);
    }
    
    @Override
    public Goddess findById(Integer id) {
        String sql = "SELECT " + COLUMNS + " FROM " + TABLE + " WHERE id = ?";
        try (Connection conn = getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? mapRow(rs) : null;
            }
        } catch (SQLException e) { throw new DaoException(e); }
    }
    
    @Override
    public int insert(Goddess g) {
        String sql = "INSERT INTO " + TABLE + " (name, mobile, email, address) VALUES (?, ?, ?, ?)";
        return executeUpdate(sql, g.getName(), g.getMobile(), g.getEmail(), g.getAddress());
    }
    
    @Override
    public int update(Goddess g) {
        String sql = "UPDATE " + TABLE + " SET name=?, mobile=?, email=?, address=? WHERE id=?";
        return executeUpdate(sql, g.getName(), g.getMobile(), g.getEmail(), g.getAddress(), g.getId());
    }
    
    @Override
    public int deleteById(Integer id) {
        String sql = "DELETE FROM " + TABLE + " WHERE id = ?";
        return executeUpdate(sql, id);
    }
    
    // 通用查詢模板
    private List<Goddess> executeQuery(String sql, Object... params) {
        try (Connection conn = getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            setParams(ps, params);
            try (ResultSet rs = ps.executeQuery()) {
                List<Goddess> list = new ArrayList<>();
                while (rs.next()) list.add(mapRow(rs));
                return list;
            }
        } catch (SQLException e) { throw new DaoException(e); }
    }
    
    private int executeUpdate(String sql, Object... params) {
        try (Connection conn = getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            setParams(ps, params);
            return ps.executeUpdate();
        } catch (SQLException e) { throw new DaoException(e); }
    }
    
    private void setParams(PreparedStatement ps, Object... params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            ps.setObject(i + 1, params[i]);
        }
    }
}

4. Service 層 (事務控制)

public class GoddessService {
    private final GoddessDao dao = new GoddessDaoImpl();
    
    public List<Goddess> getAll() { return dao.findAll(); }
    
    public Goddess getById(Integer id) { return dao.findById(id); }
    
    @Transactional // 需配合 Spring 或手動事務管理
    public void add(Goddess g) {
        validate(g);
        dao.insert(g);
    }
    
    @Transactional
    public void update(Goddess g) {
        if (g.getId() == null) throw new IllegalArgumentException("ID 不能為空");
        validate(g);
        dao.update(g);
    }
    
    @Transactional
    public void delete(Integer id) { dao.deleteById(id); }
    
    private void validate(Goddess g) {
        if (g.getName() == null || g.getName().trim().isEmpty())
            throw new IllegalArgumentException("姓名不能為空");
        // 更多驗證...
    }
}

5. 測試驗證

public class JdbcCrudTest {
    public static void main(String[] args) {
        GoddessService service = new GoddessService();
        
        // 查詢所有
        System.out.println("=== 初始數據 ===");
        service.getAll().forEach(System.out::println);
        
        // 新增
        Goddess newG = new Goddess();
        newG.setName("迪麗熱巴");
        newG.setMobile("13700003333");
        newG.setEmail("dilraba@example.com");
        newG.setAddress("新疆烏魯木齊");
        service.add(newG);
        System.out.println("\n=== 新增後 ===");
        service.getAll().forEach(System.out::println);
        
        // 修改
        Goddess toUpdate = service.getById(1);
        toUpdate.setAddress("上海市浦東新區");
        service.update(toUpdate);
        
        // 刪除
        service.delete(2);
        System.out.println("\n=== 最終數據 ===");
        service.getAll().forEach(System.out::println);
        
        DataSourceConfig.close(); // 關閉連接池
    }
}

JDBC 核心最佳實踐總結

原則	錯誤示範	正確做法
使用連接池	每次 `DriverManager.getConnection()`	HikariCP / Druid / DBCP2
預編譯語句	`Statement` 拼接 SQL	`PreparedStatement` 防注入、性能優
try-with-resources	手動 `close()` 易漏	JDK 7+ 自動關閉資源
參數綁定	字符串拼接值	`ps.setObject(index, value)`
批量操作	循環執行單條 SQL	`addBatch()` + `executeBatch()`
事務邊界清晰	DAO 層自動提交	Service 層控制事務
異常轉換	拋出 SQLException	包裝為業務異常/運行時異常

進階：動態條件查詢構建器

public class GoddessQuery {
    private String nameLike;
    private String mobile;
    private LocalDateTime createTimeStart, createTimeEnd;
    // getter/setter
}

public List<Goddess> findByCondition(GoddessQuery q) {
    StringBuilder sql = new StringBuilder("SELECT " + COLUMNS + " FROM " + TABLE + " WHERE 1=1");
    List<Object> params = new ArrayList<>();
    
    if (q.getNameLike() != null) {
        sql.append(" AND name LIKE ?");
        params.add("%" + q.getNameLike() + "%");
    }
    if (q.getMobile() != null) {
        sql.append(" AND mobile = ?");
        params.add(q.getMobile());
    }
    if (q.getCreateTimeStart() != null) {
        sql.append(" AND create_time >= ?");
        params.add(q.getCreateTimeStart());
    }
    if (q.getCreateTimeEnd() != null) {
        sql.append(" AND create_time <= ?");
        params.add(q.getCreateTimeEnd());
    }
    
    return executeQuery(sql.toString(), params.toArray());
}

總結

JDBC 是 Java 持久層的基石。掌握 連接池配置、PreparedStatement 防注入、try-with-resources 資源管理、事務邊界控制、動態 SQL 構建 等核心技能，不僅能應對面試，更能在不引入 ORM 框架的輕量級場景（微服務、工具類、遷移腳本）中高效落地。理解 JDBC 原理，亦是深入掌握 MyBatis、Hibernate、Spring Data JPA 等上層框架的前提。