•  

Java 调用存储过程、函数竟然如此简单,令人惊叹!

2017-12-22

市场上的数据库非常多,有关系型数据库 oracle,sqlserver,mysql,db2等等,对象型数据库 HBase、Redis、MongoDB、Couchbase、LevelDB等等,正所谓种类繁多。

作为一个架构师,从技术角度对调用存储过程,函数 针对不同的数据库要做不同的处理,这是非常麻烦而困难的事情。在此我这里抛出我个人对这块的设计。这里主要是java实现。


1、先定义一个接口类,这个接口定义两个抽象方法(调用存储过程,调用函数)

package com.sl.basic.dao;

import java.sql.Connection;

/**
 * 
 * @Description:数据库特殊方法
 * @Copyright: Copyright ShenZhen SL Software Corporation
 * @Company:
 * @Author : jiangfl
 * @Date: 
 * @Version 1.0
 */
public interface IDbSpecial {
	
	/**
	 * 设置数据库连接
	 *@param con
	 *@author:jiangfl
	 *@date 2016-4-11
	 */
	public void setConnection(Connection con);

	/***************************************************************************
	 * 调用后台存储过程
	 * 
	 * @注意
	 * 1、输出
	 * (1)sqlserver暂不支持return 返回结果,采用select 'xx' 返回结果,
	 * (2)输出参数最好使用select @a='xx';而不要使用 set  @a='xx',
	 * (3):可以输出多个游标,游标直接实用select 出来即可,不需要采用变量输出参数; 
	 * 2、oracle 输出游标,必须采用输出参数(cursor类型)方式
	 * @param procedureName
	 *            procedureName存储过程名称
	 * @param params
	 *            输入输出参数
	 * @return
	 * @throws Exception
	 * @author jiangfl
	 */
	public Object executeProcedure(String procedureName, Object... params)
			throws Exception;

	/**
	 * 执行函数
	 * 
	 * @param functionName
	 *            函数名称
	 * @param params
	 *            参数
	 * @return
	 * @throws Exception
	 * @author jiangfl
	 */
	public Object executeFunction(String functionName, Object... params)
			throws Exception;
}


2、定义一个参数类
因为参数有输入,输出参数,因此可以用一个类来定义

package com.sl.basic.dao;

/**
 * 
 * @desc 输出参数
 * @author jiangfl
 * @Copyright:Copyright ShenZhen SL Software Corporation
 */
public class OutParam {
	/**
	 * 数据类型
	 */
	public int dataType;

	public OutParam(int dataType) {
		this.dataType = dataType;
	}

}


3、定义oracle实现

package com.sl.basic.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.sl.basic.util.SLDbUtil;

/**
 * oracle 特殊数据库访问层
 * 
 * @Copyright: Copyright ShenZhen SL Software Corporation
 * @Company:
 * @Author : jiangfl
 * @Version 1.0
 */
public class OracleImp implements IDbSpecial {

	Connection con = null;

	public Object executeProcedure(String procedureName, Object... params) throws Exception {

		CallableStatement cstmt = null;

		try {
			// 组织查询语句
			String procedure = "{ call " + procedureName + "(";

			if (params != null) {
				for (int k = 0; k < params.length; k++) {
					procedure += "?,";
				}
			}
			procedure = StringUtils.substringBeforeLast(procedure, ",") + ")}";
			cstmt = con.prepareCall(procedure);

			// 设置查询in 参数
			if (params != null) {
				Object p = null;
				for (int i = 1; i < params.length + 1; i++) {
					p = params[i - 1];
					if (p instanceof OutParam) { // 如果是输入参数
						// 如果是输出参数
						cstmt.registerOutParameter(i, ((OutParam) p).dataType);

					} else {
						if (p instanceof java.util.Date) {
							cstmt.setDate(i, new java.sql.Date(((java.util.Date) p).getTime()));
						} else {
							cstmt.setObject(i, p);
						}
					}
				}
			}

			cstmt.execute();
			// 根据不同的out 参数 返回对应的结果集
			if (params == null || params.length > 0) {
				return null;
			}
			
			List<Object> result = new ArrayList<Object>();
			for (int i = 1; i <= params.length; i++) {
				if (!(params[i - 1] instanceof OutParam)) {// 如果是输入参数
					continue;
				}
				if (cstmt.getObject(i) instanceof ResultSet) {// 返回结果集
					ResultSet rs = (ResultSet) cstmt.getObject(i);
					result.add(SLDbUtil.getResultSet(rs));
				} else if (cstmt.getObject(i) instanceof String) {// 返回字符串
					result.add(cstmt.getObject(i));
				}
			}
			if (result.size() == 1) {
				return result.get(0);
			} else if (result.size() > 0) {
				return result;
			}

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (null != cstmt) {
				cstmt.close();
				cstmt = null;
			}
		}
		return null;

	}

	@Override
	public Object executeFunction(String functionName, Object... params) throws Exception {
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			String sql = "select " + functionName + "(";
			for (int k = 0; k < params.length; k++) {
				sql += "?,";
			}
			sql = sql.substring(0, sql.length() - 1) + ") from dual";

			pst = con.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				pst.setObject(i + 1, params[i]);
			}
			rs = pst.executeQuery();
			if (rs.next()) {
				return rs.getObject(1);
			}
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

	@Override
	public void setConnection(Connection con) {
		this.con = con;
	}


	public static void main(String args[])throws  Exception{
		IDbSpecial obj=new OracleImp();
		Connection con=null;//自己获取数据库连接对象
		obj.setConnection(con);
		//调用函数
		obj.executeFunction("func_getName","1");
		//调用存储过程,1:为输入参数,第二个为输出参数
		Object res=obj.executeProcedure("proc_getPage",1,new OutParam(Types.REF_CURSOR));
	}

}


4、定义sqlserver实现

package com.sl.basic.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.sl.basic.util.SLDbUtil;

public class SqlserverImp implements IDbSpecial {
	Connection con = null;

	public Object executeProcedure(String procedureName, Object... params) throws Exception {

		CallableStatement cstmt = null;
		Object p = null;

		try {
			String procedure = "{ call " + procedureName + "(";
			if (params != null) {
				for (int k = 0; k < params.length; k++) {
					procedure += "?,";
				}
			}
			procedure = StringUtils.substringBeforeLast(procedure, ",") + ")}";
			cstmt = con.prepareCall(procedure);

			// 设置查询in 参数
			if (params != null) {

				for (int i = 1; i < params.length + 1; i++) {
					p = params[i - 1];

					if (p instanceof OutParam) {
						// 如果是输出参数
						cstmt.registerOutParameter(i, ((OutParam) p).dataType);
					} else {
						// 输入参数

						if (p instanceof java.util.Date) {
							cstmt.setDate(i, new java.sql.Date(((java.util.Date) p).getTime()));
						} else {
							cstmt.setObject(i, p);
						}

					}
				}
			}

			cstmt.execute();

			List<Object> result = new ArrayList<Object>();

			// 根据不同的out 参数 返回对应的结果集
			if (params != null && params.length > 0) {

				for (int i = 1; i <= params.length; i++) {
					p = params[i - 1];
					if (p instanceof OutParam) {// 如果是输出参数
						if (cstmt.getObject(i) instanceof ResultSet) {// 返回结果集
							result.add(SLDbUtil.getResultSet((ResultSet) cstmt.getObject(i)));
						} else {
							result.add(cstmt.getObject(i));
						}
					}

				}
			}

			// 更新器
			int updateCount = -1;
			// 先处理游标结果
			do {
				updateCount = cstmt.getUpdateCount();
				if (updateCount != -1) {// 说明当前行是一个更新计数
					cstmt.getMoreResults();
					continue;// 已经是更新计数了,处理完成后应该移动到下一行,不再判断是否是ResultSet
				}
				List<Object> resultList = SLDbUtil.getResultSet(cstmt.getResultSet());
				if (resultList != null) {
					result.add(resultList);
				}
			} while (updateCount != -1 || cstmt.getMoreResults());

			if (result.size() == 1) {
				return result.get(0);
			} else if (result.size() > 0) {
				return result;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return null;

	}

	public Object executeFunction(String functionName, Object... params) throws Exception {
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			String sql = "select " + functionName + "(";
			for (int k = 0; k < params.length; k++) {
				sql += "?,";
			}
			sql = sql.substring(0, sql.length() - 1) + ")";

			pst = con.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				pst.setObject(i + 1, params[i]);
			}
			rs = pst.executeQuery();
			if (rs.next()) {
				return rs.getObject(1);
			}
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

	@Override
	public void setConnection(Connection con) {
		this.con = con;
	}


	public static void main(String args[])throws  Exception{
		IDbSpecial obj=new SqlserverImp();
		Connection con=null;//自己获取数据库连接对象
		obj.setConnection(con);
		//调用函数
		obj.executeFunction("func_getName","1");
		//调用存储过程,1:为输入参数,第二个为输出参数,通过select 出来的结果集,也能够查询出来
		Object res=obj.executeProcedure("proc_getPage",1,new OutParam(Types.REF_CURSOR));
	}

}


5、分享一个工具类

package com.sl.basic.util;

import java.io.BufferedReader;
import java.io.Reader;
import java.sql.Clob;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang3.StringUtils;

/**
 * <p>
 * Title:SQL查询语句工具类
 * </p>
 * <p>
 * Description:辅助构建SQL语句
 * </p>
 * <p>
 * Copyright: Copyright 2011 ShenZhen SL SoftwareCorparation
 * </p>
 * <p>
 * </p>
 */
public class SLDbUtil {

	/**
	 * 去除HQL查询语句的最后一个order by部分。 如果order by 是子查询 不去除
	 * 
	 * @param hql
	 *            HQL查询语句
	 * @return 返回去除了order by部分的语句。
	 */
	public static String removeOrders(String hql) {
		Pattern p = Pattern.compile("\\sorder\\s", Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(hql);
		if (!m.find()) {
			return hql;
		}
		hql = m.replaceAll(" order ");
		// 最后一个 order的位置
		int len = StringUtils.lastIndexOf(hql, " order ");
		if (len > 0) {
			// order by 的前部分
			String sql_1 = StringUtils.substring(hql, 0, len);
			// order by 及后部分
			String sql_2 = StringUtils.substring(hql, len, hql.length()).toLowerCase();
			// 判断是否包含 ),如果包含 则表明是子查询
			if (numberOfStr(sql_2, ")") <= 0||sql_2.endsWith(" asc")||sql_2.endsWith(" desc")) {
				return sql_1;
			}
		}
		return hql;
	}

	/**
	 * 根据查询语句返回对应查询总数的语句 去除HQL查询语句的select部分。
	 * 
	 * @param hql
	 *            HQL查询语句
	 * @return 返回去除了select部分的语句。 如select aa from tbale 返回 from tbale
	 */
	public static String removeSelect(String hql) {
		hql = " " + hql;
		Pattern p = Pattern.compile("\\sfrom\\s", Pattern.CASE_INSENSITIVE);
		hql = p.matcher(hql).replaceAll(" from ");
		// 查询语句如果只包含一个form
		if (numberOfStr(hql, " from ") == 1) {
			return hql.substring(hql.indexOf(" from "));
		} else {// 出现多个from 语句可能比较复杂 另外处理
				// 出现的位置
			int index = -1;
			// 循环多次出现的位置
			while ((index = hql.indexOf(" from ", index + 1)) >= 0) {
				String newstr = hql.substring(index);
				// 如果出现位置 截取字符串 中包含()都匹配,就直接返回
				if (numberOfStr(newstr, "(") == numberOfStr(newstr, ")")) {
					return newstr;
				}
			}
		}
		return "";

	}

	/**
	 * 根据查询语句返回对应查询总数的语句 去除HQL查询语句的select部分。返回select count(*) 语句
	 * 
	 * @param hql
	 *            HQL查询语句
	 * @return 返回查询select count(*) form xx 语句。
	 */
	public static String selectCount(String hql) {
		return " select count(*) " + removeSelect(hql);
	}

	/**
	 * 判断出现的位置个数
	 * 
	 * @param str
	 * @param con
	 * @return
	 */
	private static int numberOfStr(String str, String con) {
		return StringUtils.countMatches(str, con);
	}

	public static void main(String[] args) {

		// String hql = "select * torDer from  tb1";
		String sql = "select a,b,c count(d)  from  inner join  tb1,(select * from t order by tsf desc) where t=1 ORDER By aa ss,bb ss  ";
		System.out.println("select count (*)" + SLDbUtil.removeSelect(SLDbUtil.removeOrders(sql)));
	}

	public static List<Object> getResultSet(ResultSet rs) throws Exception {
		if (rs == null) {
			return null;
		}
		List<Object> lst = new ArrayList<Object>();
		try {
			int length = rs.getMetaData().getColumnCount();
			Map<Object, Object> objMap = null;
			while (rs.next()) {
				objMap = new HashMap<Object, Object>();
				Object val = null;
				for (int j = 0; j < length; j++) {
					if (rs.getMetaData().getColumnType(j + 1) == Types.DATE) {
						val = rs.getTimestamp(j + 1);
					} else {
						val = rs.getObject(j + 1);
						if (val instanceof Clob) {
							val = ClobToString((Clob) val);
						}
					}

					/*if (SLCaseInsensitiveContainerFactory.toLowerCase == null) {
						objMap.put(rs.getMetaData().getColumnName(j + 1), val);
					} else if (SLCaseInsensitiveContainerFactory.toLowerCase == false) {
						objMap.put(rs.getMetaData().getColumnName(j + 1).toUpperCase(), val);
					} else if (SLCaseInsensitiveContainerFactory.toLowerCase == true) {
						objMap.put(rs.getMetaData().getColumnName(j + 1).toLowerCase(), val);
					}
					*/
					objMap.put(rs.getMetaData().getColumnName(j + 1), val);

				}
				lst.add(objMap);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;

		} finally {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		}
		return lst;
	}

	public static String ClobToString(Clob clob) throws Exception {
		StringBuffer sb = new StringBuffer();
		BufferedReader br = null;
		try {
			Reader is = clob.getCharacterStream();// 得到流,读取完后
			br = new BufferedReader(is);
			String s = br.readLine();

			while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
				sb.append(s);
				s = br.readLine();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (br != null) {
				br.close();
			}
		}
		return sb.toString();
	}

}


6、调用

public static void main(String args[])throws  Exception{
		IDbSpecial obj=new SqlserverImp();
		Connection con=null;//自己获取数据库连接对象
		obj.setConnection(con);
		//调用函数
		obj.executeFunction("func_getName","1");
		//调用存储过程,1:为输入参数,第二个为输出参数,通过select 出来的结果集,也能够查询出来
		Object res=obj.executeProcedure("proc_getPage",1,new OutParam(Types.REF_CURSOR));
	}
public static void main(String args[])throws  Exception{
		IDbSpecial obj=new OracleImp();
		Connection con=null;//自己获取数据库连接对象
		obj.setConnection(con);
		//调用函数
		obj.executeFunction("func_getName","1");
		//调用存储过程,1:为输入参数,第二个为输出参数
		Object res=obj.executeProcedure("proc_getPage",1,new OutParam(Types.REF_CURSOR));
	}


申明:本文受法律保护,未经作者同意不得用于商业用途,如转载请说明出处!
评论