Back end small projects - simple commodity management

preface

1, Environment and tools

SpringBoot 2.5.5
JDK1.8
IntelliJ IDEA 2021.2.2 (Ultimate Edition)
Postman
MySql
Navicat Premium
Python 3.7
VSCode

2, Project requirements

Simulate the functions of e-commerce website user login, adding commodities, modifying commodity information, deleting commodities in batch and querying commodity information. The user and commodity information are stored in the database.

1. User login: the student number and password are used as the information of successful login. After successful login, the user information is returned in JASON format, and after failed login, the login failure information is returned.
2. Add commodity: if the user has logged in, it is allowed to receive RESTful requests for adding commodities. The commodity information is transmitted in JASON format with unlimited attributes. The commodity information is added to the database and the addition success information is returned; Otherwise, the request will not be executed.
   3. Modify commodity: if the user has logged in, it is allowed to receive a RESTful request to modify commodity information. In the request, the commodity information to be modified is transmitted in JASON format with unlimited attributes. Modify the corresponding data in the database according to the transmitted commodity information and return the modification success information; Otherwise, the request will not be executed.
3. Batch delete commodities: if the user has logged in, it is allowed to receive a RESTful request to delete commodity information. In the request, the id values of all commodities to be deleted are transmitted, the corresponding data in the database is deleted, and the deletion success information is returned; Otherwise, the request will not be executed.
4. Query commodity: if the user has logged in, it is allowed to receive a RESTful request to query commodity information. In the request, the name of the commodity to be queried, the page number (pages) to be provided in the page display and the maximum number of records displayed on one page (limits) are transmitted. In the database, query the records (pages-1)limits+1 to pagelimits in the commodity with commodity name including name, If it is queried, all the queried commodity information is returned in JASON format, with unlimited attributes. Otherwise, the query failure information is returned. If the user has not logged in, the request will not be executed.

3, Build database

First, create a database named Commerce in premium, create user tables, commodity tables and fields, and pay attention to the types of fields. When you first created it, because the student ID is set to int, which directly exceeds the orientation of MySql int type, you should use bigint
In order to have enough data, add data in batches in Python (I'm not familiar with SQL syntax).

import pymysql # Open database connection db = pymysql.connect(host="localhost", user="root", password="123456", database="commerce") print(db) # Create a cursor object using the cursor() method cursor = db.cursor() sql_user="insert into user(user_id,pwd) value(%s,%s)" sql_goods="insert into goods(goods_id,goods_name,price,message) value(%s,%s,%s,%s)" # Execute the SQL query using the execute() method goods_id=1001 user=[] goods=[] for user_id in range(2018329621202,2018329621301): user.append((user_id,"123456")) goods.append((goods_id,"Trade name",goods_id,"commodity"+str(goods_id))) goods_id+=1 cursor.executemany(sql_user,user) cursor.executemany(sql_goods,goods) db.commit() # Close database connection db.close()

To this end, the work of the database is completed.

4, Basic configuration of SpringBoot

Refer to the previous blog: Getting started with SpringBoot

5, Use of interceptors

In order to meet the needs of intercepting various operations of unlisted users, interceptors are used. To understand the working principle of interceptors, you must first be familiar with cookie s and session s.
Detailed explanation of cookie s and session s
First, create an interceptor, implement the HandlerInterceptor interface, and overload three methods.

Next, configure the interceptor through the class, and remember to annotate @ Configuration

6, Use of JDBC

<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency>

spring: datasource: url: jdbc:mysql://localhost:3306/Commerce?serverTimezone=UTC username: root password: 123456 driver-class-name: com.mysql.cj.jdbc.Driver server: port: 8083

@RequestMapping(path = "/jdbc") public String connect(@RequestParam(value = "user_id") String user_id) { String sql = String.format("select * from user where user_id=%s", user_id); List<Map<String, Object>> userList = jdbcTemplate.queryForList(sql); return userList.toString(); }

7, Realization of each function

The session should be set here. The normal front and back-end developers should verify who set it.

@PostMapping(path = "/login") public String login(@RequestParam(value = "user_id") String user_id, @RequestParam(value = "pwd") String pwd, HttpServletRequest request) { String sql = String.format("select * from user where user_id=%s", user_id); List<Map<String, Object>> userList = jdbcTemplate.queryForList(sql); if (pwd.equals(userList.get(0).get("pwd"))) { request.getSession().setAttribute("user_id", user_id); return JSON.toJSONString(new User(user_id, pwd, "Login succeeded!")); } else { System.out.println(JSON.toJSONString(new User(null, null, "Login failed!"))); return JSON.toJSONString(new User(null, null, "Login failed!")); } }

@RequestMapping(value = "/add", method = RequestMethod.POST, produces = "application/json;charset=UTF-8") public String add(@RequestBody JSONObject jsonParam) { String id = jsonParam.get("goods_id").toString(); String name = jsonParam.get("goods_name").toString(); String price = jsonParam.get("goods_price").toString(); String message = jsonParam.get("goods_message").toString(); System.out.println(jsonParam); String sql = String.format("insert into goods (goods_id,goods_name,price,message) values ( %s, '%s', %s, '%s')", id, name, price, message); System.out.println(sql); jdbcTemplate.execute(sql); // Encapsulate the obtained json data in one layer, and then return it to the JSONObject result = new JSONObject(); result.put("message", "Added successfully"); result.put("method", "json"); result.put("data", jsonParam); return result.toString(); }

@RequestMapping(value = "/change", method = RequestMethod.POST, produces = "application/json;charset=UTF-8") public String change(@RequestBody JSONObject jsonParam) { String id = jsonParam.get("goods_id").toString(); String name = jsonParam.get("goods_name").toString(); String price = jsonParam.get("goods_price").toString(); String message = jsonParam.get("goods_message").toString(); String sql = String.format("update goods set goods_name='%s',price=%s,message='%s' where goods_id = %s ", name, price, message, id); System.out.println(sql); jdbcTemplate.execute(sql); // Encapsulate the obtained json data in one layer, and then return it to the JSONObject result = new JSONObject(); result.put("msg", "ok"); result.put("message", "Modified successfully"); result.put("data", jsonParam); return result.toString(); }

@RequestMapping(value = "/delete", method = RequestMethod.POST, produces = "application/json;charset=UTF-8") public String delete(@RequestBody JSONObject jsonObject) { ArrayList ids = (ArrayList) jsonObject.get("ids"); for (int i = 0; i < ids.size(); i++) { System.out.println(ids.get(i)); String sql = String.format("delete from goods where goods_id=%s", ids.get(i)); jdbcTemplate.execute(sql); } JSONObject result = new JSONObject(); result.put("msg", "ok"); result.put("message", "Delete succeeded"); return result.toString(); }

@PostMapping(path = "/query") public String query(@RequestParam(value = "name") String goods_name,@RequestParam(value = "pages") int pages,@RequestParam(value = "limits") int limits) { String sql=String.format("select * from goods where goods_name='%s'",goods_name); List list = jdbcTemplate.queryForList(sql); return JSON.toJSONString(list.subList((pages-1)*limits,pages*limits)); }

To this end, all functions have been realized.

8, Summary

         This project is a very small project, but many problems have been encountered in the implementation process, mainly including the following problems:
1. When Java connects to the database and executes sql statements, all non numeric types should be enclosed in single quotes!!!
2. I didn't understand the session deeply, which led to a long delay.
3. Do not forget to add comments on the Java backend! Don't confuse, such as RequestBody and ResponseBody
         In addition, a fastjson package from Ali is also used in this project.

<dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.47</version> </dependency>

Back end novice on the road, if there are deficiencies, please give more advice

Project source code

Add new comment

0 comments