sqlbean.java

数据库访问通用组件,实现通用的数据库操作,并附有用此通用组件的一个dao类.

package dao;

import javax.sql.RowSet;
import java.sql.*;
import javax.sql.DataSource;
import sun.jdbc.rowset.CachedRowSet;
import java.util.ArrayList;

public class SqlBean {
  private boolean bUseDataSource=false;
  private static SqlBean instance = null;
  private javax.naming.Context context = null;
  private DataSource dataSource=null;

  private SqlBean() throws Exception
  {
	if(!bUseDataSource)
		return;
    try
    {
      context = new javax.naming.InitialContext();

      //初始化数据库连接
      try
      {
        dataSource = (javax.sql.DataSource) context.lookup("jdbc/test");
      }
      catch(Exception e) {
          throw new Exception("寻找数据源出现异常(Error looking up dataSource): " + e.toString());
      }
    }
    catch(Exception e)
    {
      throw new Exception("初始化上下文出现异常(Error initializing context):" + e.toString());
    }
  }

  public static SqlBean getInstance()
  {
	try{
		if(instance==null)
		{
			instance = new SqlBean();
		}
	}
	catch(Exception e){
		e.printStackTrace();
	}
    return instance;
  }

  private Connection getConnection()
  {
	Connection conn=null;
	try {
	  if(bUseDataSource)
		return dataSource.getConnection();
	  Class.forName("org.gjt.mm.mysql.Driver");
	  conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
	}
	catch(Exception e) {
		e.printStackTrace();
	}
	return conn;
  }

  void closeConnection(Connection connection, Statement statement)
  {
    try
    {
      if (statement != null)
      {
        statement.close();
      }
    }
    catch(SQLException e) {}
    try
    {
      if (connection != null)
      {
        connection.close();
      }
    }
    catch(SQLException e) {}
  }

  public RowSet getRowSet(String strSQL,String tableName)
  {
      Connection connection = null;
      Statement ps = null;
      CachedRowSet crs = null;
      try
      {
        connection = getConnection();
        ps = connection.createStatement();
        ResultSet rs = ps.executeQuery(strSQL);
        crs = new CachedRowSet();
        crs.populate(rs);
        crs.setTableName(tableName);
        rs.close();
      }
      catch(Exception e){
          System.out.println("提取集合异常(RowSet):"+strSQL + e.getMessage());
    	  e.printStackTrace();
      }
      finally{
          closeConnection(connection, ps);
      }
      return crs;
  }

  public int execute(String strSQL) throws Exception
  {
      Statement statement = null;
      Connection conn = null;
      int flag=-1;
      try
      {
          conn = getConnection();
          statement = conn.createStatement();
          flag=statement.executeUpdate(strSQL);
      }
      catch(SQLException e)
      {
          throw new Exception("Error executing SQL "+strSQL+": " + e.toString());
      }
      finally
      {
          closeConnection(conn, statement);
      }
      return flag;
  }

  public int exeBatch(ArrayList al) throws Exception
  {
    if(al==null || al.size()==0)
      return 0;
    int len=al.size();
    String sql=null;
    Statement statement = null;
    int i = 0;
    Connection conn = null;
    try
    {
      conn = getConnection();
      statement = conn.createStatement();
      for (i = 0; i < len; i++)
      {
        sql=(String)al.get(i);
        statement.executeUpdate(sql);
      }
    }
    catch(Exception e)
    {
      throw new Exception("Error executing SQL:"+sql+": " + e.toString());
    }
    finally
    {
      closeConnection(conn, statement);
    }
    return i;
  }
}