Node.js connects to MySql database to realize addition, deletion, modification and query

The query method is directly used to add, delete, modify and query without processing

Then, create index.js and api.http in the root directory of the folder (the file name can be named casually and you can recognize it). The api.http file is used to test whether the route is written successfully. You can test the get, post, delete and put request methods at the same time. You need to install the REST Client plug-in to use it

• be careful
  The get and delete request methods take values in req.query, and the parameter transfer form is directly spliced after the path
  The post and pur request modes are taken in req.body, and the parameter transfer form must comply with the strict json mode

Write code in the index.js file to build a simple server

const express = require('express') const mysql = require('mysql') // Introduce two modules for download const app = express() // Instantiate the express module app.use(express.json()) // The express.json() function is a built-in middleware function in express. It uses body parser to parse incoming requests with json payload // Test whether the server is set up successfully app.get('/', (req, res) => { res.send('You have succeeded~~') }) app.listen(3030, () => { console.log('The server is turned on at port 3030...') })

When the following results appear, the server has been set up successfully

Then encapsulate a function to connect to the database and return the connection method

// Method of encapsulating connection data function Db() { let conn = mysql.createConnection({ host: 'localhost', // Host name. The default is localhost port: '3306', // MySql connection port, 3306 is the default port number user: 'root', // User name to connect to the database password: '123456', // password database: 'demo' // Database name }) conn.connect((err) => { if (err) throw err }) return conn // Return the encapsulated connection method }

Write the specific data of the operation database in the api.http file. The complete api.http code is as follows

// api.http @url = http://localhost:3030 / / the default port number is 3030 POST {}/addData Content-Type: application/json { "username": "root", "password": "123456", "sex": "female", "age": "23" } ### This is a comment that must be written to separate different routes DELETE {}/deleteData?username=bbb ### PUT {}/updateData Content-Type: application/json { "username": "root", "age": "27" } ### GET {}/selectData?username=root

After the above preparation work is completed, the addition, deletion, modification and query of the database can be realized

Add data

// Add data app.post('/addData', (req, res) => { let { username, password, sex, age } = req.body let conn = Db() // Establish connection let sql = `insert into users(username, password, sex, age) values('$', '$', '$', '$')` conn.query(sql, (err, result) => { if(err){ console.log(err) } else{ res.send(result) } }) conn.end() // Close connection })

The data table before adding data is not executed is as follows

Click Send Request to send the request. The request result is as follows:

The data table after successful request is as follows

Delete data

// Delete data app.delete('/deleteData', (req, res) => { let { username } = req.query let conn = Db() let sql = `delete from users where username = '$'` conn.query(sql, (err, result) => { if(err){ console.log(err) } else{ res.send(result) } }) conn.end() })

The data table before deletion is as follows

The operation in the api.http file is the same as when adding data, but a delete request is sent
The deleted data table is as follows

Modify data

// Modify data app.put('/updateData', (req, res) => { let { username, age } = req.body let conn = Db() let sql = `update users set age = '$' where username = '$'` conn.query(sql, (err, result) => { if(err){ console.log(err) } else{ res.send(result) } }) conn.end() })

Before modifying the data, the data table is as follows

The operation in the api.http file is the same as when adding data, but a put request is sent
After modifying the data, the data table is as follows. It can be seen that the age has changed

Query data

// Find data app.get('/selectData', (req, res) => { let { username } = req.query let conn = Db() let sql = `select * from users where username = '$'` conn.query(sql, (err, result) => { if(err){ console.log(err) } else{ res.send(result) } }) conn.end() })

The operation in the api.http file is the same as when adding data, but you need to send a get request
The data in the data sheet are as follows:

The query results are as follows

The above implements the addition, deletion, modification and query of Node operation MySql. The complete index.js code is as follows, and the complete api.http code is above

// index.js const express = require('express') const mysql = require('mysql') // Introduce two modules for download const app = express() // Instantiate the express module app.use(express.json()) // The express.json() function is a built-in middleware function in express. It uses body parser to parse incoming requests with json payload // Method of encapsulating connection data function Db() { let conn = mysql.createConnection({ host: 'localhost', // Host name. The default is localhost port: '3306', // MySql connection port, 3306 is the default port number user: 'root', // User name to connect to the database password: '123456', // password database: 'demo' // Database name }) conn.connect((err) => { if (err) throw err }) return conn // Return the encapsulated connection method } // Test whether the server is set up successfully app.get('/', (req, res) => { res.send('You have succeeded~~') }) // Add data app.post('/addData', (req, res) => { let { username, password, sex, age } = req.body let conn = Db() // Establish connection let sql = `insert into users(username, password, sex, age) values('$', '$', '$', '$')` conn.query(sql, (err, result) => { if(err){ console.log(err) } else{ res.send(result) } }) conn.end() // Close connection }) // Delete data app.delete('/deleteData', (req, res) => { let { username } = req.query let conn = Db() let sql = `delete from users where username = '$'` conn.query(sql, (err, result) => { if(err){ console.log(err) } else{ res.send(result) } }) conn.end() }) // Modify data app.put('/updateData', (req, res) => { let { username, age } = req.body let conn = Db() let sql = `update users set age = '$' where username = '$'` conn.query(sql, (err, result) => { if(err){ console.log(err) } else{ res.send(result) } }) conn.end() }) // Find data app.get('/selectData', (req, res) => { let { username } = req.query let conn = Db() let sql = `select * from users where username = '$'` conn.query(sql, (err, result) => { if(err){ console.log(err) } else{ res.send(result) } }) conn.end() }) app.listen(3030, () => { console.log('The server is turned on at port 3030...') })

Use Promise, async and await to add, delete, modify and query

Peomise is an asynchronous loading container, which can combine async and await to convert asynchronous operations into synchronous operations, and then solve the callback problem
As above, in index_ Write code in promise.js file to create a simple server

const express = require('express') const mysql = require('mysql') const app = express() app.use(express.json()) // test app.get('/', (req, res) => { res.send('You succeeded!!') }) app.listen(4040, () => { console.log('The server starts on port 4040...') })

When the following results appear, the server has been set up successfully

Then start connecting to the database

// Connect to database const Db = mysql.createConnection({ host:'localhost', user:'root', password:'123456', database:'demo', port:3306 }) Db.connect((err)=> { if(err) throw err })

Encapsulate Promise function, realize the operation of database, and return the result through one-dimensional data

// Encapsulate Promise function function Query(sql){ return new Promise((resolve, reject) => { Db.query(sql, (err, result) => { if(!err){ resolve([null, result]) } else{ resolve([err]) } }) }) }

Encapsulate the error information processing function to uniformly process the format of error information

// Encapsulating error message handling functions function Error(options){ let { status, message, result = [] } = options return { status, message, result } }

Encapsulates the correct result function and uniformly processes the format of the returned correct result information

// Encapsulate the correct result function function Success(options){ let { status = 200, message, result} = options return { status, message, result } }

In api_Promise.http file to write the specific data of the operation database and complete api_Promise.http code is as follows

@url = http://localhost:4040 / / port 4040 is set this time POST {}/addData Content-Type: application/json { "username": "Zhang San", "password": "123456", "sex": "male", "age": "23" } ### DELETE {}/deleteData?username=Li Si ### PUT {}/updateData Content-Type: application/json { "username": "Zhang San", "age": "25" } ### GET {}/selectData?username=Zhang San

After the above preparation work is completed, the addition, deletion, modification and query of the database can be realized

Add data

// Add data app.post('/addData', async (req, res, next) => { let { username, password, sex, age } = req.body let sql = `insert into users(username, password, sex, age) values('$', '$', '$',' $')` let [err, result] = await Query(sql) if(err){ res.send(Error({ status: 500, message: 'Add failed' })) return ; } res.send(Success({ message: 'Added successfully', result })) })

The data table before adding is as follows

The added results are as follows

The data table after adding is as follows

Delete data

// Delete data app.delete('/deleteData', async(req, res, next) => { let { username } = req.query let sql = `delete from users where username = '$'` let [err, result] = await Query(sql) if(err){ res.send(Error({ status: 500, message: 'Deletion failed' })) return ; } res.send(Success({ message: 'Delete succeeded', result })) })

The contents of the data table before deletion are as follows

The data table after deletion is as follows:

Modify data

// Modify data app.put('/updateData', async(req, res, next) => { let { username, age } = req.body let sql = `update users set age = '$' where username = '$'` let [err, result] = await Query(sql) if(err){ res.send(Error({ status: 500, message: 'Modification failed' })) return ; } res.send(Success({ message: 'Modified successfully', result })) })

The data sheet before modification is as follows:

The contents of the modified data table are as follows. It can be seen that the age has changed

Find data

// Find data app.get('/selectData', async(req, res, next) => { let { username } = req.query let sql = `select * from users where username = '$'` // let sql = `select * from users` let [err, result] = await Query(sql) if(err){ res.send(Error({ status: 500, message: 'Search failed' })) return ; } res.send(Success({ message: 'Search succeeded', result })) })

The contents in the data sheet are as follows:

The results of searching data are as follows

In the above, promise combines async and await operations to add, delete, modify and query MySql, and complete index_Promise.js code is as follows, api_Promise.http the complete code is above

// index_Promise.js const express = require('express') const mysql = require('mysql') const app = express() app.use(express.json()) // Connect to database const Db = mysql.createConnection({ host:'localhost', user:'root', password:'123456', database:'demo', port:3306 }) Db.connect((err)=> { if(err) throw err }) // Encapsulate Promise function function Query(sql){ return new Promise((resolve, reject) => { Db.query(sql, (err, result) => { if(!err){ resolve([null, result]) } else{ resolve([err]) } }) }) } // Encapsulating error message handling functions function Error(options){ let { status, message, result = [] } = options return { status, message, result } } // Encapsulate the correct result function function Success(options){ let { status = 200, message, result} = options return { status, message, result } } // test app.get('/', (req, res) => { res.send('You succeeded!!') }) // Add data app.post('/addData', async (req, res, next) => { let { username, password, sex, age } = req.body let sql = `insert into users(username, password, sex, age) values('$', '$', '$',' $')` let [err, result] = await Query(sql) if(err){ res.send(Error({ status: 500, message: 'Add failed' })) return ; } res.send(Success({ message: 'Added successfully', result })) }) // Delete data app.delete('/deleteData', async(req, res, next) => { let { username } = req.query let sql = `delete from users where username = '$'` let [err, result] = await Query(sql) if(err){ res.send(Error({ status: 500, message: 'Deletion failed' })) return ; } res.send(Success({ message: 'Delete succeeded', result })) }) // Modify data app.put('/updateData', async(req, res, next) => { let { username, age } = req.body let sql = `update users set age = '$' where username = '$'` let [err, result] = await Query(sql) if(err){ res.send(Error({ status: 500, message: 'Modification failed' })) return ; } res.send(Success({ message: 'Modified successfully', result })) }) // Find data app.get('/selectData', async(req, res, next) => { let { username } = req.query let sql = `select * from users where username = '$'` // let sql = `select * from users` let [err, result] = await Query(sql) if(err){ res.send(Error({ status: 500, message: 'Search failed' })) return ; } res.send(Success({ message: 'Search succeeded', result })) }) app.listen(4040, () => { console.log('The server starts on port 4040...') })

The above are two ways to implement Node operation MySql for data addition, deletion, modification and query. If you don't understand anything, you are welcome to leave a message~~

Add new comment

For adding a comment, please log in

Login or create account

0 comments