PagerDAOImpl
时间：2010-07-06 来源：mozhx
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<objects.length;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<objects.length;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<objects.length;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<objects.length;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<objects.length;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<VOpager> SearchByName(int pweek,int pageSize,int pageIndex,String name) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
List<VOpager> 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<VOpager>();
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<Pager> getList(int pageSize, int pageIndex,int pweek) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
List<Pager> 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<Pager>();
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<Pager> list = new ArrayList<Pager>();
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<Pager> list = new ArrayList<Pager>();
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<Pager> list = new ArrayList<Pager>();
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<Pager> list = new ArrayList<Pager>();
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<pid.length;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<pid.length;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<pid.length;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;
} }
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<objects.length;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<objects.length;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<objects.length;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<objects.length;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<objects.length;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<VOpager> SearchByName(int pweek,int pageSize,int pageIndex,String name) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
List<VOpager> 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<VOpager>();
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<Pager> getList(int pageSize, int pageIndex,int pweek) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
List<Pager> 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<Pager>();
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<Pager> list = new ArrayList<Pager>();
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<Pager> list = new ArrayList<Pager>();
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<Pager> list = new ArrayList<Pager>();
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<Pager> list = new ArrayList<Pager>();
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<pid.length;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<pid.length;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<pid.length;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;
} }
相关阅读 更多 +
