Day10.连接池&dbutils

1.C3P0:

  1. 概述:c3p0是一个开源的JDBC连接池,它实现了数据源和JNDI绑定,支持JDBC3规范和JDBC2标准扩展,目前使用它的开源项目有Hibernate、Spring等。
  2. 使用步骤:
    1. 导入相应的包


    2. 找到api(快速入门)复制粘贴

ComboPooledDataSource ds = new ComboPooledDataSource();

        ds.setDriverClass("com.mysql.jdbc.Driver");

        ds.setJdbcUrl("jdbc:mysql://localhost:3306/bj232");

        ds.setUser("root");

        ds.setPassword("root");

  1. 配置文件:

    文件名必须为:c3P0-config.xml

        配置文件名称:c3p0-config.xml(固定)

        配置文件位置:src(类路径)

        配置文件内容:命名配置/默认配置

<?xml
version="1.0"
encoding="UTF-8"?>

<c3p0-config>

    <default-config><!-- 默认配置 -->

        <property
name="driverClass">com.mysql.jdbc.Driver</property>

        <property
name="jdbcUrl">jdbc:mysql://localhost:3306/bj232</property>

        <property
name="user">root</property>

        <property
name="password">root</property>

    </default-config>

    <named-config
name="hello"><!-- 指定的配置 -->

 

        <property
name="driverClass">com.mysql.jdbc.Driver</property>

        <property
name="jdbcUrl">jdbc:mysql:///web07</property>

        <property
name="user">root</property>

        <property
name="password">root</property>

    </named-config>

</c3p0-config>

  1. 代码实现:

 

package cn.itcast.c3p0;

 

import java.beans.PropertyVetoException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

 

import org.junit.Test;

 

import com.mchange.v2.c3p0.ComboPooledDataSource;

 

import cn.itcast.utils.JdbcUtil;

 

public class Demo01 {

    @Test

    public void test1() throws PropertyVetoException, SQLException{

        ComboPooledDataSource ds = new ComboPooledDataSource();

        

        ds.setDriverClass("com.mysql.jdbc.Driver");

        ds.setJdbcUrl("jdbc:mysql://localhost:3306/bj232");

        ds.setUser("root");

        ds.setPassword("root");

        /*

         * 只要连接池都实现了接口Datasource接口,都有获得连接对象的方法

         */

        Connection con = ds.getConnection();

        String sql= "select * from goods";

        PreparedStatement stat = con.prepareStatement(sql);

        

        ResultSet rs = stat.executeQuery();

        while (rs.next()) {

            String str = rs.getString(2);

            System.out.println(str);

            

        }

        JdbcUtil.closeResoure(con, stat, rs);

    }

}

2.JdbcUtils封装:

(1)原来

package cn.itcast.utils;

 

import java.io.FileReader;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

 

public class JdbcUtil {

    private static final String drivername;

    private static final String url;

    private static final String user;

    private static final String password;

 

    static {

        Properties pp = new Properties();

        try {

            pp.load(new FileReader("src/jdbc.properties"));

        } catch (IOException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

 

        drivername = pp.getProperty("drivername");

        url = pp.getProperty("url");

        user = pp.getProperty("user");

        password = pp.getProperty("password");

 

    }

    static {

        // 当类被加载的时候执行一次

        try {

            Class.forName(drivername);

        } catch (ClassNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

 

    }

 

    public static Connection getConnection() {

        Connection con = null;

        try {

            con = DriverManager.getConnection(url, user, password);

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return con;

    }

 

    public static void closeResoure(Connection con, Statement stat, ResultSet rs) {

        if (rs != null) {

            try {

                rs.close();

            } catch (SQLException e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            }

            rs = null;

        }

 

        if (stat != null) {

            try {

                stat.close();

            } catch (SQLException e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            }

            stat = null;

        }

 

        if (con != null) {

            try {

                con.close();

            } catch (SQLException e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            }

            con = null;

        }

    }

    // 方法重载

    public static void closeResoure(Connection con, Statement stat) {

 

        if (stat != null) {

            try {

                stat.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

            stat = null;

        }

        if (con != null) {

            try {

                con.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

            con = null;

        }

    }

}

(2)now


package cn.itcast.utils;

 

import java.sql.Connection;

import java.sql.SQLException;

 

import javax.sql.DataSource;

 

import com.mchange.v2.c3p0.ComboPooledDataSource;

 

public class JdbcUtils {

    //目的为了得到连接对象

    private static final ComboPooledDataSource ds = new ComboPooledDataSource();

    

    public static Connection getCon(){

        //我们可以通过池子里面得到连接对象

        Connection con = null;

        try {

            con=ds.getConnection();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return con;

    }

    

    public static DataSource getDataSource(){

        return ds;

    }

}

 

 

3.DBUtils

  1. 概述:是Apache组织提供的一个对JDBC进行简单封装的开源工具类库,使用它能简化JDBC应用程序的开发,同时也不会影响程序的性能。
  2. 使用步骤:
    1. 导入jar包:


    

 

ArrayHandler:将我们查询结果中的第一个结果封装到数组中返回。*/

ArrayListHandler:将查询结果全部封装大数组中,再将数组封装到list中返回给我们*/

MapHandler:将结果中的第一条结果封装到map中返回*/

MapListHandler:将查询的所有的结果放到map中,然后封装到list中返回*/

ScalarHandler:一般用于一个结果 常用语聚合函数

BeanHandler:将查询的结果中的第一条封装bean对象里面返回。

BeanListHandler:将所有的结果封装到bean里面 然后将bean封装到list集合中返

  1. 代码实现:

 

package cn.itcast.dbcp;

 

import java.sql.SQLException;

import java.util.Arrays;

import java.util.List;

import java.util.Map;

 

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.ArrayHandler;

import org.apache.commons.dbutils.handlers.ArrayListHandler;

import org.apache.commons.dbutils.handlers.BeanHandler;

import org.apache.commons.dbutils.handlers.BeanListHandler;

import org.apache.commons.dbutils.handlers.MapHandler;

import org.apache.commons.dbutils.handlers.MapListHandler;

import org.apache.commons.dbutils.handlers.ScalarHandler;

import org.junit.Test;

 

import cn.itcast.utils.JdbcUtils;

 

public class DbcpDemo02 {

    @Test

    public void demo1() throws SQLException{

        //创建核心类

        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());

        String sql = "select * from goods";

        /*

         * 参数:

         * 第一个:sql

         * 第二个:查询结果返回的结果类型

         * 第三个:指的是sql语句中的?,需要我们传入的参数

         */

        Object[] query = qr.query(sql, new ArrayHandler());

        String str = Arrays.toString(query);

        System.out.println(str);

    }

    

    @Test

    /*

     * ArrayListHandler:将查询结果全部封装到数据中,再将数组封装到list集合中返回,

     */

    public void demo2() throws SQLException{

        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());

        String sql = "select *from goods";

        List<Object[]> query = qr.query(sql, new ArrayListHandler());

        for (Object[] obj : query) {

            System.out.println(Arrays.toString(obj));

        }

    }

    

    @Test

    /*

     * MapHandler将结果中的第一条结果封装到map中返回

     */

    public void demo3() throws SQLException{

        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());

        String sql = "select *from goods";

        Map<String, Object> map = qr.query(sql, new MapHandler());

        for(String key: map.keySet()){

            System.out.println(key+"===="+map.get(key));

        }

    }

    

    @Test

    /*

     * MapListHandler:将查询的所有结果放到map中,然后封装到list中返回

     */

    public void demo4() throws SQLException{

        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());

        String sql = "select *from goods";

        List<Map<String,Object>> list = qr.query(sql, new MapListHandler());

        for (Map<String, Object> map : list) {

            for(String key:map.keySet()){

                System.out.println(key+"="+map.get(key));

            }

        }

    }

    

    @Test

    /*

     * ScalarHandler:一般用于一个结果,常用语:聚合函数

     */

    public void demo5() throws SQLException{

        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());

        String sql = "select count(*) from goods";

        Object object = qr.query(sql, new ScalarHandler());

        System.out.println(object);

    }

    

    @Test

    public void demo6() throws SQLException{

        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());

        String sql = "select *from goods";

        Goods goods = qr.query(sql, new BeanHandler<Goods>(Goods.class));

        System.out.println(goods);

    }

    

    @Test

    public void demo7() throws SQLException{

        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());

        String sql = "select *from goods";

        List<Goods> list = qr.query(sql, new BeanListHandler<Goods>(Goods.class));

        for (Goods goods : list) {

            System.out.println(goods);

        }

    }

}

4.dbcp

  1. 导入jar包:



  2. 配置文件:

        配置文件名称:*.properties

配置文件位置:任意,建议src

配置文件内容:properties不能编写中文,不支持在STS中修改,必须使用记事本修改内容,否则中文注释就乱码了

 

 

package cn.itcast.utils;

 

import java.io.FileReader;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import org.junit.Test;

 

public class DbcpUtils {

    @Test

    public void Demo01() throws SQLException{

        /*

         * 步骤一:导入jar包

         * BasicDataSource实现了DataSource接口

         * 接口中有方法getConnection

         * 调用getConnection

         */

        

        BasicDataSource bds = new BasicDataSource();

        //基本配置

        bds.setDriverClassName("com.mysql.jdbc.Driver");//设置驱动

        bds.setUrl("jdbc:mysql://localhost:3306/bj232");//设置连接地址

        bds.setUsername("root");

        bds.setPassword("root");

        

        Connection con = bds.getConnection();

        //获得操作数据库对象

        String sql = "select * from goods";

        PreparedStatement stat = con.prepareStatement(sql);

        //执行

        ResultSet rs = stat.executeQuery();

        while (rs.next()){

            String str = rs.getString(6);

            System.out.println(str);

        }

        //释放资源

        JdbcUtil.closeResoure(con, stat, rs);

    }

通过配置文件:

public
void demo2() throws Exception{

        Properties pp = new Properties();

        pp.load(new FileReader("src/dbcp.properties"));

        

        DataSource ds = BasicDataSourceFactory.createDataSource(pp);

        

        //获得连接对象

        Connection con = ds.getConnection();

        //获得执行sql语句的对象

        String sql = "select * from goods";

        PreparedStatement stat = con.prepareStatement(sql);

        //执行sql

        ResultSet rs = stat.executeQuery();

        while(rs.next()){

            String str = rs.getString(6);

            System.out.println(str);

        }

        //关闭资源

        JdbcUtil.closeResoure(con, stat, rs);

    }

5.装饰者模式::

    概述:

        专门为解决某一类问题,而编写的固定格式的代码

    结构:    

        接口A, 已知实现类C,需要装饰者创建代理类B

    步骤:

        1.创建类B ,并实现接口A

        2.提供类B的构造方法,参数类型为A ,用于接收A接口的其它实现类(C)

        3.给类B添加类型为A成员变量,用于存放A接口的其它实现类

        4.增强需要的方法

        5.实现不需要增强的方法,方法体冲调用成员变量存放其它实现类对应的方法

    代码实现:

总结:(1)本质都要使用相同的接口

(2)包装者的写法

public class 包装者 implement 接口{

 

private 类型 被包装对象;

//构造

public 包装者(被包装对象){

 

this.被包装对象 = 被包装对象;

}

 

具体的方法(){

//加强

}

 

}

缺点:我们需要将接口所有的方法重写。

 

小温

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: