先暂且不涉及连接池,单纯的对数据库进行操作。node.js的封装非常漂亮,对数据库的操作,提供了统一的接口
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '',
port: '3306',
database: 'NodeSample',
});
connection.connect();
var userAddSql = 'INSERT INTO MyTable(firstname, lastname, message) VALUES(?,?,?)';
var userAddSql_Params = ['James', 'Potter', 'hello world'];
var userModSql = 'UPDATE MyTable SET firstname = ?,lastname = ?, message=? WHERE Id = ?';
var userModSql_Params = ['Petter', 'HK', 'modify this' , 5];
var userDelSql = 'DELETE FROM MyTable WHERE Id=?';
var userDelSql_Params = [5];
connection.query(userDelSql, userDelSql_Params,function (err, result) {
if(err){
console.log('[ERROR] - ',err.message);
return;
}
console.log('-------------------------------------------------------------');
//console.log('INSERT ID:',result.insertId);
console.log(result);
console.log('-------------------------------------------------------------');
});
connection.end();
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '',
port: '3306',
database: 'NodeSample',
});
connection.connect();
var userGetSql = 'SELECT * FROM MyTable';
var userDelSql = 'DELETE FROM MyTable';
connection.query(userGetSql, [],function (err, result) {
if(err){
console.log('[SELECT ERROR] - ',err.message);
return;
}
console.log('--------------------------SELECT----------------------------');
console.log(result);
console.log('-----------------------------------------------------------------');
});
connection.end();
下面使用连接池查询数据库
// Create a MySQL connection pool with
// a max of 10 connections, a min of 2, and a 30 second max idle time
var Pool = require('generic-pool').Pool;
var mysql = require('mysql'); // v2.10.x
var pool = new Pool({
name : 'mysql',
create : function(callback) {
var c = mysql.createConnection({
user: 'root',
password: '',
database:'NodeSample'
})
// parameter order: err, resource
callback(null, c);
},
destroy : function(client) { client.end(); },
max : 10,
// optional. if you set this, make sure to drain() (see step 3)
min : 2,
// specifies how long a resource can stay idle in pool before being removed
idleTimeoutMillis : 30000,
// if true, logs via console.log - can also be a function
log : true
});
// acquire connection - callback function is called
// once a resource becomes available
pool.acquire(function(err, client) {
if (err) {
// handle error - this is generally the err from your
// factory.create function
}
else {
client.query("select * from MyTable", [], function(err, result) {
console.log('-------------------------------------------------------------');
console.log(result);
console.log('-------------------------------------------------------------');
// return object back to pool
pool.release(client);
});
}
});
// Only call this once in your application -- at the point you want
// to shutdown and stop using this pool.
pool.drain(function() {
pool.destroyAllNow();
});