Java Spring整合JDBC案例实操

本文目录导读：

1. 项目结构
2. Maven 依赖 (pom.xml)
3. 数据库表结构
4. 配置文件
5. Java 代码
6. 运行结果示例
7. 关键点说明

我来为您提供一个完整的 Java Spring 整合 JDBC 的实操案例。

项目结构

spring-jdbc-demo/
├── src/main/java/
│   └── com/example/
│       ├── entity/
│       │   └── User.java
│       ├── dao/
│       │   └── UserDao.java
│       ├── service/
│       │   └── UserService.java
│       └── Main.java
├── src/main/resources/
│   ├── applicationContext.xml
│   └── jdbc.properties
└── 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>spring-jdbc-demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <spring.version>5.3.20</spring.version>
    </properties>
    <dependencies>
        <!-- Spring 核心 -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!-- Spring JDBC -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!-- MySQL 驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        <!-- 连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.15</version>
        </dependency>
        <!-- 单元测试 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

数据库表结构

CREATE DATABASE IF NOT EXISTS spring_jdbc_demo;
USE spring_jdbc_demo;
CREATE TABLE IF NOT EXISTS `user` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `password` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100),
    `age` INT,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据
INSERT INTO `user` (`username`, `password`, `email`, `age`) VALUES
('张三', '123456', 'zhangsan@example.com', 25),
('李四', 'abcdef', 'lisi@example.com', 30),
('王五', '789012', 'wangwu@example.com', 28);

配置文件

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="
           http://www.springframework.org/schema/beans 
           http://www.springframework.org/schema/beans/spring-beans.xsd
           http://www.springframework.org/schema/context 
           http://www.springframework.org/schema/context/spring-context.xsd">
    <!-- 加载数据库配置文件 -->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <!-- 配置 Druid 数据源 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <!-- 连接池配置 -->
        <property name="initialSize" value="5"/>
        <property name="minIdle" value="5"/>
        <property name="maxActive" value="20"/>
        <property name="maxWait" value="60000"/>
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>
        <property name="minEvictableIdleTimeMillis" value="300000"/>
        <property name="validationQuery" value="SELECT 1"/>
        <property name="testWhileIdle" value="true"/>
        <property name="testOnBorrow" value="false"/>
        <property name="testOnReturn" value="false"/>
    </bean>
    <!-- 配置 JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!-- 配置 DAO 层 -->
    <bean id="userDao" class="com.example.dao.UserDao">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
    <!-- 配置 Service 层 -->
    <bean id="userService" class="com.example.service.UserService">
        <property name="userDao" ref="userDao"/>
    </bean>
</beans>

jdbc.properties

# 数据库配置
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring_jdbc_demo?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8
jdbc.username=root
jdbc.password=123456

Java 代码

实体类 (User.java)

package com.example.entity;
import java.util.Date;
public class User {
    private Integer id;
    private String username;
    private String password;
    private String email;
    private Integer age;
    private Date createTime;
    private Date updateTime;
    // 无参构造
    public User() {}
    // 有参构造
    public User(String username, String password, String email, Integer age) {
        this.username = username;
        this.password = password;
        this.email = email;
        this.age = age;
    }
    // Getter 和 Setter 方法
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    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;
    }
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    public Date getUpdateTime() {
        return updateTime;
    }
    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                ", createTime=" + createTime +
                ", updateTime=" + updateTime +
                '}';
    }
}

DAO 层 (UserDao.java)

package com.example.dao;
import com.example.entity.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
public class UserDao {
    private JdbcTemplate jdbcTemplate;
    // 注入 JdbcTemplate
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    // 1. 添加用户
    public int addUser(User user) {
        String sql = "INSERT INTO user (username, password, email, age) VALUES (?, ?, ?, ?)";
        return jdbcTemplate.update(sql, 
            user.getUsername(), 
            user.getPassword(), 
            user.getEmail(), 
            user.getAge()
        );
    }
    // 2. 删除用户
    public int deleteUser(Integer id) {
        String sql = "DELETE FROM user WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }
    // 3. 更新用户
    public int updateUser(User user) {
        String sql = "UPDATE user SET username = ?, password = ?, email = ?, age = ? WHERE id = ?";
        return jdbcTemplate.update(sql, 
            user.getUsername(), 
            user.getPassword(), 
            user.getEmail(), 
            user.getAge(), 
            user.getId()
        );
    }
    // 4. 查询单个用户
    public User getUserById(Integer id) {
        String sql = "SELECT * FROM user WHERE id = ?";
        // 方式1：使用 BeanPropertyRowMapper
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
    }
    // 5. 查询所有用户
    public List<User> getAllUsers() {
        String sql = "SELECT * FROM user";
        // 方式2：自定义 RowMapper
        return jdbcTemplate.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setAge(rs.getInt("age"));
                user.setCreateTime(rs.getTimestamp("create_time"));
                user.setUpdateTime(rs.getTimestamp("update_time"));
                return user;
            }
        });
    }
    // 6. 条件查询
    public List<User> getUserByAge(Integer minAge, Integer maxAge) {
        String sql = "SELECT * FROM user WHERE age BETWEEN ? AND ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), minAge, maxAge);
    }
    // 7. 统计用户数量
    public Integer getUserCount() {
        String sql = "SELECT COUNT(*) FROM user";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    // 8. 模糊查询
    public List<User> searchUser(String keyword) {
        String sql = "SELECT * FROM user WHERE username LIKE ? OR email LIKE ?";
        String searchPattern = "%" + keyword + "%";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), searchPattern, searchPattern);
    }
}

Service 层 (UserService.java)

package com.example.service;
import com.example.dao.UserDao;
import com.example.entity.User;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import java.util.List;
public class UserService {
    private UserDao userDao;
    // 注入 DAO
    public void setUserDao(UserDao userDao) {
        this.userDao = userDao;
    }
    // 1. 创建用户
    public void createUser(User user) {
        int result = userDao.addUser(user);
        if (result > 0) {
            System.out.println("用户创建成功：" + user.getUsername());
        } else {
            System.out.println("用户创建失败");
        }
    }
    // 2. 删除用户
    public void removeUser(Integer id) {
        User user = userDao.getUserById(id);
        if (user != null) {
            int result = userDao.deleteUser(id);
            if (result > 0) {
                System.out.println("用户删除成功：" + user.getUsername());
            }
        } else {
            System.out.println("用户不存在");
        }
    }
    // 3. 更新用户
    public void modifyUser(User user) {
        int result = userDao.updateUser(user);
        if (result > 0) {
            System.out.println("用户更新成功");
        } else {
            System.out.println("用户更新失败");
        }
    }
    // 4. 查询用户
    public void displayUser(Integer id) {
        try {
            User user = userDao.getUserById(id);
            System.out.println("查询结果：" + user);
        } catch (Exception e) {
            System.out.println("用户不存在，ID：" + id);
        }
    }
    // 5. 显示所有用户
    public void displayAllUsers() {
        List<User> users = userDao.getAllUsers();
        System.out.println("===== 所有用户列表 =====");
        for (User user : users) {
            System.out.println(user);
        }
        System.out.println("共 " + users.size() + " 条记录");
    }
    // 6. 根据年龄范围查询
    public void displayUsersByAge(int minAge, int maxAge) {
        List<User> users = userDao.getUserByAge(minAge, maxAge);
        System.out.println("===== 年龄在 " + minAge + "-" + maxAge + " 的用户 =====");
        for (User user : users) {
            System.out.println(user);
        }
    }
    // 7. 搜索用户
    public void searchUsers(String keyword) {
        List<User> users = userDao.searchUser(keyword);
        System.out.println("===== 搜索结果：'" + keyword + "' =====");
        for (User user : users) {
            System.out.println(user);
        }
    }
    // 8. 显示用户统计
    public void displayUserCount() {
        Integer count = userDao.getUserCount();
        System.out.println("当前用户总数：" + count);
    }
}

主程序 (Main.java)

package com.example;
import com.example.entity.User;
import com.example.service.UserService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Main {
    public static void main(String[] args) {
        // 加载 Spring 配置
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        // 获取 UserService
        UserService userService = context.getBean(UserService.class);
        System.out.println("========== Spring JDBC 案例演示 ==========");
        // 1. 显示所有用户
        System.out.println("\n1. 查看所有用户：");
        userService.displayAllUsers();
        // 2. 添加新用户
        System.out.println("\n2. 添加新用户：");
        User newUser = new User("赵六", "pass123", "zhaoliu@example.com", 22);
        userService.createUser(newUser);
        // 3. 查询用户
        System.out.println("\n3. 查询用户 ID=1：");
        userService.displayUser(1);
        // 4. 更新用户
        System.out.println("\n4. 更新用户 ID=1：");
        User updateUser = userService.getUserById(1);
        if (updateUser != null) {
            updateUser.setAge(26);
            updateUser.setEmail("zhangsan_new@example.com");
            userService.modifyUser(updateUser);
        }
        // 5. 根据年龄查询
        System.out.println("\n5. 查询年龄 25-30 的用户：");
        userService.displayUsersByAge(25, 30);
        // 6. 搜索用户
        System.out.println("\n6. 搜索用户包含'张'：");
        userService.searchUsers("张");
        // 7. 用户统计
        System.out.println("\n7. 用户统计：");
        userService.displayUserCount();
        // 8. 删除用户
        System.out.println("\n8. 删除用户 ID=4：");
        userService.removeUser(4);
        // 9. 最终显示
        System.out.println("\n9. 最终用户列表：");
        userService.displayAllUsers();
    }
}

运行结果示例

========== Spring JDBC 案例演示 ==========
1. 查看所有用户：
===== 所有用户列表 =====
User{id=1, username='张三', password='123456', email='zhangsan@example.com', age=25, createTime=...}
User{id=2, username='李四', password='abcdef', email='lisi@example.com', age=30, createTime=...}
User{id=3, username='王五', password='789012', email='wangwu@example.com', age=28, createTime=...}
共 3 条记录
2. 添加新用户：
用户创建成功：赵六
3. 查询用户 ID=1：
查询结果：User{id=1, ...}
...
9. 最终用户列表：
===== 所有用户列表 =====
User{id=1, username='张三', password='123456', email='zhangsan_new@example.com', age=26, ...}
User{id=2, username='李四', password='abcdef', email='lisi@example.com', age=30, ...}
User{id=3, username='王五', password='789012', email='wangwu@example.com', age=28, ...}
共 3 条记录

关键点说明

1 JdbcTemplate 核心方法

2 常见的 RowMapper

1. BeanPropertyRowMapper: 自动映射（推荐）

   new BeanPropertyRowMapper<>(User.class)

2. 自定义 RowMapper: 灵活控制映射

   new RowMapper<User>() {
       @Override
       public User mapRow(ResultSet rs, int rowNum) {
           // 自定义映射逻辑
       }
   }

3 常见错误处理

1. 数据源配置错误: 检查数据库连接参数
2. SQL 语法错误: 检查 SQL 语句
3. 实体类字段不匹配: 确保属性名与数据库字段对应
4. 找不到 Bean: 检查 applicationContext.xml 配置

这个案例演示了 Spring 整合 JDBC 的完整流程，包括 CRUD 操作、条件查询、模糊搜索等常用功能,您可以根据实际需求进行扩展和修改。