一个运行在node上的mysql数据库查询工具库

发表于 | 分类于 | 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
module.exports = function() {
    var mysqlobj = require('mysql');
    var async = require('async');
    var pool = mysqlobj.createPool({
        host: 'host',
        user: 'user',
        password: 'password',
        database: "databases",
        //debug: true,
    });
    return {
        pool:pool,
        query:function(sql, param, callback){
            if(typeof param == "function"){
                callback = param;
                param = [];
            }
            pool.getConnection(function(err, connection) {
                if(err){
                    callback(err, [])
                    return;
                    
                }
                try{
                    connection.query( sql, param, function(err, rows) {
                        connection.release();
                        callback(err, rows)
                    });
                }
                catch(e){
                    console.log("数据库查询出错了："+sql);
                    console.log(param);
                    console.log(e);
                    
                    callback("err", null)
                }
            });
        },
        /*
        sqlParamsArr:[
            {
                sql:"update table set .......",
                params:[p1,p2],
            }
        ]
        */
        startTransaction:function(sqlParamsArr, callback){
            pool.getConnection(function(err, connection) {
                if(err){
                    callback(err, [])
                    return;
                }
                
                asyncCallFunctionList = [];
                for(var i=0; i<sqlParamsArr.length; i++){
                    asyncCallFunctionList.push(function(ii){
                        return function(callbackInner){
                            connection.query(sqlParamsArr[ii].sql,sqlParamsArr[ii].params , callbackInner);
                        }
                    }(i));
                }
                
                try{
                    
                    
                    connection.beginTransaction(function(err) {
                        if(err) {
                            callback("err");
                        }
                        async.series(asyncCallFunctionList, function(err, result) {
                            if(err) {
                                connection.rollback(function() {
                                    callback("err");
                                });
                                return ;
                            }
                            // 提交事务
                            connection.commit(function(err) {
                                if (err) { 
                                    connection.rollback(function() {
                                        callback("err");
                                    });
                                }
                                console.log('事务提交成功!');
                                callback(null);
                            });
                        });
                });
                }
                catch(e){
                    console.log("数据库查询出错了（事务处理）："+sqlParamsArr);
                    
                    console.log(e);
                    callback("err", null)
                }
            });
        },
        execSql:function(sql, param, callback){
            
        }
    }
}()