JDBC Statement 插入数据


本文讲述如何用 JDBC 的 Statement 在 MySQL 的表中插入(或者说添加、增加)数据。插入数据用INSERT。

数据库和表设计

-- 创建数据库
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;

JDBC Statement插入数据

下面的代码示例中给出了两个方法insertV1insertV2,都能插入数据,不过insertV2insertV1 多了一个获取插入数据主键 ID 的特性。

package demo;

import java.sql.*;

/**
 * 使用 Statement 插入数据
 */
public class StatementInsert {

    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 insertV1(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("INSERT INTO user_balance(name, balance) VALUES('%s', %s)", name, balance);
            int affectRowsNum = stmt.executeUpdate(sql);
            System.out.println("影响的行数:" + affectRowsNum);
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            conn.close();
        }
    }

    public static void insertV2(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("INSERT INTO user_balance(name, balance) VALUES('%s', %s)", name, balance);
            // 指定第2个参数为Statement.RETURN_GENERATED_KEYS,可以获取生成的主键id值
            int affectRowsNum = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            System.out.println("影响的行数:" + affectRowsNum);
            try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    System.out.println("生成的主键ID是:" + generatedKeys.getLong(1));
                }
            }
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            conn.close();
        }
    }


    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        insertV1("letian", 1000L);
        insertV2("xiaosi", 1001L);

        /**
         * 结果:
         *
         * 影响的行数:1
         * 影响的行数:1
         * 生成的主键ID是:2
         */
    }
}

运行后输出:

影响的行数:1
影响的行数:1
生成的主键ID是:2

在mysql命令行中查询表中数据:

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


(本文完)


JDBC 教程