dbfunc.java~24~

来自「采用web2.0技术,采用动态标签,sql语句全部存储在数据库里面.开发速度快.」· JAVA~24~ 代码 · 共 531 行 · 第 1/2 页

package com.sztheater.framework.util;
import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.naming.*;
/**
*	数据库操作公共函数
*/
public class DbFunc {

	private static HashMap    m_db_pool   = null;
	public  static boolean    m_test_flag = false;

	/*
	描述：
	*<p>依据指定的数据源名(dsName)，获取执行数据库连接</p>
	* @param dsName       数据源(连接池)名
	* @return 数据库连接
	*/
	public static synchronized java.sql.Connection getConnection(boolean bAutoCommit) throws Exception{
		return getConnection( null, bAutoCommit);
	}
	public static synchronized java.sql.Connection getConnection(String pool_name,boolean bAutoCommit)throws Exception{
		if(pool_name==null || pool_name.trim().equals("")) pool_name = "szbusi";
		pool_name = pool_name.trim();
		if(m_db_pool==null) m_db_pool = new HashMap();
		if(m_test_flag){
                Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
                return DriverManager.getConnection("jdbc:microsoft:sqlserver://220.168.31.84:1433;DatabaseName=SZTheater",
                    "SZTheater", "SZTheater");

		}
		Context    ctx   = null;
		Object     dsObj = null;
		DataSource ds    = null;
		Connection conn  = null;
		String     strMsg= "";
		ds         = (DataSource)m_db_pool.get(pool_name);

		if(ds==null){
			try{
				ctx  = new InitialContext();
				dsObj= ctx.lookup("java:comp/env/jdbc/" + pool_name);
			}catch(Exception e){
				strMsg = e.getMessage();
				if(strMsg.indexOf("not bound in this Context")>-1) strMsg = "";
				strMsg = "连接池对象【"+pool_name+"】不存在 "+ strMsg;
				throw new Exception(strMsg);
			}
			if(dsObj==null) return null;
			ds   = (DataSource)dsObj;
			m_db_pool.put(pool_name,ds);
		}
		if(ds==null){
			strMsg = "取【"+pool_name+"】连接时，没有取到数据源";
			throw new Exception(strMsg);
		}
		try{
			conn = ds.getConnection();
			conn.setAutoCommit(bAutoCommit);
			return conn ;
		}catch(Exception e){
			strMsg = "取【"+pool_name+"】数据库连接异常"+e.getMessage();
			throw new Exception(strMsg);
		}
	}

    /*
	描述：
	*<p>释放资源</p>
	* @param bSuccFlag 是否提交(对外是否成功标志，成功则提交，否则回滚)
	* @return 无
	*/
	public static void free(Connection conn,int iSuccFlag){
		if(iSuccFlag<0){
			free(conn,false);
		}else{
			free(conn,true);
		}
	}
	public static  void free(Connection conn,boolean bSuccFlag){
		if(conn==null) return ;
		boolean bIsClosed   = false;
        boolean bAutoCommit = false;
		try{
			bIsClosed  = conn.isClosed();
        	bAutoCommit= conn.getAutoCommit();
			if (!bIsClosed && !bAutoCommit){
				if (bSuccFlag){
					System.out.println("commit");
					conn.commit();
				}else{
					System.out.println("rollback");
					conn.rollback();
				}
				conn.setAutoCommit(true);
			}
			conn.close();
		}catch(Exception e){

		}
		conn = null;
	}
	/*
	描述：
	*<p>取得集合(RSTemp)中的字段值</p>
	* @param RSTemp       记录集合名
	* @param f_name       字段名
	* @param f_def        缺省值
	* @param bTrim        是否去掉空格
	* @return 字段值
	*/
	public  static void putValue(HashMap aHash,ResultSet RSTemp,String f_name,String f_def,boolean bTrim){
		if(aHash==null || RSTemp==null || f_name==null || f_name.trim().equals("")) return ;
		String f_value = null;
		try{
			f_value = RSTemp.getString(f_name);
			if(f_value==null) f_value = f_def;
			if(f_value==null) return ;
			if(bTrim) f_value = f_value.trim();
			aHash.put(f_name,f_value);
		}catch(Exception e){

		}
	}
	public  static String getValue(ResultSet RSTemp,String f_name ,String f_def,boolean bTrim){
		String f_value = f_def;
		if(RSTemp==null || f_name==null) return f_value;
		f_name = f_name.trim();
		try{
			f_value = RSTemp.getString(f_name);
		}catch(Exception e){
			System.out.println("取字段"+f_name+"值异常\n"+e.getMessage());
		}
		if(f_value==null) f_value = f_def;
		if(bTrim && f_value!=null) f_value = f_value.trim();
		return f_value;
	}
	/*
	描述：
	*<p>取得集合(RSTemp)中的字段名,bRepeating 是否能够重复</p>
	* @param RSTemp       记录集合名
	* @param hashField    保存字段详细描述的内容
	* @param bRepeating   字段是否能够重复
	* @return 字段描述列表
	*/
	public  static ArrayList getFields(ResultSet RSTemp,boolean bRepeating){
		int iLoopNum        = 0;
		int iLoop           = 0;
		String    fieldName = null; //字段名称
		String    fieldType = null;
		ArrayList aList     = null;
		ResultSetMetaData rsdm = null;
		boolean    bExists  = false;
		if(RSTemp==null) return null;
		try{
			rsdm   = RSTemp.getMetaData();
			if(rsdm!=null) iLoopNum = rsdm.getColumnCount();
			for (iLoop=1;iLoop<=iLoopNum;iLoop++){
				fieldName = rsdm.getColumnName(iLoop);
				if (fieldName==null || fieldName.trim().equals("")) continue;
				if(aList==null) aList = new ArrayList(20);
				if(!bRepeating ){
					bExists = CommFunc.judgeExists(aList,fieldName);
					if(bExists) continue;
				}
				fieldName = fieldName.trim().toLowerCase();
				aList.add(fieldName);
			}
		}catch(Exception e){
			System.out.println("取得集合(RSTemp)中的字段名" + e.getMessage());
			return null;
		}
		return aList;
	}
	/*
	描述：
	*<p>依据指定的SQL语句取得指定范围的记录集合</p>
	* @param strSQL      SQL语句
	* @param iStart      开始记录行
	* @param iEnd        结束记录行
	* @param aList       保存查询结果
	* @param hashField   保存查询字段名信息
	* @return 返回查询的记录数
	*/
	public static  int getRecordSet(int iErrFlag,String strDesc,Connection conn,String strSQL,ArrayList aList) throws Exception {
		return getRecordSet( iErrFlag, strDesc, conn, strSQL, 0, 0, aList,null);
	}
	public static  int getRecordSet(int iErrFlag,String strDesc,Connection conn,String strSQL,int iStart,int iEnd,ArrayList aList) throws Exception {
		return getRecordSet( iErrFlag, strDesc, conn, strSQL, iStart, iEnd, aList,null);
	}
	public static  int getRecordSet(int iErrFlag,String strDesc,Connection conn,String strSQL,int iStart,int iEnd,ArrayList aList,ValueObject valuePack) throws Exception {
		Statement  stmt   = null;
		ResultSet  RSTemp = null;
		String     strMsg = null;
		String     f_name = null;
		String     f_value= null;
		ArrayList  fList  = null;
		HashMap    aHash  = null;
		int iRecCount     = 0;
		int iLoopNum      = 0;
		int iLoop         = 0;
		try{
			if(valuePack!=null) {
				if(strDesc==null) strDesc = "";
				valuePack.addTrace(String.valueOf(iErrFlag)+" " + strDesc);
				valuePack.addTrace(strSQL);
			}
			stmt     = conn.createStatement();
			RSTemp   = stmt.executeQuery( strSQL);
			fList    = getFields(RSTemp,false);

			iLoopNum = 0;
			if(fList!=null) iLoopNum = fList.size();
			while (RSTemp != null && RSTemp.next()) {
				iRecCount ++;
				if ( iStart > 0 && iRecCount < iStart) continue;
				if ( iEnd   > 0 && iRecCount > iEnd  ) continue;
				aHash = null;
				for (iLoop=0;iLoop<iLoopNum;iLoop++){
					f_name  = (String)fList.get(iLoop);
					f_value = RSTemp.getString(f_name);
					if (f_name==null || f_value==null) continue;
					if(aHash==null)aHash=new HashMap();
					aHash.put(f_name,f_value);
				}
				if(aHash==null) continue;
				aList.add(aHash);
			}
			if(RSTemp!=null) RSTemp.close();
			RSTemp = null;
		}catch(Exception e){
			CommFunc.freeObj(aList);
			if(aList!=null) aList.clear();
			strMsg = "发生异常\n" + e.getMessage();
			System.out.println(strMsg);
			System.out.println("SQL******************* \n"+strSQL );
			if(valuePack!=null) valuePack.addTrace(strMsg);
			throw new Exception(strMsg);
		}finally{
			try{
				if(RSTemp!=null) RSTemp.close();
			}catch(Exception e1){
			}
			try{
				if(stmt!=null) stmt.close();
			}catch(Exception e1){
			}
			CommFunc.freeObj(fList);
			if(fList!=null) fList.clear();
			fList = null;
			RSTemp= null;
		}
		return iRecCount;
	}
	/*
	描述：
	*<p>执行SQL语句</p>
	* @param iErrFlag    错误号
	* @param strDesc     执行业务描述
	* @param strSQL      SQL语句
	* @return 返回执行SQL语句影响的数据行数
	*/
	public static int runSql(int iErrFlag,String strDesc,Connection conn,String strSQL,ValueObject valuePack) throws Exception{
		String     strMsg   = null;