使用JDBC中的PreparedStatement操作MySQL数据库表

news/2024/5/18 22:59:15 标签: mysql, java, jdbc, sql, Statement

根据《JDBC测试MySQL准备工作》一文先将数据库和表创建完毕。

  • 引入依赖
<dependencies>
    <dependency>
        <groupId>sql>mysql</groupId>
        <artifactId>sql>mysql-connector-java</artifactId>
        <version>8.0.21</version>
    </dependency>
</dependencies>

java">import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test {

    private static final String url = "jdbc:sql>mysql://gosuncn.fun:3306/gosuncn?serverTimezone=Asia/Shanghai&characterEncoding=utf8";

    private static final String user = "gosuncn";

    private static final String password = "gosuncn";

    private static final String sql = "insert into t_user (username, password) VALUES (?, ?)";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, "黎明");
            preparedStatement.setString(2, "123456");
            int effectRowCount = preparedStatement.executeUpdate();
            System.out.println("effectRowCount = " + effectRowCount);
        } catch (SQLException exception) {
            System.out.println("SQLException Happening ...");
        }
    }
}

executeUpdate()方法可以执行插入、更新、删除以及像DML没有返回值的语句。

返回值:

（1）、执行DML语句返回受影响的行数。
（2）、没有返回的内容的语句返回值为0。

sql">insert into t_user (username, password) VALUES (?, ?)

preparedStatement.setString(1, "黎明") 设置第1个 ❓ 的值。
preparedStatement.setString(2, "123456") 设置第2个 ❓ 的值。

java">import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test {

    private static final String url = "jdbc:sql>mysql://gosuncn.fun:3306/gosuncn?serverTimezone=Asia/Shanghai&characterEncoding=utf8";

    private static final String user = "gosuncn";

    private static final String password = "gosuncn";

    private static final String sql = "delete from t_user where id = ?";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setInt(1, 6);
            int effectRowCount = preparedStatement.executeUpdate();
            System.out.println("effectRowCount = " + effectRowCount);
        } catch (SQLException exception) {
            System.out.println("SQLException Happening ...");
        }
    }
}

删除t_user表id为6的记录。

java">import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test {

    private static final String url = "jdbc:sql>mysql://gosuncn.fun:3306/gosuncn?serverTimezone=Asia/Shanghai&characterEncoding=utf8";

    private static final String user = "gosuncn";

    private static final String password = "gosuncn";

    private static final String sql = "update t_user set password = ? where username = ?";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, "321654");
            preparedStatement.setString(2, "Paul");
            int effectRowCount = preparedStatement.executeUpdate();
            System.out.println("effectRowCount = " + effectRowCount);
        } catch (SQLException exception) {
            System.out.println("SQLException Happening ...");
        }
    }
}

根据username修改password。

插入成功时 - 获取插入记录的主键

java">import java.sql.*;

public class Test {

    private static final String url = "jdbc:sql>mysql://gosuncn.fun:3306/gosuncn?serverTimezone=Asia/Shanghai&characterEncoding=utf8";

    private static final String user = "gosuncn";

    private static final String password = "gosuncn";

    private static final String sql = "insert into t_user (username, password) VALUES (?, ?)";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            preparedStatement.setString(1, "黎明");
            preparedStatement.setString(2, "123456");
            int effectRowCount = preparedStatement.executeUpdate();
            System.out.println("effectRowCount = " + effectRowCount);
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            while (generatedKeys.next()) {
                int keysInt = generatedKeys.getInt(1);
                System.out.println("keysInt = " + keysInt);
            }
        } catch (SQLException exception) {
            System.out.println("SQLException Happening ...");
        }
    }
}

  • 总结

    • 第一步：connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)创建时，设置第二个参数。
    • 第二步：preparedStatement.getGeneratedKeys() 获取生成的主键结果集。

查询操作获取结果集

java">import java.sql.*;

public class Test {

    private static final String url = "jdbc:sql>mysql://gosuncn.fun:3306/gosuncn?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8";

    private static final String user = "gosuncn";

    private static final String password = "gosuncn";

    private static final String sql = "select id, username, password from t_user where username = 'Apollo'";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String username = resultSet.getString(2);
                String password = resultSet.getString(3);
                System.out.print(id + "\t");
                System.out.print(username + "\t");
                System.out.println(password);
                int idLabel = resultSet.getInt("id");
                String usernameLabel = resultSet.getString("username");
                String passwordLabel = resultSet.getString("password");
                System.out.print(idLabel + "\t");
                System.out.print(usernameLabel + "\t");
                System.out.println(passwordLabel);
            }
        } catch (SQLException exception) {
            System.out.println("SQLException Happening ...");
        }
    }
}

获取结果集后，获取具体的列时，有两种方式：①：通过索引坐标；②：通过列字段名。

设置事务提交

java">import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test {

    private static final String url = "jdbc:sql>mysql://gosuncn.fun:3306/gosuncn?serverTimezone=Asia/Shanghai&characterEncoding=utf8";

    private static final String user = "gosuncn";

    private static final String password = "gosuncn";

    private static final String sql = "delete from t_user where id > ?";

    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
            /**
             * 第一步：设置自动提交为 false 。
             */
            connection.setAutoCommit(false);
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 3);
            int effectRowCount = preparedStatement.executeUpdate();
            //int x = 1 / 0;
            System.out.println("effectRowCount = " + effectRowCount);
            /**
             * 提交事务
             */
            connection.commit();
        } catch (Exception exception) {
            System.out.println("Exception Happening ...");
            try {
                /**
                 * 第二步：回滚事务
                 */
                if (connection != null) {
                    connection.rollback();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } finally {
            if (connection != null) {
                /**
                 * 第三步：修改回原来的 true 。关闭连接
                 */
                connection.setAutoCommit(true);
                connection.close();
            }
        }
    }
}

批量添加数据

java">import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

public class Test {

    private static final String url = "jdbc:sql>mysql://gosuncn.fun:3306/gosuncn?serverTimezone=Asia/Shanghai&characterEncoding=utf8";

    private static final String user = "gosuncn";

    private static final String password = "gosuncn";

    private static final String sql = "insert into t_user (username, password) VALUES (?, ?)";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (int i = 0; i < 10; i++) {
                preparedStatement.setString(1, "黎明-" + (i + 1));
                preparedStatement.setString(2, "123456");
                preparedStatement.addBatch();
            }
            int[] batchRowEffect = preparedStatement.executeBatch();
            System.out.println("batchRowEffect = " + Arrays.toString(batchRowEffect));
        } catch (SQLException exception) {
            System.out.println("SQLException Happening ...");
        }
    }
}

先将SQL语句添加到批处理中，在批量执行语句。

①：preparedStatement.addBatch() // 先将SQL语句添加到批处理中

②：preparedStatement.executeBatch() // 在批量执行语句


http://www.niftyadmin.cn/n/923295.html

相关文章

使用Java程序发送一封QQ邮件

使用Java程序发送一封QQ邮件

首先&#xff0c;添加POM依赖 <?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.a…
阅读更多...
SpringBoot使用MockMvc简单测试接口

SpringBoot使用MockMvc简单测试接口

接口内容 import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController;RestController public class HelloController {GetMapping("/hello")public String hello() {return "Hello World! W…
阅读更多...
Springboot使用SocketJs实现通信

Springboot使用SocketJs实现通信

先看一下项目结构&#xff1a; 一个简单的SpringBoot项目&#xff1a;POM文件&#xff1a; <?xml version"1.0" encoding"UTF-8"?> <project xmlns"http://maven.apache.org/POM/4.0.0" xmlns:xsi"http://www.w3.org/2001/XMLS…
阅读更多...
MySQL备份命令mysqldump浅记

MySQL备份命令mysqldump浅记

备份所有的数据库 mysqldump -u<用户名> -p --all-databases > <备份文件路径>举例&#xff1a;mysqldump -uroot -p --all-databases > /root/alldb.sql 使用root用户对其权限下的所有库进行备份&#xff0c;生成SQL脚本&#xff0c;重定向保存到/root/all…
阅读更多...
Java实现文件的下载功能

Java实现文件的下载功能

首先呢&#xff0c;我们先建立一个Springboot项目&#xff1a; <?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:sch…
阅读更多...
自定义Maven插件入门

自定义Maven插件入门

新建一个Maven项目并加入必要依赖 <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/maven-v4_0_0.xsd…
阅读更多...
使用POI将数据库中的数据导入到Excel中

使用POI将数据库中的数据导入到Excel中

准备工作&#xff1a; 创建表和数据 -- ---------------------------- -- Table structure for sys_role -- ---------------------------- DROP TABLE IF EXISTS sys_role; CREATE TABLE sys_role (ID int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 角色ID,NAME varch…
阅读更多...
rabbitmq保证消息投递成功的对比分析

rabbitmq保证消息投递成功的对比分析

pom.xml 引入rabbitmq所需jar包 <?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.…
阅读更多...
最新文章

Copyright @ 2022~2023