Background: Before the Mid-Autumn Festival, I tried to build a node environment. I ran a little. After returning, I began to write, add, delete and check with node. But our company encapsulated a lot of things. I am also the caller. Perhaps the directory structure of each company is different. I will talk about our company here.
First, I will introduce the directory structure that I understand.
Entry - entry port number
example - Business Server, Processing Code Logic, Connecting Database, etc.
Controlor -- Logical Layer
dao - Database Operating Layer
AppProvider - - - Load model layer dao layer and controller layer
model--mysql--User -- Designs database table fields
public --config -- constant pool
Public - - responseE - - Error message
Config - - App - - Connect to database
To run the node project. entry and example folders, you need to be in the running state. Command line instruction: node start
The example console is looking at business logic errors.
Step 1: First, design the table.
Create a new User file under the mysql folder of the model layer
"users" is the table name. I gave the field name, class, Student_id, age, sex, status, create_time, update_time.
Index: Here is the unique index. I set Student_id as the unique field.
Allow Null: It means not empty.
The init() method and the createTable() method are encapsulated, and I call them directly. I'm not particularly sure if they're encapsulated in our iot-cloud-core or sequelize. Forgive me for using them on the first day.
Step 2: New UserDao file in dao layer
This file mainly operates on the database. There are ways to add, delete, and modify it.
UserDao.js file content:
const DaoBase = require('iot-cloud-core').BASE.DaoBase; const Sequelize = require('sequelize'); class UserDao extends DaoBase{ //users table added data async createUsers(data){ this.checkKeyExists(data,'name','class','Student_id','age','sex'); let createData={ name:data.name, class:data.class, Student_id:data.Student_id, age:data.age, sex:data.sex, status:1, }; let res=await this.models.mysql.users.create(createData); return res; } //Obtain user table data based on student number async findUsersInfoByStudentId(data){ this.checkKeyExists(data,'Student_id'); let selectData={ raw:true, attributes:[ ['class','class'],['sex','sex'],['age','age'], ['name','name'], 'status','create_time','update_time' ], where: { Student_id:data.Student_id, } }; let res = await this.models.mysql.users.find(selectData); console.log(res); return res; } //Get all the data in the users table async findUsersList(data){ let selectData={ raw:true, attributes:[ ['id','Student_id'],['class','class'],['sex','sex'],['age','age'], ['name','name'],'status','create_time','update_time' ], where:data.where, offset:data.offset, limit:data.limit }; if(!data.where){ selectDara.where = { status:1 }; }else { selectData.where.status = 1; } let res = await this.models.mysql.users.findAll(selectData); return res; } //Get the total amount of user table data async countUsersList(data){ let selectData = { where:data.where, }; if(!data.where){ selectData.where = { status:1 }; }else{ selectData.where.status = 1; } let res = await this.models.mysql.users.count(selectData); return res; } //Getting user table data based on primary key id async findUsersInfoById(data){ this.checkKeyExists(data,'Student_id'); let selectData ={ raw:true, attributes:[ ['id','Student_id'],['class','class'],['sex','sex'],['age','age'], ['name','name'],'status','create_time','update_time' ], where:{ Student_id:data.Student_id, status:1 } }; let res = await this.models.mysql.users.find(selectData); return res; } //Logically delete user table data async deleteUsersById(data){ this.checkKeyExists(data,'Student_id'); let deleteData = { status:0 }; let condition={ where:{ Student_id:data.Student_id, status:1 } }; let res = await this.models.mysql.users.update(deleteData,condition); if(res[0] >0){ return true; }else{ return false; } } //Update User List async updataUsers(data){ this.checkKeyExists(data,'update','where'); let updateData = data.update; let condition={ where:data.where, } let res = await this.models.mysql.users.update(updateData,condition); if(res[0]>0){ return true; }else{ return false; } } } module.exports = UserDao;
Step 3: Create a new UsersControllor file at the control layer
The file name here is the method name after the interface address. The composition of the next interface address will be described in detail later.
UsersControllor.js file content:
const ControllerBase = require('iot-cloud-core').BASE.ControllerBase; const NoAuth = require('iot-cloud-core').BASE.ControllerDecorator.NoAuth; const responseE = require('../public/responseE'); const config = require('../public/config'); const ServiceManager = require('iot-cloud-fs'); class UsersController extends ControllerBase { /** * addUsers * @description Add user * @param {string} name Name * @param {string} class class * @param {string} Student_id Student ID * @param {int} age Age * @param {string} sex Gender */ @NoAuth async addUsersAction(data){ //Inspection parameters let param=data.params; this.checkKeyExists(param,'name','class','Student_id','age','sex'); //Check whether the corresponding data exists let findUsersInfoRes = await this.dao.get('users').findUsersInfoByStudentId({ Student_id:param.Student_id, }); if(!findUsersInfoRes){ //When data does not exist, add new data let creatData={ name:param.name, class:param.class, Student_id:param.Student_id, age:param.age, sex:param.sex, }; let res =await this.dao.get('users').createUsers(creatData); if(!res){ responseE.error.call(this,'Sql_Error'); } return { success: true } }else if(findUsersInfoRes && findUsersInfoRes.status == 0){ //Data has been logically deleted and updated let updataData ={ update:{ status:1, name:param.name, class:param.class, sex:param.sex, age:param.age, }, where:{ Student_id:param.Student_id, }, }; let res = await this.dao.get('users').updataUsers(updataData); if(!res){ responseE.error.call(this,'Sql_Error'); } return { success: true } }else if(findUsersInfoRes && findUsersInfoRes.status ==1){ //Data already exists, return error responseE.error.call(this,'User_Is_Exist'); } } /** * delUsers * @description delete user * @param {int} Student_id Student ID */ @NoAuth async delUsersAction(data){ //Inspection parameters let param = data.params; this.checkKeyExists(param,'Student_id'); //Check if the student number exists let findUsersInfoRes = await this.dao.get('users').findUsersInfoById({ Student_id:param.Student_id }); if(!findUsersInfoRes){ responseE.error.call(this,'Users_Is_Not_Exist'); } //Delete user data let res = await this.dao.get('users').deleteUsersById({ Student_id:param.Student_id }); if(!res){ responseE.error.call(this,'Sql_Error'); } return {success :true} } /** * updateUsers * @description Update user * @param {string} name Name * @param {string} class class * @param {string} Student_id Student ID * @param {int} age Age * @param {string} sex Gender */ @NoAuth async updateUsersAction(data){ //Inspection parameters let param = data.params; this.checkKeyExists(param,'Student_id','sex','age','class','name'); //Check if the student number exists let findUsersInfoRes = await this.dao.get('users').findUsersInfoById({ Student_id:param.Student_id }); if(!findUsersInfoRes){ responseE.error.call(this,'Users_Is_Not_Exist'); } //Update data let updateData = { update:{ name:param.name, sex:param.sex, age:param.age, class:param.class, }, where:{ Student_id:param.Student_id, status:1 } }; let res = await this.dao.get('users').updataUsers(updateData); if(!res){ responseE.error.call(this,'Sql_Error'); } return {success:true}; } /** * getUsersInfo * @description Query User Details * @param {int} Student_id Student ID */ @NoAuth async getUsersInfoAction(data){ //Inspection parameters let param =data.params; this.checkKeyExists(param,'Student_id'); let findUsersInfoRes = await this.dao.get('users').findUsersInfoById({ Student_id:param.Student_id }); if(!findUsersInfoRes){ responseE.error.call(this,'Users_Is_Not_Exist'); } return findUsersInfoRes; } /** * getUsersList * @description Get the user list * @param {json} where Condition json * */ @NoAuth async getUsersListAction(data){ let param = data.params; this.checkKeyExists(param,'where','page'); //Transform the incoming parameters into database query fields let findListData = { where:{} }; if(param.page.hasOwnProperty('currentPage')&& param.page.hasOwnProperty('pageSize')){ findListData.offset = (parseInt(param.page.currentPage) - 1 ) * parseInt(param.page.pageSize); findListData.limit = parseInt(param.page.pageSize); } for(const key in param.where){ let newKey = ''; if(param.where.hasOwnProperty(key) && config.USERS_LIST_WHERE_MAP.hasOwnProperty(key)){ newKey = config.USERS_LIST_WHERE_MAP[key]; findListData.where[newKey]= param.where[key]; } if(param.where.hasOwnProperty(key) && config.USERS_LIST_WHERE_LIKE_MAP.hasOwnProperty(key)){ newKey =config.USERS_LIST_WHERE_LIKE_MAP[key]; findListData.where[newKey] = { $like:`%${param.where[key]}%`}; } } let findListDataRes = await this.dao.get('users').findUsersList(findListData); let countUsersListRes = await this.dao.get('users').countUsersList(findListData); return { list:findListDataRes, total:countUsersListRes } } } module.exports = UsersController;
Step 4: Setting constant pools and error messages
Information config and response E files in the public folder
Cong.js file content:
var config ={ USERS_LIST_WHERE_MAP:{ 'StudentId':'id', 'name':'name', 'sex':'sex', 'age':'age', 'class':'class', }, USERS_LIST_WHERE_LIKE_MAP:{ 'name':'name', } } module.exports =config;
ResponsseE.js content:
var errorconfig={ 'Sql_Error':[10001,'sql operation failed'], 'Users_Is_Exist':[40107,'User already exists'], 'Users_Is_Not_Exist':[40108,'user does not exist'], } class ErrorTemplate{ static error(data){ let mb = errorconfig[data]; this.error(mb[1],mb[0]); } } module.exports = ErrorTemplate;
Step 5: Download Postman Test Interface for Availability
Note that here are all json data. So be sure to add {} and double quotation marks. Do not add commas to the last data, or you will report errors.
Step 6: Open Navicat and see the changes in the data in your table
I don't have screenshots anymore. This is the final result of the table after each method has been tested.
The composition of the lower interface address is introduced.
For example, call the interface address of the method that adds a user:
http://localhost:3000/openapi/custom/10002/users/addUsers
localhost: Local
3000:entry folder runs to get the port number. It can be changed in entry/config.js
openapi: Fixed
Custom: Your own custom/public custom: Project's own interface public: public interface (users, devices, etc.)
10002:appId, you can change appId in example/app/config/app.js. (If it changes here, then the appId in entry/start.js should also remember to change.)
Users: This. registerController ("users", controller_test) in example/app/model/AppProvider.js; "users" here is the user
addUsers: AdUsers of addUsersAction in example/app/controller/UsersController.js. (Action must be added.)
Conclusion:
Today, it is only simple to connect mysql database with node to complete the addition, deletion and alteration checking. There are also many problems in the process:
(1) Users that have been deleted are only logically deleted, and the database status is changed from 1 to 0 without real deletion. When querying the list of users, the data whose status is 0 should be avoided.
(2) Change user information, Student_id already exists, but the status is 0. Then change the status to 1 and other fields to updated information.
(3) Carelessness, a lot of words are written in reverse letters, errors are not reported in that detail, find errors for half a day. In fact, only words are written in reverse letters, or less commas. Attention!!