DBUtils的使用.

使用步骤

1.导入jar包   

commons-dbutils-xx.jar

c3p0-xxx.jar

mysql-connector-java-xxxx-bin.jar            

2 .创建QueryRunner对象

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

3 .使用query方法执行select语句

package com.wency.dbutils;

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

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import com.wency.entity.User;


public class TestDBUtils {
	@Test
	public void testFind() throws SQLException{
		
		//创建QuerRunner对象
		QueryRunner qr=new QueryRunner(C3p0Utils.getDataSource());
        List<User> users = qr.query("select * from user",new ResultSetHandler<List<User>>(){

			@Override
			public List<User> handle(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				
				List<User> list=new ArrayList<User>();
				while (rs.next()) {
					User user=new User();
					int id=rs.getInt(1);
					String name=rs.getString(2);
					String pwd=rs.getString(3);
					String email=rs.getString(4);
					user.setId(id);
					user.setPwd(pwd);
					user.setUserName(name);
					user.setEmail(email);
					list.add(user);
				}
				return  list;
			}
			

		});
        
        System.out.println(users);
        
	}
	
	@Test
	public void testSelect2() throws SQLException
	{
		//创建一个QueryRunner对象
		QueryRunner qr=new QueryRunner(C3p0Utils.getDataSource());
		//调用qr的Query()方法
		List<User> list = qr.query("select * from user where _id=?",new BeanListHandler<User>(User.class),2);
	    System.out.println(list);
	}
	
	@Test
	public void testInsert() throws SQLException{
		QueryRunner qr=new QueryRunner(C3p0Utils.getDataSource());
		int update = qr.update("insert into user(userName,pwd,email) values(?,?,?)","小刚","345555","344536@qq.com");
		System.out.println(update);
		
	}
	
	@Test
	public void testUpdate() throws SQLException{
		QueryRunner qr=new QueryRunner(C3p0Utils.getDataSource());
		int update = qr.update("update user set userName=? where _id=?","小美",3);
	}
	
	@Test
	public void testDelete() throws SQLException{
		QueryRunner qr=new QueryRunner(C3p0Utils.getDataSource());
		qr.update("delete from user where _id=?","3");
	}
	
	@Test
	public void testBatch() throws SQLException{
		QueryRunner qr=new QueryRunner(C3p0Utils.getDataSource());
		Object[][] params=new Object[10][];
		for (int i = 0; i < params.length; i++) {
			params[i]=new Object[]{"wency","3344","345@454.com"};
		}
		qr.batch("insert into user(userName,pwd,email) values(?,?,?)", params); 
		
	}
}