在jdbc基础上进阶一小步的C3p0 连接池(DBCP 不能读xml配置文件,已淘汰) 和DBUtils 中两个主要类QueryRunner和ResultSetHandler的使用

时间：2024-01-21 21:40:57

首先看C3p0这个连接池,最大优势可以自动读取默认的配置文件

<?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/swift_db

        </property>

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

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

        <property name="initialPoolSize">20</property>

        <property name="acquireIncrement">5</property>

        <property name="maxPoolSize">50</property>

        <property name="minPoolSize">5</property>

    </default-config>

</c3p0-config>

配置文件中有常规的4个主选项和一些其他配置

只需要使用C3p0中的实现javax.sql.DateSource接口的实现类ComboPooledDataSource 创建对象即可

private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

这个是放成员变量那,只要new 空参就会自动调用xml配置文件,配置文件需要标签<default-config>

也可以调用命名的配置文件,需要配置文件<named-config>

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

<c3p0-config>

    <named-config>

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

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

        </property>

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

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

        <property name="initialPoolSize">20</property>

        <property name="acquireIncrement">5</property>

        <property name="maxPoolSize">50</property>

        <property name="minPoolSize">5</property>

    </named-config>

</c3p0-config>

一般把C3p0做成工具类使用,方便调用

package com.swift.base;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3p0Utils {

    // 数据源连接池c3p0作用不用每次建立连接,浪费资源与等待时间,把连接做好放在连接池中,需要时获取

    // c3p0连接池可以通过配置文件c3p0-config.xml文件自动读取相关属性

    // c3p0连接池是java中 javax.sql.DataSource的实现类,其中必须实现getConnection方法

    private static ComboPooledDataSource dataSource = new ComboPooledDataSource("swift");

    // 获得数据源(连接池)的方法

    public static DataSource getDataSource() {

        return dataSource;

    }

    // 从连接池中获得一个conn资源的

    public static Connection getConnection() throws Exception {

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

        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/swift_db");

        dataSource.setUser("root");

        dataSource.setPassword("root");

        dataSource.setAcquireIncrement(5);

        dataSource.setInitialPoolSize(20);

        dataSource.setMinPoolSize(5);

        dataSource.setMaxPoolSize(50);

        Connection conn = dataSource.getConnection();

        return conn;

    }

    public static void close(Connection conn, Statement stmt, ResultSet rs) {

        if (rs != null) {

            try {

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        if (stmt != null) {

            try {

                stmt.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        if (conn != null) {

            try {

                conn.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

}

这个已经调用了xml配置文件,每次又调用Connection都会来一遍属性配置,浪费资源,可以用静态代码块,只调用一次即可

package com.swift.jinji;

import java.beans.PropertyVetoException;

import java.sql.Connection;

import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/*使用C3P0获得10个连接对象

要求：不使用配置文件*/

public class C3p0_10con {

    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

    static {

        try {

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

        } catch (PropertyVetoException e) {

            e.printStackTrace();

        }

        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/swift_db");

        dataSource.setUser("root");

        dataSource.setPassword("root");

        dataSource.setInitialPoolSize(20);

        dataSource.setAcquireIncrement(5);

        dataSource.setMaxPoolSize(50);

        dataSource.setMinPoolSize(5);

    }

    public static void main(String[] args) throws PropertyVetoException {

        for(int i=0;i<10;i++) {

            System.out.println(C3p0_10con.getConnection());

        }

    }

    public static DataSource getDataSource() {

        return dataSource;

    }

    public static Connection getConnection() {

        Connection con = null;

        try {

            con = dataSource.getConnection();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return con;

    }

    public static void close(Connection con) {

        if(con!=null) {

            try {

                con.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

}

配合DBUtils的QureyRunner类和ResultSetHandler类使用,更加方便数据库增删改与查询

package com.swift.jinji;

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.ResultSetHandler;

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.ColumnListHandler;

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

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

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

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

import com.swift.base.C3p0Utils;

import com.swift.domain.Puppy;

/*查询用户表中的第一条数据。并将数据封装成对象数组。*/

public class Qurey_Puppy {

    public static void main(String[] args) {

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

        String sql="select * from puppy;";

        ResultSetHandler<Puppy> rsh=new BeanHandler<Puppy>(Puppy.class);

        ResultSetHandler<Object[]> rsh1=new ArrayHandler();

        ResultSetHandler<List<Object[]>> rsh2=new ArrayListHandler();

        ResultSetHandler<Object> rsh3=new ScalarHandler("name");

        ResultSetHandler<Map<String, Object>> rsh4=new MapHandler();

        ResultSetHandler<List<Map<String, Object>>> rsh5=new MapListHandler();

        ResultSetHandler<List<Object>> rsh6=new ColumnListHandler("name");

        ResultSetHandler<Map<Object,Map<String,Object>>> rsh7=new KeyedHandler("id");

        try {

             Map<Object, Map<String, Object>> result = qr.query(sql, rsh7);

        /*    for(Object[] puppy:dog) {

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

            }*/

//            System.out.println(result);

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

ResultSetHandler有很多子类,可以放到各种容器中,从数据库取出数据后,将数据放到对象数组,数组集合,对象元素,Map集合,Map集合列表,对象列表,Map的Map中

相关文章