dataosir / smbms Goto Github PK

import java.math.BigDecimal;
import java.util.Date;
public class Bill {
   private Integer id;   //id 
   private String billCode; //账单编码 
   private String productName; //商品名称 
   private String productDesc; //商品描述 
   private String productUnit; //商品单位
   private BigDecimal productCount; //商品数量 
   private BigDecimal totalPrice; //总金额
   private Integer isPayment; //是否支付 
   private Integer providerId; //供应商ID 
   private Integer createdBy; //创建者
   private Date creationDate; //创建时间
   private Integer modifyBy; //更新者
   private Date modifyDate;//更新时间
   
   private String providerName;//供应商名称
   
   
   public String getProviderName() {
   	return providerName;
   }
   public void setProviderName(String providerName) {
   	this.providerName = providerName;
   }
   public Integer getId() {
   	return id;
   }
   public void setId(Integer id) {
   	this.id = id;
   }
   public String getBillCode() {
   	return billCode;
   }
   public void setBillCode(String billCode) {
   	this.billCode = billCode;
   }
   public String getProductName() {
   	return productName;
   }
   public void setProductName(String productName) {
   	this.productName = productName;
   }
   public String getProductDesc() {
   	return productDesc;
   }
   public void setProductDesc(String productDesc) {
   	this.productDesc = productDesc;
   }
   public String getProductUnit() {
   	return productUnit;
   }
   public void setProductUnit(String productUnit) {
   	this.productUnit = productUnit;
   }
   public BigDecimal getProductCount() {
   	return productCount;
   }
   public void setProductCount(BigDecimal productCount) {
   	this.productCount = productCount;
   }
   public BigDecimal getTotalPrice() {
   	return totalPrice;
   }
   public void setTotalPrice(BigDecimal totalPrice) {
   	this.totalPrice = totalPrice;
   }
   public Integer getIsPayment() {
   	return isPayment;
   }
   public void setIsPayment(Integer isPayment) {
   	this.isPayment = isPayment;
   }
   
   public Integer getProviderId() {
   	return providerId;
   }
   public void setProviderId(Integer providerId) {
   	this.providerId = providerId;
   }
   public Integer getCreatedBy() {
   	return createdBy;
   }
   public void setCreatedBy(Integer createdBy) {
   	this.createdBy = createdBy;
   }
   public Date getCreationDate() {
   	return creationDate;
   }
   public void setCreationDate(Date creationDate) {
   	this.creationDate = creationDate;
   }
   public Integer getModifyBy() {
   	return modifyBy;
   }
   public void setModifyBy(Integer modifyBy) {
   	this.modifyBy = modifyBy;
   }
   public Date getModifyDate() {
   	return modifyDate;
   }
   public void setModifyDate(Date modifyDate) {
   	this.modifyDate = modifyDate;
   }
}

import java.util.Date;
public class Role {	
   private Integer id;   //id
   private String roleCode; //角色编码
   private String roleName; //角色名称
   private Integer createdBy; //创建者
   private Date creationDate; //创建时间
   private Integer modifyBy; //更新者
   private Date modifyDate;//更新时间
   
   public Integer getId() {
   	return id;
   }
   public void setId(Integer id) {
   	this.id = id;
   }
   public String getRoleCode() {
   	return roleCode;
   }
   public void setRoleCode(String roleCode) {
   	this.roleCode = roleCode;
   }
   public String getRoleName() {
   	return roleName;
   }
   public void setRoleName(String roleName) {
   	this.roleName = roleName;
   }
   public Integer getCreatedBy() {
   	return createdBy;
   }
   public void setCreatedBy(Integer createdBy) {
   	this.createdBy = createdBy;
   }
   public Date getCreationDate() {
   	return creationDate;
   }
   public void setCreationDate(Date creationDate) {
   	this.creationDate = creationDate;
   }
   public Integer getModifyBy() {
   	return modifyBy;
   }
   public void setModifyBy(Integer modifyBy) {
   	this.modifyBy = modifyBy;
   }
   public Date getModifyDate() {
   	return modifyDate;
   }
   public void setModifyDate(Date modifyDate) {
   	this.modifyDate = modifyDate;
   }
}

import java.util.Date;
public class Provider {
   private Integer id;   //id
   private String proCode; //供应商编码
   private String proName; //供应商名称
   private String proDesc; //供应商描述
   private String proContact; //供应商联系人
   private String proPhone; //供应商电话
   private String proAddress; //供应商地址
   private String proFax; //供应商传真
   private Integer createdBy; //创建者
   private Date creationDate; //创建时间
   private Integer modifyBy; //更新者
   private Date modifyDate;//更新时间

   public Integer getId() {
   	return id;
   }
   public void setId(Integer id) {
   	this.id = id;
   }
   public String getProCode() {
   	return proCode;
   }
   public void setProCode(String proCode) {
   	this.proCode = proCode;
   }
   public String getProName() {
   	return proName;
   }
   public void setProName(String proName) {
   	this.proName = proName;
   }
   public String getProDesc() {
   	return proDesc;
   }
   public void setProDesc(String proDesc) {
   	this.proDesc = proDesc;
   }
   public String getProContact() {
   	return proContact;
   }
   public void setProContact(String proContact) {
   	this.proContact = proContact;
   }
   public String getProPhone() {
   	return proPhone;
   }
   public void setProPhone(String proPhone) {
   	this.proPhone = proPhone;
   }
   public String getProAddress() {
   	return proAddress;
   }
   public void setProAddress(String proAddress) {
   	this.proAddress = proAddress;
   }
   public String getProFax() {
   	return proFax;
   }
   public void setProFax(String proFax) {
   	this.proFax = proFax;
   }
   public Integer getCreatedBy() {
   	return createdBy;
   }
   public void setCreatedBy(Integer createdBy) {
   	this.createdBy = createdBy;
   }
   public Date getCreationDate() {
   	return creationDate;
   }
   public void setCreationDate(Date creationDate) {
   	this.creationDate = creationDate;
   }
   public Integer getModifyBy() {
   	return modifyBy;
   }
   public void setModifyBy(Integer modifyBy) {
   	this.modifyBy = modifyBy;
   }
   public Date getModifyDate() {
   	return modifyDate;
   }
   public void setModifyDate(Date modifyDate) {
   	this.modifyDate = modifyDate;
   }
}

import java.util.Date;
public class User {
   private Integer id; //id 
   private String userCode; //用户编码
   private String userName; //用户名称
   private String userPassword; //用户密码
   private Integer gender;  //性别
   private Date birthday;  //出生日期
   private String phone;   //电话
   private String address; //地址
   private Integer userRole;    //用户角色
   private Integer createdBy;   //创建者
   private Date creationDate; //创建时间
   private Integer modifyBy;     //更新者
   private Date modifyDate;   //更新时间
   
   private Integer age;//年龄
   private String userRoleName;    //用户角色名称


   public String getUserRoleName() {
   	return userRoleName;
   }
   public void setUserRoleName(String userRoleName) {
   	this.userRoleName = userRoleName;
   }
   public Integer getAge() {
   	Date date = new Date();
   	Integer age = date.getYear()-birthday.getYear();
   	return age;
   }
   public Integer getId() {
   	return id;
   }
   public void setId(Integer id) {
   	this.id = id;
   }
   public String getUserCode() {
   	return userCode;
   }
   public void setUserCode(String userCode) {
   	this.userCode = userCode;
   }
   public String getUserName() {
   	return userName;
   }
   public void setUserName(String userName) {
   	this.userName = userName;
   }
   public String getUserPassword() {
   	return userPassword;
   }
   public void setUserPassword(String userPassword) {
   	this.userPassword = userPassword;
   }
   public Integer getGender() {
   	return gender;
   }
   public void setGender(Integer gender) {
   	this.gender = gender;
   }
   public Date getBirthday() {
   	return birthday;
   }
   public void setBirthday(Date birthday) {
   	this.birthday = birthday;
   }
   public String getPhone() {
   	return phone;
   }
   public void setPhone(String phone) {
   	this.phone = phone;
   }
   public String getAddress() {
   	return address;
   }
   public void setAddress(String address) {
   	this.address = address;
   }
   public Integer getUserRole() {
   	return userRole;
   }
   public void setUserRole(Integer userRole) {
   	this.userRole = userRole;
   }
   public Integer getCreatedBy() {
   	return createdBy;
   }
   public void setCreatedBy(Integer createdBy) {
   	this.createdBy = createdBy;
   }
   public Date getCreationDate() {
   	return creationDate;
   }
   public void setCreationDate(Date creationDate) {
   	this.creationDate = creationDate;
   }
   public Integer getModifyBy() {
   	return modifyBy;
   }
   public void setModifyBy(Integer modifyBy) {
   	this.modifyBy = modifyBy;
   }
   public Date getModifyDate() {
   	return modifyDate;
   }
   public void setModifyDate(Date modifyDate) {
   	this.modifyDate = modifyDate;
   }

}

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//操作数据库的公共类
     public class BaseDao {     
         private static String driver;
         private static String url;
         private static String username;
         private static String password;
     
         //静态代码块，类加载的时候就初始化了
         static {
             Properties properties = new Properties();
             //通过类加载器读取对应的资源
             InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
     
             try {
                 properties.load(is);
             } catch (IOException e) {
                 e.printStackTrace();
             }
     
             driver = properties.getProperty("driver");
             url = properties.getProperty("url");
             username = properties.getProperty("username");
             password = properties.getProperty("password");
         }
     
         //获取数据库的链接
         public static Connection getConnection(){
             Connection connection = null;
             try {
                 Class.forName(driver);
                 connection = DriverManager.getConnection(url, username, password);
             } catch (Exception e) {
                 e.printStackTrace();
             }
             return connection;
         }
     
         //编写查询公共方法
         public static ResultSet execute(Connection connection,String sql,Object[] params,ResultSet resultSet,PreparedStatement preparedStatement) throws SQLException {
             //预编译的sql，在后面直接执行就可以了
             preparedStatement = connection.prepareStatement(sql);
     
             for (int i = 0; i < params.length; i++) {
                 //setObject,占位符从1开始，但是我们的数组是从0开始！
                 preparedStatement.setObject(i+1,params[i]);
             }
     
             resultSet = preparedStatement.executeQuery();
             return resultSet;
         }
     
     
         //编写增删改公共方法
         public static int execute(Connection connection,String sql,Object[] params,PreparedStatement preparedStatement) throws SQLException {
             preparedStatement = connection.prepareStatement(sql);
     
             for (int i = 0; i < params.length; i++) {
                 //setObject,占位符从1开始，但是我们的数组是从0开始！
                 preparedStatement.setObject(i+1,params[i]);
             }
     
             int updateRows = preparedStatement.executeUpdate();
             return updateRows;
         }
     
     
         //释放资源
         public static boolean closeResource(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet){
             boolean flag = true;
     
             if (resultSet!=null){
                 try {
                     resultSet.close();
                     //GC回收
                     resultSet = null;
                 } catch (SQLException e) {
                     e.printStackTrace();
                     flag = false;
                 }
             }
     
             if (preparedStatement!=null){
                 try {
                     preparedStatement.close();
                     //GC回收
                     preparedStatement = null;
                 } catch (SQLException e) {
                     e.printStackTrace();
                     flag = false;
                 }
             }
     
             if (connection!=null){
                 try {
                     connection.close();
                     //GC回收
                     connection = null;
                 } catch (SQLException e) {
                     e.printStackTrace();
                     flag = false;
                 }
             }
             return flag;
         }
     }

import javax.servlet.*;
import java.io.IOException;

public class CharacterEncodingFilter implements Filter {

    public void init(FilterConfig filterConfig) throws ServletException {
    }
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        chain.doFilter(request,response);
    }
    public void destroy() {

    }
}

import com.mario.pojo.User;

import java.sql.Connection;
import java.sql.SQLException;

public interface UserDao {
    public User getLoginUser(Connection connection, String userCode, String userPassword) throws SQLException;

}

import com.mario.dao.BaseDao;
import com.mario.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDaoImpl implements UserDao {
    //得到要登录的用户
    public User getLoginUser(Connection connection, String userCode,String userPassword) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        User user = null;

        if (connection!=null){
            String sql = "select * from smbms_user where userCode=?";
            Object[] params = {userCode};
            //System.out.println(userPassword);
            rs = BaseDao.execute(connection,sql,params,rs,pstm);
            if (rs.next()){
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setCreationDate(rs.getTimestamp("creationDate"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setModifyDate(rs.getTimestamp("modifyDate"));
            }
            BaseDao.closeResource(null,pstm,rs);
            if (!user.getUserPassword().equals(userPassword))
                user=null;
        }
        return user;
    }
}

import com.mario.pojo.User;

public interface UserService {
    //用户登录
    public User login(String userCode, String password);
}

import com.mario.dao.BaseDao;
import com.mario.dao.user.UserDao;
import com.mario.dao.user.UserDaoImpl;
import com.mario.pojo.User;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;

public class UserServiceImpl implements UserService {

    //业务层都会调用dao层，所以我们要引入Dao层；
    private UserDao userDao;
    public UserServiceImpl(){
        userDao = new UserDaoImpl();
    }


    public User login(String userCode, String password) {
        Connection connection = null;
        User user = null;

        try {
            connection = BaseDao.getConnection();
            //通过业务层调用对应的具体的数据库操作
            user = userDao.getLoginUser(connection, userCode,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return user;
    }
//    @Test
//    public void test(){
//        UserServiceImpl userService = new UserServiceImpl();
//        User login = userService.login("test", "111");
//        System.out.println(login.getUserPassword());
//    }

}

import com.mario.pojo.User;
import com.mario.service.user.UserService;
import com.mario.service.user.UserServiceImpl;
import com.mario.util.Constants;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class LoginServlet extends HttpServlet {
    //Servlet:控制层，调用业务层代码

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        System.out.println("LoginServlet--start....");
        //获取用户名和密码
        String userCode = req.getParameter("userCode");
        String userPassword = req.getParameter("userPassword");
        //和数据库中的密码进行对比，调用业务层；
        UserService userService = new UserServiceImpl();
        User user = userService.login(userCode, userPassword);  //这里已经把登录的人给查出来了
        System.out.println(userCode);
        System.out.println(userPassword);
        if (user!=null){ //查有此人，可以登录
            //将用户的信息放到Session中;
            req.getSession().setAttribute(Constants.USER_SESSION,user);
            //跳转到主页重定向
            resp.sendRedirect("jsp/frame.jsp");
        }else {//查无此人，无法登录
            //转发回登录页面，顺带提示它，用户名或者密码错误；
            req.setAttribute("error","用户名或者密码不正确");
            req.getRequestDispatcher("login.jsp").forward(req,resp);
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}

import com.mario.util.Constants;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class LogoutServlet  extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //移除用户的session
        req.getSession().removeAttribute(Constants.USER_SESSION);
        resp.sendRedirect(req.getContextPath()+"/login.jsp");//返回登录页面
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}
123456789101112131415161718192021

import com.mario.pojo.User;
import com.mario.util.Constants;

import javax.servlet.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class SysFilter implements Filter {
    public void init(FilterConfig filterConfig) throws ServletException {
    }

    public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws IOException, ServletException {
        HttpServletRequest request = (HttpServletRequest) req;
        HttpServletResponse response = (HttpServletResponse) resp;

        //过滤器，从Session中获取用户，
        User user = (User) request.getSession().getAttribute(Constants.USER_SESSION);

        if (user==null){ //已经被移除或者注销了，或者未登录
            response.sendRedirect("/smbms/error.jsp");
        }else {
            chain.doFilter(req,resp);
        }
    }

    public void destroy() {

    }
}

//修改当前用户密码
//增删改都会影响数据库的变化，所以是返回int类型，说明有几行受到了影响
public int updatePwd(Connection connection,int id,String userPassword)throws SQLException;

//修改当前用户密码
//增删改都会影响数据库的变化，所以是返回int类型，说明有几行受到了影响
public int updatePwd(Connection connection, int id, String userPassword) throws SQLException {
    int updateRows=0;
    PreparedStatement pstm = null;
    if(connection!=null){
        String Sql="UPDATE `smbms_user` SET `userPassword`=? WHERE `id`=? ";
        Object []params={userPassword,id};
        updateRows=BaseDao.execute(connection,Sql,params,pstm);
    }
    BaseDao.closeResource(null,pstm,null);
    return updateRows;
}

//根据用户id修改密码
public boolean updatePwd(int id,String password);

//根据用户id修改密码
//通过返回的参数flag判断是否修改成功
public boolean updatePwd(int id, String password) {
    boolean flag = false;
    Connection connection = null;
    try{
        connection = BaseDao.getConnection();
        if(userDao.updatePwd(connection,id,password) > 0)
            flag = true;
    }catch (Exception e) {
        e.printStackTrace();
    }finally{
        BaseDao.closeResource(connection, null, null);
    }
    return flag;
}

//实现Servlet复用，实现复用需要提取出方法，然后在doGet函数中调用即可
        String method = req.getParameter("method");
        if(method.equals("savepwd") && method!=null){
            this.updatePwd(req,resp);
        }


//在doGet外写复用方法
 public void updatePwd(HttpServletRequest req, HttpServletResponse resp){
        //从session中获得用户id,这里的attribute包括了用户的所用信息
        Object attribute = req.getSession().getAttribute(Constants.USER_SESSION);
        //获得新密码
        String newpassword = req.getParameter("newpassword");
        boolean flag= false;
        //判断是否有这个用户是否存在，以及新密码不为空
        if(attribute!=null && !StringUtils.isNullOrEmpty(newpassword)){
            UserService userService = new UserServiceImpl();
            flag = userService.updatePwd(((User) attribute).getId(), newpassword);
            if (flag) {
                req.setAttribute(Constants.SYS_MESSAGE,"修改密码成功，请退出后重新登录");
                //密码修改成功后移除当前session
                req.getSession().removeAttribute(Constants.USER_SESSION);
            }else{
                req.setAttribute(Constants.SYS_MESSAGE,"密码修改失败请重新输入");
            }
        }else{
            req.setAttribute(Constants.SYS_MESSAGE,"新密码设置错误请重新输入");
        }
        try {
            req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp);
        } catch (ServletException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

else if(method.equals("pwdmodify") && method!=null){//收到前端发来的请求，根据不同的method值来启动不同的Servlet应对
            this.modifyPwd(req,resp);
        }

//在下面继续写
//验证旧密码，session中可以获得旧密码，不需要重复去数据库中寻找
    public void modifyPwd(HttpServletRequest req, HttpServletResponse resp){
        //从session中获得用户的旧密码,这里的attribute包括了用户的所用信息
        Object attribute = req.getSession().getAttribute(Constants.USER_SESSION);
        //从前端输入的页面中获得输入的旧密码
        String oldpassword = req.getParameter("oldpassword");
        //万能的Map
        Map<String, String> resultMap = new HashMap<String,String>();
        if (attribute==null){//取到的session为空，意味着session过期了
            resultMap.put("result","sessionerror");
        }else if (StringUtils.isNullOrEmpty(oldpassword)){//如果输入的旧密码为空
            resultMap.put("result","sessionerror");
        }else{//session不为空，输入的旧密码也不为空，则取出当前旧密码与之比较
            String userPassword = ((User) attribute).getUserPassword();
            if(oldpassword.equals(userPassword)){
                resultMap.put("result","true");
            }else {
                resultMap.put("result","false");
            }
        }
        try {
            resp.setContentType("application/json");
            PrintWriter out = resp.getWriter();
            out.write(JSONArray.toJSONString(resultMap));
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

public int getUserCount(Connection connection, String userName, int userRole)throws Exception;

//根据用户输入的名字或者角色id来查询计算用户数量
    public int getUserCount(Connection connection, String userName, int userRole) throws Exception {
        int count=0;
        PreparedStatement pstm = null;
        ResultSet rs=null;
        if (connection!=null) {
            StringBuffer sql=new StringBuffer();
            sql.append("SELECT COUNT(*) AS count FROM `smbms_user` u,`smbms_role` r WHERE u.`userRole`=r.`id`");
            ArrayList<Object> list = new ArrayList<Object>();//存放可能会放进sql里的参数，就是用来替代?的params
            if(!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.username like ?");
                list.add("%"+userName+"%");//模糊查询，index:0
            }
            if(userRole>0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);//index:1
            }
            Object[] params = list.toArray();//转换成数组
            System.out.println("当前的sql语句为------------>"+sql);
            rs = BaseDao.execute(connection, sql.toString(), params, rs, pstm);
            if(rs.next()){
                count=rs.getInt("count");

            }
            BaseDao.closeResource(null,pstm,rs);

        }
        return count;
    }

//根据条件（用户的查询输入）查询用户记录数
public int getUserCount(String queryUserName, int queryUserRole);

//根据条件（用户的查询输入）查询用户记录数
public int getUserCount(String queryUserName, int queryUserRole) {
    int count=0;
    Connection connection=null;
    try {
        connection = BaseDao.getConnection();
        count = userDao.getUserCount(connection, queryUserName, queryUserRole);
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        BaseDao.closeResource(connection, null, null);
    }
    return count;
}

//通过用户输入的条件查询用户列表
public List<User> getUserList(Connection connection,String userName,int userRole,int currentPageNo,int pageSize) throws  Exception;

//通过用户输入的条件查询用户列表
    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception {
        List<User> userList = new ArrayList<User>();
        PreparedStatement pstm=null;
        ResultSet rs=null;
        if(connection!=null){
            StringBuffer sql = new StringBuffer();
            sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
            List<Object> list = new ArrayList<Object>();
            if(!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userName like ?");
                list.add("%"+userName+"%");
            }
            if(userRole > 0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }
          	//实现分页显示
            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo = (currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);

            Object[] params = list.toArray();
            System.out.println("sql ----> " + sql.toString());
            rs = BaseDao.execute(connection,sql.toString(),params,rs,pstm);
            while(rs.next()){
                User _user = new User();
                _user.setId(rs.getInt("id"));
                _user.setUserCode(rs.getString("userCode"));
                _user.setUserName(rs.getString("userName"));
                _user.setGender(rs.getInt("gender"));
                _user.setBirthday(rs.getDate("birthday"));
                _user.setPhone(rs.getString("phone"));
                _user.setUserRole(rs.getInt("userRole"));
                _user.setUserRoleName(rs.getString("userRoleName"));
                userList.add(_user);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return userList;
    }

//根据条件查询用户列表
    public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);

 public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) {
        // TODO Auto-generated method stub
        Connection connection = null;
        List<User> userList = null;
        System.out.println("queryUserName ---- > " + queryUserName);
        System.out.println("queryUserRole ---- > " + queryUserRole);
        System.out.println("currentPageNo ---- > " + currentPageNo);
        System.out.println("pageSize ---- > " + pageSize);
        try {
            connection = BaseDao.getConnection();
            userList = userDao.getUserList(connection, queryUserName,queryUserRole,currentPageNo,pageSize);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            BaseDao.closeResource(connection, null, null);
        }
        return userList;
    }

import com.mario.pojo.Role;

import java.sql.Connection;
import java.util.List;

public interface RoleDao {
    //获取角色表
    public List<Role> getRoleList(Connection connection) throws Exception;
}

import com.mario.dao.BaseDao;
import com.mario.pojo.Role;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class RoleDaoImpl implements RoleDao {
    public List<Role> getRoleList(Connection connection) throws Exception {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        List<Role> roleList = new ArrayList<Role>();
        if(connection!=null){
            String sql="SELECT * FROM `smbms_role`";
            Object[] params={};
            rs = BaseDao.execute(connection, sql, params, rs, pstm);
            while(rs.next()){
                Role role = new Role();
                role.setId(rs.getInt("id"));
                role.setRoleCode(rs.getString("roleCode"));
                role.setRoleName(rs.getString("roleName"));
                roleList.add(role);
            }
        }
        BaseDao.closeResource(null,pstm,rs);
        return roleList;
    }
}

import com.mario.pojo.Role;

import java.util.List;

public interface RoleService {

    //得到用户角色表
    public List<Role> getRoleList();
}

import com.mario.dao.BaseDao;
import com.mario.dao.role.RoleDao;
import com.mario.dao.role.RoleDaoImpl;
import com.mario.pojo.Role;

import java.sql.Connection;
import java.util.List;

public class RoleServiceImpl  implements RoleService{
    //业务层都会调用dao层，所以我们要引入Dao层；
    private RoleDao roleDao;
    public RoleServiceImpl(){ roleDao=new RoleDaoImpl();}
    //方法
    //Service层一般步骤都是获取连接执行操作，返回结果就行
    public List<Role> getRoleList() {
        Connection connection=null;
        List<Role> roleList=null;
        try {
            connection= BaseDao.getConnection();
            roleList = roleDao.getRoleList(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return roleList;
    }
}

else if (method.equals("query") && method!=null){
            this.query(req,resp);
        }



    //用户管理模块页面查询
    //怎么处理
    public  void query(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
        //接收前端传来的参数
        String queryUserName = req.getParameter("queryname");
        String temp = req.getParameter("queryUserRole");//从前端传回来的用户角色码不知是否为空或者是有效角色码，所以暂存起来
        String pageIndex = req.getParameter("pageIndex");
        int queryUserRole=0;
        //先设置一个默认的用户角色码，若temp为空，则将这个传进sql语句中，这是真正放进sql语句中的角色码
        /*
            if(userRole > 0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }
            这句便不会被执行
         */
        //通过UserServiceImpl得到用户列表,用户数
        UserServiceImpl userService = new UserServiceImpl();
        //通过RoleServiceImpl得到角色表
        RoleService roleService = new RoleServiceImpl();
        List<User> userList=null;//用来存储用户列表
        List<Role> roleList=null;//用来存储角色表
        //设置每页显示的页面容量
        int pageSize=Constants.pageSize;
        //设置当前的默认页码
        int currentPageNo=1;
        //输出控制台，显示参数的当前值
        System.out.println("queryUserName servlet--------"+queryUserName);
        System.out.println("queryUserRole servlet--------"+queryUserRole);
        System.out.println("query pageIndex--------- > " + pageIndex);

        //前端传来的参数若不符合查询sql语句，即如果用户不进行设置，值为空会影响sql查询，需要给它们进行一些约束
        if(queryUserName==null){//这里为空，说明用户没有输入要查询的用户名，则sql语句传值为""，%%，会查询所有记录
            queryUserName="";
        }
        if(temp!=null && !temp.equals("")){
            //不为空，说明前端有传来的用户所设置的userCode，更新真正的角色码
            queryUserRole=Integer.parseInt(temp);//强制转换，前端传递的参数都是默认字符串，要转成int类型
        }

        if(pageIndex!=null){//说明当前用户有进行设置跳转页面
            currentPageNo=Integer.valueOf(pageIndex);
        }

        //有了用户名和用户角色后可以开始查询了，所以需要显示当前查询到的总记录条数
        int totalCount = userService.getUserCount(queryUserName, queryUserRole);
        //根据总记录条数以及当前每页的页面容量可以算出，一共有几页，以及最后一页的显示条数
        PageSupport pageSupport = new PageSupport();
        pageSupport.setCurrentPageNo(currentPageNo);
        pageSupport.setPageSize(pageSize);
        pageSupport.setTotalCount(totalCount);
        //可显示的总页数
        int totalPageCount=pageSupport.getTotalPageCount();

        //约束首位页，即防止用户输入的页面索引小于1或者大于总页数
        if(currentPageNo<1){
            currentPageNo=1;
        }else if(currentPageNo>totalPageCount){
            currentPageNo=totalPageCount;
        }
        //有了，待查询条件，当前页码，以及每页的页面容量后，就可以给出每页的具体显示情况了
        userList = userService.getUserList(queryUserName, queryUserRole, currentPageNo, pageSize);
        roleList = roleService.getRoleList();
        //得到了用户表与角色表以及各种经过处理后的参数，都存进req中
        req.setAttribute("userList",userList);
        req.setAttribute("roleList",roleList);
        req.setAttribute("queryUserName", queryUserName);
        req.setAttribute("queryUserRole", queryUserRole);
        req.setAttribute("totalPageCount", totalPageCount);
        req.setAttribute("totalCount", totalCount);
        req.setAttribute("currentPageNo", currentPageNo);
        //将所得到的的所有req参数送回给前端
        req.getRequestDispatcher("userlist.jsp").forward(req,resp);

    }

    //增加用户信息
    public  int add(Connection connection,User user) throws Exception;

    //通过用户id删除用户信息
    public int deleteUserById(Connection connection, Integer delId)throws Exception;

    //通过userId查看当前用户信息
    public User getUserById(Connection connection, String id)throws Exception;
    //修改用户信息
    public int modify(Connection connection, User user)throws Exception;

    //增加用户信息
    public int add(Connection connection, User user) throws Exception {
        PreparedStatement pstm=null;
        int updateNum=0;
        if(connection!=null) {
            String sql = "insert into smbms_user (userCode,userName,userPassword," +
                    "userRole,gender,birthday,phone,address,creationDate,createdBy) " +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            Object[] params = {user.getUserCode(), user.getUserName(), user.getUserPassword(),
                    user.getUserRole(), user.getGender(), user.getBirthday(),
                    user.getPhone(), user.getAddress(), user.getCreationDate(), user.getCreatedBy()};
            updateNum = BaseDao.execute(connection, sql, params, pstm);
            BaseDao.closeResource(null, pstm, null);
        }
        return updateNum;
    }

    //根据用户id删除该用户
    public int deleteUserById(Connection connection, Integer delId) throws Exception {
        PreparedStatement pstm=null;
        int deleteNum=0;
        if(connection!=null){
            String sql="DELETE FROM `smbms_user` WHERE id=?";
            Object[] params={delId};
            deleteNum=BaseDao.execute(connection,sql,params,pstm);
            BaseDao.closeResource(null,pstm,null);
        }
        return deleteNum;
    }

    //通过userId查看当前用户信息
    public User getUserById(Connection connection, String id) throws Exception {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        User user = new User();
        if(connection!=null){
            String sql="select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.id=? and u.userRole = r.id";
            Object[] params={id};
            rs = BaseDao.execute(connection, sql, params, rs, pstm);
            while(rs.next()){
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setCreationDate(rs.getTimestamp("creationDate"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setModifyDate(rs.getTimestamp("modifyDate"));
                user.setUserRoleName(rs.getString("userRoleName"));
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return user;
    }

    //修改用户的信息
    public int modify(Connection connection, User user) throws Exception {
        int updateNum = 0;
        PreparedStatement pstm = null;
        if(null != connection){
            String sql = "update smbms_user set userName=?,"+
                    "gender=?,birthday=?,phone=?,address=?,userRole=?,modifyBy=?,modifyDate=? where id = ? ";
            Object[] params = {user.getUserName(),user.getGender(),user.getBirthday(),
                    user.getPhone(),user.getAddress(),user.getUserRole(),user.getModifyBy(),
                    user.getModifyDate(),user.getId()};
            updateNum = BaseDao.execute(connection, sql,params,pstm);
            BaseDao.closeResource(null, pstm, null);
        }
        return updateNum;
    }

//增加用户
public Boolean add(User user);

//修改用户信息
public Boolean modify(User user) throws Exception;

//根据用户编码，判断用户是否存在
public User selectUserCodeExist(String userCode);

//根据用户id删除用户
public boolean deleteUserById(Integer delId);

//根据用户id得到当前用户
public User getUserById(String id);

    public Boolean add(User user) {
        boolean flag = false;
        Connection connection = null;
        try {
            connection = BaseDao.getConnection();//获得连接
            connection.setAutoCommit(false);//开启JDBC事务管理
            int updateRows = userDao.add(connection,user);
            connection.commit();
            if(updateRows > 0){
                flag = true;
                System.out.println("add success!");
            }else{
                System.out.println("add failed!");
            }

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            try {
                System.out.println("rollback==================");
                connection.rollback();//失败就回滚
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        }finally{
            //在service层进行connection连接的关闭
            BaseDao.closeResource(connection, null, null);
        }
        return flag;
    }

    public Boolean modify(User user) {
        Boolean flag=false;
        Connection connection=null;
        try {
            connection=BaseDao.getConnection();
            connection.setAutoCommit(false);//开启JDBC事务
            int updateNum = userDao.modify(connection, user);//执行修改sql
            connection.commit();//提交事务
            if(updateNum>0){
                flag=true;
                System.out.println("修改用户成功");
            }else{
                System.out.println("修改用户失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            //若抛出异常，则说明修改失败需要回滚
            System.out.println("修改失败，回滚事务");
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return flag;
    }

    public User selectUserCodeExist(String userCode) {

        Connection connection = null;
        User user = null;
        try {
            connection = BaseDao.getConnection();
            user = userDao.getLoginUser(connection, userCode);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            BaseDao.closeResource(connection, null, null);
        }
        return user;
    }

    public boolean deleteUserById(Integer delId) {
        Boolean flag=false;
        Connection connection=null;
        connection=BaseDao.getConnection();
        try {
            int deleteNum=userDao.deleteUserById(connection,delId);
            if(deleteNum>0)flag=true;
        } catch (Exception e) {
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return flag;
    }

    public User getUserById(String id) {
        User user = new User();
        Connection connection=null;
        try {
            connection=BaseDao.getConnection();
            user = userDao.getUserById(connection,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return  user;
    }

//整体的Servlet
    public void init() throws ServletException {
        // Put your code here
    }
    public UserServlet() {
        super();
    }
    public void destroy() {
        super.destroy();
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String method = request.getParameter("method");
        System.out.println("method----> " + method);
        if(method != null && method.equals("add")){
            //增加操作
            this.add(request, response);
        }else if(method != null && method.equals("query")){
            //查询用户操作
            this.query(request, response);
        }else if(method != null && method.equals("getrolelist")){
            //查询用户角色表
            this.getRoleList(request, response);
        }else if(method != null && method.equals("ucexist")){
            //查询当前用户编码是否存在
            this.userCodeExist(request, response);
        }else if(method != null && method.equals("deluser")){
            //删除用户
            this.delUser(request, response);
        }else if(method != null && method.equals("view")){
            //通过用户id得到用户
            this.getUserById(request, response,"userview.jsp");
        }else if(method != null && method.equals("modify")){
            //通过用户id得到用户
            this.getUserById(request, response,"usermodify.jsp");
        }else if(method != null && method.equals("modifyexe")){
            //验证用户
            this.modify(request, response);
        }else if(method != null && method.equals("pwdmodify")){
            //验证用户密码
            this.modifyPwd(request, response);
        }else if(method != null && method.equals("savepwd")){
            //更新用户密码
            this.updatePwd(request, response);
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }





		//增加用户
    private void add(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("当前正在执行增加用户操作");
        //从前端得到页面的请求的参数即用户输入的值
        String userCode = req.getParameter("userCode");
        String userName = req.getParameter("userName");
        String userPassword = req.getParameter("userPassword");
        //String ruserPassword = req.getParameter("ruserPassword");
        String gender = req.getParameter("gender");
        String birthday = req.getParameter("birthday");
        String phone = req.getParameter("phone");
        String address = req.getParameter("address");
        String userRole = req.getParameter("userRole");
        //把这些值塞进一个用户属性中
        User user = new User();
        user.setUserCode(userCode);
        user.setUserName(userName);
        user.setUserPassword(userPassword);
        user.setAddress(address);
        user.setGender(Integer.valueOf(gender));
        user.setPhone(phone);
        try {
            user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(birthday));
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        user.setUserRole(Integer.valueOf(userRole));
        user.setCreationDate(new Date());
        //查找当前正在登陆的用户的id
        user.setCreatedBy(((User)req.getSession().getAttribute(Constants.USER_SESSION)).getId());
        UserServiceImpl userService = new UserServiceImpl();
        Boolean flag = userService.add(user);
        //如果添加成功，则页面转发，否则重新刷新，再次跳转到当前页面
        if(flag){
            resp.sendRedirect(req.getContextPath()+"/jsp/user.do?method=query");
        }else{
            req.getRequestDispatcher("useradd.jsp").forward(req,resp);
        }
    }

    //得到用户角色表
    private void getRoleList(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
        List<Role> roleList = null;
        RoleService roleService = new RoleServiceImpl();
        roleList = roleService.getRoleList();
        //把roleList转换成json对象输出
        resp.setContentType("application/json");
        PrintWriter outPrintWriter = resp.getWriter();
        outPrintWriter.write(JSONArray.toJSONString(roleList));
        outPrintWriter.flush();
        outPrintWriter.close();
    }

    //判断当前输入用户编码是否可用，即是否与已经存在的编码发生冲突
    private void userCodeExist(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
        //先拿到用户的编码
        String userCode = req.getParameter("userCode");
        //用一个hashmap，暂存现在所有现存的用户编码
        HashMap<String, String> resultMap = new HashMap<String, String>();
        if(StringUtils.isNullOrEmpty(userCode)){
            //userCode == null || userCode.equals("")
            //如果输入的这个编码为空或者不存在，说明可用
            resultMap.put("userCode", "exist");
        }else{//如果输入的编码不为空，则需要去找一下是否存在这个用户
            UserService userService = new UserServiceImpl();
            User user = userService.selectUserCodeExist(userCode);
            if(null != user){
                resultMap.put("userCode","exist");
            }else{
                resultMap.put("userCode", "notexist");
            }
        }
        //把resultMap转为json字符串以json的形式输出
        //配置上下文的输出类型
        resp.setContentType("application/json");
        //从response对象中获取往外输出的writer对象
        PrintWriter outPrintWriter = resp.getWriter();
        //把resultMap转为json字符串 输出
        outPrintWriter.write(JSONArray.toJSONString(resultMap));
        outPrintWriter.flush();//刷新
        outPrintWriter.close();//关闭流
    }

    //删除用户，需要当前的Id，来找到这个用户然后删除
    private void delUser(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
        String id = req.getParameter("uid");
        Integer delId = 0;
        try{
            delId = Integer.parseInt(id);
        }catch (Exception e) {
            // TODO: handle exception
            delId = 0;
        }
        //需要判断是否能删除成功
        HashMap<String, String> resultMap = new HashMap<String, String>();
        if(delId <= 0){
            resultMap.put("delResult", "notexist");
        }else{
            UserService userService = new UserServiceImpl();
            if(userService.deleteUserById(delId)){
                resultMap.put("delResult", "true");
            }else{
                resultMap.put("delResult", "false");
            }
        }

        //把resultMap转换成json对象输出
        resp.setContentType("application/json");
        PrintWriter outPrintWriter = resp.getWriter();
        outPrintWriter.write(JSONArray.toJSONString(resultMap));
        outPrintWriter.flush();
        outPrintWriter.close();
    }

    //通过id得到用户信息
    private void getUserById(HttpServletRequest req, HttpServletResponse resp,String url) throws ServletException, IOException{

        String id = req.getParameter("uid");
        if(!StringUtils.isNullOrEmpty(id)){
            //调用后台方法得到user对象
            UserService userService = new UserServiceImpl();
            User user = userService.getUserById(id);
            req.setAttribute("user", user);
            req.getRequestDispatcher(url).forward(req, resp);
        }
    }

    //修改用户信息
    private void modify(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
        //需要拿到前端传递进来的参数
        String id = req.getParameter("uid");;
        String userName = req.getParameter("userName");
        String gender = req.getParameter("gender");
        String birthday = req.getParameter("birthday");
        String phone = req.getParameter("phone");
        String address = req.getParameter("address");
        String userRole = req.getParameter("userRole");

        //创建一个user对象接收这些参数
        User user = new User();
        user.setId(Integer.valueOf(id));
        user.setUserName(userName);
        user.setGender(Integer.valueOf(gender));
        try {
            user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(birthday));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        user.setPhone(phone);
        user.setAddress(address);
        user.setUserRole(Integer.valueOf(userRole));
        user.setModifyBy(((User)req.getSession().getAttribute(Constants.USER_SESSION)).getId());
        user.setModifyDate(new Date());

        //调用service层
        UserServiceImpl userService = new UserServiceImpl();
        Boolean flag = userService.modify(user);

        //判断是否修改成功来决定跳转到哪个页面
        if(flag){
            resp.sendRedirect(req.getContextPath()+"/jsp/user.do?method=query");
        }else{
            req.getRequestDispatcher("usermodify.jsp").forward(req, resp);
        }

    }

package com.mario.dao.bill;

import com.mario.pojo.Bill;


import java.sql.Connection;
import java.util.List;

public interface BillDao {
    /**
     * 增加订单
     * @param connection
     * @param bill
     * @return
     * @throws Exception
     */
    public int add(Connection connection, Bill bill)throws Exception;


    /**
     * 通过查询条件获取供应商列表-模糊查询-getBillList
     * @param connection
     * @param bill
     * @return
     * @throws Exception
     */
    public List<Bill> getBillList(Connection connection, Bill bill)throws Exception;

    /**
     * 通过delId删除Bill
     * @param connection
     * @param delId
     * @return
     * @throws Exception
     */
    public int deleteBillById(Connection connection, String delId)throws Exception;


    /**
     * 通过billId获取Bill
     * @param connection
     * @param id
     * @return
     * @throws Exception
     */
    public Bill getBillById(Connection connection, String id)throws Exception;

    /**
     * 修改订单信息
     * @param connection
     * @param bill
     * @return
     * @throws Exception
     */
    public int modify(Connection connection, Bill bill)throws Exception;

    /**
     * 根据供应商ID查询订单数量
     * @param connection
     * @param providerId
     * @return
     * @throws Exception
     */
    public int getBillCountByProviderId(Connection connection, String providerId)throws Exception;
}

package com.mario.dao.bill;

import com.mario.dao.BaseDao;
import com.mario.pojo.Bill;
import com.mysql.cj.util.StringUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;

public class BillDaoImpl  implements BillDao{

    //根据用户输入的值，新增订单表
    public int add(Connection connection, Bill bill) throws Exception {
        int updateNum=0;
        PreparedStatement pstm=null;
        if(connection!=null){
            String sql = "insert into smbms_bill (billCode,productName,productDesc," +
                    "productUnit,productCount,totalPrice,isPayment,providerId,createdBy,creationDate) " +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            Object[] params = {bill.getBillCode(),bill.getProductName(),bill.getProductDesc(),
                    bill.getProductUnit(),bill.getProductCount(),bill.getTotalPrice(),bill.getIsPayment(),
                    bill.getProviderId(),bill.getCreatedBy(),bill.getCreationDate()};
            updateNum = BaseDao.execute(connection, sql, params,pstm);
            BaseDao.closeResource(null, pstm, null);
            System.out.println("dao--------修改行数 " + updateNum);
        }
        return updateNum;
    }

    //根据用户输入的值条件，查询订单表
    public List<Bill> getBillList(Connection connection, Bill bill) throws Exception {
        List<Bill> billList = new ArrayList<Bill>();
        PreparedStatement pstm=null;
        ResultSet rs=null;
        if(connection!=null){
            StringBuffer sql=new StringBuffer();
            sql.append("SELECT b.*,p.proName AS providerName FROM smbms_bill b, smbms_provider p WHERE b.providerId = p.id");
            List<Object> list = new ArrayList<Object>();//用来暂存用户的输入
            if(!StringUtils.isNullOrEmpty(bill.getProductName())){//判断用户是否输入商品名称
                sql.append(" AND b.`productName` LIKE ?");
                list.add("%"+bill.getProductName()+"%");
            }
            if(bill.getProviderId()>0){//判断是否选择了供应商
                sql.append(" AND p.`providerId`=?");
                list.add(bill.getProviderId());
            }if(bill.getIsPayment()>0){//判断是否选择了是否付款
                sql.append(" AND b.`isPayment`=?");
                list.add(bill.getIsPayment());
            }
            Object[] params=list.toArray();
            rs= BaseDao.execute(connection, sql.toString(), params, rs, pstm);
            System.out.println("当前的sql是----->"+sql.toString());
            while(rs.next()){
                Bill _bill = new Bill();//创建一个bill对象存储查询到的属性
                _bill.setId(rs.getInt("id"));
                _bill.setBillCode(rs.getString("billCode"));
                _bill.setProductName(rs.getString("productName"));
                _bill.setProductDesc(rs.getString("productDesc"));
                _bill.setProductUnit(rs.getString("productUnit"));
                _bill.setProductCount(rs.getBigDecimal("productCount"));
                _bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                _bill.setIsPayment(rs.getInt("isPayment"));
                _bill.setProviderId(rs.getInt("providerId"));
                _bill.setProviderName(rs.getString("providerName"));
                _bill.setCreationDate(rs.getTimestamp("creationDate"));
                _bill.setCreatedBy(rs.getInt("createdBy"));
                billList.add(_bill);
            }
            BaseDao.closeResource(null,pstm,rs);
        }

        return billList;
    }

    //根据订单id删除该订单
    public int deleteBillById(Connection connection, String delId) throws Exception {
        int delNum=0;
        PreparedStatement pstm=null;
        if(connection!=null){
            String sql="DELETE FROM `smbms_bill` WHERE id=?";
            Object[] params={delId};
            delNum = BaseDao.execute(connection, sql, params, pstm);
            BaseDao.closeResource(null,pstm,null);
        }
        return delNum;
    }

    //通过订单id得到该订单的所有信息（正确）
    public Bill getBillById(Connection connection, String id) throws Exception {
        // TODO Auto-generated method stub
        Bill bill = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        if(null != connection){
            String sql = "select b.*,p.proName as providerName from smbms_bill b, smbms_provider p " +
                    "where b.providerId = p.id and b.id=?";
            Object[] params = {id};
            rs = BaseDao.execute(connection,sql, params,rs,pstm);
            if(rs.next()){
                bill = new Bill();
                bill.setId(rs.getInt("id"));
                bill.setBillCode(rs.getString("billCode"));
                bill.setProductName(rs.getString("productName"));
                bill.setProductDesc(rs.getString("productDesc"));
                bill.setProductUnit(rs.getString("productUnit"));
                bill.setProductCount(rs.getBigDecimal("productCount"));
                bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                bill.setIsPayment(rs.getInt("isPayment"));
                bill.setProviderId(rs.getInt("providerId"));
                bill.setProviderName(rs.getString("providerName"));
                bill.setModifyBy(rs.getInt("modifyBy"));
                bill.setModifyDate(rs.getTimestamp("modifyDate"));
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return bill;
    }

    //根据用户传递输入的值修改订单表
    public int modify(Connection connection, Bill bill) throws Exception {
        int modifyNum=0;
        PreparedStatement pstm=null;
        if(connection!=null){
            String sql = "update smbms_bill set productName=?," +
                    "productDesc=?,productUnit=?,productCount=?,totalPrice=?," +
                    "isPayment=?,providerId=?,modifyBy=?,modifyDate=? where id = ? ";
            Object[] params = {bill.getProductName(),bill.getProductDesc(),
                    bill.getProductUnit(),bill.getProductCount(),bill.getTotalPrice(),bill.getIsPayment(),
                    bill.getProviderId(),bill.getModifyBy(),bill.getModifyDate(),bill.getId()};
            modifyNum=BaseDao.execute(connection,sql,params,pstm);
            BaseDao.closeResource(null,pstm,null);
        }
        return modifyNum;
    }

    //通过供应商id得到该供应商总订单数(正确）
    public int getBillCountByProviderId(Connection connection, String providerId) throws Exception {
        int billcount=0;
        PreparedStatement pstm=null;
        ResultSet rs=null;
        if(connection!=null){
            String sql="SELECT COUNT(1) as billCount FROM `smbms_bill` WHERE `providerId`=?";
            Object[] params={providerId};
            rs = BaseDao.execute(connection, sql, params, rs, pstm);
            while(rs.next()){
                billcount=rs.getInt("billCount");
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return billcount;
    }
//    @Test
//    public void test(){
//        BillDaoImpl billDao = new BillDaoImpl();
//        Connection connection=BaseDao.getConnection();
//        int billList=0;
//        try {
//            billList=billDao.deleteBillById(connection,"19");
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        BaseDao.closeResource(connection,null,null);
//        System.out.println(billList);
//    }
}