初学java之C3P0(25)

c3p0是一个开源的JDNC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibermate,Spring等。
导入jar包

添加配置文件c3p0-config.xml；

 从连接池中获取数据源， 可以编写工具类(专门获取连接对象)，包名com.xx.utils
Dao层通过C3P0查询
分别编写表现层，业务逻辑层，数据访问层

 C3p0实现curd (有3个方法)

QueryRunner  curd 的核心

查询的结果是：对象的集合:  queryRunner.query(sql, new BeanListHandler<类名>(类名.class)

查询的结果是一个对象：qRunner.query(sql,new BeanHandler<类名>(类名.class),参数);（sql语句中需要的参数，如果没有就不要..）

查询数量：  Long  num= (Long) qRunner.query(sql,new ScalarHandler(参数),stuno,pwd);//参数写法1编写获取值的索引 写法2 列名

增删改： qRunner.update(sql, 参数1,  参数2......);
例如:
 

package controller;

import service.SortService;
import service.impl.SortServiceImpl;

import java.util.List;

public class SortController {
    SortService ss = new SortServiceImpl();
    public List<String> getList(){


        return ss.getList();
    }

}

package dao;

import java.util.List;

public interface SortDao {
    List<String> getList();
}

package dao.impl;

import dao.SortDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import utils.C3P0Utils;

import java.sql.SQLException;
import java.util.List;

public class SortDaoImpl implements SortDao{

    @Override
    public List<String> getList() {
        QueryRunner qr = C3P0Utils.getqueryRunner();
        String sql = "select sname from sort";
        List<String> query = null;
        try {
            query = qr.query(sql, new ColumnListHandler<String>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }
}

package service;

import java.util.List;

public interface SortService {

    List<String> getList();
}

package service.impl;

import dao.SortDao;
import dao.impl.SortDaoImpl;
import service.SortService;

import java.util.List;

public class SortServiceImpl implements SortService{
    SortDao sd = new SortDaoImpl();
    @Override
    public List<String> getList() {
        return sd.getList();
    }
}

package pojo;

public class Sort {
    private int sid;
    private String sanme;
    private int sprize;
    private String sinfo;

    public Sort() {
    }

    public Sort(int sid, String sanme, int sprize, String sinfo) {
        this.sid = sid;
        this.sanme = sanme;
        this.sprize = sprize;
        this.sinfo = sinfo;
    }

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public String getSanme() {
        return sanme;
    }

    public void setSanme(String sanme) {
        this.sanme = sanme;
    }

    public int getSprize() {
        return sprize;
    }

    public void setSprize(int sprize) {
        this.sprize = sprize;
    }

    public String getSinfo() {
        return sinfo;
    }

    public void setSinfo(String sinfo) {
        this.sinfo = sinfo;
    }

    @Override
    public String toString() {
        return "Sort{" +
                "sid=" + sid +
                ", sanme='" + sanme + '\'' +
                ", sprize=" + sprize +
                ", sinfo='" + sinfo + '\'' +
                '}';
    }
}

package utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;

import javax.sql.DataSource;

public class C3P0Utils {
    private static DataSource ds;
    static {
        ds =new ComboPooledDataSource();
    }

   public static QueryRunner getqueryRunner(){
       return new QueryRunner(ds);
   }
}

import controller.SortController;

import java.util.List;

public class Test {
    public static void main(String[] args) {
        SortController sc = new SortController();
        List<String> list = sc.getList();

        for(String s :list){
            System.out.println(s);
        }
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
   <default-config>
      <property name="user">root</property>
      <property name="password">root</property>
      <property name="jdbcUrl">jdbc:mysql://localhost:3306/day25
      </property>
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <!-- 连接池初始化的时候的连接数 -->
      <property name="initialPoolSize">5</property>
      <!-- 连接池中连接的最大个数 -->
      <property name="maxPoolSize">20</property>
      <!-- 用户获得连接Connection的时候，如果有连接就获得，没有就等待的时间，如果超时就报异常 -->
      <property name="maxIdleTime">5000</property>
      <!-- 连接池中连接的最小个数 -->
      <property name="minPoolSize">5</property>
   </default-config>
</c3p0-config>