- 首页 > it技术 > >
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
*
* @author
*
*/
public class ProUtil {
/**
* 给定存储过程（必须有一个或以上输入参数 最后一个为输出动态游标）名称、 参数输出结果
* @param conn 连接
* @param proceducesName 存储过程名称
* @param params 存储过程传入参数
* @return
*/
public static List getProList(Connection conn, String proceducesName,
String... params) {
List result = null;
if (params == null || params.length == 0)
return result;
String call = getCallName(proceducesName, params);
CallableStatement cstmt = null;
ResultSet rs = null;
try {
cstmt = conn.prepareCall(call);
String str = "";
for (int i = 0;
i < params.length;
i++) {
str = StringUtil.isNull(params[i]) ? "" : params[i];
cstmt.setString(i + 1, str);
}
cstmt.registerOutParameter(params.length + 1, oracle.jdbc.OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(params.length + 1);
result = parseResult(rs);
} catch (Exception e) {
e.printStackTrace();
System.err.println("出现错误:" + e.getMessage());
} finally {
closeResource(rs, cstmt, conn);
}
return result;
}
/**
* 根据存储过程（必须有一个或以上输入参数 最后一个为输出动态游标）名称返回结果集 包含结果集列名
* @param conn
* @param proceducesName
* @param params
* @return
*/
public static List getProListLm(Connection conn, String proceducesName,
String... params) {
List result = null;
if (params == null || params.length == 0)
return result;
String call = getCallName(proceducesName, params);
CallableStatement cstmt = null;
ResultSet rs = null;
try {
cstmt = conn.prepareCall(call);
String str = "";
for (int i = 0;
i < params.length;
i++) {
str = StringUtil.isNull(params[i]) ? "" : params[i];
cstmt.setString(i + 1, str);
}
cstmt.registerOutParameter(params.length + 1, oracle.jdbc.OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(params.length + 1);
result = parseResults(rs);
} catch (Exception e) {
e.printStackTrace();
System.err.println("出现错误:" + e.getMessage());
} finally {
closeResource(rs, cstmt, conn);
}
return result;
}
/**
* 给定存储过程（必须有一个或以上输入参数 最后一个为输出动态游标）名称，对象返回结果
* @param conn
* @param cl
* @param proceducesName 存储过程名称
* @param params 存储过程传入参数
* @return List
* */
public static List getProList(Connection conn, Class cl, String proceducesName,
String... params) {
List result = null;
if (params == null || params.length == 0)
return result;
String call = getCallName(proceducesName, params);
CallableStatement cstmt = null;
ResultSet rs = null;
try {
cstmt = conn.prepareCall(call);
String str = "";
for (int i = 0;
i < params.length;
i++) {
str = StringUtil.isNull(params[i]) ? "" : params[i];
cstmt.setString(i + 1, str);
}
cstmt.registerOutParameter(params.length + 1, oracle.jdbc.OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(params.length + 1);
result = parseResult(rs, cl);
} catch (Exception e) {
System.err.println("出现错误:" + e.getMessage());
e.printStackTrace();
} finally {
closeResource(rs, cstmt, conn);
}
return result;
}
/**
* 给定存储过程（必须有一个或以上输入参数 最后一个为输出动态游标）名称 类对象组返回结果
* @param conn
* @param cl
* @param proceducesName 存储过程名称
* @param params 存储过程传入参数
* @return List
* */
public static List> getProLists(Connection conn, Class[] cls, String proceducesName,
String... params) {
List> result = null;
if (params == null || params.length == 0)
return result;
String call = getCallName(proceducesName, params);
CallableStatement cstmt = null;
ResultSet rs = null;
try {
cstmt = conn.prepareCall(call);
String str = "";
for (int i = 0;
i < params.length;
i++) {
str = StringUtil.isNull(params[i]) ? "" : params[i];
cstmt.setString(i + 1, str);
}
cstmt.registerOutParameter(params.length + 1, oracle.jdbc.OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(params.length + 1);
result= parseResults(rs, cls);
} catch (Exception e) {
System.err.println("出现错误:" + e.getMessage());
e.printStackTrace();
} finally {
closeResource(rs, cstmt, conn);
}
return result;
}
/**
* 根据sql 与对象 返回对象结果集
* @param conn
* @param cl
* @param sql
* @param params
* @return
*/
public static List SelectList(Connection conn, Class cl, String sql,
String... params){
List result = null;
PreparedStatement cstmt = null;
ResultSet rs = null;
try {
cstmt = conn.prepareStatement(sql);
if(params!=null){
for(int i=0;
i【使用反射写一个简单的JDBC工具类】 List parseResult(ResultSet rs, Class cl) throws SecurityException,
IllegalArgumentException, SQLException, InstantiationException, IllegalAccessException, Exception {
ResultSetMetaData metaData = https://www.it610.com/article/rs.getMetaData();
int colSize = metaData.getColumnCount();
List result = new ArrayList();
while (rs.next()) {
T clt = cl.newInstance();
Field[] fls = clt.getClass().getDeclaredFields();
c:for(Field fl:fls){
for(int i = 0;
i < colSize;
i++){
if(metaData.getColumnName( i + 1 ).equals(fl.getName().toUpperCase())){
String va=rs.getString(fl.getName());
fl.setAccessible(true);
setValue(clt, fl, va);
continue c;
}
}
}
result.add(clt);
}
return result;
}
private static List> parseResults(ResultSet rs, Class[] cls) throws SecurityException,
IllegalArgumentException, SQLException, InstantiationException, IllegalAccessException, Exception {
ResultSetMetaData metaData = https://www.it610.com/article/rs.getMetaData();
int colSize = metaData.getColumnCount();
List> result = new ArrayList>();
for(int i=0;
i());
}
while (rs.next()) {
for(int j=0;
j parseResult(ResultSet rs) throws Exception {
List result = new ArrayList();
ResultSetMetaData metaData = https://www.it610.com/article/rs.getMetaData();
int size = metaData.getColumnCount();
while (rs.next()) {
List t = new ArrayList();
for (int i = 1;
i <= size;
i++) {
String str = rs.getString(i);
str = StringUtils.removeSpecilChar(str);
t.add(str);
}
result.add(t);
}
return result;
}
/**
* 结果集取出包含列名称
* @param rs
* @return
* @throws Exception
*/
private static List parseResults(ResultSet rs) throws Exception {
List result = new ArrayList();
ResultSetMetaData metaData = https://www.it610.com/article/rs.getMetaData();
int size = metaData.getColumnCount();
List ts = new ArrayList();
result.add(ts);
for(int i = 1;
i <= size;
i++){
ts.add(metaData.getColumnLabel(i));
}
while (rs.next()) {
List t = new ArrayList();
for (int i = 1;
i <= size;
i++) {
String str = rs.getString(i);
str = StringUtils.removeSpecilChar(str);
t.add(str);
}
result.add(t);
}
return result;
}
/**
* 拼接调用存储过程字符串
* @param proceducesName
* @param params
* @return
*/
private static String getCallName(String proceducesName, String[] params) {
String call ="{call " + proceducesName + "(?";
for (int i = 0;
i < params.length;
i++) {
call += ",?";
}
call += ")}";
return call;
}
/**
* 关闭连接等
* @param rs
* @param cstmt
* @param conn
*/
private static void closeResource(ResultSet rs, CallableStatement cstmt, Connection conn) {
try {
if (null != rs)
rs.close();
if (null != cstmt)
cstmt.close();
if (null != conn)
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static void setValue(T t, Field f, Object value) throws IllegalAccessException {
// TODO 以数据库类型为准绳，还是以java数据类型为准绳？还是混合两种方式？
if (null == value)
return;
String v = value.toString();
String n = f.getType().getName();
if ("java.lang.Byte".equals(n) || "byte".equals(n)) {
f.set(t, Byte.parseByte(v));
} else if ("java.lang.Short".equals(n) || "short".equals(n)) {
f.set(t, Short.parseShort(v));
} else if ("java.lang.Integer".equals(n) || "int".equals(n)) {
f.set(t, Integer.parseInt(v));
} else if ("java.lang.Long".equals(n) || "long".equals(n)) {
f.set(t, Long.parseLong(v));
} else if ("java.lang.Float".equals(n) || "float".equals(n)) {
f.set(t, Float.parseFloat(v));
} else if ("java.lang.Double".equals(n) || "double".equals(n)) {
f.set(t, Double.parseDouble(v));
} else if ("java.lang.String".equals(n)) {
f.set(t, value.toString());
} else if ("java.lang.Character".equals(n) || "char".equals(n)) {
f.set(t, (Character) value);
} else if ("java.lang.Date".equals(n)) {
f.set(t, new Date(((java.sql.Date) value).getTime()));
} else if ("java.lang.Timer".equals(n)) {
f.set(t, new Time(((java.sql.Time) value).getTime()));
} else if ("java.sql.Timestamp".equals(n)) {
f.set(t, (java.sql.Timestamp) value);
} else {
System.out.println("SqlError：暂时不支持此数据类型，请使用其他类型代替此类型！");
}
}
}
推荐阅读
