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

Java JDBC修改案例如何开发

wen java案例 30

本文目录导读：

Java JDBC修改案例如何开发

  1. 项目环境准备
  2. 基础JDBC修改实现
  3. Service层实现
  4. 完整测试案例
  5. 使用连接池优化（Druid）
  6. 最佳实践建议

我来为您详细介绍Java JDBC修改数据（UPDATE）的完整开发案例。

项目环境准备

1 添加依赖（Maven项目）

<dependencies>
    <!-- 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.20</version>
    </dependency>
</dependencies>

2 数据库表结构

CREATE TABLE `user` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100),
    `phone` VARCHAR(20),
    `status` TINYINT DEFAULT 1,
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 插入测试数据
INSERT INTO `user` (`username`, `password`, `email`, `phone`) 
VALUES ('张三', '123456', 'zhangsan@example.com', '13800138001');

基础JDBC修改实现

1 实体类

public class User {
    private Integer id;
    private String username;
    private String password;
    private String email;
    private String phone;
    private Integer status;
    private Date createTime;
    private Date updateTime;
    // 构造器、getter/setter、toString方法
    public User() {}
    public User(Integer id, String username, String password, String email, String phone) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.email = email;
        this.phone = phone;
    }
    // 省略getter/setter...
    @Override
    public String toString() {
        return "User{id=" + id + ", username='" + username + "', email='" + email + "'}";
    }
}

2 数据库连接工具类

import java.sql.*;
import java.util.ResourceBundle;
public class DBUtil {
    private static String url;
    private static String username;
    private static String password;
    static {
        try {
            // 加载配置文件
            ResourceBundle bundle = ResourceBundle.getBundle("db");
            url = bundle.getString("jdbc.url");
            username = bundle.getString("jdbc.username");
            password = bundle.getString("jdbc.password");
            // 加载驱动
            Class.forName(bundle.getString("jdbc.driver"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    // 关闭资源
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3 配置文件（db.properties）

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8
jdbc.username=root
jdbc.password=123456

4 DAO层实现

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDAO {
    // 1. 根据ID更新用户信息
    public int updateById(User user) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "UPDATE `user` SET username = ?, password = ?, email = ?, phone = ? WHERE id = ?";
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            // 设置参数
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getEmail());
            pstmt.setString(4, user.getPhone());
            pstmt.setInt(5, user.getId());
            // 执行更新
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
    }
    // 2. 动态更新（只更新非空字段）
    public int updateDynamic(User user) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        StringBuilder sql = new StringBuilder("UPDATE `user` SET ");
        List<Object> params = new ArrayList<>();
        // 动态构建SQL
        if (user.getUsername() != null) {
            sql.append("username = ?, ");
            params.add(user.getUsername());
        }
        if (user.getPassword() != null) {
            sql.append("password = ?, ");
            params.add(user.getPassword());
        }
        if (user.getEmail() != null) {
            sql.append("email = ?, ");
            params.add(user.getEmail());
        }
        if (user.getPhone() != null) {
            sql.append("phone = ?, ");
            params.add(user.getPhone());
        }
        sql.append("update_time = NOW() WHERE id = ?");
        params.add(user.getId());
        String finalSql = sql.toString().replace(", WHERE", " WHERE");
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(finalSql);
            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
    }
    // 3. 批量更新
    public int[] batchUpdate(List<User> userList) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "UPDATE `user` SET email = ?, phone = ? WHERE id = ?";
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false); // 开启事务
            pstmt = conn.prepareStatement(sql);
            for (User user : userList) {
                pstmt.setString(1, user.getEmail());
                pstmt.setString(2, user.getPhone());
                pstmt.setInt(3, user.getId());
                pstmt.addBatch(); // 添加到批处理
            }
            // 执行批处理
            int[] results = pstmt.executeBatch();
            conn.commit(); // 提交事务
            return results;
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback(); // 回滚事务
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBUtil.close(conn, pstmt, null);
        }
    }
}

Service层实现

public class UserService {
    private UserDAO userDAO = new UserDAO();
    // 修改用户信息
    public boolean updateUser(User user) {
        if (user == null || user.getId() == null) {
            throw new IllegalArgumentException("用户信息不完整");
        }
        // 参数校验
        if (user.getUsername() != null && user.getUsername().length() < 2) {
            throw new IllegalArgumentException("用户名长度不能小于2");
        }
        int result = userDAO.updateById(user);
        return result > 0;
    }
    // 批量更新用户
    public boolean batchUpdateUsers(List<User> userList) {
        if (userList == null || userList.isEmpty()) {
            return false;
        }
        int[] results = userDAO.batchUpdate(userList);
        if (results == null) {
            return false;
        }
        // 检查是否全部更新成功
        for (int result : results) {
            if (result <= 0) {
                return false;
            }
        }
        return true;
    }
}

完整测试案例

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class UpdateDemo {
    public static void main(String[] args) {
        UserService userService = new UserService();
        // 1. 修改单个用户
        System.out.println("=== 修改单个用户 ===");
        User user = new User();
        user.setId(1);
        user.setUsername("李四");
        user.setPassword("654321");
        user.setEmail("lisi@example.com");
        user.setPhone("13900139001");
        boolean success = userService.updateUser(user);
        System.out.println("修改" + (success ? "成功" : "失败"));
        // 2. 动态更新（只更新部分字段）
        System.out.println("\n=== 动态更新用户 ===");
        UserDAO userDAO = new UserDAO();
        User partialUser = new User();
        partialUser.setId(1);
        partialUser.setEmail("newemail@example.com");
        partialUser.setPhone("13700137001");
        int count = userDAO.updateDynamic(partialUser);
        System.out.println("动态更新了 " + count + " 条记录");
        // 3. 批量更新
        System.out.println("\n=== 批量更新用户 ===");
        List<User> userList = new ArrayList<>();
        User user1 = new User();
        user1.setId(1);
        user1.setEmail("user1@example.com");
        user1.setPhone("11111111111");
        User user2 = new User();
        user2.setId(2);
        user2.setEmail("user2@example.com");
        user2.setPhone("22222222222");
        userList.add(user1);
        userList.add(user2);
        boolean batchSuccess = userService.batchUpdateUsers(userList);
        System.out.println("批量更新" + (batchSuccess ? "成功" : "失败"));
        // 4. 查询验证更新结果
        System.out.println("\n=== 验证更新结果 ===");
        UserQueryDAO queryDAO = new UserQueryDAO();
        User updatedUser = queryDAO.findById(1);
        System.out.println("更新后的用户: " + updatedUser);
    }
}
// 查询DAO
class UserQueryDAO {
    public User findById(Integer id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM `user` WHERE id = ?";
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                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.setPhone(rs.getString("phone"));
                user.setStatus(rs.getInt("status"));
                user.setCreateTime(rs.getTimestamp("create_time"));
                user.setUpdateTime(rs.getTimestamp("update_time"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return null;
    }
}

使用连接池优化（Druid）

1 连接池配置

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.util.Properties;
public class DruidUtil {
    private static DataSource dataSource;
    static {
        try {
            Properties props = new Properties();
            InputStream is = DruidUtil.class.getClassLoader()
                .getResourceAsStream("druid.properties");
            props.load(is);
            dataSource = DruidDataSourceFactory.createDataSource(props);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

2 druid.properties配置

# 基本配置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=Asia/Shanghai
username=root
password=123456
# 连接池配置
initialSize=5
maxActive=20
minIdle=5
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=true
maxPoolPreparedStatementPerConnectionSize=20

最佳实践建议

1 使用PreparedStatement防止SQL注入

// 错误示例（存在SQL注入风险）
String sql = "UPDATE user SET email = '" + email + "' WHERE id = " + id;
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
// 正确示例
String sql = "UPDATE user SET email = ? WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, email);
pstmt.setInt(2, id);
pstmt.executeUpdate();

2 事务管理

Connection conn = null;
try {
    conn = DBUtil.getConnection();
    conn.setAutoCommit(false); // 开启事务
    // 执行多个更新操作
    userDAO.updateUser(conn, user1);
    userDAO.updateUser(conn, user2);
    conn.commit(); // 提交事务
} catch (SQLException e) {
    conn.rollback(); // 回滚事务
    e.printStackTrace();
} finally {
    if (conn != null) {
        conn.setAutoCommit(true);
        conn.close();
    }
}

这个完整的JDBC修改案例涵盖了：

  • 基本的单条记录更新
  • 动态SQL更新（只更新非空字段）
  • 批量更新操作
  • 事务管理
  • 连接池配置
  • 最佳实践

您可以根据具体需求选择合适的实现方式。

上一篇Java JDBC查询案例如何编写

下一篇Java JDBC连接案例怎么实现

相关文章

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