node. JS connects to MySQL database and constructs json for correct posture

Do a tidy, before also very casually introduced the package link database, and later found often connected problems, abnormal exit, and later use on-line a method to solve the problem, the URL because the bookmark is lost, sorry can not be quoted. Then there is a simple modular, so that the directory reasonable point, and then there is a note, the title of the suspicion of earning eyeballs, code I use normal here, and I think it is very good use, but does not mean that it is really the way to write, after all, I am a node rookie, great God passing there is better ways and means, please leave footprints

Node version:v0.10.34

Express version:4.9.0

Before proceeding, let's say you've built a local environment where you can see the interface:

--------------------------------------------------------------------------------------------------------

All right, keep going. First, take a look at my directory:

The controllor directory and setting.js are new, with some processing features, such as Database.jsfor manipulating the database, and dd_ for working with the data and returning the JSON Tongji.js,settings.js used to store some configuration information

If you create a new project, you need to add two lines of code in the routing (routers/index.js) to introduce our modules and configuration URLs

var express = require (' Express '); var router = Express. Router (); var dd_tongji = require ('.. /controller/dd_tongji.js '); Introduce a custom module/ **/router.get (function(req, res) {  Res.render (' index ', {title: ' Express ' }); Router.get ('/data '= router;

Then add the following code to controller/dd_tongji.js

function (req, res) {  return res.jsonp ({"Hello": "World"}) ;

Restart service after access: Http://127.0.0.1:3000/data

This way, the back end of the work is like this, the front desk access to this site can get data ~

- What about the database? What about the construction JSON?

- don't worry, look down.

----------

(i) node. js connects to MySQL database

In dd_tongji.js we want to introduce data from MySQL and process it into the JSON format we want, well, look at what's in database.js :

varMySQL = require (' MySQL '), Settings= require ('.. /settings '); Module.exports.getConnection=function () {    if(module.exports.connection) && (module.exports.connection.socket) && (module.exports.connection._ socket.readable) &&(module.exports.connection._socket.writable)) {        returnmodule.exports.connection; } console.log ((module.exports.connection)? "Unhealthy SQL CONNECTION; RE ":") + "CONNECT to SQL."); varConnection =mysql.createconnection ({host:settings.mysql.host, port:settings.mysql.port, Datab            Ase:settings.mysql.database, User:settings.mysql.user, Password:settings.mysql.password, CharSet:"UTF8"    }); Connection.connect (function(err) {if(Err) {Console.log ("SQL CONNECT ERROR:", err); } Else{Console.log ("SQL CONNECT successful.");    }    }); Connection.on ("Close",function(Err) {Console.log ("SQL CONNECTION CLOSED.");    }); Connection.on ("Error",function(Err) {Console.log ("SQL CONNECTION ERROR:." +err);    }); Module.exports.connection=connection; returnmodule.exports.connection;}; Module.exports.getConnection ();

Configuration information for the database in Settings.js, we have passed settings = require ('.. /settings '); introduction

Look at the contents of the settings.js :

Module.exports = {mysql: {          ' 127.0.0.1 ',          3306,          ' user ',          ' 123456 ',          ' MyData '       }}

Using ingest in dd_tongji.js (part of the code)

var connection = Database.getconnection ();
Connection.query (SQL, function (err, rows, fields) {
//rows is the data that can be queried by rows[i]. Field Access data
}

Since the introduction of the database is the first, the complete Dd_tongji.js code is written when the JSON is constructed.

And it seems like there's no need to close the connection. (Forget why, sorry ...) ）

Well, the database section is temporarily like this, and in a moment there will be a sample demo fetching the data, stitching the JSON, and returning.

(ii) node. JS Constructs JSON

If the URL interface is http://127.0.0.1:3000/data?genus=DEFAULT&evt=ABOUT&begin=2014-09-09&end=2014-09-20

So that means the data we need to query is the database genus Field Default,ent field is about, dates from 2014-09-09 to 2014-09-20 data

Check out the new Dd_tongji.js code:

varDatabase = require ('./database '))/*number of days passed in string fetch interval*/functionDateDiff (sDate1, SDate2) {//sDate1 and SDate2 are 2002-12-18 formats  varadate, ODate1, ODate2, Idays adate= Sdate1.split ("-") oDate1=NewDate (adate[1] + '-' + adate[2] + '-' + adate[0])//Convert to 12-18-2002 formatAdate = Sdate2.split ("-") ODate2=NewDate (adate[1] + '-' + adate[2] + '-' + adate[0]) Idays= parseint (Math.Abs (odate1-odate2)/1000/60/60/24)//Convert the difference in milliseconds to a number of daysreturnIdays}/*A string that returns a date based on a parameter*/functiongetdatestr (begindate, adddaycount) {varBegin =Date.parse (begindate); varDD =NewDate (begin); Dd.setdate (Dd.getdate ()+adddaycount);//get the date adddaycount days  vary =dd.getfullyear (); varm = Dd.getmonth () +1;//Gets the date of the current month  varD =dd.getdate (); if(m<10) m = ' 0 ' +m; if(d<10) d = ' 0 ' +D; returnY + "-" + M + "-" +D;} Exports.get_click=function(req, res) {//Console.log (DateDiff (Req.query.begin, Req.query.end));  //construct a list of dates  varDatelist = []  varBegindate =Req.query.begin; varEndDate =Req.query.end; varDatenum = DateDiff (begindate, EndDate) + 1;//date days to query   for(vari=0; i<datenum; i++) { time=getdatestr (begindate, i);  Datelist.push (Time)} console.log (Datelist);  Console.log (datelist.length); //data used to save the query to  vardata = {};  for(vari=0; i< datelist.length; i++) {Data[datelist[i]]={click_num:0, People_num:0, Average_num:0    }  }  //Defining query Statements  varsql = "SELECT * FROM click WHERE genus= '" + Req.query.genus + "' and evt= '" + req.query.evt + "' and date>= '" + req.que Ry.begin + "' and date <= '" + req.query.end + "'"console.log (SQL); //connecting to a database  varConnection =database.getconnection (); Connection.query (SQL,function(Err, rows, fields) {if(ERR)Throwerr;  for(vari=0; i < rows.length; i++) {Data[rows[i].date]={click_num:rows[i].click_num, people_num:rows[i].people_num, average:rows[i].average} }    varresult ={genus:req.query.genus, evt:req.query.evt, data:data}returnRes.jsonp (Result); });};

Not much to explain, the example is very well understood, the data initialization operation is to fill some dates without data problems.

Look at the table structure and JSON results in the database

OK, you can, if you find me writing where there is a problem, remember to notify me Oh, grateful

node. JS connects to MySQL database and constructs json for correct posture