executeoracle.java

基于数据库操作的封装

    }catch(SQLException sqlex){
      throw sqlex;
    }catch(Exception ex){
      throw ex;
    }finally{
      return hashResult;
    }
  }


  /**
   *
   *    key  -- PrimaryKeyObj
   * 	  item -- Hashtable	锟斤拷要锟斤拷锟铰碉拷锟斤拷锟絓uFFFD
   *
   * @see org.shaoye.magic.Operate#set(Object, Map)
   *
   */
  public void set(Object key, Map item) throws SQLException {

          if (table == null || table.length() == 0)
                  throw (new MagicException("Error 001 : table's name is null"));
          //001锟斤拷锟斤拷

          sql = "";
          sql += "UPDATE " + table + " SET ";
          int size = item.size();
          String[] arrfield = new String[size];
          Object[] arrvalue = new Object[size];

          PrimaryKeyObj _pk = new PrimaryKeyObj();
          _pk = (PrimaryKeyObj) key;

          String condition_param = "";
          String param = "";
          Hashtable _item = new Hashtable(item);
          int i = 0;

          for (java.util.Enumeration en = _item.keys(); en.hasMoreElements();) {
                  String fieldName = (String)en.nextElement();
                  arrfield[i] = fieldName;
                  arrvalue[i] = _item.get(fieldName);
                  sql = sql + fieldName + "=?";
                  if (i < size - 1) {
                          sql += ",";
                          param += ",";
                  }
                  i++;
          }
          sql = sql.substring(0,sql.length()-1);
          sql += " WHERE ";

          for (int j = 0; j < _pk.getPrimaryKeySize(); j++) {
                  Column col = new Column();
                  sql = sql + ((Column) _pk.getPrimaryKey(j)).getName() + "=?";
                  if (j < _pk.getPrimaryKeySize() - 1)
                          sql += " and ";
          }

          pstmt = conn.prepareStatement(sql);
          System.out.println(sql);
          for (int k = 0; k < arrfield.length; k++) {
                  this.setData(pstmt, k + 1, arrfield[k], arrvalue[k]);
          }

          for (int l = 0; l < _pk.getPrimaryKeySize(); l++) {
                  this.setData(
                          pstmt,
                          l + 1 + arrfield.length,
                          ((Column) _pk.getPrimaryKey(l)).getName(),
                          ((Column) _pk.getPrimaryKey(l)).getDefaultValue());
          }

          pstmt.executeUpdate();

  }

  /**
  *
  *    key  -- PrimaryKeyObj
  * 	  item -- Hashtable	锟斤拷要锟斤拷锟铰碉拷锟斤拷锟絓uFFFD
  *
  * @see org.shaoye.magic.Operate#set(Object, Map)
  *
  */
 public void set(PrimaryKeyObj key, Map item) throws SQLException {

         if (table == null || table.length() == 0)
                 throw (new MagicException("Error 001 : table's name is null"));
         //001锟斤拷锟斤拷

         sql = "";
         sql += "UPDATE " + table + " SET ";
         int size = item.size();
         String[] arrfield = new String[size];
         Object[] arrvalue = new Object[size];

         PrimaryKeyObj _pk = new PrimaryKeyObj();
         _pk = (PrimaryKeyObj) key;

         String condition_param = "";
         String param = "";
         Hashtable _item = new Hashtable(item);
         int i = 0;

         for (java.util.Enumeration en = _item.keys(); en.hasMoreElements();) {
                 String fieldName = (String)en.nextElement();
                 arrfield[i] = fieldName;
                 arrvalue[i] = _item.get(fieldName);
                 if(arrvalue[i]!=null)
                	 sql = sql + fieldName + "=";
                 else
                	 i++;
                 
                 Set col_keys = this.columns.keySet();
                 java.util.Iterator col_iter = col_keys.iterator();
                 while(col_iter.hasNext())
                 {
               	  String col_key = (String)col_iter.next();
               	  String col_type = "";
               	  if(fieldName.equals(col_key)&&arrvalue[i]!=null)
               	  {
               		  col_type = ((Column)columns.get(fieldName)).getType();
               		  if(col_type.toUpperCase().indexOf("CHAR") >= 0) {
               			  sql +="'"+arrvalue[i]+"',";
               		  }
               		  else if(col_type.equalsIgnoreCase("NUMBER")){
               			  sql +=arrvalue[i]+",";
               		  }
               		  else if(col_type.equalsIgnoreCase("DATE")){
               			  sql +="TO_DATE('"+arrvalue[i]+"','"
               			           + Globals.DATE_FORMAT_SHORT +"'),";
               		  }
               		  else
               			  continue;
               	  }
                 } 
         }
         sql = sql.substring(0,sql.length()-1);
         sql += " WHERE ";

         for (int j = 0; j < _pk.getPrimaryKeySize(); j++) {
                 Column col = new Column();
                 sql = sql + ((Column) _pk.getPrimaryKey(j)).getName() + "=" + ((Column)_pk.getPrimaryKey(j)).getDefaultValue();
                 if (j < _pk.getPrimaryKeySize() - 1)
                         sql += " and ";
         }
         if(i==size);
         else{	 
        	 pstmt = conn.prepareStatement(sql);
        	 System.out.println(sql);
        	 pstmt.executeUpdate();
         }

 }  
  
  /**
   * 锟斤拷锟斤拷莘诺锟絇reparedStatement锟斤拷
   * */
  protected void setData(
          PreparedStatement ps,
          int columnIndex,
          String colname,
          Object value)
          throws SQLException {
          String _break="";
          for (Enumeration en = columns.keys(); en.hasMoreElements();) {
            Column column = new Column();
            String _colname = (String)(en.nextElement());
            if (colname.equalsIgnoreCase(_colname)) {
              column = (Column)columns.get(_colname);
              _break="break";
            }
            else
              continue;
            String type = column.getType();
            if (type.toUpperCase().indexOf("CHAR") >= 0) {
              System.out.println(column.getName());
              ps.setString(columnIndex, (String) value);
              return;
            }
            if (type.equalsIgnoreCase("NUMBER") || type.equalsIgnoreCase("INT")) {
              String val = "";
                  val = (String)value;
              if(val==null || val.equals(""))val="0";
              if (column.getScale() == 0 && column.getPrecision() <= 12) {
                if(val=="0")
                  ps.setInt(columnIndex, (Integer.parseInt(val)));
                else
                  ps.setInt(columnIndex, (Integer.parseInt(String.valueOf(value))));
                return;
              }
              if (column.getScale() == 0 && column.getPrecision() > 12) {
                if(val=="0")
                  ps.setLong(columnIndex, (Long.parseLong(val)));
                else
                  ps.setLong(columnIndex, ( Long.parseLong(String.valueOf(value))));
                return;
              }
              if (column.getScale() > 0) {
                if(val=="0")
                  ps.setDouble(columnIndex, ( Double.parseDouble(val)));
                else
                  ps.setDouble(columnIndex, ( Double.parseDouble(String.valueOf(value))));
                return;
              }
            }
            if (type.equalsIgnoreCase("DATE")) {
              if (value instanceof java.sql.Date) {
                ps.setDate(columnIndex, (java.sql.Date) value);
                return;
              }
              if (value instanceof java.sql.Time) {
                ps.setTime(columnIndex, (java.sql.Time) value);
                return;
              }
              if (value instanceof java.util.Date) {
                ps.setTimestamp(
                    columnIndex,
                    (new java.sql.Timestamp( ( (java.util.Date) value).getTime())));
                return;
              }
              throw new MagicException(
                  "Error 010 : Datetime object instance "
                  + value.getClass().getName()
                  + " no support");
            }
            if(_break.equals("break"))
              break;
          }
  }

  /**
   * 取锟斤拷一锟斤拷锟斤拷\uFFFD
   * key -- 锟斤拷录锟斤拷锟斤拷锟斤拷锟絓uFFFD
   *
   * @see org.shaoye.magic.Operate#get(Object)
   */
  public Map get(Object key) throws SQLException,Exception {

          if (table == null || table.length() == 0)
                  throw (new MagicException("Error 001 : table's name is null"));
          //001锟斤拷锟斤拷
          Hashtable hashResult = new Hashtable();
          PrimaryKeyObj _pk = new PrimaryKeyObj();
          ResultSet rs = null;
          _pk = (PrimaryKeyObj) key;
          try{
            sql = "";
            sql += "SELECT * FROM " + table + " WHERE ";

            for (int j = 0; j < _pk.getPrimaryKeySize(); j++) {
              Column col = new Column();
              sql = sql + ( (Column) _pk.getPrimaryKey(j)).getName() + "=?";
              if (j < _pk.getPrimaryKeySize() - 1)
                sql += " and ";
            }
            pstmt =
                conn.prepareStatement(
                sql);
            System.out.println(sql);

            for (int l = 0; l < _pk.getPrimaryKeySize(); l++) {
              this.setData(
                  pstmt,
                  l + 1,
                  ( (Column) _pk.getPrimaryKey(l)).getName(),
                  ( (Column) _pk.getPrimaryKey(l)).getDefaultValue());
            }
            rs = pstmt.executeQuery();

            if (rs.next()) {
              for (int s = 0; s < rs.getMetaData().getColumnCount(); s++) {
                if (rs.getString(rs.getMetaData().getColumnName(s + 1)) != null)
                  hashResult.put(
                      rs.getMetaData().getColumnName(s + 1),
                      rs.getString(rs.getMetaData().getColumnName(s + 1)));
                else
                  hashResult.put(
                      rs.getMetaData().getColumnName(s + 1), "");

              }
            }
            if (rs != null)
              rs.close();
          }catch(SQLException sqlex){
            throw sqlex;
          }catch(Exception ex){
            throw ex;
          }finally{
            return hashResult;
          }
  }

  /**
   * 锟斤拷始锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷\uFFFD
   * */
  protected Hashtable initColumn(String owner,String tblName) throws SQLException {
          String sql = "select * from all_tab_columns "
               + "where owner=upper('"+ owner +"') and table_name=upper('"+ tblName +"')";
          System.out.println(sql);
          Hashtable _columns = new Hashtable();
          pstmt = conn.prepareStatement(sql);
          ResultSet rs = pstmt.executeQuery();
          while (rs.next()) {
                  Column column = new Column();
                  column.setColumnId(rs.getInt("COLUMN_ID"));
                  column.setDefaultValue(rs.getString("DATA_DEFAULT"));
                  column.setLength(rs.getInt("DATA_LENGTH"));
                  column.setName(rs.getString("COLUMN_NAME"));
                  String nullable = rs.getString("NULLABLE");
                  column.setNullable(nullable.equalsIgnoreCase("Y") ? true : false);
                  column.setType(rs.getString("DATA_TYPE"));
                  if (column.getType().equalsIgnoreCase("number")) {
                          column.setPrecision(rs.getInt("DATA_PRECISION"));
                          column.setScale(rs.getInt("DATA_SCALE"));
                  }
                  _columns.put(column.getName(), column);
          }
          rs.close();
          return _columns;
  }