C3P0的配置以及基础使用方法

1：导各种包

                <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>3.0.1</version>
			<scope>provided</scope>
		</dependency>
		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.0.8</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/commons-pool/commons-pool -->
		<dependency>
			<groupId>commons-pool</groupId>
			<artifactId>commons-pool</artifactId>
			<version>1.6</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/c3p0/c3p0 -->
		<dependency>
			<groupId>c3p0</groupId>
			<artifactId>c3p0</artifactId>
			<version>0.9.1.2</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
		<dependency>
			<groupId>commons-dbutils</groupId>
			<artifactId>commons-dbutils</artifactId>
			<version>1.6</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/javax.servlet/jstl -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/taglibs/standard -->
		<dependency>
			<groupId>taglibs</groupId>
			<artifactId>standard</artifactId>
			<version>1.1.2</version>
		</dependency>

2：创建c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/_mycms?useUnicode=true&characterEncoding=UTF-8
</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">root</property>
 
 
<property name="checkoutTimeout">60*10</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
</c3p0-config>

3：创建C3p0Utils.java

package com.syp.utils;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3p0Utils {
	private static DataSource ds;
	static {
		ds=new ComboPooledDataSource();
	}
	
	public static DataSource getDataSource(){
		return ds;
	}
}

4: 创建实体类 User

package com.syp.po;

public class User {
	private int id;
	private String name;
	private String password;
	
	public User() {
		// TODO 自动生成的构造函数存根
	}

	public User(int id, String name, String password) {
		super();
		this.id = id;
		this.name = name;
		this.password = password;
	}

	public int getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", password=" + password + "]";
	}
	
	
}

5:创建UserDaoIn.java  接口

package com.syp.dao;


import java.util.List;


import com.syp.po.User;


public interface UserDaoIn {
	List<User> findAll();


	User find(int id);


	Boolean insert(User user);


	Boolean update(User user);


	Boolean delete(int id);


}

6: 创建 UserDaoIm.java 实现类

package com.syp.dao.imp;


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


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


import com.syp.dao.UserDaoIn;
import com.syp.po.User;
import com.syp.utils.C3p0Utils;


public class UserDaoIm implements UserDaoIn {


	@Override
	public List<User> findAll(){
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select * from user";
		List<User> list = null;
		try {
			list = (List<User>) runner.query(sql, new BeanListHandler<>(User.class));


			return list;
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return list;
	}


	@Override
	public User find(int id) {
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select * from user where id=?";
		ResultSetHandler<User> rsh = new BeanHandler<>(User.class);


		User user = null;
		try {
			user = (User) runner.query(sql, rsh);
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}


		return user;
	}


	@Override
	public Boolean insert(User user) {
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "insert into user(name,password) values(?,?)";


		int num = 0;
		try {
			num = runner.update(sql, new Object[] { user.getName(), user.getPassword() });
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		if (num > 0) {
			return true;
		}
		return false;
	}


	@Override
	public Boolean update(User user){
		QueryRunner runner=new QueryRunner(C3p0Utils.getDataSource());
		String sql="update user set name=? , password=? where id=?";
		Object[] param={user.getName(),user.getPassword(),user.getId()};
		int num = 0;
		try {
			num = runner.update(sql, param);
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		if(num >0){
			return true;
		}
		return false;
	}


	@Override
	public Boolean delete(int id){
		QueryRunner runner=new QueryRunner(C3p0Utils.getDataSource());
		String sql="delete from user where id=?";
		Object[] param={id};
		
		int num = 0;
		try {
			num = runner.update(sql, param);
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		if(num>0){
			return true;
		}
		return false;
	}
	






}

7：创建UserService.java

package com.syp.service;


import java.util.List;


import com.syp.dao.UserDaoIn;
import com.syp.dao.imp.UserDaoIm;
import com.syp.po.User;


public class UserService implements UserDaoIn {
	private UserDaoIm userdao=new UserDaoIm();
	@Override
	public List<User> findAll() {
		return userdao.findAll();
	}


	@Override
	public User find(int id) {
		return userdao.find(id);
	}


	@Override
	public Boolean insert(User user){
		return userdao.insert(user);
	}


	@Override
	public Boolean update(User user) {
		return userdao.update(user);
	}


	@Override
	public Boolean delete(int id) {
		return userdao.delete(id);
	}


}

8: 利用UserService进行操作