Servlet程序连接jdbc Postman 方法查询！

-- 1)查询注册资金在80到500万之间的餐饮类的企业信息和企业类型。
select c.*,c2.name from company c left join com_type c2 on c.tid = c2.id
where c.salary between 80 and 500 and c2.name='餐饮';

-- 2)查询注册日期以‘1’结尾的企业信息和企业类型。
select c.*,c2.name from company c left join com_type c2 on c.tid = c2.id
where c.hiredate like '%1';

-- 3)使用左连接查询倒闭的企业信息和企业类型。
select c.*,c2.name from company c left join com_type c2 on c.tid = c2.id
where c.zt='倒闭';

-- 4)查询2002-01-01以后注册的企业信息。
select c.*,c2.name from company c left join com_type c2 on c.tid = c2.id
where c.hiredate > '2002-01-01';

-- 5)查询每个类型的企业数量，平均注册资金，显示企业类型，数量，平均注册资金。
select c2.name,count(c.id),avg(salary) from company c left join com_type c2 on c.tid = c2.id
group by c.tid;

-- 6)把‘传智’的运营状态改为倒闭，并且注册资金上涨20%。
update company set zt='倒闭',salary=salary+salary*0.2
where name='传智';

1)完成添加企业信息功能，并通过postMan工具测试成功
a.添加的企业运营状态只能是‘正常’或者‘倒闭’，否则抛出异常信息：
状态值错
b.添加的企业名不能重复，否则抛出异常信息：名称重复，换一个
c.添加成功
insert into company values (null ,?,?,?,?,?);

@WebServlet("/AddAllServlet")
public class AddAllServlet extends HttpServlet {
    CompanyDao dao=new CompanyDao();
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //获取参数
        String name = request.getParameter("name");
        String salary = request.getParameter("salary");
        String zt = request.getParameter("zt");
        String hiredate = request.getParameter("hiredate");
        String tid = request.getParameter("tid");

        if (zt.equals("正常")||zt.equals("倒闭")){

            List<Company> list = dao.showAll();
            for (Company company : list) {
                if (company.getName().equals(name)){
                    response.sendError(412,"名字重复换一个");
                    return;
                }
            }
            //调用dao层
            dao.add(name,salary,zt,hiredate,tid);
            //重定向到输出
            response.sendRedirect("ShowAllServlet");
        }else{
            response.sendError(412,"状态值错误");
        }



    }
}

2)使用左连接查询每个类型的企业数量，显示企业数量，类型名称，并通过postMan
工具测试成功。
select c2.name,count(c.id) from company c left join com_type c2 on c.tid = c2.id
group by c.tid;

@WebServlet("/ShowCountServlet")
public class ShowCountServlet extends HttpServlet {
    CompanyDao dao=new CompanyDao();
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //解决乱码
        response.setContentType("text/html;charset=UTF-8");
        //调用dao层的showAll
        List<CompanyCount> list = dao.showCount();
        //将list转换成json字符串
        ObjectMapper mapper = new ObjectMapper();
        String json = mapper.writeValueAsString(list);
        //将字符串输出到浏览器上
        response.getWriter().write(json);

    }
}

3)查询所有企业的信息和企业类型，并通过postMan工具测试成功。
select c.*,c2.name tname from company c left join com_type c2 on c.tid = c2.id;

@WebServlet("/ShowAllServlet")
public class ShowAllServlet extends HttpServlet {
    CompanyDao dao=new CompanyDao();
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //解决乱码
        response.setContentType("text/html;charset=UTF-8");
        //调用dao层的showAll
        List<Company> list = dao.showAll();
        //将list转换成json字符串
        ObjectMapper mapper = new ObjectMapper();
        String json = mapper.writeValueAsString(list);
        //将字符串输出到浏览器上
        response.getWriter().write(json);

    }
}

4)查询id为1003的企业信息和企业类型，并通过postMan工具测试成功
select c.*,c2.name tname from company c left join com_type c2 on c.tid = c2.id
where c.id=1003;

@WebServlet("/ShowIdServlet")
public class ShowIdServlet extends HttpServlet {
    CompanyDao dao=new CompanyDao();
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //解决乱码
        response.setContentType("text/html;charset=UTF-8");
        request.setCharacterEncoding("UTF-8");
        //获取参数
        String id = request.getParameter("id");
        //调用dao层的showAll
        List<Company> list = dao.showId(id);
        //将list转换成json字符串
        ObjectMapper mapper = new ObjectMapper();
        String json = mapper.writeValueAsString(list);
        //将字符串输出到浏览器上
        response.getWriter().write(json);

    }
}

5)删除所有倒闭的企业信息，并通过postMan工具测试成功
delete from company where zt='倒闭';

@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
    CompanyDao dao=new CompanyDao();
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //获取参数
        String zt = request.getParameter("zt");

        //调用dao层
        dao.delete(zt);
        //重定向show
        response.sendRedirect("ShowAllServlet");


    }
}