Java commons-dbutils c3p0加载数据库

１.　需要的包：
c3p0.jar
commons-dbutils.jar
mysql-connector-java.jar
mchange-commons-java.jar

2 . 配置文件　
工程根目录(src/c3p0-config.xml)

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 默认配置，如果没有指定则使用这个配置 -->
	<default-config>
		<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///goods?serverTimezone=UTC</property>
		<property name="user">root</property>
		<property name="password">123456</property>
		<property name="checkoutTimeout">1200</property>
		<property name="idleConnectionTestPeriod">30</property>
		<property name="initialPoolSize">10</property>
		<property name="maxIdleTime">30</property>
		<property name="maxPoolSize">20</property>
		<property name="maxStatements">0</property>
		<user-overrides user="test-user">
			<property name="maxPoolSize">10</property>
			<property name="minPoolSize">1</property>
			<property name="maxStatements">0</property>
		</user-overrides>
	</default-config>
	
	<!-- 命名配置 -->
	<name-config name="goods">
		<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///goods?serverTimezone=UTC</property>
		<property name="user">root</property>
		<property name="password">123456</property>
		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">5</property>
		<property name="maxPoolSize">20</property>
		<property name="maxStatements">0</property>
		<property name="maxStatementsPerConnection">5</property>
	</name-config>
</c3p0-config>

辅助类：C3p0Utils

/**
 * 
 */
package com.igeek.utils;

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;

/**
 * @author Administrator
 *
 */
public class C3p0Utils {
	
	/***
	 * 创建连接对象
	 */
	private static ComboPooledDataSource dataSource = new ComboPooledDataSource("goods");
	
	//本地线程池：缓存连接对象
	private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
	
	public static DataSource getDataSource()
	{
		return dataSource;
	}
	
	public static Connection getConnection() throws SQLException{
		//先尝试从本地线程池中获取连接对象
		Connection connection = threadLocal.get();
		if(connection == null)
		{
			//如果时第一次调用，则本地线程池中没有现成的连接对象
			//则从连接池中获取一个新的连接对象
			connection = dataSource.getConnection();
			threadLocal.set(connection);
		}
		return connection;
	
	}
	
	public static void close(ResultSet rs,Statement statement,Connection connection)
	{
		if(rs!= null)
		{
			try {
				rs.close();
			
			}catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
		if(statement != null)
		{
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(connection != null)
		{
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	
}

调用的案例

/**
 * 
 */
package com.igeek.domain;

import java.io.Serializable;

/**
 * @author Administrator
 *
 */
public class Member implements Serializable{

	private Integer id;
	
	private String username;
	
	private String password;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

	public int getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	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 "Member [id=" + id + ", username=" + username + ", password=" + password + "]";
	}

	public Member() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	
	
	
}



/**
 * 
 */
package com.igeek.dao;

import java.sql.SQLException;

import com.igeek.domain.Member;

/**
 * @author Administrator
 *
 */
public interface MemberDao {

	
	public Member selectByName(String name) throws SQLException;
	
	public Member selectById(Integer id) throws SQLException;
	
	
}


/**
 * 
 */
package com.igeek.dao.imp;

import java.sql.SQLException;

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

import com.igeek.dao.MemberDao;
import com.igeek.dao.User;
import com.igeek.domain.Member;
import com.igeek.utils.C3p0Utils;

/**
 * @author Administrator
 *
 */
public class MemberDaoImpl implements MemberDao{

	@Override
	public Member selectByName(String name) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select id , username,password from member where username=?";
		Object[] params = {name};
		Member member = qr.query(sql, new BeanHandler<Member>(Member.class),params);
		return member;
	}

	@Override
	public Member selectById(Integer id) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select id , username,password from member where id=?";
		Object[] params = {id};
		Member member = qr.query(sql, new BeanHandler<Member>(Member.class),params);
	
		return member;
	}
	
	

}



/**
 * 
 */
package com.igeek.service;

import com.igeek.domain.Member;

/**
 * @author Administrator
 *业务操作
 */
public interface MemberService {

	public Member login(String username,String password);
}


/**
 * 
 */
package com.igeek.service.impl;

import java.sql.SQLException;

import com.igeek.dao.MemberDao;
import com.igeek.dao.imp.MemberDaoImpl;
import com.igeek.domain.Member;
import com.igeek.service.MemberService;

/**
 * @author Administrator
 *
 */
public class MemberServiceImpl implements MemberService {

	private MemberDao memberDao = new MemberDaoImpl();
	
	@Override
	public Member login(String username, String password) {
		// 
		try {
			Member member = memberDao.selectByName(username);
			if(member != null)
			{
				if(member.getPassword().equals(password))
				{
					return member;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

}


/**
 * 
 */
package com.igeek.web;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.igeek.domain.Member;
import com.igeek.service.MemberService;
import com.igeek.service.impl.MemberServiceImpl;

/**
 * @author Administrator
 *
 */
public class LoginServlet extends HttpServlet {

	/**
	 * 
	 */
	private static final long serialVersionUID = -66914447102026201L;


	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		String username = req.getParameter("username");
		String password = req.getParameter("password");
	
		MemberService userService = new MemberServiceImpl();
		Member member =  userService.login(username, password);
		resp.setContentType("text/ html;charset=utf-8");
		PrintWriter out = resp.getWriter();
		if(member == null)
		{
			out.append("当前的账户和密码错误！");
		}
		else
		{
			out.append(member.toString());
		}
		
		out.flush();
		out.close();
		
	}
	
	
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		doGet(req,resp);
	}
	
	
}




<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>登录页</title>
</head>
<body>
	<form action="${pageContext.request.contextPath }/register" method="post">
		<table >
			<tr>
			<th>用户名：</th>
			<th><input type="text" name="username" />  </th>
			</tr>
			<tr>
			<th>密码： </th>	
			<th><input type="password" name="password"/></th>
			
			</tr>
		
			<tr >
				<th colspan="2" style="align-self: center;">
					<input type="submit" value="注册" style="margin-top: 20px;">
				</th>
			
			</tr>
		
		</table>
	
	</form>
	
	
</body>
</html>



web.xml
  <servlet>
  	<servlet-name>Login</servlet-name>
  	<servlet-class>com.igeek.web.LoginServlet</servlet-class>
  </servlet>
  <servlet-mapping>
  	<servlet-name>Login</servlet-name>
  	<url-pattern>/login</url-pattern>
  </servlet-mapping>