从前面的代码示例中可以看出,Statement、PreparedStatement、CallableStatement 有三个 execute 开头的方法:execute、executeQuery、executeUpdate。
三个的返回参数的区别:
方法名 | 返回参数 | 支持的SQL |
---|---|---|
executeQuery | ResultSet | SELECT |
executeUpdate | int ,代表影响的行数。对于什么都不返回的请求,则返回0。 | CREATE、DROP、ALTER,;DELETE、UPDATE、INSERT |
execute | boolean。true代表执行的SQL可以返回ResultSet,false代表可以返回影响的行数。 | 所有 |
我们测试下用 execute、executeUpdate 删除数据库、创建数据库、创建表、删除表会返回什么?
package demo;
import org.junit.Test;
import java.sql.*;
/**
* 创建数据库和表格,并查询表格信息
*/
public class CreateDbAndTableExampleV2 {
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"; // 注意,这里未指定数据库
/**
* 获取数据库连接
*/
public static Connection getConnection() throws SQLException, ClassNotFoundException {
Class.forName(JDBC_DRIVER); // 注册 MySQL JDBC 驱动
return DriverManager.getConnection(DB_URL, USER, PASSWORD);
}
@Test
public void test01() throws SQLException, ClassNotFoundException {
Connection conn = getConnection();
Statement stmt = null;
boolean result;
try {
stmt = conn.createStatement();
// 已有则删除
result = stmt.execute("DROP DATABASE IF EXISTS `bank`");
System.out.println("drop database: " + result);
// 创建库
result = stmt.execute("CREATE DATABASE `bank`");
System.out.println("create database: " + result);
// 切换到 bank 库
conn.setCatalog("bank");
// 需要需要重新获取stmt
stmt.close();
stmt = conn.createStatement();
// 创建表
result = stmt.execute("CREATE TABLE `user_balance` (\n" +
" `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,\n" +
" `name` VARCHAR(45) NOT NULL ,\n" +
" `balance` BIGINT NOT NULL ,\n" +
" PRIMARY KEY (`id`)\n" +
")\n" +
"ENGINE = InnoDB\n" +
"DEFAULT CHARACTER SET = utf8mb4\n" +
"COLLATE = utf8mb4_general_ci;");
System.out.println("create table: " + result);
// 删除表
result = stmt.execute("DROP TABLE `user_balance`");
System.out.println("drop table: " + result);
} finally {
if (stmt != null) {
stmt.close();
}
conn.close();
}
}
@Test
public void test02() throws SQLException, ClassNotFoundException {
Connection conn = getConnection();
Statement stmt = null;
int affected;
try {
stmt = conn.createStatement();
// 已有则删除
affected = stmt.executeUpdate("DROP DATABASE IF EXISTS `bank`");
System.out.println("drop db: " + affected);
// 创建库
affected = stmt.executeUpdate("CREATE DATABASE `bank`");
System.out.println("create db: " + affected);
// 切换到 bank 库
conn.setCatalog("bank");
// 需要需要重新获取stmt
stmt.close();
stmt = conn.createStatement();
// 创建表
affected = stmt.executeUpdate("CREATE TABLE `user_balance` (\n" +
" `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,\n" +
" `name` VARCHAR(45) NOT NULL ,\n" +
" `balance` BIGINT NOT NULL ,\n" +
" PRIMARY KEY (`id`)\n" +
")\n" +
"ENGINE = InnoDB\n" +
"DEFAULT CHARACTER SET = utf8mb4\n" +
"COLLATE = utf8mb4_general_ci;");
System.out.println("create table: " + affected);
// 删除表
affected = stmt.executeUpdate("DROP TABLE `user_balance`");
System.out.println("drop table: " + affected);
} finally {
if (stmt != null) {
stmt.close();
}
conn.close();
}
}
}
注意,这里引入的junit单元测试框架。
执行 test01
,结果是:
drop database: false
create database: false
create table: false
drop table: false
执行 test02
,结果是:
drop db: 0
create db: 1
create table: 0
drop table: 0