databaseoperation.java

网上报名系统,是一个功能很完善的系统,推存给大家,很好用的

package myjdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.*;
import java.sql.SQLException;
import java.util.Properties;

import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;
import myjdbc.util.WrapperUtil;

public class DatabaseOperation {
  private static  DatabaseOperation dbo = null;
  private static final int FETCH_ALL = Integer.MAX_VALUE;
  private static final int NO_OFFSET = -1;
  private static final int DEFAULT_PAGE_SIZE = 20;
  private Connection conn = null;
  private PreparedStatement pst = null;
  private ResultSet rs = null;
  private DataSource ds = null;
  private static String staticTableConfigPath = "src/conf";//
  private static String staticDataSourceName = "DataSource";
 //私有构在函数
  private DatabaseOperation() {
    try {
      Context ctx = getWeblogicServerInitialContext();
      ds = (DataSource)ctx.lookup(staticDataSourceName);
    }
    catch (NamingException ex) {
      ex.printStackTrace();
    }
    catch(Exception e){
      e.printStackTrace();
    }
  }
 //获得weblogic的上下文
  private final Context getWeblogicServerInitialContext() throws Exception {
         String url = "t3://127.0.0.1:7001";
         String user = null;
         String password = null;
         Properties properties;
         try {
             properties = new Properties();
           //  properties.put("tableConfigPath","F:/jbuilder_project/writebook/relation_manage/src/conf");
             properties.put(Context.INITIAL_CONTEXT_FACTORY,
                            "weblogic.jndi.WLInitialContextFactory");
             properties.put(Context.PROVIDER_URL, url);
             if (user != null) {
                 properties.put(Context.SECURITY_PRINCIPAL, user);
                 properties.put(Context.SECURITY_CREDENTIALS,
                                password == null ? "" : password);

             }
             return new javax.naming.InitialContext(properties);
         } catch (Exception e) {
             e.printStackTrace();
             throw e;
         }
   }
  //获得DatabaseOperation的对象
  public static DatabaseOperation getInstance(String tableConfigPath,String dataSource)
  {
    staticTableConfigPath = tableConfigPath;
    staticDataSourceName = dataSource;
    if (dbo == null)
      dbo = new DatabaseOperation();
    return dbo;
  }
  public static DatabaseOperation getInstance()
  {
    if (dbo == null)
      dbo = new DatabaseOperation();
    return dbo;
  }
  //初始化Connection
  private void openConnection()
  {
    if (conn == null)
    {
      try {
        conn = ds.getConnection();
      }
      catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
  //关闭connection
  private void colseConnection()
  {
    try {
      if (rs != null) {
        rs.close();
      }
      if (pst != null) {
        pst.close();
      }
      if (conn != null) {
        conn.close();
        conn = null;
      }
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }
  //真正的执行SQL语句的函数
  public List executeSQL(String sql ,Object[] paras,int maxRow,int offset)
  {
    openConnection();
    try {
      sql = sql.toLowerCase();
      pst = conn.prepareStatement(sql);
      pst.setMaxRows(maxRow);
      if (paras != null && paras.length > 0)
      {
        for (int i = 0,len = paras.length; i < len; i++) {
          pst.setObject(i+1,paras[i]);
        }
      }
      if (sql.trim().startsWith("s"))
     {
       rs = pst.executeQuery();
       if (offset >0 )
        rs.absolute(offset);
       return  WrapperUtil.getInstance().wrapperToObjectCol(rs, sql,staticTableConfigPath);
     }
     else
     {
       int i = pst.executeUpdate();
//       if (i<=0)
//         throw new Exception("数据库操作执行失败！");
       return null;
     }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
      return null;
    }
    catch(Exception e) {
      e.printStackTrace();
      return null;
    }
    finally{
      this.colseConnection();
    }
  }
  public List executeSQL(String sql ,Object[] paras,int maxRow)
 {
   return executeSQL(sql,paras,maxRow,NO_OFFSET);
 }

  public List executeSQL(String sql ,Object[] paras)
  {
    return executeSQL(sql,paras,FETCH_ALL,NO_OFFSET);
  }
  public List executeSQL(String sql) {
    return  executeSQL(sql,null,FETCH_ALL,NO_OFFSET);
  }
  //pageNo从0开始
  public List executeSQLSeperatePage(String sql ,Object[] paras,int pageSize,int pageNo)
  {
      int offset = -1;
      if (pageNo != 1)
        offset = pageSize * (pageNo-1);
        //rs.absolute(pageSize * pageNo);
      System.out.println("offset = " + offset);
      return  executeSQL(sql,paras,pageSize*(pageNo), offset);
  }
  //获得总页数
  public int getTotalPage(String sql,Object[] paras,int pageSize)
  {
    try {
      openConnection();
      PreparedStatement _pst = conn.prepareStatement(sql);

     if (paras != null && paras.length > 0)
     {
       for (int i = 0,len = paras.length; i < len; i++) {
         _pst.setObject(i+1,paras[i]);
       }
     }
      ResultSet _rs = _pst.executeQuery();
      _rs.last();
      int rows = _rs.getRow();
      return (rows%pageSize == 0) ? rows/pageSize : rows/pageSize+1;
    }catch(SQLException e) {
      e.printStackTrace();
      return -1;
    }finally
    {colseConnection();}

  }
  public int getTotalPage(String sql)
  {
    return getTotalPage(sql,null,DEFAULT_PAGE_SIZE);
  }
  public int getTotalPage(String sql ,int pageSize)
  {
    return getTotalPage(sql,null,pageSize);
  }
  public int getTotalPage(String sql ,Object[] paras)
 {
   return getTotalPage(sql,paras,DEFAULT_PAGE_SIZE);
 }

  public static void main(String args[]) throws Exception{
    List result = new ArrayList();
    String sql = "select * from Wb_RelationDetail as a";
//    sql = sql.toLowerCase();
    DatabaseOperation database = DatabaseOperation.getInstance();
 //   database.openConnection();
    result =  database.executeSQL(sql);
//    String[] columns = parserSQL(sql,"select","from");
//    //sql语句如果是select * from table,这样的模式，则列名数组为空
//    if (columns == null)
//    {
//      String[] tables = getTableName(sql);
//      if (tables.length > 1)
//        throw new Exception("暂时只支持单表的 select * from table 的操作！");
//
//
//    }
//    else {
//      int len = columns.length;
//      int i = 0;
//      try {
//        while (rs.next()) {
//          Object[] temp = new Object[len];
//          while (i < len) {
//            temp[i] = rs.getObject(columns[i]);
//            i++;
//          }
//          result.add(temp);
//          i = 0;
//        }
//      }
//      catch (SQLException ex) {
//        ex.printStackTrace();
//      }
//      prt(result);
//      database.colseConnection();
//    }
  }
//  private static void prt(List list)
//  {
//
//    for (Iterator iter = list.iterator(); iter.hasNext(); ) {
//       WbRelationDetail item = (WbRelationDetail) iter.next();
//     System.out.println("relation sn = " + item.getRelationSn() +" , and relation name = "+ item.getName());
//
//    }
//  }
}