第三方数据库连接池包

1.dbcp

第一步：导入dbcp包

第二步：通过核心类连接数据库

public void Demo1(){
        BasicDataSource ds=new BasicDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql///contacts?characterEncoding=UTF8");
        ds.setUsername("root");
        ds.setPassword("admin");
        ds.setMaxActive(5);//设置最多有几个连接
        ds.setInitialSize(2);//设置在开始时创建几个连接
    }

第三步：创建一个资源文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/contacts?useUnicode=true&characterEncoding=UTF8
username=root
password=admin
maxActive=3

Properties p=new Properties();
        p.load(Demo1pool.class.getResourceAsStream("jdbc.properties"));
        DataSource ds=new BasicDataSourceFactory().createDataSource(p);

使用连接池

在一个项目中，就只能拥有一个DataSource的实例。在这个dataqSource3中有多个Connectioin。声明一个工厂类，创建维护唯一的一个DataSource

 

package javaee.utils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DataSourceUtils {
    private DataSourceUtils() {
    }

    private static DataSource ds;
    static {
        try {
            Properties p = new Properties();
            p.load(DataSourceUtils.class.getClassLoader().getResourceAsStream(
                    "jdbc.properties"));
            ds = new BasicDataSourceFactory().createDataSource(p);
        } catch (Exception e) {
        }
    }
    // 返回一个唯一的连接
    public static Connection getCon() {
        Connection con = null;
        try {
            con = ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    // 提供一个方法返回DataSource
    public static DataSource getDs() {
        return ds;
    }
}

第三方操作数据库包专门接收DataSource-dbutils

Dbutils

操作数据第三方包。依赖数据源DataSource（DBCP|C3p0）。

QueryRunner – 接收DataSource｜Connection，查询数据删除修改操作。返回结果。

ResultSetHandler – 结果集句柄，将结果数据封装成程序所需要的数据类型Map,List,Bean。

package cn.demo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

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.BeanListHandler;
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 org.junit.Test;

import cn.domain.Contact;
import cn.domain.User;
import static cn.dbutils.DataSourceUtils.*;

public class Demo1 {
    @Test
    public void query1() throws Exception {
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "select * from users";
        Object[] o = run.query(sql, new ArrayHandler());
        for (Object s : o) {
            System.err.println(s);
        }

    }

    @Test
    public void query2() throws Exception {
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "select * from users";
        List<Object[]> o = run.query(sql, new ArrayListHandler());
        for (Object[] s : o) {
            for (Object os : s) {
                System.err.println(os);
            }
        }
    }

    @Test
    public void queryBean() throws Exception {
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "select * from users ";
        User user = run.query(sql, new BeanHandler<User>(User.class));
        System.err.println(user);

    }

    @Test
    public void queryBean1() throws Exception {
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "select * from users where id=?";
        User user = run.query(sql, new BeanHandler<User>(User.class), "U003");
        System.err.println(user);

    }

    @Test
    public void queryBeanList() throws Exception {
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "select * from users ";
        List<User> user = run.query(sql, new BeanListHandler<User>(User.class));
        for (User u : user) {
            System.err.println(u);
        }

    }

    @Test
    public void queryColumnList() throws Exception {
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "select name from users ";
        List<Object> obj = run.query(sql, new ColumnListHandler());
        for (Object o : obj) {
            System.err.println(o);
        }

    }

    @Test
    public void querykeyed() throws Exception {
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "select * from users";
        Map<Object, Map<String, Object>> mm = run.query(sql, new KeyedHandler(
                "id"));
        System.err.println(mm);
        Iterator it = mm.keySet().iterator();
        while (it.hasNext()) {
            Map m1 = mm.get(it.next());
            System.err.println(m1.get("id") + "," + m1.get("name") + ","
                    + m1.get("pwd"));
        }

    }
    @Test
    public void queryMap() throws Exception{
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "SELECT u.name as uname,c.name as cname"+
                     " FROM users u INNER JOIN contacts c ON u.id=c.uid where u.id='U001'";
        System.err.println(sql);
        Map<String,Object> mm = run.query(sql,new MapHandler());
        System.err.println(mm);
    }
    
    
    @Test
    public void queryMapList() throws Exception{
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "SELECT u.name as uname,c.name as cname"+
                     " FROM users u INNER JOIN contacts c ON u.id=c.uid where u.id='U001'";
        System.err.println(sql);
        List<Map<String, Object>> mm = run.query(sql,new MapListHandler());
        System.err.println(mm);
    }

    @Test
    public void queryScalar() throws Exception{
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "select count(*) from users ";
        System.err.println(sql);
        Object mm = run.query(sql,new ScalarHandler());
        System.err.println(mm);
    }
    
    @Test
    public void queryValidBean() throws Exception{
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "select id as cid,name as cname,sex from contacts";
        List<Contact> cs = run.query(sql,new BeanListHandler<Contact>(Contact.class));
        System.err.println(cs);
    }
    //手工封装
    @Test
    public void queryValidBean1() throws Exception{
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "select * from contacts";
        List<Contact> cs =
                run.query(sql, 
                new ResultSetHandler<List<Contact>>(){
                    @Override
                    public List<Contact> handle(ResultSet rs)
                            throws SQLException {
                        List<Contact> list = new ArrayList<Contact>();
                        while(rs.next()){
                            Contact c = new Contact();
                            c.setCid(rs.getString("name"));
                            c.setCname(rs.getString("id"));
                            c.setSex(rs.getString("sex"));
                            list.add(c);
                        }
                        return list;
                    }
                });
        System.err.println(">>>:"+cs);
    }
    @Test
    public void Insert1() throws Exception {
        QueryRunner run = new QueryRunner(getDataSource());
        run.update("insert into users values('U003','张三','888')");
    }

    @Test
    public void Insert2() throws Exception {
        QueryRunner run = new QueryRunner(getDataSource());
        run.update("insert into users values(?,?,?)", "u004", "王武", "999");
    }

    @Test
    public void del() throws Exception {
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "delete from users where name ='王武'";
        int len = run.update(sql);
        System.err.println(len);
    }

    @Test
    public void update() throws Exception {
        QueryRunner run = new QueryRunner(getDataSource());
        String sql = "update users set name='李四' where id='U003'";
        int len = run.update(sql);
        System.err.println(len);
    }
}

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/contacts?useUnicode=true&characterEncoding=UTF8&useOldAliasMetadataBehavior=true
username=root
password=admin
maxActive=5

package cn.domain;

public class Contact {
    private String cid;
    private String cname;
    private String sex;
    public String getCid() {
        return cid;
    }
    public void setCid(String cid) {
        this.cid = cid;
    }
    public String getCname() {
        return cname;
    }
    public void setCname(String cname) {
        this.cname = cname;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String toString(){
        return "Contect[id='"+cid+"'  name='"+cname+"'  sex='"+sex+"']";
    }
}

package cn.domain;

public class User {

    private String id;
    private String name;
    private String pwd;
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPwd() {
        return pwd;
    }
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
    @Override
    public String toString(){
        return "User[id='"+id+"',name='"+name+"',pwd='"+pwd+"']";
    }
    
}