J2ee項目開發過程中用到的資料庫操作類
此資料庫操作封裝類在一個省級項目中使用到,在一個集團公司的科技管理項目中也使用過.
package jing.dbnew;
import java.sql.*;
import java.util.*;
import javax.sql.rowset.CachedRowSet;
import oracle.jdbc.rowset.OracleCachedRowSet;
import com.sun.rowset.CachedRowSetImpl;
public class DBManager {
public DBManager(){
}
public void beginTransaction() {
try {
Connection con = ConnectionFactory.getInstance().getConnection();
con.setAutoCommit(false);
} catch (Exception e) {
e.printStackTrace();
}
}
public void rollbackTransactoin() {
try {
Connection con = ConnectionFactory.getInstance().getConnection();
con.rollback();
} catch (Exception e) {
e.printStackTrace();
}
}
public void commitTransaction() {
try {
Connection con = ConnectionFactory.getInstance().getConnection();
con.commit();
con.setAutoCommit(true);
ConnectionFactory.getInstance().closeConnection();
} catch (Exception e) {
e.printStackTrace();
}
}
// public void endTransaction() {
// try {
// Connection con = ConnectionFactory.getInstance().getConnection();
// con.commit();
// con.setAutoCommit(true);
// ConnectionFactory.getInstance().closeConnection();
// } catch (Exception e) {
// e.printStackTrace();
// }
// }
public boolean execute(String sql) throws Exception {
System.out.println("execute：" + sql);
boolean re = false;
Connection con = null;
PreparedStatement pstmt = null;
try {
con = ConnectionFactory.getInstance().getConnection();
pstmt =con.prepareStatement(sql);
re = pstmt.execute();
}catch(Exception ex){
ex.printStackTrace();
con.rollback();
} finally {
if (pstmt != null) {
pstmt.close();
}
}
return re;
}
public int[] executeBatch(String[] sql) throws Exception {
int[] re=null;;
Connection con = null;
Statement stmt = null;
try {
con = ConnectionFactory.getInstance().getConnection();
con.setAutoCommit(false);
stmt =con.createStatement();
stmt.clearBatch();
for(int i=0;i<sql.length;i++){
stmt.addBatch(sql[i]);
System.out.println("executeBatch：" + sql[i]);
}
re = stmt.executeBatch();
con.commit();
}catch(Exception ex){
ex.printStackTrace();
con.rollback();
}
finally {
if (stmt != null) {
stmt.close();
}
}
return re;
}
public boolean execute(String sql, String[] value, int[] type)
throws Exception {
System.out.println("execute：" + sql);
boolean re = false;
Connection con = null;
PreparedStatement pstmt = null;
try {
con = ConnectionFactory.getInstance().getConnection();
pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
this.setParameters(pstmt,value,type); //調用私有方法設定參數
re = pstmt.execute();
} catch(Exception ex){
ex.printStackTrace();
con.rollback();
}finally {
if (pstmt != null) {
pstmt.close();
}
}
return re;
}
public CachedRowSet executeQueryCachedRowSet(String sql) throws Exception {
System.out.println("executeQueryCachedRowSet：" + sql);
CachedRowSet crs = null;
//OracleCachedRowSet crs=null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = ConnectionFactory.getInstance().getConnection();
pstmt=con.prepareStatement(sql);
rs = pstmt.executeQuery();
if (!(rs.isBeforeFirst() == rs.isAfterLast())) {
crs = new CachedRowSetImpl();
//crs = new OracleCachedRowSet();
crs.populate(rs);
}
} catch(Exception ex){
ex.printStackTrace();
con.rollback();
}finally {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
}
return crs;
}
public OracleCachedRowSet executeQueryOracleCachedRowSet(String sql) throws Exception {
System.out.println("executeQueryOracleCachedRowSet：" + sql);
OracleCachedRowSet crs=null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = ConnectionFactory.getInstance().getConnection();
pstmt=con.prepareStatement(sql);
rs = pstmt.executeQuery();
if (!(rs.isBeforeFirst() == rs.isAfterLast())) {
crs = new OracleCachedRowSet();
crs.populate(rs);
}
}catch(Exception ex){
ex.printStackTrace();
con.rollback();
} finally {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
}
return crs;
}
public CachedRowSet executeQueryCachedRowSet(String sql, String[] value,
int[] type) throws Exception {
System.out.println("executeQueryRowSet：" + sql);
CachedRowSet crs = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = ConnectionFactory.getInstance().getConnection();
pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
this.setParameters(pstmt,value,type); //調用私有方法設定參數
rs = pstmt.executeQuery();
if (!(rs.isBeforeFirst() == rs.isAfterLast())) {
crs = new CachedRowSetImpl();
crs.populate(rs);
}
}catch(Exception ex){
ex.printStackTrace();
con.rollback();
} finally {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
}
return crs;
}
public Map executeQueryMap(String sql) throws Exception {
System.out.println("executeQueryMap：" + sql);
Map map = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = ConnectionFactory.getInstance().getConnection();
pstmt =con.prepareStatement(sql);
rs = pstmt.executeQuery();
if (rs.next()) { // 有記錄的情況
map = new HashMap();
ResultSetMetaData rsm = rs.getMetaData();
int colnum = rsm.getColumnCount(); // 得到記錄的列數
for (int i = 1; i <= colnum; i++) {
// rsm.getColumnName(i).toUpperCase()統一列名大寫
// rs.getObject(i)更的值為對象類型
map
.put(rsm.getColumnName(i).toUpperCase(), rs
.getObject(i));
}
}
} catch(Exception ex){
ex.printStackTrace();
con.rollback();
}finally {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
}
return map;
}
public Map executeQueryMap(String sql, String[] value, int[] type)
throws Exception {
System.out.println("executeQueryMap：" + sql);
Map map = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = ConnectionFactory.getInstance().getConnection();
pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
this.setParameters(pstmt,value,type); //調用私有方法設定參數
rs = pstmt.executeQuery();
if (rs.next()) { // 有記錄的情況
map = new HashMap();
ResultSetMetaData rsm = rs.getMetaData();
int colnum = rsm.getColumnCount(); // 得到記錄的列數
for (int i = 1; i <= colnum; i++) {
// rsm.getColumnName(i).toUpperCase()統一列名大寫
// rs.getObject(i)更的值為對象類型
map
.put(rsm.getColumnName(i).toUpperCase(), rs
.getObject(i));
}
}
} catch(Exception ex){
ex.printStackTrace();
con.rollback();
}finally {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
}
return map;
}
public int executeUpdate(String sql) throws Exception {
System.out.println("executeUpdate：" + sql);
int re = 0;
Connection con = null;
PreparedStatement pstmt = null;
try {
con = ConnectionFactory.getInstance().getConnection();
pstmt = con.prepareStatement(sql);
re = pstmt.executeUpdate();
}catch(Exception ex){
ex.printStackTrace();
con.rollback();
} finally {
if (pstmt != null) {
pstmt.close();
}
}
return re;
}
public int executeUpdate(String sql, String[] value, int[] type)
throws Exception {
System.out.println("executeUpdate：" + sql);
int re = 0;
Connection con = null;
PreparedStatement pstmt = null;
try {
con = ConnectionFactory.getInstance().getConnection();
pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
this.setParameters(pstmt,value,type); //調用私有方法設定參數
re = pstmt.executeUpdate(); // 影響的記錄行數
}catch(Exception ex){
ex.printStackTrace();
con.rollback();
} finally {
if (pstmt != null) {
pstmt.close();
}
}
return re;
}
private void setParameters(PreparedStatement pstmt,String[] value,int[] type)throws Exception{
int j = 1;
for (int i = 0; i < value.length; i++) {
switch (type[i]) {
case Types.INTEGER:
pstmt.setInt(j, Integer.parseInt(value[i]));
System.out.println("Parameter("+i+") INTEGER "+value[i]);
break;
case Types.FLOAT:
case Types.DOUBLE:
case Types.DECIMAL:
case Types.NUMERIC:
pstmt.setDouble(j, Double.parseDouble(value[i]));
System.out.println("Parameter("+i+") FLOAT、DOUBLE、DECIMAL、NUMERIC "+value[i]);
break;
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
pstmt.setString(j, value[i]);
System.out.println("Parameter("+i+") CHAR、VARCHAR、LONGVARCHAR "+value[i]);
break;
case Types.DATE:
case Types.TIMESTAMP:
pstmt.setString(j, value[i]);
System.out.println("Parameter("+i+") DATE、TIMESTAMP "+value[i]);
break;
case Types.BOOLEAN:
// Boolean.parseBoolean(value[i]) JDK5API
pstmt.setBoolean(j, Boolean.getBoolean(value[i]));
System.out.println("Parameter("+i+") BOOLEAN "+value[i]);
break;
}
j = j + 1;
}
}
public List<Object> executeProc(String procName,int returnType,int[] outParameter,Object... args) throws Exception {
List<Object> list = null;
Connection con = null;
CallableStatement cstmt = null;
try {
con = ConnectionFactory.getInstance().getConnection();
int iInStart = 1;// 存儲過程輸入參數的起始位
// 統計問号的個數
int parameterCount = args.length+outParameter.length; //調用的存儲過程的總參數個數
// 組裝帶問号的SQL
String sql = "{call " + procName + "(";
for (int i = 1; i <= parameterCount; i++) {
if (i == parameterCount){
sql += "?";
}else{
sql += "?,";
}
}
sql += ")}";
System.out.println("executeProc："+sql);
cstmt = con.prepareCall(sql);
for (int i = 0; i < args.length; i++) {// 循環設定存儲過程的輸入參數
cstmt.setObject(iInStart + i, args[i]);
System.out.println("設定輸入參數:"+iInStart+" "+args[i]);
}
int iOutStart = args.length + iInStart;// 存儲過程輸出參數的起始位
for (int i = 0; i < outParameter.length; i++) {// 循環設定存儲過程的輸出參數
if (outParameter[i]==Types.REF){ //代表遊标
cstmt.registerOutParameter(i + iOutStart, oracle.jdbc.driver.OracleTypes.CURSOR);
System.out.println("設定輸出參數:"+(i + iOutStart)+" 遊标");
}else if((outParameter[i]==Types.INTEGER)){
cstmt.registerOutParameter(i + iOutStart, Types.INTEGER);
System.out.println("設定輸出參數:"+(i + iOutStart)+" 數值型");
}else{
cstmt.registerOutParameter(i + iOutStart, Types.VARCHAR);
System.out.println("設定輸出參數:"+(i + iOutStart)+" 字元型");
}
}
cstmt.execute(); //執行
list = new ArrayList<Object>();
switch (returnType) {
case 0:// 把存儲過程中傳回的遊标轉換為CachedRowSet類型輸出
for (int i = 0; i < outParameter.length; i++) {// 循環擷取存儲過程的輸出結果，包括遊标、字元串
if (outParameter[i]==Types.REF) { //處理遊标
CachedRowSet crs=new CachedRowSetImpl();
//OracleCachedRowSet crs=new OracleCachedRowSet(); //使用Oracle10g的資料則必須要用10g的驅動,Oracle 10g驅動解決了“ORA-01002: 讀取違反順序”這個問題
if (cstmt.getObject(i + iOutStart) != null){
crs.populate((ResultSet)cstmt.getObject(i + iOutStart));
}
list.add(crs);
} else{ //非遊标
list.add(cstmt.getObject(i + iOutStart));
}
}
break;
}
} catch (Exception ex) {
ex.printStackTrace();
con.rollback();
} finally {
cstmt.close();
}
return list;
}
public static void main(String[] args) throws Exception {
//DBManager dbm = new DBManager();
//dbm.execute("update dept set dname=? where deptno=? ",new String[]{"sdfsdfsad","51"},new int[]{Types.VARCHAR,Types.NUMERIC});
// Connection con = SingletonDataSource.getInstance().getConnection();
// CachedRowSet crs = dbm.executeQueryRowSet(
// "SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP WHERE
// EMPNO >7654",
// con);
// while (crs.next()) {
// System.out.println(crs.getString("EMPNO") + " " +
// crs.getString("ENAME"));
// }
// System.out.println("########################");
// Map map = dbm.executeQueryMap(
// "SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP WHERE
// EMPNO=7654",
// con);
// System.out.println(map.get("EMPNO") + " " + map.get("ENAME") + " " +
// map.get("COMM"));
// con.close();
// System.out.println(dbm.selectList("test", "empno", "ename", true,
// "","select * from emp", null, null));
//DBManager dbmt = new DBManager();
//自動事物處理
//dbmt.execute("insert into emp values (1236, 'TEST', 'TEST', 0, to_date('2008-03-12', 'yyyy-mm-dd'), 0, 0, 10)");
}
}
package jing.dbnew;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public class ConnectionFactory {
private static DataSource ds = null;
private static ConnectionFactory cf = new ConnectionFactory();
private static final ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>(); //線程本地變量
private ConnectionFactory() {
try {
InitialContext ctx = new InitialContext();
// 使用時改成正确的資料配置
//ds = (DataSource) ctx.lookup("jdbc/OracleSource");
//TomCat中的連接配接池擷取方式使用java:/comp/env/
ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/OracleSource");
} catch (NamingException ne) {
System.out.println(ne.getMessage() + "/n沒有使用連接配接池！");
}
}
public static ConnectionFactory getInstance() {
return cf;
}
public Connection getConnection() throws Exception {
Connection con = threadLocal.get();
if (con == null) {
if (ds == null) {
// 使用時改成正确的資料配置
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String url = "jdbc:oracle:thin:@localhost:1521:sample";
con = DriverManager.getConnection(url, "scott", "tiger");
} else {
con = ds.getConnection();
}
//System.out.println("未使用線程中的連接配接！");
threadLocal.set(con);
}else{
//System.out.println("使用線程中的連接配接！");
}
return con;
}
public void closeConnection() {
Connection con = threadLocal.get();
if (con != null) {
try {
con.close();
threadLocal.set(null);
con=null;
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) throws Exception {
Connection con =ConnectionFactory.getInstance().getConnection();
ConnectionFactory.getInstance().closeConnection();
con.createStatement();
}
}
-------------------------------------------------------------------------------------------------
畢業後頭五年決定你的一生 海量Android教程、開發資料和源碼
10類最急需IT人才：Java開發者居首 給将成為“Android高手”的10個建議
成為Java高手的25個學習目标--非常經典 Android 4.1果凍豆新特性詳解
晶片巨頭海思和展訊：給中國晶片業帶來信心 海量經典Java教程、學習資料和源碼
Java侵權訴訟Google獲勝,Android厚積薄發 面試必備：Android筆試總結
Android高手必須掌握的28大内容和10個建議 Android平台研發人才缺口30萬
Android開發環境安裝和配置步驟詳細圖解 2012國内移動App開發者大調查結果
Windows 7下搭建android開發環境步驟圖解 Android 4.0的30個突出的新特性
Android高手要經過的6個階段和6個境界 linux下搭建Android開發環境步驟
從IT菜鳥變為“IT骨幹開發者”的11個建議 程式員程式設計技術迅速提高的終極攻略
2012世界各國人均GDP排名,中國超泰國 2012年全國各省平均工資排行
2012年中國大學高校排行榜（580強排名） 中國各省市面積和人口數量排名
中國百萬開發者大調查：程式員的薪水不錯 Java高手需要越過的10座高山
周立功談嵌入式：我的25年嵌入式生涯 Android和Java語言的異同和關系
華為中國區手機銷量達千萬,80%為智能機 谷歌Android碎片化嚴重
2012年中國各省GDP和人均GDP排名 90後就業“錢景”:IT仍是最佳選擇
2012全球城市競争力500強，69個中國城市上榜 不要做浮躁的軟體工程師
2012年世界500強，79家大陸香港台灣公司上榜名單 給IT新兵的15個建議
美國知名科技公司入門級軟體工程師的薪水排名 回顧Java經過的風風雨雨
71道經典Android面試題和答案--重要知識點都涉及到了
高校應屆畢業生“IT業”收入最高，Android技術最熱門
![GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)