JDBC调用存储过程的工具类

1.处理存储过程参数的工具类

package com.yanshu.utils;

/**
 * 处理存储过程参数的工具类
 * @author Administrator
 *
 */
public class ConnSql {


public ConnSql() {
}



/**
* 传入参数即存储名称预编译处理
* @param storageName 存储过程的名称
* @param size 参数的数量
* @return 返回存储过程的参数集
*/
public static String storageNameHandle(String storageName, Integer size) {


// 参数没有，直接返回存储名
if (size <= 0) {
return storageName;
}
// 预处理参数
String params = "?";
// 参数拼接
for (int i = 1; i < size; i++) {
params += ",?";
}
String newstorageName = storageName + "(" + params + ")";


return newstorageName;


}


public static void main(String[] args) {


String newstorageName = ConnSql.storageNameHandle("call op_search_flow_port_analysis_trend ", 4);


System.out.println("newstorageName=" + newstorageName);
}

}

2.

package com.yanshu.utils;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import com.alibaba.fastjson.JSON;


import com.yanshu.utils.ConnSql;
import com.yanshu.utils.JdbcUtil;
/**
 * 
 * @author Administrator
 *
 */
public class ProcJsonUtil {


public static   Map<String, Object> findProcs(String storageName, String[] args) {
CallableStatement cs = null;
ResultSet rs = null;
Map<String, Object> map = new HashMap<String, Object>();
Connection conn = JdbcUtil.getConn();
try {
String newStorageName = ConnSql.storageNameHandle(storageName, args.length);
cs = conn.prepareCall("{call " + newStorageName + "}");
for (int i = 0; i < args.length; i++) {
cs.setString(i + 1, args[i]);
}
rs = cs.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();

/**
* 遍历循环出表的列名
*/
List list = new ArrayList<>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
String columnLabel = rsmd.getColumnLabel(i + 1);
list.add(columnLabel);
}
list = (List) JSON.toJSON(list);
/**
* 遍历循环出列向对应的字段
*/
List listtwo = new ArrayList<>();
int listii = 0;
int ii = rsmd.getColumnCount();
while (rs.next()) {
List listtre = new ArrayList<>();


for (int i = 1; i <= ii; i++) {
Object columnValue = rs.getObject(i);
if(columnValue==null)
{
Object object="0";
columnValue=object;
}
listtre.add(columnValue);


}
listtwo.add(listtre);
listii = listtwo.size();
}
Map<String, ArrayList<?>> map1 = new HashMap<String, ArrayList<?>>();
Map<String, Object> mapTwo = new HashMap<String, Object>();


mapTwo.put("rows", listtwo);
mapTwo.put("columns", list);
//map.put("count", listii);
map.put("data", mapTwo);


} catch (SQLException e) {
e.printStackTrace();
}finally
{
//释放资源
JdbcUtil.closeConn(rs, cs, null, null, conn);
}
return map;


}
public static String getIndicators(String storageName, String[] args)
{

Connection conn = null;
CallableStatement cs=null;
ResultSet rs=null;
try {
conn = JdbcUtil.getConn();
System.out.println("conn--->>>"+conn);
String newStorageName = ConnSql.storageNameHandle(storageName, args.length);
cs = conn.prepareCall("{call " + newStorageName + "}");
//遍历循环参数
for (int i = 0; i < args.length; i++) {
cs.setString(i + 1, args[i]);
}
rs = cs.executeQuery();
ResultSetMetaData rsmdThree = rs.getMetaData();
int columnCount = rsmdThree.getColumnCount();
System.out.println("columnCount-->>"+columnCount);

/**
* 遍历循环出表的列名
*/
List list = new ArrayList<>();

for (int i = 0; i <columnCount; i++) {
String columnLabel = rsmdThree.getColumnLabel(i + 1);
list.add(columnLabel);
}
list = (List) JSON.toJSON(list);
System.out.println(list);
/**
* 遍历循环出列向对应的字段
*/
List listtwo = new ArrayList<>();
List listIndicators=new ArrayList<>();
int listii = 0;
while (rs.next()) {
List listtre = new ArrayList<>();
for (int i = 1; i <=columnCount; i++) {
Object columnValue = rs.getObject(i);
if(columnValue==null)
{
Object object="0";
columnValue=object;
}
listtre.add(columnValue);


}
listtwo.add(listtre);
listii = listtwo.size();
//
Map<String, Object> dataMap = new HashMap<String, Object>(0);
for (int i = 1; i <= rsmdThree.getColumnCount(); i++) {
dataMap.put(rsmdThree.getColumnName(i), rs.getObject(i));
}

listIndicators.add(dataMap);
}
System.out.println(JSON.toJSONString(listIndicators));

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return "111";
}
public static void main(String[] args) throws Exception {


//String[] par = new String[] {"1783198133", "36155392729","2","20170801","20170820"};
//Map<String, Object> map1 = findProcs("SP_Find_Auctiontargetes", par);
//exec  SP_FindTarget_news @Sellerid=601879343  ,  @Starttime= 20170801 , @ETime=20170825 , @platfrom=2
String[] par = new String[] {"601879343", "20170801","20170825","2"};
//Map<String, Object> map1 = getIndicators("SP_FindTarget_news", par);
System.out.println(getIndicators("SP_FindTarget_news", par));





}




}