dbaccess.java

来自「这是一个jsp联合javabean的电话查询系统。」· Java 代码 · 共 138 行

package org.jzgs.telnumber.ec.Util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.jzgs.telnumber.db.ConnectionPool;
import org.jzgs.telnumber.db.DB;





public class DBAccess {

	private Connection con = null;
    private ConnectionPool connPool=null;
	private String msg = "";
	
	DBAccess() throws MyException {
		openDatabase();
	}
	
	public static DBAccess getInstance() throws MyException {
		return new DBAccess();
	}

	public void closeDatabase() throws MyException {
		if(con!=null)
		 connPool = DB.getConnPool();
		connPool.freeConnection(con);
		con = null;
	}
	
	private void openDatabase() throws MyException {
		try {
			if (con == null|| con.isClosed()) {
			    connPool = DB.getConnPool();
				con = connPool.getConnection();
				//Context ctx = new InitialContext();
				//DataSource ds = (DataSource) ctx
				//		.lookup("java:comp/env/jdbc/oracle");
				//con = ds.getConnection();
				con.setAutoCommit(false);
			}
		} catch (SQLException e) {
			msg = e.getMessage();
			throw new MyException(msg);
		}
}

	public void commit() throws MyException {
		try {
			con.commit();
		} catch (Exception e) {
			msg = e.getMessage();
			throw new MyException(msg);
		}
	}

	public void rollback() throws MyException {
		try {
			con.rollback();
		} catch (Exception e) {
			msg = e.getMessage();
			throw new MyException(msg);
		}
	}
	public ArrayList executeQueryGetBeanList(String sql) throws SQLException, MyException {

		if(con==null || con.isClosed()){
			openDatabase();
		}
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// System.out.println(sql);
			ArrayList resultList = new ArrayList();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				HashMap nsDataObj = new HashMap();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					nsDataObj.put(rsmd.getColumnName(i), StringUtil
							.nullToSpace(rs.getString(i)).replaceAll("亅",
									"@@僟僢僔儏@@"));
				}
				TelNumValueMap tempBean = new TelNumValueMap(nsDataObj);
				resultList.add(tempBean);
			}
			rs.close();
			stmt.close();
			return resultList;
		} catch (Exception e) {
			e.printStackTrace();
			return null;

		} 
	}
	
	public TelNumValueMap executeQueryGetBean(String sql) throws MyException {
		try {
			if(con==null || con.isClosed()){
				openDatabase();
			}
			//System.out.println(sql);
			HashMap nsDataObj=new HashMap(); 
			TelNumValueMap bean=new TelNumValueMap();
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {				
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					nsDataObj.put(rsmd.getColumnName(i), StringUtil.nullToSpace(rs.getString(i)) );
				}
			}
			rs.close();
			stmt.close();
			bean=new TelNumValueMap(nsDataObj);
			return bean;
		} catch (Exception e) {
			msg = e.getMessage();
			throw new MyException(msg);
		}
	}	
	
}