JDBC调用存储过程的工具类

缺乏、安全感 2022-06-02 09:47 98阅读 0赞

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);  
\}

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));  
  
  
  
  
  
\}  
  
  
  
  
\}