dboper.java

简单的j2ee轻量级框架

package com.prinice.jfoot.util.dbo;
/**
 * @author:Leo
 * @version:1.0
 * @description:数据库连接工具
 * @date:2008-2-17
 */

import java.sql.*;
import javax.sql.*;
import javax.naming.*;

public class DBOper {
  private Connection cn = null;
  private Statement stmt = null;
  private ResultSet rs = null;
  
  public DBOper(){
	  try{
		  cn = this.getDBConnection();
		  stmt = cn.createStatement();
	  }catch(Exception e){
		  e.printStackTrace();
	  }
  }
  
  /*得到数据库连接*/
  private Connection getDBConnection(){
	DataSource ds = null;
	try{
	  /*tomcat,sql数据库连接池方式*/	
      Context initCtx = new InitialContext();
      Context envCtx = (Context) initCtx.lookup("java:comp/env");
      //获取连接池对象并进行类型转换
      ds = (DataSource)envCtx.lookup("jdbc/MysqlDBConnectionPool");//mysql数据库连接池
      //ds = (DataSource)envCtx.lookup("jdbc/OracleDBConnectionPool");//oracle数据库连接池
	  if(ds!=null){
        System.out.println("DataSource is OK!");
        Connection cn=ds.getConnection();
        if(cn!=null){
          System.out.println("Connection is Ok!");
          return cn;
        }else
          System.out.println("Connection is not Ok!");
          return null;             
	  }else{
		System.out.println("DataSource is not OK!"); 
		return null;
	  }
	}catch(Exception e){
	  System.out.println("get DBConnection failed!");
      e.printStackTrace();
      return null;
	}
  }
  /*
   * 关闭数据库连接
   */
  public void close(){
	  if(cn != null){
		  try{
			  System.out.println("释放连接...");
			  stmt.close();
			  cn.close();
			  stmt = null;
			  cn = null;
		  }catch(Exception e){
			  e.printStackTrace();
		  }
	  }
  }
  /*执行更新语句，返回影响的行数*/
  public int executeUpdate(String sql){
	int rowNumUpdated = 0;
	try{
		System.out.println("sql is : " + sql);
	    rowNumUpdated = stmt.executeUpdate(sql);    
        if(rowNumUpdated > 0){
      	  System.out.println("update success!");
  	      System.out.println(rowNumUpdated + " updated!");
        }else
          System.out.println("executeUpdate failed because rowNumUpdated <= 0!");
        return rowNumUpdated; 
	}catch(Exception e){
	  System.out.println("executeUpdate failed!");
	  e.printStackTrace();
	  return -1;	  
	}
  }
  /*
   * 执行查询语句，返回查出的结果集
   * 调用者需要在调用该方法后手动关闭连接
   */
  public ResultSet executeQuery(String sql){  
	try{
		System.out.println("sql is : " + sql);
		rs = stmt.executeQuery(sql);
        if(rs != null)
      	  System.out.println("query success!");
        else
          System.out.println("query failed because rs is null!");
        return rs;
	}catch(Exception e){
	  System.out.println("executeQuery failed!");
	  e.printStackTrace();
	  return null;	  
	}
  }

  /* 执行分页查询语句，返回指定页的结果集，返回类型为ArrayList
   * 目前该方法实现的是oracle数据库的sql，oracle的rownum是从1开始计数的
   * 这点需要特别注意！
   * @param sql:提交的sql语句
   * @param curruentPage:当前页
   * @param pageSize:每页显示条数
   */
  public ResultSetQueryByPage executeQueryByPage(SqlStatement sqlInput,int currentPage,int pageSize){  
	  ResultSetQueryByPage rsQueryByPage = new ResultSetQueryByPage();
	StringBuffer sql = new StringBuffer();
	int pageStartIndex = 0;
	int pageEndIndex = 0;
	/*计算本页首索引和末索引*/
	//pageStartIndex = pageSize * (currentPage - 1) + 1;//oracle计算方式
	pageStartIndex = pageSize * (currentPage - 1);//mysql计算方式
	pageEndIndex = pageSize * currentPage;
	try{
		//计算总记录数
	    Statement stmt1 = cn.createStatement();
	    ResultSet rs1 = null;
	    rs1 = stmt1.executeQuery(sqlInput.getSelectCountSql());
	    if(rs1.next()){ 
	    	rsQueryByPage.setTotalSize(rs1.getInt(1));
	    	System.out.println("rsQueryByPage.getTotalSize() is " + rsQueryByPage.getTotalSize());
        } else { 
        	rsQueryByPage.setTotalSize(1); 
        }
	    rs1.close();
	    stmt1.close();
	    rs1 = null;
	    stmt1 = null;
	    /*
	     * 分页查询sql语句的oracle实现方式
	     * 需要注意的是oralce中，是先order，再rownum，所以采用这种两重select
	     * 但是更为重要的是oracle中没有rownum>这个语法，例如select info_id from t_info where rownum > 1是查不出数据的 
	     * 所以最后要采用三重select，这里给出一个示范
	     * 
	     */
	    /*
	    sql.append("select * from ")
	       .append("(select t_tmp.*,rownum rownum_tmp from ")
	       .append("(").append(sqlInput.getSelectSql()).append(") t_tmp) ")
	       .append("where rownum_tmp between ").append(pageStartIndex).append(" and ").append(pageEndIndex);
	    */
	    /*
	     * sql语句是mysql的实现方式
	     */
	    sql.append("(").append(sqlInput.getSelectSql()).append(")")
	       .append(" limit ").append(pageStartIndex).append(",").append(pageSize);
	    
	    System.out.println("sql is : " + sql.toString());
	    rs = stmt.executeQuery(sql.toString());
        if(rs != null){
      	  System.out.println("querybypage success!");
      	  rsQueryByPage.setRsQueryByPage(rs);
        }else
          System.out.println("querybypage failed because rs is null!");
        return rsQueryByPage; 
	}catch(Exception e){
	  System.out.println("executeQueryByPage failed!");
	  e.printStackTrace();
	  return null;	  
	}
  }  
  /*执行删除语句，返回执行成功或失败*/
  public Boolean executeDelete(String sql){
	try{
		System.out.println("sql is : " + sql);
	    stmt.execute(sql);
	    System.out.println("delete success!");
        return true; 
	}catch(Exception e){
	  System.out.println("executeDelete failed!");
	  e.printStackTrace();
	  return false;	  
	}
  }
  /*执行新增语句，返回执行成功或失败*/
  /*
   * 注意javaapi中的关于Statement.execute()的介绍
   * Returns:
   * true if the first result is a ResultSet object; 
   * false if it is an update count or there are no results 
   */
  public Boolean executeInsert(String sql){
	try{
		System.out.println("sql is : " + sql);
	    stmt.execute(sql);
	    System.out.println("executeInsert success!");
	    return true;
	}catch(Exception e){
	  System.out.println("executeInsert failed!");
	  e.printStackTrace();
	  return false;	  
	}
  }
}