dboperator.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 DBOperator {
	
	private Connection _conn = null;	
	private Statement _stmt = null;
	private ResultSet _rset = null;
	private boolean _auto = true;

	public DBOperator() {
		this._auto = true;
	}
//	DBUtils dbut=new DBUtils();
	public  Connection connection()	{
		try{
			//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			_conn=DBUtils.getConnection();
			//_conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://218.22.22.146;SelectMethod=cursor;DatabaseName=osim","osim","123321");
		}catch(Exception e)	{
		}
		return _conn ;
	}

	private void _close() {
		try {
			if (_rset != null)	DBUtils.closeResultSet(_rset);
			if (_stmt != null)	 DBUtils.closeStatement(_stmt);
           
        	
		} catch (Exception e1) {
			//cat.debug("_rset and _stmt is closed.");
		}
		_rset = null;
		_stmt = null;
	}

	/** Function : 释放数据库连接
	*/
	private void _DB_Disconnect() {
		 DBUtils.closeStatement(_stmt);
		 DBUtils.closeConnection(_conn);
		if (this._auto) {
			_conn = null; 
		}
	}
	
	/** Function : 手动释放数据库连接
	*/
	public void removeConnect() {
	if (!(this._auto)) {
		try {
			//DbManger.freeConnection(_conn);
		} catch (Exception e) {
		//	cat.debug("在释放 _conn 时出错。");
		}
		_conn = null;
	}
	}
	
	public  Connection connection1()  //连接access参数表 
	{
		try	{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			_conn=DriverManager.getConnection("jdbc:odbc:sa","","");
		}catch(Exception e){
		}
		return _conn ;
	}

	/** 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;
	}
	
	//取表中id最大的元素id值并返回, 0表示没有
	public int getId(String tableName) throws SQLException{
		this.connection();	
		int maxId = 1;
		//判断tableName表中有无id字段
		//判断id字段是否为可更改
		//判断id字段的数据类型，为int
		_stmt = _conn.createStatement();
		_rset = null;
		_rset=_stmt.executeQuery("select max(id) from "+tableName);
		 System.out.println("------1.5------!"+maxId); 		
		if(_rset.next()){
			maxId=_rset.getInt(1)+1 ; 
		}
		this._DB_Disconnect();
		return maxId;
	}
	public int getId(String tableName,String id) throws SQLException{
		this.connection();	
		int maxId = 1;
		//判断tableName表中有无id字段
		//判断id字段是否为可更改
		//判断id字段的数据类型，为int
		_stmt = _conn.createStatement();
		_rset = null;
		_rset=_stmt.executeQuery("select max("+id+") from "+tableName);
		 System.out.println("------1.5------!"+maxId); 		
		if(_rset.next()){
			maxId=_rset.getInt(1)+1 ; 
		}
		this._DB_Disconnect();
		return maxId;
	}
	
	//调试中，暂不能用
	/**Function: 将选定表中的id字段值为空或0的记录按增序赋于不重复的整型值
	* @param  String tableName : 数据表名
	* @return int    :  更新id后该表中的最大id值, 0 = 没有需要更新的id值
	*/
	public int setId(String tableName) throws SQLException{
		this.connection();	
		int id = 0;
		//ArrayList list = null;
		//判断tableName表中有无id字段
		//判断id字段是否为可更改
		//判断id字段的数据类型，为int
		System.out.println("-------1------!"+id);
		try{
			_stmt = _conn.createStatement();
			_rset = null;
			_rset=_stmt.executeQuery("select max(id) from "+tableName);
			 System.out.println("------1.5------!"+id); 		
			if(_rset.next()){
				id=_rset.getInt(1) ; //id 取为表中最大id
			}	
			 System.out.println("------2------!"+id); 
			 String sql = "select id from "+tableName+" where id is null or id=0";
			// list = ExecuteSelect(false,sql);
			 System.out.println("------21------!"+id); 
			while(_rset.next()){    //将id为空的记录依次赋于一个不重复的值
				 System.out.println("------22------!"+id);
				 id ++;
				_rset.updateInt(1,3);//error !?jdbc版本问题，816及其以前的都有，用817就没有，强烈建议。 
				 System.out.println("------23------!"+id); 
				_rset.updateRow();
			}
		}catch(Exception e){
			System.out.println("--error--!"+id);
			id=0;
		}
		return id;
	}	

	/**Function : 依据传入的参数查询数据库并返回记录的二维数组
	*@paramg     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  boolean  update(String sql){
		boolean tru=true;
		try {
			this.connection();	
     		_stmt=_conn.createStatement();
			_stmt.executeUpdate( sql);
			  			 
		} catch (SQLException e) {
			e.printStackTrace();
		tru=false;
		}finally{
			
			DBUtils.closeStatement(_stmt);
            DBUtils.closeConnection(_conn);  
		}
		return tru;
	}

    public boolean  insert(String sql){
     	boolean tru=true;
    	String sql0="";
    	String sql2="";
       if(sql.contains("insert")){
    	int d=sql.indexOf("(", 24)+1;
        //  System.out.println("d-->"+d);	
          sql0=sql.substring(0,d);
          sql2=sql.substring(sql.lastIndexOf(")")-1,sql.length());
    	  sql=sql.substring(d,sql.lastIndexOf(")")-1);
    	  System.out.println(sql);	
    	  sql=sql.replaceAll("','", "OoPPjj");
    	  sql=sql.replaceAll(",'", "KkPpjj");
    	  sql=sql.replaceAll("',", "LlPpjj");
      	  sql=sql.replaceAll("'", "''");
    	  sql=sql.replaceAll("OoPPjj", "','");
    	  sql=sql.replaceAll("KkPpjj", ",'"); 
    	  sql=sql.replaceAll("LlPpjj", "',"); 
    	  sql=sql0+sql+sql2; 
       }
    	  
    	 System.out.println(sql);	
    	 try{
     		this.connection();	
     		_stmt=_conn.createStatement();
     		_stmt.execute(sql);
     		//_stmt.close(); 
			//_conn.close();
			 

    	}catch(SQLException e){
	  		e.printStackTrace();	
    	 tru=false;
	   	}finally{
	   		
	   	 DBUtils.closeStatement(_stmt);
         DBUtils.closeConnection(_conn);  	
	   		
	   	}
     return tru;
    }
    
    public boolean  delete(String sql){
     	boolean tru=true;
    	try{
     		this.connection();	
     		_stmt=_conn.createStatement();
     		_stmt.execute(sql);
     	
    	}catch(SQLException e){
	  		e.printStackTrace();	
    	 tru=false;
	   	}finally{
			
			DBUtils.closeStatement(_stmt);
            DBUtils.closeConnection(_conn);  
	
			
		}
     return tru;
    }
    
    
}