[大数据]JDBC

之前我们学习了JavaSE，编写了Java程序，数据保存在变量、数组、集合等中，无法持久化，后来学习了IO流可以将数据写入文件，但不方便管理数据以及维护数据的关系；

后来我们学习了数据库管理软件MySQL，可以方便的管理数据。

那么如何将它俩结合起来呢？即Java程序 ：负责数据的输入，业务的处理，数据的显示<==>MySQL：负责数据的存储和管理。

那么就可以使用JDBC技术。

一、JDBC概述

JDBC ：Java? Database? Connectivity

? ? ? ? ? =? JDK核心类库中的一套API（接口+部分工具类）+数据库厂商提供的驱动jar

Java程序不仅仅能够连接MySQL数据库，可以连接很多数据库（Oracle，SQL Server，DB2，...）。

这就有一个问题？

?数据库不同，它们的操作方式会有所不同，因为它们的底层实现方式，实现的语言等都是不同的。

?? ?那么Java去连接不同的数据库时，就会有不同的API。这样的话，就会导致：

?? ?（1）程序员的学习成本增加

? ?（2）如果发生数据库迁移，Java代码就需要“重写”

?? ?如果是这样的话，就非常麻烦，可移植性、可维护性等非常差。

SUN公司（现在Oracle）就说，必须统一一套API，可以操作各种数据库。但是SUN公司又不同知道所有数据库内部是如何实现的，

也无法要求所有的数据库厂商按照统一的标准来开发他们的数据库软件。

SUN公司（现在Oracle）就设计了一套接口 + 部分类。然后各个数据库厂商，来提供这些接口的实现类。

==>Java程序中面向接口编程，在程序运行时，又需要引入这些接口的实现类，这些实现类就是数据库驱动jar。

?二、JDBC使用步骤

1、引入mysql驱动jar

（1）在项目路径下建一个文件夹“jdbclibs”，把mysql的驱动jar放到里面
mysql-connector-java-5.1.36-bin.jar
（2）项目设置-->libraries--> + ->java-->文件夹“jdbclibs”
（3）选择需要这个jar的模块
项目设置-->modules-->模块名-->dependencies--> + - >library->Java -> 库

2、在内存中加载驱动类

? ? Class.forName("com.mysql.jdbc.Driver");? ?//新版的

? ? Class.forName("org.git.mm.mysql.Driver");? ?//老版的

3、连接数据库

?此时的Java程序是MySQL的一个客户端

?连接数据库：mysql服务器主机的IP地址、端口号、用户名、密码

String? url = "jdbc:mysql://localhost:3307/test";

Connection conn = DriverManager.getConnection(url,"root","123456");

4、操作数据库? Statement接口

int len = statement.executeUpdate("insert into t_department values(null,'测试'，'xxx')"); 执行一条sql

5、释放资源（close）

public class TestJdbcUse {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

//      Class.forName("org.gjt.mm.mysql.Driver");//老版的
        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3307/test";
        Connection conn = DriverManager.getConnection(url,"root","123456");
        Connection是接口，DriverManager.getConnection(...)返回了接口的实现类对象
        System.out.println(conn);

        Statement statement = conn.createStatement();
        //Statement是接口，conn.createStatement()返回的是接口的实现类对象
        System.out.println(statement);

        int len = statement.executeUpdate("insert into t_department values(null,'测试','xxx')");
        System.out.println("len =" + len);

        statement.close();
        conn.close();
        
    }
}

三、Statement接口实现增删改查

相关的API：

?1、DriverManager: 驱动管理类

2、 Connection：代表数据库连接

3、? Statement和reparedStatement:用来执行sql

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 执行增、删、改：int execyteUpate()

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 执行查询：ResultSet executeQuery()

4、如何遍历ResultSet

?（1）boolean? next() ：判断是否还有下一行

?（2）getString(字段名或序号)，getInt(字段名或序号),getObject(字段名或序号）

//添加
public class TestInsert {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test", "root", "123456");
        Statement statement = connection.createStatement();
        int len = statement.executeUpdate("insert into t_department values(null,'测试2','aaa')");
        System.out.println(len>0?"添加成功":"添加失败");
        statement.close();
        connection.close();
    }
}

//修改
public class TestUpdate {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test", "root", "123456");
        Statement statement = connection.createStatement();
        int len = statement.executeUpdate("update t_department set description='负责测试工作' where did=6");//sql中不用加;
        System.out.println(len>0?"修改成功":"修改失败");
        statement.close();
        connection.close();
    }

//删除
public class TestDelete {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test", "root", "123456");
        Statement statement = connection.createStatement();
        int len = statement.executeUpdate("delete from t_department where did=6");//sql中不用加;
        System.out.println(len>0?"删除成功":"删除失败");
        statement.close();
        connection.close();
    }
}

//查询
public class TestSelect {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test", "root", "123456");
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from t_department");//执行查询的sql，调用的方法不同，executeQuery
        /*
        ResultSet是接口，=右边返回的是它的一个实现类对象。
        ResultSet接口有一些方法，可以遍历结果集。
        （1）boolean next()判断是否还有下一行记录
        （2）xxx  getXxx（）通过调用不同的get方法，获取这一行的每一个单元格（字段）的值
         */
        while(resultSet.next()){  //像是是Iterator迭代器的hasNext()，判断是否还有下一行记录
            int did = resultSet.getInt("did");
            String dname = resultSet.getString("dname");
            String description = resultSet.getString("description");
            System.out.println(did+"\t" + dname+"\t" +description);
        }

        resultSet.close();
        statement.close();
        connection.close();
    }
}

sql拼接问题，PreparedStatement代替Statement，避免了sql拼接

sql注入问题，PreparedStatement代替Statement，避免了sql注入

blob类型存储图片

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(50) NOT NULL,
  `photo` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


(1)用PreparedStatement代表Statement添加图片等Blob类型的数据。
(2)当图片比较大时，报错误
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'photo' at row 1

每一种blob有各自大小限制：
tinyblob:255字节、blob:65k、mediumblob:16M、longblob:4G

修改表结构：ALTER TABLE t_user MODIFY photo MEDIUMBLOB;

(3)mysql服务器端，一次接收的数据包的大小限制问题
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (2743838 > 2097152). You can change this value on the server by setting the max_allowed_packet' variable.
my.ini文件中的参数：
max_allowed_packet=2M
 */
public class TestProblem3 {
    @Test
    public void test02()throws Exception{
        Scanner input = new Scanner(System.in);
        System.out.print("请输入用户名：");
        Object username = input.next();

        System.out.print("请输入密码：");
        Object password = input.next();

        System.out.print("请选择照片：");
        String path = input.next();//这里没有图形化界面，只能输入路径，通过IO流读取图片的内容

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test", "root", "123456");
        String sql = "insert into t_user values(null,?,?,?)";//如果这么写拼接的是图片的路径
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setObject(1,username);
        preparedStatement.setObject(2,password);
        preparedStatement.setObject(3, new FileInputStream(path));

        int len = preparedStatement.executeUpdate();
        System.out.println(len>0?"添加成功":"添加失败");
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void test01()throws Exception{
        Scanner input = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String username = input.next();

        System.out.print("请输入密码：");
        String password = input.next();

        System.out.print("请选择照片：");
        String path = input.next();//这里没有图形化界面，只能输入路径，通过IO流读取图片的内容

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test", "root", "123456");
        Statement statement = connection.createStatement();
        String sql = "insert into t_user values(null,'"+username+"','"+password+"',"+path+")";//如果这么写拼接的是图片的路径
       //失败，拼接的方式不行


    }
}

获取自增长键值

/*
PreparedStatement获取自增长的键值。
比如：从键盘输入一个部门的信息，添加到t_department部门表，添加成功后，希望立刻返回部门的编号。
因为部门编号是自增长。

（1）PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
Statement.RETURN_GENERATED_KEYS：常量值的意思是获取/返回自增长键值

（2）执行完sql之后，调用PreparedStatement的getGeneratedKeys()
    ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
    if(generatedKeys.next()){
        System.out.println("新添加的部门的编号：" + generatedKeys.getObject(1));
    }

 */
public class TestGenerateKey {
    @Test
    public void test01()throws Exception{
        Scanner input = new Scanner(System.in);
        System.out.print("请输入部门名称：");
        String dname = input.next();

        System.out.print("请输入部门简介：");
        String desc = input.next();

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test", "root", "123456");
        String sql = "insert into t_department values(null,?,?)";//null表示did自增
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        //在获取PreparedStatement对象时，要指定参数，告诉服务器端，需要获取自增长键值

        preparedStatement.setObject(1,dname);//这个1代表是第1个？
        preparedStatement.setObject(2,desc);//这个2代表是第2个？

        int len = preparedStatement.executeUpdate();
        System.out.println(len>0?"添加成功":"添加失败");

        ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
        if(generatedKeys.next()){
            System.out.println("新添加的部门的编号：" + generatedKeys.getObject(1));
        }

        generatedKeys.close();
        preparedStatement.close();
        connection.close();
    }
}

批处理

批处理：
    为了提高效率，使用批处理，执行一组sql。
    例如：
        电商项目时，用户下订单后，
        （1）修改商品表，修改商品的库存量、销量
        （2）在订单表添加一条记录，表示有新订单
        （3）在订单明细表中添加n条记录
        这组sql可以一起执行。

   演示：在部门表添加1000条记录

（1）在url中增加一个开通批处理功能的参数

jdbc:mysql://localhost:3307/test?rewriteBatchedStatements=true

（2）调用PreparedStatement的方法不同

preparedStatement.addBatch();
preparedStatement.executeBatch();

（3）insert语句values不要写错value

 */
public class TestBatch {
    @Test
    public void test03()throws Exception{
        long start = System.currentTimeMillis();

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test?rewriteBatchedStatements=true", "root", "123456");
        //注意：url中要添加一个参数  ?rewriteBatchedStatements=true  这里?的意思是表示从?开始后面是(key,value)格式参数

        String sql = "insert into t_department values(null,?,?)";//null表示did自增
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        for(int i=1; i<=1000; i++) {
            preparedStatement.setObject(1, "模拟部门名称"+i);//这个1代表是第1个？
            preparedStatement.setObject(2, "模拟部门简介"+i);//这个2代表是第2个？

            preparedStatement.addBatch();//添加到批处理命令中
        }
        preparedStatement.executeBatch(); //一次性添加

        preparedStatement.close();
        connection.close();

        long end = System.currentTimeMillis();
        System.out.println("用时：" + (end-start));//用时：251
    }

        @Test
    public void test04()throws Exception{
        long start = System.currentTimeMillis();

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test?rewriteBatchedStatements=true", "root", "123456");
        //注意：url中要添加一个参数  ?rewriteBatchedStatements=true  这里?的意思是表示从?开始后面是(key,value)格式参数

        String sql = "insert into t_department values(null,?,?)";//values写错value，效率低下
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        for(int i=1; i<=1000; i++) {
            preparedStatement.setObject(1, "模拟部门名称"+i);//这个1代表是第1个？
            preparedStatement.setObject(2, "模拟部门简介"+i);//这个2代表是第2个？

            preparedStatement.addBatch();//添加到批处理命令中
        }
        preparedStatement.executeBatch(); //一次性添加

        preparedStatement.close();
        connection.close();

        long end = System.currentTimeMillis();
        System.out.println("用时：" + (end-start));//用时：1756
    }
    
    @Test
    public void test02()throws Exception{
        long start = System.currentTimeMillis();

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test", "root", "123456");
        String sql = "insert into t_department values(null,?,?)";//null表示did自增
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        for(int i=1; i<=1000; i++) {
            preparedStatement.setObject(1, "模拟部门名称"+i);//这个1代表是第1个？
            preparedStatement.setObject(2, "模拟部门简介"+i);//这个2代表是第2个？

            preparedStatement.addBatch();//添加到批处理命令中
        }
        preparedStatement.executeBatch(); //一次性添加

        preparedStatement.close();
        connection.close();

        long end = System.currentTimeMillis();
        System.out.println("用时：" + (end-start));//用时：1684
    }

    @Test
    public void test01()throws Exception{
        long start = System.currentTimeMillis();

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test", "root", "123456");
        String sql = "insert into t_department values(null,?,?)";//null表示did自增
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        for(int i=1; i<=1000; i++) {
            preparedStatement.setObject(1, "模拟部门名称"+i);//这个1代表是第1个？
            preparedStatement.setObject(2, "模拟部门简介"+i);//这个2代表是第2个？

            preparedStatement.executeUpdate();//不接受返回值，直接添加
        }
        preparedStatement.close();
        connection.close();

        long end = System.currentTimeMillis();
        System.out.println("用时：" + (end-start));//用时：1785
    }
}

事务处理

/*
JDBC的事务处理：
    MySQL默认情况下是自动提交事务。

   例如：
        电商项目时，用户下订单后，
        （1）修改商品表，修改商品的库存量、销量
        （2）在订单表添加一条记录，表示有新订单
        （3）在订单明细表中添加n条记录
        这组sql可以一起执行。

   上面这种sql要么一起成功，要么一起失败。

演示：
	update t_department set description = 'xx' where did = 2;
	update t_department set description = 'yy' where did = 3;

	故意把其中一条sql语句写错。

    update t_department set description = 'xx' where did = 2;
	update t_department set description = 'yy' what did = 3;  #what是错误的

	希望这两天sql要么一起成功，要么一起失败

（1）设置手动提交模式
connection.setAutoCommit(false);//设置手动提交模式

（2）如果sql执行成功，调用commit()方法提交事务
    如果sql执行失败，调用rollback()方法回滚事务

 （3）在关闭连接/还回连接之前，记得 设置自动提交模式
   connection.setAutoCommit(true);
 */
public class TestTransaction {
    @Test
    public void test02()throws Exception{
        //自动提交模式
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test", "root", "123456");

        connection.setAutoCommit(false);//设置手动提交模式

        PreparedStatement p1 = null;
        PreparedStatement p2 = null;
        try {
            String sql1 = "update t_department set description = 'xx' where did = 2";
            String sql2 = "update t_department set description = 'yy' what did = 3";//sql可以没有？

            p1 = connection.prepareStatement(sql1);
            p2 = connection.prepareStatement(sql2);

            p1.executeUpdate();
            p2.executeUpdate();

            System.out.println("两条sql都修改成功");
            connection.commit();//提交事务
        } catch (SQLException e) {
            System.out.println("两条sql都修改失败");
            e.printStackTrace();
            connection.rollback();//回滚事务
        }

        p1.close();
        p2.close();
        connection.setAutoCommit(true);//设置事务自动提交模式
                                    /*
                                    对于本案例来说，这句话加不加没所谓。
                                    但是为了养成习惯，每次在使用完手动提交模式的事务之后，都记得还原自动提交模式，
                                    因为后面的连接是重复使用的，是从数据库连接池中拿的。
                                     */
        connection.close();
    }

    @Test
    public void test01()throws Exception{
        //自动提交模式
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/test", "root", "123456");
        String sql1 = "update t_department set description = 'xx' where did = 2";
        String sql2 = "update t_department set description = 'yy' what did = 3";//sql可以没有？

        PreparedStatement p1 = connection.prepareStatement(sql1);
        PreparedStatement p2 = connection.prepareStatement(sql2);

        int len1 = p1.executeUpdate();
        System.out.println(len1>0?"第一条修改成功":"第一条修改失败");

        int len2 = p2.executeUpdate();
        System.out.println(len1>0?"第二条修改成功":"第二条修改失败");

        p1.close();
        p2.close();
        connection.close();
    }
}

四、数据库连接池

1、什么是数据库连池

连接对象的缓冲区。负责申请，分配管理，释放连接的操作。

2、为什么要使用数据库连接池

（1）不使用数据库连接池，每次都通过DriverManager获取新连接，用完直接抛弃断开，连接的利用率太低，太浪费。

（2）对于数据库服务器来说，压力太大了。我们数据库服务器和Java程序对连接数也无法控制，很容易导致数据库服务器崩溃。

我们就希望能管理连接。

• 我们可以建立一个连接池，这个池中可以容纳一定数量的连接对象，一开始，我们可以先替用户先创建好一些连接对象，等用户要拿连接对象时，就直接从池中拿，不用新建了，这样也可以节省时间。然后用户用完后，放回去，别人可以接着用。

• 可以提高连接的使用率。当池中的现有的连接都用完了，那么连接池可以向服务器申请新的连接放到池中。 直到池中的连接达到“最大连接数”，就不能在申请新的连接了，如果没有拿到连接的用户只能等待。

3、市面上有很多现成的数据库连接池技术

JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口（通常被称为数据源），该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：

DBCP 是Apache提供的数据库连接池，速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持

C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以

Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点

BoneCP 是一个开源组织提供的数据库连接池，速度快

Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池4、如何使用德鲁伊数据库连接池

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3307/test
username=root
password=123456
initialSize=5
maxActive=10
maxWait=1000

/*
1、为什么要使用数据库连接池。
问题1：当连接MySQL服务器的用户从少量增加到很多，大家同时都和MySQL服务器进行通信，都要创建连接。
     这个时候，如果我们不对连接进行控制，就会导致MySQL服务器创建过多的连接，要么使得性能低下，要么使得MySQL服务器崩溃。

    思路：对连接的数量进行控制。

问题2：每次用户与MySQL服务器进行通信时，都是“现”获取连接，那么就比较慢。
      回忆：网络编程
            TCP/IP协议的编程。
            TCP：面向连接的，可靠的，基于字节流的 传输控制协议。它在传输数据之前，都要进行“三次握手”，用完之后“四次挥手”。
      MySQL软件是C/S结构，即基于TCP/IP协议的程序。
      每次用户与MySQL服务器进行通信时，创建新连接时，都要“三次握手”，用完之后“四次挥手”。这样是很耗时间等成本。
      如果每次获取完连接之后，只是执行了某个sql语句，就释放了，那么太浪费资源。

    思路：能不能重复利用连接资源。


数据库连接池：连接对象的缓冲区。负责申请，分配管理，释放连接的操作。

2、如何使用数据库连接池？
市面上有很多现成的数据库连接池技术。
目前咱们讲的是阿里的德鲁伊连接池技术。

JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口。实现类是各种数据库连接池技术组织提供。

连接池又称为数据源。

步骤：
（1）引入jar（和mysql驱动一样操作）
druid-1.1.10.jar
（2）创建连接池对象
（3）从连接池对象中获取链接
Connection connection = 链接池对象.getConnection();
（4）用完链接要还回去，否则连接就会“枯竭”
 */
public class TestPool {
    public static void main(String[] args) throws Exception {
        //(1)创建连接池对象
        Properties properties = new Properties();
        //src下有一个druid.properties文件
        properties.load(TestPool.class.getClassLoader().getResourceAsStream("druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(properties);

        //（2）从链接池中获取链接对象
//        Connection connection = ds.getConnection();
//        System.out.println("connection = " + connection);

        for (int i = 1; i <= 15; i++) {
            new Thread() {
                public void run() {
                    try {
                        Connection connection = ds.getConnection();
                        System.out.println("connection = " + connection);

                        Thread.sleep(1000);

                        connection.close();//这里close不是断开链接，而是放回连接池
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }.start();

        }
    }
}