package com.topcheer.file.excel;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Map;
import org.apache.log4j.Logger;
import org.topcheer.common.util.jdbc.JdbcServiceHelper;
/**
*
*
* TODO 数据库操作通用类
*/
public class DbUtil {
private static final String errPrefix = DbUtil.class.getName();
private final String PINT = "int";
private final String PSTRING = "String";
private final String PLONG = "long";
private final String PFLOAT = "float";
private final String PDOUBLE = "double";
private final String PBOOLEAN = "boolean";
private final String OUTPUT = "OUTPUT";
private static DbUtil m_Instance = null;
private Logger log = Logger.getLogger(DbUtil.class);
static public DbUtil GetInstance() {
if (m_Instance == null) {
m_Instance = new DbUtil();
LoadDrivers();
}
return m_Instance;
}
public void Init() {
m_Instance = new DbUtil();
}
static private void LoadDrivers() {
String driverClassName = "oracle.jdbc.driver.OracleDriver";
try {
Class.forName(driverClassName);
//DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
} catch (Exception e) {
e.printStackTrace();
}
}
public ArrayList sqlToArrayListMap1(String sql) {
ArrayList arraylist = new ArrayList();
Connection con = null;
Statement stmt = null;
ResultSet rst = null;
try {
con = null;//MasterInstance.GetInstance().getConnection();
stmt = con.createStatement();
rst = stmt.executeQuery(sql);
int count = rst.getMetaData().getColumnCount();
ResultSetMetaData rsmd = rst.getMetaData();
while (rst.next()) {
HashMap temp = new HashMap();
for (int i = 0; i < count; i++) {
String columnName = rsmd.getColumnName(i + 1);
columnName = columnName.toUpperCase();
temp.put(columnName, (rst.getString(i + 1)));
}
arraylist.add(temp);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rst.close();
} catch (Exception ex) {
}
rst = null;
try {
stmt.close();
} catch (Exception ex) {
}
stmt = null;
try {
con.close();
} catch (Exception ex) {
}
con = null;
}
return arraylist;
}
public ArrayList rstToArrayListMap(ResultSet rst) {
ArrayList arraylist = new ArrayList();
try {
int count = rst.getMetaData().getColumnCount();
ResultSetMetaData rsmd = rst.getMetaData();
while (rst.next()) {
HashMap temp = new HashMap();
for (int i = 0; i < count; i++) {
String columnName = rsmd.getColumnName(i + 1);
columnName = columnName.toUpperCase();
temp.put(columnName, (rst.getString(i + 1)));
}
arraylist.add(temp);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
}
return arraylist;
}
/**
*
* @param proName PRODUCRE NAME
* @param cmdParams PARAMS
* @param con CONNECIOTN
* @return 0:success -1 :failed
* TODO EXECUTE NO QUERY PRODUCRE ,EXAMPLE update ,delte ,insert ADD SO ON OPERATION.
*/
public int execCSTMTNonQuery(String proName, CstmtUtil[] cmdParams,
java.sql.Connection con) {
int result = 0;
if (con == null) {
log.debug("CONNECTION IS NULL!");
return -1;
}
CallableStatement cstmt = null;
String callString = "";
try {
int cnt = 0;
if (cmdParams != null) {
cnt = cmdParams.length;
}
for (int i = 0; i < cnt; i++) {
if (callString.length() == 0) {
callString = "?";
} else {
callString = callString + ",?";
}
}
callString = "{call " + proName + "(" + callString + ")}";
cstmt = con.prepareCall(callString);
for (int i = 0; i < cnt; i++) {
CstmtUtil cmdParam = cmdParams[i];
if (cmdParam.Direction == CstmtUtil.OUTPUT) {
cstmt.registerOutParameter(i + 1, cmdParam.sqlType);
} else {
String strValue = cmdParam.value.toString();
if ((cmdParam.parameterType).equals(CstmtUtil.BYTE)) {
cstmt.setByte(i + 1, Byte.parseByte(strValue));
} else if ((cmdParam.parameterType)
.equals(CstmtUtil.DOUBLE)) {
cstmt.setDouble(i + 1, Double.parseDouble(strValue));
} else if ((cmdParam.parameterType).equals(CstmtUtil.FLOAT)) {
cstmt.setFloat(i + 1, Float.parseFloat(strValue));
} else if ((cmdParam.parameterType).equals(CstmtUtil.INT)) {
cstmt.setInt(i + 1, Integer.parseInt(strValue));
} else if ((cmdParam.parameterType).equals(CstmtUtil.LONG)) {
cstmt.setLong(i + 1, Long.parseLong(strValue));
} else if ((cmdParam.parameterType).equals(CstmtUtil.SHORT)) {
cstmt.setShort(i + 1, Short.parseShort(strValue));
} else if ((cmdParam.parameterType)
.equals(CstmtUtil.STRING)) {
cstmt.setString(i + 1, strValue);
}
}
}
cstmt.execute();
for (int i = 0; i < cnt; i++) {
CstmtUtil cmdParam = cmdParams[i];
if (cmdParam.Direction == CstmtUtil.OUTPUT) {
if (cmdParam.sqlType == oracle.jdbc.OracleTypes.CURSOR) {
cmdParam.outvalue = (ResultSet) cstmt.getObject(2);
} else if (cmdParam.sqlType == Types.DOUBLE) {
cmdParam.outvalue = new Double(cstmt.getDouble(i + 1));
} else if (cmdParam.sqlType == Types.FLOAT) {
cmdParam.outvalue = new Float(cstmt.getFloat(i + 1));
} else if (cmdParam.sqlType == Types.INTEGER) {
cmdParam.outvalue = new Integer(cstmt.getInt(i + 1));
} else if (cmdParam.sqlType == Types.VARCHAR) {
if (cstmt.getString(i + 1) == null)
cmdParam.outvalue = "";
else
cmdParam.outvalue = cstmt.getString(i + 1);
} else {
cmdParam.outvalue = "";
}
} else {
cmdParam.outvalue = "";
}
}
} catch (Exception e) {
String strLog = "execCSTMTNonQuery" + callString;
for (int i = 0; i < cmdParams.length; i++) {
strLog = strLog + "\r\n" + (i + 1);
strLog = strLog + "inout=" + cmdParams[i].Direction;
strLog = strLog + "parameterType=" + cmdParams[i].parameterType;
strLog = strLog + "sqlType=" + cmdParams[i].sqlType;
strLog = strLog + "in=" + cmdParams[i].value;
strLog = strLog + " out=" + cmdParams[i].outvalue;
}
result = -1;
log.error("EXECUTE [" + proName + "][" + strLog + "]", e);
} finally {
try {
cstmt.close();
} catch (Exception ex) {
log.error("CLOSE CALLABLESTEMT ", ex);
result = -1;
} finally {
cstmt = null;
}
}
return result;
}
/**
*
* @param proName PROCEDURE NAME
* @param cmdParams PARAMS
* @param con
* @return
* TODO ONLY DO QUERY OPERTAION.
*/
public ArrayList execCSTMTQuery(String proName, CstmtUtil[] cmdParams,
java.sql.Connection con) {
int result = -1;
ArrayList rs = null;
if (con == null) {
return null;
}
CallableStatement cstmt = null;
String callString = "";
try {
int cnt = 0;
if (cmdParams != null) {
cnt = cmdParams.length;
}
for (int i = 0; i < cnt; i++) {
if (callString.length() == 0) {
callString = "?";
} else {
callString = callString + ",?";
}
}
callString = "{call " + proName + "(" + callString + ")}";
//System.out.println("callString="+ con + callString);
cstmt = con.prepareCall(callString);
for (int i = 0; i < cnt; i++) {
CstmtUtil cmdParam = cmdParams[i];
if (cmdParam.Direction == CstmtUtil.OUTPUT) {
cstmt.registerOutParameter(i + 1, cmdParam.sqlType);
} else {
String strValue = cmdParam.value.toString();
if ((cmdParam.parameterType).equals(CstmtUtil.BYTE)) {
cstmt.setByte(i + 1, Byte.parseByte(strValue));
} else if ((cmdParam.parameterType)
.equals(CstmtUtil.DOUBLE)) {
cstmt.setDouble(i + 1, Double.parseDouble(strValue));
} else if ((cmdParam.parameterType).equals(CstmtUtil.FLOAT)) {
cstmt.setFloat(i + 1, Float.parseFloat(strValue));
} else if ((cmdParam.parameterType).equals(CstmtUtil.INT)) {
cstmt.setInt(i + 1, Integer.parseInt(strValue));
} else if ((cmdParam.parameterType).equals(CstmtUtil.LONG)) {
cstmt.setLong(i + 1, Long.parseLong(strValue));
} else if ((cmdParam.parameterType).equals(CstmtUtil.SHORT)) {
cstmt.setShort(i + 1, Short.parseShort(strValue));
} else if ((cmdParam.parameterType)
.equals(CstmtUtil.STRING)) {
cstmt.setString(i + 1, strValue);
}
}
}
cstmt.execute();
for (int i = 0; i < cnt; i++) {
CstmtUtil cmdParam = cmdParams[i];
if (cmdParam.Direction == CstmtUtil.OUTPUT) {
if (cmdParam.sqlType == oracle.jdbc.OracleTypes.CURSOR) {
ResultSet rst = (ResultSet) cstmt.getObject(i + 1);
rs = rstToArrayListMap((ResultSet) rst);
rst.close();
} else if (cmdParam.sqlType == Types.DOUBLE) {
cmdParam.outvalue = new Double(cstmt.getDouble(i + 1));
} else if (cmdParam.sqlType == Types.FLOAT) {
cmdParam.outvalue = new Float(cstmt.getFloat(i + 1));
} else if (cmdParam.sqlType == Types.INTEGER) {
cmdParam.outvalue = new Integer(cstmt.getInt(i + 1));
} else if (cmdParam.sqlType == Types.VARCHAR) {
cmdParam.outvalue = cstmt.getString(i + 1);
}
}
}
} catch (Exception e) {
String strLog = "execCSTMTNonQuery" + callString;
for (int i = 0; i < cmdParams.length; i++) {
strLog = strLog + "\r\n????" + (i + 1);
strLog = strLog + "inout=" + cmdParams[i].Direction;
strLog = strLog + "parameterType=" + cmdParams[i].parameterType;
strLog = strLog + "sqlType=" + cmdParams[i].sqlType;
strLog = strLog + "in=" + cmdParams[i].value;
strLog = strLog + " out=" + cmdParams[i].outvalue;
}
} finally {
try {
cstmt.close();
} catch (Exception ex) {
log.error("CLOSE CALLABLESTAMENT ", ex);
}
cstmt = null;
}
return rs;
}
public int executeProc(final String callSql, Collection collect,
final Connection conn) throws SQLException {
Connection con = null;
CallableStatement callstmt = null;
int ret = -1;
try {
if (conn == null) {
return -1;
}
callstmt = con.prepareCall(callSql);
try {
callstmt = parseParam(callstmt, collect, 0);
ret = callstmt.executeUpdate();
} catch (SQLException ex) {
log.error("execute " + callSql + "failed", ex);
throw ex;
}
return ret;
} finally {
collect = null;
callstmt.close();
callstmt = null;
}
}
/**
*
* @param callstmt
* @param collect
* @param temp
* @return
* @throws NumberFormatException
* @throws SQLException
* TODO common organiztion PARAMS
*/
private CallableStatement parseParam(final CallableStatement callstmt,
final Collection collect, int temp) throws NumberFormatException,
SQLException {
int i = temp;
String paraList = "";
if (collect != null) {
Iterator it = collect.iterator();
while (it.hasNext()) {
int index = i + 1;
String para = it.next().toString();
if (para.indexOf(PINT) >= 0 && para.indexOf(OUTPUT) < 0) {
callstmt.setInt(index, Integer.parseInt(para.substring(3)));
paraList += PINT + para.substring(3) + ", ";
} else if (para.indexOf(PSTRING) >= 0
&& para.indexOf(OUTPUT) < 0) {
callstmt.setString(index, para.substring(6));
paraList += PSTRING + para.substring(6) + ", ";
} else if (para.indexOf(PBOOLEAN) >= 0
&& para.indexOf(OUTPUT) < 0) {
callstmt.setBoolean(index, Boolean.getBoolean(para
.substring(7)));
paraList += PBOOLEAN + para.substring(7) + ", ";
} else if (para.indexOf(PDOUBLE) >= 0
&& para.indexOf(OUTPUT) < 0) {
callstmt.setDouble(index, Double.parseDouble(para
.substring(6)));
paraList += PDOUBLE + para.substring(6) + ", ";
} else if (para.indexOf(PFLOAT) >= 0
&& para.indexOf(OUTPUT) < 0) {
callstmt.setFloat(index, Float
.parseFloat(para.substring(5)));
paraList += PFLOAT + para.substring(5) + ", ";
} else if (para.indexOf(PLONG) >= 0 && para.indexOf(OUTPUT) < 0) {
callstmt.setLong(index, Long.parseLong(para.substring(4)));
paraList += PLONG + para.substring(4) + ", ";
}
i++;
}
it = null;
}
paraList = null;
return callstmt;
}
/**
*
* @param collect PARAMS
* @param con CONNECT
* @return EXECUTE ARRAY
* @throws SQLException
* TODO
*/
public int[] execute(final Collection collect, final Connection con)
throws SQLException {
if (collect == null || collect.isEmpty()) {
return null;
}
Statement stmt = null;
int[] ret = new int[collect.size()];
try {
stmt = con.createStatement();
String sql = "";
try {
int i = 0;
final Iterator it = collect.iterator();
while (it.hasNext()) {
sql = it.next().toString();
ret[i++] = stmt.executeUpdate(sql);
}
} catch (SQLException ex) {
throw ex;
}
return ret;
} finally {
stmt.close();
stmt = null;
}
}
/**
*
* @param collect
* @return
* @throws SQLException
* TODO
*/
public int[] execute(final Collection collect) throws SQLException {
Connection conn = JdbcServiceHelper.getConnection();
;
if (conn == null) {
throw new IllegalArgumentException(errPrefix + " : conn is null!");
}
if (collect == null || collect.isEmpty()) {
return null;
}
Statement stmt = null;
int[] ret = new int[collect.size()];
try {
stmt = conn.createStatement();
if (conn.getAutoCommit()) {
conn.setAutoCommit(false);
}
String sql = "";
try {
int i = 0;
final Iterator it = collect.iterator();
while (it.hasNext()) {
sql = it.next().toString();
ret[i++] = stmt.executeUpdate(sql);
log.debug(sql + ";\n");
}
conn.commit();
} catch (SQLException ex) {
log.debug("execute:");
try {
conn.rollback();
} catch (SQLException e) {
log.error("rollback connection ", ex);
e.printStackTrace();
}
throw ex;
}
return ret;
} finally {
freeResource(null, stmt, conn);
}
}
/**
*
* @param rs
* @param stmt
* TODO
*/
private static void freeResource(final ResultSet rs, final Statement stmt) {
freeResource(rs, stmt, null);
}
/**
*
* @param rs ResultSet
* @param stmt Statement
* @param con Connection
*/
public static void freeResource(ResultSet rs, Statement stmt, Connection con) //aban change private to public
{
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
//con = null;
}
}
private Map query(final String callSql, final Connection con,
Collection collect, int outPutIndex) throws SQLException {
CallableStatement stmt = null;
Map result = new HashMap();
Map outMap = new HashMap();
try {
if (con == null) {
return null;
}
stmt = con.prepareCall(callSql);
try {
stmt = parseParam(stmt, collect, 0);
registerOutParameter(stmt, collect, outMap, outPutIndex);
stmt.execute();
putParamToCollect(stmt, outMap, result, outPutIndex);
} catch (SQLException ex) {
throw ex;
}
} finally {
collect = null;
outMap = null;
stmt.close();
stmt = null;
}
return result;
}
/**
*
* @param callstmt
* @param outMap
* @param result
* @param outPutIndex
* TODO
*/
private void putParamToCollect(CallableStatement callstmt, Map outMap,
Map result, int outPutIndex) {
// TODO Auto-generated method stub
int size = outMap.size();
String value = null;
try {
int registerIndex;
for (int i = 0; i < size; i++) {
String in = String.valueOf(i);
value = (String) outMap.get(in);
registerIndex = outPutIndex + i;
if (PINT.equals(value)) {
result.put(in, String.valueOf(callstmt
.getInt(registerIndex)));
} else if (PSTRING.equals(value)) {
result.put(in, String.valueOf(callstmt
.getString(registerIndex) == null ? "" : callstmt
.getString(registerIndex)));
} else if (PBOOLEAN.equals(value)) {
result.put(in, String.valueOf(callstmt
.getBoolean(registerIndex)));
} else if (PDOUBLE.equals(value)) {
result.put(in, String.valueOf(callstmt
.getDouble(registerIndex)));
} else if (PFLOAT.equals(value)) {
result.put(in, String.valueOf(callstmt
.getFloat(registerIndex)));
} else if (PLONG.equals(value)) {
result.put(in, String.valueOf(callstmt
.getLong(registerIndex)));
}
}
} catch (SQLException ex) {
log.error("JDBCִPARAM TO COLLECT", ex);
}
}
/**
* REGISTER OUT PARAMETER
*/
private void registerOutParameter(CallableStatement callstmt,
Collection collect, Map outMap, int outPutIndex) {
try {
if (collect != null) {
Iterator it = collect.iterator();
int j = 0;
int registerIndex;
while (it.hasNext()) {
registerIndex = outPutIndex + j;
String para = it.next().toString();
if (para.indexOf(PINT) >= 0 && para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.INTEGER);
outMap.put(String.valueOf(j++), PINT);
} else if (para.indexOf(PSTRING) >= 0
&& para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.VARCHAR);
outMap.put(String.valueOf(j++), PSTRING);
} else if (para.indexOf(PBOOLEAN) >= 0
&& para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.BOOLEAN);
outMap.put(String.valueOf(j++), PBOOLEAN);
} else if (para.indexOf(PDOUBLE) >= 0
&& para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.DOUBLE);
outMap.put(String.valueOf(j++), PDOUBLE);
} else if (para.indexOf(PFLOAT) >= 0
&& para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.FLOAT);
outMap.put(String.valueOf(j++), PFLOAT);
} else if (para.indexOf(PLONG) >= 0
&& para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.LONGVARBINARY);
outMap.put(String.valueOf(j++), PLONG);
}
}
}
} catch (SQLException ex) {
log.error("JDBC REGISTER PARAM ", ex);
}
}
/**
*
* @param rs
* @return
* @throws SQLException
* TODO
*/
private ArrayList resToList(final ResultSet rs) throws SQLException {
final int column = rs.getMetaData().getColumnCount();
final LinkedList list = new LinkedList();
String temp;
while (rs.next()) {
final ArrayList subList = new ArrayList(column);
for (int i = 1; i <= column; i++) {
temp = rs.getString(i);
if (temp == null) {
temp = "";
}
subList.add(temp.trim());
}
list.add(subList);
}
return new ArrayList(list);
}
/**
*
* @param rs
* @return
* @throws SQLException
* TODO
*/
private ArrayList resToOneList(final ResultSet rs) throws SQLException {
final int column = rs.getMetaData().getColumnCount();
final LinkedList list = new LinkedList();
String temp;
while (rs.next()) {
for (int i = 1; i <= column; i++) {
temp = rs.getString(i);
if (temp == null) {
temp = "";
}
list.add(temp.trim());
}
}
return new ArrayList(list);
}
/**
*
* @param rs
* @param beginIndex
* @param num
* @param size
* @return
* @throws SQLException
* TODO
*/
private ArrayList resToList(final ResultSet rs, final int beginIndex,
final int num, final int size) throws SQLException {
final int column = rs.getMetaData().getColumnCount();
final ArrayList list = new ArrayList(size);
String temp;
rs.absolute(beginIndex);
for (int i = beginIndex; i < beginIndex + num; i++) {
final ArrayList subList = new ArrayList(column);
for (int j = 1; j <= column; j++) {
temp = rs.getString(j);
subList.add(temp == null ? "" : temp.trim());
}
list.add(subList);
rs.next();
}
return list;
}
/**
*
* @param rs
* @param beginIndex
* @param num
* @param size
* @return
* @throws SQLException
* TODO
*/
private ArrayList resToOneList(final ResultSet rs, final int beginIndex,
final int num, final int size) throws SQLException {
final int column = rs.getMetaData().getColumnCount();
final ArrayList list = new ArrayList(size * column);
String temp;
rs.absolute(beginIndex);
for (int i = beginIndex; i < beginIndex + num; i++) {
for (int j = 1; j <= column; j++) {
temp = rs.getString(j);
list.add(temp == null ? "" : temp.trim());
}
rs.next();
}
return list;
}
public ArrayList query(final String sql) throws SQLException
{
ArrayList rst = null;
try
{
java.sql.Connection con = getConnection();
rst = query(sql,con,-1,-1,0);
try
{
con.close();
}catch(Exception ex)
{
ex.printStackTrace();
}
con=null;//*/
}
catch(Exception e)
{
e.printStackTrace();
}
return rst;
}
/**
* <p>查询数据库</p>
*
* @param sql 查询语句
* @param conn 数据库的连接
* @param type 0表示两层结构,1表示一层结构,2表示只取第一行
* @param beginIndex 起始位置,从1开始,不用担心越界
* @param num 个数,不用担心越界
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException SQL异常
*/
@SuppressWarnings("unchecked")
public ArrayList query(final String sql, final Connection con, final int beginIndex, final int num, final int type)
throws SQLException
{
final String exsql = sql.trim();
int begin = beginIndex;
int count = num;
boolean needPosition = false;
if (begin != -1 || count != -1) {
needPosition = true;
}
ArrayList list = new ArrayList();
Statement stmt = null;
ResultSet rs = null;
try {
if(con == null)
{
return null;
}
if (needPosition) {
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
} else {
stmt = con.createStatement();
}
rs = stmt.executeQuery(exsql);
if (rs == null)
{
return list;
}
int size = -1;
if (needPosition)
{
if (!rs.next()) {
return list;
}
rs.last();
size = rs.getRow();
rs.first();
if (begin < 1) {
begin = 1;
}
if (count == -1 || begin + count > size + 1) {
count = size - begin + 1;
}
}
if (type == 0)
{
list = needPosition ? resToList(rs, begin, count, size) : resToList(rs);
} else if (type == 1)
{
list = needPosition ? resToOneList(rs, begin, count, size) : resToOneList(rs);
} else if (type == 2)
{
list = resToFirstList(rs);
}
return list;
} catch (SQLException ex) {
log.error("查询数据时出现异常:.\r\nmyQuery:sql = \r\n" + exsql, ex);
throw ex;
} finally
{
stmt.close();
stmt = null;
}
}
/**
* <p>把ResultSet中第一行的信息按顺序全部复制到ArrayList中,</p>
* <p>一层结构。</p>
* <p>调用此方式时要注意关闭ResultSet类型的rs和</p>
* <p>Statement类型的statement,不推荐直接使用</p>
*
* @param rs 要处理的集合
* @return 处理后的ArrayList
* @throws SQLException
*/
private ArrayList resToFirstList(final ResultSet rs) throws SQLException
{
final int column = rs.getMetaData().getColumnCount();
final ArrayList list = new ArrayList(1);
String temp;
if (!rs.next()) {
return new ArrayList();
}
for (int i = 1; i <= column; i++) {
//将结果集按顺序存入向量
temp = rs.getString(i);
list.add(temp == null ? "" : temp.trim());
}
return list;
}
/**
* DIRECT GET CONNECTION
* @return
*/
public Connection getConnection(){
Connection conn = null;
try {
/***
Class.forName("oracle.jdbc.driver.OracleDriver");
String sqlServerUrl = "jdbc:oracle:thin:@10.112.5.179:1521:WHSJ";
conn = DriverManager.getConnection(sqlServerUrl, "whsj",
"whsj");
**/
conn = JdbcServiceHelper.getConnection();
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
}
package com.topcheer.file.excel;
public class CstmtUtil{
public static String BYTE = byte.class.toString();
public static String DOUBLE =double.class.toString();
public static String FLOAT = float.class.toString();
public static String INT = int.class.toString();
public static String LONG = long.class.toString();
public static String SHORT = short.class.toString();
public static String STRING = String.class.toString();
public static String OBJECT = "OBJECT";
public static int IN = 0;
public static int OUTPUT = 2;
public Object value;
public String parameterType;
public int sqlType;
public int Direction;
public Object outvalue;
public CstmtUtil(Object value, String parameterType,int Direction,int sqlType){
this.value=value;
this.parameterType=parameterType;
this.Direction=Direction;
this.sqlType=sqlType;
}
public CstmtUtil(Object value, String parameterType){
this.value = value;
this.parameterType = parameterType;
this.Direction = IN;
}
public CstmtUtil(int sqlType){
this.Direction = OUTPUT;
this.sqlType = sqlType;
}
}
----------------------------------------------------------------------------------------------------------------------------------
import java.io.BufferedReader;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Map;
import org.apache.log4j.Logger;
import org.topcheer.common.getdata.service.DataProcess;
/**
* 这是一个提供了操作数据库方法的类 例: String sql="select * from table"; ArrayList
* list=DBSql.query(sql);
* 调用query()方法传入sql语句来执行数据库的操作返回的是ArrayList.ArrayList中的一个元素就是数据库中的一行
* 这个只是此类里面的简单的方法应用还有一些方法是带有事务处理的
*/
public final class DBSql {
private static final Logger LogUtil = Logger.getLogger("DBSql");
private static final String errPrefix = DBSql.class.getName();
private static final String PINT = "int";
private static final String PSTRING = "String";
private static final String PLONG = "long";
private static final String PFLOAT = "float";
private static final String PDOUBLE = "double";
private static final String PBOOLEAN = "boolean";
private static final String OUTPUT = "OUTPUT";
/** 输出参数 */
private static final String RESULT = "Reuslt";
private static final String PDATE = "date";
private DBSql() {
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException
* SQL异常
*/
public static ArrayList query(final String sql) throws SQLException {
return myQuery(sql, null, 0);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @param beginIndex
* 起始位置,从1开始,不用担心越界
* @param num
* 个数,不用担心越界
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException
* SQL异常
*/
public static ArrayList query(final String sql, final int beginIndex,
final int num) throws SQLException {
return myQuery(sql, null, beginIndex, num, 0);
}
/**
* <p>
* 查询数据库
* </p>
* 分页查询时使用
*
* @param sql
* 查询语句
* @param curPage
* 当前页数
* @param num
* 每页显示的数量
* @return
* @throws SQLException
*/
public static ArrayList queryPage(final String sql, final int curPage,
final int num) throws SQLException {
int beginIndex = num * (curPage - 1) + 1;
return myQuery(sql, null, beginIndex, num, 0);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @param con
* 数据库连接
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException
* SQL异常
*/
public static ArrayList query(final String sql, final Connection con)
throws SQLException {
return myQuery(sql, con, 0);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @param con
* 数据库连接
* @param beginIndex
* 起始位置,从1开始,不用担心越界
* @param num
* 个数,不用担心越界
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException
* SQL异常
*/
public static ArrayList query(final String sql, final Connection con,
final int beginIndex, final int num) throws SQLException {
return myQuery(sql, con, beginIndex, num, 0);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @return ArrayList类型的查询结果,按查询结果顺序依次放入一个List中。
* @throws SQLException
* SQL异常
*/
public static ArrayList queryOneList(final String sql) throws SQLException {
return myQuery(sql, null, 1);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @param beginIndex
* 起始位置,从1开始,不用担心越界
* @param num
* 个数,不用担心越界
* @return ArrayList类型的查询结果,按查询结果顺序依次放入一个List中。
* @throws SQLException
* SQL异常
*/
public static ArrayList queryOneList(final String sql,
final int beginIndex, final int num) throws SQLException {
return myQuery(sql, null, beginIndex, num, 1);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @param con
* 数据库连接
* @return ArrayList类型的查询结果,按查询结果顺序依次放入一个List中。
* @throws SQLException
* SQL异常
*/
public static ArrayList queryOneList(final String sql, final Connection con)
throws SQLException {
return myQuery(sql, con, 1);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @param con
* 数据库连接
* @param beginIndex
* 起始位置,从1开始,不用担心越界
* @param num
* 个数,不用担心越界
* @return ArrayList类型的查询结果,按查询结果顺序依次放入一个List中。
* @throws SQLException
* SQL异常
*/
public static ArrayList queryOneList(final String sql,
final Connection con, final int beginIndex, final int num)
throws SQLException {
return myQuery(sql, con, beginIndex, num, 1);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @return ArrayList类型的查询结果,只取第一行的记录。
* @throws SQLException
* SQL异常
*/
public static ArrayList queryFirstRow(final String sql) throws SQLException {
return myQuery(sql, null, 2);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @param con
* 数据库连接
* @return ArrayList类型的查询结果,只取第一行的记录。
* @throws SQLException
* SQL异常
*/
public static ArrayList queryFirstRow(final String sql, final Connection con)
throws SQLException {
return myQuery(sql, con, 2);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @param con
* 数据库的连接
* @param type
* 0表示两层结构,1表示一层结构,2表示只取第一行
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException
* SQL异常
*/
private static ArrayList myQuery(final String sql, final Connection con,
final int type) throws SQLException {
return myQuery(sql, con, -1, -1, type);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @param conn
* 数据库的连接
* @param type
* 0表示两层结构,1表示一层结构,2表示只取第一行
* @param beginIndex
* 起始位置,从1开始,不用担心越界
* @param num
* 个数,不用担心越界
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException
* SQL异常
*/
private static ArrayList myQuery(final String sql, final Connection conn,
final int beginIndex, final int num, final int type)
throws SQLException {
final String exsql = sql.trim();
int begin = beginIndex;
int count = num;
boolean needPosition = false;
if (begin != -1 || count != -1) {
needPosition = true;
}
ArrayList list = new ArrayList();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = DataProcess.getDataSource().getConnection();
if (needPosition) {
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
} else {
stmt = con.createStatement();
}
rs = stmt.executeQuery(exsql);
// aban add start
// System.out.print(exsql+";\n");
// aban add end
if (rs == null) {
return list;
}
int size = -1;
if (needPosition) {
if (!rs.next()) {
return list;
}
rs.last();
size = rs.getRow();
rs.first();
if (begin < 1) {
begin = 1;
}
if (count == -1 || begin + count > size + 1) {
count = size - begin + 1;
}
}
if (type == 0) {
list = needPosition ? resToList(rs, begin, count, size)
: resToList(rs);
} else if (type == 1) {
list = needPosition ? resToOneList(rs, begin, count, size)
: resToOneList(rs);
} else if (type == 2) {
list = resToFirstList(rs);
}
return list;
} catch (SQLException ex) {
LogUtil.error(ex);
// System.out.println(" : myQuery:" + ex + ".\r\nmyQuery:sql = \r\n"
// + exsql);
throw ex;
} finally {
if (con != null) {
freeResource(rs, stmt, con);
} else {
freeResource(rs, stmt);
}
}
}
/**
* <p>
* 执行插入、更新和删除操作的,某一条SQL语句,支持事务的回滚
* </p>
*
* @param sql
* sql语句
* @return 执行结果
* @throws SQLException
* SQL异常
*/
public static int execute(final String sql) throws SQLException {
final LinkedList list = new LinkedList();
list.add(sql);
return execute(list)[0];
}
/**
* <p>
* 执行插入、更新和删除操作的,某一条SQL语句,支持事务的回滚
* </p>
*
* @param sql
* sql语句
* @param con
* 数据库连接
* @return 执行结果
* @throws SQLException
* SQL异常
*/
public static int execute(final String sql, final Connection con)
throws SQLException {
final LinkedList list = new LinkedList();
list.add(sql);
return execute(list, con, true)[0];
}
/**
* <p>
* 执行插入、更新和删除操作的,某一条SQL语句,支持事务的回滚
* </p>
*
* @param sql
* sql语句
* @param con
* 数据库连接
* @param needCommit
* 需要提交
* @return 执行结果
* @throws SQLException
* SQL异常
*/
public static int execute(final String sql, final Connection con,
final boolean needCommit) throws SQLException {
final LinkedList list = new LinkedList();
list.add(sql);
return execute(list, con, needCommit)[0];
}
/**
* <p>
* 执行插入、更新和删除操作的若干条SQL语句,支持事务的回滚,
* </p>
* <p>
* Collection是一个接口,凡是实现了这个接口的都可以使用,
* </p>
* <p>
* 比如说LinkedList、ArrayList、List、HashMap,Vector和HashTable
* </p>
* <p>
* 不再推荐使用,LinkedList的添加删除性能非常好,本质上是个双向链表,
* </p>
* <p>
* 可以双向搜索。
* </p>
*
* @param collect
* SQL语句集
* @return 执行结果
* @throws SQLException
* SQL异常
*/
public static int[] execute(final Collection collect) throws SQLException {
return execute(collect, null, true);
}
/**
* <p>
* 执行插入、更新和删除操作的若干条SQL语句,支持事务的回滚,
* </p>
* <p>
* Collection是一个接口,凡是实现了这个接口的都可以使用,
* </p>
* <p>
* 比如说LinkedList、ArrayList、List、HashMap,Vector和HashTable
* </p>
* <p>
* 不再推荐使用,LinkedList的添加删除性能非常好,本质上是个双向链表,
* </p>
* <p>
* 可以双向搜索。
* </p>
*
* @param collect
* SQL语句集
* @param con
* 数据库连接
* @return 执行结果
* @throws SQLException
*/
public static int[] execute(final Collection collect, final Connection con)
throws SQLException {
return execute(collect, con, true);
}
/**
* <p>
* 执行插入、更新和删除操作的若干条SQL语句,支持事务的回滚,
* </p>
* <p>
* Collection是一个接口,凡是实现了这个接口的都可以使用,
* </p>
* <p>
* 比如说LinkedList、ArrayList、List、HashMap,Vector和HashTable
* </p>
* <p>
* 不再推荐使用,LinkedList的添加删除性能非常好,本质上是个双向链表,
* </p>
* <p>
* 可以双向搜索。
* </p>
*
* @param collect
* SQL语句集
* @param conn
* 数据库连接
* @param needCommit
* 需要提交
* @return 执行结果
* @throws IllegalArgumentException
* 如果不提交而且参数con为空,将抛出IllegalArgumentException
* @throws SQLException
*/
private static int[] execute(final Collection collect,
final Connection conn, final boolean needCommit)
throws SQLException {
if (!needCommit && conn == null) {
throw new IllegalArgumentException(errPrefix + " : conn is null!");
}
if (collect == null || collect.isEmpty()) {
return null;
}
Connection con = null;
Statement stmt = null;
int[] ret = new int[collect.size()];
try {
con = DataProcess.getDataSource().getConnection();
stmt = con.createStatement();
if (con.getAutoCommit()) {
con.setAutoCommit(false);
}
String sql = "";
try {
int i = 0;
final Iterator it = collect.iterator();
while (it.hasNext()) {
sql = it.next().toString();
System.out.print(sql+";\n");
ret[i++] = stmt.executeUpdate(sql);
// aban add start
// System.out.print(sql+";\n");
// aban add end
}
if (needCommit) {
con.commit();
}
} catch (SQLException ex) {
if (needCommit) {
// 事务回滚
LogUtil.debug("execute:开始事务回滚");
try {
con.rollback();
} catch (SQLException e) {
LogUtil.error("DBPool.execute() 出现异常 : 事务回滚中断", e);
}
}
LogUtil
.debug("execute:" + ex + ".\r\nexecute:sql = \r\n"
+ sql);
// System.out.println(errPrefix + " : execute:" + ex +
// ".\r\nexecute:sql = \r\n" + sql);
throw ex;
}
if (needCommit) {
con.setAutoCommit(true);
}
return ret;
} finally {
if (con != null) {
freeResource(null, stmt, con);
} else {
freeResource(null, stmt);
}
}
}
/**
* <p>
* 执行储存过程 如果需要事务请在存储过程里面实现
* </p>
* <p>
* 比如说LinkedList、ArrayList、List、HashMap,Vector和HashTable
* </p>
* <p>
* 不再推荐使用,LinkedList的添加删除性能非常好,本质上是个双向链表,
* </p>
* <p>
* 可以双向搜索。
* </p>
*
* @param callSql
* 要执行的存储过程语句:
* <p>
* 存储过程的调用方式是 "{ call ProcedureName() }"
* </p>
* @return 执行结果
* @throws IllegalArgumentException
* 如果不提交而且参数con为空,将抛出IllegalArgumentException
* @throws SQLException
*/
public static int executeProc(final String callSql) throws SQLException {
return executeProc(callSql, null, null);
}
/**
* <p>
* 执行储存过程 如果需要事务请在存储过程里面实现
* </p>
* <p>
* 比如说LinkedList、ArrayList、List、HashMap,Vector和HashTable
* </p>
* <p>
* 不再推荐使用,LinkedList的添加删除性能非常好,本质上是个双向链表,
* </p>
* <p>
* 可以双向搜索。
* </p>
*
* @param callSql
* 要执行的存储过程语句:
* <p>
* 存储过程的调用方式是 "{ call ProcedureName(?) }"
* </p>
* <p>
* 括号中的 “?” 表示 需要带进来的参数 可以带多个不同类型或相同类型的参数
* </p>
*
* @param collect
* 执行存储过程的时候需要带进来的参数列表:
* <p>
* 根据括号中的 “?” 多少来确定需要传进来多少
* </p>
* <p>
* 参数的类型 可以是 int, String, boolean, long, float, double
* </p>
* <p>
* 传参方式是 集合中是这样存储的: index0 = int1 代表传进来的是 int类型 值为1 其他类型也都是一样的方式
* </p>
*
* @return 执行结果
* @throws IllegalArgumentException
* 如果不提交而且参数con为空,将抛出IllegalArgumentException
* @throws SQLException
*/
public static int executeProc(final String callSql, final Collection collect)
throws SQLException {
return executeProc(callSql, null, collect);
}
/**
*
* @param callSql
* 要执行的存储过程语句:
* <p>
* 存储过程的调用方式是 "{ call ProcedureName(?) }"
* </p>
* <p>
* 括号中的 “?” 表示 需要带进来的参数 可以带多个不同类型或相同类型的参数
* </p>
*
* @param collect
* 执行存储过程的时候需要带进来的参数列表:
* <p>
* 根据括号中的 “?” 多少来确定需要传进来多少
* </p>
* <p>
* 参数的类型 可以是 int, String, boolean, long, float, double
* </p>
* <p>
* 传参方式是 集合中是这样存储的: index0 = int1 代表传进来的是 int类型 值为1 其他类型也都是一样的方式
* </p>
*
* @param outPutIndex
* 第一个输出参数在存储过程中的(位置)
* @return 执行结果
* @throws IllegalArgumentException
* 如果不提交而且参数con为空,将抛出IllegalArgumentException
* @throws SQLException
* 2008-3-24 李增群
*/
public static Map queryProcReturnValue(final String callSql,
final Collection collect, int outPutIndex) throws SQLException {
return queryProcReturnValue(callSql, null, collect, outPutIndex,
java.sql.Types.CLOB);
}
/**
*
* @param callSql
* @param collect
* @param outPutIndex
* @return
* @throws SQLException
*/
public static Map queryPRValue(final String callSql,
final Collection collect, int outPutIndex) throws SQLException {
return queryProcReturnValue(callSql, null, collect, outPutIndex,
java.sql.Types.INTEGER);
}
/**
* <p>
* 执行储存过程 如果需要事务请在存储过程里面实现
* </p>
* <p>
* 比如说LinkedList、ArrayList、List、HashMap,Vector和HashTable
* </p>
* <p>
* 不再推荐使用,LinkedList的添加删除性能非常好,本质上是个双向链表,
* </p>
* <p>
* 可以双向搜索。
* </p>
*
* @param callSql
* 要执行的存储过程语句:
* <p>
* 存储过程的调用方式是 "{ call ProcedureName(?) }"
* </p>
* <p>
* 括号中的 “?” 表示 需要带进来的参数 可以带多个不同类型或相同类型的参数
* </p>
*
* @param collect
* 执行存储过程的时候需要带进来的参数列表:
* <p>
* 根据括号中的 “?” 多少来确定需要传进来多少
* </p>
* <p>
* 参数的类型 可以是 int, String, boolean, long, float, double
* </p>
* <p>
* 传参方式是 集合中是这样存储的: index0 = int1 代表传进来的是 int类型 值为1 其他类型也都是一样的方式
* </p>
*
* @param conn
* 数据库连接
* @return 执行结果
* @throws IllegalArgumentException
* 如果不提交而且参数con为空,将抛出IllegalArgumentException
* @throws SQLException
*/
private static int executeProc(final String callSql, final Connection conn,
Collection collect) throws SQLException {
return execute(callSql, conn, collect).code;
}
public static SPParam execute(final String callSql, Collection collect)
throws SQLException {
return execute(callSql, null, collect);
}
/**
* <p>
* 执行储存过程 如果需要事务请在存储过程里面实现, 返回值是Procuder中的返回值 返回的值是1, Int 2, String
* </p>
* <p>
* 比如说LinkedList、ArrayList、List、HashMap,Vector和HashTable
* </p>
* <p>
* 不再推荐使用,LinkedList的添加删除性能非常好,本质上是个双向链表,
* </p>
* <p>
* 可以双向搜索。
* </p>
*
* @param callSql
* 要执行的存储过程语句:
* <p>
* 存储过程的调用方式是 "{ call ProcedureName(?) }"
* </p>
* <p>
* 括号中的 “?” 表示 需要带进来的参数 可以带多个不同类型或相同类型的参数
* </p>
*
* @param collect
* 执行存储过程的时候需要带进来的参数列表:
* <p>
* 根据括号中的 “?” 多少来确定需要传进来多少
* </p>
* <p>
* 参数的类型 可以是 int, String, boolean, long, float, double
* </p>
* <p>
* 传参方式是 集合中是这样存储的: index0 = int1 代表传进来的是 int类型 值为1 其他类型也都是一样的方式
* </p>
*
* @param conn
* 数据库连接
* @return 执行结果
* @throws IllegalArgumentException
* 如果不提交而且参数con为空,将抛出IllegalArgumentException
* @throws SQLException
*/
public static SPParam execute(final String callSql, final Connection conn,
Collection collect) throws SQLException {
Connection con = null;
CallableStatement callstmt = null;
SPParam spp = new SPParam();
try {
con = DataProcess.getDataSource().getConnection();
if (con.getAutoCommit()) {
con.setAutoCommit(false);
}
callstmt = con.prepareCall(callSql);
int inSize = collect.size();
try {
callstmt = parseParam(callstmt, collect, 0);
int codeIndex = inSize + 1;
int msgIndex = inSize + 2;
if (inSize == 0) {
codeIndex -= 1;
msgIndex -= 1;
}
callstmt
.registerOutParameter(codeIndex, java.sql.Types.INTEGER);
callstmt.registerOutParameter(msgIndex, java.sql.Types.VARCHAR);
callstmt.execute();
spp.code = callstmt.getInt(codeIndex);
spp.str = callstmt.getString(msgIndex);
con.commit();
} catch (SQLException ex) {
// 开始回滚
LogUtil.debug("execute:开始事务回滚");
try {
con.rollback();
} catch (SQLException e) {
LogUtil.error("DBSql.exeProcRets() 出现异常 : 事务回滚中断", e);
}
LogUtil.error("存储过程 " + callSql + " 执行异常:", ex);
throw ex;
}
return spp;
} finally {
collect = null;
if (con == null) {
LogUtil.error("无法取到数据库连接,请排查原因");
freeResource(null, callstmt);
} else {
freeResource(null, callstmt, con);
}
}
}
/**
* <p>
* 执行储存过程 如果需要事务请在存储过程里面实现, 返回值是Procuder中的返回值
* </p>
* <p>
* 比如说LinkedList、ArrayList、List、HashMap,Vector和HashTable
* </p>
* <p>
* 不再推荐使用,LinkedList的添加删除性能非常好,本质上是个双向链表,
* </p>
* <p>
* 可以双向搜索。
* </p>
*
* @param callSql
* 要执行的存储过程语句:
* <p>
* 存储过程的调用方式是 "{ call ProcedureName(?) }"
* </p>
* <p>
* 括号中的 “?” 表示 需要带进来的参数 可以带多个不同类型或相同类型的参数
* </p>
*
* @param collect
* 执行存储过程的时候需要带进来的参数列表:
* <p>
* 根据括号中的 “?” 多少来确定需要传进来多少
* </p>
* <p>
* 参数的类型 可以是 int, String, boolean, long, float, double
* </p>
* <p>
* 传参方式是 集合中是这样存储的: index0 = int1 代表传进来的是 int类型 值为1 其他类型也都是一样的方式
* </p>
*
* @param conn
* 数据库连接
* @return 执行结果
* @throws IllegalArgumentException
* 如果不提交而且参数con为空,将抛出IllegalArgumentException
* @throws SQLException
*/
public static int executeProcReturn(final String callSql,
final Connection conn, Collection collect) throws SQLException {
return execute(callSql, conn, collect).code;
}
/**
*
* @param callSql
* 要执行的存储过程语句:
* <p>
* 存储过程的调用方式是 "{ call ProcedureName(?) }"
* </p>
* <p>
* 括号中的 “?” 表示 需要带进来的参数 可以带多个不同类型或相同类型的参数
* </p>
*
* @param collect
* 执行存储过程的时候需要带进来的参数列表:
* <p>
* 根据括号中的 “?” 多少来确定需要传进来多少
* </p>
* <p>
* 参数的类型 可以是 int, String, boolean, long, float, double
* </p>
* <p>
* 传参方式是 集合中是这样存储的: index0 = int1 代表传进来的是 int类型 值为1 其他类型也都是一样的方式
* </p>
*
* @param conn
* 数据库连接
* @param outPutIndex
* 输出参数在存储过程中的(位置)
* @return 执行结果
* @throws IllegalArgumentException
* 如果不提交而且参数con为空,将抛出IllegalArgumentException
* @throws SQLException
* 2008-3-24 李增群
* @throws
*/
private static Map queryProcReturnValue(final String callSql,
final Connection conn, Collection collect, int outPutIndex,
int reDataType) throws SQLException {
Connection con = null;
CallableStatement callstmt = null;
Map result = new HashMap();
try {
con = DataProcess.getDataSource().getConnection();
callstmt = con.prepareCall(callSql);
try {
callstmt.registerOutParameter(1, reDataType);
callstmt = parseParam(callstmt, collect, 1);
callstmt.execute();
Object obj = callstmt.getObject(1);
if (obj != null) {
if (reDataType == java.sql.Types.INTEGER) {
result.put("0", "" + ((Integer) obj).intValue());
} else {
result.put("0", readValue(obj));
}
}
// putParamToCollect(callstmt,outMap,result,outPutIndex);
} catch (SQLException ex) {
LogUtil.error("存储过程 " + callSql + " 执行异常:", ex);
throw ex;
}
} finally {
collect = null;
if (con != null) {
freeResource(null, callstmt, con);
} else {
freeResource(null, callstmt);
}
}
return result;
}
/**
*
* @param obj
* @return
*/
private static String readValue(Object obj) {
StringBuffer str = new StringBuffer();
java.sql.Clob clob = (java.sql.Clob) obj;
BufferedReader buffer;
try {
buffer = new BufferedReader(clob.getCharacterStream());
try {
String s = "";
while ((s = buffer.readLine()) != null) {
str.append(s);
}
} catch (IOException e) {
LogUtil.error("处理CLOB字段值时异常:", e);
} finally {
try {
buffer.close();
} catch (IOException e) {
}
}
} catch (SQLException e1) {
LogUtil.error("处理CLOB字段值时异常:", e1);
}
return str.toString();
}
/***
*
* @param callstmt
* CALL PROC JDBC CallableStatement
* @param result
* 调用存储过程返回1-N个输出参数 2008-3-24 李增群
*/
private static void putParamToCollect(CallableStatement callstmt,
Map outMap, Map result, int outPutIndex) {
// TODO Auto-generated method stub
int size = outMap.size();
String value = null;
try {
int registerIndex;
for (int i = 0; i < size; i++) {
String in = String.valueOf(i);
value = (String) outMap.get(in);
registerIndex = outPutIndex + i;
if (PINT.equals(value)) {
result.put(in, String.valueOf(callstmt
.getInt(registerIndex)));
} else if (PSTRING.equals(value)) {
result.put(in, String.valueOf(callstmt
.getString(registerIndex) == null ? "" : callstmt
.getString(registerIndex)));
} else if (PBOOLEAN.equals(value)) {
result.put(in, String.valueOf(callstmt
.getBoolean(registerIndex)));
} else if (PDOUBLE.equals(value)) {
result.put(in, String.valueOf(callstmt
.getDouble(registerIndex)));
} else if (PFLOAT.equals(value)) {
result.put(in, String.valueOf(callstmt
.getFloat(registerIndex)));
} else if (PLONG.equals(value)) {
result.put(in, String.valueOf(callstmt
.getLong(registerIndex)));
}
}
} catch (SQLException ex) {
LogUtil.error("JDBC执行完存储过程得到 输出参数时 发生 异常:", ex);
}
}
/**
*
* @param callstmt
* CALL PROC JDBC CallableStatement
*@param collect
* 执行存储过程的时候需要带进来的参数列表:
* <p>
* 根据括号中的 “?” 多少来确定需要传进来多少
* </p>
* <p>
* 参数的类型 可以是 int, String, boolean, long, float, double
* </p>
* <p>
* 传参方式是 集合中是这样存储的: index0 = int1 代表传进来的是 int类型 值为1 其他类型也都是一样的方式
* </p>
* @param outPutIndex
* 存储过程入参中输出参数的位置 2008-3-24 李增群
*/
private static void registerOutParameter(CallableStatement callstmt,
Collection collect, Map outMap, int outPutIndex) {
try {
if (collect != null) {
Iterator it = collect.iterator();
int j = 0;
int registerIndex;
while (it.hasNext()) {
registerIndex = outPutIndex + j;
String para = it.next().toString();
if (para.indexOf(PINT) >= 0 && para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.INTEGER);
outMap.put(String.valueOf(j++), PINT);
} else if (para.indexOf(PSTRING) >= 0
&& para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.VARCHAR);
outMap.put(String.valueOf(j++), PSTRING);
} else if (para.indexOf(PBOOLEAN) >= 0
&& para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.BOOLEAN);
outMap.put(String.valueOf(j++), PBOOLEAN);
} else if (para.indexOf(PDOUBLE) >= 0
&& para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.DOUBLE);
outMap.put(String.valueOf(j++), PDOUBLE);
} else if (para.indexOf(PFLOAT) >= 0
&& para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.FLOAT);
outMap.put(String.valueOf(j++), PFLOAT);
} else if (para.indexOf(PLONG) >= 0
&& para.indexOf(OUTPUT) >= 0) {
callstmt.registerOutParameter(registerIndex,
java.sql.Types.LONGVARBINARY);
outMap.put(String.valueOf(j++), PLONG);
}
}
}
} catch (SQLException ex) {
LogUtil.error("JDBC注册存储过程 输出参数时 发生 执行异常:", ex);
}
}
/**
* <p>
* 存储过程查询数据库
* </p>
*
* @param sql
* 查询语句
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException
* @throws SQLException
* SQL异常
*/
public static ArrayList queryProc(final String sql) throws SQLException {
return queryProc(sql, null, -1, -1, 0, null);
}
/**
* <p>
* 存储过程查询数据库
* </p>
*
* @param sql
* 查询语句
* @param collect
* 参数列表
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException
* @throws SQLException
* SQL异常
*
* public static Object queryProc(final String sql, final
* Collection collect, final Object obj) throws SQLException{
*
*
* return queryProc(sql, null, -1, -1, 0, collect); }
*/
/**
* <p>
* 存储过程查询数据库
* </p>
*
* @param sql
* 查询语句
* @param collect
* 参数列表
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException
* @throws SQLException
* SQL异常
*/
public static ArrayList queryProc(final String sql, Connection con,
final Collection collect) throws SQLException {
return queryProc(sql, con, -1, -1, 0, collect);
}
/**
* <p>
* 存储过程查询数据库
* </p>
*
* @param sql
* 查询语句
* @param collect
* 参数列表
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException
* @throws SQLException
* SQL异常
*/
public static ArrayList queryProc(final String sql, final Collection collect)
throws SQLException {
return queryProc(sql, null, -1, -1, 0, collect);
}
/**
* <p>
* 查询数据库
* </p>
*
* @param sql
* 查询语句
* @param conn
* 数据库的连接
* @param type
* 0表示两层结构,1表示一层结构,2表示只取第一行
* @param beginIndex
* 起始位置,从1开始,不用担心越界
* @param num
* 个数,不用担心越界
* @param collect
* 参数列表
* @return ArrayList类型的行集查询结果,其中每个行集也是ArrayList类型
* @throws SQLException
* SQL异常
*/
private static ArrayList queryProc(final String sql, final Connection conn,
final int beginIndex, final int num, final int type,
Collection collect) throws SQLException {
final String exsql = sql.trim();
int begin = beginIndex;
int count = num;
boolean needPosition = false;
if (begin != -1 || count != -1) {
needPosition = true;
}
ArrayList list = new ArrayList();
Connection con = null;
CallableStatement callstement = null;
ResultSet rs = null;
try {
con = DataProcess.getDataSource().getConnection();
if (needPosition) {
callstement = callstement = con.prepareCall(exsql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
} else {
callstement = con.prepareCall(exsql);
}
callstement = parseParam(callstement, collect, 0);
int paramSize = collect.size();
if (paramSize > 0) {
paramSize += 1;
}
if (collect.size() == 0) {
paramSize = 1;
}
callstement.registerOutParameter(paramSize,
oracle.jdbc.OracleTypes.CURSOR);// 返参 执行时返回记录集
callstement.executeQuery();
rs = (ResultSet) callstement.getObject(paramSize);
if (rs == null) {
return list;
}
int size = -1;
if (needPosition) {
if (!rs.next()) {
return list;
}
rs.last();
size = rs.getRow();
rs.first();
if (begin < 1) {
begin = 1;
}
if (count == -1 || begin + count > size + 1) {
count = size - begin + 1;
}
}
if (type == 0) {
list = needPosition ? resToList(rs, begin, count, size)
: resToList(rs);
} else if (type == 1) {
list = needPosition ? resToOneList(rs, begin, count, size)
: resToOneList(rs);
} else if (type == 2) {
list = resToFirstList(rs);
}
return list;
} catch (SQLException ex) {
LogUtil.error("查询数据时出现异常:.\r\nmyQuery:sql = \r\n" + exsql, ex);
throw ex;
} finally {
collect = null;
if (con != null) {
freeResource(rs, callstement, con);
} else {
freeResource(rs, callstement);
}
}
}
/**
* 解析需要传到存储过程中的参数
*
* @param callstatement
* 执行存储过程的对象
* @param temp
* 输入参数开始索引
* @throws SQLException
* @throws NumberFormatException
*/
private static CallableStatement parseParam(
final CallableStatement callstmt, final Collection collect, int temp)
throws NumberFormatException, SQLException {
int i = temp;
// String paraList = "";
if (collect != null) {
Iterator it = collect.iterator();
StringBuffer para = new StringBuffer();
while (it.hasNext()) {
int index = i + 1;
para.append(it.next().toString());
if (para.indexOf(PINT) == 0) {
String str = para.substring(3).trim();
if (str.length() == 0)
str = "0";
callstmt.setInt(index, Integer.parseInt(str));
} else if (para.indexOf(PSTRING) == 0) {
callstmt.setString(index, para.substring(6));
} else if (para.indexOf(PBOOLEAN) == 0) {
callstmt.setBoolean(index, Boolean.getBoolean(para
.substring(7)));
} else if (para.indexOf(PDOUBLE) == 0) {
String str = para.substring(6).replaceAll("[-,]", "")
.trim();
try {
callstmt.setDouble(index, Double.parseDouble(str));
} catch (java.lang.NumberFormatException nfe) {
callstmt.setDouble(index, 0);
}
} else if (para.indexOf(PFLOAT) == 0) {
callstmt.setFloat(index, Float
.parseFloat(para.substring(5)));
} else if (para.indexOf(PLONG) == 0) {
callstmt.setLong(index, Long.parseLong(para.substring(4)));
} else if (para.indexOf(PDATE) == 0) {
callstmt.setDate(index, java.sql.Date.valueOf(para
.substring(4)));
}
i++;
para.delete(0, para.length());
}
}
return callstmt;
}
/**
* 执行若干条SQL语句(SQL语句类型见JDKAPI文档:java.sql.Statement.executeUpdate())
*
* @param collect
* SQL语句集
* @param con
* 数据库连接
* @return 执行结果
* @throws SQLException
* @throws IllegalArgumentException
* 如果参数con为空,将抛出IllegalArgumentException
*/
private static int[] uncommittedExecute(final Collection collect,
final Connection con) throws SQLException, IllegalArgumentException {
return execute(collect, con, false);
}
/**
* 执行一条SQL语句(SQL语句类型见JDKAPI文档:java.sql.Statement.executeUpdate())
*
* @param sql
* SQL语句
* @param con
* 数据库连接
* @return 执行结果
* @throws SQLException
* @throws IllegalArgumentException
* 如果参数con为空,将抛出IllegalArgumentException
*/
public static int uncommittedExecute(final String sql, final Connection con)
throws SQLException, IllegalArgumentException {
final LinkedList list = new LinkedList();
list.add(sql);
return uncommittedExecute(list, con)[0];
}
/**
* <p>
* 把ResultSet中的信息按顺序全部复制到ArrayList中,
* </p>
* <p>
* 是两层结构二位表的结构。
* </p>
* <p>
* 调用此方式时要注意关闭ResultSet类型的rs和
* </p>
* <p>
* Statement类型的statement,不推荐直接使用
* </p>
*
* @param rs
* 要处理的集合
* @return 处理后的ArrayList
* @throws SQLException
*/
private static ArrayList resToList(final ResultSet rs) throws SQLException {
final int column = rs.getMetaData().getColumnCount();
final LinkedList list = new LinkedList();
String temp;
while (rs.next()) {
final ArrayList subList = new ArrayList(column);
// 将结果集的每行的几项按顺序存入向量,然后是下一行
for (int i = 1; i <= column; i++) {
temp = rs.getString(i);
if (temp == null) {
temp = "";
}
subList.add(temp.trim());
}
list.add(subList);
}
return new ArrayList(list);
}
/**
* <p>
* 把ResultSet中的信息按顺序全部复制到ArrayList中,
* </p>
* <p>
* 一层结构。
* </p>
* <p>
* 调用此方式时要注意关闭ResultSet类型的rs和
* </p>
* <p>
* Statement类型的statement,不推荐直接使用
* </p>
*
* @param rs
* 要处理的集合
* @return 处理后的ArrayList
* @throws SQLException
*/
private static ArrayList resToOneList(final ResultSet rs)
throws SQLException {
final int column = rs.getMetaData().getColumnCount();
final LinkedList list = new LinkedList();
String temp;
while (rs.next()) {
for (int i = 1; i <= column; i++) {
// 将结果集按顺序存入向量
temp = rs.getString(i);
if (temp == null) {
temp = "";
}
list.add(temp.trim());
}
}
return new ArrayList(list);
}
/**
* <p>
* 把ResultSet中的信息按顺序全部复制到ArrayList中,
* </p>
* <p>
* 是两层结构二位表的结构。
* </p>
* <p>
* 调用此方式时要注意关闭ResultSet类型的rs和
* </p>
* <p>
* Statement类型的statement,不推荐直接使用
* </p>
*
* @param rs
* 要处理的集合
* @param beginIndex
* 起始位置,从1开始,不用担心越界
* @param num
* 个数,不用担心越界
* @param size
* 记录数
* @return 处理后的ArrayList
* @throws SQLException
*/
private static ArrayList resToList(final ResultSet rs,
final int beginIndex, final int num, final int size)
throws SQLException {
final int column = rs.getMetaData().getColumnCount();
final ArrayList list = new ArrayList(size);
String temp;
rs.absolute(beginIndex);
for (int i = beginIndex; i < beginIndex + num; i++) {
final ArrayList subList = new ArrayList(column);
// 将结果集的每行的几项按顺序存入向量,然后是下一行
for (int j = 1; j <= column; j++) {
temp = rs.getString(j);
subList.add(temp == null ? "" : temp.trim());
}
list.add(subList);
rs.next();
}
return list;
}
/**
* <p>
* 把ResultSet中的信息按顺序全部复制到ArrayList中,
* </p>
* <p>
* 一层结构。
* </p>
* <p>
* 调用此方式时要注意关闭ResultSet类型的rs和
* </p>
* <p>
* Statement类型的statement,不推荐直接使用
* </p>
*
* @param rs
* 要处理的集合
* @param beginIndex
* 起始位置,从1开始,不用担心越界
* @param num
* 个数,不用担心越界
* @param size
* 记录数
* @return 处理后的ArrayList
* @throws SQLException
*/
private static ArrayList resToOneList(final ResultSet rs,
final int beginIndex, final int num, final int size)
throws SQLException {
final int column = rs.getMetaData().getColumnCount();
final ArrayList list = new ArrayList(size * column);
String temp;
rs.absolute(beginIndex);
for (int i = beginIndex; i < beginIndex + num; i++) {
for (int j = 1; j <= column; j++) {
// 将结果集按顺序存入向量
temp = rs.getString(j);
list.add(temp == null ? "" : temp.trim());
}
rs.next();
}
return list;
}
/**
* <p>
* 把ResultSet中第一行的信息按顺序全部复制到ArrayList中,
* </p>
* <p>
* 一层结构。
* </p>
* <p>
* 调用此方式时要注意关闭ResultSet类型的rs和
* </p>
* <p>
* Statement类型的statement,不推荐直接使用
* </p>
*
* @param rs
* 要处理的集合
* @return 处理后的ArrayList
* @throws SQLException
*/
private static ArrayList resToFirstList(final ResultSet rs)
throws SQLException {
final int column = rs.getMetaData().getColumnCount();
final ArrayList list = new ArrayList(1);
String temp;
if (!rs.next()) {
return new ArrayList();
}
for (int i = 1; i <= column; i++) {
// 将结果集按顺序存入向量
temp = rs.getString(i);
list.add(temp == null ? "" : temp.trim());
}
return list;
}
/**
* <p>
* 释放相关资源
* </p>
*
* @param rs
* ResultSet
* @param stmt
* Statement
*/
private static void freeResource(final ResultSet rs, final Statement stmt) {
freeResource(rs, stmt, null);
}
/**
* <p>
* 释放相关资源
* </p>
*
* @param rs
* ResultSet
* @param stmt
* Statement
* @param con
* Connection
*/
// private static void freeResource(final ResultSet rs, final Statement
// stmt, final Connection con) {
public static void freeResource(ResultSet rs, Statement stmt, Connection con) // aban
// change
// private
// to
// public
{
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
LogUtil.error("关闭ResultSet对象出现异常:", e);
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
LogUtil.error("关闭Statement对象出现异常:", e);
}
stmt = null;
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
LogUtil.error("关闭Connection对象出现异常:", e);
}
// con = null;
}
}
/**
* Create Procedure 调用语句
*
* @param proName
* @param pCount
* @return
*/
public static String getProSql(String proName, int pCount) {
StringBuffer strbuf = new StringBuffer();
strbuf.append("{call " + proName + "(?");
for (int k = 0; k < pCount; k++)
strbuf.append(",?");
strbuf.append(")}");
return strbuf.toString();
}
}