import { Provide } from "@midwayjs/decorator"
|
|
@Provide()
|
export class MysqlUtil {
|
|
/**
|
* 生成select语句 [sql_count、sql]
|
* @param {Object} obj select_ from_ where_ order_ server_paging pageindex_ pagesize_
|
*/
|
create_sql(obj, pageindex_, pagesize_, query) {
|
let str_select_ = [];
|
let str_select_count = [];
|
str_select_.push(`${obj.select_} ${obj.from_} ${obj.where_} ${obj.order_}`);
|
str_select_count.push(`select count(1) as counter ${obj.from_} ${obj.where_} ${obj.order_}`);
|
if (obj.server_paging == '1') {
|
let pageindex = Number.isNaN(Number(pageindex_)) ? Number(pageindex_) : 1;
|
let pagesize = Number.isNaN(Number(pagesize_)) ? Number(pagesize_) : 10;
|
let limit_begin = (pageindex - 1) * pagesize;
|
str_select_.push(` limit ${limit_begin} , ${pagesize}`);
|
}
|
return this.replaceParams([str_select_.join(''), str_select_count.join('')], query)
|
}
|
|
/**
|
* 生成select语句 [sql_count、sql]
|
* @param {Object} obj select_ from_ where_ order_ server_paging pageindex_ pagesize_
|
*/
|
create_sync_sql(obj) {
|
let str_select_ = [];
|
let str_sync = [];
|
str_select_.push(`${obj.select_} ${obj.from_} ${obj.where_} ${obj.order_}`);
|
str_sync.push(`${obj.insert_} ${obj.column_} ${obj.values_} ${obj.updateDuplicate_}`);
|
|
return [str_sync.join(''), str_select_.join('')]
|
}
|
|
/**
|
* 生成sql
|
* @param [] strs 要处理的数据 sql_count、sql
|
* @param {JSON} reqParams 请求参数
|
*/
|
replaceParams(str, reqParas) {
|
let pattern = /([#|$](\s{0,}){(\s{0,})(.*?)(\s{0,})})/g;
|
let match = str[0].match(pattern);
|
let status = 1;
|
let msg = '';
|
if (!match) {
|
|
} else {
|
for (let index = 0; index < match.length; index++) {
|
let paramName = this.getBraceStr(match[index]).replace(/\s*/g, "")
|
if (!(paramName in reqParas)) {
|
status = 0;
|
msg = 'parameter ' + paramName + ' is not provided';
|
}
|
str[0] = str[0].replace(match[index], reqParas[paramName]);
|
str[1] = str[1].replace(match[index], reqParas[paramName]);
|
}
|
}
|
return { "status": status, 'sql_count': str[1], 'sql': str[0], 'msg': msg };
|
}
|
|
|
/**
|
* 取出大括号内的内容
|
* @param text
|
* @returns {string}
|
*/
|
getBraceStr(text) {
|
let result = ''
|
if (this.isObjEmpty(text))
|
return result
|
let regex = /\{(.+?)\}/g;
|
let options = text.match(regex)
|
if (!this.isObjEmpty(options)) {
|
let option = options[0]
|
if (!this.isObjEmpty(option)) {
|
result = option.substring(1, option.length - 1)
|
}
|
}
|
return result
|
}
|
|
isObjEmpty(obj) {
|
var name;
|
for (name in obj) {
|
return false;
|
}
|
return true;
|
}
|
|
}
|
