为JDBC增加命名参数的支持

href="为JDBC增加命名参数的支持.files/filelist.xml" rel="File-List" />

为JDBC增加命名参数的支持 广州城市信息研究所 梁生红   本人因一个项目要使用J2EE架构，所以最近几天研究了一下JDBC,发现JDBC有很多使用不方便的地方，尤其对命名参数的支持很差。为了方便程序的使用，本人直接编写了一些辅助类及函数，以供大家参考 代码如下：(本人采用的是Oracle数据库,其他数据库一样操作) package com.chinadci.datamodule;   import java.util.ArrayList; import java.util.HashMap;   public class SQLUtils {       //是否是标示字符     private static boolean IsIdientChar(char c)     {         return (c >= '0' && c <= '9') || (c >= 'a' && c <= 'z')                 || (c >= 'A' && c <= 'Z') || c == '_' || c == '$' || c == '#'                 || (c >= 128 && c <= 256);     }       private static void RegisterParam(HashMap paramsMap,String paramName,int Index)     {         paramName = paramName.toLowerCase();         ArrayList List = null;         if(paramsMap.get(paramName)==null)         {             List = new ArrayList();             paramsMap.put(paramName,List);         }         else         {             List = (ArrayList)paramsMap.get(paramName);         }         List.add(Integer.toString(Index));     }     /**      * ParserSQLParam 解析SQL语句中的参数      */     public static void ParserSQLParam(String SQL,HashMap paramsMap)     {         SQL += "/n";         char Mode = 'S';         String VarName = "";         int SQLLength = SQL.length();         int Index = 1;           char C = 0;         for (int i = 0; i < SQLLength; i++)         {             C = SQL.charAt(i);             switch (Mode)             {                 case 'S':                     if (C == ':')                     {                         Mode = 'V';                         VarName = "";                     }                     else if (C == '/'')                     {                         Mode = 'Q';                     }                     else if (C == '/' && SQL.charAt(i + 1) == '*')                     {                         Mode = 'C';                     }                     else if (C == '-' && SQL.charAt(i + 1) == '-')                     {                         Mode = 'c';                     }                     break;                 case 'V':                     if (C <= 255 && !IsIdientChar(C))                     {                         if (!VarName.equals(""))                         {                             RegisterParam(paramsMap,VarName,Index);                             Index++;                             Mode = 'S';                         }                     }                     else                     {                         VarName += C;                     }                     break;                 case 'c':                     if(C=='/n')                     {                         Mode = 'S';                     }                     break;                 case 'C':                     if(C=='*' && SQL.charAt(i+1)=='/')                     {                         Mode = 'S';                     }                     break;                 case 'Q':                     if (C == '/'')                     {                         Mode = 'S';                     }                     break;             }         }     }     /**      * TransNamedParam 将Script中命名的参数转化为JDBC支持的参数?      * @param SQL      * @return      */     public static String TransNamedParam(String SQL)     {         StringBuffer strBuf = new StringBuffer();                 SQL += "/n";         char Mode = 'S';         int SQLLength = SQL.length();           char C = 0;         for (int i = 0; i < SQLLength; i++)         {             C = SQL.charAt(i);             switch (Mode)             {                 case 'S':                     if (C == ':')                     {                         Mode = 'V';                         strBuf.append("?");                     }                     else if (C == '/'')                     {                         Mode = 'Q';                     }                     else if (C == '/' && SQL.charAt(i + 1) == '*')                     {                         Mode = 'C';                     }                     else if (C == '-' && SQL.charAt(i + 1) == '-')                     {                         Mode = 'c';                     }                     break;                 case 'V':                     if (C <= 255 && !IsIdientChar(C))                     {                             Mode = 'S';                     }                     break;                 case 'c':                     if(C=='/n')                     {                         Mode = 'S';                     }                     break;                 case 'C':                     if(C=='*' && SQL.charAt(i+1)=='/')                     {                         Mode = 'S';                     }                     break;                 case 'Q':                     if (C == '/'')                     {                         Mode = 'S';                     }                     break;             }             if(Mode!='V')             {               strBuf.append(C);              }         }         return strBuf.toString();     }       private SQLUtils()     {         super();     }   }   /**  * 广州城市信息研究所 Oracle Script的实现 用于弥补 OraclePreparedStatement的不足 主要用于修改  * setStringAtName等错误情况 ver 1.0  */ package com.chinadci.datamodule;   import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap;   import oracle.jdbc.OraclePreparedStatement; import oracle.sql.BLOB; import oracle.sql.CLOB; import oracle.sql.DATE;   public class OracleScript {     private OraclePreparedStatement oraStmt = null;       private HashMap paramsMap = new HashMap();       /**      * OracleScript 构造函数用于创建一个 OracleScript      *      * @param con      *            数据库连接      * @param SQL      *            SQL语句      * @throws SQLException      */     public OracleScript(Connection con, String SQL) throws SQLException     {         super();         oraStmt = (OraclePreparedStatement) con.prepareStatement(SQLUtils.TransNamedParam(SQL));         SQLUtils.ParserSQLParam(SQL, paramsMap);     }       /**      * Execute 执行Script命令      *      * @return 成功返回true 否则返回 false      * @throws SQLException      */     public boolean Execute() throws SQLException     {         return oraStmt.execute();     }       /**      * SetStringAtName 实现      * @param ParamName      * @param Value      * @throws SQLException      */     public void setStringAtName(String paramName, String value) throws SQLException     {         ArrayList paramList = (ArrayList) paramsMap.get(paramName);         if (paramList != null)         {            for(int i=0;i<paramList.size();i++)            {                int ID = (new Integer((String)paramList.get(i))).intValue();                oraStmt.setString(ID,value);            }         }     }         /**      * setIntAtName 实现      * @param ParamName      * @param Value      * @throws SQLException      */     public void setIntAtName(String paramName, int value) throws SQLException     {         ArrayList paramList = (ArrayList) paramsMap.get(paramName);         if (paramList != null)         {            for(int i=0;i<paramList.size();i++)            {                int ID = (new Integer((String)paramList.get(i))).intValue();                oraStmt.setInt(ID,value);            }         }     }         /**      * setDateAtName 实现      * @param ParamName      * @param Value      * @throws SQLException      */     public void setDateAtName(String paramName,DATE  value) throws SQLException     {         ArrayList paramList = (ArrayList) paramsMap.get(paramName);         if (paramList != null)         {            for(int i=0;i<paramList.size();i++)            {                int ID = (new Integer((String)paramList.get(i))).intValue();                oraStmt.setDATE(ID,value);            }         }     }       /**      * setBOLBAtName 实现      * @param ParamName      * @param Value      * @throws SQLException      */     public void setBLOBAtName(String paramName,BLOB  value) throws SQLException     {         ArrayList paramList = (ArrayList) paramsMap.get(paramName);         if (paramList != null)         {            for(int i=0;i<paramList.size();i++)            {                int ID = (new Integer((String)paramList.get(i))).intValue();                oraStmt.setBLOB(ID,value);            }         }     }       /**      * setCLOBAtName 实现      * @param ParamName      * @param Value      * @throws SQLException      */     public void setCLOBAtName(String paramName,CLOB  value) throws SQLException     {         ArrayList paramList = (ArrayList) paramsMap.get(paramName);         if (paramList != null)         {            for(int i=0;i<paramList.size();i++)            {                int ID = (new Integer((String)paramList.get(i))).intValue();                oraStmt.setCLOB(ID,value);            }         }     }         /**      * Commit 提交      * @throws SQLException      */     public void Commit() throws SQLException     {         oraStmt.getConnection().commit();     }   } //测试代码段 public static void main(String[] args) throws Exception {         Connection con = OraDB.getConnection();         String SQL = "begin update blobtest set id=:id1 where id<>:id1;end;";         OracleScript oraScript = new OracleScript(con,SQL);         oraScript.setIntAtName("id1",1);         oraScript.Execute(); }     后记 OraclePrepareStatement已经提供了对命名参数的支持但它只支持简单的SQL语句而不支持SQL代码段