IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

如何用Java连接MySQL案例

wen java案例 3

本文目录导读：

如何用Java连接MySQL案例

  1. 环境准备
  2. 数据库准备
  3. Java连接MySQL示例
  4. 使用连接池（推荐）
  5. 最佳实践建议
  6. 常见问题解决

我来为您提供一个完整的Java连接MySQL的案例,包含基础连接、CRUD操作和最佳实践。

环境准备

1 添加MySQL驱动依赖

Maven项目 (pom.xml):

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

Gradle项目 (build.gradle):

implementation 'mysql:mysql-connector-java:8.0.33'

非构建工具：下载JDBC驱动jar包并添加到classpath。

数据库准备

-- 创建数据库
CREATE DATABASE IF NOT EXISTS java_demo DEFAULT CHARACTER SET utf8mb4;
-- 创建用户表
USE java_demo;
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入测试数据
INSERT INTO users (username, email, age) VALUES
('zhangsan', 'zhangsan@example.com', 25),
('lisi', 'lisi@example.com', 30);

Java连接MySQL示例

1 基础连接示例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class MySQLConnection {
    // 数据库配置
    private static final String URL = "jdbc:mysql://localhost:3306/java_demo?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";
    public static void main(String[] args) {
        Connection connection = null;
        try {
            // 1. 注册驱动（MySQL 8.0以上版本可省略）
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 获取连接
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            // 3. 验证连接
            if (connection != null && !connection.isClosed()) {
                System.out.println("数据库连接成功！");
                System.out.println("数据库信息: " + connection.getMetaData().getDatabaseProductName());
            }
        } catch (ClassNotFoundException e) {
            System.err.println("MySQL驱动类未找到！");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("数据库连接失败！");
            e.printStackTrace();
        } finally {
            // 4. 关闭连接
            if (connection != null) {
                try {
                    connection.close();
                    System.out.println("数据库连接已关闭");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

2 完整CRUD操作示例

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDAO {
    // 数据库配置
    private static final String URL = "jdbc:mysql://localhost:3306/java_demo?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";
    // 获取数据库连接
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    // 1. 新增用户
    public boolean addUser(User user) {
        String sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getEmail());
            ps.setInt(3, user.getAge());
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    // 2. 查询所有用户
    public List<User> getAllUsers() {
        String sql = "SELECT * FROM users";
        List<User> users = new ArrayList<>();
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setEmail(rs.getString("email"));
                user.setAge(rs.getInt("age"));
                user.setCreatedAt(rs.getTimestamp("created_at"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }
    // 3. 根据ID查询用户
    public User getUserById(int id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setEmail(rs.getString("email"));
                user.setAge(rs.getInt("age"));
                user.setCreatedAt(rs.getTimestamp("created_at"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    // 4. 更新用户信息
    public boolean updateUser(User user) {
        String sql = "UPDATE users SET username = ?, email = ?, age = ? WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getEmail());
            ps.setInt(3, user.getAge());
            ps.setInt(4, user.getId());
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    // 5. 删除用户
    public boolean deleteUser(int id) {
        String sql = "DELETE FROM users WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
}

3 用户实体类

import java.util.Date;
public class User {
    private int id;
    private String username;
    private String email;
    private int age;
    private Date createdAt;
    // 无参构造
    public User() {}
    // 带参构造
    public User(int id, String username, String email, int age) {
        this.id = id;
        this.username = username;
        this.email = email;
        this.age = age;
    }
    // Getter和Setter方法
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public int getAge() { return age; }
    public void setAge(int age) { this.age = age; }
    public Date getCreatedAt() { return createdAt; }
    public void setCreatedAt(Date createdAt) { this.createdAt = createdAt; }
    @Override
    public String toString() {
        return String.format("User{id=%d, username='%s', email='%s', age=%d}",
                id, username, email, age);
    }
}

4 主程序测试

import java.util.List;
public class MainApplication {
    public static void main(String[] args) {
        UserDAO dao = new UserDAO();
        // 1. 测试新增
        System.out.println("=== 新增用户 ===");
        User newUser = new User(0, "wangwu", "wangwu@example.com", 28);
        boolean added = dao.addUser(newUser);
        System.out.println("新增结果: " + (added ? "成功" : "失败"));
        // 2. 测试查询所有用户
        System.out.println("\n=== 所有用户 ===");
        List<User> users = dao.getAllUsers();
        users.forEach(System.out::println);
        // 3. 测试按ID查询
        System.out.println("\n=== 查询ID=1的用户 ===");
        User user = dao.getUserById(1);
        if (user != null) {
            System.out.println(user);
        } else {
            System.out.println("用户不存在");
        }
        // 4. 测试更新用户
        System.out.println("\n=== 更新用户 ===");
        User updateUser = new User(1, "zhangsan_update", "zhangsan_new@example.com", 26);
        boolean updated = dao.updateUser(updateUser);
        System.out.println("更新结果: " + (updated ? "成功" : "失败"));
        // 5. 测试删除用户
        System.out.println("\n=== 删除用户 ===");
        boolean deleted = dao.deleteUser(newUser.getId());
        System.out.println("删除结果: " + (deleted ? "成功" : "失败"));
    }
}

使用连接池（推荐）

1 HikariCP连接池配置

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class DatabasePool {
    private static HikariDataSource dataSource;
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/java_demo?useSSL=false&serverTimezone=UTC");
        config.setUsername("root");
        config.setPassword("your_password");
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        // 连接池配置
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        dataSource = new HikariDataSource(config);
    }
    // 获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    // 关闭连接池
    public static void closePool() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
        }
    }
}
// 使用连接池
class UserDAOWithPool {
    public List<User> getAllUsers() {
        String sql = "SELECT * FROM users";
        List<User> users = new ArrayList<>();
        try (Connection conn = DatabasePool.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setEmail(rs.getString("email"));
                user.setAge(rs.getInt("age"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }
}

最佳实践建议

  1. 使用PreparedStatement：防止SQL注入，提高性能
  2. 使用连接池：避免频繁创建和关闭连接
  3. 使用try-with-resources：自动关闭资源
  4. 配置连接参数：
    • useSSL=false：本地开发时避免SSL警告
    • serverTimezone=UTC：解决时区问题
    • characterEncoding=utf8：支持中文
  5. 日志记录：记录SQL执行时间和错误信息
  6. 事务管理：进行多步操作时使用事务

常见问题解决

// 1. 时区错误
// 解决方案：在URL中添加 serverTimezone=Asia/Shanghai
// 2. SSL警告
// 解决方案：添加 useSSL=false（开发环境）
// 3. 中文乱码
// 解决方案：添加 characterEncoding=utf8
// 4. 驱动类找不到
// 解决方案：新版驱动使用 com.mysql.cj.jdbc.Driver

这个完整的案例涵盖了Java连接MySQL的所有基本操作,您可以根据实际需求进行调整使用。

上一篇怎么实现Java文件上传案例

下一篇Java工厂模式案例哪里找

相关文章

抱歉，评论功能暂时关闭!