JDBC 帮助类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


public class DbUtil {


public static Log log = LogFactory.getLog(DbUtilEx.class);

private static Connection getConnectionJDBC() {
Connection conn = null;
try {
String jdbcUrl = Constants.JDBC_URL;
String JDBC_USERNAME = Constants.JDBC_USERNAME;
String JDBC_PASSWORD = Constants.JDBC_PASSWORD;
String JDBC_DRIVER = Constants.JDBC_DRIVER;
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(jdbcUrl, JDBC_USERNAME, JDBC_PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
private static Connection getConnectionJDBCS() {
Connection conn = null;
try {
String jdbcUrl = Constants.JDBC_URL_SEL;
String JDBC_USERNAME = Constants.JDBC_USERNAME;
String JDBC_PASSWORD = Constants.JDBC_PASSWORD;
String JDBC_DRIVER = Constants.JDBC_DRIVER;
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(jdbcUrl, JDBC_USERNAME, JDBC_PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}

public static Connection getConn() {
return getConnectionJDBC();
}
public static Connection getConnS() {
return getConnectionJDBCS();
}


public static List<String[]> executeQuery(String sqlStr, String... params) {
List<String[]> retList = new ArrayList<String[]>();
if ((sqlStr == null) || (sqlStr.equals(""))) {
return null;
}
Connection conn = getConnS();
PreparedStatement stmt = null;
ResultSet rs = null;
try {


stmt = conn.prepareStatement(sqlStr);
for (int i = 0; i < params.length; i++) {
stmt.setString(i + 1, params[i]);
}
rs = stmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int colNum = rsmd.getColumnCount();
String[] strArr = null;
while (rs.next()) {
strArr = new String[colNum];
for (int i = 1; i <= colNum; i++) {
strArr[(i - 1)] = rs.getString(i);
}
retList.add(strArr);
}
} catch (SQLException e) {
log.error("jdbc error.." + e);
log.error("失败的sql=[" + sqlStr + "]");
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
rs = null;
stmt = null;
conn = null;
} catch (SQLException e) {
log.error("失败的sql=[" + sqlStr + "]");
log.error("jdbc error.." + e);
e.printStackTrace();
}
}


return retList;
}

public static int executeUpdate(String sqlStr, String... params) {
int rows = 0;
if ((sqlStr == null) || (sqlStr.equals(""))) {
return rows;
}
Connection conn = getConn();

PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sqlStr);
for (int i = 0; i < params.length; i++) {
stmt.setString(i + 1, params[i]);
}
rows = stmt.executeUpdate();
log.info("jdbc执行成功的sql=[" + sqlStr + "]");
} catch (SQLException e) {
log.error("失败的sql=[" + sqlStr + "]");
log.error("jdbc error.." + e);
e.printStackTrace();
} finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
stmt = null;
conn = null;
} catch (SQLException e) {
log.error("失败的sql=[" + sqlStr + "]");
log.error("jdbc error.." + e);
e.printStackTrace();
}
}
return rows;
}


public static String getString(String sqlStr,String...params) {
log.error("jdbc执行成功的sql=[" + sqlStr + "]"+params);
List<String[]> list = executeQuery(sqlStr,params);
if (list.size() == 0) {
return null;
}
if ((list.get(0))[0] == null) {
return null;
} else {
return (list.get(0))[0];
}
}

public static String[] getStringArray(String sqlStr,String...params) {
List<String[]> list = executeQuery(sqlStr,params);
if (list == null || list.size() == 0) {
return null;
} else {
return list.get(0);
}
}


public static boolean executeUpdateBatch(String[] sqlStrArr) {
boolean isOk = true;
if (sqlStrArr == null || sqlStrArr.length == 0) {
log.error("批处理sql失败....sqlarr为空或length=0");
return false;
}
Connection conn = getConn();
Statement stmt = null;
try {
conn.setAutoCommit(false);
stmt = conn.createStatement();
} catch (SQLException e) {
log.error(e, e);
return false;
}


for (int i = 0; i < sqlStrArr.length; i++) {
String sqlStr = sqlStrArr[i];
try {
stmt.executeUpdate(sqlStr);
} catch (SQLException e) {
isOk = false;
log.error("批处理sql失败在第[" + (i + 1) + "]条 sql=" + sqlStr);
log.error(e, e);
break;
}
}
try {
if (isOk) {
conn.commit();
} else {
conn.rollback();
}
} catch (SQLException e) {
log.error(e, e);
} finally {
if (stmt != null) {
try {
stmt.close();
stmt = null;
} catch (SQLException e) {
log.error(e);
}
}
if (conn != null) {
try {
conn.setAutoCommit(true);
conn.close();
conn = null;
} catch (SQLException e) {
log.error(e);
}
}
}
return isOk;
}


}