nodejs的查询构造器

nodejs的查询构造器，QQ群里的一个哥们（路人某）给的一个类，感觉挺好的，分享出来

我使用的是express框架，类放在项目下的  /plugin/query.js中

/**
 * Created by Administrator on 2014/5/9.
 */
var mootools = require('mootools');

function QueryElement(name, elements, glue){
    this.elements = [];
    this.type = "";
    this.name = name;
    this.glue = glue || ',';
    this.appends(elements);
};


QueryElement.prototype.appends = function(elements){

    if (typeof elements == 'array') {
       this.elements.append(elements);
    } else {
       this.elements.append(new Array(elements));
    }
};
QueryElement.prototype.toString = function(){
    
    if(this.name.substr(-2) == '()'){
       return this.name.substr(0,-2)+"("+this.elements.join(this.glue)+")";
    } else{
       return this.name+" "+this.elements.join(this.glue)+" ";
    }
};

QueryElement.prototype.getElement = function(){
    return this.elements;
};

function Query(){

    this.type = null;
    this.froms = null;
    this.groups = null;
    this.havings = null;
    this.orders = null;
    this.elements = null;
    this.selects = null;
    this.deletes = null;
    this.updates = null;
    this.inserts = null;
    this.joins = null;
    this.sets = null;
    this.wheres = null;
    this.columnss = null;
    this.valuess = null;
    this.limits = null;
    this.autoIncrementField = null;
};

/**
 * query.from('table')
 * @param tables
 * @returns {Query}
 */
Query.prototype.from = function(tables){

    this.froms = new QueryElement('FROM',tables).toString();
    return this;

};
/**
 *
 * @param columns
 * @returns {Query}
 */
Query.prototype.group = function(columns){

    this.groups = new QueryElement('GROUP BY',columns).toString();

    return this;
};

Query.prototype.having = function(conditions,glue){
    glue = glue || 'AND';
    this.havings = new QueryElement('HAVING',conditions," "+glue+" ").toString();
    return this;
};

Query.prototype.innerJoin = function (condition){
    this.join('INNER',condition);
    return this;
};
Query.prototype.leftJoin = function (condition){
    this.join('LEFT',condition);
    return this;
};
Query.prototype.join = function(type,condition){
    if(this.joins == null){
        this.joins = [];
    }
    this.joins.append(new Array(new QueryElement(type.toLocaleUpperCase()+'JOIN',condition).toString()));

    return this;
};

Query.prototype.insert = function(table,autoIncrementField){
    this.type = 'INSERT';
    this.inserts = new QueryElement('INSERT INTO ',table).toString();
    this.autoIncrementField = autoIncrementField || false;
    return this;
};

Query.prototype.length = function(value){
    return 'LENGTH('+ value +')';
    return this;
};

Query.prototype.order = function (columns){

    this.orders =  new QueryElement('ORDER BY ',columns).toString();
    return this;

};
Query.prototype.outerJoin = function (conditions){
    this.join('OUTER',condtions);
    return this;
};

Query.prototype.rightJoin = function (conditions){
    this.join('right',condtions);
    return this;

};
/**
 *
 * eq:
 * query.select('a.*').select('b.id');
 * query.select(['a.*', 'b.id']);
 *
 * @param columns
 * @returns {Query}
 */
Query.prototype.select = function (columns){
    this.type='SELECT';

    this.selects = new QueryElement('SELECT',columns+"").toString();

    return this;

};
/**
 * eq:
 * query.set("a = 1").set('b=2');
 * qyert.set([a:1,b:2]);
 * @param columns
 * @param glue
 * @returns {Query}
 */
Query.prototype.set = function (columns,glue){
    glue = glue || ','

    this.sets = new QueryElement('VALUES',columns," "+glue+" ").toString();

    return this;
};
/**
 * eq;
 * query.update('table')->set('a=1');
 * @param table
 * @returns {Query}
 */
Query.prototype.update = function (table){
    this.type = 'update';
    this.updates = new QueryElement('UPDATE',table).toString();
    return this;
};
/**
 * eq:
 * query.values('1,123,3').values('2,2,3,4');
 * query.values([[1,2,3],[2,3,3]])
 * @param values
 * @returns {Query}
 */
Query.prototype.values = function (values){

    this.valuess = new QueryElement('()',values,'),(').toString();
    return this;
};
/**
 * eq :
 * query.where('a=b').where("b=a");
 * query.where(['a=b','b=a']);
 * @param conditions
 * @param glue
 * @returns {Query}
 */
Query.prototype.where = function(conditions,glue){
    glue = glue || 'AND';

    glue = glue.toLocaleUpperCase();
    this.wheres = new QueryElement('WHERE',conditions," "+glue+" ").toString();

    return this;
};

/**;
 *
 * query.delete('#__a').where('id = 1');
 * @param table
 * @returns {Query}
 */
Query.prototype.delete = function(table){
    this.type = 'delete';
    this.deletes = new QueryElement('DELETE',null).toString();
    if(table){
        this.from(table);
    }
    return this;

};

Query.prototype.columns = function(columns){

    this.columnss = new QueryElement('()',columns).toString();
    return this;
};

Query.prototype.limit = function(columns){
    this.limits = new QueryElement('LIMIT',columns).toString();
    return this;
}
Query.prototype.toString = function(){
    var query = '';
    var t = this.type.toLocaleLowerCase();


    switch (t){
        case 'element' :
            query += this.elements;
            break;
        case 'select'  :

            query += this.selects;
            query += this.froms;

            if(this.joins){
                this.joins.each(function(val){
                      query += val;
                })

            }

            if(this.wheres){
                query +=this.wheres;
            }
            if(this.havings){
                console.log(this.havings);
                query += this.havings;
            }
            if(this.groups){
                query += this.groups;
            }
            if(this.orders){
                query += this.orders;
            }
            if(this.limits){
                query +=this.limits;
            } else{
                query += ' LIMIT 1';
            }
            break;
        case 'delete' :
            query += this.deletes;
            query += this.froms;

            if(this.joins){
                this.joins.each(function(val){
                    query += val;
                })

            }
            if(this.wheres){
                query +=this.wheres;
            }
            if(this.limits){
                query +=this.limits;
            }
            
            break;
        case 'update' :
            query += this.updates;

            if(this.joins){
                this.joins.each(function(val){
                    query += val;
                })

            }
            query += this.sets;
            if(this.wheres){
                query +=this.wheres;
            }
            if(this.limits){
                query +=this.limits;
            }
            break;
        case 'insert' :
            query += this.inserts;

            // Set method
            if (this.sets) {
                query = this.sets;
            } else if (this.valuess) {
                if (this.columnss) {
                    query +=this.columnss;
                }

                query += ' VALUES ';
                query += this.valuess;
            }

            break;
    }
    return query;

};

module.exports  = Query;

使用方法

var query = require("../plugin/query.js");	
var M = new query();
var sql = M.delete('tabname').where('id = 1').toString();
console.log(sql);//DELETE  FROM tabname WHERE id = 1

转载于:https://my.oschina.net/tongjh/blog/278739