databasesession.java

封装JDBC常用操作

package database;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Driver;
import java.util.Date;
import java.sql.DriverManager;
import java.io.InputStream;
import java.util.Vector;
import java.sql.PreparedStatement;
import org.jdom.input.SAXBuilder;
import java.net.URL;
import org.jdom.Document;
import org.jdom.Element;
import java.util.Iterator;
import java.util.List;
import java.lang.Integer;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
public class DatabaseSession{
   public static final int SESSION_SUCCESS=1;
   public static final int SESSION_FAILURE=2;
   public static final int COMMON_QUERY=1;
   public static final int PREPARED_QUERY=2;
   public static final int CALLABLE_QUERY=3;
   public static final int INT_TYPE=Types.INTEGER;
   public static final int FLOAT_TYPE=Types.FLOAT;
   public static final int STRING_TYPE=Types.VARCHAR;
   public static final int CURSOR_TYPE=OracleTypes.CURSOR;
   public static final int BLOB_TYPE=Types.BLOB;
   public static final int CLOB_TYPE=Types.CLOB;
   static Vector pool=new Vector();
   static int availCount=0;
   static HashMap dataSourceMap=new HashMap();
   Connection conn=null;
   Statement stmt=null;
   PreparedStatement psmt=null;
   OracleCallableStatement csmt=null;
   ResultSet rs=null;
   boolean isAuto=true;
   int effectRow=-1;
   String sqlStr="";
   int queryType=0;
   DatabaseSession session=null;
   static String user="";
   static String password="";
   static String url="";
   static{
      URL u= DatabaseSession.class.getResource("/xml/ConfigDataSource.xml");
      try{
          SAXBuilder builder = new SAXBuilder();
          Document doc = builder.build(u);
          Element root = doc.getRootElement();
          List children = root.getChildren();
          Iterator it = children.iterator();
          while (it.hasNext()) {
              Element e = (Element) it.next();
              String name = e.getChildText("NAME");
              dataSourceMap.put(name, e);
          }
      }catch(Exception e){}
   }
   public DatabaseSession(){
     if(availCount>=0)
         this.conn=getConnectionFromPool();
     else
         this.conn=waitForPool();
   }
   public static DatabaseSession getSession(String dbName){
      Element e=(Element)dataSourceMap.get(dbName);
      url=e.getChildText("URL");
      user=e.getChildText("USER");
      password=e.getChildText("PASSWORD");
      return new DatabaseSession();
   }
   public Connection waitForPool(){
      int timeout=5000;
      float beginTime=new Date().getTime();
      while(availCount<=0){
         try{
           wait(1000);
         }catch(InterruptedException e){
            System.out.println(e);
            return getConnectionFromPool();
         }
         if(new Date().getTime()-beginTime>=5000){
             try{
                 return DriverManager.getConnection(url, user, password);
             }catch(SQLException e){}
         }
      }
      return getConnectionFromPool();
   }
   public synchronized Connection getConnectionFromPool(){
       if(availCount>=0){
           Connection conn = (Connection)pool.get(availCount);
           pool.remove(availCount--);
       }else{
           conn=waitForPool();
       }
      return conn;
   }
   public void close(){
       if(rs!=null)
           try{
               rs.close();
           }catch(SQLException e){}
       if(stmt!=null){
          try{
              stmt.close();
          }catch(SQLException e){}
       }
       if(psmt!=null){
          try{
              psmt.close();
          }catch(SQLException e){}
       }
       pool.add(this.conn);
       conn=null;
       availCount++;
   }
   public void setCommit(boolean isAuto){
       this.isAuto=isAuto;
   }
   public void setSQL(String sqlStr,int queryType){
       try{
           this.conn.setAutoCommit(this.isAuto);
           this.sqlStr = sqlStr;
           this.queryType = queryType;
           this.effectRow=-1;
           this.rs=null;
           this.stmt = null;
           this.psmt = null;
           this.csmt = null;
           switch (queryType) {
           case 1:
               stmt = conn.createStatement();
               break;
           case 2:
               psmt = (PreparedStatement) conn.prepareStatement(sqlStr);
               break;
           case 3:
               csmt = (OracleCallableStatement) conn.prepareCall(sqlStr);
               break;
           default:
               this.queryType = 0;
               this.sqlStr = "";
               break;
           }
       }catch(SQLException e){}
   }
   public void registerOutParameter(int inx,int paraType){
       if(queryType==CALLABLE_QUERY)
           try{
               csmt.registerOutParameter(inx, paraType);
           }catch(SQLException e){}
   }
   public String executeQuery(){
       String returnStr="";
       switch(this.queryType){
         case 1:
             try{
                 rs = stmt.executeQuery(this.sqlStr);
                 returnStr="1";
             }catch(SQLException e){}
             break;
         case 2:
             try{
                 rs=psmt.executeQuery();
                 returnStr="1";
             }catch(SQLException e){}
             break;
         case 3:
             try{
                 csmt.executeQuery();
                 returnStr="1";
             }catch(SQLException e){}
             break;
      }
      return returnStr;
   }
   public String executeUpdate(){
       String returnStr="";
      switch(this.queryType){
         case 1:
             try{
                 effectRow=stmt.executeUpdate(this.sqlStr);
                 returnStr="1";
             }catch(SQLException e){}
         case 2:
             try{
                effectRow=psmt.executeUpdate();
                returnStr="1";
             }catch(SQLException e){}
         case 3:
             try{
                csmt.executeUpdate();
                returnStr="1";
             }catch(SQLException e){}
      }
      return returnStr;
   }
   public String setParameter(int inx,Object var){
      String returnStr="";
      if(var!=null){
         switch(this.queryType){
             case 2:
                 try{
                     psmt.setObject(inx, var);
                     returnStr = "1";
                 }catch(SQLException e){}
                 break;
             case 3:
                 try{
                     csmt.setObject(inx,var);
                     returnStr="1";
                 }catch(SQLException e){}
                 break;
         }
      }else{
         int paraType=-1;
         if(var instanceof String)
             paraType=STRING_TYPE;
         if(var instanceof Integer)
             paraType=INT_TYPE;
         if(var instanceof Float)
             paraType=FLOAT_TYPE;
         if(this.queryType==2){
             try {
                 psmt.setNull(inx, paraType);
                 returnStr = "1";
             } catch (SQLException e) {}
         }
      }
      return returnStr;
   }
   public Integer getParameter(){
      return this.effectRow==-1?null:Integer.valueOf(this.effectRow);
   }
   public Object getParameter(int inx){
      Object paraObject=null;
      if(this.queryType==CALLABLE_QUERY){
          try{
              paraObject = csmt.getObject(inx);
          }catch(Exception e){}
      }
      return paraObject;
   }
   public Object[][] getResuleArray(){
       Object[][] resultArray=null;
       try{
           if (this.queryType == 1 || this.queryType == 3) {
               int rowInx = 0;
               ArrayList tempTable=new ArrayList();
               while (!rs.next()) {
                   ArrayList tempRow = new ArrayList();
                   try {
                       for (int i = 1; ; i++)
                           tempRow.add(rs.getObject(i));
                   } catch (Exception e) {}
                   tempTable.add(tempRow);
               }
               int rowCount=tempTable.size();
               resultArray=new Object[rowCount][];
               for(int i=0;i<rowCount;i++)
                  resultArray[i]=(Object[])((ArrayList)tempTable.get(i)).toArray(new Object[0]);
           }
       }catch(Exception e){}
      return resultArray;
   }
   public Object[][] getResultArray(int inx){
      Object[][] resultArray=null;
      if(this.queryType==3){
          try {
              this.rs = (ResultSet)csmt.getCursor(inx);
              resultArray=this.getResuleArray();
          }catch(SQLException e){}
      }
      return resultArray;
   }

   /*public String setParameter(int inx,int var){
      String returnStr="";
      switch(this.queryType){
          case 2:
              try{
                   psmt.setInt(inx,var);
                   returnStr="1";
              }catch(SQLException e){}
              break;
          case 3:
              try{
                  csmt.setInt(inx,var);
              }catch(SQLException e){}
              break;
      }
      return returnStr;
   }
   public String setParameter(int inx,float var){
      String returnStr="";
      switch(this.queryType){
         case 2:
             try{
                 psmt.setFloat(inx,var);
                 returnStr="1";
             }catch(SQLException e){}
         case 3:
             try{
                 csmt.setFloat(inx,var);
                 returnStr="1";
             }catch(SQLException e){}
      }
      return returnStr;
   }
   public String setParameter(int inx,String var){
      String returnStr="";
      switch(this.queryType){
         case 2:
             try{
                psmt.setString(inx,var);
                returnStr="1";
             }catch(SQLException e){}
         case 3:
             try{
                 csmt.setString(inx,var);
                 returnStr="1";
             }catch(SQLException e){}
      }
      return returnStr;
   }
   public String setNull(int inx,int paraType){
      String returnStr="";
      switch(this.queryType){
         case 2:
             try{
                psmt;
             }
      }
   }
   public String setParameter(int inx,Object valObject){
       String returnStr="";
       switch(this.queryType){
           case 2:
               try{
                   if(valObject instanceof String)
                      psmt.setString(inx,((String)valObject).toString());
                  if(valObject instanceof Integer)
                      psmt.setInt(inx,((Integer)valObject).intValue());
                   if(valObject instanceof Float)
                      psmt.setFloat(inx, ((Float)valObject).floatValue());
                   returnStr="1";
               }catch(Exception e){}
               break;
           case 3:
               try{
                   if(valObject instanceof String)
                      csmt.setString(inx,((String)valObject).toString());
                   if(valObject instanceof Integer)
                       csmt.setInt(inx,((Integer)valObject).intValue());
                   if(valObject instanceof Float)
                       csmt.setFloat(inx,((Float)valObject).floatValue());
                   returnStr="1";
               }catch(Exception e){}
               break;
      }
      return returnStr;
   }*/


}