JDBC基础(3)连接池和封装工具（操作curd）

1.连接池

概念：

类似与线程池，在连接池初始化的时候存入一定数量的连接,用的时候通过方法获取,不用的时候归还连接，目的就是为了提高项目的性能。

市面上的连接池很多：DBCP（Apache公司）、C3P0、德鲁伊druid（阿里）等。

连接池配置的方法有两种：

1. 硬编码：通过代码连接数据库（不推荐）。
2. 配置文件加载：通过调properties文件连接数据库。

1.DBCP

• 硬编码：

public class Demo3 {
    public static void main(String[] args) throws SQLException {
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        basicDataSource.setUrl("jdbc:mysql:///test");
        basicDataSource.setUsername("root");
        basicDataSource.setPassword("123456");

        Connection conn = basicDataSource.getConnection();
        PreparedStatement preparedStatement = conn.prepareStatement("select * from bank where name=?");
        preparedStatement.setString(1,"zhangsan");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String name = resultSet.getString(1);
            String money = resultSet.getString(2);
            System.out.println(name+"==="+money);
        }
        //不需要释放资源，连接池底层已封装
    }
}

• 配置文件：

Properties properties = new Properties();
properties.load(new FileReader("dbcp.properties"));
BasicDataSourceFactory basicDataSourceFactory = new BasicDataSourceFactory();
DataSource dataSource = basicDataSourceFactory.createDataSource(properties);
Connection conn = dataSource.getConnection();

2.C3P0

• 硬编码

  public class Demo3 {
      public static void main(String[] args) throws Exception {
          ComboPooledDataSource dataSource = new ComboPooledDataSource();
          dataSource.setDriverClass("com.mysql.jdbc.Driver");
          dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
          dataSource.setUser("root");
          dataSource.setPassword("123456");
          Connection conn = dataSource.getConnection();
          
          PreparedStatement preparedStatement = conn.prepareStatement("select * from bank where name=?");
          preparedStatement.setString(1,"zhangsan");
          ResultSet resultSet = preparedStatement.executeQuery();
          while (resultSet.next()){
              String name = resultSet.getString(1);
              String money = resultSet.getString(2);
              System.out.println(name+"==="+money);
          }
      }
  }
  

• 配置文件
  要求：

  1. 配置文件的名称:c3p0.properties 或者 c3p0-config.xml（名称不能变）
  2. 配置文件的路径:src下

  ComboPooledDataSource dataSource = new ComboPooledDataSource("c3p0.properties");
  Connection conn = dataSource.getConnection();
  或者>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
  ComboPooledDataSource dataSource = new ComboPooledDataSource("c3p0.properties");
  

3.德鲁伊druid

• 硬编码

  DruidDataSource dataSource = new DruidDataSource();
  dataSource.setDriverClassName("com.mysql.jdbc.Driver");
  dataSource.setUrl("jdbc:mysql://localhost:3306/test");
  dataSource.setUsername("root");
  dataSource.setPassword("123456");
  DruidPooledConnection conn = dataSource.getConnection();
  

• 配置文件

  Properties properties = new Properties();
  properties.load(new FileReader("druid.properties"));
  DataSource dataSource = new DruidDataSourceFactory().createDataSource(properties);
  Connection conn = dataSource.getConnection();
  

2.封装的工具类（使用dbutils完成curd操作）

1.概括：

是apache组织的一个工具类,jdbc的框架,更方便我们使用

2.使用步骤：

使用步骤:
1.导入jar包(commons-dbutils-1.4.jar)
2.创建一个queryrunner类
queryrunner作用:操作sql语句
构造方法:new QueryRunner(Datasource ds);
3.编写sql
4.执行sql
query(…):执行r操作
update(…):执行cud操作

3.示例：

1.使用工具进行curd操作

public class Demo4 {
    public static void main(String[] args) throws Exception {
        //使用C3P0的方式连接数据库
        ComboPooledDataSource dataSource = new ComboPooledDataSource("c3p0.properties");
        //使用DButils工具类
        QueryRunner queryRunner = new QueryRunner(dataSource);
        //参1为sql语句，参2为第一个问号的值，参2为第二个问号的值
        queryRunner.update("insert into user values(?,?)", "a", "123456");
    }
}

2.将查询结果封装到list集合中

public class Demo4 {
    public static void main(String[] args) throws Exception {
        ComboPooledDataSource dataSource = new ComboPooledDataSource("c3p0.properties");
        QueryRunner queryRunner = new QueryRunner(dataSource);
        //参1为sql语句，参2为BeanListHandler 把从数据库查出来的多条数据
        //封装到对象里面，再把对象放到集合里面
        //因此需要创建一个与数据库相对应的类
        List<user> list = queryRunner.query("select * from user", new BeanListHandler<user>(user.class));
        System.out.println(list);
    }
}

//与数据库对应的类
package com.ge.Demo1;

public class user {
    private String username;
    private  String password;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "user{" +
                "username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

3.查询第一条结果，把这个结果封装进对象里面

public class Demo4 {
    public static void main(String[] args) throws Exception {
        ComboPooledDataSource dataSource = new ComboPooledDataSource("c3p0.properties");
        QueryRunner queryRunner = new QueryRunner(dataSource);
        //参1为sql语句，参2与上面的类似
        user userobj = queryRunner.query("select * from user", new BeanHandler<user>(user.class));
        System.out.println(userobj);
    }
}

4.把查询的结果封装到Map集合里面

public class Demo4 {
    public static void main(String[] args) throws Exception {
        ComboPooledDataSource dataSource = new ComboPooledDataSource("c3p0.properties");
        QueryRunner queryRunner = new QueryRunner(dataSource);
        Map<String, Object> map = queryRunner.query("select * from user", new MapHandler());
    }
}