1.0.8 • Published 3 months ago
como-sql-builder v1.0.8
como-sql-builder
支持mysql、sqlite3 原生typescript 实现,无任何依赖库,方便实用
实现此库的主要目的是在mysql和sqlite3数据库切换时类型转换不兼容的情况 此库的数据类型验证比较宽松,实现了 Sequelize 的70%的功能
手工构建SQL语句并不有趣，尤其是在一种对多行字符串支持笨拙的语言中
让我们用JavaScript来构建它
安装
$ npm install como-sql-builder使用示例
import { FindOptions, WhereOptions } from "../types/application";
import { SQLBuilderException, ColumnProperty } from "./core/BaseBuilder";
import { Op } from "./core/operators";
import { SqlBuilder } from "./main";
//自定义回调场景转换或验证函数
//这是只展示了转换函数
//如果需要验证 可以在函数内部验证
const md5 = function(value:string,data:object,scene:string) {
if(value != "1234") throw new SQLBuilderException("数据验证未通过,请检查数据是否正确");
if(scene == "insert") {
return `${value}helloworld`;
}
return value;
}
class AdminUserPipe {
@ColumnProperty()
//ColumnProperty({convert:Fn})
//此处的Number是内定的转换函数 也是可以自定义转换函数
//会回调三个参数(value,data,scene)
// value 当前字段的值
// data 整个对象的值
// scene 使用场景 当前只支持insert和update
//如果没有装饰器 则在插入数据 查询数据 更新数据中忽略该字段
//但前题是必须要使用这个使用 没有使用此类无效
admin_id?: number;
@ColumnProperty()
username?: string;
// @ColumnProperty({convert:Fn})
@ColumnProperty()
password?: string;
@ColumnProperty()
admin_status?:number
@ColumnProperty()
role_id?:number
@ColumnProperty({
insertDefaultValue:"192.168.18.99"
})
login_ip?:number
}
// class RolesPipe {
// role_id?:number
// role_desc?:string
// role_name?:string
// role_flag?:string | number
// admin_id?:number
// create_time?:string
// }
const builder = SqlBuilder.Ins({prefix:"szj_"});
//查询语句
/**
* 返回示例:
* SELECT `SzjAdminUser`.`admin_id`,`SzjAdminUser`.`username`,`SzjAdminUser`.`password`,`SzjAdminUser`.`admin_status`,`SzjAdminUser`.`role_id`,`role`.`role_name` AS `role_name` FROM `szj_admin_user` AS `SzjAdminUser` LEFT JOIN `szj_admin_user` AS `au` ON `SzjAdminUser`.`action_user` = `au`.`admin_id` LEFT JOIN `szj_roles` AS `role` ON `SzjAdminUser`.`role_id` = `role`.`role_id` ORDER BY `SzjAdminUser`.`admin_id` DESC
*/
const selectSQL = builder.Builder({cls:AdminUserPipe}).Table("admin_user").FindAll({
include:[
{table:'admin_user',as:'au',attributes:[],condition:{
foreignKey:"action_user",targetKey:'admin_id'
}},
{table:"roles",as:'role',attributes:["role_name"],condition:{
foreignKey:'role_id',targetKey:'role_id'
}}
],
attributes:{
include:[["au.username","action_username"]],
exclude:["login_time","login_count"]
},order:[["admin_id","desc"]]
} as FindOptions<object>);
//插入语句
/**
* 返回示例:
* INSERT INTO `szj_admin_user` (`username`,`password`,`role_id`) VALUES ("como","123456",1)
*/
const insertSQL = builder.Builder({cls:AdminUserPipe}).Table("admin_user").Insert<AdminUserPipe>({
username:"como",
password:"123456",
role_id:"1",
});
//占位符插入
/**
* 返回示例:
* [
'INSERT INTO `szj_admin_user` (`username`,`password`,`role_id`) VALUES ($0,$1,$2)',
[ 'como', '123456', 1 ]
]
*/
const insertValSql = builder.Builder({cls:AdminUserPipe}).Table("admin_user").InsertVal({
username:"como",
password:"123456",
role_id:1,
});
//更新语句
/**
* 返回示例:
* UPDATE `szj_admin_user` SET `password` = "12345678",`admin_status` = 1 WHERE ( `admin_id` = 38 )
*/
const updateSQL = builder.Builder({cls:AdminUserPipe}).Table("admin_user").Update<AdminUserPipe>({
password:"12345678",admin_status:1
},{where:{admin_id:38}});
//更新语句
/**
* 返回示例:
* [
'UPDATE `szj_admin_user` SET `password` = $0,`admin_status` = $1 WHERE ( `admin_id` = 38 )',
[ '12345678', 1 ]
]
*/
const updateValSQL = builder.Builder({cls:AdminUserPipe}).Table("admin_user").UpdateVal({
password:"12345678",admin_status:1,action_user:1,login_count:10
},{where:{admin_id:38}});
//删除语句
/**
* 返回示例:
* DELETE FROM `szj_admin_user` WHERE ( `admin_id` = 38 )
*/
const deleteSql = builder.Builder({cls:AdminUserPipe}).Table("admin_user").Delete<AdminUserPipe>({
where:{admin_id:38}
});
//统计语句查询
/**
* 返回示例:
* SELECT count(`SzjArticles`.`admin_id`) AS `count` FROM `szj_articles` AS `SzjArticles`
*/
const countSql = builder.Builder().Table("articles").Count({col:"admin_id"});
//查询并统计所有数据
/**
* 返回示例:
* SELECT count(*) AS `count` FROM `szj_admin_user` AS `SzjAdminUser` LEFT JOIN `szj_admin_user` AS `au` ON `SzjAdminUser`.`action_user` = `au`.`admin_id`
* SELECT `SzjAdminUser`.`admin_id`,`SzjAdminUser`.`username`,`SzjAdminUser`.`password`,`SzjAdminUser`.`admin_status`,`SzjAdminUser`.`role_id` FROM `szj_admin_user` AS `SzjAdminUser` LEFT JOIN `szj_admin_user` AS `au` ON `SzjAdminUser`.`action_user` = `au`.`admin_id`
*/
const [AllCountSql,AllDataSql] = builder.Builder({cls:AdminUserPipe}).Table("admin_user").FindAndCountAll({
attributes:{
exclude:['create_time']
},
include:[
{table:"admin_user",as:'au',attributes:[],condition:{
foreignKey:"action_user",targetKey:'admin_id'
}}
]
});
//使用函数
/**
* 返回示例:
* SELECT `SzjArticles`.`article_id`,`SzjArticles`.`article_title`,`SzjArticles`.`create_time`,FROM_UNIXTIME(UNIX_TIMESTAMP(`create_time`)) AS `create_at_time` FROM `szj_articles` AS `SzjArticles`
*/
const fnSql = builder.Builder().Table("articles").FindAll({
attributes:[
"article_id","article_title","create_time",
[builder.Fn("FROM_UNIXTIME",builder.Fn("UNIX_TIMESTAMP","create_time")),"create_at_time"],
]
});
//无参数查询
/**
* 返回示例:
* [
'SELECT count(*) AS `count` FROM `szj_admin_user` AS `SzjAdminUser` LEFT JOIN `szj_admin_user` AS `au` ON `SzjAdminUser`.`action_user` = `au`.`admin_id`',
'SELECT `SzjAdminUser`.`admin_id`,`SzjAdminUser`.`username`,`SzjAdminUser`.`password`,`SzjAdminUser`.`admin_status`,`SzjAdminUser`.`role_id` FROM `szj_admin_user` AS `SzjAdminUser` LEFT JOIN `szj_admin_user` AS `au` ON `SzjAdminUser`.`action_user` = `au`.`admin_id`'
]
*/
const notParamsSql = builder.Builder({cls:AdminUserPipe}).Table("admin_user").FindAndCountAll({
include:[
{table:"admin_user",as:'au',attributes:[],condition:{
foreignKey:'action_user',targetKey:'admin_id'
}}
]
});
//单条查询
/**
* 返回示例:
* SELECT `SzjAdminUser`.`admin_id`,`SzjAdminUser`.`username`,`SzjAdminUser`.`password`,`SzjAdminUser`.`admin_status`,`SzjAdminUser`.`role_id` FROM `szj_admin_user` AS `SzjAdminUser` WHERE ( `SzjAdminUser`.`admin_id` > 5 AND `SzjAdminUser`.`admin_id` < 30 ) LIMIT 1
*/
const FindOneSql = builder.Builder({cls:AdminUserPipe}).Table("admin_user").FindOne<AdminUserPipe>({
where:{
[Op.and]:[
{admin_id:{[Op.gt]:5}} as WhereOptions<AdminUserPipe>,
{admin_id:{[Op.lt]:30}} as WhereOptions<AdminUserPipe>
],
// [Op.or]:[
// {admin_id:{[Op.gt]:5}} as WhereOptions<AdminUserPipe>,
// {admin_id:{[Op.lt]:30}} as WhereOptions<AdminUserPipe>
// ]
}
});
const insertAllSql = builder.Builder({cls:AdminUserPipe}).Table("admin_user").InsertAll([
{username:"szjcomo",role_id:1,password:"szjcomo"},
{username:"szjcomo123",role_id:3,password:"szjcomo123"},
]);
console.log('selectSQL',selectSQL);
console.log('insertSQL',insertSQL);
console.log('insertValSql',insertValSql);
console.log('updateSQL',updateSQL);
console.log('updateValSQL',updateValSQL);
console.log('deleteSql',deleteSql);
console.log('countSql',countSql);
console.log('AllCountSql',AllCountSql);
console.log('AllDataSql',AllDataSql);
console.log('fnSql',fnSql);
console.log('notParamsSql',notParamsSql);
console.log('FindOneSql',FindOneSql);
console.log('insertAllSql',insertAllSql);