sqlhelper.java

一些基本jsp

package noodleWeb.ServerWeb.SQLHelper;

import java.util.LinkedList;
import java.util.ListIterator;
import java.util.List;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Enumeration;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.rmi.PortableRemoteObject;

import java.sql.SQLException;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DatabaseMetaData;
import java.sql.Connection;
import java.sql.PreparedStatement;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.NamingException;

import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.net.URL;
import java.sql.Date;
import java.math.BigDecimal;
import java.sql.Time;
import java.sql.Timestamp;

import java.lang.reflect.Field;
import java.lang.reflect.Method;

import org.apache.log4j.Logger;

import java.lang.Class;

public class SqlHelper{
	
	private static Logger logger = Logger.getLogger ( SqlHelper.class.getName());
	private Connection connection;
	private Statement statement;
	private PreparedStatement preparedStatement = null;
	
//===============================================================================================================
	//通过JNDI访问DataSource Name 从连接池中获得一条连接
	public void createConnection( String DSName ){
		DSName = "java:"+DSName ;
		DataSource dataSource = null;
		try{
			Context initialContext = new InitialContext();
			dataSource = (DataSource)initialContext.lookup( DSName );
		} catch( NamingException e ){
			logger.error( "JNDI name ["+DSName+"] not found" ); 
			e.printStackTrace();
		}
		
		try{
			connection = dataSource.getConnection();
		} catch ( SQLException e ){
			logger.error( "fetch connection error from DS ,SQL ErrorCode ["+e.getErrorCode()+"]" );
		}
		
		logger.info( "fetch connection succeed with JNDI name:["+DSName+"]" );
	}
	
//==============================================================================================================
	//创建数据访问对象Statement
	public void createStatement( Connection connection ) throws SQLException {
		try{
			statement = connection.createStatement();
		} catch ( SQLException e ){
			logger.error( "create statement error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "create statement succeed" );
	}
	
	public void createStatement(){
		try{
			statement = this.connection.createStatement();
		} catch ( SQLException e ){
			logger.error( "create statement error : SQL ErrorCode ["+e.getErrorCode()+"]" );
			e.printStackTrace();
		}
		
		logger.info( "create statement succeed" );
	}
	
	public Statement getStatement(){
		return this.statement;
	}
	
//================================================================================================================
	//初始化预处理SQL语句访问对象
	public PreparedStatement prepareStatement( String sqlString )  throws SQLException {
		
		try{	
			preparedStatement = connection.prepareStatement( sqlString );
		} catch( SQLException e ){
			logger.error( "prepareStatement:["+sqlString+"] SQL Error:["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "prepareStatement:["+sqlString+"] succeed" );
		return preparedStatement;
	}
	
	public PreparedStatement getPrepareStatement(){
		return preparedStatement;
	}
	
//===============================================================================================================
	//开始事务
	public void beginWork( Connection connection ) throws SQLException{
		
		try{
			connection.setAutoCommit( false );
		} catch ( SQLException e ){
			logger.error( "begin transaction error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "beginWork====================================>" );
	}
	
	public void beginWork() throws SQLException{
		
		try{
			this.connection.setAutoCommit( false );
		} catch ( SQLException e ){
			logger.error( "begin transaction error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "beginWork====================================>" );
	}
	
//================================================================================================================
	//回滚事务
	public void rollbackWork ( Connection connection ) throws SQLException {
		
		try{
			connection.rollback();
		} catch( SQLException e ){
			logger.error( "rollback transaction error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "rollbackWork=================================>" );
	}
	
	public void rollbackWork () throws SQLException {
		
		try{
			this.connection.rollback();
		} catch( SQLException e ){
			logger.error( "rollback transaction error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "rollbackWork=================================>" );
	}
	
//===============================================================================================================
	//提交事务
	public void commitWork ( Connection connection ) throws SQLException{
		
		try{
			connection.commit();
		} catch( SQLException e ){
			logger.error( "commit transaction error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "commitWork===================================>" );
	}
	
	public void commitWork () throws SQLException{
		
		try{
			this.connection.commit();
		} catch( SQLException e ){
			logger.error( "commit transaction error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "commitWork===================================>" );
	}
	
//===============================================================================================================
	//关闭数据访问对象
	public void clossStatement( Statement statement ) throws SQLException{
		try{
			statement.close();
		} catch ( SQLException e ){
			logger.error( "closs statement error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "closs statement succed" );
	}
	
	public void clossStatement() throws SQLException{
		try{
			this.statement.close();
		} catch ( SQLException e ){
			logger.error( "closs statement error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "closs statement succed" );
	}
	
//================================================================================================================
	//将连接重新放入缓冲池
	public void closeConnection ( Connection connection ) throws SQLException{
		
		try{
			connection.close();
		} catch( SQLException e ){
			logger.error( "close connection error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "close connection succeed" );
	}
	
	public void closeConnection () throws SQLException{
		
		try{
			this.connection.close();
		} catch( SQLException e ){
			logger.error( "close connection error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "close connection succeed" );
	}
	
//================================================================================================================
	//关闭预处理数据访问对象
	public void clossPreparedStatement( PreparedStatement preparedStatement ) throws SQLException{
		
		try{
			preparedStatement.close();
		} catch( SQLException e ){
			logger.error( "close PreparedStatement error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		}
		
		logger.info( "close PreparedStatement succeed" );
	}
	
	public void clossPreparedStatement() throws SQLException{
		
		if( this.preparedStatement != null ){
			try{
				this.preparedStatement.close();
			} catch( SQLException e ){
				logger.error( "close PreparedStatement error : SQL Error ["+e.getErrorCode()+"]" );
				e.printStackTrace();
				throw e;
			}
			
			logger.info( "close PreparedStatement succeed" );
		}
		
		this.preparedStatement = null;
	}
	
//===============================================================================================================
	//关闭SqlHelper
	public void closeSqlHelper(){
		
		try{
			clossStatement();
			clossPreparedStatement();
			closeConnection();
//			SimpleConnetionPool.printDebugMsg();
		} catch( SQLException e ){
			logger.error( "close SqlHelper error : SQL Error ["+e.getErrorCode()+"]" );
			e.printStackTrace();
		}
		
		logger.info( "closs SqlHelper succed" );
	}
	
//==========================================================================================

	/************ 好像没有意义2004-05-28 >>>>>>>>>>>>>>>>>>>>>>>>
	//查询数据库
	public List executeQuery( String sqlString, Class[] clazz )throws SQLException, NoodleSQLHelperException, Exception {
		
		LinkedList resultList = new LinkedList();
		ResultSet resultSet;
		
		try{
			resultSet = statement.executeQuery( sqlString );
		} catch( SQLException e ){
			logger.error( "executeQuery SQL Error:["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		} catch( Exception e ){
			logger.error( "executeQuery Error" );
			e.printStackTrace();
			throw e;
		}
	
		int columnId = 0;
		
		ResultSetMetaData resMetaData = resultSet.getMetaData();
		int columnCount = resMetaData.getColumnCount();
		
		if( clazz.length != columnCount ){
			logger.error( this.getClass().getName()+"executeQuery( String sqlString , Class[] clazz )"
							+"类字段与返回数据字段数量不匹配" );
			throw new NoodleSQLHelperException("类字段与返回数据字段数量不匹配");
		}
		
		String javaType = "";
		String jdbcType = "";
		
		for( int i=0; i<columnCount; i++ ){
			
			javaType = clazz[i].getName().trim();
			jdbcType = resMetaData.getColumnClassName(i+1);
			
			if( !javaType.trim().equals( jdbcType.trim() ) ){
				
				//注意有byte[]的未处理
				logger.error( this.getClass().getName()+"executeQuery( String sqlString , Class[] clazz )"
								+"类字段类型与数据库返回类型不匹配，需要：["+jdbcType
								+"]发现为：["+javaType+"]" );
				throw new NoodleSQLHelperException( "类字段类型与数据库返回类型不匹配" );
				
			}
		}
		
		return resultSetWraper( resultSet );
		
	}
	<<<<<<<<<<<<<<<<<<<<< end **/
	
//==========================================================================================
	//查询数据库
	public List executeQuery( String sqlString , Class clazz )throws SQLException, NoodleSQLHelperException, Exception {
		
		logger.info( "SQL String is:["+sqlString+"]" );
		logger.info( "Class name is:["+clazz.getName()+"]" );
		
		ResultSet resultSet;
		try{
			resultSet = statement.executeQuery( sqlString );
		} catch( SQLException e ){
			logger.error( "executeQuery SQL Error:["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		} catch( Exception e ){
			logger.error( "executeQuery Error" );
			e.printStackTrace();
			throw e;
		}
		
		return resultSetWraper( resultSet, clazz );
	}
	
//==========================================================================================
	//查询数据库
	public List executeQuery( String sqlString , Class clazz, int bgnLine, int retLine )throws SQLException, NoodleSQLHelperException, Exception {
		
		logger.info( "SQL String is:["+sqlString+"]" );
		logger.info( "Class name is:["+clazz.getName()+"]" );
		
		ResultSet resultSet;
		try{
			resultSet = statement.executeQuery( sqlString );
		} catch( SQLException e ){
			logger.error( "executeQuery SQL Error:["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		} catch( Exception e ){
			logger.error( "executeQuery Error" );
			e.printStackTrace();
			throw e;
		}
		
		return resultSetWraper( resultSet, clazz );
	}

//==========================================================================================
	//查询数据库
	public List executeQuery ( String sqlString, int bgnLine, int retLine ) throws SQLException, Exception{
		
		logger.info( "SQL String is:["+sqlString+"]" );
		logger.info( "bgnLine is:["+bgnLine+"]" );
		logger.info( "retLine is:["+retLine+"]" );
		ResultSet rs;
		
		try{
			rs = statement.executeQuery( sqlString );
			
		} catch( SQLException e ){
			logger.error( "executeQuery SQL Error:["+e.getErrorCode()+"]" );
			e.printStackTrace();
			throw e;
		} catch( Exception e ){
			logger.error( "executeQuery Error" );
			e.printStackTrace();
			throw e;
		}
		
		return resultSetWraper( rs, bgnLine, retLine );
	}
	
//==========================================================================================
	//查询数据库
	public List executeQueryProperties ( String sqlString, int bgnLine, int retLine ) throws SQLException, Exception{
		
		logger.info( "SQL String is:["+sqlString+"]" );
		logger.info( "bgnLine is:["+bgnLine+"]" );
		logger.info( "retLine is:["+retLine+"]" );
		ResultSet rs;
		
		try{
			Statement fetchStatement = connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
			rs = fetchStatement.executeQuery( sqlString );
		} catch( SQLException e ){