node.js_mysql_advanced

/**
 * Module dependencies.
 */

var express = require('express');
var routes = require('./routes');
var user = require('./routes/user');
var http = require('http');
var path = require('path');
var mysql = require('mysql');
var connection = mysql.createConnection({
    host    :'localhost',
    port : 3306,
    user : 'terry',
    password : 'asdf1234',
    database:'terry'
});
connection.connect(function(err) {
    if (err) {
        console.error('mysql connection error');
        console.error(err);
        throw err;
    }
});

var app = express();

// all environments
app.set('port', process.env.PORT || 3000);
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
app.use(express.favicon());
app.use(express.logger('dev'));
app.use(express.json());
app.use(express.urlencoded());
app.use(express.methodOverride());
app.use(express.cookieParser('your secret here'));
app.use(express.session());
app.use(app.router);
app.use(express.static(path.join(__dirname, 'public')));

// development only
if ('development' == app.get('env')) {
  app.use(express.errorHandler());
}

// insert
app.post('/users',function(req,res){
    var user = {'userid':req.body.userid,
                'name':req.body.name,
                'address':req.body.address};
    var query = connection.query('insert into users set ?',user,function(err,result){
        if (err) {
            console.error(err);
            throw err;
        }
        console.log(query);
        res.send(200,'success');
    });
});

// insert with transaction
app.post('/userstx',function(req,res){
    var user = {'userid':req.body.userid,
        'name':req.body.name,
        'address':req.body.address};

    connection.beginTransaction(function(err) {
        if (err) {
            throw err;
        }
        connection.query('insert into users set ?', user, function (err, result) {
            if (err) {
                console.error(err);
                connection.rollback(function () {
                    console.error('rollback error');
                    throw err;
                });
            }// if err
            console.log('insert transaction log');
            var log = {'userid': req.body.userid};
            connection.query('insert into log set ?', log, function (err, result) {
                 if (err) {
                     console.error(err);
                     connection.rollback(function () {
                        console.error('rollback error');
                         throw err;
                      });
                  }// if err
                 connection.commit(function (err) {
                    if (err) {
                        console.error(err);
                        connection.rollback(function () {
                               console.error('rollback error');
                               throw err;
                            });
                    }// if err
                    res.send(200, 'success');

                 });// commit
                });// insert into log
        });// inset into users
    }); // begin trnsaction
});

//select all
app.get('/users', function(req,res){
    var query = connection.query('select * from users',function(err,rows){
        console.log(rows);
        res.json(rows);
    });
    console.log(query);
});

// select where
app.get('/users/:userid', function(req,res){
    var query = connection.query('select * from users where userid='+mysql.escape(req.params.userid),function(err,rows){
        console.log(rows);
        res.json(rows);
    });
    console.log(query);
});


http.createServer(app).listen(app.get('port'), function(){
  console.log('Express server listening on port ' + app.get('port'));
});