dbfunc.java~21~

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

	}
	/*
	描述：
	*<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;
		Statement  stmt     = null;

		try{
			if(valuePack!=null) {
				if(strDesc==null) strDesc = "";
				valuePack.addTrace(String.valueOf(iErrFlag)+" " + strDesc);
				valuePack.addTrace(strSQL);
			}
			if(conn==null || conn.isClosed()) return 0;
			stmt   = conn.createStatement();
			return stmt.executeUpdate(strSQL);
		}catch(Exception e){
			strMsg =  iErrFlag + " "+strDesc + "执行异常\n"  + e.getMessage();
			if(valuePack!=null) valuePack.addTrace(strMsg);
			System.out.println(strMsg);
			throw new Exception (strMsg);
		}finally{
			try{
				if(stmt!=null) stmt.close();
			}catch(Exception e){

			}
			stmt   = null;
		}
	}

	/*
	描述：
	*<p>取得当前时间</p>
	* @param conn       数据库连接
	* @param s_formate  时间格式字符串
	* @return 返回当前时间
	*/
	public static String getCurTime(Connection conn) {
		return getCurTime(conn,"");
	}
	public static String getCurTime(Connection conn,String s_formate) {
		String     strMsg   = null;
		Statement  stmt     = null;
		ResultSet  RSTemp   = null;
		String     strSQL   = null;

		if(s_formate==null) s_formate = "";
		s_formate = s_formate.trim();
		if(s_formate.equals("")) s_formate = "YYYY-MM-DD HH24:MI:SS";
		try{
			strSQL = "select to_char(sysdate,'"+s_formate +"') as cur_time  from dual";
			if(conn==null || conn.isClosed()) return "";
			stmt   = conn.createStatement();
			RSTemp = stmt.executeQuery( strSQL);
			if (RSTemp != null && RSTemp.next()) {
				return  RSTemp.getString(1);
			}
		}catch(Exception e){
			strMsg =  "取得当前时间异常:\n"+strSQL + "\n"  + e.getMessage();
			System.out.println(strMsg);
		}finally{
			try{
				if(RSTemp!=null) RSTemp.close();
			}catch(Exception e){
			}
			try{
				if(stmt!=null) stmt.close();
			}catch(Exception e){

			}
			RSTemp = null;
			stmt   = null;
		}
		return "";
	}

	/*
	描述：
	*<p>依据指定的SQL语句取得指定业务描述的的一个数值</p>
	* @param iErrFlag    错误号
	* @param strDesc     执行业务描述
	* @param strSQL      SQL语句
	* @param iDef        缺省值
	* @return 返回第一个字段的数值
	*/
	public static int getNumValue(int iErrFlag,String strDesc,Connection conn,String strSQL,int iDef,ValueObject valuePack) throws Exception {

		String     strMsg   = null;
		Statement  stmt     = null;
		ResultSet  RSTemp   = null;
		try{
			if(valuePack!=null) {
				if(strDesc==null) strDesc = "";
				valuePack.addTrace(String.valueOf(iErrFlag)+" " + strDesc);
				valuePack.addTrace(strSQL);
			}
			if(conn==null || conn.isClosed()) return iDef;
			stmt   = conn.createStatement();
			RSTemp = stmt.executeQuery( strSQL);
			if (RSTemp != null && RSTemp.next()) {
				return  RSTemp.getInt(1);
			}
		}catch(Exception e){
			strMsg =  "取得(存在)数字值异常:"  + e.getMessage();
			if(valuePack!=null) valuePack.addTrace(strMsg);
			strMsg =  "取得(存在)数字值异常:\n"+strSQL + "\n"  + e.getMessage();
			throw new Exception (strMsg);
		}finally{
			try{
				if(RSTemp!=null) RSTemp.close();
			}catch(Exception e){
			}
			try{
				if(stmt!=null) stmt.close();
			}catch(Exception e){

			}
			RSTemp = null;
			stmt   = null;
		}
		return iDef;
	}

	public static int  runsql(String strSQL) throws Exception{
		if(strSQL==null || strSQL.trim().equals("")) return 0;

		Connection conn     = null;
		Statement  stmt     = null;
		String     strMsg   = null;
		int        iSuccFlag= 0;
		conn = getConnection("",true);

		if(conn==null) {
			throw new Exception ("数据库连接为空");
		}
		try{
			stmt   = conn.createStatement();
			return stmt.executeUpdate(strSQL);
		}catch(Exception e){
			iSuccFlag = -1;
			strMsg =  "runsql *****\n"  + strSQL;
			System.out.println(strMsg);
			throw new Exception (e.getMessage());
		}finally{
			try{
				if(stmt!=null) stmt.close();
			}catch(Exception e){

			}
			free(conn,iSuccFlag);
			conn   = null;
			stmt   = null;
		}
	}

	/*
	描述：
	*<p>依据指定表名称(业务)，取得指定长度的序号</p>
	* @param pool_name   连接池名称
	* @param table_name  表名称(业务)
	* @param iSeqLen     指定长度
	* @param prefix      前缀字符串
	* @return 返回定长的带日期的序列
	*/
	public static String getSequence(int iErrFlag,String table_name,int iSeqLen,String prefix) throws Exception {
		return getSequence( iErrFlag, table_name, iSeqLen, prefix,null);
	}
	public static String getSequence(int iErrFlag,String table_name,int iSeqLen,String prefix,ValueObject valuePack) throws Exception {
		String     strSQL   = null;
		String     strMsg   = null;
		String     seq_name = "";
		String     seq_value= "";
		String     cur_date = "";
		Connection conn     = null;
		Statement  stmt     = null;
		ResultSet  RSTemp   = null;
		int        iExistNum= 0;
		int        iSuccFlag= 0;
		conn = getConnection("",true);
		if(conn==null) {
			throw new Exception ("数据库连接为空");
		}
		if(table_name==null || table_name.trim().equals("")) table_name = "default";
		if(prefix==null) prefix = "";

		table_name= table_name.trim().toUpperCase();
		prefix    = prefix.trim();
		seq_name = "SEQ_"+table_name;

		if(valuePack!=null) valuePack.addTrace(String.valueOf(iErrFlag)+"取业务["+seq_name+"]序列");

		//判断序列是否存在
		strSQL = "select count(*) from user_sequences a \n"
				+"where a.sequence_name = '"+seq_name+"'";
		if(valuePack!=null) valuePack.addTrace(strSQL);
		try{
			stmt   = conn.createStatement();
			RSTemp = stmt.executeQuery( strSQL);
			if (RSTemp != null && RSTemp.next()) {
				iExistNum = RSTemp.getInt(1);
			}
			if(RSTemp!=null) RSTemp.close();
			RSTemp = null;
			if(iExistNum<1){
				strSQL  = "create sequence "+seq_name+" \n"
						+ "minvalue 1 maxvalue 99999 start with 1 \n"
						+ "increment by 1 cache 20";
				if(valuePack!=null) valuePack.addTrace(strSQL);
				stmt.executeUpdate(strSQL);
			}
			strSQL  = "select to_char(sysdate,'YYYYMMDD') as cur_date,"+seq_name+".nextval from dual";
			if(valuePack!=null) valuePack.addTrace(strSQL);
			RSTemp = stmt.executeQuery( strSQL);
			if (RSTemp != null && RSTemp.next()) {
				cur_date  = RSTemp.getString(1);
				seq_value = RSTemp.getString(2);
			}
			if(RSTemp!=null) RSTemp.close();
			RSTemp = null;
			if(seq_value==null) seq_value = "";
			seq_value = seq_value.trim();

			while(seq_value.length()<iSeqLen) seq_value = "0" + seq_value;
			return prefix+cur_date+seq_value;
		}catch(Exception e){
			strMsg = "取序列异常"+ e.getMessage();
			if(valuePack!=null) valuePack.addTrace(strMsg);
			throw new Exception ("取序列异常"+ e.getMessage());
		}finally{
			try{
				if(RSTemp!=null) RSTemp.close();
			}catch(Exception e){
			}
			try{
				if(stmt!=null) stmt.close();
			}catch(Exception e){

			}
			free(conn,iSuccFlag);
			conn   = null;
			RSTemp = null;
			stmt   = null;
		}
	}
}