spinfodao.java

采用JAVA开发

package com.gctech.sms.dao;

import java.util.Collection;
import java.util.Map;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import org.apache.log4j.Logger;
import com.gctech.sms.util.ConnectionManager;
import com.gctech.sms.vo.SpInfoObject;
import java.sql.PreparedStatement;
import java.sql.*;
import java.util.ArrayList;
/**
 * <p>Title: SP管理模块</p>
 * <p>Description: 负责增、删除、修改Sp数据</p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: gctech</p>
 * @author wengjl
 * @version $Id: SpInfoDao.java,v 1.3 2004/04/22 01:46:30 wanghb Exp $
 */

public class SpInfoDao {
  //public SpInfoDao() {
  //}



  static SpInfoObject fromResultSet(ResultSet rs) throws SQLException {
    SpInfoObject model = new SpInfoObject();
    String id = rs.getString("id");
    model.setId(id);
    String name = rs.getString("name");
    model.setName(name);
    String des = rs.getString("desciption");
    model.setDesc(des);
    String linkman = rs.getString("linkman");
    model.setLinkMan(linkman);
    String tel = rs.getString("tel");
    model.setTel(tel);
    String fax = rs.getString("fax");
    model.setFax(fax);
    String email = rs.getString("email");
    model.setEmail(email);
    String url = rs.getString("url");
    model.setUrl(url);
    String settlerate = rs.getString("settlerate");
    model.setSettleRate(settlerate);
    return model;
  }
  public static SpInfoObject findByPk(String spId){
    SpInfoObject rt = null;
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SpInfoDao.class);
      stmt = con.createStatement();
      rs = stmt.executeQuery("select * from spInfo where id='"+spId+"'");
      if ( rs.next() ){
        rt = fromResultSet(rs);
      }
    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( rs != null ){
        try {
          rs.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( stmt != null ){
        try {
          stmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
    }
    return rt;
  }
  public static int addSp(SpInfoObject info){//增加数据

    Connection con = null;
    PreparedStatement pstmt = null;
    int rs = 0;
    try {
      con = ConnectionManager.getInstance().getConnection(SpInfoDao.class);
      String sql = " INSERT INTO SPINFO ( SPINFO.ID,Spinfo.NAME,Spinfo.DESCIPTION,Spinfo.LINKMAN,spinfo.tel,spinfo.email,spinfo.fax,spinfo.url,spinfo.SETTLERATE ) VALUES ( ?, ?, ? , ?, ?, ?, ?, ?, ?)  ";
      pstmt = con.prepareStatement(sql.toString());//往数据库增加数据
      if (info.getId() == null){
        pstmt.setNull(1, Types.VARCHAR);
      }else{
        pstmt.setString(1, info.getId());
      }
      if (info.getName()==null){
        pstmt.setNull(2, Types.VARCHAR);
      }else{
        pstmt.setString(2, info.getName());
      }
      if (info.getDesc()==null){
        pstmt.setNull(3, Types.VARCHAR);
      }else{
        pstmt.setString(3, info.getDesc());
      }
      if (info.getLinkMan()==null){
        pstmt.setNull(4, Types.VARCHAR);
      }else{
        pstmt.setString(4, info.getLinkMan());
      }
      if (info.getTel()==null){
        pstmt.setNull(5, Types.VARCHAR);
      }else{
        pstmt.setString(5, info.getTel());
      }
      if (info.getEmail()==null){
        pstmt.setNull(6, Types.VARCHAR);
      }else{
        pstmt.setString(6, info.getEmail());
      }
      if (info.getFax()==null){
        pstmt.setNull(7, Types.VARCHAR);
      }else{
        pstmt.setString(7, info.getFax());
      }
      if (info.getUrl()==null){
        pstmt.setNull(8, Types.VARCHAR);
      }else{
        pstmt.setString(8, info.getUrl());
      }
      if (info.getSettleRate()==null){
        pstmt.setNull(9, Types.INTEGER);
      }else{
        pstmt.setString(9, info.getSettleRate());
      }
      rs = pstmt.executeUpdate();
    }
    catch (Throwable ex) {
      logger.error(ex, ex);
    }finally{
      if ( pstmt != null ){
        try {
          pstmt.close();
        }
        catch (SQLException ex1) {
          logger.error(ex1, ex1);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (SQLException ex1) {
          logger.error(ex1, ex1);
        }
      }
    }
    return rs;
  }
  public static SpInfoObject[] getAllSpInfo(SpInfoObject spinfovalue) throws SQLException {//查询所有数据
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();
        try{
          con = ConnectionManager.getInstance().getConnection(SpInfoDao.class);
          StringBuffer sql = new StringBuffer("select a.id,a.name,a.DESCIPTION,a.LINKMAN,a.tel,");
          sql.append( "a.fax,a.email,a.url,a.SETTLERATE");
          sql.append( " from spinfo a");
          sql.append( " where 1=1 ");
          if ( spinfovalue!=null && spinfovalue.getId()!=null&&!spinfovalue.getId().equals("")){
            sql.append( " and a.id = '" + spinfovalue.getId() + "'");
          }

          logger.debug(sql.toString() + " sql");
          pstmt = con.prepareStatement(sql.toString());
          rs = pstmt.executeQuery();
          while (rs.next()){
            SpInfoObject model = new SpInfoObject();
            String id = rs.getString("id");
            if (!rs.wasNull()) model.setId(id);
            String name = rs.getString("name");
            if (!rs.wasNull()) model.setName(name);
            String des = rs.getString("desciption");
            if (!rs.wasNull()) model.setDesc(des);
            String linkman = rs.getString("linkman");
            if (!rs.wasNull()) model.setLinkMan(linkman);
            String tel = rs.getString("tel");
            if (!rs.wasNull()) model.setTel(tel);
            String fax = rs.getString("fax");
            if (!rs.wasNull()) model.setFax(fax);
            String email = rs.getString("email");
            if (!rs.wasNull()) model.setEmail(email);
            String url = rs.getString("url");
            if (!rs.wasNull()) model.setUrl(url);
            String settlerate = rs.getString("settlerate");
            if (!rs.wasNull()) model.setSettleRate(settlerate);


            list.add(model);
          }
        }catch( Throwable e ){
          logger.error(e, e);
        }finally{
          if ( rs != null ){
            try {
              rs.close();
            }
            catch (Throwable ex) {
              logger.error(ex, ex);
            }
          }
          if ( pstmt != null ){
            try {
              pstmt.close();
            }
            catch (Throwable ex) {
              logger.error(ex, ex);
            }
          }
          if ( con != null ){
            try {
              con.close();
            }
            catch (Throwable ex) {
              logger.error(ex, ex);
            }
          }
        }
        //返回数据
        return (SpInfoObject[])list.toArray(new SpInfoObject[list.size()]);
  }
  public int deleteSpInfo(SpInfoObject spinfovalue) throws SQLException {//删除数据
        Connection con = null;
        PreparedStatement pstmt = null;
        int rs = 0 ;
        try{
          con = ConnectionManager.getInstance().getConnection(SpInfoDao.class);
          StringBuffer sql = new StringBuffer("delete from spinfo where 1=1 ");
          sql.append( "and spinfo.id = '" + spinfovalue.getId() + "'");

          pstmt = con.prepareStatement(sql.toString());
          rs = pstmt.executeUpdate();

        }catch( Throwable e ){
          logger.error(e, e);
        }finally{
          if ( pstmt != null ){
            try {
              pstmt.close();
            }
            catch (Throwable ex) {
              logger.error(ex, ex);
            }
          }
          if ( con != null ){
            try {
              con.close();
            }
            catch (Throwable ex) {
              logger.error(ex, ex);
            }
          }
        }
        //返回数据
        return rs;
  }
  public int updateSpInfo(SpInfoObject spinfovalue) throws SQLException {//修改数据
    Connection con = null;
    PreparedStatement pstmt = null;
    int rs = 0 ;
    try{
      con = ConnectionManager.getInstance().getConnection(SpInfoDao.class);
      StringBuffer sql = new StringBuffer("update spinfo set ");
      if (spinfovalue.getName()!=null){
        if (spinfovalue.getName().equals("@_@")){
            sql.append( " spinfo.name = null, ");
        }else{
            sql.append( " spinfo.name = '" + spinfovalue.getName() + "',");
        }
      }
      if (spinfovalue.getDesc()!=null){
        if (spinfovalue.getDesc().equals("@_@")){
           sql.append( " spinfo.DESCIPTION = null, ");
        }else{
          sql.append( " spinfo.DESCIPTION = '" + spinfovalue.getDesc() + "',");
        }
      }
      if (spinfovalue.getLinkMan()!=null){
        if (spinfovalue.getLinkMan().equals("@_@")){
           sql.append( " spinfo.linkman = null, ");
        }else{
          sql.append( " spinfo.linkman = '" + spinfovalue.getLinkMan() + "',");
        }
      }
      if (spinfovalue.getTel()!=null){
        if (spinfovalue.getTel().equals("@_@")){
           sql.append( " spinfo.tel = null, ");
        }else{
          sql.append( " spinfo.tel = '" + spinfovalue.getTel() + "',");
        }
      }
      if (spinfovalue.getEmail()!=null){
        if (spinfovalue.getEmail().equals("@_@")){
           sql.append( " spinfo.email = null, ");
        }else{
          sql.append( " spinfo.email = '" + spinfovalue.getEmail() + "',");
        }
      }
      if (spinfovalue.getFax()!=null){
        if (spinfovalue.getFax().equals("@_@")){
           sql.append( " spinfo.fax = null, ");
        }else{
          sql.append( " spinfo.fax = '" + spinfovalue.getFax() + "',");
        }
      }
      if (spinfovalue.getUrl()!=null){
        if (spinfovalue.getUrl().equals("@_@")){
           sql.append( " spinfo.url = null, ");
        }else{
          sql.append( " spinfo.url = '" + spinfovalue.getUrl() + "',");
        }
      }
      if (spinfovalue.getSettleRate()!=null){
        if (spinfovalue.getSettleRate().equals("@_@")){
           sql.append( " spinfo.SETTLERATE = null, ");
        }else{
          sql.append( " spinfo.SETTLERATE = '" + spinfovalue.getSettleRate() + "',");
        }
      }
      sql.setLength(sql.length()-1);
      sql.append( " where 1=1");
      if (spinfovalue.getId()!=null){
        sql.append(" and spinfo.id = '" + spinfovalue.getId()+ "'");
      }
      logger.debug(sql.toString() + " update");
      pstmt = con.prepareStatement(sql.toString());

      rs = pstmt.executeUpdate();

    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( pstmt != null ){
        try {
          pstmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
    }
    //返回数据
    return rs;

  }
/*
     stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
        rs = stmt.executeQuery(sb.toString());
        rs.last();
        int totalRecord = rs.getRow();
        int totalPage = (totalRecord + pageSize - 1) / pageSize;
        list.setTotalPage(totalPage);
        list.setTotalRecord(totalRecord);
        if (rs.absolute( (nextPage - 1 ) * pageSize + 1)) {
          rs.previous();
          for (; rs.next() && pageSize > 0; pageSize--) {

          }

      */
  static final Logger logger = Logger.getLogger(SpInfoDao.class);
}