Chinaunix首页 | 论坛 | 博客
  • 博客访问: 992516
  • 博文数量: 152
  • 博客积分: 4937
  • 博客等级: 上校
  • 技术积分: 1662
  • 用 户 组: 普通用户
  • 注册时间: 2007-10-05 16:55
文章分类

全部博文(152)

文章存档

2013年(12)

2012年(6)

2011年(58)

2010年(43)

2009年(1)

2008年(15)

2007年(17)

我的朋友

分类: Java

2010-07-06 11:04:08

package com.newspager.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.newspager.dao.PagerDAO;
import com.newspager.model.Pager;
import com.newspager.util.DB;
import com.newspager.vo.VOpager;
public class PagerDAOImpl implements PagerDAO {
 
 public boolean add(Pager pager,int[] objects) {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  boolean bl=false;
  int pid = 0;
  try{
   conn = DB.getConn();
   DB.setAutoCommit(false, conn);
   
   String sql = "insert into pager(name,pweek,orderNo,canReturn,stopDate,price,invalidation,messageName,tempWeek) values(?,?,?,?,?,?,?,?,0)";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setString(1, pager.getName());
   pstmt.setInt(2, pager.getPweek());
   pstmt.setInt(3, pager.getOrderNo());
   pstmt.setString(4, pager.getCanReturn());
   pstmt.setString(5, pager.getStopDate());
   pstmt.setDouble(6, pager.getPrice());
   pstmt.setInt(7, pager.getInvalidation());
   pstmt.setString(8, pager.getMessageName());
   pstmt.execute();
   
   
   //sql = "select max(id)+1 as id from pager";//这种方法在mysql中不行
   //sql = "SELECT @@Identity";//也正确获得最新id
   sql = "select @@Identity";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.execute();
   rs = pstmt.getResultSet();
   if(rs.next())pid = rs.getInt(1);
   
   if(objects != null){
    sql = "insert into provider(pagerId,userId) values(?,?)";
    pstmt = DB.prepareStmt(conn, sql);
    for(int i=0;i     pstmt.setInt(1, pid);
     pstmt.setInt(2, objects[i]);
     pstmt.addBatch();
    }
    pstmt.executeBatch();
    
    //新添加的报纸,设置供货商单价为0
    sql = "insert into price(pagerId,userId,price) values(?,?,0)";
    pstmt = DB.prepareStmt(conn, sql);
    for(int i=0;i     pstmt.setInt(1, pid);
     pstmt.setInt(2, objects[i]);
     pstmt.addBatch();
    }
    pstmt.executeBatch();
   }
   
   
   
   DB.commit(conn);
   DB.setAutoCommit(true, conn);
   bl = true;
  }catch(Exception ex){
   DB.roleBack(conn);
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(rs);
   DB.close(conn);
  }
  return bl;
 }
 public boolean delete(int id) {
  Connection conn = null;
  PreparedStatement pstmt = null;
  boolean bl = false;
  try{
   conn = DB.getConn();
   DB.setAutoCommit(false, conn);//设置为手动提交事务
   String sql = "delete from temp_number where pagerId=?";
   pstmt = DB.prepareStmt(conn, sql);
   
   Object[] params = new Object[1];
   params[0] = id;
   
   DB.setObject(pstmt, params);
   pstmt.execute();
   
   //删除供货商表记录
   sql = "delete from provider where pagerId=?";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, id);
   pstmt.execute();
   
   //删除单价表记录
   sql = "delete from price where pagerId=?";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, id);
   pstmt.execute();
   
   //删除数量表该报纸的记录
   sql = "delete from temp_number where pagerId=?";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, id);
   pstmt.execute();
   
   //注意:这几个表中具有主外键关联,所以这个表记录必须在最后删除
   //删除该报纸
   sql = "delete from pager where id=?";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, id);
   pstmt.execute();
   
   DB.commit(conn);//提交事务
   DB.setAutoCommit(true, conn);
   
   bl = true;
  }catch(Exception ex){
   DB.roleBack(conn);
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(conn);
  }
  return bl;
 }
 /**
  * 该方法取出数据用于更新报纸信息
  */
 public VOpager getById(int id) {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  VOpager pager = new VOpager();;
  
  try{
   conn = DB.getConn();
   String sql = "select *,if(invalidation=0 or invalidation is null,'生效','--') as invalidation2 from pager where id = ?";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, id);
   boolean bl = pstmt.execute();
   if (bl){
    rs = pstmt.getResultSet();
    if (rs.next()){
     pager.initUserRS(rs);
    }
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(rs);
   DB.close(pstmt);
   DB.close(conn);
  }
  return pager;
 }
 public int getIdByName(String pagerName) {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  int pid=0;
  try{
   conn = DB.getConn();
   String sql = "select id from pager where name = ?";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setString(1, pagerName);
   boolean bl = pstmt.execute();
   if (bl){
    rs = pstmt.getResultSet();
    if (rs.next()){
     pid=rs.getInt(1);
    }
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(rs);
   DB.close(pstmt);
   DB.close(conn);
  }
  return pid;
 }
 
 
 /**
  * 使用了JDBC的事务同时更新Pager表和Provider
  */
 public boolean update(Pager pager,int[] objects,int noFixUID) {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  boolean bl = false;
  String sql="";
  double price=0.0;
  try{
   conn = DB.getConn();
   conn.setAutoCommit(false);//关闭自动提交
   sql = "select * from pager where id=?";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, pager.getId());
   pstmt.execute();
   rs = pstmt.getResultSet();
   if(rs.next()){
    price=rs.getDouble("price");
   }
   if(price!=pager.getPrice()){
    sql="delete from price where pagerId=?";
    pstmt = DB.prepareStmt(conn, sql);
    pstmt.setInt(1, pager.getId());
    pstmt.execute();
   }
   
   //update表pager
   sql = "update pager set name=?,pweek=?,orderNo=?,canReturn=?,stopDate=?,price=?,tempWeek=?,invalidation=?,messageName=? where id=?";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setString(1, pager.getName());
   pstmt.setInt(2, pager.getPweek());
   pstmt.setInt(3, pager.getOrderNo());
   pstmt.setString(4, pager.getCanReturn());
   pstmt.setString(5,pager.getStopDate());
   pstmt.setDouble(6, pager.getPrice());
   pstmt.setInt(7, pager.getTempWeek());
   pstmt.setInt(8, pager.getInvalidation());
   pstmt.setString(9, pager.getMessageName());
   pstmt.setInt(10, pager.getId());
   pstmt.execute();
   
   //更新provider表采用先删除再添加方法进行更新
   sql = "delete from provider where pagerId=?";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, pager.getId());
   pstmt.execute();
   
   if(objects != null){
    sql = "insert into provider(pagerId,userId,isFixAmount) values(?,?,?)";
    pstmt = DB.prepareStmt(conn, sql);
    if(noFixUID==0){
     noFixUID=objects[0];
    }
    for(int i=0;i     pstmt.setInt(1, pager.getId());
     pstmt.setInt(2, objects[i]);
     if(objects.length>1){
      if(objects[i]==noFixUID){
       pstmt.setInt(3, 2);
      }else{
       pstmt.setInt(3, 1);
      }
     }else{
      pstmt.setInt(3, 0);
     }
     pstmt.addBatch();
     
    }
    pstmt.executeBatch();
   }
   
   //当改变报纸供货商时,同样删除曾经属于该供货商的数量
   String str="";
   String str2="";
   if(objects!=null){
    for(int i=0;i     if(i==objects.length -1){
      str=str+"?";
     }else{
      str=str+"?,";
     }
    }
    str2="and id not in("+str+")";
    sql = "delete from temp_number where pagerId=? and userId in(select id from user where userType='供货商' "+str2+")";
    pstmt = DB.prepareStmt(conn, sql);
    pstmt.setInt(1, pager.getId());
    for(int i=0;i     pstmt.setInt(i+2, objects[i]);
    }
    pstmt.execute();
   }else{
    sql = "delete from temp_number where pagerId=? and userId in(select id from user where userType='供货商')";
    pstmt = DB.prepareStmt(conn, sql);
    pstmt.setInt(1, pager.getId());
    pstmt.execute();
   }
   
   
   
   
   DB.commit(conn);
   conn.setAutoCommit(true);//打开自动提交,否则有可能会引会死锁问题
   bl = true;
  }catch(Exception ex){
   DB.roleBack(conn);
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(rs);
   DB.close(conn);
  }
  return bl;
 }
 /**
  * 按名称搜索报纸(模糊查找)
  */
 public List SearchByName(int pweek,int pageSize,int pageIndex,String name) {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  List pagers = null;
  String sql ="";
  
  try{
   conn = DB.getConn();
   
   //创建临时表
   sql = "CREATE temporary  TABLE IF NOT EXISTS  temppager (id int(11) NOT NULL auto_increment,pid int,PRIMARY KEY  (id))";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.execute();
   
   //将pager表的id号插入到临时表中
   //sql = "insert into temppager(pid) select id from pager where pweek like ? and name like ?";
   if(pweek == 8){
    sql = "insert into temppager(pid) select id from pager where name like ? and pweek<>0";
    pstmt = DB.prepareStmt(conn, sql);
    pstmt.setString(1, "%"+name+"%");
   }else{
    sql = "insert into temppager(pid) select id from pager where pweek like ? and name like ?";
    pstmt = DB.prepareStmt(conn, sql);
    pstmt.setString(1, "%"+pweek+"%");//报纸的出版日期
    pstmt.setString(2, "%"+name+"%");
   }
   
//   pstmt = DB.prepareStmt(conn, sql);
//   if(pweek == 8){
//    pstmt.setString(1, "%"+name+"%");
//   }else{
//    pstmt.setString(1, "%"+pweek+"%");//报纸的出版日期
//    pstmt.setString(2, "%"+name+"%");
//   }
   pstmt.execute();
   
   //按分页参数查询数据
   sql = "select p.*,if(invalidation=0 or invalidation is null,'生效','--') as invalidation2 from temppager t,pager p where t.pid=p.id and t.id>? and t.id<=? order by p.orderNo";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, pageSize*(pageIndex-1));
   pstmt.setInt(2, pageSize*pageIndex);
   pstmt.execute();
   
   rs = pstmt.getResultSet();
   pagers = new ArrayList();
   while (rs.next()){
    VOpager p = new VOpager();
    p.initUserRS(rs);
    pagers.add(p);
   }
   
   //清空临时表数据
//   pstmt = DB.prepareStmt(conn, "truncate table temppager");
//   pstmt.execute();
   
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(rs);
   DB.close(conn);
  }
  return pagers;
 }
 
 /**
  * 按供货商查询报纸
  */
 public List getByUserId(int uid,String pweek) {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  List pagers = new ArrayList();
  try{
   conn = DB.getConn();
   String sql="select * from pager p where p.userId=? and p.pweek like ? union " +
     "select p.* from pager p,provider pd where p.pweek like ? and pd.pagerId=p.id and pd.userId=? order by pd.orderNo";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, uid);
   pstmt.setString(2, "%"+pweek+"%");
   pstmt.setString(3, "%"+pweek+"%");
   pstmt.setInt(4, uid);
   pstmt.execute();
   rs = pstmt.getResultSet();
   while(rs.next()){
    Pager p = new Pager();
    p.initUserRS(rs);
    pagers.add(p);
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(rs);
   DB.close(conn);
  }
  return pagers;
 }
 /**
  * 总记录条数,用于分页
  */
 public int getTotal(int pweek,String searchStr){
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  int num = 0;
  String sql = "";
  if(searchStr.equals("")){
   if(pweek == 8){
    sql = "select count(*) from pager where pweek<>0";
   }else{
    sql = "select count(*) from pager where pweek like ?";
   }
  }else{
   if(pweek == 8){
    sql = "select count(*) from pager where name like ?";
   }else{
    sql = "select count(*) from pager where pweek like ? and name like ?";
   }
  }
  
  try{
   conn = DB.getConn();
   pstmt = DB.prepareStmt(conn, sql);
   if(searchStr.equals("")){
    if(pweek != 8){
     pstmt.setString(1, "%"+pweek+"%");
    }
   }else{
    if(pweek == 8){
     pstmt.setString(1, "%"+searchStr+"%");
    }else{
     pstmt.setString(1, "%"+pweek+"%");
     pstmt.setString(2, "%"+searchStr+"%");
    }
   }
   boolean bl = pstmt.execute();
   if (bl){
    rs = pstmt.getResultSet();
    if (rs.next()){
     num = rs.getInt(1);
    }
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(rs);
   DB.close(pstmt);
   DB.close(conn);
  }
  return num;
  
 }
 /**本类的isExistPager方法调用
  * 按名称查询报纸(精确查找)
  * @param name
  * @return
  */
 public Pager getByName(String name){
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  Pager p =null;
  try{
   conn = DB.getConn();
   String sql = "select * from pager where name=?";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setString(1, name);
   pstmt.execute();
   rs = pstmt.getResultSet();
   
   if(rs.next()){
    p = new Pager();
    p.initUserRS(rs);
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(rs);
   DB.close(conn);
  }
  return p;
 }
 /**
  * 判断报纸是否存在
  * 在添加报纸时被调用
  */
 public boolean isExistPager(Pager pager){
  boolean bl = false;
  Pager p = this.getByName(pager.getName());
  if(p != null){
   if(pager.getId()>0 && p.getId()==pager.getId()){
    bl=false;
   }else{
    bl = true;
   }
  }
  return bl;
 }
 /**
  * 该方法没有使用(就是不用分页一次性查出当天出版的所有报纸)
  * 当天出版的所有报纸
  * @param pageSize
  * @param pageIndex
  * @param pweek
  * @return
  */
 private List getList(int pageSize, int pageIndex,int pweek) {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  List pagers = null;
  String sql ="";
  
  try{
   conn = DB.getConn();
   //创建临时表
   sql = "CREATE temporary  TABLE temppager (id int(11) NOT NULL auto_increment,pid int,PRIMARY KEY  (id))";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.execute();
   
   //将pager表的id号插入到临时表中 
   if(pweek == 8){
    //pweek=8时查找所有报纸
    sql = "insert into temppager(pid) select id from pager where pweek<>0";
   }else{
    sql = "insert into temppager(pid) select id from pager where pweek like ?";
   }
   pstmt = DB.prepareStmt(conn,sql);
   if(pweek != 8){
    pstmt.setString(1, "%"+pweek+"%");//报纸的出版日期
   }
   pstmt.execute();
   
   //按分页参数查询数据
   sql = "select p.* from temppager t,pager p where t.pid=p.id and t.id>? and t.id<=? order by p.orderNo";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, pageSize*(pageIndex-1));
   pstmt.setInt(2, pageSize*pageIndex);
   pstmt.execute();
   
   rs = pstmt.getResultSet();
   pagers = new ArrayList();
   
   while (rs.next()){
    Pager p = new Pager();
    p.initUserRS(rs);
    pagers.add(p);
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(rs);
   DB.close(conn);
  }
  return pagers;
 }
 /**获得所有报纸名称和对应的供货商名pweek<>0
  * 修改排序显示用到
  *
  */
 public List getPagerNames() {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  List list = new ArrayList();
  try{
   conn = DB.getConn();
   String sql ="select p.id,p.name,u.name as userName from " +
     "(select p.id,pd.userId,p.orderNo,p.name from pager p left join (select distinct(pagerId),userId from provider group by pagerId) pd on p.id=pd.pagerId where p.pweek<>0) p " +
     "left join user u on p.userId=u.id order by p.orderNo";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.execute();
   rs = pstmt.getResultSet();
   String userName;
   while(rs.next()){
    Pager p = new Pager();
    p.setId(rs.getInt("id"));
    userName = rs.getString("userName");
    if(userName==null){
     p.setName(rs.getString("name"));
    }else{
     p.setName(userName+"_"+rs.getString("name"));
    }
    list.add(p);
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(rs);
   DB.close(conn);
  }
  return list;
 }
 /**
  * 只获得报纸名称及ID
  * 在添加自动加减数用到
  * @param pweek
  * @return
  */
 public List getNames() {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  List list = new ArrayList();
  try{
   conn = DB.getConn();
   String sql ="select id,name from pager where pweek <>0 order by orderNo";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.execute();
   rs = pstmt.getResultSet();
   while(rs.next()){
    Pager p = new Pager();
    p.setId(rs.getInt("id"));
    p.setName(rs.getString("name"));
    list.add(p);
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(rs);
   DB.close(conn);
  }
  return list;
 }
 /**获得所有报纸名称及ID   查询条件pweek<>0
  * 拼音头字母查报纸时用到。
  */
 public List getPagerNameByHZPY(String py,String rdoType){
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  List list = new ArrayList();
  String sql="";
  if(rdoType.equals("user")){
   sql ="select id,name from user where f_hzcode(name) like ? order by orderNo";
  }else{
   sql ="select id,name from pager where f_hzcode(name) like ? and pweek<>0 order by orderNo";
  }
  try{
   conn = DB.getConn();
   
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setString(1, py+"%");
   pstmt.execute();
   rs = pstmt.getResultSet();
   while(rs.next()){
    Pager p = new Pager();
    p.setId(rs.getInt("id"));
    p.setName(rs.getString("name"));
    list.add(p);
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(rs);
   DB.close(conn);
  }
  return list;
 }
 /**获得报纸名称
  * 设置临时出版星期用到
  */
 public List getPagerNames(int pweek) {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  List list = new ArrayList();
  try{
   conn = DB.getConn();
   String sql ="select id,name from pager where pweek like ? order by orderNo";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setString(1, "%"+pweek+"%");
   pstmt.execute();
   rs = pstmt.getResultSet();
   while(rs.next()){
    Pager p = new Pager();
    p.setId(rs.getInt("id"));
    p.setName(rs.getString("name"));
    list.add(p);
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(rs);
   DB.close(conn);
  }
  return list;
 }
 /**获得临时出版报纸名称
  * 设置临时出版星期用到
  */
 public List getPagerNamesByTempWeek(int tempWeek) {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt = null;
  List list = new ArrayList();
  try{
   conn = DB.getConn();
   String sql ="select id,name from pager where tempWeek like ? order by orderNo";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setString(1, "%"+tempWeek+"%");
   pstmt.execute();
   rs = pstmt.getResultSet();
   while(rs.next()){
    Pager p = new Pager();
    p.setId(rs.getInt("id"));
    p.setName(rs.getString("name"));
    list.add(p);
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(rs);
   DB.close(conn);
  }
  return list;
 }
 
 
 
 /**
  * 更新报纸排序
  */
 public boolean updateOrderNo(int[] pid) {
  Connection conn = null;
  PreparedStatement pstmt = null;
  boolean bl = false;
  try{
   conn = DB.getConn();
   DB.setAutoCommit(false, conn);//设置为手动提交事务
   String sql = "update pager set orderNo=? where id=?";
   pstmt = DB.prepareStmt(conn, sql);
   
   for(int i=0;i    pstmt.setString(1, String.valueOf(i));
    pstmt.setInt(2, pid[i]);
    pstmt.addBatch();
   }
   pstmt.executeBatch();
   DB.commit(conn);//提交事务
   DB.setAutoCommit(true, conn);
   bl = true;
  }catch(Exception ex){
   DB.roleBack(conn);
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(conn);
  }
  return bl;
 }
 /**
  * 更新供货商报纸排序
  */
 public boolean updateOrderNo(int uid,int[] pid) {
  Connection conn = null;
  PreparedStatement pstmt = null;
  boolean bl = false;
  try{
   conn = DB.getConn();
   DB.setAutoCommit(false, conn);//设置为手动提交事务
   String sql = "update provider set orderNo=? where pagerId=? and userId=?";
   pstmt = DB.prepareStmt(conn, sql);
   for(int i=0;i    pstmt.setString(1, String.valueOf(i));
    pstmt.setInt(2, pid[i]);
    pstmt.setInt(3, uid);
    pstmt.addBatch();
   }
   pstmt.executeBatch();
   DB.commit(conn);//提交事务
   DB.setAutoCommit(true, conn);
   bl = true;
  }catch(Exception ex){
   DB.roleBack(conn);
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(conn);
  }
  return bl;
 }
 /**
  * 更新临时出版星期
  */
 public boolean updateTempWeek(int tempWeek,int[] pid) {
  Connection conn = null;
  PreparedStatement pstmt = null;
  boolean bl = false;
  String sql ="";
  try{
   conn = DB.getConn();
   DB.setAutoCommit(false, conn);//设置为手动提交事务
   
//   sql = "update pager set tempWeek=0";
//   pstmt = DB.prepareStmt(conn, sql);
//   pstmt.execute();
   sql = "update pager set tempWeek=if(length(tempWeek)>1,concat(left(tempWeek,instr(tempWeek,?)-1),substring(tempWeek,instr(tempWeek,?)+1)),0) where tempWeek like ?";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, tempWeek);
   pstmt.setInt(2, tempWeek);
   pstmt.setString(3, "%"+tempWeek+"%");
   pstmt.execute();
   
   sql = "update pager set tempWeek=concat(tempWeek,?) where id=? and instr(tempWeek,?)=0";
   pstmt = DB.prepareStmt(conn, sql);
   for(int i=0;i    pstmt.setInt(1, tempWeek);
    pstmt.setInt(2, pid[i]);
    pstmt.setInt(3, tempWeek);
    pstmt.addBatch();
   }
   pstmt.executeBatch();
   DB.commit(conn);//提交事务
   DB.setAutoCommit(true, conn);
   bl = true;
  }catch(Exception ex){
   DB.roleBack(conn);
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(conn);
  }
  return bl;
 }
 /**
  * 获得供货商的报纸名称
  * @param uid
  * @return
  */
 public List getPagerNamesByProvider(int uid){
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  List list = new ArrayList();
  try{
   conn = DB.getConn();
   
   String sql = "select pg.id,pg.name from pager pg,provider pd where pd.userId=? and pd.pagerId=pg.id and pg.pweek<>0 order by pd.orderNo";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, uid);
   pstmt.execute();
   rs = pstmt.getResultSet();
   while(rs.next()){
    Pager p = new Pager();
    p.setId(rs.getInt("id"));
    p.setName(rs.getString("name"));
    list.add(p);
   }
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(rs);
   DB.close(pstmt);
   DB.close(conn);
  }
  return list;
 }
 /*
  * 更新所有报纸的自动加减数功能失效或生效 pweek<>0
  * value=1自动加减失效
  * value=0自动加减生效
  */
 public boolean updateInvalidation(int value) {
  Connection conn = null;
  PreparedStatement pstmt = null;
  boolean bl = false;
  try{
   conn = DB.getConn();
   String sql = "update pager set invalidation=? where pweek<>0";
   pstmt = DB.prepareStmt(conn, sql);
   pstmt.setInt(1, value);
   pstmt.execute();
   bl = true;
  }catch(Exception ex){
   ex.printStackTrace();
  }finally{
   DB.close(pstmt);
   DB.close(conn);
  }
  return bl;
 }
}
阅读(465) | 评论(0) | 转发(0) |
0

上一篇:AddOrDelNumberServlet

下一篇:DatabaseUtil

给主人留下些什么吧!~~