Day2 动态sql
管理员新增功能：
点击+，如果输入的信息不符合要求，显示正则验证未通过？
点击保存修改，触发一个事件（函数）：首先对参数进行校验，看是否符合要求，不符合规则的条件下，直接就返回，不会去使用axios发送HTTP请求。
无论前端还是服务端都应该对数据进行校验？为什么呢？
前端的页面是非常容易跳过的。
我只需要记住前端接下来发送的请求的地址以及请求的方式，其实完全可以自己去工具去发送这样的一个请求，那么如果你的代码中只是前端做了校验，那么就可以直接跳过校验。
postman、还可以写代码。
如果发现2.100上面的请求会携带id，但是你本地的没有携带id，为什么？
前端页面上面显示的任何数据都是你传给前端的，如果没有，那么往前追溯
遇到bug一般我是如何处理的？
1.排查状态码；如果failed（跨域失败、后端服务器异常（一般先执行mvn clean操作，再次执行mvn package操作））；如果是500，去找错误日志（server、localhost log；自己去try catch ）
package com.cskaoyan.mall.controller;
import com.alibaba.druid.util.StringUtils;
import com.cskaoyan.mall.model.Result;
import com.cskaoyan.mall.model.bo.AddAdminBO;
import com.cskaoyan.mall.model.bo.AdminLoginBO;
import com.cskaoyan.mall.model.bo.SearchAdminBO;
import com.cskaoyan.mall.model.vo.AllAdminVO;
import com.cskaoyan.mall.model.vo.LoginVo;
import com.cskaoyan.mall.service.AdminService;
import com.cskaoyan.mall.service.AdminServiceImpl;
import com.cskaoyan.mall.utils.HttpUtils;
import com.google.gson.Gson;
import javax.servlet.ServletException;
import javax.servlet.ServletInputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;
/**
* 用来处理后台管理系统的管理员模块的增删改查等操作
*/
@WebServlet("/api/admin/admin/*")
public class AdminServlet extends HttpServlet {
private Gson gson = new Gson();
private AdminService adminService = new AdminServiceImpl();
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String requestURI = request.getRequestURI();
String action = requestURI.replace(request.getContextPath() + "/api/admin/admin/", "");
if("login".equals(action)){
login(request,response);
}else if("addAdminss".equals(action)){
addAdminss(request, response);
}else if("getSearchAdmins".equals(action)){
getSearchAdmins(request, response);
}
}
/**
* 通过输入账户和昵称可以对管理员进行模糊查询
* @param request
* @param response
*/
private void getSearchAdmins(HttpServletRequest request, HttpServletResponse response) throws IOException {
String requestBody = HttpUtils.getRequestBody(request);
SearchAdminBO searchAdminBO = gson.fromJson(requestBody, SearchAdminBO.class);
List<AllAdminVO> adminVOS = adminService.searchAdmin(searchAdminBO);
response.getWriter().println(gson.toJson(Result.ok(adminVOS)));
}
/**
* 新增管理员账号的逻辑：
* 1.获取到请求参数-----key=value json字符串
* 2.执行具体的业务逻辑----保存到数据库
* 3.返回一个结果
* {"nickname":"admin1234","email":"66678@qq.com","pwd":"Aa12345%"}
* @param request
* @param response
*/
private void addAdminss(HttpServletRequest request, HttpServletResponse response) throws IOException {
String requestBody = HttpUtils.getRequestBody(request);
AddAdminBO addAdminBO = gson.fromJson(requestBody, AddAdminBO.class);
//无论前端还是服务端都应该对数据进行校验？为什么呢？
//对前端传递过来的参数进行校验 正则 TODO
int code = adminService.addAdmin(addAdminBO);
if(code == 200){
//成功
response.getWriter().println(gson.toJson(Result.ok()));
}else {
//失败
response.getWriter().println(gson.toJson(Result.error("当前用户名已经被占用")));
}
}
/**
* 登录的逻辑：首先需要接收到用户提交过来的请求参数
* 将请求参数放置到数据库中进行比对，判断用户名、密码是否正确
* 返回一个结果给客户端
* @param request
* @param response
*/
private void login(HttpServletRequest request, HttpServletResponse response) throws IOException {
//请求参数位于请求体中，并且时以json字符串的形式存在
//如何拿到请求体 为什么请求体是以inputStream的形式存在
//因为请求体不仅可以存储文本类型的请求参数，还可以存储二进制类型数据 文件上传
String requestBody = HttpUtils.getRequestBody(request);
AdminLoginBO loginBO = gson.fromJson(requestBody, AdminLoginBO.class);
//校验
if(StringUtils.isEmpty(loginBO.getEmail()) || StringUtils.isEmpty(loginBO.getPwd())){
// TODO
Result result = new Result(10000, "参数不能为空", null);
response.getWriter().println(gson.toJson(result));
return;
}
//接下来应该调用模型的方法（三层架构里面的service和dao其实就是之前model的进一步解耦）
//调用service的方法，返回结果即可
int code = adminService.login(loginBO);
Result result = null;
if(code == 200){
//登录成功
LoginVo loginVo = new LoginVo(loginBO.getEmail(), loginBO.getEmail());
result = new Result(0, null, loginVo);
}else {
//登录失败
result = new Result(10000, "用户名、密码错误", null);
}
response.getWriter().println(gson.toJson(result));
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String requestURI = request.getRequestURI();
String action = requestURI.replace(request.getContextPath() + "/api/admin/admin/", "");
if("allAdmins".equals(action)){
allAdmins(request,response);
}else if("getAdminsInfo".equals(action)){
getAdminsInfo(request, response);
}
}
/**
* 通过id去取得当前管理员账号的详细信息
* @param request
* @param response
*/
private void getAdminsInfo(HttpServletRequest request, HttpServletResponse response) throws IOException {
String id = request.getParameter("id");
//校验
AllAdminVO adminVO = adminService.getAdmin(Integer.parseInt(id));
response.getWriter().println(gson.toJson(Result.ok(adminVO)));
}
/**
* 逻辑：1.获取请求参数（没有）
* 2.执行当前接口的具体业务逻辑：数据库里面的管理员账户信息全部返回给前端 JDBC
* 3.返回结果
* {"code":0,"data":[{"id":1,"email":"admin","nickname":"admin","pwd":"admin"}]}
* [{"id":1,"email":"admin","nickname":"admin","pwd":"admin"}]
* @param request
* @param response
*/
private void allAdmins(HttpServletRequest request, HttpServletResponse response) throws IOException {
List<AllAdminVO> adminVOS = adminService.allAdmins();
Result result = new Result(0, null, adminVOS);
response.getWriter().println(gson.toJson(result));
}
}
package com.cskaoyan.mall.service;
import com.cskaoyan.mall.dao.AdminDao;
import com.cskaoyan.mall.model.Admin;
import com.cskaoyan.mall.model.bo.AddAdminBO;
import com.cskaoyan.mall.model.bo.AdminLoginBO;
import com.cskaoyan.mall.model.bo.SearchAdminBO;
import com.cskaoyan.mall.model.vo.AllAdminVO;
import com.cskaoyan.mall.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.ArrayList;
import java.util.List;
public class AdminServiceImpl implements AdminService {
@Override
public int login(AdminLoginBO loginBO) {
//需要读取mybatis.xml文件
SqlSession sqlSession = MybatisUtils.openSession();
AdminDao adminDao = sqlSession.getMapper(AdminDao.class);
Admin admin = new Admin(null, loginBO.getEmail(), loginBO.getPwd(), null);
int count = adminDao.count(admin);
//即便时查询，也要把sqlSession给关闭，否则会出现死锁问题
//sqlSession一定不要写成成员变量，不能复用
sqlSession.commit();
sqlSession.close();
if(count == 1){
return 200;
}
return 404;
}
@Override
public List<AllAdminVO> allAdmins() {
//sqlSession一定不能设置成成员变量
SqlSession sqlSession = MybatisUtils.openSession();
AdminDao adminDao = sqlSession.getMapper(AdminDao.class);
List<Admin> adminList = adminDao.allAdmins(new Admin());
sqlSession.commit();
sqlSession.close();
List<AllAdminVO> adminVOS = new ArrayList<>();
for (Admin admin : adminList) {
AllAdminVO adminVO = new AllAdminVO(admin.getId(), admin.getUsername(), admin.getPassword(), admin.getNickname());
adminVOS.add(adminVO);
}
return adminVOS;
}
@Override
public int addAdmin(AddAdminBO addAdminBO) {
SqlSession sqlSession = MybatisUtils.openSession();
AdminDao adminDao = sqlSession.getMapper(AdminDao.class);
Admin admin = new Admin(null, addAdminBO.getEmail(), addAdminBO.getPwd(), addAdminBO.getNickname());
try {
adminDao.addAdmin(admin);
return 200;
}catch (Exception e){
}finally {
sqlSession.commit();
sqlSession.close();
}
return 500;
}
@Override
public AllAdminVO getAdmin(int id) {
SqlSession sqlSession = MybatisUtils.openSession();
AdminDao adminDao = sqlSession.getMapper(AdminDao.class);
Admin admin = new Admin(id, null, null, null);
List<Admin> adminList = adminDao.allAdmins(admin);
sqlSession.commit();
sqlSession.close();
admin = adminList.get(0);
AllAdminVO adminVO = new AllAdminVO(admin.getId(), admin.getUsername(), admin.getPassword(), admin.getNickname());
return adminVO;
}
@Override
public List<AllAdminVO> searchAdmin(SearchAdminBO searchAdminBO) {
SqlSession sqlSession = MybatisUtils.openSession();
AdminDao adminDao = sqlSession.getMapper(AdminDao.class);
Admin admin = new Admin(null, searchAdminBO.getEmail(), null, searchAdminBO.getNickname());
List<Admin> adminList = adminDao.allAdmins(admin);
List<AllAdminVO> adminVOS = new ArrayList<>();
for (Admin ad : adminList) {
AllAdminVO adminVO = new AllAdminVO(ad.getId(), ad.getUsername(), ad.getPassword(), ad.getNickname());
adminVOS.add(adminVO);
}
return adminVOS;
}
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cskaoyan.mall.dao.AdminDao">
<select id="count" parameterType="com.cskaoyan.mall.model.Admin" resultType="java.lang.Integer">
select count(*) from admin where username = #{username} and password = #{password}
</select>
<select id="allAdmins" resultType="com.cskaoyan.mall.model.Admin" parameterType="com.cskaoyan.mall.model.Admin">
select id,username,password,nickname from admin
<where>
<if test="id != null">
id = #{id}
</if>
<if test="username != null and username != ''">
and username like concat("%", #{username}, "%")
</if>
<if test="nickname != null and nickname != ''">
and nickname like concat("%", #{nickname}, "%")
</if>
</where>
</select>
<insert id="addAdmin" parameterType="com.cskaoyan.mall.model.Admin">
insert into admin values (null, #{username}, #{password}, #{nickname})
</insert>
</mapper>
Day3 商品管理
后台管理系统中可以对前台的商品进行管理，比如发布、修改等，那么相对应的前台系统中商品信息也会随着同步发生修改。
实际上它们之间的关联就是通过数据库里面的表，一个修改表，一个查询表。
三层架构代码如何编写：
1.每个模块写一个controller，命名 见名知意。AdminServlet 或者AdminController，同时配套写一个service、dao
2.每新建一个新的controller，或者开启一个新的模块，那么都需要适配写一个新的service、dao
类目、商品、规格其实都可以写在一个servlet中。
如果添加类目，最终显示在页面上的名称中文乱码，如何分析，如何debug？
1.大体可以将整个过程先分为两个阶段，保存到数据库之前，一个是保存到数据库之后；
2.通过去debug排查，看一下究竟在哪个阶段它就已经出现了乱码问题。打断点------让程序执行。断点的作用是当程序执行的时候，运行到改行代码，那么会停顿下来。
接下来去新建商品表和规格表
一对多的关系。关系的维护写在多的一方（规格）
商品表：
id
name
typeId
image
description
price ------最低 如果商品表不存price，需要在查询的时候进行连接查询；如果商品表存price，直接查询单表即可；新增或者修改的时候，需要去维护一下数据即可
stockNum----顺带维护一个stockNum
规格表：
id
goodsId
name
stockNum
price
新增商品：
关于文件上传，一定不要去执行读取流转换成字符串操作，否则文件直接损坏。
新增商品的逻辑：
1.将参数保存到商品表（price、stockNum），在specList中的，将list里面的数据取出来迭代，取出最低价格，stockNum之和
2.将数据保存到规格表（里面需要goodsId）
怎么获取goods的id呢？因为商品表的id是自增的，是交给数据库去维护的，如何获取到goods 的id呢？
controller：数据的获取、封装、校验等，调用service代码逻辑，返回结果
service：具体的业务逻辑细节都应该在service层
dao：负责一个一个的方法
删除类目：自己思考决定如何实现该功能？1.如果下面有商品，那么不能删除 2.可以删除，全部都删除 3.可以删除类目，但是商品不删除，可以设置一个缺省的分类，如果类目删除了，把商品移到缺省类目中
删除商品的同时肯定也需要把规格一并删除
package com.cskaoyan.mall.controller;
import com.cskaoyan.mall.model.Result;
import com.cskaoyan.mall.model.Type;
import com.cskaoyan.mall.model.bo.AddGoodsBO;
import com.cskaoyan.mall.model.vo.GetGoodsByTypeVO;
import com.cskaoyan.mall.service.GoodsService;
import com.cskaoyan.mall.service.GoodsServiceImpl;
import com.cskaoyan.mall.utils.FileUploadUtils;
import com.cskaoyan.mall.utils.HttpUtils;
import com.google.gson.Gson;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.Map;
@WebServlet("/api/admin/goods/*")
public class GoodsServlet extends HttpServlet {
private Gson gson = new Gson();
private GoodsService goodsService = new GoodsServiceImpl();
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String requestURI = request.getRequestURI();
String action = requestURI.replace(request.getContextPath() + "/api/admin/goods/", "");
if("imgUpload".equals(action)){
imgUpload(request, response);
}else if("addGoods".equals(action)){
addGoods(request, response);
}
}
private void addGoods(HttpServletRequest request, HttpServletResponse response) throws IOException {
//1.获取请求参数 请求体 json字符串
String requestBody = HttpUtils.getRequestBody(request);
AddGoodsBO addGoodsBO = null;
try {
addGoodsBO = gson.fromJson(requestBody, AddGoodsBO.class);
}catch (Exception e){
response.getWriter().println(gson.toJson(Result.error("参数不合法")));
return;
}
goodsService.addGoods(addGoodsBO);
response.getWriter().println(gson.toJson(Result.ok()));
}
/**
* 还是使用我们之前介绍的commons-fileupload来处理即可
* @param request
* @param response
*/
private void imgUpload(HttpServletRequest request, HttpServletResponse response) throws IOException {
Map<String, Object> map = FileUploadUtils.parseRequest(request);
String file = (String) map.get("file");
response.getWriter().println(gson.toJson(Result.ok(file)));
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String requestURI = request.getRequestURI();
String action = requestURI.replace(request.getContextPath() + "/api/admin/goods/", "");
if("getType".equals(action)){
getType(request, response);
}else if("getGoodsByType".equals(action)){
getGoodsByType(request, response);
}
}
/**
* 后台管理系统中查询某个分类下商品信息的接口逻辑
* 1.获取请求参数typeId
* @param request
* @param response
*/
private void getGoodsByType(HttpServletRequest request, HttpServletResponse response) throws IOException {
String typeId = request.getParameter("typeId");
//校验
List<GetGoodsByTypeVO> goodsByTypeVOS = goodsService.getGoodsByType(Integer.parseInt(typeId));
response.getWriter().println(gson.toJson(Result.ok(goodsByTypeVOS)));
}
private void getType(HttpServletRequest request, HttpServletResponse response) throws IOException {
List<Type> typeList = goodsService.getType();
response.getWriter().println(gson.toJson(Result.ok(typeList)));
}
}
package com.cskaoyan.mall.service;
import com.cskaoyan.mall.dao.GoodsDao;
import com.cskaoyan.mall.model.Goods;
import com.cskaoyan.mall.model.Spec;
import com.cskaoyan.mall.model.Type;
import com.cskaoyan.mall.model.bo.AddGoodsBO;
import com.cskaoyan.mall.model.bo.AddGoodsSpecBO;
import com.cskaoyan.mall.model.vo.GetGoodsByTypeVO;
import com.cskaoyan.mall.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.ArrayList;
import java.util.List;
public class GoodsServiceImpl implements GoodsService {
@Override
public List<Type> getType() {
SqlSession sqlSession = MybatisUtils.openSession();
GoodsDao goodsDao = sqlSession.getMapper(GoodsDao.class);
List<Type> types = goodsDao.getType();
sqlSession.commit();
sqlSession.close();
return types;
}
@Override
public List<GetGoodsByTypeVO> getGoodsByType(int typeId) {
SqlSession sqlSession = MybatisUtils.openSession();
GoodsDao goodsDao = sqlSession.getMapper(GoodsDao.class);
List<Goods> goodsList = goodsDao.getGoodsByType(typeId);
sqlSession.commit();
sqlSession.close();
List<GetGoodsByTypeVO> goodsByTypeVOS = new ArrayList<>();
for (Goods goods : goodsList) {
goodsByTypeVOS.add(new GetGoodsByTypeVO(goods.getId(),goods.getName(), goods.getTypeId(),goods.getImage(), goods.getPrice(), goods.getStockNum()));
}
return goodsByTypeVOS;
}
/**
* 事务：一组操作，要么全部成功，要么全部不成功
* ACID：原子性、一致性、隔离性、持久性
*隔离性更为重要一些：多个事务并发访问数据库时，数据库给每个事务提供互不干扰的一种保障
* 问题：脏读（一个事务读取了另外一个事务未提交的事务）、不可重复读（在一个事务内前后进行两次查询，查询的结果不一致，读取的是另外事务已提交的数据）、虚幻读（前后条数不一致 新增）
* mysql的repeatable read可以屏蔽三种问题 快照
* @param addGoodsBO
*/
@Override
public void addGoods(AddGoodsBO addGoodsBO) {
List<AddGoodsSpecBO> list = addGoodsBO.getSpecList();
int stockNum = 0;
double price = Double.MAX_VALUE;
for (AddGoodsSpecBO addGoodsSpecBO : list) {
if(addGoodsSpecBO.getUnitPrice() < price){
price = addGoodsSpecBO.getUnitPrice();
}
stockNum += addGoodsSpecBO.getStockNum();
}
SqlSession sqlSession = MybatisUtils.openSession();
GoodsDao goodsDao = sqlSession.getMapper(GoodsDao.class);
Goods goods = new Goods(null, addGoodsBO.getName(), addGoodsBO.getTypeId(), addGoodsBO.getImg(), addGoodsBO.getDesc(), price, stockNum);
//只需要执行完插入语句，那么goods的id就会封装到原先的goods对象中
goodsDao.addGoods(goods);
Integer goodsId = goods.getId();
List<Spec> specList = new ArrayList<>();
for (AddGoodsSpecBO addGoodsSpecBO : list) {
//做一个转换，将addGoodsSpecBo-----Spec
specList.add(new Spec(null, goodsId, addGoodsSpecBO.getSpecName(), addGoodsSpecBO.getStockNum(), addGoodsSpecBO.getUnitPrice()));
}
int i = 1 / 0;
goodsDao.addSpeces(specList);
sqlSession.commit();
sqlSession.close();
}
}
package com.cskaoyan.mall.dao;
import com.cskaoyan.mall.model.Goods;
import com.cskaoyan.mall.model.Spec;
import com.cskaoyan.mall.model.Type;
import java.util.List;
public interface GoodsDao {
List<Type> getType();
List<Goods> getGoodsByType(int typeId);
void addGoods(Goods goods);
void addSpeces(List<Spec> specList);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cskaoyan.mall.dao.GoodsDao">
<select id="getType" resultType="com.cskaoyan.mall.model.Type">
select id,name from type;
</select>
<select id="getGoodsByType" parameterType="java.lang.Integer" resultType="com.cskaoyan.mall.model.Goods">
select id,name,typeId,image,description,price,stockNum from goods where typeId = #{typeId}
</select>
<insert id="addGoods" useGeneratedKeys="true" keyProperty="id" parameterType="com.cskaoyan.mall.model.Goods">
insert into goods values (null, #{name}, #{typeId}, #{image}, #{description}, #{price}, #{stockNum})
</insert>
<insert id="addSpeces" parameterType="com.cskaoyan.mall.model.Spec">
insert into spec values
<foreach collection="list" item="sp" separator=",">
(null, #{sp.goodsId}, #{sp.name}, #{sp.stockNum}, #{sp.price})
</foreach>
</insert>
</mapper>
package com.cskaoyan.mall.model.bo;
import java.util.List;
public class AddGoodsBO {
private String img;
private String name;
private Integer typeId;
private String desc;
private List<AddGoodsSpecBO> specList;
public void setImg(String img) {
this.img = img;
}
public void setName(String name) {
this.name = name;
}
public Integer getTypeId() {
return typeId;
}
public void setTypeId(Integer typeId) {
this.typeId = typeId;
}
public void setDesc(String desc) {
this.desc = desc;
}
public String getImg() {
return img;
}
public String getName() {
return name;
}
public String getDesc() {
return desc;
}
public List<AddGoodsSpecBO> getSpecList() {
return specList;
}
public void setSpecList(List<AddGoodsSpecBO> specList) {
this.specList = specList;
}
}
package com.cskaoyan.mall.model.bo;
public class AddGoodsSpecBO {
private Double unitPrice;
private String specName;
private Integer stockNum;
public Double getUnitPrice() {
return unitPrice;
}
public void setUnitPrice(Double unitPrice) {
this.unitPrice = unitPrice;
}
public String getSpecName() {
return specName;
}
public void setSpecName(String specName) {
this.specName = specName;
}
public Integer getStockNum() {
return stockNum;
}
public void setStockNum(Integer stockNum) {
this.stockNum = stockNum;
}
}
package com.cskaoyan.mall.model.vo;
/**
* int和Integer的区别是啥？ 基本类型 包装类型 java 面向对象
* 默认赋值 int---0 Integer null
*/
public class GetGoodsByTypeVO {
private Integer id;
private String name;
private Integer typeId;
private String img;
private Double price;
private Integer stockNum;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getTypeId() {
return typeId;
}
public void setTypeId(Integer typeId) {
this.typeId = typeId;
}
public String getImg() {
return img;
}
public void setImg(String img) {
this.img = img;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public Integer getStockNum() {
return stockNum;
}
public void setStockNum(Integer stockNum) {
this.stockNum = stockNum;
}
public GetGoodsByTypeVO(Integer id, String name, Integer typeId, String img, Double price, Integer stockNum) {
this.id = id;
this.name = name;
this.typeId = typeId;
this.img = "http://localhost:8084/" + img;
this.price = price;
this.stockNum = stockNum;
}
public GetGoodsByTypeVO() {
}
}