简介：
- 数据库连接：用于向数据库发送请求，一个数据库连接就是一个socket连接
- CRUD：crud是指在做计算处理时的增加(Create)、读取(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写。crud主要被用在描述软件系统中数据库或者持久层的基本操作功能。
java
-
三种操作方式
- Statement：用于执行不带参数的简单sql语句—现在很少用：
- sql注入：因为它的语句，只是用string作了简单的拼接
- 性能差等等问题
- PreparedStatement：用于执行预编译sql语句
- CallableStatement：用于执行对存储过程的调用
- Statement：用于执行不带参数的简单sql语句—现在很少用：
-
类图关系：
mysql实现了jdbc的三个Statment操作接口 -
重点关注PrepareStatement
代码
- 前置：封装一个获取连接的工具类：
package utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class ConnGetUtil {
/**
* 加载配置文件获取一个连接
*/
public static Connection getConnection() throws Exception {
InputStream resourceAsStream = ConnGetUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
Class.forName(properties.getProperty("driverClass"));
Connection connection = DriverManager.getConnection(properties.getProperty("url"),
properties.getProperty("user"),
properties.getProperty("password"));
return connection;
}
/**
* 关闭数据库连接和PreparedStatment
*/
public static void closeConnection(Connection connection, PreparedStatement ps, ResultSet resultSet) {
if (ps != null) {
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
Statement代码示例：
- 由于Statement存在不安全的场景：故这里只做简单介绍：
package StatementTest;
import org.junit.Test;
import utils.ConnGetUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class Test1 {
@Test
public void test01() throws Exception {
Connection connection = ConnGetUtil.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from user_table;");
statement.execute("insert into user_table(user , password, balance) values ('qianliu', '333', 200)");
// System.out.println(resultSet.toString());
// System.out.println(resultSet.getArray(1));
}
}
- 输出略
PrepareStatement代码示例：
- 通用操作：
package PreparedStatementTest;
import domain.Order;
import domain.UserTable;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import utils.ConnGetUtil;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
/**
* 将Test1中的CRUD操作，封装为通用的，不再限制死
*/
public class Test2Common {
private Connection connection;
private PreparedStatement preparedStatement;
private ResultSet resultSet;
@Before
public void setConnection() {
try {
connection = ConnGetUtil.getConnection();
} catch (Exception e) {
System.out.println("获取连接失败");
e.printStackTrace();
}
}
@After
public void close() {
ConnGetUtil.closeConnection(connection, preparedStatement, resultSet);
}
/**
* 1. 开始封装通用的查询操作
*/
public List<Object> queryUserTable(String sql, Object... args) throws Exception{
// 1.先获取连接
connection = ConnGetUtil.getConnection();
// 2.获取PreparedStatement，并进行预编译
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1, args[i]);
}
// 3.执行查询
ResultSet resultSet = preparedStatement.executeQuery();
// 4.通过反射赋值
List<Object> result = new ArrayList<>();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while (resultSet.next()) {
UserTable userTable = new UserTable();
for (int i = 0; i < columnCount; i++) {
String columnName = metaData.getColumnName(i + 1);
Object object = resultSet.getObject(i + 1);
// 反射获取UserTable中的字段
Field declaredField = UserTable.class.getDeclaredField(columnName);
if (declaredField != null) {
declaredField.setAccessible(true);
declaredField.set(userTable, object);
}
}
System.out.println(userTable);
result.add(userTable);
}
return result;
}
@Test
public void testQuery() throws Exception {
System.out.println("============== 查询一条 ==============");
String sql = "select id, user, password, balance from user_table where id = ?;";
queryUserTable(sql, 1);
System.out.println("============== 查询所有 ==============");
sql = "select id, user, password, balance from user_table;";
queryUserTable(sql);
System.out.println("============== 只查一个字段 ==============");
sql = "select user from user_table;";
queryUserTable(sql);
}
/**
* 2. 使用泛型操作
*/
public <T> List<T> queryWithGeneric(String sql, Class<T> tClass, Object... args) throws Exception{
// 1.先获取连接
connection = ConnGetUtil.getConnection();
// 2.获取PreparedStatement，并进行预编译
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1, args[i]);
}
// 3.执行查询
ResultSet resultSet = preparedStatement.executeQuery();
// 4.通过反射赋值
List<T> result = new ArrayList<>();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while (resultSet.next()) {
T t = tClass.newInstance();
for (int i = 0; i < columnCount; i++) {
// 这里需要使用getColumnLable获取label标志（别名），不能用getColumnName（只能获取表的字段名）
// String columnName = metaData.getColumnName(i + 1);
String columnLabel = metaData.getColumnLabel(i + 1);
Object object = resultSet.getObject(i + 1);
// 反射获取UserTable中的字段
Field declaredField = tClass.getDeclaredField(columnLabel);
if (declaredField != null) {
declaredField.setAccessible(true);
declaredField.set(t, object);
}
}
System.out.println(t);
result.add(t);
}
return result;
}
/**
* 用泛型测试最通用的
* @throws Exception
*/
@Test
public void testQueryGeneric() throws Exception {
System.out.println("============== 使用泛型， 查询一条 ==============");
String sql = "select id, user, password, balance from user_table where id = ?;";
queryWithGeneric(sql, UserTable.class, 1);
System.out.println("============== 使用泛型， 查询所有 ==============");
sql = "select id, user, password, balance from user_table;";
queryWithGeneric(sql, UserTable.class);
}
/**
* 测试数据库字段和类字段不匹配的情况：
* 1. select的时候，需要对字段设置别名
* 2. 通用里面，需要用getColumnLabel()获取别名，不能使用getColumnName
*/
@Test
public void testOrder() throws Exception {
System.out.println("============== 使用泛型， 查询所有 ==============");
String sql = "select order_id orderId, order_name orderName, order_date orderDate from `order`;";
queryWithGeneric(sql, Order.class);
}
}
输出：
Wed Jun 10 22:19:53 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
============== 使用泛型， 查询一条 ==============
Wed Jun 10 22:19:53 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
UserTable{id=1, user='lisi', password='456', balance=9999}
============== 使用泛型， 查询所有 ==============
Wed Jun 10 22:19:53 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
UserTable{id=1, user='lisi', password='456', balance=9999}
UserTable{id=2, user='qianliu', password='333', balance=200}
UserTable{id=3, user='wangwu', password='123_new', balance=1000}
UserTable{id=4, user='zhagnsan', password='123', balance=1000}
UserTable{id=12, user='lisi', password='777', balance=9999}
UserTable{id=13, user='lisi', password='777', balance=1500}
UserTable{id=22, user='qianliu', password='333', balance=200}
Wed Jun 10 22:19:53 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
============== 使用泛型， 查询所有 ==============
Wed Jun 10 22:19:53 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Order{orderId=1, orderName='pens', orderDate=2000-01-01 00:00:00.0}
Order{orderId=2, orderName='food', orderDate=1995-03-12 00:00:00.0}
Wed Jun 10 22:19:53 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
============== 查询一条 ==============
Wed Jun 10 22:19:53 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
UserTable{id=1, user='lisi', password='456', balance=9999}
============== 查询所有 ==============
Wed Jun 10 22:19:53 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
UserTable{id=1, user='lisi', password='456', balance=9999}
UserTable{id=2, user='qianliu', password='333', balance=200}
UserTable{id=3, user='wangwu', password='123_new', balance=1000}
UserTable{id=4, user='zhagnsan', password='123', balance=1000}
UserTable{id=12, user='lisi', password='777', balance=9999}
UserTable{id=20, user='lisi', password='777', balance=1500}
UserTable{id=22, user='qianliu', password='333', balance=200}
============== 只查一个字段 ==============
Wed Jun 10 22:19:53 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
UserTable{id=0, user='lisi', password='null', balance=0}
UserTable{id=0, user='qianliu', password='null', balance=0}
UserTable{id=0, user='wangwu', password='null', balance=0}
UserTable{id=0, user='zhagnsan', password='null', balance=0}
UserTable{id=0, user='lisi', password='null', balance=0}
UserTable{id=0, user='qianliu', password='null', balance=0}
- 批量操作效率：
package PreparedStatementTest;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import sun.lwawt.macosx.CSystemTray;
import utils.ConnGetUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* 测试批量提交
*/
public class TestBatch {
private Connection connection;
private PreparedStatement preparedStatement;
private ResultSet resultSet;
@Before
public void setConnection() {
try {
connection = ConnGetUtil.getConnection();
} catch (Exception e) {
System.out.println("获取连接失败");
e.printStackTrace();
}
}
@After
public void close() {
ConnGetUtil.closeConnection(connection, preparedStatement, resultSet);
}
/**
* 测试批量提交
* 方式1：使用Statement
* 方式2：使用PreparedStatement直接提交，简化sql翻译
* 因为PreparedStatement只翻译一次，故下面四项，都只做一次
* 语法检查：
* 语义检查：
* 翻译成二进制指令：
* 缓存
* 后续占位符传入的时候，只需要传参过去即可
* 方式3：使用PreparedStatement批量提交
* 方式4：使用PreparedStatement批量提交+一次性提交（利用AutoCommit方式）
*/
@Test
public void testBatch() throws Exception{
int batchSize = 20000;
System.out.println("================ 方式1：使用Statement ================");
long start = System.currentTimeMillis();
Statement statement = connection.createStatement();
for (int i = 0; i < batchSize; i++) {
String sql = "insert into batch_test(name) values('statement" + i + "');";
statement.execute(sql);
}
statement.close();
System.out.println("时间花费为：" + (System.currentTimeMillis() - start) + " ms");
System.out.println("================ 方式2：使用PreparedStatement直接提交，简化sql翻译 ================");
start = System.currentTimeMillis();
preparedStatement = connection.prepareStatement("insert into batch_test(name) values(?);");
for (int i = 0; i < batchSize; i++) {
preparedStatement.setString(1, "preparedStatement" + i);
preparedStatement.execute();
}
preparedStatement.close();
System.out.println("时间花费为：" + (System.currentTimeMillis() - start) + " ms");
System.out.println("================ 方式3：使用PreparedStatement批量提交 ================");
start = System.currentTimeMillis();
preparedStatement = connection.prepareStatement("insert into batch_test(name) values(?);");
for (int i = 0; i < batchSize; i++) {
preparedStatement.setString(1, "preparedStatement" + i);
preparedStatement.addBatch();
if (i % 500 == 0 || i >= batchSize - 1) {
// 批量执行、批量清空
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
preparedStatement.close();
System.out.println("时间花费为：" + (System.currentTimeMillis() - start) + " ms");
System.out.println("================ 方式4：使用PreparedStatement批量提交+一次性提交（利用AutoCommit方式） ================");
start = System.currentTimeMillis();
preparedStatement = connection.prepareStatement("insert into batch_test(name) values(?);");
connection.setAutoCommit(false);
for (int i = 0; i < batchSize; i++) {
preparedStatement.setString(1, "preparedStatement" + i);
preparedStatement.addBatch();
if (i % 500 == 0 || i >= batchSize - 1) {
// 批量执行、批量清空
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
connection.commit();
preparedStatement.close();
System.out.println("时间花费为：" + (System.currentTimeMillis() - start) + " ms");
}
@Test
public void testBestBatchInsert() throws Exception{
int batchSize = 20000;
System.out.println("================ 方式4：使用PreparedStatement批量提交+一次性提交（利用AutoCommit方式） ================");
long start = System.currentTimeMillis();
preparedStatement = connection.prepareStatement("insert into batch_test(name) values(?);");
connection.setAutoCommit(false);
for (int i = 0; i < batchSize; i++) {
preparedStatement.setString(1, "preparedStatement" + i);
preparedStatement.addBatch();
if (i % 500 == 0 || i >= batchSize - 1) {
// 批量执行、批量清空
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
connection.commit();
preparedStatement.close();
System.out.println("时间花费为：" + (System.currentTimeMillis() - start) + " ms");
}
}
输出：
================ 方式1：使用Statement ================
时间花费为：6082 ms
================ 方式2：使用PreparedStatement直接提交，简化sql翻译 ================
时间花费为：5706 ms
================ 方式3：使用PreparedStatement批量提交 ================
时间花费为：4940 ms
================ 方式4：使用PreparedStatement批量提交+一次性提交（利用AutoCommit方式） ================
时间花费为：1693 ms
- 操作二进制大文件：
package PreparedStatementTest;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import utils.ConnGetUtil;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Date;
/**
* 测试Bloc的插入、查询
* （删除和更新类似，这里不做描述了）
*/
public class TestBlob {
private Connection connection;
private PreparedStatement preparedStatement;
private ResultSet resultSet;
@Before
public void setConnection() {
try {
connection = ConnGetUtil.getConnection();
} catch (Exception e) {
System.out.println("获取连接失败");
e.printStackTrace();
}
}
@After
public void close() {
ConnGetUtil.closeConnection(connection, preparedStatement, resultSet);
}
/**
* 测试插入blob
*/
@Test
public void testInsertBlob() throws Exception {
String sql = "insert into customers(name, email, birth, photo) values(?, ?, ?, ?);";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, "zhangsan");
preparedStatement.setObject(2, "zhangsan.qq.com");
preparedStatement.setObject(3, new Date());
FileInputStream fis = new FileInputStream(
new File("src/main/resources/tianfuzhen.jpg"));
preparedStatement.setBlob(4, fis);
preparedStatement.execute();
fis.close();
}
/**
* 测试查询blob
*/
@Test
public void testGetBlob() throws Exception {
String sql = "select * from customers;";
preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet != null) {
System.out.println("查询到了结果...");
if (resultSet.next()) {
// 声明一个输入流，读取db中的照片
Blob photo1 = resultSet.getBlob("photo");
InputStream binaryStream = photo1.getBinaryStream();
// 声明一个输出流，用于保存照片
FileOutputStream fileOutputStream = new FileOutputStream("src/main/resources/tianfuzhen_read.jpg");
byte[] buf = new byte[1024];
int len = 0;
while ((len = binaryStream.read(buf)) != -1) {
fileOutputStream.write(buf, 0, len);
}
binaryStream.close();
fileOutputStream.close();
}
}
}
}
CallableStatement代码示例：
- 存储过程略，待后续分析mysql的call过程再看
扫一扫
1113
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
