JDBC 更新数据

数据库和表设计

-- 创建数据库
CREATE DATABASE `bank`;

-- 切换到 bank 库
USE `bank`;

-- 创建表
CREATE TABLE `user_balance` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `balance` BIGINT NOT NULL ,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci;

使用 Statement 更新数据

在 user_balance 表中准备两条数据：

mysql> select * from user_balance;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | xigua |    1000 |
|  2 | fanqie |    1001 |
+----+--------+---------+
2 rows in set (0.00 sec)

以下是更新/删除数据的示例：

package demo;

import java.sql.*;

/**
 * 使用 Statement 更新数据
 */
public class StatementUpdateDemo {

    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/bank";

    /**
     * 根据name更新balance
     */
    public static void update(String name, Long balance) throws ClassNotFoundException, SQLException {
        Class.forName(JDBC_DRIVER);
        Connection conn =  DriverManager.getConnection(DB_URL, USER, PASSWORD);
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            String sql = String.format("UPDATE user_balance SET balance=%s WHERE name='%s'", balance, name);
            int affectRowsNum = stmt.executeUpdate(sql);
            System.out.println("影响的行数：" + affectRowsNum);
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            conn.close();
        }
    }


    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        update("xigua", 1002L);
    }
}

运行后输出：

影响的行数：1

查看 user_balance表中内容：

mysql> select * from user_balance;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | xigua |    1002 |
|  2 | fanqie |    1001 |
+----+--------+---------+
1 row in set (0.00 sec)

可以看到 name 为 xigua 的记录的balance变成了1002。

使用 PreparedStatement 更新数据

package demo;

import java.sql.*;

/**
 * 使用 PreparedStatement 更新数据
 */
public class PreparedStatementUpdateDemo {

    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/bank";

    public static void update(String name, Long balance) throws ClassNotFoundException, SQLException {
        Class.forName(JDBC_DRIVER);
        Connection conn =  DriverManager.getConnection(DB_URL, USER, PASSWORD);
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("UPDATE user_balance SET balance=? WHERE name=?");
            pstmt.setLong(1, balance);
            pstmt.setString(2, name);
            int affectRowsNum = pstmt.executeUpdate();
            System.out.println("影响的行数：" + affectRowsNum);
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
            conn.close();
        }
    }


    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        update("xigua", 1002L);
    }
}

执行结果：

影响的行数：1

查看 user_balance表中内容：

mysql> select * from user_balance;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | xigua |    1002 |
|  2 | fanqie |    1001 |
+----+--------+---------+
1 row in set (0.00 sec)

可以看到 name 为 xigua 的记录的balance变成了1002。