//public int[] executeBatch(String[] sql, List[] params) 还是有点问题!!!
//jdk1.4
//需要ojdbc14.jar
package com.sehz.skmps.db.oracle.jdbc;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
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;
public class SkmpsDao {
/**
* exam:
*
StringBuffer sb = new StringBuffer();
sb.append(" SELECT DISTINCT M.PATH_FLAG, M.EMP_NO ");
sb.append(" FROM SEC_APPROVE_PATH M ");
sb.append(" WHERE M.PATH_FLAG = ? AND M.VERSION = ?");
List listparam = new ArrayList();
listparam.add(prefix);
listparam.add(new Integer(iVer));
List listPath = sc.getList(sb.toString(), listparam);
* @param sql
* @param params
* @return
*/
public List getList(String sql, List params) throws Exception{
List result = null;
Connection conn = null;
try{
SkmpsConnection sc = new SkmpsConnection();
conn = sc.getDBConnection();
result = getList(conn, sql, params);
} catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
if(conn != null){
try{
conn.close();
conn = null;
} catch (SQLException se) {
se.printStackTrace();
}
}
}
return result;
}
public List getList(Connection conn, String sql, List params) throws SQLException{
List result = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
result = new ArrayList();
ps = conn.prepareStatement(sql);
if(params != null){
for(int i = 0; i < params.size(); i++){
Object param = params.get(i);
if(param == null){
ps.setObject(i+1, null);
}else if(param instanceof Integer){
ps.setInt(i+1, ((Integer) param).intValue());
}else if(param instanceof BigDecimal){
ps.setFloat(i+1, ((BigDecimal) param).floatValue());
}else if(param instanceof Date){
ps.setDate(i+1, (Date) param);
}else if(param instanceof String){
ps.setString(i+1, (String) param);
}else{
ps.setObject(i+1, param);
}
}
}
rs = ps.executeQuery();
Map map = null;
ResultSetMetaData md = null;
while (rs.next()) {
map = new HashMap();
md = rs.getMetaData();
for(int i = 0; i < md.getColumnCount(); i++){
String colName = md.getColumnName(i+1);
map.put(colName.toUpperCase(), rs.getObject(colName));
}
result.add(map);
}
} catch (SQLException se) {
se.printStackTrace();
throw se;
} finally {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
} catch (Exception e) {
System.out.println("Cannot close the Statement!");
}
}
return result;
}
/**
*
List list_listParam = new ArrayList();
for(int i = 0; i < 10; i++){
List listparam = new ArrayList();
listparam.add(epid);
listparam.add((String) mapPath.get("PATH_FLAG"));
listparam.add(empno);
list_listParam.add(listparam);
}
sc.executeBatch(sb.toString(), list_listParam);
* @param conn
* @param sql
* @param params
*/
public int[] executeBatch(String[] sql, List[] params) throws Exception{
int[] result = null;
Connection conn = null;
try{
SkmpsConnection sc = new SkmpsConnection();
conn = sc.getDBConnection();
conn.setAutoCommit(false);
result = executeBatch(conn, sql, params);
conn.commit();
} catch (Exception e) {
try {
conn.rollback();
} catch (Exception e1) {
System.out.println("Cannot close the Statement!");
}
e.printStackTrace();
throw e;
} finally {
if(conn != null){
try{
conn.close();
conn = null;
} catch (SQLException se) {
se.printStackTrace();
}
}
}
return result;
}
private int[] executeBatch(Connection conn, String[] sql, List[] params) throws SQLException{
int[] result = null;
if(sql.length != params.length){
System.out.println("The param's length is not equal.");
return new int[]{0};
}
PreparedStatement ps = null;
try {
for(int i = 0; i < sql.length; i++){
ps = conn.prepareStatement(sql[i]);
System.out.println("[" + i + "]sql:/n" + sql[i]);
if(params[i] != null){
if(params[i].size() > 0){
for(int j = 0; j < params[i].size(); j++){
List sqlParams = (List) params[i].get(j);
for(int k = 0; k < sqlParams.size(); k++){
Object param = sqlParams.get(k);
if(param == null){
ps.setObject(k+1, null);
System.out.println("param[null]:" + param);
}else if(param instanceof Integer){
ps.setInt(k+1, ((Integer) param).intValue());
System.out.println("param[Integer]:" + param);
}else if(param instanceof BigDecimal){
ps.setFloat(k+1, ((BigDecimal) param).floatValue());
System.out.println("param[BigDecimal]:" + param);
}else if(param instanceof Date){
ps.setDate(k+1, (Date) param);
System.out.println("param[Date]:" + param);
}else if(param instanceof String){
ps.setString(k+1, (String) param);
System.out.println("param[String]:" + param);
}else{
ps.setObject(k+1, param);
System.out.println("param[else]:" + param);
}
}
ps.addBatch();
}
} else {
ps.addBatch();
}
} else {
ps.addBatch();
}
}
result = ps.executeBatch();
} catch (SQLException se) {
se.printStackTrace();
throw se;
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (Exception e) {
System.out.println("Cannot close the Statement!");
}
}
return result;
}
private int executeUpdate(Connection conn, String sql, List params) throws SQLException{
int result = -1;
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
if(params != null){
System.out.println("executeUpdate---sql:/n" + sql);
for(int k = 0; k < params.size(); k++){
Object param = params.get(k);
if(param == null){
ps.setObject(k+1, null);
System.out.println(">>>>>>>>>>>>param[" + (k+1) + "]:null{NULL}");
}else if(param instanceof Integer){
ps.setInt(k+1, ((Integer) param).intValue());
System.out.println(">>>>>>>>>>>>param[" + (k+1) + "]:" + ((Integer) param).intValue() + "{Integer}");
}else if(param instanceof BigDecimal){
ps.setFloat(k+1, ((BigDecimal) param).floatValue());
System.out.println(">>>>>>>>>>>>param[" + (k+1) + "]:" + ((BigDecimal) param).floatValue() + "{BigDecimal}");
}else if(param instanceof Date){
ps.setDate(k+1, (Date) param);
System.out.println(">>>>>>>>>>>>param[" + (k+1) + "]:" + String.valueOf((Date) param) + "{Date}");
}else if(param instanceof String){
ps.setString(k+1, (String) param);
System.out.println(">>>>>>>>>>>>param[" + (k+1) + "]:" + param + "{String}");
}else{
ps.setObject(k+1, param);
System.out.println(">>>>>>>>>>>>param[" + (k+1) + "]:" + param + "{else}");
}
}
}
result = ps.executeUpdate();
} catch (SQLException se) {
se.printStackTrace();
throw se;
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (Exception e) {
System.out.println("Cannot close the Statement!");
}
}
return result;
}
public int[] executeUpdate(String[] sql, List[] params) throws Exception{
int[] result = null;
if(sql.length != params.length){
System.out.println("The param's length is not equal.");
return new int[]{0};
}
Connection conn = null;
try{
SkmpsConnection sc = new SkmpsConnection();
conn = sc.getDBConnection();
} catch (Exception e) {
try {
//conn.rollback();
} catch (Exception e1) {
System.out.println("Cannot close the Statement!");
}
e.printStackTrace();
throw e;
}
ArrayList arr_result = new ArrayList();
try {
conn.setAutoCommit(false);
System.out.println("---->sql start..");
for(int i = 0; i < sql.length; i++){
if(params[i] != null){
for(int j = 0; j < params[i].size(); j++){
List sqlParams = (List) params[i].get(j);
arr_result.add(new Integer(executeUpdate(conn, sql[i], sqlParams)));
}
}
}
conn.commit();
System.out.println("<----sql ended..");
} catch (SQLException se) {
se.printStackTrace();
try {
conn.rollback();
} catch (Exception e) {
System.out.println("Cannot close the Statement!");
}
throw se;
} finally {
if(conn != null){
try{
conn.close();
conn = null;
} catch (SQLException se) {
se.printStackTrace();
}
}
}
result = new int[arr_result.size()];
for(int i = 0; i < arr_result.size(); i++){
result[i] = ((Integer) arr_result.get(i)).intValue();
}
return result;
}
public int executeUpdate(String sql, List params) throws Exception{
int result = -1;
Connection conn = null;
try{
SkmpsConnection sc = new SkmpsConnection();
conn = sc.getDBConnection();
} catch (Exception e) {
try {
//conn.rollback();
} catch (Exception e1) {
System.out.println("Cannot close the Statement!");
}
e.printStackTrace();
throw e;
}
try {
conn.setAutoCommit(false);
System.out.println("---->sql start..");
result = executeUpdate(conn, sql, params);
conn.commit();
System.out.println("<----sql ended..");
} catch (SQLException se) {
se.printStackTrace();
try {
conn.rollback();
} catch (Exception e) {
System.out.println("Cannot close the Statement!");
}
throw se;
} finally {
if(conn != null){
try{
conn.close();
conn = null;
} catch (SQLException se) {
se.printStackTrace();
}
}
}
return result;
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
}
}