dboperat.java

功能是公安和学校的管理系统,用struts+hibernate+spring等匡架组成,在eclepse就能编译

/*
* 2006 jfchen . All Rights Reserved.
* 本 Java 程序为数据库操作共通的一部分开发.
* 
*/
/********************************************************************
* NAME        : DBOperator
* FUNCTION    : SQL语句相关共通
* PROGRAMMED  :hexiesoft
* DATE(ORG)   : 2006-4-14
* 
********************************************************************/
package com.db;

import java.sql.*;
import java.util.ArrayList;


/**
Description   SQL语句相关共通 Advanced Database operator.
@author       jfchen
@version      1.0
Copyright     All Rights Reserved,
 */
public class dboperat {
	
	private Connection _conn = null;	
	private Statement _stmt = null;
	private ResultSet _rset = null;
	private boolean _auto = true;
		public dboperat() {
	
	
		
		this._auto = true;
	}
	
		public  Connection connection()
		{
		try{
				        
		//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
		String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=D:/yelin/10.23.mdb"; 
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
		_conn=DriverManager.getConnection(url); 
		}
		catch(Exception e)
		{
			}
		return _conn ;
		}
		
		
			private void _close() {
		try {
			//System.out.println("ffffff");
			if (_rset != null)
				_rset.close();
			if (_stmt != null)
				_stmt.close();
		} catch (Exception e1) {
			//cat.debug("_rset and _stmt is closed.");
		}
		_rset = null;
		_stmt = null;
		}

		/** Function : 释放数据库连接
		*/
		private void _DB_Disconnect() {
		_close();
		if (this._auto) {
			try {
				//System.out.println("A connection is closed .. at _DB_Close");
				//cat.debug("A connection is closed .. at _DB_Close");
				//System.out.println("自动态释放连接");
			//	DbManger.freeConnection(_conn);
			} catch (Exception e) {
				//cat.debug("在释放 _conn 时出错。");
			}
			_conn = null;
		}
		}

		/** Function : 手动释放数据库连接
		*/
		public void removeConnect() {
		if (!(this._auto)) {
			try {
				//DbManger.freeConnection(_conn);
			} catch (Exception e) {
			//	cat.debug("在释放 _conn 时出错。");
			}
			_conn = null;
		}
		}

		/** Function : 根据参数查询表并返回记录集
		* @param  strSql : 完整的查询语句
		* @return ResultSet : 记录集
		*/
		private void ExecuteSelect(String strSql)
		throws java.lang.NullPointerException, java.sql.SQLException {
		try {
			_stmt = _conn.createStatement();
			_rset = _stmt.executeQuery(strSql.replaceAll("\1", "?"));
		} catch (NullPointerException eNULL) {
			throw new NullPointerException();
		} catch (SQLException eSQL) {
			throw new SQLException();
		}
		}
		/**
		* 事务处理操作
		* @param sql
		* @return
		*/
		public int ShiwuchuliArylist(ArrayList SqlList)
		throws java.lang.NullPointerException, java.sql.SQLException {
		this.connection();
		int flag=1;
		if(SqlList.size()!=0 && SqlList!=null){
			try{
				_conn.setAutoCommit(false);
				Statement stmt = _conn.createStatement();
				for(int j =0;j<SqlList.size();j++){
					if(SqlList.get(j) != null && SqlList.get(j).toString().trim() != ""){
						stmt.executeUpdate(SqlList.get(j).toString());
					}
				}
				_conn.commit();
				_conn.setAutoCommit(true);
			}catch(Exception ex){
				flag=0;
				ex.printStackTrace();
				try {
					_conn.rollback();
					_conn.setAutoCommit(true);
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
		this._DB_Disconnect();
		return (flag);
		}
		/**Function: 根据输入的 SQL 文执行数据库的 UPDATE 或 DELETE 操作
		* @param  String strSql : 完整的SQL语句
		* @return int    : 1 = 正常执行, 0 = 执行错误
		*/
		public int ExecuteSQL(String strSQL)
		throws java.lang.NullPointerException, java.sql.SQLException {
		this.connection();
		try {
		    _stmt = _conn.createStatement();
			_stmt.execute(strSQL);
		} 
		catch (Exception e) {
				throw new NullPointerException("NullPointerException at ExecuteSQL,可能是 connection 为 NULL");
		}
		/*	catch (NullPointerException eNULL) {
			cat.debug(
				"NullPointerException at ExecuteSQL:"
					+ eNULL.toString()
					+ ", 可能是 connection 为 NULL. ");
			throw new NullPointerException("NullPointerException at ExecuteSQL,可能是 connection 为 NULL");
		} catch (SQLException eSQL) {
			this._DB_Disconnect();
			cat.debug(
				"SQLException at ExecuteSQL:"
					+ eSQL.toString()
					+ ", 可能是 SQL 语句有误 ，语句为："
					+ strSQL);
			throw new SQLException(
				"SQLException at ExecuteSQL,可能是 SQL 语句有误 ，语句为：" + strSQL);
		}*/
		this._DB_Disconnect();
		return 1;
		}

		/**Function : 依据传入的参数查询数据库并返回记录的二维数组  最常用的方法
		*@param     boolean ColumnList : true = 包括表头 , false = 不包括表头  
		*@param     String strSql      : 完整查询语句
		*@return    ArrayList          : 返回记录的二维数组
		 */
	 
		


		/**Function : 依据传入的参数查询数据库并返回记录的二维数组
		*@param     boolean ColumnList : true = 包括表头 , false = 不包括表头  
		*@param     String strSql      : 完整查询语句
		*@param     int nPageNo        : 返回记录集起始页  0,1,2...
		*@param     int nNumberPerPage : 每页记录数        1,2,3...
		*@return    ArrayList          : 返回记录的二维数组
		*
		*</p><b>Example:</b>
		*</p> 
		*</p>  Suppose table "staff" with following fields:  id , name , section , time_come.
		*</p>  Now create a sql string and use this method to get data from "staff".
		*</p>  
		*</p>  line 3 make each paper with 2 records and show only the 1 page. Remember
		*</p>  the pageID parameter begin with 0 ?  So the out put is the 2nd page . 
		*</p>  -------------     create source code like below  ----------------------- 
		*</p> 
		*</p>	1:  String queryString = "select id,name,section,time_come from staff";
		*</p>	2:  DBOperator objDB = new DBOperator();
		*</p>	3:  ArrayList objArray = objDB.ExecuteSelect(true,queryString,1,2)
		*</p>	4:  for (int i=0;i<objArray.size();i++){
		*</p>	5:     ArrayList itemArray = (ArrayList)objArray.get(i);
		*</p>	6:     StringBuffer linestring = new StringBuffer();
		*</p>	7:     for (int j=0;j<itemArray.size();j++){
		*</p>	8:         linestring.append((String)itemArray.get(j)+"\t|  ");
		*</p>	9:     }
		*</p>	10:    linestring.append("\n");
		*</p>	11:    System.out.println(linestring.toString());
		*</p>	12: }
		*</p>  ----------------------------  source code end ---------------------------
		*</p> 
		*</p>  
		*/
		public ArrayList ExecuteSelect(
		String strSql,
		boolean ColumnList,		
		int nPageNo,
		int nNumberPerPage)
		throws java.sql.SQLException, java.lang.NullPointerException {
		this.connection();
		try {
			ExecuteSelect(strSql.replaceAll("\1", "?"));
		} catch (NullPointerException eNULL) {
				throw new NullPointerException("NullPointerException at ExecuteSQL,可能是 connection 为 NULL");
		} catch (SQLException eSQL) {
			this._DB_Disconnect();
						throw new SQLException(
				"SQLException at ExecuteSQL,可能是 SQL 语句有误 ，语句为：" + strSql);
		}
		ArrayList tmpList =
			Convert_ResultSet_ArrayList(ColumnList, nPageNo, nNumberPerPage);
		this._DB_Disconnect();
		return tmpList;
		}
		
		/**Function : 依据传入的参数查询数据库并返回记录的二维数组
		 *@param     boolean ColumnList : true = 包括表头 , false = 不包括表头  
		 *@param     String strSql      : 完整查询语句
		 *@return    ArrayList          : 返回记录的二维数组
		 **
		 *</p>    
		 *<b>Example:</b>
		 *</p> 
		 *</p>  Suppose table "staff" with following fields:  id , name , section , time_come.
		 *</p>  Now create a sql string and use this method to get data from "staff".
		 *</p>  Line 3 shows that all data will be output.
		 *</p>
		 *</p>  -------------     create source code like below  ----------------------- 
		 *</p>
		 *</p>  1:  String queryString = "select id,name,section,time_come from staff";
		 *</p>	2:  DBOperator objDB = new DBOperator();
		 *</p>	3:  ArrayList objArray = objDB.ExecuteSelect(true,queryString)
		 *</p>	4:  for (int i=0;i<objArray.size();i++){
		 *</p>	5:     ArrayList itemArray = (ArrayList)objArray.get(i);
		 *</p>	6:     StringBuffer linestring = new StringBuffer();
		 *</p>	7:     for (int j=0;j<itemArray.size();j++){
		 *</p>	8:         linestring.append((String)itemArray.get(j)+"\t|  ");
		 *</p>	9:     }
		 *</p>	10:    linestring.append("\n");
		 *</p>	11:    System.out.println(linestring.toString());
		 *</p>	12: }
		 *</p>  ----------------------------  source code end ---------------------------
		 *</p>
		 *</p>  ----------------------------       output     ---------------------------
		 *</p>
		 *</p>	 id     |  name         |  section      |  time_come            |
		 *</p> */
		public ArrayList ExecuteSelect(boolean ColumnList, String strSql)
			throws java.sql.SQLException, java.lang.NullPointerException {
			this.connection();
			try {
				ExecuteSelect(strSql.replaceAll("\1", "?"));
			} catch (NullPointerException eNULL) {
						throw new NullPointerException("NullPointerException at ExecuteSQL,可能是 connection 为 NULL");
			} catch (SQLException eSQL) {
				this._DB_Disconnect();
				throw new SQLException(
					"SQLException at ExecuteSQL,可能是 SQL 语句有误 ，语句为：" + strSql);
			}
			ArrayList tmpList = Convert_ResultSet_ArrayList(ColumnList, -1, -1);
			this._DB_Disconnect();
			return tmpList;
		}

		

		/**Function : 将内置 ResultSet 对象 _rset 转换为 ArrayList
		*@param     boolean ColumnList : true = 包括表头 , false = 不包括表头  
		*@param     int nPageNo        : 返回记录集起始页  0,1,2...
		*@param     int nNumberPerPage : 每页记录数        1,2,3...
		*@return    ArrayList          : 返回记录的二维数组
		*/
		private ArrayList Convert_ResultSet_ArrayList(
		boolean ColumnList,
		int nPageNo,
		int nNumberPerPage)
		throws java.sql.SQLException {
		ArrayList finalResultList = new ArrayList();
//		 用于容纳所有记录的一级 ArrayList 对象
		int iColumnNum = _rset.getMetaData().getColumnCount();
		int iRecordNo = 0;
		if (ColumnList) {
			/* 此处用于将返回记录集的头部信息(字段名称)加载到结果集中 */
			ArrayList tempList = new ArrayList();
			for (int iTemp = 1; iTemp <= iColumnNum; iTemp++) {
				String sColumnName = _rset.getMetaData().getColumnName(iTemp);
				sColumnName = (sColumnName == null) ? "" : sColumnName;
				tempList.add(sColumnName);
			}
			finalResultList.add(tempList);
		}
		try {
			while (_rset.next()) {
				/*  当 返回记录集起始页不小于0 且 每页记录数大于 0 时, 返回部分记录 */
				if ((nPageNo >= 0 && nNumberPerPage > 0)
					&& ((iRecordNo < nPageNo * nNumberPerPage)
						|| (iRecordNo >= (nPageNo + 1) * nNumberPerPage))) {
					iRecordNo++;
					continue;
				}
				ArrayList tempList = new ArrayList();
				// 新生成容纳单条记录的二级 ArrayList 对象
				for (int iTemp = 1; iTemp <= iColumnNum; iTemp++) {
					String tmpstr = _rset.getString(iTemp);
					tmpstr = (tmpstr == null) ? "" : tmpstr;
					tempList.add(tmpstr);
				}
				finalResultList.add(tempList);
				iRecordNo++;
			}
		} catch (SQLException e) {
				return null;
		}
		return finalResultList;
		}
		
		public   void  Update(String sql  ){
		// _stmt = _conn.createStatement();
			
			try {
				_stmt.executeUpdate( sql);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		

        public void  insert (String sql){
        	
        	try{
        		_stmt.execute(sql);
        		
        		
        	}catch(SQLException e){
        		
        		
        		e.printStackTrace();	
        		
        	}
        	
        
        }
        	
        	
        }