sqlutil.java

招标投标网上系统

package cn.com.syntc.common.util;

import java.io.*;
import java.math.*;
import java.sql.*;
import java.text.*;
import java.util.*;

import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.http.*;

import cn.com.syntc.common.io.*;

/**
 * 数据库操作类<br>
 * @author wang yong
 * @version 1.0
 */
public class SqlUtil {
  // DEBUG FLAG
  private final static boolean DEBUG = true;

  // 数据库连接
  private Connection _Conn = null;

  private Statement _Stmt = null;

	private String _Sql = null;
	private byte[][] _TemplateStrings = null;
	protected String[] _ParameterStrings = null;
	protected InputStream[] _ParameterStreams = null;
	protected boolean[] _IsStream = null;
	protected boolean[] _IsNull = null;

	class EndPoint
	{
		int begin;
		int end;

		EndPoint(int b, int e)
		{
			begin = b;
			end = e;
		}
	}

  /**
   * 构造函数
   */
  public SqlUtil() 
    throws CommonException {
    // 取得连接
    try{
      getConnetionFromDatasource();
    }
    catch(Exception ex){
      System.out.println("取得连接失败【"+ex.getMessage()+"】");
    }
  }

  /**
   * 从连接池中取得连接
   */
  private void getConnetionFromDatasource() 
    throws CommonException {
    try{
      InitialContext initialContext = new InitialContext ();
      Context envContext = (Context) initialContext.lookup ("java:comp/env");
      DataSource dataSource = (DataSource) envContext.lookup ("jdbc/MysqlDB");

      // connecting to Databse
      _Conn = dataSource.getConnection();

      // Desable AutoCommit
      _Conn.setAutoCommit(false);

    }catch(SQLException sqle){
      if(this.DEBUG){
        sqle.printStackTrace();
      }
      throw new CommonException();
    }catch(NamingException ne){
      if(this.DEBUG){
        ne.printStackTrace();
      }
      throw new CommonException();
    }catch(Exception e){
      if(this.DEBUG){
        e.printStackTrace();
      }
      throw new CommonException();
    }
  }

  /**
   * 执行查询语句，将结果转存到ResultSet中
   */
	public cn.com.syntc.common.type.ResultSet executeQuery(String sql)
    throws CommonException {
    
    try{
        ResultSet resultset = null;

        if(_Stmt!=null){
          _Stmt.close();
        }

        _Stmt = _Conn.createStatement();

        System.out.println("FrameWork：Execute Sql = " + StringUtil.ISO2GB2312(sql) );

        resultset = _Stmt.executeQuery(sql);

        return remakeResultSet(resultset);
    }
    catch(Exception ex){
      if(this.DEBUG){
        ex.printStackTrace();
      }
      throw new CommonException();
    }
  }

  /**
   * 执行查询语句，将结果转存到ResultSet中
   */
	public cn.com.syntc.common.type.ResultSet executeQuery()
    throws CommonException {
    
    try{
        String sql = "";
        ResultSet resultset = null;

        if(_Stmt!=null){
          _Stmt.close();
        }

        _Stmt = _Conn.createStatement();

        for(int i=0;i<_ParameterStrings.length;i++){
          sql += new String(_TemplateStrings[i]) + _ParameterStrings[i];
        }

        if( _ParameterStrings.length == 0){
          sql = new String(_TemplateStrings[0]);
        }

        System.out.println("FrameWork：Execute Sql = " + StringUtil.ISO2GB2312(sql) );

        resultset = _Stmt.executeQuery(sql);

        return remakeResultSet(resultset);
    }
    catch(Exception ex){
      if(this.DEBUG){
        ex.printStackTrace();
      }
      throw new CommonException();
    }
  }

  /**
   * 执行更新语句
   */
	public int executeUpdate(String sql)
    throws CommonException {
    
    try{
        int updateCount = 0;

        if(_Stmt!=null){
          _Stmt.close();
        }

        _Stmt = _Conn.createStatement();

        System.out.println("FrameWork：Execute Sql = " + StringUtil.ISO2GB2312(sql) );

        updateCount = _Stmt.executeUpdate(sql);

        return updateCount;
    }
    catch(Exception ex){
      if(this.DEBUG){
        ex.printStackTrace();
      }
      throw new CommonException();
    }
  }

  /**
   * 执行更新语句
   */
	public int executeUpdate()
    throws CommonException {
    
    try{
        String sql = "";
        int updateCount = 0;

        if(_Stmt!=null){
          _Stmt.close();
        }

        _Stmt = _Conn.createStatement();

        for(int i=0;i<_ParameterStrings.length;i++){
          sql += new String(_TemplateStrings[i]) + _ParameterStrings[i];
        }

        if( _ParameterStrings.length == 0){
          sql = new String(_TemplateStrings[0]);
        }

        System.out.println("FrameWork：Execute Sql = " + StringUtil.ISO2GB2312(sql) );

        updateCount = _Stmt.executeUpdate(sql);

        return updateCount;
    }
    catch(Exception ex){
      if(this.DEBUG){
        ex.printStackTrace();
      }
      throw new CommonException();
    }
  }


  /**
   * 执行SQL语句
   */
	public boolean execute(String sql)
    throws CommonException {
    
    try{
        boolean isOk = false;

        if(_Stmt!=null){
          _Stmt.close();
        }

        _Stmt = _Conn.createStatement();

        System.out.println("FrameWork：Execute Sql = " + StringUtil.ISO2GB2312(sql) );

        isOk = _Stmt.execute(sql);

        return isOk;
    }
    catch(Exception ex){
      if(this.DEBUG){
        ex.printStackTrace();
      }
      throw new CommonException();
    }
  }

  /**
   * 执行查询语句，将结果转存到ResultSet中
   */
	public cn.com.syntc.common.type.ResultSet remakeResultSet(ResultSet resultset)
    throws CommonException {
    
    try{
        cn.com.syntc.common.type.ResultSet newResultSet = new cn.com.syntc.common.type.ResultSet();
        
        ArrayList columnArrayList = new ArrayList();
        ArrayList columnTypeArrayList = new ArrayList();
        ResultSetMetaData metadata = resultset.getMetaData();
        int columnSize = metadata.getColumnCount();
        String columnTypeName;
        for(int columnNum=0; columnNum<columnSize;columnNum++){
            columnArrayList.add(columnNum,metadata.getColumnName(columnNum+1).toUpperCase());
            columnTypeArrayList.add(columnNum,metadata.getColumnTypeName(columnNum+1).toUpperCase());
        }

        // 3. make result.
        int rowNumber = 1;
        while(resultset.next()){
          for(int columnNum=0; columnNum<columnSize; columnNum++){
            newResultSet.setRow(rowNumber,(String)columnArrayList.get(columnNum),convertNull(resultset.getString(columnNum+1)));
          }
          rowNumber++;
        }

        newResultSet.rewind();

        if(resultset!=null){
          resultset.close();
        }

        return newResultSet;
    }
    catch(Exception ex){
      if(this.DEBUG){
        ex.printStackTrace();
      }
      throw new CommonException();
    }
  }

  /**
   * This method is null string change to string "".
   * @parm value value of fetched string form database
   */
  private String convertNull(String value) {
    try{
      if(value == null){
        return "";
      } else {
        // No change
        return value;
      }
    }catch(Exception e){
      //error
      return value;
    }
  }


	public  void close() 
    throws CommonException {

		if (_Stmt != null) {
			try
			{
				_Stmt.close();
			}
			catch (Exception ex) {
			}
		}
		
		if (_Conn != null) {
			try
			{
				_Conn.close();
			}
			catch (Exception ex) {
			}
		}

		_Stmt = null;
		_Conn = null;
	}

  /**
   * 创建preparedStatement
   */
	public void preparedStatement(String Sql)
		throws CommonException {

  try{
		char[] statementAsChars = Sql.toCharArray();

		int statementLength = statementAsChars.length;

		int placeHolderCount = 0;

		for (int i = 0; i < statementLength; i++)
		{
			if (statementAsChars[i] == '?')
			{
				placeHolderCount++;
			}
		}

		Vector V = new Vector(placeHolderCount + 1);
		boolean inQuotes = false;
		int lastParmEnd = 0, i;

		_Sql = Sql;

		int pre1 = 0;
		int pre2 = 0;

		for (i = 0; i < statementLength; ++i)
		{
			int c = statementAsChars[i];

			if (c == '\'' && pre1 == '\\' && pre2 == '\\')
			{