本文目录导读：
- 项目结构
- Maven依赖配置 (pom.xml)
- 实体类 (User.java)
- MySQL数据库操作 (UserDao.java)
- Redis缓存操作 (RedisDao.java)
- 业务服务层 (UserService.java)
- 主程序入口 (Main.java)
- 数据库初始化脚本 (init.sql)
- 混合数据库的优势
我来为您提供一个Java混合数据库的完整案例,同时操作MySQL和Redis数据库：
项目结构
src/
├── main/
│ ├── java/com/example/
│ │ ├── entity/
│ │ │ └── User.java
│ │ ├── dao/
│ │ │ ├── UserDao.java
│ │ │ └── RedisDao.java
│ │ ├── service/
│ │ │ └── UserService.java
│ │ └── Main.java
│ └── resources/
│ └── pom.xmlMaven依赖配置 (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;
混合数据库的优势
- 性能优化：Redis缓存热点数据，减少MySQL查询压力
- 数据一致性：通过业务层保证MySQL和Redis数据一致性
- 灵活扩展：可以针对不同场景使用不同数据库
- 高可用性：Redis宕机时仍可从MySQL读取数据
这个案例展示了Java混合使用MySQL和Redis的完整实现，包括数据库连接、CRUD操作、缓存策略等核心功能。
