SMBMS超市订单管理系统（完结）

最新推荐文章于 2023-08-28 19:37:26 发布

长安也有星星

最新推荐文章于 2023-08-28 19:37:26 发布

阅读量1.5k

点赞数 2

文章标签： servlet java tomcat maven github

本文链接：https://blog.csdn.net/weixin_71614127/article/details/128012905

版权

文章目录

项目资源可以从我的github上获取

https://github.com/chiyu999/smbms.git

七、供应商管理界面功能实现

这个模块跟上面的基本上一样，甚至还精简了，总共需要走5条线

在这里插入图片描述

思路：

写第一条线，也就是点击供应商管理按钮的时候，跳转到的页面，需要展示所有的供应商以及供应商的部分信息，因此分析：

1、我们需要去数据库查询所有的供应商信息，并返回一个供应商列表给前端，那么dao层就需要有一个获取所有供应商列表的方法

2、我们根据条件搜索我们想要的供应商的信息，如果符合条件的供应商信息有多个，那么返回的也是一个供应商列表，因此我们可以和上面的方法合起来，如果从前端传入的参数为空，也就是没有搜索条件的时候，展示的就是所有供应商的信息，而加入前端传入的参数不为空，则根据传入的参数拼接sql语句去查询，返回的也是一个供应商列表。

3、因此，我们从dao层写起，获取供应商列表的方法，然后Service层调用dao层，servlet调用service层，然后将获取到的供应商列表传回给前端遍历渲染即可。

思路，分析是自顶而下，而写代码是自下而上，层层相扣

下面仅展示代码，不细致写了

7.1、ProviderDao

package com.zhang.dao.provider;

import com.zhang.pojo.Provider;

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

public interface ProvideDao {
    /**
     * 查询供应商列表
     * @param connection
     * @param proCode
     * @param proName
     * @return
     * @throws SQLException
     */
    public List<Provider> getProviderList(Connection connection,String proCode,String proName) throws SQLException;

    /**
     * 根据Id查询供应商
     * @param connection
     * @param proId
     * @return
     * @throws SQLException
     */
    public Provider getProviderById(Connection connection,int proId) throws SQLException;

    /**
     * 添加供应商
     * @param provider
     * @return
     * @throws SQLException
     */
    public int addProvider(Connection connection,Provider provider) throws SQLException;

    /**
     * 根据Id更改供应商信息
     * @param connection
     * @param provider
     * @return
     * @throws SQLException
     */
    public int modifyProviderById(Connection connection,Provider provider) throws SQLException;

    public int deleteProviderById(Connection connection,int providerId) throws SQLException;
}

7.2、ProviderDaoImpl

package com.zhang.dao.provider;

import com.mysql.jdbc.StringUtils;
import com.zhang.dao.BaseDao;
import com.zhang.pojo.Provider;
import org.junit.Test;

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



public class ProviderDaoImpl implements ProvideDao{
    /**
     * 根据条件查询供应商列表
     * @param connection
     * @param proCode
     * @param proName
     * @return
     * @throws SQLException
     */
    @Override
    public List<Provider> getProviderList(Connection connection,String proCode,String proName) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Object> list = new ArrayList<>(); //存放参数
        List<Provider> providerList = new ArrayList<>();
        if (connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select * from smbms_provider p");
            if (!StringUtils.isNullOrEmpty(proCode)){
                sql.append(" where p.proCode like ?");
                list.add("%"+proCode+"%");
                if (!StringUtils.isNullOrEmpty(proName)){
                    sql.append(" and p.proName like ?");
                    list.add("%"+proName+"%");
                }
            }else {
                if (!StringUtils.isNullOrEmpty(proName)){
                    sql.append(" where p.proName like ?");
                    list.add("%"+proName+"%");
                }
            }

            Object[] params = list.toArray();
            for (Object param : params) {
                System.out.println(param);
            }
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            System.out.println("providerDao ====> sql=====>"+sql.toString());
            while (rs.next()){
                Provider provider = new Provider();
                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProDesc(rs.getString("proDesc"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProAddress(rs.getString("proAddress"));
                provider.setProFax(rs.getString("proFax"));
                //provider.setCreatedBy(rs.getInt("createdBy"));
                provider.setCreationDate(rs.getDate("creationDate"));
                //provider.setModifyBy(rs.getInt("modifyBy"));
                //provider.setModifyDate(rs.getDate("modifyDate"));
                providerList.add(provider);
            }
            BaseDao.closeResource(null, pstm, rs);

        }
        return providerList;
    }

    /**
     * 根据id查询供应商
     * @param connection
     * @param proId
     * @return
     * @throws SQLException
     */
    @Override
    public Provider getProviderById(Connection connection, int proId) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Provider provider = null;
        if (connection != null){
            String sql = "select * from smbms_provider p where p.id = ?";
            Object[] params = {proId};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()){
                provider = new Provider();
                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProDesc(rs.getString("proDesc"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProAddress(rs.getString("proAddress"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProFax(rs.getString("proFax"));
            }

        }
        return provider;
    }

    /**
     * 添加供应商
     * @param provider
     * @return
     * @throws SQLException
     */
    @Override
    public int addProvider(Connection connection,Provider provider) throws SQLException {
        int result = 0;
        PreparedStatement pstm = null;
        if (connection!= null){
            String sql = "insert into smbms_provider" +
                    "(proCode,proName,proDesc,proContact,proPhone,proAddress,proFax,createdBy,creationDate)" +
                    "values (?,?,?,?,?,?,?,?,?)";
            Object[] params = {
                    provider.getProCode(),
                    provider.getProName(),
                    provider.getProDesc(),
                    provider.getProCode(),
                    provider.getProPhone(),
                    provider.getProAddress(),
                    provider.getProFax(),
                    provider.getCreatedBy(),
                    new Date()
            };
            result = BaseDao.execute(connection,pstm,sql,params);
        }
        return result;
    }

    /**
     * 根据Id修改供应商信息
     * @param connection
     * @param provider
     * @return
     * @throws SQLException
     */
    @Override
    public int modifyProviderById(Connection connection, Provider provider) throws SQLException {
        int result = 0;
        PreparedStatement pstm = null;
        if (connection != null) {
            System.out.println("ProviderDaoImp[l=======>modifyProviderById");
            String sql = "update smbms_provider" +
                    " set proCode=?,proName=?,proContact=?,proPhone=?,proAddress=?,proFax=?,proDesc=? where id=?";
            Object[] params = {
                    provider.getProCode(),
                    provider.getProName(),
                    provider.getProContact(),
                    provider.getProPhone(),
                    provider.getProAddress(),
                    provider.getProFax(),
                    provider.getProDesc(),
                    provider.getId()};
            result = BaseDao.execute(connection,pstm,sql,params);
            BaseDao.closeResource(null, pstm, null);
            }
        return result;
    }

    /**
     * 根据Id删除供应商
     * @param connection
     * @param providerId
     * @return
     * @throws SQLException
     */
    @Override
    public int deleteProviderById(Connection connection, int providerId) throws SQLException {
        int result = 0;
        PreparedStatement pstm = null;
        if (connection!= null) {
            String sql = "delete from smbms_provider" +
                    " where id=?";
            Object[] params = {providerId};
            result = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return result;
    }

    //测试用
    @Test
    public void test() throws SQLException {
        Connection connection = BaseDao.getConnection();
//        if (connection != null){
//            System.out.println("connection != null");
//            ProviderDaoImpl providerDao = new ProviderDaoImpl();
//            List<Provider> providerList = providerDao.getProviderList(connection, "BJ_GYS001", "北京");
//            for (Provider provider : providerList) {
//                System.out.println(provider.getProCode());
//            }
//        }
//        if (connection != null){
//            Provider providerById = new ProviderDaoImpl().getProviderById(connection, 1);
//            System.out.println(providerById.toString());
//        }

    }
}

7.3、ProviderService

package com.zhang.service.provider;

import com.zhang.pojo.Provider;

import java.sql.SQLException;
import java.util.List;

public interface ProviderService {
    /**
     * 根据条件获取供应商列表
     * @param proCode
     * @param proName
     * @return
     * @throws SQLException
     */
    public List<Provider> getProviderList(String proCode,String proName) throws SQLException;

    /**
     * 根据id获取供应商详细信息
     * @param proId
     * @return
     * @throws SQLException
     */
    public Provider getProviderById(int proId) throws SQLException;

    /**
     * 添加供应商
     * @param provider
     * @return
     * @throws SQLException
     */
    public boolean addProvider(Provider provider) throws SQLException;

    /**
     * 根据用户id修改用户信息
     * @param provider
     * @return
     * @throws SQLException
     */
    public boolean modifyProviderById(Provider provider) throws SQLException;

    /**
     * 删除供应商
     * @param providerId
     * @return
     * @throws SQLException
     */
    public boolean deleteProviderById(int providerId) throws SQLException;


}

7.4、ProviderServiceImpl

package com.zhang.service.provider;

import com.zhang.dao.BaseDao;
import com.zhang.dao.provider.ProviderDaoImpl;
import com.zhang.pojo.Provider;

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

public class ProviderServiceImpl implements ProviderService{

    private ProviderDaoImpl providerDao;
    public ProviderServiceImpl(){
        providerDao = new ProviderDaoImpl();
    }

    /**
     * 根据条件获取用户列表
     * @param proCode
     * @param proName
     * @return
     * @throws SQLException
     */
    @Override
    public List<Provider> getProviderList(String proCode, String proName) throws SQLException {
        Connection connection = null;
        List<Provider> providerList = null;
        try {
            connection = BaseDao.getConnection();
            providerList = providerDao.getProviderList(connection, proCode, proName);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return providerList;
    }

    /**
     * 根据proId获取供应商详细信息
     * @param proId
     * @return
     * @throws SQLException
     */
    @Override
    public Provider getProviderById(int proId) throws SQLException {
        Connection connection = null;
        Provider provider = null;
        try {
            connection = BaseDao.getConnection();
            provider = providerDao.getProviderById(connection, proId);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return provider;
    }

    /**
     * 增加供应商
     * @param provider
     * @return
     * @throws SQLException
     */
    @Override
    public boolean addProvider(Provider provider) throws SQLException {
        Connection connection = null;
        boolean flag = false;
        try {
            connection = BaseDao.getConnection();
            connection.setAutoCommit(false);
            int i = providerDao.addProvider(connection, provider);
            connection.commit();
            if (i > 0){
                flag = true;
            }
        }catch (Exception e){
            connection.rollback();
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return flag;
    }

    /**
     * 更改供应商信息
     * @param provider
     * @return
     * @throws SQLException
     */
    @Override
    public boolean modifyProviderById(Provider provider) throws SQLException {
        boolean flag = false;
        Connection connection = null;
        if (provider != null){
            System.out.println("ProviderServiceImp[l=======>modifyProviderById");
            try {
                connection = BaseDao.getConnection();
                connection.setAutoCommit(false);
                int i = providerDao.modifyProviderById(connection, provider);
                connection.commit();
                if (i > 0) {
                    flag = true;
                }
            }catch (Exception e) {
                connection.rollback();
                e.printStackTrace();
            }finally {
                BaseDao.closeResource(connection,null,null);
            }
        }
        return flag;
    }

    /**
     * 删除供应商
     * @param providerId
     * @return
     * @throws SQLException
     */
    @Override
    public boolean deleteProviderById(int providerId) throws SQLException {
        boolean flag = false;
        Connection connection = null;
        try {
            connection = BaseDao.getConnection();
            connection.setAutoCommit(false);
            int i = providerDao.deleteProviderById(connection, providerId);
            connection.commit();
            if (i > 0) {
                flag = true;
            }
        }catch (SQLException e){
            connection.commit();
            e.printStackTrace();
        } finally {
            BaseDao.closeResource(connection,null,null);
        }
        return flag;
    }
}

7.5、ProviderServlet

package com.zhang.servlet.provider;

import com.alibaba.fastjson.JSONArray;
import com.mysql.jdbc.StringUtils;
import com.zhang.pojo.Provider;
import com.zhang.pojo.User;
import com.zhang.service.provider.ProviderServiceImpl;
import com.zhang.utils.Constants;
import lombok.SneakyThrows;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class ProviderServlet extends HttpServlet {
    private ProviderServiceImpl providerService;
    public ProviderServlet(){
        providerService = new ProviderServiceImpl();

    }

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

        String method = req.getParameter("method");
        if (method != null && method.equals("query")) {
            this.query(req, resp);
        }else if (method != null && method.equals("view")){
            this.viewProviderById(req, resp,"providerview.jsp");
        }else if (method != null && method.equals("add")){
            this.addProvider(req, resp);
        }else if (method != null && method.equals("modify")){
            this.viewProviderById(req, resp,"providermodify.jsp");
        }else if (method != null && method.equals("modifysave")){
            this.modifyProviderById(req, resp);
        }else if (method != null && method.equals("delprovider")){
            this.deleteProviderById(req, resp);
        }
    }

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

    /**
     * 查询供应商列表
     * @param req
     * @param resp
     * @throws Exception
     */
    public void query(HttpServletRequest req, HttpServletResponse resp) throws Exception{
        String queryProCode = req.getParameter("queryProCode");
        String queryProName = req.getParameter("queryProName");
        List<Provider> providerList = providerService.getProviderList(queryProCode, queryProName);
        req.setAttribute("providerList",providerList);
        req.setAttribute("queryProCode",queryProCode);
        req.setAttribute("queryProName",queryProName);
        req.getRequestDispatcher("providerlist.jsp").forward(req, resp);
    }

    /**
     * 根据Id查询用户详细信息
     * @param req
     * @param resp
     * @throws Exception
     */
    public void viewProviderById(HttpServletRequest req, HttpServletResponse resp,String url) throws Exception {
        String proId = req.getParameter("proid");
        if (proId != null){
            Provider provider = providerService.getProviderById(Integer.valueOf(proId));
            req.setAttribute("provider",provider);
            req.getRequestDispatcher(url).forward(req, resp);
        }else {
            req.getRequestDispatcher("error.jsp").forward(req, resp);
        }
    }

    /**
     * 添加用户
     * @param req
     * @param resp
     * @throws Exception
     */
    public void addProvider(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        String proCode = req.getParameter("proCode");
        String proName = req.getParameter("proName");
        String proContact = req.getParameter("proContact");
        String proPhone = req.getParameter("proPhone");
        String proAddress = req.getParameter("proAddress");
        String proFax = req.getParameter("proFax");
        String proDesc = req.getParameter("proDesc");
        Provider provider = new Provider();
        provider.setProCode(proCode);
        provider.setProName(proName);
        provider.setProContact(proContact);
        provider.setProPhone(proPhone);
        provider.setProAddress(proAddress);
        provider.setProFax(proFax);
        provider.setProDesc(proDesc);
        provider.setCreatedBy(((User) req.getSession().getAttribute(Constants.USER_SESSION)).getId());
        boolean flag = providerService.addProvider(provider);
        if (flag){
            resp.sendRedirect(req.getContextPath()+"/jsp/provider.do?method=query");
        }else {
            req.getRequestDispatcher("provideradd.jsp").forward(req, resp);
        }


    }

    /**
     * 根据ID修改供应商信息
     * @param req
     * @param resp
     * @throws Exception
     */
    public void modifyProviderById(HttpServletRequest req, HttpServletResponse resp) throws Exception{
        String id = req.getParameter("id");
        System.out.println(id);
        String proCode = req.getParameter("proCode");
        String proName = req.getParameter("proName");
        String proContact = req.getParameter("proContact");
        String proPhone = req.getParameter("proPhone");
        String proAddress = req.getParameter("proAddress");
        String proFax = req.getParameter("proFax");
        String proDesc = req.getParameter("proDesc");
        Provider provider = new Provider();
        if (!StringUtils.isNullOrEmpty(id)){
            provider.setId(Integer.parseInt(id));
        }
        provider.setProCode(proCode);
        provider.setProName(proName);
        provider.setProContact(proContact);
        provider.setProPhone(proPhone);
        provider.setProAddress(proAddress);
        provider.setProFax(proFax);
        provider.setProDesc(proDesc);
        System.out.println(provider.toString());
        if (providerService.modifyProviderById(provider)){
            resp.sendRedirect(req.getContextPath()+"/jsp/provider.do?method=query");
        }else {
            req.getRequestDispatcher("providermodify.jsp").forward(req, resp);
        }

    }

    /**
     * 删除供应商
     * @param req
     * @param resp
     * @throws Exception
     */
    public void deleteProviderById(HttpServletRequest req, HttpServletResponse resp) throws Exception{
        String proId = req.getParameter("proid");
        //要删除的供应商id 默认为0
        int delProviderId = 0;
        //如果要从前端传过来的参数不能转换为int类型的用户id，则给要删除的用户id赋值为0
        try {
            delProviderId = Integer.parseInt(proId);
        }catch (Exception e){
            e.printStackTrace();
            delProviderId = 0;
        }
        //万能的Map,用来存放返回前端的消息
        Map<String, String> map = new HashMap<>();
        if (delProviderId <= 0){
            map.put("delResult","notexist");
        }else{
            boolean flag = providerService.deleteProviderById(delProviderId);
            if (flag) {
                map.put("delResult", "true");
            }else {
                map.put("delResult", "false");
            }
        }
        //将map转换为JSON字符串返回
        resp.setContentType("application/json");
        PrintWriter writer = resp.getWriter();
        String result = JSONArray.toJSONString(map);
        writer.write(result);
        writer.flush();
        writer.close();
    }
}

7.6、测试

测试成功即可！！！

八、订单管理界面功能实现

思路：

在写Dao层的时候还需要增加一个根据供应商id查询订单数量的方法，因为订单表跟供应商表是有对应关系的，订单界面需要展示供应商的名称，而供应商的名称是根据供应商id来获取的，所以我们根据供应商id如果查询该供应商有订单记录的话，在供应商管理界面操作删除供应商的时候就应该不让他操作成功！！！

优化ProviderService层的删除供应商的业务逻辑代码！！！

8.1、BillDao

package com.zhang.dao.bill;

import com.zhang.pojo.Bill;

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

public interface BillDao {
    /**
     * 通过查询条件获取供应商列表
     * @param connection
     * @param queryBill
     * @return
     * @throws SQLException
     */
    public List<Bill> getBillList(Connection connection,Bill queryBill) throws SQLException;

    /**
     * 添加订单
     * @param connection
     * @param bill
     * @return
     * @throws Exception
     */
    public int add(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;


}

8.2、BillDaoImpl

package com.zhang.dao.bill;

import com.mysql.jdbc.StringUtils;
import com.zhang.dao.BaseDao;
import com.zhang.pojo.Bill;

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

public class BillDaoImpl implements BillDao{
    /**
     * 根据查询条件获取订单列表
     * @param connection
     * @param queryBill
     * @return
     * @throws SQLException
     */
    @Override
    public List<Bill> getBillList(Connection connection, Bill queryBill) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Bill> billList = new ArrayList<>();
        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(queryBill.getProductName())) {
                sql.append(" and productName like ?");
                list.add("%" + queryBill.getProductName() + "%");
            }
            if (queryBill.getProviderId() > 0) {
                sql.append(" and providerId = ?");
                list.add(queryBill.getProviderId());
            }
            if (queryBill.getIsPayment() > 0) {
                sql.append(" and isPayment = ?");
                list.add(queryBill.getIsPayment());
            }
            Object[] params = list.toArray();
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            while (rs.next()) {
                Bill 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.setCreationDate(rs.getTimestamp("creationDate"));
                bill.setCreatedBy(rs.getInt("createdBy"));

                billList.add(bill);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return billList;
    }

    /**
     * 添加订单
     * @param connection
     * @param bill
     * @return
     * @throws Exception
     */
    @Override
    public int add(Connection connection, Bill bill) throws Exception {
        PreparedStatement pstm = null;
        int result = 0;
        if (null != connection) {
            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()};
            result = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return result;
    }

    /**
     * 删除订单
     * @param connection
     * @param delId
     * @return
     * @throws Exception
     */
    @Override
    public int deleteBillById(Connection connection, String delId) throws Exception {
        PreparedStatement pstm = null;
        int result = 0;
        if (null != connection) {
            String sql = "delete from smbms_bill where id=?";
            Object[] params = {delId};
            result = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return result;
    }

    /**
     * 通过id获取订单
     * @param connection
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public Bill getBillById(Connection connection, String id) throws Exception {
        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, pstm, rs, sql, params);
            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;
    }

    /**
     * 修改订单信息
     * @param connection
     * @param bill
     * @return
     * @throws Exception
     */
    @Override
    public int modify(Connection connection, Bill bill) throws Exception {
        int flag = 0;
        PreparedStatement pstm = null;
        if (null != connection) {
            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()};
            flag = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return flag;
    }

    /**
     * 根据供应商id查询订单数量
     * @param connection
     * @param providerId
     * @return
     * @throws Exception
     */
    @Override
    public int getBillCountByProviderId(Connection connection, String providerId) throws Exception {
        int count = 0;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        if (null != connection) {
            String sql = "select count(1) as billCount from smbms_bill where" +
                    "	providerId = ?";
            Object[] params = {providerId};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            if (rs.next()) {
                count = rs.getInt("billCount");
            }
            BaseDao.closeResource(null, pstm, rs);
        }

        return count;
    }
}

8.3、BillService

package com.zhang.service.bill;

import com.zhang.pojo.Bill;

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

public interface BillService {
    /**
     * 根据查询条件查询订单列表
     * @param queryBill
     * @return
     * @throws SQLException
     */
    public List<Bill> getBillList(Bill queryBill) throws SQLException;

    /**
     * 添加订单
     * @param bill
     * @return
     * @throws Exception
     */
    public boolean add(Bill bill) throws Exception;

    /**
     * 通过billId删除Bill
     * @param delId
     * @return
     */
    public boolean deleteBillById(String delId);

    /**
     * 通过billId获取Bill
     *
     * @param id
     * @return
     */
    public Bill getBillById(String id);

    /**
     * 修改订单信息
     *
     * @param bill
     * @return
     */
    public boolean modify(Bill bill);
}

8.4、BillServiceImpl

package com.zhang.service.bill;

import com.zhang.dao.BaseDao;
import com.zhang.dao.bill.BillDao;
import com.zhang.dao.bill.BillDaoImpl;
import com.zhang.pojo.Bill;

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

public class BillServiceImpl implements BillService {
    private BillDao billDao;

    public BillServiceImpl() {
        billDao = new BillDaoImpl();
    }

    /**
     * 根据查询条件获取到订单列表
     * @param queryBill
     * @return
     * @throws SQLException
     */
    @Override
    public List<Bill> getBillList(Bill queryBill) throws SQLException {
        Connection connection = null;
        List<Bill> billList = null;
        try {
            connection = BaseDao.getConnection();
            billList = billDao.getBillList(connection, queryBill);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeResource(connection, null, null);
        }
        return billList;

    }

    /**
     * 添加订单
     * @param bill
     * @return
     * @throws Exception
     */
    @Override
    public boolean add(Bill bill) throws Exception {
        boolean flag = false;
        Connection connection = null;
        try {
            connection = BaseDao.getConnection();
            connection.setAutoCommit(false);//开启JDBC事务管理
            if(billDao.add(connection,bill) > 0) {
                flag = true;
            }
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //在service层进行connection连接的关闭
            BaseDao.closeResource(connection, null, null);
        }
        return flag;
    }

    /**
     * 删除订单
     * @param delId
     * @return
     */
    @Override
    public boolean deleteBillById(String delId) {
        Connection connection = null;
        boolean flag = false;
        try {
            connection = BaseDao.getConnection();
            if(billDao.deleteBillById(connection, delId) > 0)
                flag = true;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            BaseDao.closeResource(connection, null, null);
        }
        return flag;
    }

    /**
     * 获取订单信息
     * @param id
     * @return
     */
    @Override
    public Bill getBillById(String id) {
        Bill bill = null;
        Connection connection = null;
        try{
            connection = BaseDao.getConnection();
            bill = billDao.getBillById(connection, id);
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            bill = null;
        }finally{
            BaseDao.closeResource(connection, null, null);
        }
        return bill;
    }

    /**
     * 修改订单信息
     * @param bill
     * @return
     */
    @Override
    public boolean modify(Bill bill) {
        Connection connection = null;
        boolean flag = false;
        try {
            connection = BaseDao.getConnection();
            if(billDao.modify(connection,bill) > 0){
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            BaseDao.closeResource(connection, null, null);
        }
        return flag;
    }


}

8.5、BillServlet

package com.zhang.servlet.bill;

import com.alibaba.fastjson.JSONArray;
import com.mysql.jdbc.StringUtils;
import com.zhang.pojo.Bill;
import com.zhang.pojo.Provider;
import com.zhang.pojo.User;
import com.zhang.service.bill.BillService;
import com.zhang.service.bill.BillServiceImpl;
import com.zhang.service.provider.ProviderService;
import com.zhang.service.provider.ProviderServiceImpl;
import com.zhang.utils.Constants;
import lombok.SneakyThrows;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

public class BillServlet extends HttpServlet {
    private BillService billService;
    public BillServlet(){
        billService = new BillServiceImpl();
    }

    @SneakyThrows
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String method = req.getParameter("method");
        if (method != null && method.equals("query")){
            this.query(req, resp);
        }else if (method != null && method.equals("add")){
            this.add(req, resp);
        }else if (method != null && method.equals("getproviderlist")){
            this.getProviderList(req, resp);
        }else if(method != null && method.equals("delbill")) {
            this.delBillById(req, resp);
        }else if(method != null && method.equals("view")){
            this.getBillById(req,resp,"billview.jsp");
        }else if(method != null && method.equals("modify")){
            this.getBillById(req,resp,"billmodify.jsp");
        }else if(method != null && method.equals("modifysave")){
            this.modify(req,resp);
        }
    }

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

    /**
     * 查询订单列表
     * @param req
     * @param resp
     * @throws SQLException
     * @throws ServletException
     * @throws IOException
     */
    public void query(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        List<Provider> providerList = new ArrayList<Provider>();
        ProviderService providerService = new ProviderServiceImpl();
        providerList = providerService.getProviderList("","");
        req.setAttribute("providerList", providerList);

        String queryProductName = req.getParameter("queryProductName");
        String queryProviderId = req.getParameter("queryProviderId");
        String queryIsPayment = req.getParameter("queryIsPayment");

        if(StringUtils.isNullOrEmpty(queryProductName)){
            queryProductName = "";
        }

        List<Bill> billList = new ArrayList<Bill>();
        Bill bill = new Bill();
        if(StringUtils.isNullOrEmpty(queryIsPayment)){
            bill.setIsPayment(0);
        }else{
            bill.setIsPayment(Integer.parseInt(queryIsPayment));
        }

        if(StringUtils.isNullOrEmpty(queryProviderId)){
            bill.setProviderId(0);
        }else{
            bill.setProviderId(Integer.parseInt(queryProviderId));
        }
        bill.setProductName(queryProductName);
        billList = billService.getBillList(bill);
        req.setAttribute("billList", billList);
        req.setAttribute("queryProductName", queryProductName);
        req.setAttribute("queryProviderId", queryProviderId);
        req.setAttribute("queryIsPayment", queryIsPayment);
        req.getRequestDispatcher("billlist.jsp").forward(req,resp);
    }

    /**
     * 添加订单
     * @param req
     * @param resp
     * @throws Exception
     */
    public void add(HttpServletRequest req, HttpServletResponse resp) throws Exception{
        String billCode = req.getParameter("billCode");
        String productName = req.getParameter("productName");
        String productDesc = req.getParameter("productDesc");
        String productUnit = req.getParameter("productUnit");
        String productCount = req.getParameter("productCount");
        String totalPrice = req.getParameter("totalPrice");
        String providerId = req.getParameter("providerId");
        String isPayment = req.getParameter("isPayment");

        Bill bill = new Bill();
        bill.setBillCode(billCode);
        bill.setProductName(productName);
        bill.setProductDesc(productDesc);
        bill.setProductUnit(productUnit);
        bill.setProductCount(new BigDecimal(productCount).setScale(2,BigDecimal.ROUND_DOWN)); //保留小数点后两位，直接去掉多余的位数
        bill.setIsPayment(Integer.parseInt(isPayment));
        bill.setTotalPrice(new BigDecimal(totalPrice).setScale(2,BigDecimal.ROUND_DOWN));
        bill.setProviderId(Integer.parseInt(providerId));
        bill.setCreatedBy(((User)req.getSession().getAttribute(Constants.USER_SESSION)).getId());
        bill.setCreationDate(new Date());
        boolean flag = false;
        flag = billService.add(bill);
        if(flag){
            resp.sendRedirect(req.getContextPath()+"/jsp/bill.do?method=query");
        }else{
            req.getRequestDispatcher("billadd.jsp").forward(req, resp);
        }
    }

    /**
     * 删除订单
     * @param req
     * @param resp
     * @throws Exception
     */
    public void delBillById(HttpServletRequest req, HttpServletResponse resp) throws Exception{
        String id = req.getParameter("billid");
        HashMap<String, String> resultMap = new HashMap<String, String>();
        if(!StringUtils.isNullOrEmpty(id)){
            boolean flag = billService.deleteBillById(id);
            if(flag){//删除成功
                resultMap.put("delResult", "true");
            }else{//删除失败
                resultMap.put("delResult", "false");
            }
        }else{
            resultMap.put("delResult", "notexit");
        }
        //把resultMap转换成json对象输出
        resp.setContentType("application/json");
        PrintWriter outPrintWriter = resp.getWriter();
        outPrintWriter.write(JSONArray.toJSONString(resultMap));
        outPrintWriter.flush();
        outPrintWriter.close();
    }

    /**
     * 获取供应商列表
     * @param req
     * @param resp
     * @throws Exception
     */
    public void getProviderList(HttpServletRequest req, HttpServletResponse resp) throws Exception{

        System.out.println("getproviderlist ========================= ");

        List<Provider> providerList = new ArrayList<Provider>();
        ProviderService providerService = new ProviderServiceImpl();
        providerList = providerService.getProviderList("","");
        //把providerList转换成json对象输出
        resp.setContentType("application/json");
        PrintWriter outPrintWriter = resp.getWriter();
        outPrintWriter.write(JSONArray.toJSONString(providerList));
        outPrintWriter.flush();
        outPrintWriter.close();
    }

    /**
     * 获取订单详细信息
     * @param request
     * @param response
     * @param url
     * @throws ServletException
     * @throws IOException
     */
    public void getBillById(HttpServletRequest request, HttpServletResponse response,String url) throws Exception {
        String id = request.getParameter("billid");
        if(!StringUtils.isNullOrEmpty(id)){
            BillService billService = new BillServiceImpl();
            Bill bill = null;
            bill = billService.getBillById(id);
            request.setAttribute("bill", bill);
            request.getRequestDispatcher(url).forward(request, response);
        }
    }

    /**
     * 修改订单信息
     * @param req
     * @param resp
     * @throws Exception
     */
    public void modify(HttpServletRequest req, HttpServletResponse resp) throws Exception{
        System.out.println("modify===============");
        String id = req.getParameter("id");
        String productName = req.getParameter("productName");
        String productDesc = req.getParameter("productDesc");
        String productUnit = req.getParameter("productUnit");
        String productCount = req.getParameter("productCount");
        String totalPrice = req.getParameter("totalPrice");
        String providerId = req.getParameter("providerId");
        String isPayment = req.getParameter("isPayment");

        Bill bill = new Bill();
        bill.setId(Integer.valueOf(id));
        bill.setProductName(productName);
        bill.setProductDesc(productDesc);
        bill.setProductUnit(productUnit);
        bill.setProductCount(new BigDecimal(productCount).setScale(2,BigDecimal.ROUND_DOWN));
        bill.setIsPayment(Integer.parseInt(isPayment));
        bill.setTotalPrice(new BigDecimal(totalPrice).setScale(2,BigDecimal.ROUND_DOWN));
        bill.setProviderId(Integer.parseInt(providerId));

        bill.setModifyBy(((User)req.getSession().getAttribute(Constants.USER_SESSION)).getId());
        bill.setModifyDate(new Date());
        boolean flag = false;
        BillService billService = new BillServiceImpl();
        flag = billService.modify(bill);
        if(flag){
            resp.sendRedirect(req.getContextPath()+"/jsp/bill.do?method=query");
        }else{
            req.getRequestDispatcher("billmodify.jsp").forward(req, resp);
        }
    }

}

8.6、完善删除供应商功能

public void deleteProviderById(HttpServletRequest req, HttpServletResponse resp) throws Exception{
        String proId = req.getParameter("proid");
        //要删除的供应商id 默认为0
        int delProviderId = 0;
        //如果要从前端传过来的参数不能转换为int类型的用户id，则给要删除的用户id赋值为0
        try {
            delProviderId = Integer.parseInt(proId);
        }catch (Exception e){
            e.printStackTrace();
            delProviderId = 0;
        }

        //万能的Map,用来存放返回前端的消息
        Map<String, String> map = new HashMap<>();
        if (delProviderId <= 0){
            map.put("delResult","notexist");
        }else{
            boolean flag = providerService.deleteProviderById(delProviderId);
            if (flag) {
                map.put("delResult", "true");
            }else {
                map.put("delResult", "false");
            }
        }

        //将map转换为JSON字符串返回
        resp.setContentType("application/json");
        PrintWriter writer = resp.getWriter();
        String result = JSONArray.toJSONString(map);
        writer.write(result);
        writer.flush();
        writer.close();
    }