delplace.java

WAP PUSH后台源码,WAP PUSH后台源码

package com.sxit.wap.dbtools;

import java.sql.*;
import java.util.*;
import com.sxit.wap.common.*;
import com.sxit.wap.exception.*;

public class delplace {
  public Collection queryBySql(String sql, int beginRow, int endRow) throws
      SysException {
    Connection dbConnection = null;
    Statement stmt = null;
    ResultSet rs = null;
    ArrayList list = new ArrayList();
    try {
      dbConnection = getDBConnection();
      stmt = dbConnection.createStatement();
      rs = stmt.executeQuery(sql);
      ResultSetMetaData rsmd = rs.getMetaData();
      int row = 0;
      while (rs.next()) {
        if (++row < beginRow)
          continue;
        if (row > endRow)
          break;
        Hashtable element = new Hashtable();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
          String columnName = rsmd.getColumnName(i);
          String fieldName = Function.format(columnName);
          String fieldType = ColumnType.getFieldType(Database.dbType, rsmd, i);
          if (fieldType == null)
            continue;
          if ("Timestamp".equals(fieldType)) {
            element.put(fieldName, rs.getTimestamp(columnName));
          }
          else if ("String".equals(fieldType)) {
            String value = Function.readDBEncode(rs.getString(columnName));
            if (value == null)
              value = "";
            element.put(fieldName, value);
          }
          else if ("int".equals(fieldType)) {
            element.put(fieldName, String.valueOf(rs.getInt(columnName)));
          }
          else if ("long".equals(fieldType)) {
            element.put(fieldName, String.valueOf(rs.getLong(columnName)));
          }
          else if ("float".equals(fieldType)) {
            element.put(fieldName, String.valueOf(rs.getFloat(columnName)));
          }
        }
        list.add(element);
      }
      return list;
    }
    catch (SQLException e) {
      Debug.println(sql + "\n" +
                    "SQLException while execute queryBySql mothod :\n" + e);
      throw new SysException("SQLException while execute queryBySql mothod :\n" +
                             e);
    }
    finally {
      closeResultSet(rs);
      closeStatement(stmt);
      closeConnection(dbConnection);
    }
  }

  public int getRowCountBySql(String sql) throws SysException {
    Connection dbConnection = null;
    Statement stmt = null;
    ResultSet rs = null;
    int rowCount = 0;
    try {
      dbConnection = getDBConnection();
      stmt = dbConnection.createStatement();
      rs = stmt.executeQuery(sql);
      if (rs.next()) {
        rowCount = rs.getInt(1);
      }
      else {
        Debug.println(sql + "\n" + "ERROR in get row count!!");
        //throw new SysException ( "ERROR in get row count!!" );
      }
      return rowCount;
    }
    catch (SQLException e) {
      Debug.println(sql + "\n" +
                    "SQLException while execute getRowCountBySql mothod :\n" +
                    e);
      throw new SysException(
          "SQLException while execute getRowCountBySql mothod :\n" + e);
    }
    finally {
      closeResultSet(rs);
      closeStatement(stmt);
      closeConnection(dbConnection);
    }
  }

  private Connection getDBConnection() throws SysException {
    return Database.getConnection();
  }

  private void closeConnection(Connection dbConnection) throws SysException {
    try {
      if (dbConnection != null && !dbConnection.isClosed()) {
        dbConnection.close();
        dbConnection = null;
      }
    }
    catch (SQLException se) {
      //throw new SysException("SQL Exception while closing DB connection : \n" + se);
    }
  }

  private void closeResultSet(ResultSet result) throws SysException {
    try {
      if (result != null) {
        result.close();
      }
    }
    catch (SQLException se) {
      //throw new SysException("SQL Exception while closing Result Set : \n" + se);
    }
  }

  private static void closeStatement(Statement stmt) throws SysException {
    try {
      if (stmt != null) {
        stmt.close();
      }
    }
    catch (SQLException se) {
      //throw new SysException("SQL Exception while closing Statement : \n" + se);
    }
  }

  public Collection queryBySql(String sql) throws SysException {
    Connection dbConnection = null;
    Statement stmt = null;
    ResultSet rs = null;
    ArrayList list = new ArrayList();
    try {
      dbConnection = getDBConnection();
      stmt = dbConnection.createStatement();
      rs = stmt.executeQuery(sql);
      ResultSetMetaData rsmd = rs.getMetaData();
      while (rs.next()) {
        Hashtable element = new Hashtable();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
          String columnName = rsmd.getColumnName(i);
          String fieldName = Function.format(columnName);
          String fieldType = ColumnType.getFieldType(Database.dbType, rsmd, i);
          if (fieldType == null)
            continue;
          if ("Timestamp".equals(fieldType)) {
            element.put(fieldName, rs.getTimestamp(columnName));
          }
          else if ("String".equals(fieldType)) {
            String value = Function.readDBEncode(rs.getString(columnName));
            if (value == null)
              value = "";
            element.put(fieldName, value);
          }
          else if ("int".equals(fieldType)) {
            element.put(fieldName, String.valueOf(rs.getInt(columnName)));
          }
          else if ("long".equals(fieldType)) {
            element.put(fieldName, String.valueOf(rs.getLong(columnName)));
          }
          else if ("float".equals(fieldType)) {
            element.put(fieldName, String.valueOf(rs.getFloat(columnName)));
          }
        }
        list.add(element);
      }
      return list;
    }
    catch (SQLException e) {
      Debug.println(sql + "\n" +
                    "SQLException while execute queryBySql mothod :\n" + e);
      throw new SysException("SQLException while execute queryBySql mothod :\n" +
                             e);
    }
    finally {
      closeResultSet(rs);
      closeStatement(stmt);
      closeConnection(dbConnection);
    }
  }

  public  int updateBySql(String sql) throws SysException {
   Connection dbConnection = null;
   Statement stmt = null;
   try {
     dbConnection = getDBConnection();
     stmt = dbConnection.createStatement();
     return stmt.executeUpdate(sql);
   } catch(SQLException e) {
     Debug.println(sql + "\n" + "SQLException while execute updateBySql mothod :\n" + e);
     throw new SysException("SQLException while execute updateBySql mothod :\n" + e);
   } finally {
     closeStatement(stmt);
     closeConnection(dbConnection);
   }
 }

  public void getStaffUserWS() throws SysException {
    String sql =
        "select id,user_name from wap_staff where user_name like '% %'";
    Collection coll = this.queryBySql(sql);
    Iterator it = coll.iterator();
    int j = 1;
    while (it.hasNext()) {
      Hashtable element = (Hashtable) it.next();
      String id = (String) element.get("id");
      String userName = (String) element.get("userName");
      System.out.println("序列号:" + j++);
      System.out.println("修改前的:" + id);
      System.out.println("修改前的:" + userName);
      userName = StringUtil.delSpace(userName);
      System.out.println("修改后的:" + userName);
      String usql = "update wap_staff set user_name ='" +userName + "' where id = " + id;
      int i = this.updateBySql(usql);
      if(i==1){
        System.out.println("数据更新成功");
      }
    }
  }

  public static void main(String[] args) {
    try {
      delplace dd = new delplace();
      //dd.getStaffUserWS();
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
  }

}