Learn JDBC-02

前言

上面我们已经说完了如何对JDBC中的增删改查的操作进行封装。其中最为复杂的就是查询的操作。因为我们需要考虑返回值是什么东西，其中我们使用两种类型的返回值。一种是使用JAVA Bean对象作为返回值，此时我们需要根据表中的信息建立相对应的方式，然后再调用函数的时候传入Class对象。在函数中根据查询到的信息，然后使用反射的反思创建的对象，第二种方式是使用Map的方式，这种方式我们不需要根据表创建对应的什么东西，我们只需要将查询到的东西直接以键值对的形式放入Map中就行了。我个人感觉这种方式似乎是更不错的。

Java与SQL中的类型

前面我们只接触到了SQL中的两种数据类型，一种是int，另一种是varchar(n)。这两种类型毫无疑问的是对应Java中的int和String类型的数据。但是SQL中还有其他的不少类型的数据，他们与Java中的类型的对应关系也是需要我们来掌握的，不然都不知道直接应该插入什么样的类型的数据了。

Java类型	SQL类型
boolean	BIT
byte	TINYINT
int	INTEGER
long	BIGINT
String	CHAR, VARCHAR, LONGVARCHAR
byte array	BINARY, VARBINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP

上面就是Java和SQL中的一些常用的类型的对应关系。这也是非常容易记的，不过需要注意的是后面的三个东西。他们是java.sql这个下面的而不是java.util下的。

不过也不是每一个SQL语言都有以上的内容的，这就是SQLtmd操蛋的地方，没有统一的标准。其中MySQL中有一个类型是DATETIME。DATETIME和TIMESTAMP是有有一定的区别的，TIMESTAMP只占用4个字节，表示的时间范围比较少。而DATETIME占用了八个字节，表示的范围很大。不过他们两个之间更大的一个区别是TIMESTAMP和时区有关，但是DATETIME和时区是无关的。

这里就不得不说如果我们需要插入一个时间的时候该怎么做了，我们只需要按照特定的时间的格式就可以了。在MySQL中的格式是%Y-%m-%d %H:%i:%s其中这是使用的24小时制。如果需要使用使用12小时制的话，需要使用的是%h并且加上代表am pm的%p。示例如下。

select date_format(ctime, '%Y-%m-%d %H:%i:%s') from datetest;

select date_format(ctime, '%Y-%m-%d %h:%i:%s %p') from datetest; 

MySQL中没有to_char函数，这是真的头疼，数据库为什么不能同一呢？

如果是使用的Oracle，格式是yyyy-mm-dd HH24:mi:ss，如果是返回12小时制的就将上面的24去掉就行了。

select to_char(ctime, 'yyyy-mm-dd HH24:mi:ss') from datetest;

select to_char(ctime, 'yyyy-mm-dd HH:mi:ss am/pm') from datetest;

下面的那个十二小时制后面的那个am/pm的写法我也不确实是否是正确的，毕竟我现在只有MySQL的环境。其实上面的那个两个相比较的话，我是更喜欢下面的这种方式的，因为Java中使用的也是差不多这样的方式。yyyy-MM-dd hh:mm:ss是Java中日期常用的格式。上面的oracle之所以使用mi就是因为SQL语言是不区别大小写的，所谓无法区别month和minute。说起Java中日期的格式我们就在这儿另外插入一个话题。Java中的字符串和日期的格式转换。

public static void main(String[] args) {
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss a");
    Date date = new Date();
    String format = dateFormat.format(date);
    System.out.println(format);
    // 2019-10-23 07:12:07 PM
}

也可以使用二十四小时的格式

public static void main(String[] args) {
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    Date date = new Date();
    String format = dateFormat.format(date);
    System.out.println(format);
    // 2019-10-23 19:13:13
}

@Test
public void test() {
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    try {
        Date date = dateFormat.parse("1111-11-11 11:11:11");
        System.out.println(date);
    } catch (ParseException e) {
        e.printStackTrace();
    }
}

使用SimpleDateFormat同样也可以将字符串转为日期(java.util.date)，需要注意的是，格式一定要对应，不然是会抛出异常的。

上面有点儿说多了，现在我们回到JDBC中的话题来，是否还有其他的类型数据被我们遗忘了。

操作BLOB类型数据

除了上面的这些类型的数据之外，还有一个非常重要的数据类型，那就是——BLOB。MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。 BLOB又分为一下的四种，不过除了大小之外，他们都是一样的。

类型	大小
TINYBLOB	255B
BLOB	65KB
MEDIUMBLOB	16M
LONGBLOB	4G

这个BLOB可以是一张图片，也可以一个视频，可以是任何的一个文件。我们需要根据插入的类型的大小确定上面的对应的BOLB 的类型。不过需要说明的，如果在数据库中插入过多或者过大数据的时候，数据库的性能将会受到较大的影响。

如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如 下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。

这种BLOB类型的数据是无法想之前的那样值算个格式就可以处理的了，那么我们该如何来处理这个BLOB类型的数据呢？是使用Statement还是使用PreparedStatement呢？其实傻子应该都可以看出来是肯定不可能是使用这个垃圾的Statement的，加入说我们想要上传一个图片进入一个数据中的时候，我们总不能使用数据库指令拼接的形式吧。所以说，如果我们想要插入或者是读取BOLB类型的话，我们是只可以使用PreparedStatement这种形式的。不过问题在于，如何使用。类比一下我们处理文件的时候，或者说是通过网络来获取或者是发送信息的时候，我们使用的工具是什么东西呢？就是流，我们使用InputStream来读取流的信息，使用OutputStream来发送流的信息。通过PreparedStatement中的setBlob的方式，我们可以传入一个文件流，(比如说是一张图片)，我们就可以将这个图片写入到数据库当中去了。不过使用setObject的方式也是非常的正确的方式。

@Test
public void test1() {
    BufferedInputStream bis = null;
    Connection conn = null;
    try {
        bis = new BufferedInputStream(new FileInputStream(new File("kawali.jpg")));
        conn = JDBCUtils.getConnection();
        JDBCUtils.update(conn, "insert into blobtest values(?)", bis);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, null, null);
        if (bis != null) {
            try {
                bis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

通过这种方式，我们就可以将一张可爱的图片写入到我们的数据库当中去。使用的方式就是之前我们已经说过的各种我们都已经封装好了的方法。不过此时如果我们需要读取的话，可就不是那么容易的了。

@Test
public void test2() {
    Connection conn = null;
    BufferedOutputStream bos = null;
    try {
        conn = JDBCUtils.getConnection();
        byte[] cblob = (byte[]) JDBCUtils.getValue(conn, "select cblob from blobtest");
        assert cblob != null;
        InputStream binaryStream = new ByteArrayInputStream(cblob);
        bos = new BufferedOutputStream(new FileOutputStream("kawali-sql.jpg"));
        byte[] buff = new byte[1024];
        int len = -1;
        while ((len = binaryStream.read(buff)) != -1) {
            bos.write(buff, 0, len);
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, null, null);
        if (bos != null) {
            try {
                bos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

一开始我将getValue()的返回值转化成为了java.sql.Blob类型，但是发生了异常，看来不是使用getBlob方式似乎是不可以转化成为Blob类型的。如果可以转化成为Blob类型的话，我们可以使用getBinaryStream的方式获取InputSteram。然后通过这个InputStream创建一个OutputStream，然后将图片写入到文件当中去。不过即使不可以使用Blob的方式，我们可以将返回值转化成为一个byte[]的数组，然后通过ByteArrayInputStream的方式返回一个InputStream，后面的步骤就都是一致的了。

需要注意的是上面我使用的函数式getValue，这个纯属是一个意外，按道理来说我们使用使用getInstance或者是getMapInstance函数，不过这里我只有一个返回值，使用getValue完全就是图个方便而已。

批量插入数据

上面我们研究完了如何使用PreparedStatement来读取或者是插入一个Blob类型的数据，而这种事情正是Statement做不到的事情，事实上尽管很多Statement能够做到的事情，我们也不会使用这个垃圾的Statement来做的。比如下面我们要说的这个批量插入的问题。

在SQL中很多的操作都是天生就是批量的，比如说删除，查找，和修改。如果我们不对此加以限制的话，我们会修改数据中的所以的数据的信息。不过唯独有一个语句是例外，那就是插入语句。我们在数据库中只能使用insert语句，一条数据一条数据的进行插入，这非常的难受。不过现在有了Java，我们可以使用for循环来帮助我们进行插入数据了。不过我们应该使用什么样的方式呢？什么样的方式是最好的呢？

加入我们要想数据库中插入十万条数据。

先来考虑一下使用Statement的方式，emm，，这中方式真的需要考虑吗？别说插入的性能，光是拼接产生的sql语句的字符串都有上万条了吧。所以说这种方法是考虑都不用考虑的。

那么我们就只好使用PreparedStatement的方式了。

第一种方式

@Test
public void test3() {
    Connection conn = null;
    try {
        conn = JDBCUtils.getConnection();
        String sql = "insert into inserttest values (?, ?)";
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            JDBCUtils.update(conn, sql, i, "stud_" + i);
        }
        long end = System.currentTimeMillis();
        System.out.println("Time: " + (end - start) + "ms");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, null, null);
    }
}

Time: 8097ms，插入一万条的数据只是花了八千多。select count(*) from inserttest;可以查询表中数据的数量。(也可以使用sqlyog，一种SQL的可视化的工具直接看)。

如果我们使用的是Statement，我敢保证一分钟之内是不可能完成了。现在我们来分析一下，为什么这种方式快。我们之前也说过了PreparedStatement是会经过预编译的。当我们多次使用的时候，不过多次编译。所以效率非常的快。

但是注意到，就像我们写入文件的的时候，是一个字节一个字节的方式写入文件的，而这里我们也是一次一次的进行插入的。此时我们多次和数据库进行互动，进行了一万次的提交。大大的降低了效率。于是写如文件的时候，我们使用了一个缓存数组，那么这里我们是否有相似的缓存的机制呢？

第二种方式

jdbc中确实有这种东西，就是Batch(批处理)。

@Test
public void test4() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = JDBCUtils.getConnection();
        String sql = "insert into inserttest values (?, ?)";
        ps = conn.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 1; i <= 10000; i++) {
            ps.setInt(1, i);
            ps.setString(2, "stud_" + i);
            ps.addBatch();

            if (i % 500 == 0) {
                ps.executeBatch();

                ps.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("Time: " + (end - start) + "ms");

    } catch (Exception e) {
        e.printStackTrace();
    }
}

我们使用addBatch方法将语句放入批处理中，当满足一定的条件的时候，比如上面是，有五百个的时候，就提交一次。使用executeBatch的方法可以提交之前加入到批处理中的数据。不过别忘了，提交玩了之后，我们需要清空批处理中的数据，需要调用clearBatch方法。

这个方法运行的时间是Time: 7904ms,似乎是提升的不是很大。。。emm，看来这个方法并不是非常的管用。其实不是这样的，我们写入文件的时候使用缓存区，效率是之前的好多好多倍。这个时候之所以没有提升是因为MySQL默认是不开启批处理的功能的，我们需要在连接的时候加上参数rewriteBatchedStatements=true，这个也就是在我们的jdbc.properties文件中进行修改，不需要改动之前的源码，这就是使用资源文件的好处。

配置文件如下。

mysqlUser=root
mysqlPasswd=root
mysqlUrl=jdbc:mysql:///sher?serverTimezone=UTC&rewriteBatchedStatements=true
mysqlDriverClass=com.mysql.cj.jdbc.Driver

此时，时间是Time: 130ms，这个就不用多说了吧。效率是刚刚的。

如果我们插入一百万条数据的话，时间就是Time: 5770ms。如果是不使用批处理的话，这个时间是不敢想象的。不过这个一百万的速度还不是最快的，这还不是最好的方法。

第三种方式

@Test
@SuppressWarnings("all")
public void test5() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = JDBCUtils.getConnection();
        conn.setAutoCommit(false);
        String sql = "insert into inserttest values (?, ?)";
        ps = conn.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 1; i <= 1000000; i++) {
            ps.setInt(1, i);
            ps.setString(2, "stud_" + i);
            ps.addBatch();

            if (i % 500 == 0) {
                ps.executeBatch();

                ps.clearBatch();
            }
        }
        conn.commit();
        long end = System.currentTimeMillis();
        System.out.println("Time: " + (end - start) + "ms");

    } catch (Exception e) {
        e.printStackTrace();
    }
}

上面的代码相比与之前只是增加了第8行和第23行的代码。conn.setAutoCommit(false)是关闭Connection的自动提交事务。我们可以关闭这个东西，然后在最后在提交这个事务。

运行的时间是Time: 4525ms，提高的似乎不是很高。。。又是我操作的事务么？ :)

至于上面的commit的详细的用法，这就要到之后再说了。

总结

上面我们主要就写了三个方面的知识，第一个是Java和SQL中数据之间的对应关系。第二个就是我们如何插入和读取Blob的数据。第三个就是如何更加高效的进行数据的批量的提交。后面我们将要讨论数据库事务和隔离的各种各样的问题了。