博客介绍了Java中使用Statement接口实现CRUD操作及存在的弊端，如拼串繁琐、有SQL注入问题等。重点阐述了用PreparedStatement替换Statement实现CRUD操作，包括通用增删改查方法、操作Blob类型变量及高效批量插入，还对比了二者异同，指出PreparedStatement优势。
Statement接口和它的子接口PreparedStatement
Statement接口实现CRUD操作（了解）
增、删、改；查询
/*
* 实现对数据表中数据的增删改操作
*/
public void update(String sql) {
Connection connection = null;
Statement statement = null;
try {
//1.获取数据库的连接
connection = JDBCUtils.getConnection();
//2.创建一个Statement的实例
statement = connection.createStatement();
//3.根据提供的sql语句，执行
statement.execute(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
//4.资源的关闭
try {
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (statement != null) {
statement.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
/*
* 实现对数据表的查询操作。需要使用结果集：ResultSet
*
*/
public <T> T get(String sql, Class<T> clazz) {// (sql, Customer.class)
T t = null;
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
// 获取结果集的元数据
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
// 获取结果集的列数
int columnCount = resultSetMetaData.getColumnCount();
if (resultSet.next()) {
t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// 1. 获取列的别名
// 注意：获取结果集中（相当于数据表）列的名称（别名）
String columnName = resultSetMetaData.getColumnLabel(i + 1);
// 2. 根据列名获取对应数据表中的数据
Object columnVal = resultSet.getObject(columnName);
// 3. 将数据表中得到的数据，封装进对象
// 注意：反射根据Java中类的属性获取Field对象
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columnVal);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return null;
}
Statement使用的弊端
问题一：存在拼串操作，繁琐
问题二：存在SQL注入问题
- SQL注入问题是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的SQL语句端或命令，从而利用系统的SQL引擎完成恶意行为的做法
拼串
String sql = "insert into customers(name,email,birth)values('" + name +"','"+email+"','"+birth+"')";
SQL注入：
String sql = "SELECT user,password FROM user_table WHERE user = '"+ user + "' AND password = '" + password + "'"
//原意：SELECT user,password FROM user_table WHERE user = '？' AND password = '？'
String user = "1' or ";
String password = "=1 or '1' = '1";
//执行：SELECT user,password FROM user_table WHERE user = '1' or ' AND password = '=1 or '1' = '1'
其他问题：
Statement没办法操作Blob类型变量
Statement实现批量插入时，效率较低
PreparedStatement替换Statement实现CRUD操作
1.PreparedStatement的理解：
① PreparedStatement 是Statement的子接口
② An object that represents a precompiled SQL statement.
③ 可以解决Statement的sql注入问题，拼串问题
2.使用PreparedStatement实现通用的增、删、改的方法：
通用的增删改操作
public void update(String sql,Object ...args){//sql中占位符的个数与可变形参的长度相同！
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1.获取数据库的连接
connection = JDBCUtils.getConnection();
//2.预编译sql语句，返回PreparedStatement的实例
preparedStatement = connection.prepareStatement(sql);
//3.填充占位符
for(int i = 0;i < args.length;i++){
preparedStatement.setObject(i + 1, args[i]);//小心参数声明错误！！
}
//4.执行
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}finally{
//5.资源的关闭
JDBCUtils.closeResource(connection, preparedStatement);
}
}
3.使用PreparedStatement实现通用的查询操作：version 1.0
public <T> T getInstance(Class<T> clazz,String sql,Object ...args){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
resultSet = preparedStatement.executeQuery();
// 获取结果集的元数据 :ResultSetMetaData
ResultSetMetaData metaData = resultSet.getMetaData();
// 通过ResultSetMetaData获取结果集中的列数
int columnCount = metaData.getColumnCount();
if (resultSet.next()){
T t = clazz.newInstance();
// 处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
// 获取列值
Object columnValue = resultSet.getObject(i + 1);
// 获取每个列的列名
String columnLabel = metaData.getColumnLabel(i + 1);
// 给t对象指定的columnName属性，赋值为columValue：通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
public <T> List<T> getForList(Class<T> clazz, String sql, Object ...args){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
resultSet = preparedStatement.executeQuery();
// 获取结果集的元数据 :ResultSetMetaData
ResultSetMetaData metaData = resultSet.getMetaData();
// 通过ResultSetMetaData获取结果集中的列数
int columnCount = metaData.getColumnCount();
//创建集合对象
ArrayList<T> list = new ArrayList<>();
while (resultSet.next()){
T t = clazz.newInstance();
// 处理结果集一行数据中的每一个列:给t对象指定的属性赋值
for (int i = 0; i < columnCount; i++) {
// 获取列值
Object columnValue = resultSet.getObject(i + 1);
// 获取每个列的列名
String columnLabel = metaData.getColumnLabel(i + 1);
// 给t对象指定的columnName属性，赋值为columValue：通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
【总结】
两种思想：
1.面向接口编程的思想
2.ORM编程思想：（object relational mapping）
- 一个数据表对应一个java类
- 表中的一条记录对应java类的一个对象
- 表中的一个字段对应java类的一个属性
两种技术：
1.使用结果集的元数据：ResultSetMetaData
getColumnCount():获取列数
getColumnLabel():获取列的别名
说明：如果sql中没给字段其别名，getColumnLabel()获取的就是列名
2.反射的使用（①创建对应的运行时类的对象 ② 在运行时，动态的调用指定的运行时类的属性、方法）
PreparedStatement操作Blob类型的变量
PreparedStatement可以操作Blob类型的变量。
写入操作的方法：setBlob(InputStream is);
读取操作的方法：
Blob blob = getBlob(int index);
InputStream is = blob.getBinaryStream();
具体的insert:
public void testInsert(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
String sql = "insert into test(name,email,birth,photo)values(?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,"张三");
preparedStatement.setObject(2,"zangsan@**.com");
preparedStatement.setObject(3,"1000-01-01");
FileInputStream is = new FileInputStream(new File("张三.jpg"));
preparedStatement.setBlob(4,is);
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}
具体的query:
public void testQuery(){
InputStream is = null;
FileOutputStream fos = null;
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "select id,name,email,birth,photo from customers where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,22);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
Date birth = resultSet.getDate("birth");
Custormer custormer = new Custormer(id, name, email, birth);
System.out.println(custormer);
Blob photo = resultSet.getBlob("photo");
is = photo.getBinaryStream();
fos = new FileOutputStream("nice.jpg");
byte[] buffer = new byte[1024];
int len;
while ((len = is.read(buffer)) != -1){
fos.write(buffer,0,len);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (is != null){
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if (fos != null){
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
}
注意：
如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下配置的参数：max_allowed_packet=16M。
同时注意：
修改了my.ini文件后需要重新启动mysql服务
PreparedStatement实现高效的批量插入
基础版
插入20000条数据耗时21202毫秒
@Test
public void testInsert1(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 1; i <= 20000; i++) {
preparedStatement.setString(1,"name_" + i);
preparedStatement.execute();
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为"+ (end - start));//20000:21202
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}
进阶版
插入20000条数据耗时391毫秒，插入1000000条数据耗时4734毫秒
1.addBatch()、executeBatch()、clearBatch()
2.mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。?rewriteBatchedStatements=true 写在配置文件的url后面
3.使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
@Test
public void testInsert2(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 1; i <= 1000000; i++) {
preparedStatement.setString(1,"name_" + i);
preparedStatement.addBatch();
if (i % 500 == 0){
preparedStatement.execute();
preparedStatement.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为"+ (end - start));//20000:391//1000000：4734
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}
最终版
插入20000条数据耗时309毫秒，插入1000000条数据耗时988毫秒
设置连接不允许自动提交数据
@Test
public void testInsert3(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
connection.setAutoCommit(false);
String sql = "insert into goods(name)values(?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 1; i <= 1000000; i++) {
preparedStatement.setString(1,"name_" + i);
preparedStatement.addBatch();
if (i % 500 == 0){
preparedStatement.execute();
preparedStatement.clearBatch();
}
}
connection.commit();
long end = System.currentTimeMillis();
System.out.println("花费的时间为"+ (end - start));//20000:309//1000000：988
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}
总结：PreparedStatement与Statement的异同？
① 指出二者的关系？ 接口 与 子接口的关系
② 开发中，PreparedStatement替换Statement
③ An object that represents a precompiled SQL statement.
- PreparedStatement能最大可能提高性能
DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。
在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意 义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
(语法检查，语义检查，翻译成二进制命令，缓存) - PreparedStatement可以防止SQL注入
扫一扫
1739
到【灌水乐园】发言
