java调用存储过程的另类封装的开发日志（一）

选择了存储过程，就意味着牺牲了可移植性和灵活性，换来的确是安全性和稳定性和高性能以及特定数据库的特性。不过事情往往都是相对的，正是由于plsql的灵活多变实现一个目标往往可以有很多种途径而导致说想要写出安全稳定高效并重的存储过程也不是一件容易的事。我个人觉得存储过程最方便的地方在于传统方式可能需要几次交互的信息，丢给存储过程一次就可以搞定。维护存储过程也比可能破坏设计模式和一致风格的java代码修改来说要容易的多。
调用存储过程的花样有很多种，比如传入参数可以是基本类型，对象类型，数组类型。传出参数亦然，而且甚至可以是空参数。于是封装这个过程就不是一件容易的事情，好在经过两个星期的改进我已经成功地封装了一套模版，在工作当中稳定地运行着，现在将整个设计思想和开发的过程记录下来，包括如何一步一步的思考然后还一步一步走的弯路。可能需要很多篇博客才能描写出来，希望起到一个抛砖引玉的作用，如果大家有什么好的思想和建议还望及时与我交流相互学习促进，只要有人支持我就会写下去。
在此之前找了一下网络上的资源，发现并没有符合我心中想法的框架，参考过springjdbc调用存储过程给了我一定的启发，但它可能考虑的东西太多导致让我觉得封装的程度还是不够理想特别对于复杂参数（对象，数组，对象数组）的传递以及多结果集的返回，我理想的是增删改查不管什么情况都是一句话调用。对于sql，hibernate已经可以做到了，但是存储过程似乎还没有，无奈只能自己动手了。
先有一张USERS表字段三个：ID(NUMBER),NAME(VARCHAR2),BIRTHDAY(TIMESMTP)。
JAVA的实体BEAN

package test.bean;

import java.util.Date;

public class User {
	int id;
	String name;
	Date birthday;
	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 Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

}

这种设计是为了到时候测试基本类型而使用，我个人倾向于设计bean字段全用String，因为字符型是元字型，任何别的类型都可以由它转换而来，全字符型字段也方便我们写存储过程，为什么，以后说。扯远了。。

接着就是DAO和IMP了，接口就不写出来了，首先我们要封装的是最简单的一种情况，存储过程就一个传入参数基本类型，一个返回参数游标！

package test.bean.dao;

import java.util.List;
import test.bean.User;
import test.common.ProcRunner;

public class UserDaoImp implements UserDao{
	@Override
	public List<User> getUsers(String id) {
		String sql="{CALL PKG_TEST.GETUSERS_FROM_CURSOR(?,?)}";
//这个ProcRunner类究竟是什么，现在先不关心，我们期望的效果就是这样
		return (List<User>) new ProcRunner().execute(sql,User.class);
	}
}	

这是我们期待的封装效果，那么问题就来了，如果我们仅仅在sql中使用一个“？”我们怎么才能知道它是传入还是传出？于是这个sql还需要做一点点改进。

package test.bean.dao;

import java.util.List;
import test.bean.User;
import test.common.ProcRunner;

public class UserDaoImp implements UserDao{
	@Override
	public List<User> getUsers(int id) {
		String sql="{CALL PKG_TEST.GETUSERS_FROM_CURSOR({1},{o})}";
		return (List<User>) new ProcRunner().execute(sql,User.class);
	}
}

之所以设计成{1},{2}...这种形式是因为假如这个getUsers方法参数是这样的
getUsers(int id,String name,String ...)我们就可以将多个参数设置进去，而一个
{o}代表着传出。什么？为什么不直接用 1，2，3，o？这是怕引起视觉混淆，当然这个问题其实不重要。
现在所有的重头戏就集中到了ProcRunner身上。根据我们的期望把他的架子搭出来

package test.common;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.List;
/*
 * 这个类的设计思路我觉得应该是这样子的。首先将我们自定义的sql解析出来
 * 里面有我们指定的传入传入参数的设置和序列，它会根据我们这些符号做4件事
 * 1：设置传入参数（如果需要）
 * 2：注册传出参数（如果需要）
 * 3：执行
 * 4：获取传出结果集放到集合中返回（List<bean>|null）
 * */
public class ProcRunner {
	Connection conn;
	CallableStatement cs;
	//在面对不需要返回值的情况下，我们就不用传递一个class让它装配了
	//于是在这里设置了可变参数。
	public List<?> execute(String sql,Class<?> ...clazz){
		return null;
	}
}

接下来还要考虑就是怎么样能够把
getUsers(int id)的参数注入到这个ProcRunner里面去？否则即便我知道了要讲第一个参数设置到这个地方，可这第一个参数是什么？这样我们就要动用动态代理来帮我们把方法的参数注入到ProcRunner里面去。动态代理能够很轻易的拿到getUsers方法的所有参数，它只需要找到ProcRunner的对象就可以注入进去，这样就意味着这个ProcRunner对象不能在getUsers里去申明了，但是在DaoImp里面申明，麻烦。直接做一个父类就好了将所有要用到的对象全部申明出来就好了。这样只需要让DaoImp去继承它，就可以直接使用了，至于初始化的任务就可以全权交给动态代理了

package test.bean.dao;
import test.common.ProcRunner;
public class ProxyObjects {
	public ProcRunner pr;
}

修改daoImp层代码

package test.bean.dao;

import java.util.List;
import test.bean.User;

public class UserDaoImp extends ProxyObjects implements UserDao{
	@Override
	public List<User> getUsers(int id) {
		String sql="{CALL PKG_TEST.GETUSERS_FROM_CURSOR({1},{o})}";
		return (List<User>)pr.execute(sql,User.class);
	}
}

修改ProcRunner，使之具有参数属性。

package test.common;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.List;

public class ProcRunner {
	Connection conn;
	CallableStatement cs;
	Object[] paras;
	String[] order;

	public Object[] getParas() {
		return paras;
	}

	public void setParas(Object[] paras) {
		this.paras = paras;
	}

	// 动态代理通过构造函数为paras赋值
	public ProcRunner(Object[] paras) {
		this.paras = paras;
	}

	public List<?> execute(String sql, Class<?>... clazz) {
		return null;
	}
}

然后创建拦截器，在此我选用的是cglib作为动态代理，因为jdk的proxy只能对实现接口类代理，而cglib是以继承的方式实现无需接口，更灵活自由。但在使用上面大同小异。
拦截器如下

package test.common.proxy;

import java.lang.reflect.Field;
import java.lang.reflect.Method;

import test.common.ProcRunner;
import net.sf.cglib.proxy.Enhancer;
import net.sf.cglib.proxy.MethodInterceptor;
import net.sf.cglib.proxy.MethodProxy;

public class Interceptor implements MethodInterceptor {
	private Object target;

	public Object getInstance(Object target) {
		this.target = target;
		Enhancer enhancer = new Enhancer();
		enhancer.setSuperclass(this.target.getClass());
		enhancer.setCallback(this);
		return enhancer.create();
	}

	@Override
	public Object intercept(Object obj, Method method, Object[] args,
			MethodProxy proxy) throws Throwable {
		Class<?> clazz=target.getClass().getSuperclass();
		Field field_pr=clazz.getDeclaredField("pr");
		ProcRunner pr=new ProcRunner(args);//创建对象并赋参数
		field_pr.set(target, pr);//将建立的ProcRunner对象注入到target中
		Object ret;
		ret=method.invoke(target, args);
		return ret;
	}
}

接下来从拦截器中创建daoimp就会被拦截器作用了，我确信参数已经被拦截器注入到了daoimp的procrunner对象pr中了。
现在所有的焦点就都集中到了ProcRunner身上了，他具有了我们要为存储过程设置的参数，还通过解析我们自定义的sql知道了这些参数设置的序列，实现它的思路就清晰了，还有一步准备工作就是怎么样把游标变成对象集合？完成这么一个工具类！

package test.common.utils;

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

public class BeanUtils {
	public static List<Object> getBeans(ResultSet rs, Class<?> clazz)
			throws Exception {
		List<Object> ret = new ArrayList<Object>();
		int attrsCount = rs.getMetaData().getColumnCount();// 游标有多少列
		while (rs.next()) {
			Object bean = clazz.newInstance();
			for (int i = 1; i <= attrsCount; i++) {
				// 每列给对应的属性赋值
				String attr = rs.getMetaData().getColumnLabel(i);
				Field field = clazz.getDeclaredField(attr);
				field.setAccessible(true);
				field.set(bean, rs.getObject(i));
			}
			ret.add(bean);
		}
		return ret;
	}
}

接下来就可以完整的写出ProcRunner类了，一气呵成完成它吧！

package test.common;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import oracle.jdbc.OracleTypes;
import db.until.DbHelperJdbc;
import static test.common.utils.BeanUtils.*;

public class ProcRunner {
	enum ParamType{
		CURSOR,ARRAY;
	}
	Connection conn;
	CallableStatement cs;
	ResultSet rs;
	Object[] paras;//DaoImp方法上面的参数
	String[] orders;

	public Object[] getParas() {
		return paras;
	}

	public void setParas(Object[] paras) {
		this.paras = paras;
	}

	// 动态代理通过构造函数为paras赋值
	public ProcRunner(Object[] paras) {
		this.paras = paras;
	}

	/*
	 * 这个类的设计思路我觉得应该是这样子的。首先将我们自定义的sql解析出来 里面有我们指定的传入传入参数的设置和序列，它会根据我们这些符号做三件事
	 * 1：设置传入参数（如果需要） 2：注册传出参数（如果需要） 3：执行 4：获取传出结果集放到集合中返回（List<bean>|null）
	 */
	public List<?> execute(String sql, Class<?>... clazz) throws SQLException {
		List<?> ret = null;// 最终返回
		// 注册参数需要在执行之后获取，所以需要一个集合来存放注册的序号和类型
		Map<Integer, ParamType> map = new LinkedHashMap<Integer, ParamType>();
		try {
			// 获取连接
			conn = DbHelperJdbc.getInstance().getConn();
			// 获取自定义参数序列 "1,2,3,o"之类的
			orders = getOrdersFromSql(sql);
			// 准备语句，需要讲我们设置的东西重新替换成'?'
			cs = conn.prepareCall(sql.replaceAll("\\{\\w\\}", "?"));
			// 1.设置传入传出参数！
			for (int index = 0; index < orders.length; index++) {
				try {
					int order = Integer.parseInt(orders[index]);
					cs.setObject(index + 1, paras[order - 1]);
				} catch (NumberFormatException e) {
					// 转型失败说明应该是有o了说明应该是输出类型
					// 2.注册传出参数！
					cs.registerOutParameter(index + 1, OracleTypes.CURSOR);
					map.put(index + 1, ParamType.CURSOR);
				}
			}
			// 3.执行！
			cs.execute();
			// 4.获取结果集并通过传递进来的bean的class加载并放到list中返回(如果需要)！
			if (map.size() > 0) {
				ret=new ArrayList();
				int clazz_index=0;//对于传进来的bean.class是数组，我们需要用来初始化收集
				Iterator<Integer> it = map.keySet().iterator();
				while (it.hasNext()) {
					int reg_order=it.next();//注册时候的序号
					ParamType reg_type=map.get(reg_order);//注册时候的类型
					switch (reg_type) {
					case CURSOR:
						//游标怎么处理
						//先暂时写到这里吧，看看整个执行过程对不对
						rs=(ResultSet) cs.getObject(reg_order);
						ret=getBeans(rs,clazz[clazz_index]);
						break;
					}
					++clazz_index;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs!=null)
				rs.close();
			if (cs != null)
				cs.close();
			if (conn != null)
				conn.close();
		}
		return ret;
	}

	// 首先我们观察传入的SQL，简化成"{X X.X({1},{2},{o})}"
	// 我们实质上是想得到其中的"1,2,o"
	public String[] getOrdersFromSql(String sql) {
		// 直接截取中括号里面的内容
		sql = sql.substring(sql.indexOf("(") + 1,
				sql.indexOf(")"));
		// 现在sql 为"{1},{2},{o}"再替换"{","}"为""
		sql = sql.replaceAll("\\{|\\}", "");
		// 现在sql为"1,2,o" 按","分割返回
		return sql.split(",");
	}
}

现在激动人心的时刻到了，测试是惊险的。
先往表里添加记录

   INSERT INTO USERS VALUES('1','Billy',sysdate);
   INSERT INTO USERS VALUES('2','Lily',sysdate);
   INSERT INTO USERS VALUES('3','Rowland',sysdate);

存储过程：

CREATE OR REPLACE PACKAGE BODY PKG_TEST IS
   PROCEDURE GETUSERS_FROM_CURSOR(I_ID IN NUMBER,O_RES OUT SYS_REFCURSOR) AS
     BEGIN
       OPEN O_RES FOR SELECT 
         ID AS "id",
         NAME AS "name",
         BIRTHDAY AS "birthday"
         FROM USERS WHERE ID=I_ID;
     END;
END PKG_TEST;

package test;

import java.util.List;

import test.bean.User;
import test.bean.dao.UserDao;
import test.bean.dao.UserDaoImp;
import test.common.proxy.Interceptor;

public class Test {
	public static void main(String[] args) throws Exception {
		UserDao ud=(UserDao) new Interceptor().getInstance(new UserDaoImp());
		List<User> list=ud.getUsers(1);
		System.out.println("beans count:"+list.size());
		for(User user:list){
			System.out.println("id="+user.getId());
			System.out.println("name="+user.getName());
			System.out.println("birthday"+user.getBirthday());
		}
	}
}

结果呢？报错了
java.lang.IllegalArgumentException: Can not set java.lang.Integer field test.bean.User.id to java.math.BigDecimal
这么看来应该是类型错误，如果我们仅仅只是rs.getObject就很容易出现这种问题，应该要使用rs.getInt去赋值bean的id，现在知道我说我倾向于在Bean时候使用字符类型了吧，有人会说那我java端要拿去做计算不方便，我说，既然选择了使用存储过程就应该把业务逻辑全权交给存储过程java端就只需要取出来展示一下，或者传进数据库存入一下。不应该出现java端还有复杂计算。针对一些展示格式上面特别的小的拼接和运算，那么交给前台js去处理就好了，js本身也是弱类型的。所以解决的思路有两个，那么在BeanUtil里拿到bean属性的时候去判断一下类型，这么一来你至少需要8个基本类型+字符型+Date+...，我是比较懒了，所以我直接把bean的属性都改成string算了。
那么相应的存储过程就需要全部返回字符型了

CREATE OR REPLACE PACKAGE BODY PKG_TEST IS
   PROCEDURE GETUSERS_FROM_CURSOR(I_ID IN NUMBER,O_RES OUT SYS_REFCURSOR) AS
     BEGIN
       OPEN O_RES FOR SELECT 
         to_char(ID) AS "id",
         NAME AS "name",
         to_char(BIRTHDAY,'yyyy-mm-dd') AS "birthday"
         FROM USERS WHERE ID=I_ID,ID;
     END;
END PKG_TEST;

再来测试一下，结果如下：
beans count:1
id=1
name=Billy
birthday=2012-11-24
测试完了一个参数的情况，我们测试一下两个参数看看吧

package test.bean.dao;

import java.util.List;
import test.bean.User;

public class UserDaoImp extends ProxyObjects implements UserDao {
	public List<User> getUsers(int id,String name) throws Exception {
		return (List<User>) pr.execute("{CALL PKG_TEST.GETUSERS_FROM_CURSOR({1},{2},{o})}", User.class);
	}
}

   PROCEDURE GETUSERS_FROM_CURSOR(I_ID IN NUMBER,I_NAME IN VARCHAR2,O_RES OUT SYS_REFCURSOR) AS
     BEGIN
       OPEN O_RES FOR SELECT 
         to_char(ID) AS "id",
         NAME AS "name",
         to_char(BIRTHDAY,'yyyy-mm-dd') AS "birthday"
         FROM USERS WHERE ID=ID AND NAME=I_NAME;
     END; 

测试代码只需要改动一个地方
List<User> list=ud.getUsers(1,"Bily");
看看结果？
beans count:0
什么？没报错？又没查出来？什么情况？赶紧查查数据库，哎呀我去！数据库里是"Billy"
实在是太粗心了。改成Billy之后正常。
我们再来测试一下无参的情况

package test.bean.dao;

import java.util.List;
import test.bean.User;

public class UserDaoImp extends ProxyObjects implements UserDao {
	public List<User> getUsers()throws Exception {
		return (List<User>) pr.execute("{CALL PKG_TEST.GETUSERS_FROM_CURSOR({o})}", User.class);
	}
}

存储过程只需要将传入参数删掉，where语句删掉就不贴出来了。结果如下
beans count:3
id=1
name=Billy
birthday=2012-11-24
id=2
name=Lily
birthday=2012-11-24
id=3
name=Rowland
birthday=2012-11-24
OK，大功告成。看来我们达到了目标了，接下来的任务就是不断地对ProcRunner进行开发，使之兼容各种类型的传入传出。这一篇解决了游标，下一篇咱们来解决数组。

总结：其实整个过程并没有使用太深奥超前的技术，尽量不借助第三方包而完成整个过程。最麻烦的地方在于多样的类型，如果想要真正封装成一套框架的话，那么对于细节的处理就不能像我这么大条。好在终于是完成了它。今后使用存储过程就显得很方便了。