三.Blob数据类型

四.事务

?一.SQL注入及解决方案

1.SQL注入介绍

SQL 注入是指利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的SQL 语句段或者命令，从而利用系统的 SQL 引擎完成恶意行为的做法。

修改 account 表通用查询，使用 Statement 实现

即把 PreparedStatement 换成 Statement ，取消预编译

修改登录实现代码

?测试用例正常

以上输入的账号和密码，通过SQL拼接，在执行过程中的SQL实际上是：

select * from account 
where useraccount = 'zhangsan' and userpassword = 'baizhan'or'1=1'

由于 1=1 永远成立，所以不论账号密码是否正确，都会返回。

导致 SQL 注入的根本原因 ：

用户输入的信息中包含 SQL 语句的关键字，并且这些关键字参与 SQL 语句的编译过程，导致 SQL 语句的原意被扭曲，进而达到 SQL 注入的目的。

2.SQL注入解决方案

只要用户提供的信息不参与 SQL 语句的编译过程，即使用户提供的信息中包含 SQL 语句的关键字，但是没有参与编译，仍然不起作用。

PreparedStatement可以将信息参数化 (相当于给输入的参数加了双引号)，仍然用 PreparedStatement 实现登录功能：

实现代码就是中的第五节

以上输入的账号和密码，通过 PreparedStatement 预编译，将 baizhan'or'1=1 作为一个整体的字符串参数设置到 SQL 当中，在执行过程中的 SQL 实际上是：

select * from account 
where useraccount = 'zhangsan' and userpassword = "baizhan'or'1=1"

二.批量插入数据

update 、 delete 本身就具有批处理操作的效果。

创建一张物品空表

Create table goods( id int PRIMARY key auto_increment, goodsname VARCHAR(25) )

向 goods 表中插入 2000 条数据

1. 通过Statement + for循环方式批量插入数据，计算执行时间

Connection conn = JDBCUtils . getConnection ();

Statement statement = conn . createStatement ();

// 获取起始时间

Long start = System . currentTimeMillis ();

for ( int i = 0 ; i < 2000 ; i ++ )

{

String sql = "insert into goods(goodsname)values('name_" + i + "')" ;

statement . execute ( sql );

}

// 获取结束时间

Long end = System . currentTimeMillis ();

JDBCUtils . close ( conn , statement );?

System . out . println ( " 插入时间为： " + ( end - start ));

由于方法一使用的是 statement ，所以每次需要重新生成 sql 字符串。

2. 通过PreparedStatement + for循环方式批量插入数据，计算执行时间

Connection conn = JDBCUtils . getConnection ();

String sql = "insert into goods(goodsname)values(?)" ;

PreparedStatement psmt = conn . prepareStatement ( sql );

Long start = System . currentTimeMillis ();

for ( int i = 0 ; i < 2000 ; i ++ )

{

psmt . setObject ( 1 , "name_" + i );

psmt . executeUpdate ();

}

Long end = System . currentTimeMillis ();

JDBCUtils . close ( conn , psmt );

System . out . println ( " 插入时间为： " + ( end - start ));

方法二使用的是 PreparedStatement ， PreparedStatement 是预编译模式， DBServer 的编译器编

译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数

直接传入就可以执行。

3. 通过PreparedStatement的addBatch()和executeBatch()进行批量插入数据

因为如果sql语句一条一条执行时效率很低（每次都访问DB服务器），所以可以先批量存储，在批量执行！

addBatch() 把若干 SQL 语句装载到一起，然后一次性传送到数据库执行，即是批量处理 sql 数

据的。

executeBatch() 会将装载到一起的 SQL 语句执行。

clearBatch() 清除缓存

注： MySql 默认情况下是不支持批处理的

但从 5.1.13 开始添加了一个 rewriteBatchStatement 的参数，让 MySql 支持批处理。

在加载 url 时设置该参数： rewriteBatchedStatements=true

url = jdbc : mysql : //localhost : 3306/baizhan?

userSSL = false&rewriteBatchedStatements = true

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Insert_test {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn=JDBCUtils.getConnection();
        String sql="insert into goods(goodsname)value(?)";
        PreparedStatement ps=conn.prepareStatement(sql);
        //开始时间
        Long start=System.currentTimeMillis();
        System.out.println(start);
        for (int i=0;i<2000;i++){
            ps.setObject(1,"name_"+i);
            //缓存sql
            ps.addBatch();
            每500条缓存执行一次
            if (i%500==0){
                //批量执行sql
                ps.executeBatch();
                //清除缓存
                ps.clearBatch();
            }
        }
        //结束时间
        Long end = System.currentTimeMillis();
        JDBCUtils.close(conn,ps);
        System.out.println("插入时间为：" + (end - start)+"ms");
    }
}

三.Blob数据类型

4.1 MySql Blob 类型

MySql中，Blob是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。
插入Blob类型的数据必须使用PreparedStatement，因为Blob类型的数据无法使用字符串拼接。
MySql中有四种Blob类型，它们除了在存储的最大容量上不同，其他是一致的。
实际使用中根据需要存入的数据大小定义不同的Blob类型。
需要注意的是：如果存储的文件过大，数据库的性能会下降。
如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在MySql的安装目录下，找到my.ini文件加上如下配置参数：max_allowed_packet=16M。并重启MySql服务。

4.2 插入Blob类型数据 ?

1. 导入 movieactor.sql

photo 列类型为 MediumBlob ，存储照片的二进制。

2. 通过 PreparedStatement 存储 Blob 类型数据

为了防止中文乱码可在url中添加参数：

useUnicode=true&characterEncoding=utf8

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Blob_test {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn=JDBCUtils.getConnection();
        String sql="insert into movieactor(actorname,photo)values(?,?)";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,"朱茵");
        InputStream is=new FileInputStream(new File("D:/zhuyin.jpg"));
        ps.setBlob(2,is);
        ps.executeUpdate();
        JDBCUtils.close(conn,ps);

    }
}

使用Navicat查看图片数据，将上面的红框处的备注改为图像就可以了。

4.3 读取 Blob 类型数据

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;

public class Blob_read {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn=JDBCUtils.getConnection();
        //条件是照片不为空
        String sql="select * from movieactor where photo !=''";
        PreparedStatement ps=conn.prepareStatement(sql);
        ResultSet rs=ps.executeQuery();
        while(rs.next()){
            //分别获取各列数据
            int id=rs.getInt("id");
            String name=rs.getString("actorname");
            //获取Blob类型数据，保存成jpg格式文件
            Blob photo=rs.getBlob("photo");
            //以二进制读取
            InputStream is=photo.getBinaryStream();
            FileOutputStream fos=new FileOutputStream(id+"_"+name+".jpg");
            byte[] buffer=new byte[1024];
            int len;
            //读取长度不为-1就是还有数据
            while((len=is.read(buffer))!=-1){
                fos.write(buffer,0,len);
            }
            fos.close();
            is.close();
        }//while
        JDBCUtils.close(conn,ps);
    }
}

读取完毕会发现照片被读到src下了。

4.4 特殊情况

读入一张 7M 左右的图片（即往数据库插入一张，代码同上面的4.2）

报错

Exception in thread "main" com.mysql.jdbc.PacketTooBigException: Packet for query

is too large (8056024 > 4194304). You can change this value on the server by

setting the max_allowed_packet' variable.

虽然 MediumBlob 允许保存最大值为 16M ，但 MySql 默认允许值为 4194304 即 4M在 my.ini 中（最底下就行） 添加max_allowed_packet=16M ， 并重启MySql服务 （可在电脑服务中右键重启，也可命令行重启）。

再次执行，即可存入数据。 ?

我遇到了重新启动还不行的问题，排查之后发现原来我之前装过一个MySQL，我修改的是之前的MySQL的my.ini文件，而不是当前运行的MySQL的my.ini文件。

四.事务

1 JDBC 事务概述

1. 事务：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要不同时成

功，要不同时失败。

2. 事务操作：

开启事务
提交事务

回滚事务

3. 在 JDBC 中，使用 Connection 对象来管理事务

开启事务：void setAutoCommit(boolean autoCommit):调用该方法设置参数为false时，代表开启事务。
提交事务：commit()
回滚事务：rollback()

2 JDBC 事务实现

银行转账业务 ：

非事务实现 ：

1. 加载相关数据，导入 bank.sql

2. 由zhangsan向lisi转账500

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Bank_test {
    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=JDBCUtils.getConnection();
            String sql="update bank set balance=balance+? where accountname=?";
            ps=conn.prepareStatement(sql);
            //给lisi转账
            ps.setObject(1,500);
            ps.setObject(2,"lisi");
            ps.executeUpdate();

            //zhangsan金额减少
            ps.setObject(1,-500);
            ps.setObject(2,"zhangsan");
            //模拟异常
            //此时遇到异常就不会执行对zhangsan操作的SQL的语句了，
            // 所以只有对lisi的执行操纵被执行了
            int i=1/0;
            ps.executeUpdate();

        }catch(Exception ex){
            //输出异常信息
            System.out.println(ex.getMessage());
        }
        finally {
            JDBCUtils.close(conn,ps);
        }
    }
}

控制台输出：

数据库更新：

严重错误！ lisi 账户增加， zhangsan 未减少。

事务实现 ：

在获取数据库连接之后，通过 Connection 对象开启事务

由 zhangsan 向 lisi 转账 500

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Bank_test {
    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=JDBCUtils.getConnection();
            //开启事务,即关闭自动提交
            conn.setAutoCommit(false);

            String sql="update bank set balance=balance+? where accountname=?";
            ps=conn.prepareStatement(sql);
            //给lisi转账
            ps.setObject(1,500);
            ps.setObject(2,"lisi");
            ps.executeUpdate();

            //zhangsan金额减少
            ps.setObject(1,-500);
            ps.setObject(2,"zhangsan");
            //模拟异常
            //此时遇到异常就不会执行对zhangsan操作的SQL的语句了，
            // 所以只有对lisi的执行操纵被执行了
            int i=1/0;
            ps.executeUpdate();

            //如果没出错，提交事务
            conn.commit();

        }catch(Exception ex){
            //如果出错,回滚事务，即回滚到事务执行之前的状态
            conn.rollback();
            //输出异常信息
            System.out.println(ex.getMessage());
        }
        finally {
            //不管是否正常提交事务，均关闭资源
            JDBCUtils.close(conn,ps);
        }
    }
}

由于有异常，所以所有操作回滚，达到多操作的原子性效果。

[大数据]三天学会JDBC（三）JDBC高级

?一.SQL注入及解决方案

1.SQL注入介绍

2.SQL注入解决方案

二.批量插入数据

三.Blob数据类型

四.事务