Node. Js combat – Node. Js + MySQL + RESTful

Because of food, so study. Because of life, so love.

Message: The long wind and waves will sometimes, straight sail to the sea. This article has been included at github.com/likekk/-Blo… Welcome everyone star, learn together and make progress together. If there are any mistakes in the article, please point them out. The route of front-end learning and resource sharing will be planned on GitHub in the later stage.

preface

Recently learned about Node. Js related content, make a small initial summary here, to tell the truth about this blog related content, myself have had long before learning, but you know, “good memories than bad brush pot,” learned not to take notes, it would be easy to forget all, often is the result of the study to start over again, It’s a very painful process. In order to pick up what I have learned, I decided to write this blog to review what I have learned

Objective in this chapter

Node. Js backend

• Learn how to use Node. js to operate MySQL to implement simple add, delete and check changes
• Learn to define interfaces in a RESTful style

Web front end

• Learn to integrate Ajax with VUe2
• Learn to integrate AXIOS using VUe2

Project structures,

The MySQL database

Database script

Create flower information table, add flower number, flower name, flower price, flower purpose, flower material, flower language and other fields. Here we will directly use SQL scripts to create tables and add some test data.

CREATE table flowerinfo
(
    fid BIGINT auto_increment PRIMARY key not NULL COMMENT"Number".    fname varchar(20) not null COMMENT"Name".    fprice DECIMAL(16.2)  COMMENT"Price". fsituation varchar(20) not null COMMENT"Use holidays". fuse varchar(20) not null COMMENT"Flower Use". fhc varchar(20) not null COMMENT"Flower material". fword varchar(50) COMMENT"Says" )COMMENT"Flower Information Form"  INSERT into flowerinfo(fname,fprice,fhc,fuse,fsituation,fword) VALUES("All my life.".200."Rose, Champagne"."Flowers of Love".Valentine's Day."You are the only love of my life, and I will cherish you."), ("Bless you.".300."Rose, Champagne"."Flowers of Love".Valentine's Day, Mother's Day, Father's Day.."I give you my most sincere blessing, wish you happy every day."), ("All my life.".200."Rose, Champagne"."Flowers of Love".Valentine's Day."You are the only love of my life, and I will cherish you."), ("Bless you.".300."Rose, Champagne"."Flowers of Love".Valentine's Day, Mother's Day, Father's Day.."I give you my most sincere blessing, wish you happy every day.") Copy the code

Results:

Node.js backend project construction

1. Build node.js project

I will directly omit the process of building Node.js project. You can find out how to build Node.js project by yourself on Baidu or LATER I will add related content to the blog for your convenience. The established project structure is as follows:

2. Install mysql dependencies

Since we need to operate mysql, we definitely need to install dependencies. Here are three ways to install mysql dependencies.

A:

npm install cnpm --registry=https://registry.npm.taobao.org 
cnpm install mysql  // Use taobao mirror to rely on mysql
Copy the code

Method 2:

npm install mysql --save    // The current project installs mysql dependencies
Copy the code

Three:

npm install mysql -g    // Install mysql dependencies globally
Copy the code

Mysql > install mysql > install mysql > install mysql > install mysql > install mysql > install mysql > install mysql > install mysql

3. Write RESTful interfaces

Find the routes directory structure and create the flowerRouter.js file. The directory structure is as follows:

Create a connection between node.js and mysql database

let express=require('express'); // Introduce the Express dependency
let mysql=require('mysql')  // Introduce mysql dependencies
let router=express.Router();
let connection=mysql.createConnection({
    host: 'localhost'./ / host name
 user:'root'./ / account  password:'123456'./ / password  database:'flower' // The name of the connected database }); connection.connect(); // Establish a connection Copy the code

For details about the parameters, go to mysql Configuration

Ii. Complete Code:

let express=require('express'); // Introduce the Express dependency
let mysql=require('mysql')  // Introduce mysql dependencies
let router=express.Router();
let connection=mysql.createConnection({
    host: 'localhost'./ / host name
 user:'root'./ / account  password:'123456'./ / password  database:'flower' // The name of the connected database }); connection.connect(); // Establish a connection // Query all flower information router.get('/getAllFlower',(req,res,next)=>{  connection.query('select * from flowerinfo',(err,result)=>{  if(err){  throw err;  return;  }  res.send(result);  }) }); // Add flower information router.post('/addFlower',(req,res,next)=>{  let fname=req.body.fname; / / name  let fprice=req.body.fprice;/ / price  let fsituation=req.body.fsituation; / / holiday  let fuse=req.body.fuse; / / use  let fhc=req.body.fhc; / / flowers  let fword=req.body.fword; / / says  let addsql="insert into flowerinfo(fid,fname,fprice,fsituation,fuse,fhc,fword)values(0,? ,? ,? ,? ,? ,?) ";  let addsqlParams=[fname,fprice,fsituation,fuse,fhc,fword];  connection.query(addsql,addsqlParams,(err,result)=>{  if(err){  throw err;  return;  }  res.send('Added successfully! ');  }) }) // Query the flower information according to the flower id router.get('/findFlowerById',(req,res,next)=>{  let id=req.query.id;  let selectsql='select * from flowerinfo where fid=? ';  let selctParams=[id];  connection.query(selectsql,selctParams,(err,result)=>{  if (err){  throw err  }  res.send(result);  }) }) // Modify the flower information router.put('/updateFlower',(req,res,next)=>{  let id=req.body.fid;  let fname=req.body.fname; / / name  let fprice=req.body.fprice;/ / price  let fsituation=req.body.fsituation; / / holiday  let fuse=req.body.fuse; / / use  let fhc=req.body.fhc; / / flowers  let fword=req.body.fword; / / says  let updatesql='update flowerinfo set fname=? ,fprice=? ,fsituation=? ,fuse=? ,fhc=? ,fword=? where fid=? ';  let updatesqlParams=[fname,fprice,fsituation,fuse,fhc,fword,id]  connection.query(updatesql,updatesqlParams,(err,result)=>{  if (err){  throw err;  return false  }  res.send('Modified successfully! ');  }) }) // Delete the flower message router.delete('/deleteFlower',(req,res,next)=>{  let id=req.body.id;  let deletesql="delete from flowerinfo where fid=?";  let deletesqlParams=[id];  connection.query(deletesql,deletesqlParams,(err,result)=>{  if(err){  throw err;  return false;  }  res.send('Deleted successfully! ');  }) }) module.exports=router; Copy the code

There is a major bug here, that is, we do not close the connection after the connection is finished, which will waste resources and occupy CPU. Here you can try to solve the problem, because we are testing here, so there is no setting to close the connection.

Note:

Module. exports=router

4. Register the Router and set up cross-domain requests

Locate the app.js file in the directory structure to register routing and cross-domain request Settings.

App.js file code

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
 var indexRouter = require('./routes/index'); var usersRouter = require('./routes/users'); var productRouter=require('./routes/product'); var flowerRouter=require('./routes/flowerRouter') var app = express();  // view engine setup app.set('views', path.join(__dirname, 'views')); app.set('view engine'.'ejs');  app.use(logger('dev')); app.use(express.json()); app.use(express.urlencoded({ extended: false })); app.use(cookieParser()); app.use(express.static(path.join(__dirname, 'public'))); // Set the cross-domain request app.all(The '*'.function(req, res, next) {  res.header("Access-Control-Allow-Origin"."*");  res.header("Access-Control-Allow-Headers"."content-type");  res.header("Access-Control-Allow-Methods"."PUT,POST,GET,DELETE,OPTIONS");  res.header("X-Powered-By".'3.2.1');  res.header("Content-Type"."application/json; charset=utf-8");  if(req.method == "OPTIONS") {  res.send("200");  } else {  next();  } }); app.use('/', indexRouter); app.use('/users', usersRouter); app.use('/product',productRouter); app.use('/flower',flowerRouter);   // catch 404 and forward to error handler app.use(function(req, res, next) {  next(createError(404)); });  // error handler app.use(function(err, req, res, next) {  // set locals, only providing error in development  res.locals.message = err.message;  res.locals.error = req.app.get('env') = = ='development' ? err : {};   // render the error page  res.status(err.status || 500);  res.render('error'); });  module.exports = app; Copy the code

Cross-domain code:

// Set the cross-domain request
app.all(The '*'.function(req, res, next) {
    res.header("Access-Control-Allow-Origin"."*");
    res.header("Access-Control-Allow-Headers"."content-type");
    res.header("Access-Control-Allow-Methods"."PUT,POST,GET,DELETE,OPTIONS");
 res.header("X-Powered-By".'3.2.1');  res.header("Content-Type"."application/json; charset=utf-8");  if(req.method == "OPTIONS") {  res.send("200");  } else {  next();  } }); Copy the code

Web front end

The front end uses two main methods to manipulate the data, ajax and AXIOS, bringing in the necessary plug-ins. The directory structure is as follows:

Here we introduce vue.js, axios, jQuey, and create two new HTML files, which have been specified for naming convenience. Next comes the data manipulation.

Ajax vue2 integration

      
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Ajax and VUE operate mysql</title>
</head> <body> <div id="app">  <table border="1" width="800px" style="margin: 0 auto" cellspacing="0" cellpadding="0">  <tr>  <td>Serial number</td>  <td>The name of the</td>  <td>The price</td>  <td>Use holiday</td>  <td>Flowers use</td>  <td>Flowers flowers</td>  <td>says</td>  <td>operation</td>  </tr>  <template v-for="(item,index) of flowerArray">  <tr>  <td>{{index+1}}</td>  <td>{{item.fname}}</td>  <td>{{item.fprice}}</td>  <td>{{item.fsituation}}</td>  <td>{{item.fuse}}</td>  <td>{{item.fhc}}</td>  <td>{{item.fword}}</td>  <td>  <input type="button" :data-id="item.fid" value="Delete" @click="deleteFlower(item.fid)">  <input type="button" :data-id="item.fid" value="Change" @click="findFlowerById(item.fid)">  </td>  </tr>  </template>  </table>  <form> Name: <input type="text" v-model="fname"><br> Price: <input type="text" v-model="fprice"><br> Holiday: <input type="text" v-model="fsituation"><br> USES: <input type="text" v-model="fuse"><br> Material: <input type="text" v-model="fhc"><br> Says: <input type="text" v-model="fword"><br>  <span style="color: red">{{result}}</span><br>  <input type="button" @click="addFlower" value="Add flowers">  <input type="button" @click="updateFlower" value="Modify flowers">  </form> </div> <script src="Javascripts/jquery - 3.3.1. Min. Js." "></script> <script src="javascripts/vue.js"></script> <script>  var vm=new Vue({  el:'#app'. data: { fid:' '. fname:' '. fprice:' '. fsituation:' '. fuse:' '. fhc:' '. fword:' '. result:' '. flowerArray: []. },  mounted(){  this.findAllFlower();  },  methods: { findFlowerById:function (id) { // Query the flower information according to the number  this.fid=id;  $.ajax({  url:'http://localhost:3000/flower/findFlowerById'. type:'GET'. data: {id:id}  }).done((data) = >{  this.fname=data[0].fname;  this.fprice=data[0].fprice;  this.fsituation=data[0].fsituation;  this.fuse=data[0].fuse;  this.fhc=data[0].fhc;  this.fword=data[0].fword;  })  },  deleteFlower:function (id) { // Delete the flower message  $.ajax({  url:'http://localhost:3000/flower/deleteFlower'. type:"DELETE". data: { id:id  },  }).done((data) = >{   })  },  addFlower:function () { // Add flower information  $.ajax({  url:'http://localhost:3000/flower/addFlower'. type:'POST'. data: { fname:this.fname,  fprice:this.fprice,  fsituation:this.fsituation,  fuse:this.fuse,  fhc:this.fhc,  fword:this.fword,  }  }).done((data) = >{  })  },  updateFlower:function (id) { // Modify the flower information  $.ajax({  url:'http://localhost:3000/flower/updateFlower'. type:'PUT'. data: { fid:this.fid,  fname:this.fname,  fprice:this.fprice,  fsituation:this.fsituation,  fuse:this.fuse,  fhc:this.fhc,  fword:this.fword,  },  }).done((data) = >{   })  },  findAllFlower:function () { // Query all flower information  $.ajax({  url:'http://localhost:3000/flower/getAllFlower'. type:"GET". dataType:"json"  }).done((data) = >{  this.flowerArray=data;  })  }  },  })  </script> </body> </html> Copy the code

Integrate vue2 axios

In order to more convenient realization of the function and understanding, here I explain for you step by step. Strive for a good effect. Vue integration with Axios is similar to VUE integration with Ajax. If you want to learn more about Axios, you can refer to my blog axios Reference notes, which covers the use of AXIos in great detail. Comments and questions are welcome.

      
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Axios and vue operate mysql</title>
</head> <body> <div id="app">  <table border="1" width="800px" style="margin: 0 auto" cellspacing="0" cellpadding="0">  <tr>  <td>Serial number</td>  <td>The name of the</td>  <td>The price</td>  <td>Use holiday</td>  <td>Flowers use</td>  <td>Flowers flowers</td>  <td>says</td>  <td>operation</td>  </tr>  <template v-for="(item,index) of flowerArray">  <tr>  <td>{{index+1}}</td>  <td>{{item.fname}}</td>  <td>{{item.fprice}}</td>  <td>{{item.fsituation}}</td>  <td>{{item.fuse}}</td>  <td>{{item.fhc}}</td>  <td>{{item.fword}}</td>  <td>  <input type="button" :data-id="item.fid" value="Delete" @click="deleteFlower(item.fid)">  <input type="button" :data-id="item.fid" value="Change" @click="findFlowerById(item.fid)">  </td>  </tr>  </template>  </table>  <form> Name: <input type="text" v-model="fname"><br> Price: <input type="text" v-model="fprice"><br> Holiday: <input type="text" v-model="fsituation"><br> USES: <input type="text" v-model="fuse"><br> Material: <input type="text" v-model="fhc"><br> Says: <input type="text" v-model="fword"><br>  <span style="color: red">{{result}}</span><br>  <input type="button" @click="addFlower" value="Add flowers">  <input type="button" @click="updateFlower" value="Modify flowers">  </form> </div> <script src="javascripts/vue.js"></script> <script src="javascripts/axios.js"></script> <script>  var vm=new Vue({  el:'#app'. data: { fid:' '. fname:' '. fprice:' '. fsituation:' '. fuse:' '. fhc:' '. fword:' '. result:' '. flowerArray: []. },  mounted(){  this.findAllFlower();  },  methods: { findFlowerById:function (id) { // Query the flower information according to the number  this.fid=id;  axios({  url:'http://localhost:3000/flower/findFlowerById'. type:'GET'. params: {id:id}  }).then((data) = >{  console.log(data.data[0].fname);  this.fname=data.data[0].fname;  this.fprice=data.data[0].fprice;  this.fsituation=data.data[0].fsituation;  this.fuse=data.data[0].fuse;  this.fhc=data.data[0].fhc;  this.fword=data.data[0].fword;  })  },  deleteFlower:function (id) { // Delete the flower message  axios({  url:'http://localhost:3000/flower/deleteFlower'. method:"DELETE". data: { id:id  },  }).then((data) = >{  this.result=data.data;  })  },  addFlower:function () { // Add flower information  axios({  url:'http://localhost:3000/flower/addFlower'. method:'POST'. data: { fname:this.fname,  fprice:this.fprice,  fsituation:this.fsituation,  fuse:this.fuse,  fhc:this.fhc,  fword:this.fword,  }  }).then((data) = >{  this.result=data.data;  })  },  updateFlower:function (id) { // Modify the flower information  axios({  url:'http://localhost:3000/flower/updateFlower'. method:'PUT'. data: { fid:this.fid,  fname:this.fname,  fprice:this.fprice,  fsituation:this.fsituation,  fuse:this.fuse,  fhc:this.fhc,  fword:this.fword,  },  }).then((data) = >{  this.result=data.data;  })  },  findAllFlower:function () { // Query all flower information  axios({  url:'http://localhost:3000/flower/getAllFlower'. method:"GET". dataType:"json"  }).then((data) = >{  this.flowerArray=data.data;  })  }  },  })  </script> </body> </html> Copy the code

At the end

If you find this post useful, please click the “like” button for me.

For Yang Jian this warm male: really really very useful, your support will be I continue to write the article forward power, we see the next article.

“Original” | er lang shen Yang Jian

Original is not easy, don’t want to white piao. Erlang God Yang Jian, a programmer in the front of the Internet to scrape a living, focus on the front development, good at technology sharing. If you need to reprint, please contact the author or retain the original link, wechat public search Erlang God Yang Jian or scan the two-dimensional code below more convenient.

Come together to witness the growth of Erlang God Yang Jian! More good articles, technology to share in the public account below. Welcome to follow.