Day2 dynamic sql
New administrator features:
Click +. If the input information does not meet the requirements, the regular verification fails will be displayed?
Click Save modification to trigger an event (function): first verify the parameters to see whether they meet the requirements. If they do not meet the rules, they will return directly without sending HTTP requests using axios.
Whether the front end or the server should verify the data? Why?
The front page is very easy to skip.
I just need to remember the address of the next request sent by the front end and the way of the request. In fact, I can use my own tools to send such a request. If only the front end is verified in your code, you can skip the verification directly.
postman, you can also write code.
If you find that the request above 2.100 carries an id, but your local one does not carry an id, why?
Any data displayed on the front-end page is transmitted to the front-end. If not, go back
How do I handle bug s?
1. Check the status code; If it fails (cross domain failure, back-end server exception (generally execute mvn clean operation first and mvn package operation again)); If it is 500, go to the error log (server, localhost log; try catch yourself)
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;
/**
* It is used to handle the addition, deletion, modification and query of the administrator module of the background management system
*/
@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);
}
}
/**
* The administrator can be vaguely queried by entering the account and nickname
* @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)));
}
/**
* Logic of adding administrator account:
* 1.Get request parameter ----- key = value JSON string
* 2.Execute specific business logic - save to database
* 3.Returns a result
* {"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);
//Whether the front end or the server should verify the data? Why?
//Verify the parameters passed from the front end, regular TODO
int code = adminService.addAdmin(addAdminBO);
if(code == 200){
//success
response.getWriter().println(gson.toJson(Result.ok()));
}else {
//fail
response.getWriter().println(gson.toJson(Result.error("The current user name is already occupied")));
}
}
/**
* Logic of login: first, you need to receive the request parameters submitted by the user
* Put the request parameters into the database for comparison to judge whether the user name and password are correct
* Returns a result to the client
* @param request
* @param response
*/
private void login(HttpServletRequest request, HttpServletResponse response) throws IOException {
//The request parameter is located in the request body and exists as a json string
//How to get the request body? Why does the request body exist in the form of inputStream
//Because the request body can store not only text type request parameters, but also binary type data file upload
String requestBody = HttpUtils.getRequestBody(request);
AdminLoginBO loginBO = gson.fromJson(requestBody, AdminLoginBO.class);
//check
if(StringUtils.isEmpty(loginBO.getEmail()) || StringUtils.isEmpty(loginBO.getPwd())){
// TODO
Result result = new Result(10000, "Parameter cannot be empty", null);
response.getWriter().println(gson.toJson(result));
return;
}
//Next, you should call the method of the model (the service and dao in the three-tier architecture are actually the further decoupling of the previous model)
//Call the method of service and return the result
int code = adminService.login(loginBO);
Result result = null;
if(code == 200){
//Login succeeded
LoginVo loginVo = new LoginVo(loginBO.getEmail(), loginBO.getEmail());
result = new Result(0, null, loginVo);
}else {
//Login failed
result = new Result(10000, "Wrong user name and password", 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);
}
}
/**
* Get the details of the current administrator account through id
* @param request
* @param response
*/
private void getAdminsInfo(HttpServletRequest request, HttpServletResponse response) throws IOException {
String id = request.getParameter("id");
//check
AllAdminVO adminVO = adminService.getAdmin(Integer.parseInt(id));
response.getWriter().println(gson.toJson(Result.ok(adminVO)));
}
/**
* Logic: 1. Get request parameters (none)
* 2.Execute the specific business logic of the current interface: all the administrator account information in the database is returned to the front-end JDBC
* 3.Return results
* {"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) {
//You need to read the mybatis.xml file
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);
//Close sqlSession even when querying, otherwise deadlock will occur
//sqlSession must not be written as a member variable and cannot be reused
sqlSession.commit();
sqlSession.close();
if(count == 1){
return 200;
}
return 404;
}
@Override
public List<AllAdminVO> allAdmins() {
//sqlSession must not be set as a member variable
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 commodity management
The background management system can manage the commodities in the foreground, such as publishing, modification, etc., and the corresponding commodity information in the foreground system will be modified synchronously.
In fact, the association between them is through the tables in the database, a modification table and a query table.
How to write three-tier architecture code:
1. Write a controller for each module. See the meaning of the name for the name. AdminServlet or AdminController, and write a service and dao at the same time
2. Every time you create a new controller or open a new module, you need to adapt and write a new service and dao
Categories, commodities and specifications can actually be written in a servlet.
If you add a category and the name finally displayed on the page is garbled in Chinese, how to analyze and debug?
1. Generally, the whole process can be divided into two stages: before saving to the database and after saving to the database;
2. Go to debug and check to see at what stage the garbled code problem has occurred. Break point ---- Let the program execute. The function of breakpoints is that when the program executes and changes the line of code, it will stop.
Next, create a new product table and specification table
One to many relationship. The maintenance of the relationship is written on one side (specification)
Commodity list:
id
name
typeId
image
description
Price ---- minimum price. If there is no price in the commodity table, you need to perform a connection query during query; If price is saved in the commodity table, you can query the document table directly; When adding or modifying, you need to maintain the data
stockNum ---- maintain a stockNum by the way
Specification sheet:
id
goodsId
name
stockNum
price
New products:
For file upload, do not convert the read stream into a string, otherwise the file will be directly damaged.
Logic of new products:
1. Save the parameters to the commodity table (price and stockNum), and in the specList, take out the data in the list for iteration, and take out the lowest price and the sum of stockNum
2. Save the data to the specification table (goodsId is required)
How to get the id of goods? Because the id of the goods table is self incremented and is maintained by the database, how to obtain the id of goods?
controller: data acquisition, encapsulation, verification, etc. call service code logic and return results
Service: the specific business logic details should be in the service layer
dao: responsible for one method by one
Delete category: think for yourself and decide how to implement this function? 1. If there are commodities below, they cannot be deleted. 2. They can be deleted, all of them can be deleted. 3. The category can be deleted, but the commodities are not deleted. You can set a default category. If the category is deleted, move the commodities to the default category
When deleting goods, you must also delete the specifications
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.Get request parameter request body json string
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("Illegal parameter ');
return;
}
goodsService.addGoods(addGoodsBO);
response.getWriter().println(gson.toJson(Result.ok()));
}
/**
* You can still use the Commons file upload described earlier
* @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);
}
}
/**
* Interface logic for querying commodity information under a certain category in background management system
* 1.Get request parameter typeId
* @param request
* @param response
*/
private void getGoodsByType(HttpServletRequest request, HttpServletResponse response) throws IOException {
String typeId = request.getParameter("typeId");
//check
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;
}
/**
* Transaction: a set of operations, either all successful or all unsuccessful
* ACID: Atomicity, consistency, isolation, persistence
*Isolation is more important: when multiple transactions access the database concurrently, the database provides a guarantee that each transaction does not interfere with each other
* Problems: dirty reading (one transaction reads the uncommitted transaction of another transaction), non repeatable reading (two queries are performed before and after a transaction, the query results are inconsistent, and the data submitted by another transaction is read), illusory reading (the number of inconsistent items before and after is newly added)
* mysql The repeatable read of can mask three kinds of problem snapshots
* @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);
//Only after the insert statement is executed, the id of goods will be encapsulated in the original goods object
goodsDao.addGoods(goods);
Integer goodsId = goods.getId();
List<Spec> specList = new ArrayList<>();
for (AddGoodsSpecBO addGoodsSpecBO : list) {
//Make a conversion and add goodsspecicbo ------ 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 What is the difference between and Integer? Basic type wrapper type java object oriented
* Default assignment 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() {
}
}