Li-NingShopping

package com.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.data.DatabaseUtil; import com.entity.MemberBean; public class MemberDao { private DatabaseUtil db= new DatabaseUtil(); private int pageSize=8; private int sumPages; private int sumRows; /** * 封装List * @param rs * @return List集合 */ private List<MemberBean> fileList(ResultSet rs) { List<MemberBean> list = new ArrayList<MemberBean>(); try { while(rs.next()){ MemberBean memberBean = new MemberBean(); memberBean.setId(rs.getInt(1)); memberBean.setUsername(rs.getString(2)); memberBean.setPassword(rs.getString(3)); memberBean.setSex(rs.getString(4)); memberBean.setEmail(rs.getString(5)); memberBean.setBirthday(rs.getString(6)); memberBean.setAddress(rs.getString(7)); memberBean.setIntegral(rs.getInt(8)); memberBean.setLevelid(rs.getInt(9)); memberBean.setQuestion(rs.getString(10)); memberBean.setAnswer(rs.getString(11)); memberBean.setRegtime(rs.getString(12)); memberBean.setStatus(rs.getInt(13)); list.add(memberBean); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ db.release(rs); } return list.size()==0?null:list; } public List<MemberBean> getMemberByName(String name){ String sql="select * from dbo.memberinfo where username like '%"+name+"%' or address like '%"+name+"%'"; ResultSet rs=db.query(sql); return fileList(rs); } /** * 分页显示 * @param currentPage 当前页数 * @return List集合 */ public List<MemberBean> getPages(int currentPage){ String sql="select top %d * from memberinfo where id not in(select top %d id from memberinfo)"; ResultSet rs = db.selectByPage(sql, pageSize, currentPage); return this.fileList(rs); } /** * 重载分页显示 * @param currentPage 当前页数 * @param name 条件 * @return */ public List<MemberBean> getpages(int currentPage,String name){ String sql="select top {0} * from memberinfo where id not in(select top {1} id from memberinfo where username like ''%"+name+"%'' or address like ''%"+name+"%'') and username like ''%"+name+"%'' or address like ''%"+name+"%''"; ResultSet rs = db.selectByPageIsMessage(sql, pageSize, currentPage); return this.fileList(rs); } /** * 获取总行数 总页数 */ public void getSumRow(String name){ // String sql="select count(*) from memberinfo where username like ''%张三%'' or address like ''s''"; String sql = "select count(*) from memberinfo where username like '%"+name+"%' or address like '%"+name+"%'"; //总行数 System.out.println(sql); sumRows = db.calcRows(sql); //总页数 /*if(totalRows % pageSize == 0) totalPages = totalRows / pageSize; else { totalPages = totalRows / pageSize + 1; }*/ sumPages = (sumRows + pageSize - 1) / pageSize; } /** * 获取总行数 总页数 */ public void getSumRow(){ String sql = "select count(*) from memberinfo"; //总行数 sumRows = db.calcRows(sql); //总页数 /*if(totalRows % pageSize == 0) totalPages = totalRows / pageSize; else { totalPages = totalRows / pageSize + 1; }*/ sumPages = (sumRows + pageSize - 1) / pageSize; } /** * 查询所有会员 * @return */ public List<MemberBean> selectAll(){ String sql="select * from memberinfo"; ResultSet rs=db.query(sql); return fileList(rs); } /** * 修改用户信息 * @param m * @return */ public int update(MemberBean m){ String sql="update memberinfo set username=?, password=?, sex=?, email=?, birthday=?, address=?, integral=?, levelid=?, question=?, answer=?, regtime=? where id=?"; return db.update(sql,m.getUsername(),m.getPassword(),m.getSex(),m.getEmail(),m.getBirthday(),m.getAddress(),m.getIntegral(),m.getLevelid(),m.getQuestion(),m.getAnswer(),m.getRegtime(),m.getId()); } /** * 根据会员ID获取会员姓名 * @param id * @return */ public String getUserNameById(int id){ String sql="select username from dbo.memberinfo where id =?"; String name=null; ResultSet rs=db.query(sql,id); try { if(rs.next()){ name=rs.getString(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ db.release(rs); } return name; } /** * 删除用户 * @param m * @return */ public int delete(int id){ String sql="delete memberinfo where id=?"; return db.update(sql,id); } /** * 根据ID修改用户状态 * @param status * @param id * @return */ public int updateStatusById(int status,int id){ String sql="update dbo.memberinfo set status=? where id=?"; return db.update(sql, status,id); } /** * 发货后 根据会用买的东西 加积分 * @param score * @param name * @return */ public int updateIntegralByUserName(int score,String name){ String sql="update dbo.memberinfo set integral=(integral+?) where username=?"; return db.update(sql, score,name); } /** * 添加用户 * @param m * @return */ public int insert(MemberBean m){ String sql="insert into memberinfo values(?,?,?,?,?,?,0,1,?,?,getdate(),0)"; return db.update(sql,m.getUsername(),m.getPassword(),m.getSex(),m.getEmail(),m.getBirthday(),m.getAddress(),m.getQuestion(),m.getAnswer()); } /** * 添加会员信息 * @param member * @return */ public int addMember(MemberBean member){ String sql = "insert into memberinfo values(?,?,?,?,?,?,500,1,?,?," + "getdate(),default)"; return db.update(sql,member.getUsername(),member.getPassword(),member.getSex(),member.getEmail(), member.getBirthday(),member.getAddress(),member.getQuestion(),member.getAnswer()); } /** * 根据用户名和密码判断用户是否存在 * @param name * @param pwd * @return */ public MemberBean selectMemberByName(String name,String pwd){ String sql = "select * from memberinfo where username = ? and password = ?"; ResultSet rs = db.query(sql, name,pwd); MemberBean member = null; try { if(rs.next()){ member = new MemberBean(rs.getInt(1),rs.getString(2),rs.getString(3), rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8), rs.getInt(9),rs.getString(10),rs.getString(11),rs.getString(12),rs.getInt(13)); } } catch (SQLException e) { e.printStackTrace(); } return member; } /** * 根据Id修改个人密码 * @param id * @param newPwd * @return */ public int updatePwd(int id,String newPwd,String oldPwd){ String sql = "update memberinfo set password = ? where id = ? and password = ?"; return db.update(sql,newPwd,id,oldPwd); } /** * 根据id修改个人信息 * @param member * @return */ public int updateMemberInfo(MemberBean member){ String sql = "update memberinfo set birthday = ? , sex = ? , address = ?,email = ? where id = ?"; return db.update(sql,member.getBirthday(),member.getSex(),member.getAddress(), member.getEmail(),member.getId()); } /** * 根据id获取会员信息 * @param id * @return */ public MemberBean selectById(int id){ String sql = "select * from memberinfo where id = ? "; ResultSet rs = db.query(sql,id); MemberBean member = null; try { if(rs.next()){ member = new MemberBean(rs.getInt(1),rs.getString(2),rs.getString(3), rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7), rs.getInt(8),rs.getInt(9),rs.getString(10),rs.getString(11), rs.getString(12),rs.getInt(13)); } } catch (SQLException e) { e.printStackTrace(); }finally{ db.release(rs); } return member; } /** * 根据用户名id和用户名查询密保问题 * @param uname * @return */ public String selectQuestion(String uname){ String sql = "select question from memberinfo where