Java混合数据库案例

本文目录导读：

1. 项目结构
2. Maven依赖配置 (pom.xml)
3. 实体类 (User.java)
4. MySQL数据库操作 (UserDao.java)
5. Redis缓存操作 (RedisDao.java)
6. 业务服务层 (UserService.java)
7. 主程序入口 (Main.java)
8. 数据库初始化脚本 (init.sql)
9. 混合数据库的优势

我来为您提供一个Java混合数据库的完整案例,同时操作MySQL和Redis数据库：

项目结构

src/
├── main/
│   ├── java/com/example/
│   │   ├── entity/
│   │   │   └── User.java
│   │   ├── dao/
│   │   │   ├── UserDao.java
│   │   │   └── RedisDao.java
│   │   ├── service/
│   │   │   └── UserService.java
│   │   └── Main.java
│   └── resources/
│       └── pom.xml

Maven依赖配置 (pom.xml)

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>hybrid-db-demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <!-- MySQL驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        <!-- Redis客户端 -->
        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
            <version>4.3.1</version>
        </dependency>
        <!-- JDBC连接池 -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>5.0.1</version>
        </dependency>
    </dependencies>
</project>

实体类 (User.java)

package com.example.entity;
import java.io.Serializable;
public class User implements Serializable {
    private Long id;
    private String username;
    private String email;
    private Integer age;
    // 构造函数
    public User() {}
    public User(Long id, String username, String email, Integer age) {
        this.id = id;
        this.username = username;
        this.email = email;
        this.age = age;
    }
    // Getters和Setters
    public Long getId() { return id; }
    public void setId(Long 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 Integer getAge() { return age; }
    public void setAge(Integer age) { this.age = age; }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                '}';
    }
}

MySQL数据库操作 (UserDao.java)

package com.example.dao;
import com.example.entity.User;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDao {
    private static HikariDataSource dataSource;
    // 初始化数据库连接池
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        dataSource = new HikariDataSource(config);
    }
    // 获取数据库连接
    private Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    // 创建用户表
    public void createTable() throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS users (" +
                    "id BIGINT AUTO_INCREMENT PRIMARY KEY," +
                    "username VARCHAR(50) NOT NULL UNIQUE," +
                    "email VARCHAR(100) NOT NULL," +
                    "age INT" +
                    ")";
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
            System.out.println("用户表创建成功");
        }
    }
    // 插入用户
    public User insertUser(User user) throws SQLException {
        String sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getEmail());
            pstmt.setInt(3, user.getAge());
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                ResultSet rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    user.setId(rs.getLong(1));
                }
            }
            return user;
        }
    }
    // 根据ID查询用户
    public User getUserById(Long id) throws SQLException {
        String sql = "SELECT * FROM users WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setLong(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setUsername(rs.getString("username"));
                user.setEmail(rs.getString("email"));
                user.setAge(rs.getInt("age"));
                return user;
            }
            return null;
        }
    }
    // 查询所有用户
    public List<User> getAllUsers() throws SQLException {
        List<User> users = new ArrayList<>();
        String sql = "SELECT * FROM users";
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setUsername(rs.getString("username"));
                user.setEmail(rs.getString("email"));
                user.setAge(rs.getInt("age"));
                users.add(user);
            }
        }
        return users;
    }
    // 更新用户
    public void updateUser(User user) throws SQLException {
        String sql = "UPDATE users SET username=?, email=?, age=? WHERE id=?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getEmail());
            pstmt.setInt(3, user.getAge());
            pstmt.setLong(4, user.getId());
            pstmt.executeUpdate();
        }
    }
    // 删除用户
    public void deleteUser(Long id) throws SQLException {
        String sql = "DELETE FROM users WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setLong(1, id);
            pstmt.executeUpdate();
        }
    }
}

Redis缓存操作 (RedisDao.java)

package com.example.dao;
import com.example.entity.User;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;
import redis.clients.jedis.JedisPoolConfig;
import com.google.gson.Gson;
public class RedisDao {
    private static JedisPool jedisPool;
    private Gson gson = new Gson();
    // 初始化Redis连接池
    static {
        JedisPoolConfig config = new JedisPoolConfig();
        config.setMaxTotal(10);
        config.setMaxIdle(5);
        config.setMinIdle(2);
        config.setTestOnBorrow(true);
        config.setTestOnReturn(true);
        jedisPool = new JedisPool(config, "localhost", 6379, 2000, null);
    }
    // 获取Redis连接
    private Jedis getJedis() {
        return jedisPool.getResource();
    }
    // 缓存用户信息
    public void cacheUser(User user) {
        try (Jedis jedis = getJedis()) {
            String key = "user:" + user.getId();
            String value = gson.toJson(user);
            jedis.setex(key, 3600, value); // 缓存1小时
            System.out.println("用户信息已缓存到Redis: " + key);
        }
    }
    // 从缓存获取用户信息
    public User getCachedUser(Long userId) {
        try (Jedis jedis = getJedis()) {
            String key = "user:" + userId;
            String value = jedis.get(key);
            if (value != null) {
                System.out.println("从Redis缓存获取用户: " + key);
                return gson.fromJson(value, User.class);
            }
            return null;
        }
    }
    // 删除缓存
    public void deleteUserCache(Long userId) {
        try (Jedis jedis = getJedis()) {
            String key = "user:" + userId;
            jedis.del(key);
            System.out.println("Redis缓存已删除: " + key);
        }
    }
    // 缓存所有用户列表
    public void cacheUserList(String data) {
        try (Jedis jedis = getJedis()) {
            jedis.setex("users:list", 1800, data); // 缓存30分钟
            System.out.println("用户列表已缓存到Redis");
        }
    }
    // 获取缓存的用户列表
    public String getCachedUserList() {
        try (Jedis jedis = getJedis()) {
            return jedis.get("users:list");
        }
    }
    // 统计用户访问次数
    public void incrementUserVisitCount(String username) {
        try (Jedis jedis = getJedis()) {
            String key = "visit:count:" + username;
            long count = jedis.incr(key);
            System.out.println(username + " 访问次数: " + count);
        }
    }
    // 获取用户访问次数
    public long getUserVisitCount(String username) {
        try (Jedis jedis = getJedis()) {
            String key = "visit:count:" + username;
            String count = jedis.get(key);
            return count != null ? Long.parseLong(count) : 0;
        }
    }
}

业务服务层 (UserService.java)

package com.example.service;
import com.example.dao.UserDao;
import com.example.dao.RedisDao;
import com.example.entity.User;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import java.sql.SQLException;
import java.util.List;
public class UserService {
    private UserDao userDao = new UserDao();
    private RedisDao redisDao = new RedisDao();
    private Gson gson = new Gson();
    // 创建用户（同时写入MySQL和Redis）
    public User createUser(User user) throws SQLException {
        // 1. 写入MySQL
        User createdUser = userDao.insertUser(user);
        // 2. 缓存到Redis
        redisDao.cacheUser(createdUser);
        // 3. 清除列表缓存
        redisDao.deleteUserCache(createdUser.getId());
        System.out.println("用户创建成功: " + createdUser);
        return createdUser;
    }
    // 获取用户（先从Redis读取，没有则从MySQL读取）
    public User getUserById(Long id) throws SQLException {
        // 1. 尝试从Redis获取
        User cachedUser = redisDao.getCachedUser(id);
        if (cachedUser != null) {
            // 统计访问次数
            redisDao.incrementUserVisitCount(cachedUser.getUsername());
            return cachedUser;
        }
        // 2. Redis没有，从MySQL查询
        User user = userDao.getUserById(id);
        if (user != null) {
            // 3. 写入Redis缓存
            redisDao.cacheUser(user);
        }
        return user;
    }
    // 获取所有用户（带缓存）
    public List<User> getAllUsers() throws SQLException {
        // 1. 尝试从Redis获取列表缓存
        String cachedList = redisDao.getCachedUserList();
        if (cachedList != null) {
            System.out.println("从Redis缓存获取用户列表");
            return gson.fromJson(cachedList, new TypeToken<List<User>>(){}.getType());
        }
        // 2. 从MySQL查询
        List<User> users = userDao.getAllUsers();
        // 3. 缓存到Redis
        redisDao.cacheUserList(gson.toJson(users));
        return users;
    }
    // 更新用户（同时更新MySQL和Redis）
    public void updateUser(User user) throws SQLException {
        // 1. 更新MySQL
        userDao.updateUser(user);
        // 2. 更新Redis缓存
        redisDao.cacheUser(user);
        System.out.println("用户更新成功: " + user);
    }
    // 删除用户（同时删除MySQL和Redis）
    public void deleteUser(Long id) throws SQLException {
        // 1. 从MySQL删除
        userDao.deleteUser(id);
        // 2. 删除Redis缓存
        redisDao.deleteUserCache(id);
        System.out.println("用户删除成功, ID: " + id);
    }
    // 获取用户访问统计
    public long getUserVisitStatistics(String username) {
        return redisDao.getUserVisitCount(username);
    }
}

主程序入口 (Main.java)

package com.example;
import com.example.entity.User;
import com.example.service.UserService;
import java.sql.SQLException;
import java.util.List;
public class Main {
    public static void main(String[] args) {
        UserService userService = new UserService();
        try {
            // 测试：创建用户
            System.out.println("=== 创建用户 ===");
            User user1 = userService.createUser(new User(null, "张三", "zhangsan@example.com", 25));
            User user2 = userService.createUser(new User(null, "李四", "lisi@example.com", 30));
            User user3 = userService.createUser(new User(null, "王五", "wangwu@example.com", 28));
            // 测试：获取单个用户
            System.out.println("\n=== 获取用户 ===");
            User fetchedUser = userService.getUserById(user1.getId());
            System.out.println("获取用户: " + fetchedUser);
            // 第二次获取，应该从Redis缓存读取
            System.out.println("\n=== 再次获取用户（应该从缓存读取）===");
            User cachedUser = userService.getUserById(user1.getId());
            System.out.println("获取用户: " + cachedUser);
            // 测试：获取所有用户
            System.out.println("\n=== 获取所有用户 ===");
            List<User> allUsers = userService.getAllUsers();
            allUsers.forEach(System.out::println);
            // 测试：更新用户
            System.out.println("\n=== 更新用户 ===");
            user1.setEmail("zhangsan_new@example.com");
            user1.setAge(26);
            userService.updateUser(user1);
            // 验证更新
            User updatedUser = userService.getUserById(user1.getId());
            System.out.println("更新后用户: " + updatedUser);
            // 测试：查看访问统计
            System.out.println("\n=== 访问统计 ===");
            long visitCount = userService.getUserVisitStatistics("张三");
            System.out.println("张三的访问次数: " + visitCount);
            // 测试：删除用户
            System.out.println("\n=== 删除用户 ===");
            userService.deleteUser(user3.getId());
            // 检查删除后的用户列表
            System.out.println("\n=== 删除后的用户列表 ===");
            List<User> remainingUsers = userService.getAllUsers();
            remainingUsers.forEach(System.out::println);
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

数据库初始化脚本 (init.sql)

-- 创建数据库
CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 使用数据库
USE mydb;
-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

混合数据库的优势

1. 性能优化：Redis缓存热点数据，减少MySQL查询压力
2. 数据一致性：通过业务层保证MySQL和Redis数据一致性
3. 灵活扩展：可以针对不同场景使用不同数据库
4. 高可用性：Redis宕机时仍可从MySQL读取数据

这个案例展示了Java混合使用MySQL和Redis的完整实现，包括数据库连接、CRUD操作、缓存策略等核心功能。