First recognized NodeJS server development (Express + MySQL), nodejsmysql

First recognized NodeJS server development (Express + MySQL), nodejsmysql

NodeJS is undoubtedly a milestone for the front-end. as it becomes increasingly popular today, mastering NodeJS is no longer a plus item, but a skill that must be mastered by front-end siege engineers. This article will work with colleagues to complete an entry-level server application based on Express + MySQL, that is, a simple CRUD operation can be performed on a table in the database. However, I am brave enough to think that this application can give backend developers who are not familiar with backend development a general idea of using Node for backend development.

Prepare the Express project environment

1. Install the express and express project seed generator (what? You asked why node. JS is not installed in step 1 ..)

Install express

npm install express -g

Install express

npm install express-generator -g

1. Create a project. Go to the project directory and run the command

   express projectName

The expresst project seed generator will help us generate the corresponding project structure of express, as shown below:

• /Bin: used for application startup
• /Public: static Resource Directory
• /Routes: the controller directory.
• /Views: jade template Directory, which can be considered as a view directory
• Main file of app. js Program

1. Go to the project, install the dependency, and then start the application.

In the project root directory, use npm install to install dependencies and use npm start to start the application. After that, you can see the following interface in the command line tool. You can access the default page of our application in the browser.

 

Prepare the MySQL Environment

1. Of course, you must first prepare the MySQL environment. For more information, see logging for mysql.

2. Create a table

After MySQL is installed, go to the database and create the table (such as user) to be used. The structure is as follows:

1. Install the MySQL driver of Node (Please allow installation of X ...)

Add "mysql": "latest" in dependencies of package. json, and run npm install to install dependencies.

Write related code and integrate Express + MySQL

1. First, we should first create several directories, which can be easily divided into lower layers (can we see that I am still very careful ?)

Add three directories to the project root directory:

Util-tool Method

Conf-Configuration

Dao-interaction with databases

Project Structure after completion

2. Compile the mySQL database connection configuration in the conf directory.

// Conf/db. js // MySQL database connection configuration module. exports = {mysql: {host: '100. 0.0.1 ', user: 'root', password: '', database: 'test', // The user table created earlier is located in this database port: 3306 }};

1. Write crud SQL statements

   // Dao/userSqlMapping. js
   // Crud SQL statement
   Var user = {
   Insert: 'insert INTO user (id, name, age) VALUES (0 ,?,?) ',
   Update: 'Update user set name = ?, Age =? Where id =? ',
   Delete: 'delete from user where id =? ',
   QueryById: 'select * from user where id =? ',
   QueryAll: 'select * from user'
   };

   Module. exports = user;

2. Add routes and implement database CRUD

Add a route entry in/routes/users. js using the specific implementation of C (ADD ).

// Add User router. get ('/adduser', function (req, res, next) {userDao. add (req, res, next );});

Implement the add method in userDao

// Dao/userDao. js // implement interaction with MySQL var mysql = require ('mysql'); var $ conf = require ('.. /conf '); var $ util = require ('.. /util '); var $ SQL = require ('. /userSqlMapping '); // use the connection pool to Improve the Performance of var pool = mysql. createPool ($ util. extend ({}, $ conf. mysql); // return the simple encapsulation var jsonWrite = function (res, ret) {if (typeof ret = 'undefined') {res. json ({code: '1', msg: 'Operation failed'});} else {res. json (ret) ;}}; modul E. exports = {add: function (req, res, next) {pool. getConnection (function (err, connection) {// obtain the parameter var param = req. query | req. params; // create a connection and INSERT a value to the table. // 'insert INTO user (id, name, age) VALUES (0 ,?,?) ', Connection. query ($ SQL. insert, [param. name, param. age], function (err, result) {if (result) {result = {code: 200, msg: 'added successful'};} // In json format, return the operation result to the front-end page jsonWrite (res, result); // release the connection. release ();});});}};

1. Test whether the integration is successful

Because the preceding implementation is a get request's add method, you can use the address directly in the browser to access the route, http: // localhost: 3000/users/addUser? Name = xyz & age = 18. If you get the following JSON return or see the data inserted above in the data table, the integration is successful.

1. Similarly, to implement other CRUD methods, the complete routes/user. js is:

   Var express = require ('express ');
   Var router = express. Router ();

   Var userDao = require ('../dao/userDao ');

   /* GET users listing .*/
   Router. get ('/', function (req, res, next ){
   Res. send ('respond with a resource ');
   });

   // Add users
   // TODO supports both get and post
   Router. get ('/adduser', function (req, res, next ){
   UserDao. add (req, res, next );
   });

   Router. get ('/queryall', function (req, res, next ){
   UserDao. queryAll (req, res, next );
   });

   Router. get ('/query', function (req, res, next ){
   UserDao. queryById (req, res, next );
   });

   Router. get ('/deleteuser', function (req, res, next ){
   UserDao. delete (req, res, next );
   });

   Router. post ('/updateuser', function (req, res, next ){
   UserDao. update (req, res, next );
   });

   Module. exports = router;

The complete userDao. js is

// Dao/userDao. js // implement interaction with MySQL var mysql = require ('mysql'); var $ conf = require ('.. /conf/db'); var $ util = require ('.. /util '); var $ SQL = require ('. /userSqlMapping '); // use the connection pool to Improve the Performance of var pool = mysql. createPool ($ util. extend ({}, $ conf. mysql); // return the simple encapsulation var jsonWrite = function (res, ret) {if (typeof ret = 'undefined') {res. json ({code: '1', msg: 'Operation failed'});} else {res. json (ret) ;}}; module. Exports = {add: function (req, res, next) {pool. getConnection (function (err, connection) {// obtain the parameter var param = req. query | req. params; // create a connection and INSERT a value to the table. // 'insert INTO user (id, name, age) VALUES (0 ,?,?) ', Connection. query ($ SQL. insert, [param. name, param. age], function (err, result) {if (result) {result = {code: 200, msg: 'added successful'};} // In json format, return the operation result to the front-end page jsonWrite (res, result); // release the connection. release () ;}) ;}, delete: function (req, res, next) {// delete by Id pool. getConnection (function (err, connection) {var id = + req. query. id; connection. query ($ SQL. delete, id, function (err, result) {if (result. affectedRows> 0) {result = {code: 200, msg: 'deleted successfully'};} else {result = void 0;} jsonWrite (res, result); connection. release () ;}) ;}, update: function (req, res, next) {// update by id // For simplicity, both the name and age parameters var param = req are required. body; if (param. name = null | param. age = null | param. id = null) {jsonWrite (res, undefined); return;} pool. getConnection (function (err, connection) {connection. query ($ SQL. update, [param. name, param. age, + param. id], function (err, result) {// use the page to jump to the prompt if (result. affectedRows> 0) {res. render ('suc', {result: result}); // The second parameter can be directly used in jade} else {res. render ('fail ', {result: result});} connection. release () ;}) ;}, queryById: function (req, res, next) {var id = + req. query. id; // In order to piece together the correct SQL statement, we need to convert it to an integer pool. getConnection (function (err, connection) {connection. query ($ SQL. queryById, id, function (err, result) {jsonWrite (res, result); connection. release () ;}) ;}, queryAll: function (req, res, next) {pool. getConnection (function (err, connection) {connection. query ($ SQL. queryAll, function (err, result) {jsonWrite (res, result); connection. release ();});});}};

Except for the update test, other get requests can be directly tested using the address + parameter in the browser. To simulate post requests, the following jade template (a template engine supported by Express) is used ),

Create three new jade files in the/views directory.

UpdateUser. jadeextends layoutblock content h1 update user information form (method = 'post', action = '/p/users/updateuser') div. form-row label span ID: input (type = 'text', name = 'id') div. form-row label span name: input (type = 'text', name = 'name') div. form-row label span age: input (type = 'text', name = 'age') div. form-row input (type = 'submit ')

Suc. jade

Block content h1 operation successful! Pre # {JSON. stringify (result )}

Fail. jade

Block content h1 operation failed! Pre # {JSON. stringify (result )}

Update test results

 

 

Finally, if you are using an IDE such as idea or webStrom, you do not need to install the express and express project seed generators. The two ides can directly create NodeJS projects.

Summary:

A helloWorld of Express is almost complete. You can download the source code of this example from http://pan.baidu.com/s/1jgvd4bc. More Express functions (such as logs and automated testing) will be available soon!