sqlfacade.java

JSP聊天系统

package org.ehotsoft.yekki.sql;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.io.OutputStream;

import javax.servlet.http.HttpServletRequest;

public class SQLFacade {
	
	protected Connection cnn;
	
	//here show throws Exception
	public SQLFacade() {
		
		cnn = ConnectionManager.getConnection();
	}

	public java.sql.ResultSet executeQuery( String sql )
		throws SQLException {
		
		ResultSet resultSet = null;

		Statement statement = cnn.createStatement( java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE );
		resultSet = statement.executeQuery( sql );
		
		return resultSet;
	}

	public int executeUpdate( String sql )
		throws SQLException {
		
		int affectCount = 0;

		Statement statement = cnn.createStatement();
		affectCount = statement.executeUpdate( sql );
		statement.close();

		return affectCount;
	}
	
	//here show throws Exception
	public Connection getConnection() {
		
		return cnn;
	}

	public Pager executePage( HttpServletRequest request, String sql, int pageSize )
		throws SQLException {
		
		return executePage( request, sql, pageSize, "" );
	}
	
	public Pager executePage( HttpServletRequest request, String sql, int pageSize, String args )
		throws SQLException {
	
    	
		int absolutePage;
		int recordCount = 0;
		ResultSet resultSet = null;
		Pager pager = null;

		try {
			
			absolutePage = Integer.parseInt( request.getParameter( "page_no" ) );
		}
		catch ( NumberFormatException e ) {
			
			absolutePage = 1;
		}
		
		resultSet = executeQuery( sql );
		resultSet.last();
		recordCount = resultSet.getRow();
		
		if ( ( absolutePage - 1 ) == 0 )
			resultSet.beforeFirst();
		else
			resultSet.absolute( ( absolutePage -1 ) * pageSize );
		
		pager = new Pager( absolutePage, recordCount, pageSize, args );
		pager.setResultSet( resultSet );

    	return pager;		
	}
	
	public int updateClob( String tableName, String idColumnName, String id, String clobColumnName, String content )
		throws SQLException {
		
		int retCount = 0;
		StringBuffer sql = new StringBuffer();
		cnn.setAutoCommit( false );
		Statement stm = stm = cnn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
		
		try {
			
			sql.append( "update " )
				.append( tableName )
				.append( " set " )
				.append( clobColumnName )
				.append( "=EMPTY_CLOB() where " )
				.append( idColumnName )
				.append( "='" )
				.append( id )
				.append( "'" );

			retCount = stm.executeUpdate( sql.toString() );

			sql = new StringBuffer();
			
			sql.append( "select " )
				.append( clobColumnName )
				.append( " from " )
				.append( tableName )
				.append( " where " )
				.append( idColumnName )
				.append( "='" )
				.append( id )
				.append( "' for update" );

			ResultSet rst = stm.executeQuery( sql.toString() );
			
			if ( rst.next() ) {
				
				oracle.sql.CLOB clob = ( oracle.sql.CLOB )rst.getClob( 1 );
				clob.putString( 1, content );
			}

			cnn.commit();
		}
		catch ( SQLException e ) {
			
			cnn.rollback();
		}
		finally {
			
			cnn.setAutoCommit( true );
		}

		return retCount;
	}

	public void close()
		throws SQLException {
		
		if ( !cnn.isClosed() ) cnn.close();
	}
}