dbhandler.java

java xml开发指南（初学者推荐）Java Xml 编程指南书籍源码

package MyNa.xml;
import MyNa.utils.*;
import java.util.Hashtable;
import java.sql.Date;
import java.text.DateFormat;
import java.util.Enumeration;

import java.sql.*;   // communicate with database

public class DBHandler  { 
  DBConnectionManager dbCM=null; //one Cache of connection pools
  DBConnectionPool dbCP=null;    // connection pool for this dbUrl/usr/pwd
  Connection theConnection=null; 
  static String defaultDateFormat="yyyy-MM-dd"; // for month,day,year
  String dateFormat=null; 
  java.text.SimpleDateFormat simpleDateFormat; // reads datestrings

  Hashtable theQueries=null; // contains prepackaged queries
  String currentOp=null;
  String driverName=null; // ="sun.jdbc.odbc.JdbcOdbcDriver";
  String dbUrl=null;  // ="jdbc:odbc:PHONEBOOK";
  String theUser=null; // ="usr"; 
  String thePwd=null; // ="pwd";
  Logger lg;
  public DBHandler(String dbDriver,String dbName,
                   String dbUser,String dbPwd,
                   String [] qNames,String [] qVals)
                     throws SQLException{
    lg=new Logger();
    initDBHandler(dbDriver,dbName,dbUser,dbPwd,qNames,qVals,null);
  }
  public DBHandler(String dbDriver,String dbName,
                   String dbUser,String dbPwd,
                   String [] qNames,String [] qVals,
                   String [] qTypes)
                     throws SQLException{
   lg=new Logger();
    initDBHandler(dbDriver,dbName,dbUser,dbPwd,qNames,qVals,qTypes);
  }
  public DBHandler(Env env)throws SQLException,Exception{
    lg=new Logger(); //XXXX
    String dbDriver=env.getStr("dbDriver");
    String dbName=env.getStr("dbName");
    String dbUser=env.getStr("dbUser");
    String dbPwd=env.getStr("dbPwd");
    String dbQueries=env.getStr("dbQueries"); // LOOKUP,DELETE,ADDNUM..
    dateFormat=env.getStr("dateFormat");
    String [] qNames=Misc.stringSplit(dbQueries,',');
    env.put("dbQueries",qNames); // it is a StrSeq.
    String [] qVals=new String[qNames.length];
    String [] qTypes=new String[qNames.length];
    for(int i=0;i<qVals.length;i++){
      qVals[i]=Misc.substLineByTags(env.getStr(qNames[i]),env);
      qTypes[i]=env.getStr(qNames[i]+"_types");
      }
    initDBHandler(dbDriver,dbName,dbUser,dbPwd,qNames,qVals,qTypes);
    }

protected Connection checkConnection()throws SQLException{
  if(null!=theConnection)return theConnection;
  try{
  if(null==dbCM)dbCM=(DBConnectionManager)DBConnectionManager.getInstance(); 
  dbCM.addDriver(driverName); 
  if(null==dbCP)dbCP=dbCM.getConnectionPool(dbUrl,theUser,thePwd);
  theConnection=dbCP.getConnection();
  return theConnection;
  }catch(Exception ex){
   lg.logIt("DBHandler.checkConnection for "+dbUrl+": "+ex);
   throw new SQLException(dbUrl+", driver "+driverName+
                          " failed to connect "+ex);
  }
}
protected void freeConnection(){ // called on close or gotosleep.
  if(null==theConnection)return;
  dbCP.freeConnection(theConnection);
  theConnection=null;
}
protected Connection getConnection()throws SQLException{
// here we allow other classes access to a connection for
// which DBHandler remains the only client. Use with caution.
  return checkConnection();
}
  public void initDBHandler(String dbDriver,String dbName,
                   String dbUser,String dbPwd,
                   String [] qNames,String [] qVals,String[] qTypes)
                     throws SQLException{
    driverName=dbDriver; dbUrl=dbName;
    theUser=dbUser; thePwd=dbPwd;
    theQueries=new Hashtable();
    if(null==dateFormat)dateFormat=defaultDateFormat;
   try{
   lg.logIt("driverName="+driverName+
           "\ndbUrl="+dbUrl+
           "\ntheUser="+theUser+
           "\nthePwd="+thePwd);
     checkConnection();
//   Class.forName(driverName);
//   lg.logIt("got the class forName "+driverName);
//   theConnection=DriverManager.getConnection(dbUrl,theUser,thePwd);
//   lg.logIt("got the connection to "+dbUrl);
/* // Connection metadata may be useful in debugging.
   DatabaseMetaData dmd=theConnection.getMetaData();
   lg.logIt("max connections="+dmd.getMaxConnections());
   lg.logIt("max statements="+dmd.getMaxStatements());
   // we can also report on dmd.getTables(null,null,null,null)
   // here as a report on what's in the database.
*/
   if(qTypes==null)qTypes=new String[qVals.length];
   for(int i=0;i<qNames.length;i++){
     qVals[i]=qVals[i].trim();
//     lg.logIt("query "+i+"; maps "+qNames[i]+" to "+qVals[i]);
     Query Q=new Query(qNames[i],qVals[i],qTypes[i]);
     theQueries.put(qNames[i],Q);  
     }    
  simpleDateFormat=new java.text.SimpleDateFormat(dateFormat); //default locale
  
    }catch(Exception ex){
      ex.printStackTrace(); 
      lg.logIt("DBHandler failed ",ex);
      }
  }
  public DBHandler(String qSpecStr)throws SQLException{
   lg=new Logger();
   theQueries=new Hashtable();
   String[]S=Misc.stringSplit(qSpecStr);
   String[][]qSpec=new String[S.length][];
   for(int i=0;i<S.length;i++)qSpec[i]=Misc.stringSplit(S[i]);
   driverName=qSpec[0][0]; dbUrl=qSpec[0][1];
   theUser=qSpec[0][2]; thePwd=qSpec[0][3];             
   lg.logIt("driverName="+driverName+
           "\ndbUrl="+dbUrl+
           "\ntheUser="+theUser+
           "\nthePwd="+thePwd);
   try{
     checkConnection();
//   Class.forName(driverName);
//   theConnection=DriverManager.getConnection(dbUrl,theUser,thePwd);
//   lg.logIt("got the connection to "+dbUrl);
   for(int i=1;i<qSpec.length;i++){
     Query Q=new Query(qSpec[i]);
     theQueries.put(qSpec[i][0],Q);  
     }      
    }catch(Exception ex){
      ex.printStackTrace(); 
      lg.logIt("DBHandler failed with",ex);
      return;
      }
    lg.logIt("DBHandler connected to "+dbUrl);
  }
  public Env getQueryResult(Env qInfo)throws SQLException{
    qInfo.put("dbUser",theUser);
    qInfo.put("dbHandler",this);
    currentOp=qInfo.getStr("dbOperation");
    if(null==currentOp)return null;
    Query Q=(Query)theQueries.get(currentOp);
    return Q==null?null:Q.getQueryResult(qInfo);
  }
  public RowSeq getQueryRows(Env qInfo)throws SQLException{
    qInfo.put("dbUser",theUser);
    qInfo.put("dbHandler",this);
    currentOp=qInfo.getStr("dbOperation");
    if(null==currentOp)
      throw new SQLException("no dbOperation");
    Query Q=(Query)theQueries.get(currentOp);
    if(null==Q)
      throw new SQLException("undefined dbOperation: "+currentOp);
    return Q.getQueryRows(qInfo);
  }
  public void gotoSleep()throws SQLException{ 
   Enumeration qq=theQueries.elements();
   while(qq.hasMoreElements()){
     Query Q=(Query)qq.nextElement();
     if(null!=Q)Q.close();
     }
   if(theConnection!=null)dbCP.freeConnection(theConnection);
  }
  public void close()throws SQLException{ 
   gotoSleep();
   dbCM.freeInstance();
  }

  public void addQuery(String qNm,String qStr,String qT)
             throws SQLException{
     theQueries.put(qNm,new Query(qNm,qStr,qT));  
  }
  public void delQuery(String qNm)throws SQLException{
     Query Q=(Query)theQueries.get(qNm);
     if(null==Q)return;
     Q.close();
     theQueries.remove(qNm);  
  }
private class StrSeqList {
  public String [] hd; public int loc; public StrSeqList tl;
  public StrSeqList(String [] h, int l, StrSeqList t){
    hd=h; loc=l; tl=t;}
}



private class Query {
  public String qName; public StrSeqList theStrSeqList;
  public String qString; 
  public String[]qTypes;
  public PreparedStatement pStmnt=null;
  public int argCount; public int colCount;
  public boolean givesResultSet=false;
  ResultSet theResult=null;
  public void close()throws SQLException{
    if(null!=theResult)theResult.close(); 
    theResult=null;
    if(null!=pStmnt)pStmnt.close();
    pStmnt=null;
  }
  public Query(String [] Q)throws SQLException{this(Q[0],Q[1],Q[3]);}
  public Query(String qNm, String qStr,String qT) throws SQLException{
     qName=qNm; qString=upcaseQueryString(qStr); 
     if(null==qT)qT="";
     qTypes=Misc.stringSplit(qT,',');
     givesResultSet=qString.startsWith("SELECT");
     argCount=0;
     for(int i=0;i<qStr.length();i++)
       if(qStr.charAt(i)=='?')argCount++;
     colCount=0;
//     pStmnt=checkConnection().prepareStatement(qString);
//     lg.logIt("statement prepared"+(pStmnt==null?" but is null":""));
  }
  private PreparedStatement checkPstmnt() throws SQLException{
    if(null==pStmnt)pStmnt=checkConnection().prepareStatement(qString);
    return pStmnt;
  }
  public String upcaseQueryString(String qStr){
     // puts SQL operator, e.g. "Select", into uniform upper case.
    if(null==qStr)return null;
    StringBuffer sB=new StringBuffer(qStr);
    char c;
    for(int i=0; i<sB.length() && Character.isLetter(c=sB.charAt(i)); i++)
      sB.setCharAt(i,Character.toUpperCase(c));
    return sB.toString();
  }
  public Env getQueryResult(Env qInfo) throws SQLException{
// this is an example of using an Env to receive whatever
// parameters are needed, then to produce whatever results
// are needed. The input and result Env are the same object;
// a resultset is returned as "ResultTable", a 2-D
// matrix as in Query1, whereas an update number is returned
// as "NumberOfRowsAffected", a string (not an integer).
  checkPstmnt();
  argCount=1; String V;
  int maxArgs=qInfo.getInt("ParameterMax",1000);
  while (argCount<=maxArgs &&
         null!=(V=qInfo.getStr("Parameter"+argCount)))
    {
     setParamStr(argCount++,V);
    }
  qInfo.put("NumberOfParameters",""+(argCount-1));
  qInfo.put("dbQueryString",qString);
  if (givesResultSet){
    theResult=pStmnt.executeQuery();
    qInfo.put("ResultTable", MiscDB.resultRowsToStringMatrix(theResult));
    theResult.close();
    return qInfo;
    }
  else {int N=pStmnt.executeUpdate();
    qInfo.put("NumberOfRowsAffected",""+N);
    return qInfo;
   }
  }
  public void setParamStr(int i,String val)throws SQLException{
   try{
    String t=(i>qTypes.length)?null:qTypes[i-1];
    if(t==null||"text".equalsIgnoreCase(t)
     ||"varchar".equalsIgnoreCase(t)||"longvarchar".equalsIgnoreCase(t))
      pStmnt.setString(i,val);
    else if(t.equalsIgnoreCase("date")){
      java.util.Date d=simpleDateFormat.parse(val);
      java.sql.Date dbdate=new java.sql.Date(d.getTime());
      pStmnt.setDate(i,dbdate);
      }
    else pStmnt.setString(i,val);
   }catch(java.text.ParseException e){
     throw new SQLException("setParamStr failed on ["+val+"] as date:"+e);
   }
  }

  public void setParam(int i, Object ob)throws SQLException{
    if(ob instanceof String)setParamStr(i,(String)ob);
    else if(ob instanceof String[]){
      theStrSeqList=new StrSeqList((String [])ob,i,theStrSeqList);
      }
    else {
     lg.logIt("doQuery.setParam: invalid param "+i);
     setParamStr(i,null);
     }
  }
  public RowSeq getQueryRows(Env qInfo) 
     throws SQLException{
  argCount=1; Object ob=null;
  int maxArgs=qInfo.getInt("ParameterMax",1000);
  checkPstmnt();
  theStrSeqList=null;
  while (argCount<=maxArgs &&
          null!=(ob=qInfo.get("Parameter"+argCount)))
    { // lg.logIt("Parameter"+argCount+"="+qInfo.getStr("Parameter"+argCount));
    setParam(argCount++,ob);
    }
  qInfo.put("NumberOfParameters",""+(argCount-1));
  qInfo.put("dbQueryString",qString);
  if (givesResultSet){
//    lg.logIt("executing query "+qString+" for result set");
    theResult=checkPstmnt().executeQuery();
    try{return new DBRowSequence(theResult,qInfo);
    }catch(Exception ex){
      lg.logIt("getQueryRows "+qString+" failure "+ex);
      return null;
      }
    }
  else {int N=0;
    if(null==theStrSeqList)N=pStmnt.executeUpdate();
    else {int lim=theStrSeqList.hd.length;
      for(int i=0;i<lim;i++){
        for(StrSeqList ssl=theStrSeqList;ssl!=null;ssl=ssl.tl)
          setParamStr(ssl.loc,(String)(ssl.hd[i]));
        N+=pStmnt.executeUpdate();
        } } 
    qInfo.put("NumberOfRowsAffected",""+N);
    try{return new DBRowSequence(null,qInfo);
    }catch(Exception ex){
      lg.logIt("getQueryRows (no result set) "+qString+" failure "+ex);
      return null;
      }
   }
}
}

}