8-2、preparedStatement（预编译声明）、时间类型、大数据（保存MP3等）、批处理(针对增删改，没有查)

1、preparedStatement（预编译声明）

l 它是Statement接口的子接口；

l 强大之处：

防SQL攻击；
提高代码的可读性、可维护性；
提高效率！

l 学习PreparedStatement的用法：

如何得到PreparedStatement对象：

¨ 给出SQL模板！

¨ 调用Connection的PreparedStatement prepareStatement(String sql模板)；

¨ 调用pstmt的setXxx()系列方法sql模板中的?赋值！

¨ 调用pstmt的executeUpdate()或executeQuery()，但它的方法都没有参数。

l 预处理的原理

服务器的工作：

¨ 校验sql语句的语法！

¨ 编译：一个与函数相似的东西！

¨ 执行：调用函数

PreparedStatement：

¨ 前提：连接的数据库必须支持预处理！几乎没有不支持的！

¨ 每个pstmt都与一个sql模板绑定在一起，先把sql模板给数据库，数据库先进行校验，再进行编译。执行时只是把参数传递过去而已！

¨ 若二次执行时，就不用再次校验语法，也不用再次编译！直接执行！

1　什么是SQL攻击

在需要用户输入的地方，用户输入的是SQL语句的片段，最终用户输入的SQL片段与我们DAO中写的SQL语句合成一个完整的SQL语句！例如用户在登录时输入的用户名和密码都是为SQL语句的片段！

2　演示SQL攻击

首先我们需要创建一张用户表，用来存储用户的信息。

CREATE TABLE user(

uid CHAR(32) PRIMARY KEY,

username VARCHAR(30) UNIQUE KEY NOT NULL,

PASSWORD VARCHAR(30)

);

INSERT INTO user VALUES('U_1001', 'zs', 'zs');

SELECT * FROM user;

现在用户表中只有一行记录，就是zs。

下面我们写一个login()方法！

public void login(String username, String password) {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

con = JdbcUtils.getConnection();

stmt = con.createStatement();

String sql = "SELECT * FROM user WHERE " +

"username='" + username +

"' and password='" + password + "'";

rs = stmt.executeQuery(sql);

if(rs.next()) {

System.out.println("欢迎" + rs.getString("username"));

} else {

System.out.println("用户名或密码错误！");

}

} catch (Exception e) {

throw new RuntimeException(e);

} finally {

JdbcUtils.close(con, stmt, rs);

}

下面是调用这个方法的代码：

这行当前会使我们登录成功！因为是输入的用户名和密码是SQL语句片段，最终与我们的login()方法中的SQL语句组合在一起！我们来看看组合在一起的SQL语句：

SELECT * FROM tab_user WHERE username='a' or 'a'='a' and password='a' or 'a'='a'

3　防止SQL攻击

l 过滤用户输入的数据中是否包含非法字符；

l 分步交验！先使用用户名来查询用户，如果查找到了，再比较密码；

l 使用PreparedStatement。

4　PreparedStatement是什么？

PreparedStatement叫预编译声明！

PreparedStatement是Statement的子接口，你可以使用PreparedStatement来替换Statement。

PreparedStatement的好处：

l 防止SQL攻击；

l 提高代码的可读性，以可维护性；

l 提高效率。

5　PreparedStatement的使用(可以重复使用，但需要清空参数clearParameters…)

l 使用Connection的preparedStatement(String sql)：即创建它时就让它与一条SQL模板绑定；

l 调用PreparedStatement的setXXX()系列方法为问号设置值

l 调用executeUpdate()或executeQuery()方法，但要注意，调用没有参数的方法；

String sql = “select * from tab_student where s_number=?”;

PreparedStatement pstmt = con.prepareStatement(sql);

pstmt.setString(1, “S_1001”);

ResultSet rs = pstmt.executeQuery();

rs.close();

pstmt.clearParameters();[崔3]

pstmt.setString(1, “S_1002”);

rs = pstmt.executeQuery();

在使用Connection创建PreparedStatement对象时需要给出一个SQL模板，所谓SQL模板就是有“?”的SQL语句，其中“?”就是参数。

在得到PreparedStatement对象后，调用它的setXXX()方法为“?”赋值，这样就可以得到把模板变成一条完整的SQL语句，然后再调用PreparedStatement对象的executeQuery()方法获取ResultSet对象。

注意PreparedStatement对象独有的executeQuery()方法是没有参数的，而Statement的executeQuery()是需要参数（SQL语句）的。因为在创建PreparedStatement对象时已经让它与一条SQL模板绑定在一起了，所以在调用它的executeQuery()和executeUpdate()方法时就不再需要参数了。

PreparedStatement最大的好处就是在于重复使用同一模板，给予其不同的参数来重复的使用它。这才是真正提高效率的原因。

所以，建议大家在今后的开发中，无论什么情况，都去需要PreparedStatement，而不是使用Statement。

Mysql默认是关闭的，所以：url=jdbc:mysql://localhost:3306/test?useServerPrepStmts=true&cachePrepStmts=true

2、时间类型(记得用这个知识点,会查)

数据库类型与java中类型的对应关系：

DATE à java.sql.Date

TIME à java.sql.Time

TIMESTAMP à java.sql.Timestamp

l 领域对象（domain）中的所有属性不能出现java.sql包下的东西！即不能使用java.sql.Date；

l ResultSet#getDate()返回的是java.sql.Date()

l PreparedStatement#setDate(int, Date)，其中第二个参数也是java.sql.Date

时间类型的转换：

l java.util.Date à java.sql.Date、Time、Timestamp

把util的Date转换成毫秒值
使用毫秒值创建sql的Date、Time、Timestamp

l java.sql.Date、Time、Timestamp à java.util.Date

这一步不需要处理了：因为java.sql.Date是java.util.Date；

java.util.Date date = new java.util.Date();

long l = date.getTime();

java.sql.Date sqlDate = new java.sql.Date(l);

1　Java中的时间类型

java.sql包下给出三个与数据库相关的日期时间类型，分别是：

l Date：表示日期，只有年月日，没有时分秒。会丢失时间；

l Time：表示时间，只有时分秒，没有年月日。会丢失日期；

l Timestamp：表示时间戳，有年月日时分秒，以及毫秒。

这三个类都是java.util.Date的子类。

2　时间类型相互转换

把数据库的三种时间类型赋给java.util.Date，基本不用转换，因为这是把子类对象给父类的引用，不需要转换。

java.sql.Date date = …

java.util.Date d = date;

java.sql.Time time = …

java.util.Date d = time;

java.sql.Timestamp timestamp = …

java.util.Date d = timestamp;

当需要把java.util.Date转换成数据库的三种时间类型时，这就不能直接赋值了，这需要使用数据库三种时间类型的构造器。java.sql包下的Date、Time、TimeStamp三个类的构造器都需要一个long类型的参数，表示毫秒值。创建这三个类型的对象，只需要有毫秒值即可。我们知道java.util.Date有getTime()方法可以获取毫秒值，那么这个转换也就不是什么问题了。

java.utl.Date d = new java.util.Date();

java.sql.Date date = new java.sql.Date(d.getTime());//会丢失时分秒

Time time = new Time(d.getTime());//会丢失年月日

Timestamp timestamp = new Timestamp(d.getTime());

3　代码

我们来创建一个dt表：

CREATE TABLE dt(

d DATE,

t TIME,

ts TIMESTAMP

)

下面是向dt表中插入数据的代码：

@Test

public void fun1() throws SQLException {

Connection con = JdbcUtils.getConnection();

String sql = "insert into dt value(?,?,?)";

PreparedStatement pstmt = con.prepareStatement(sql);

java.util.Date d = new java.util.Date();

pstmt.setDate(1, new java.sql.Date(d.getTime()));

pstmt.setTime(2, new Time(d.getTime()));

pstmt.setTimestamp(3, new Timestamp(d.getTime()));

pstmt.executeUpdate();

}

下面是从dt表中查询数据的代码：

@Test

public void fun2() throws SQLException {

Connection con = JdbcUtils.getConnection();

String sql = "select * from dt";

PreparedStatement pstmt = con.prepareStatement(sql);

ResultSet rs = pstmt.executeQuery();

rs.next();

java.util.Date d1 = rs.getDate(1);

java.util.Date d2 = rs.getTime(2);

java.util.Date d3 = rs.getTimestamp(3);

System.out.println(d1);

System.out.println(d2);

System.out.println(d3);

}

大数据（保存MP3等）

目标：把mp3保存到数据库中！

在my.ini中添加如下配置！

max_allowed_packet=10485760

（注意：设置完要重新启动mysql服务）!!!!!!!!!!!!!

1　什么是大数据（blob）【pstmt.setBinaryStream（…）】

所谓大数据，就是大的字节数据，或大的字符数据。标准SQL中提供了如下类型来保存大数据类型：

类型	长度
Tinyblob（字节）	2^8--1B（256B）
blob	2¹⁶-1B（64K）
mediumblob	2²⁴-1B（16M）
longblob	2³²-1B（4G）
Tinyclob（字符）	2^8--1B（256B）
clob	2¹⁶-1B（64K）
mediumclob	2²⁴-1B（16M）
longclob	2³²-1B（4G）

但是，在mysql中没有提供tinyclob、clob、mediumclob、longclob四种类型，而是使用如下四种类型来处理文本大数据：

类型	长度
tinytext	2^8--1B（256B）
text	2¹⁶-1B（64K）
mediumtext	2²⁴-1B（16M）
longtext	2³²-1B（4G）

首先我们需要创建一张表，表中要有一个mediumblob（16M）类型的字段。

CREATE TABLE tab_bin(

id INT PRIMARY KEY AUTO_INCREMENT,

filename VARCHAR(100),

data MEDIUMBLOB

);

　　向数据库插入二进制数据需要使用PreparedStatement为原setBinaryStream(int, InputSteam)方法来完成。

con = JdbcUtils.getConnection();

String sql = "insert into tab_bin(filename,data) values(?, ?)";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, "a.jpg");

InputStream in = new FileInputStream("f:\a.jpg");[崔5]

pstmt.setBinaryStream(2, in);[崔6]

pstmt.executeUpdate();

读取二进制数据，需要在查询后使用ResultSet类的getBinaryStream()方法来获取输入流对象。也就是说，PreparedStatement有setXXX()，那么ResultSet就有getXXX()。

con = JdbcUtils.getConnection();

String sql = "select filename,data from tab_bin where id=?";

pstmt = con.prepareStatement(sql);

pstmt.setInt(1, 1);

rs = pstmt.executeQuery();

rs.next();

String filename = rs.getString("filename");

OutputStream out = new FileOutputStream("F:\" + filename)[崔7] ;

InputStream in = rs.getBinaryStream("data")[崔8] ;

IOUtils.copy(in, out)[崔9] ;

out.close();

　　还有一种方法，就是把要存储的数据包装成Blob类型，然后调用PreparedStatement的setBlob()方法来设置数据

con = JdbcUtils.getConnection();

String sql = "insert into tab_bin(filename,data) values(?, ?)";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, "a.jpg");

File file = new File("f:\a.jpg");

byte[] datas = FileUtils.getBytes(file);//获取文件中的数据

Blob blob = new SerialBlob(datas);//创建Blob对象

pstmt.setBlob(2, blob);//设置Blob类型的参数

pstmt.executeUpdate();

con = JdbcUtils.getConnection();

String sql = "select filename,data from tab_bin where id=?";

pstmt = con.prepareStatement(sql);

pstmt.setInt(1, 1);

rs = pstmt.executeQuery();

rs.next();

String filename = rs.getString("filename");

File file = new File("F:\" + filename) ;

Blob blob = rs.getBlob("data");

byte[] datas = blob.getBytes(0, (int)file.length());

FileUtils.writeByteArrayToFile(file, datas);

批处理(针对增删改，没有查)

1　Statement批处理

批处理就是一批一批的处理，而不是一个一个的处理！

当你有10条SQL语句要执行时，一次向服务器发送一条SQL语句，这么做效率上很差！处理的方案是使用批处理，即一次向服务器发送多条SQL语句，然后由服务器一次性处理。

批处理只针对更新（增、删、改）语句，批处理没有查询什么事儿！

可以多次调用Statement类的addBatch(String sql)方法，把需要执行的所有SQL语句添加到一个“批”中，然后调用Statement类的executeBatch()方法来执行当前“批”中的语句。

l void addBatch(String sql)：添加一条语句到“批”中；

l int[] executeBatch()：执行“批”中所有语句。返回值表示每条语句所影响的行数据；

l void clearBatch()：清空“批”中的所有语句。

for(int i = 0; i < 10; i++) {

String number = "S_10" + i;

String name = "stu" + i;

int age = 20 + i;

String gender = i % 2 == 0 ? "male" : "female";

String sql = "insert into stu values('" + number + "', '" + name + "', " + age + ", '" + gender + "')";

stmt .addBatch(sql);

}

stmt.executeBatch();

当执行了“批”之后，“批”中的SQL语句就会被清空！也就是说，连续两次调用executeBatch()相当于调用一次！因为第二次调用时，“批”中已经没有SQL语句了。

还可以在执行“批”之前，调用Statement的clearBatch()方法来清空“批”！

2　PreparedStatement批处理（给？赋值）

PreparedStatement的批处理有所不同，因为每个PreparedStatement对象都绑定一条SQL模板。所以向PreparedStatement中添加的不是SQL语句，而是给“?”赋值。

con = JdbcUtils.getConnection();

String sql = "insert into stu values(?,?,?,?)";

pstmt = con.prepareStatement(sql);

for(int i = 0; i < 10; i++) {

pstmt.setString(1, "S_10" + i);

pstmt.setString(2, "stu" + i);

pstmt.setInt(3, 20 + i);

pstmt.setString(4, i % 2 == 0 ? "male" : "female");

pstmt.addBatch() ;（没有参数）

}

pstmt.executeBatch ();