dataaccess.java

jsp考勤

/*
 *	Copyright (C) 2000, TOA-System Co.  All rights reserved.
 */
package dbcommon;

import java.util.*;
import java.sql.*;
import util.*;
import entity.*;
import exce.*;

/**
 * 偙偺僋儔僗偼SQL傪幚峴偡傞偨傔偺僋儔僗偱偡丅
 * <pre>
 * </pre>
 *
 * @author  Toa System Corporation
 * @version 2.0
 * @since   2.0
 */

public class DataAccess {
	public String LOGINID;
	public String MYTTY;
	Statement stmt = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	Status st = new Status();
	int size = 0;
	ChangeField cf = new ChangeField();

	/**
	 *	僥乕僽儖偺Lock(僶僢僠張棟偺傒偱巊梡)
	 *	@param  con			 DBConnection
	 *	@param  tablename	 儘僢僋傪峴偆僥乕僽儖柤
	 *  @return 側偟
	 */
	public void tableLock(Connection con, String tablename) {
		try {
			// 偙偺儊僜僢僪偱幚峴偡傞SQL暥偱偡丅
			String sql = "LOCK TABLE " + tablename + " IN EXCLUSIVE MODE NOWAIT";

			stmt = con.createStatement();
			stmt.execute(sql);

		} catch (SQLException e) {
			// 僄儔乕儊僢僙乕僕偺僨僐乕僪
			// Oracle 僄儔乕偺僉儍僢僠
			if (OracleException.isOracleException(e)){	//Oracle偺僄儔乕側傜
				throw new OracleException(e);			//Oracle梡偱張棟
			} else {									//堘偭偨傜
				throw new OriginalException(e);			//捠忢梡偱張棟
			}
		} finally {
			// Statemet偼丆昁偢close()偟傑偡丅
			try {
				if (stmt != null) stmt.close();
			} catch (Exception ex) { }
		}
	}

	/**
	 *	儗僐乕僪儘僢僋
	 *	@param  con			 DBConnection
	 *	@param  tablename	 儘僢僋傪峴偆僥乕僽儖柤
	 *  @return 専嶕僸僢僩審悢
	 */
	public int lock(Connection con, String tablename, String whe) {
		try {
			// 偙偺儊僜僢僪偱幚峴偡傞SQL暥偱偡丅
			String sql = "SELECT * FROM " + tablename + " " + whe + " FOR UPDATE NOWAIT";

			pstmt = this.sqlSet(con,sql);
			rs = this.exeQuery(pstmt);

			int size = 0;
			while (rs.next()) {
				size = size + 1;
			}
			return size;

		} catch (OracleException orae) {
			st = orae.getStatus();
			throw new OracleException(st);
		} catch (Exception e) {
			throw new OriginalException(e);
		} finally {
			// Statemet偼丆昁偢close()偟傑偡丅
			try {
				if (rs != null) rs.close();
				if (pstmt != null) pstmt.close();
			} catch (Exception ex) { }
		}
	}

	/**
	 *	SQL暥偺僙僢僩
	 *	@param  con			DBConnection
	 *	@param  whe			SQL暥
	 *  @return PreparedStatement	PreparedStatement僆僽僕僃僋僩
	 */
	public PreparedStatement sqlSet(Connection con, String sql) {
		try {
			//LoggingWriter.loggingWriter(sql);

			// 偙偺儊僜僢僪偱幚峴偡傞SQL暥偱偡丅
			
			pstmt = con.prepareStatement(sql);
			return pstmt;

		} catch (SQLException e) {
			// 僄儔乕儊僢僙乕僕偺僨僐乕僪
			// Oracle 僄儔乕偺僉儍僢僠
			if (OracleException.isOracleException(e)){	//Oracle偺僄儔乕側傜
				throw new OracleException(e);			//Oracle梡偱張棟
			} else {									//堘偭偨傜
				throw new OriginalException(e);			//捠忢梡偱張棟
			}
		} catch (Exception e) {
			throw new OriginalException(e);
		} finally {
		}
	}

	/**
	 *	SQL僋僄儕乕偺幚峴
	 *	@param  con			DBConnection
	 *	@param  whe			SQL暥
	 *  @return ResultSet	ResultSet僆僽僕僃僋僩
	 */
	public ResultSet exeQuery(PreparedStatement pstmt) {
		ResultSet rs = null;
		try {
			// 偙偺儊僜僢僪偱幚峴偡傞SQL暥偱偡丅
			rs = pstmt.executeQuery();

			return rs;

		} catch (SQLException e) {
			// 僄儔乕儊僢僙乕僕偺僨僐乕僪
			// Oracle 僄儔乕偺僉儍僢僠
			if (OracleException.isOracleException(e)){	//Oracle偺僄儔乕側傜
				throw new OracleException(e);			//Oracle梡偱張棟
			} else {									//堘偭偨傜
				throw new OriginalException(e);			//捠忢梡偱張棟
			}
		} catch (Exception e) {
			throw new OriginalException(e);
		} finally {
		}
	}

	/**
	 *	exeUpdate偺幚峴
	 *	@param  con			DBConnection
	 *	@param  whe			SQL暥
	 *  @return 峏怴審悢
	 */
	public int exeUpdate(PreparedStatement pstmt) {
		try {
			// 偙偺儊僜僢僪偱幚峴偡傞SQL暥偱偡丅
			int i = pstmt.executeUpdate();

			return i;

		} catch (SQLException e) {
			// 僄儔乕儊僢僙乕僕偺僨僐乕僪
			// Oracle 僄儔乕偺僉儍僢僠
			if (OracleException.isOracleException(e)){	//Oracle偺僄儔乕側傜
				throw new OracleException(e);			//Oracle梡偱張棟
			} else {									//堘偭偨傜
				throw new OriginalException(e);			//捠忢梡偱張棟
			}
		} catch (Exception e) {
			throw new OriginalException(e);
		} finally {
		}
	}

	/**
	 *	SQL偺幚峴
	 *	@param  con			DBConnection
	 *	@param  whe			SQL暥
	 *  @return 峏怴審悢
	 */
	public int exeSQL(Connection con, String sql) {
		try {

			// 偙偺儊僜僢僪偱幚峴偡傞SQL暥偱偡丅
			pstmt = this.sqlSet(con,sql);

			int i = this.exeUpdate(pstmt);

			return i;

		} catch (OracleException orae) {
			st = orae.getStatus();
			throw new OracleException(st);
		
		//2003/7/18  SQLException嶍彍
		/*
		} catch (SQLException e) {
			// 僄儔乕儊僢僙乕僕偺僨僐乕僪
			// Oracle 僄儔乕偺僉儍僢僠
			if (OracleException.isOracleException(e)){	//Oracle偺僄儔乕側傜
				throw new OracleException(e);			//Oracle梡偱張棟
			} else {									//堘偭偨傜
				throw new OriginalException(e);			//捠忢梡偱張棟
			}
		*/
		} catch (Exception e) {
			throw new OriginalException(e);
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
			} catch (Exception ex) { }
		}
	}

	/**
	 *	斈梡SELECT儊僜僢僪
	 *	@param  con				DBConnection
	 *	@param  tablename		峏怴偡傞僥乕僽儖柤
	 *	@param  daname			峏怴偡傞僥乕僽儖擔杮岅柤
	 *	@param  htblwhe			where暥偺崁栚偺僼傿乕儖僪偲Value(HashTable)
	 *	@param  selFields		Select偟偨偄偺崁栚偺僼傿乕儖僪
	 *	@param  orderby			SQL暥偺orderby嬪
	 *  @return 崁栚柤偲抣(Object[][])
	 */
	public Object[][] SelectFields(Connection con, String tablename, String daname,
								   Hashtable htblwhe, String[] selFields, String orderby) {
		try {
			String key = "";
			String whe = "";
			String sel = "";

			// 専嶕傪峴偆僨乕僞偺where暥傪嶌惉偟傑偡丅
			if (htblwhe != null) {
				Enumeration enumwhe = htblwhe.keys();

				while(enumwhe.hasMoreElements()){
					key = enumwhe.nextElement().toString();
					if (whe.equals("")) {
						if(htblwhe.get(key).toString().equals("NULL")){
							whe = whe + "WHERE " + key + " IS NULL ";
						}else{
							whe = whe + "WHERE " + key + " = '" + htblwhe.get(key).toString() + "'";
						}
					} else {
						if(htblwhe.get(key).toString().equals("NULL")){
							whe = whe + "  AND " + key + " IS NULL ";
						}else{
							whe = whe + "  AND " + key + " = '" + htblwhe.get(key).toString() + "'";
						}
					}
				}
			}

			// Select偟偨偄Field偑側偐偭偨傜丄Select偱偒側偄偺偱丄SQLException傪throw偟傑偡丅
			int sellen = selFields.length;
			if (sellen == 0) {
				throw new SQLException();
			}

			// select暥傪嶌惉偟傑偡丅
			sel = selFields[0];
			for (int i = 1; i < sellen; i++) {
				sel = sel + ", " + selFields[i];
			}

			// 偙偺儊僜僢僪偱幚峴偡傞SQL暥偱偡丅
			String sql =	"SELECT " + sel + " FROM " + tablename + " " + whe + " " + orderby;

		    pstmt = sqlSet(con,sql);
			rs = exeQuery(pstmt);

			Vector v = new Vector();
			while (rs.next()) {
				Object[] obj = new Object[sellen];
				for (int i = 0; i < sellen; i++) {
					obj[i] = rs.getObject(selFields[i]);        // Select偟偨寢壥傪僆僽僕僃僋僩攝楍偵僙僢僩偟傑偡丅
				}

				// Entity僆僽僕僃僋僩偼丆偄偔偮SELECT偱偒偰偄傞偐
				// 傢偐傜側偄偺偱丆偄偭偨傫 Vector 偵奿擺偟傑偡丅
				v.addElement(obj);
			}

			// Vector 偐傜 Entity僆僽僕僃僋僩偺攝楍傊堏偟懼偊傑偡丅
			int size = v.size();
			Object[][] objs = new Object[size][sellen];
			objs = (Object[][])v.toArray(objs);

		    return objs;

		} catch (OracleException orae) {
			st = orae.getStatus();
			throw new OracleException(st);
		} catch (SQLException sqle) {
			throw new OracleException(sqle);
		} catch (Exception e) {
			throw new OriginalException(e);
		} finally {
			// Statemet偼丆昁偢close()偟傑偡丅
			try {
				if (rs != null) rs.close();
				if (pstmt != null) pstmt.close();
			} catch (Exception ex) { }
		}
	}

	/**
	 *	斈梡Insert儊僜僢僪
	 *	@param  con			DB僪儔僀僶偺URL側偳
	 *	@param  tablename	Insert傪峴偆僥乕僽儖柤
	 *	@param  htblfld		Insert傪峴偄偨偄崁栚偺僼傿乕儖僪偲Value(HashTable)
	 *  @return 峏怴審悢
	 */
        public int InsertData(Connection con, String tablename, Hashtable htblfld) {
		try {
			String field = "";
			String all_field = "";
			String value = "";

			// insert暥偺撪梕傪嶌惉偟傑偡丅僼傿乕儖僪掕媊偲Value掕媊
			if (htblfld != null) {
				Enumeration enumfld = htblfld.keys();
				while(enumfld.hasMoreElements()){
					field = enumfld.nextElement().toString();
					if(all_field.equals("")){
						all_field = all_field + field;
						value     = value + "'" + htblfld.get(field).toString() + "'";
					}else{
						all_field = all_field + "," + field;
						value     = value + "," + "'" + htblfld.get(field).toString() + "'";
					}
				}
			}

			// 偙偺儊僜僢僪偱幚峴偡傞SQL暥偱偡丅
			String sql = "INSERT INTO " + tablename + " (" + all_field + ") VALUES (" + value + ")";
			pstmt = sqlSet(con,sql);
			size = exeUpdate(pstmt);

			return size;

		} catch (OracleException orae) {
			st = orae.getStatus();
			throw new OracleException(st);
		} catch (Exception e) {
			throw new OriginalException(e);
		} finally {
			// Statemet偼丆昁偢close()偟傑偡丅
			try {
				pstmt.close();
			} catch (Exception ex) { }
		}
	}

	/**
	 *	斈梡UPDATE儊僜僢僪
	 *	@param  con				DBConnection
	 *	@param  tablename		峏怴偡傞僥乕僽儖柤
	 *	@param  daname			峏怴偡傞僥乕僽儖擔杮岅柤
	 *	@param  htblwhe			where暥偺崁栚偺僼傿乕儖僪偲Value(HashTable)
	 *	@param  htblup			update偺崁栚偺僼傿乕儖僪偲Value(HashTable)
	 *	@param  otherset		廋惓僆儁儗乕僞丄廋惓擔晅偺崁栚傪峏怴偟偨偄応崌偼乽""乿峏怴偟偨偔側偄応崌偼乽NOMODIFY乿偲僙僢僩
	 *  @return 峏怴審悢
	 */
	public int UpdateFields(Connection con, String tablename, String daname,
							 Hashtable htblwhe, Hashtable htblup, String otherset) {
		try {