nodejs/cjms/framework/lib/TableHelper.js

var MySql = require('./oujMySql');

/**
 * 基本类，提供增删改查
 * @param {string} tableName 表名
 * @param {string} dbKey 
 */
function TableHelper(tableName, dbKey) {
    this.tableName = tableName;
    this.dbKey = dbKey || 'default';
    this.objMySql = new MySql(this.dbKey);
}

TableHelper.prototype.setTableName = function(tableName) {
    this.tableName = tableName;
};

/**
 * 读取数据
 * @param  {object} where   参数列表，特殊参数前缀
 * @param  {object} keyWord 查询关键字['_field', '_where', '_limit', '_sortKey', '_sortDir', '_lockRow', '_tableName', '_groupby']
 * @return {object}
 */
TableHelper.prototype.getOne = function(where, keyWord, cb) {
    var sql = this.buildSelectSql(where, keyWord);
    return this.objMySql.getOne(sql, cb);
}

TableHelper.prototype.getCol = function(where, keyWord, cb) {
    var sql = this.buildSelectSql(where, keyWord);
    return this.objMySql.getCol(sql, cb);
}

TableHelper.prototype.getRow = function(where, keyWord, cb) {
    var sql = this.buildSelectSql(where, keyWord);
    return this.objMySql.getRow(sql, cb);
}

TableHelper.prototype.getAll = function(where, keyWord, cb) {
    var sql = this.buildSelectSql(where, keyWord);
    return this.objMySql.getAll(sql, 0, cb);
}

TableHelper.prototype.updateObject = function(newData, where, cb) {
    if (!where) {
        throw new SQLException('updateObject没有传入where');
    }

    var _where = this.buildWhereSql(where);
    var sql = 'UPDATE `' + this.tableName  + '` SET ';
    sql += this.buildValueSql(newData) + ' WHERE ' + _where;

    return this.objMySql.update(sql, cb);
}

TableHelper.prototype.delObject = function(where, cb) {
    if (!where) {
        throw new SQLException('updateObject没有传入where');
    }

    var _where = this.buildWhereSql(where);
    var sql = 'DELETE FROM `' + this.tableName  + '` WHERE ' + _where;

    return this.objMySql.update(sql, cb);
}


TableHelper.prototype.replaceObject = function(newData, cb) {
    var sql = 'REPLACE INTO `' + this.tableName  + '` SET ';
    sql += this.buildValueSql(newData);
    return this.objMySql.update(sql, cb);
}

TableHelper.prototype.addObject = function(data, cb) {
    var sql = 'INSERT `' + this.tableName  + '` SET ';
    sql += this.buildValueSql(data);

    return this.objMySql.update(sql, cb);
}

TableHelper.prototype.close = function(cb) {
    return this.objMySql.close(cb);
}

TableHelper.prototype.buildValueSql = function(data, separator) {
    separator = separator || ',';
    var pool = this.objMySql.getPool();
    var values = [];
    for (var key in data) {
        values.push(pool.escapeId(key) + '=' + pool.escape(data[key]));
    }

    return values.join(separator);
}

TableHelper.prototype.buildWhereSql = function(where, keyWord) {
    keyWord = keyWord || {};
    var _sql = keyWord['_where'] || '1';
    var pool = this.objMySql.getPool();

    where = where || {};
    for (var key in where) {
        _sql += " AND " + pool.escapeId(key) + "=" + pool.escape(where[key]);
    }

    if (keyWord['_groupby']) {
        _sql += " GROUP BY " + keyWord['_groupby'];
    }

    if (keyWord['_sortKey']) {
        _sql += " ORDER BY " + keyWord['_sortKey'];
        if (keyWord['_sortDir']) {
            _sql += " " + keyWord['_sortDir'];
        }
    }

    return _sql;
}

TableHelper.prototype.buildSelectSql = function(where, keyWord) {
    keyWord = keyWord || {};
    var _field = keyWord['_field'] || '*';
    var pool = this.objMySql.getPool();

    var tableName = keyWord['_tableName'] || this.tableName;
    var _where = this.buildWhereSql(where, keyWord);
    var _sql = 'SELECT ' + _field + ' FROM ' + pool.escapeId(tableName)  + ' WHERE ' + _where;

    return _sql;
}

/**
 * @type {TableHelper}
 */
module.exports = TableHelper;