DBUtils update方法&query方法实现原理

样例以一个名叫bank的数据库,account表为例

驱动包：mysql-connector-java-5.1.39-bin.jar

JdbcUtil02.java  数据库连接工具类

需导包：c3p0和设置数据库配置文件

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/*
 * 利用c3p0进行改造
 */
public class JdbcUtil02 {

	private static ComboPooledDataSource dataSource = null;
	
	 static{
		dataSource = new ComboPooledDataSource(); //创建数据源对象
	 }
	 
	 public static Connection getConnection() throws SQLException{
		 	return dataSource.getConnection();
	 }
	 
	 
	 //释放资源
	public static void release(Object obj) {
		if (obj != null) {
			try {
				if ((obj instanceof Connection)) {
					((Connection) obj).close();
				} else if ((obj instanceof PreparedStatement)) {
					((PreparedStatement) obj).close();
				} else if ((obj instanceof Statement)) {
					((Statement) obj).close();
				} else if ((obj instanceof ResultSet)) {
					((ResultSet) obj).close();
				}
			} catch (Exception e) {
				System.out.println("资源关闭失败！");
				e.printStackTrace();
			} finally {
				obj = null;
			}
		}
	}
	public static void release(PreparedStatement pr, ResultSet re){
		release(pr);
		release(re);
		System.out.println("资源释放成功！");
		
	}
	
	public static void release(Connection conn, ResultSet re){
		release(conn);
		release(re);
		System.out.println("资源释放成功！");
		
	}
	public static void release(Connection conn, PreparedStatement pr, ResultSet re){
		release(conn);
		release(pr);
		release(re);
		System.out.println("资源释放成功！");
		
	}
}

c3p0数据库配置文件设置，文件名一定要为c3p0-config.xml, 并且一定要放在src文件夹下

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/bank</property>
    <property name="user">root</property>
    <property name="password">123456</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>

 

接口：ResultSetHandler.java

import java.sql.ResultSet;

public interface ResultSetHandler<T> {
   
	public T handle(ResultSet rs);
}

update.java

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;

import org.junit.Test; //别忘了导测试包

import util.JdbcUtil02;

/*
 * 根据问号的个数判断参数的个数
 */
public class update {

	@Test
	public void test_01(){
		update("delete from account where id=?",4);
	}
	
	/*
	 * 遍历的次数，以问号的个数为准，有多少个问号就应该有多少个参数，以防出现参数个数打错的情况
	 */
	public void update(String sql, Object...parameter){
		
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = JdbcUtil02.getConnection();
			ps = conn.prepareStatement(sql);
			
			//利用参数元数据，获取问号，占位符，即参数的个数
			ParameterMetaData parameterMetaData = ps.getParameterMetaData(); //获取参数元数据对象
			int count = parameterMetaData.getParameterCount();

			for (int i = 0; i < count; i++) {
				ps.setObject(i+1, parameter[i]);
			}
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}

query.java

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import model.Account;
import util.JdbcUtil02;

public class query {

	
	//单个查询
	@Test
	public void test_01(){
		Account account = query("select * from account where id = ?", new ResultSetHandler<Account>(){

			@Override
			public Account handle(ResultSet rs) {
				Account account = new Account();
			    try {
					if(rs.next()){
						account.setId(rs.getInt("id"));
						account.setName(rs.getString("name"));
						account.setMoney(rs.getInt("money"));
					}
					return account;
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return null;
			}
			
			
		},"1");
		
		System.out.println(account.toString());
		
	}
	
	//多个查询
	@Test
	public void test_02(){
		List<Account> lsit = query("select * from account",new ResultSetHandler<List<Account>>(){

			@Override
			public List<Account> handle(ResultSet rs) {
				List<Account> list = new ArrayList<Account>();
			    try {
					while(rs.next()){
						Account account = new Account();
						account.setId(rs.getInt("id"));
						account.setName(rs.getString("name"));
						account.setMoney(rs.getInt("money"));
						list.add(account);
					}
					return list;
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return null;
			}
			
		});
		for (Account account : lsit) {
			System.out.println(account.toString());
		}
		
	}
	
	
	public <T> T query(String sql,ResultSetHandler<T> handler,Object...parameter){
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = JdbcUtil02.getConnection();
			ps = conn.prepareStatement(sql);
			
			//利用参数元数据，获取问号，占位符，即参数的个数
			ParameterMetaData parameterMetaData = ps.getParameterMetaData();
			int count = parameterMetaData.getParameterCount();

			for (int i = 0; i < count; i++) {
				ps.setObject(i+1, parameter[i]);
			}
			
			//查询的结果存入结果集中
			rs = ps.executeQuery();
			
			//将结果集中的数据封装进ResultSetHandler中，交由handle方法进行处理
			T t = (T) handler.handle(rs);
			
			return t;
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
}