java学习笔记14——JDBC深入

最新推荐文章于 2023-10-03 23:29:28 发布

SongQijie

最新推荐文章于 2023-10-03 23:29:28 发布

阅读量605

点赞数

分类专栏： java 文章标签： java jdbc sql

本文链接：https://blog.csdn.net/u010935199/article/details/70241131

版权

java 专栏收录该内容

15 篇文章 2 订阅

订阅专栏

上一章讲了java最基础的jdbc的操作，这里加深一下。

一、statement和preparedstatement的区别

在jdbc中，可以使用statement和preparedstatement两种方式执行sql语句，区别在于：
statement如果要执行两条sql语句，会生成两个执行计划，而preparedstatement可以通过下面的方式进行插入，请看代码：

public class JDBCDemo {

  public static Connection conn(String username, String password)
      throws Exception {
    String DRIVER = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://127.0.0.1:3306/demo";
    Class.forName(DRIVER);
    Connection connection =(Connection)DriverManager.getConnection(url,username, password);
    return connection;
  }

  public static void main(String[] args) {
    String username = "root";
    String password = "root";
    String sql = "insert into tb_user values (?,?,?)";
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
      connection = conn(username, password);
      preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
      preparedStatement.setInt(1, 1);
      preparedStatement.setString(2, "hello");
      preparedStatement.setString(3, "java");
      preparedStatement.execute();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        preparedStatement.close();
        connection.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
}

结果：
这里写图片描述
不同于statement，preparedstatement在插入语句中，使用？（问号）来代替参数位置，然后再后面使用

preparedStatement.setInt(1, 1);
preparedStatement.setString(2, "hello");
preparedStatement.setString(3, "java");

对参数进行赋值，避免了在statement中，拼接sql语句的现象，那么这样做的好处是什么呢？
1. preparedStatement对sql语句进行了预编译，就是说，还没有赋值的时候就已经就绪了，当对sql进行赋值时，sql就会执行，这样的话，其实只产生一个执行计划。而statement的话，n条sql语句就会产生n个执行计划，在需要执行多次sql的时候，preparedStatement可以有效提升执行效率
2. 防止sql注入，statement是生成sql，然后立即执行，在这里需要注意，加入在查询的时候，有如下语句：
String sql =”select * from tb_user where id=’”+id+”’”;
正常状态下只要输入i参数d=1就好了，但是，假设输入的是id=“1 and user=’hello’”，如果这样，那么本来的限制条件只有id，这里又多了user，而且sql会正常执行，这样就会产生数据的安全问题。而preparedStatement有效避免了这个问题，因为它的sql是提前预编译的，通过添加参数的方式进行赋值，有效避免了上述的安全问题。
sql注入是经常在面试的时候会被问到，大家可以多了解了解

二、批量插入

批量插入其实有三种，请看下面的代码：

Statement直接插入
代码：

public class State {

  public static void main(String[] args) {
    Connection connection = null;
    Statement statement = null;
    long begin = System.currentTimeMillis();
    try {
      String sql = "insert into tb_user values (1,'hello','java')";
      connection = JDBCDemo.conn("root", "root");
      connection.setAutoCommit(false);
      statement = (Statement) connection.createStatement();
      for (int i = 0; i < 100000; i++) {
        statement.execute(sql);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        statement.close();
        connection.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    long end = System.currentTimeMillis();
    System.out.println("时间：" + (end - begin) / 1000 + "s");
  }
}

结果：
这里写图片描述

2、Statement和addBatch插入
代码：
**

public class StateBatch {
  public static void main(String[] args) {
    Connection connection = null;
    Statement statement = null;
    long begin = System.currentTimeMillis();
    try {
      String sql = "insert into tb_user values (1,'hello','java')";
      connection = JDBCDemo.conn("root", "root");
      connection.setAutoCommit(false);
      statement = (Statement) connection.createStatement();
      for (int i = 0; i < 100000; i++) {
        statement.addBatch(sql);
 connection.close();
      }
      System.out.println("100000条添加完毕，开始插入");
      statement.executeBatch();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        statement.close();
        connection.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    long end = System.currentTimeMillis();
    System.out.println("时间：" + (end - begin) / 1000 + "s");
  }
}

**
结果：
这里写图片描述

3、preparedStatement和addBatch插入
代码：

public class PreBatch {

  public static void main(String[] args) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    long begin = System.currentTimeMillis();
    try {
      String sql = "insert into tb_user values (?,?,?)";
      connection = JDBCDemo.conn("root", "root");
      connection.setAutoCommit(false);
      preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
      for (int i = 0; i < 100000; i++) {
        preparedStatement.setInt(1, 1);
        preparedStatement.setString(2, "hello");
        preparedStatement.setString(3, "java");
        preparedStatement.addBatch();
      }
      System.out.println("100000条预编译完毕，开始插入");
      preparedStatement.executeBatch();
 connection.close();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        preparedStatement.close();
        connection.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    long end = System.currentTimeMillis();
    System.out.println("时间：" + (end - begin) / 1000 + "s");
  }
}

结果：
这里写图片描述

以上三种方法都可以进行批量的插入，可以发现，其实10w条数据在插入上所用的时间并没有太大的区别，并没有在性能上有所提升。
这里需要注意，在执行批量插入的时候最主要的是将自动提交取消，这样不管是否用JDBC的batch语法应该都没有关系。

 connection.setAutoCommit(false);

三、线程池

数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
JDBC数据库连接池的实现必须实现javax.sql.DataSource接口，其中DataSource称为数据源，该数据源的实现有以下三种：

C3p0：C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。c3p0是异步操作的，缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能。目前使用它的开源项目有hibernate，spring等。c3p0有自动回收空闲连接功能。
DBCP：是Apache上的一个 Java连接池项目，也是 tomcat使用的连接池组件。单独使用dbcp需要3个包：common-dbcp.jar,common-pool.jar,common-collections.jar由于建立数据库连接是一个非常耗时耗资源的行为，所以通过连接池预先同数据库建立一些连接，放在内存中，应用程序需要建立数据库连接时直接到连接池中申请一个就行，用完后再放回去。dbcp没有自动的去回收空闲连接的功能。
Proxool：Proxool是一种Java数据库连接池技术，是sourceforge下的一个开源项目,这个项目提供一个健壮、易用的连接池，最为关键的是这个连接池提供监控的功能，方便易用，便于发现连接泄漏的情况。
对比结果：　
性能dbcp<=c3p0<proxool
稳定性dbcp>=c3p0>proxool

这里我们介绍一下c3p0连接池：
1. 需要准备jar包：c3p0-0.9.2.1.jar，mchange-commons-java-0.2.3.4.jar和mysql-connector-java-5.1.8-bin.jar，导入到新建项目
这里写图片描述
2. 在src下建立c3p0.properties文件，必须是这个命名，否则会无法识别
内容为：

#驱动
c3p0.driverClass=com.mysql.jdbc.Driver
#url链接
c3p0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/demo?3useUnicode=true&characterEncoding=utf8
#数据库用户名
c3p0.user=root
#数据库密码
c3p0.password=root
#初始化时获取连接数，取值应在minPoolSize与maxPoolSize之间
c3p0.initialPoolSize=3
#连接池中保留的最小连接数，默认为：3
c3p0.minPoolSize=3 
#连接池中保留的最大连接数。默认值: 15
c3p0.maxPoolSize=15
#当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3
c3p0.acquireIncrement=3
#最大空闲时间，多少秒内未使用则连接被丢弃。若为0则永不丢弃。默认值: 0
c3p0.maxIdleTime=1000
#定义在从数据库获取新连接失败后重复尝试的次数。默认值: 30 ；小于等于0表示无限次 
c3p0.acquireRetryAttempts=30
#重新尝试的时间间隔，默认为：1000毫秒
c3p0.acquireRetryDelay=1000
#获取一个connection需要的时间，单位毫秒
c3p0.checkoutTimeout=3000
#每隔多少秒检查所有连接池中的空闲连接。Default: 0 
c3p0.idleConnectionTestPeriod=60
#c3p0将建一张名为改配置项的空表，并使用其自带的查询语句进行测试。如果定义了这个参数那么属性preferredTestQuery将#被忽略。你不能在这张Test表上进行任何操作，它将只供c3p0测试使用。默认值: null。由于运营平台的数据库用户没有创建表的权限，故需要发sql创建表。
c3p0.automaticTestTable=sys_connectiontest
#如果设为true那么在取得连接的同时将校验连接的有效性。Default: false
c3p0.testConnectionOnCheckin=true
#一个checkout连接的超时设置，一旦一个checkout连接超时，他将物理的关闭，而不是返回池中，主要是防止连接被长期使用不释放，这个设置也是比较危险的
c3p0.unreturnedConnectionTimeout=15

建立连接池类：

public class ConnectionPool {
  private static ComboPooledDataSource ds;
  private static ConnectionPool pool;

  // 私有的构造函数
  private ConnectionPool() {
    ds = new ComboPooledDataSource();
  }

  public static final ConnectionPool getInstance() {
    if (pool == null) {
      pool = new ConnectionPool();
    }
    return pool;
  }

  // 获取连接池中的连接
  public synchronized final Connection getConnection() {
    try {
      Connection conn = ds.getConnection();
      return conn;
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return null;
  }
}

建立测试类：

public class TestDemo {

  public static void main(String[] args) {
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
      conn = pool.getConnection();
      ps = (PreparedStatement) conn
          .prepareStatement("select * from tb_user where id=1");
      rs = ps.executeQuery();
      while (rs.next()) {
        System.out.println("id:" + rs.getString("id") + "---username:"
            + rs.getString("userName") + "---password:"
            + rs.getString("passWord"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        ps.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

结果：
这里写图片描述
数据库中的数据：

可以顺利取出！
这里在写代码的时候遇到了一个问题，在选择connection和PreparedStatement ，选择了

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

而不是

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

最后的结果报错了
这里写图片描述
可见，这里引入的是java.sql下的类。原因是com.mysql.jdbc.Connection 是mysql自己的接口针对于对mysql的出来,java.sql.Connection 这是一个公共的接口包括对mysql的支持oracle,sqlserver 对很多数据库一个公共的API！所以c3p0调用的是公共的接口，这里需要注意！

那么为什么要引入线程池的概念呢？

(1)资源重用：由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。
(2)更快的系统反应速度:数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间
(3)新的资源分配手段对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置实现某一应用最大可用数据库连接数的限制避免某一应用独占所有的数据库资源.
(4)统一的连接管理，避免数据库连接泄露在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露。