使用元数据来优化JDBC对数据库的CURD操作

元数据可以用来优化JDBC对数据库的CURD操作。其中增、删、改可以优化为同一个接口，查询比较复杂些，可单独优化为一个接口。

增删改优化为如下，此方法接受两个参数，sql语句和sql语句中对应的参数：

public static int update(String sql, Object[] params){
		
		if(sql.equals("") || sql==null){
			return -1;
		}
		try {
			conn = JdbcC3p0Pools.getConnection2();
			ps = conn.prepareStatement(sql);
			if(params!=null && params.length>0){
				pm = ps.getParameterMetaData();
				int pmCount = pm.getParameterCount();
				while(pmCount>0){
					ps.setObject(pmCount, params[pmCount-1]);
					pmCount--;
				}
			}
			
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return -1;
	}

2、查询方法优化如下：

public static ArrayList<Object> query(String sql, Object[] params, Class clazz ) throws Exception{
		if(sql.equals("") || sql==null){
			return null;
		}
		
		try {
			conn = JdbcC3p0Pools.getConnection2();
			ps = conn.prepareStatement(sql);
			if(params!=null && params.length>0){
				pm = ps.getParameterMetaData();
				int pmCount = pm.getParameterCount();
				while(pmCount>0){
					ps.setObject(pmCount, params[pmCount-1]);
					pmCount--;
				}
			}
			
			rs = ps.executeQuery();
			BeanListHander hander = new BeanListHander(clazz);  //利用了策略模式，将结果封装到clazz bean中
			return hander.hander(rs);
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}

  这里需要定义另外一个类，BeanListHander,此类利用反射技术，将ResultSet对象查询结果封装到Clazz的对象中。

    类BeanListHander

package cn.itcast.utils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;


public class BeanListHander {
	private Class clazz;
	private ArrayList<Object> list = new ArrayList<Object>();
	
	
	public BeanListHander(Class clazz){
		this.clazz = clazz;
	}
	
	public ArrayList<Object> hander(ResultSet rs) throws Exception{
		
		ResultSetMetaData rm = null;
		int count = 0;
		
		while(rs.next()){
			Object bean = clazz.newInstance();
			rm = rs.getMetaData();
			count = rm.getColumnCount();
			//要求数据库字段和域方法一致
			while(count>0){
				String name = rm.getColumnName(count);
				Object value = rs.getObject(count);
				
				Field f = clazz.getDeclaredField(name);//利用反射技术
				f.setAccessible(true);
				f.set(bean, value);
				count--;
			}
			list.add(bean);
		}
		
		return list;
	}
}

//demo

package cn.itcast.demo;

import java.util.ArrayList;

import org.junit.Test;

import cn.itcast.domain.Account;
import cn.itcast.utils.DatabaseOperationUtils;

public class Demo6 {
	@Test
	public void query(){
		String sql = "select * from account";
		ArrayList<Object> list;
		try {
			list = DatabaseOperationUtils.query(sql, null, Account.class);
			for(Object a : list){
				Account account = (Account)a;
				System.out.println(account.getId()+":"+account.getName()+":"+account.getMoney());
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	@Test
	public void update(){
		String sql = "update account set money=money+1000 where name=?";
		Object[] params = new Object[1];
		params[0]="aaa";
		DatabaseOperationUtils.update(sql, params);
	}
	
	@Test
	public void add(){
		String sql = "insert into account(name,money) values(?,?)";
		Object[] params = new Object[2];
		params[0]="eee";
		params[1]=1500;
		DatabaseOperationUtils.update(sql, params);
	}
	
	@Test
	public void del(){
		String sql = "delete from account where name=?";
		Object[] params = new Object[1];
		params[0]="eee";
		DatabaseOperationUtils.update(sql, params);
	}
}