README
xiaoming-mssql的使用说明
安装
npm install xiaoming-mssql --save
如果对下面使用方式有什么不明白的,可以添加我本人的QQ 1693490575
pulgin.js中的配置
exports.mssql = {
enable: true,
package: 'xiaoming-mssql',
};
config中的配置
config.mssql = {
clients: {
db1: {
user: '你的数据库用户名',
password: '数据库连接密码',
server: '数据库服务器ip',
database: '数据库名',
port: 端口号,
},
},
};
注意:连接数据库的端口号只能是数字
使用举例
使用举例1
const pool = await this.app.mssql.get('db1');
const result = await pool.request().asyncExecute(`select * from [user] where Id = @Id`, { Id: 2 });
返回的结果
{ recordsets: [ [ [Object] ] ],
recordset: [ { Id: 2, name: '小李', age: 14 } ],
output: {},
rowsAffected: [ 1 ],
sql: 'select * from [user] where Id = 2' }
recordsets 和 recordset都是返回结果,但是recordsets返回的是对象,recordset返回的是json的字符串 rowsAffected代表查询的行数 sql代表的是真正执行的sql语句
查询
查询有两种方式
- asyncExecute
- asyncQuery
插入数据
插入数据也有三种方式
- asyncQuery
- asyncInsert
- parameterInsert 注意:当调用asyncQuery方法插入数据时,传入的参数一定要用数组包裹着 三种使用方法,都在下面可以看到演示的方法
更新数据
更新数据有两种三种方式
- asyncQuery
- asyncUpdate
- parameterUpdate 下面也都有具体的传参的方式
删除数据
- asyncQuery
- asyncDelete
事务处理
const pool = this.app.mssql.get('db1');
const transaction = pool.transaction();
return await transaction.asyncBegin(async function(err) {
const result_6 = await transaction.request().asyncQuery(`select * from [User] where UserName=@userName and Status=1`, { userName: body.UserName });
await transaction.commit();
await transaction.rollback(function() { });
}
关联查询
- linkedQuery
普通的传参查询
function query() {
const sql = `select * from [user] where Id=@id and customerCompanyId=@customerCompanyId`;
const b = translator.execute(sql, {
id: 1,
customerCompanyId: '123',
});
console.log(b)
}
真正执行的sql语句
select * from [user] where Id=1 and customerCompanyId='123'
当传参的值为null或者undefine值
function query2() {
const sql = `
declare @CustomerTypeId int
select stringValue,nullValue,numberValue,CustomerType from log
where stringValue = @stringValue and nullValue = @nullValue and numberValue = @numberValue and CustomerType=@CustomerType and number=@number`
const b = translator.execute(sql, {
stringValue: "string",
nullValue: null,
numberValue: 0,
CustomerType: 'Type',
number: undefined,
});
console.log(b)
}
真正执行的sql语句
declare @CUSTOMERTYPEID int
select stringValue,nullValue,numberValue,CustomerType from log
where stringValue = 'string' and nullValue = '' and numberValue = 0 and CustomerType='Type' and number=''
测试排序
query2();
// 测试排序
function query3() {
const sql = `select
* from table
where id=@Id and name=@name
`;
const a = translator.execute(sql, {
id: 1,
name: "你好"
}, {
order: [{
'OrderBy': "id",
"Asc": 0
}, {
'OrderBy': 'name'
}]
});
console.log(a)
}
真正执行的sql语句
select
* from table
where id=1 and name='你好'
order by id desc,name asc
排序的第二种使用方式
function query36() {
const sql = `select
* from table
where id=@Id and array in (@array) and name=@name order by @OrderBy @Asc
`;
const sql_c = translator.execute(sql, {
id: 1,
array: [1, 2, 3, 4],
name: "你好"
}, {
OrderBy: "id",
"Asc": 0
});
console.log(sql_c)
}
真正执行的sql语句
select
* from table
where id=1 and array in ('1','2','3','4') and name='你好' order by id DESC
测试数组形式的参数
function query4() {
const sql = `select
* from table
where id=@Id and array in (@array) and name=@name;
`;
const a = translator.execute(sql, {
id: 1,
array: [1, 2, 3, 4],
name: "你好"
}, {
order: {
'OrderBy': "id",
"Asc": 0
}
});
console.log(a)
}
真正执行的sql语句
select
* from table
where id=1 and array in ('1','2','3','4') and name='你好'
order by id desc
别名替换和额外数据替换
function query6() {
const sql = `insert into table (text,CreatorId,CreatorName)
Values(@text,@CreatorId,@CreatorName)`;
const a = translator.insert(
sql, [{
text1: "1",
}, {
text1: "2"
}], {
alisa: {
text: "text1"
},
extraData: {
CreatorId: 2,
CreatorName: "clg"
}
}
);
console.log(a)
}
真正执行的sql语句
insert into table (text,CreatorId,CreatorName)
values('1',2,'clg'),('2',2,'clg')
测试数组插入
function query7() {
const sql = `INSERT INTO MaterialSheet (TaskId,Type,CreatorId,CreatorName,Status,CompanyId,Warehouse)
VALUES (@TaskId,@Type,@CreatorId,@CreatorName,@Status,@CompanyId,@MaterialWarehouseEngineer)
SELECT SCOPE_IDENTITY() AS id `;
const a = translator.insert(
sql, [{
TaskId: 1,
Type: 2,
CreatorId: 3,
CreatorName: 4,
Status: 5,
CompanyId: 6,
MaterialWarehouseEngineer: 7,
}]
);
console.log(a)
}
真正执行的sql语句
INSERT INTO MaterialSheet (TaskId,Type,CreatorId,CreatorName,Status,CompanyId,Warehouse)
values ('1','2','3','4','5','6','7')
SELECT SCOPE_IDENTITY() AS id
测试以数组形式插入,并且加上额外数据
function query8() {
const sql = `INSERT [OrderLngLat] (Lng,Lat,Index,OrderId,CreatorId)
OUTPUT INSERTED.Iden INTO @batchInsertedIds(ID)
Values(@Lng,@Lat,@Index,@OrderId,@CreatorId);
`;
const a = translator.insert(sql, [{
"Index": 1,
"Lat": "113.122816",
"Lng": "23.028713"
},
{
"Index": 2,
"Lat": "113.123471",
"Lng": "23.029058"
},
{
"Index": 3,
"Lat": "113.12339",
"Lng": "23.028357"
},
{
"Index": 4,
"Lat": "113.1228",
"Lng": "23.028293"
}
], {
extraData: {
OrderId: 4,
CreatorId: 1
}
});
console.log(a)
}
真正执行的sql语句
INSERT [OrderLngLat] (Lng,Lat,Index,OrderId,CreatorId)
OUTPUT INSERTED.Iden INTO @BATCHINSERTEDIDS(ID)
values('23.028713','113.122816','1',4,1),('23.029058','113.123471','2',4,1),('23.028357','113.12339','3',4,1),('23.028293','113.1228','4',4,1)
测试查询中添加额外数据和别名
function query13() {
const sql = `select Id,Name,age from [user] where Id=@Id and Name=@Name and age=@age`
const b = translator.execute(sql, {
age: 18,
Id: 2,
}, {
alisa: {
name: 'name1',
},
extraData: {
name1: 18,
}
});
console.log(b)
}
真正执行的sql语句
select Id,Name,age from [user] where Id=2 and Name=18 and age=18
更新操作
function query18() {
const sql = 'update user set name=@name where Id=@id'
const sql_c = translator.update(sql, {
Name: '小李',
id: 2,
})
console.log(sql_c);
}
真正执行的sql语句
update user set name='小李' where Id=2
注意:update的用法和select用法是一样的,但是就是调用的方法不一样
插入多条insert并且是多条数据的插入
function query22() {
const sql = `IF EXISTS(SELECT * FROM [user] WHERE Id=@id)
INSERT INTO user2(name,age,gender) values(@name,@age,@gender)
ELSE
INSERT INTO [user](name,age) values(@name,@age);`
const sql_c = translator.insert(sql, [{
name: 'xiaohong',
age: 13,
gender: '1355'
},
{
name: 'xiaozhang',
age: 14,
gender: '1223'
},
{
name: 'xiaoli',
age: 17,
gender: '15'
},
{
name: 'xiaoming',
age: 20,
gender: '23'
},
],{extraData:{
Id:2,
}})
console.log(sql_c);
}
真正执行的sql语句
IF EXISTS(SELECT * FROM [user] WHERE Id=2)
INSERT INTO user2(name,age,gender) values('xiaohong','13','1355'),('xiaozhang','14','1223'),('xiaoli','17','15'),('xiaoming','20','23')
ELSE
INSERT INTO [user](name,age) values('xiaohong','13'),('xiaozhang','14'),('xiaoli','17'),('xiaoming','20');
以传参的方式插入数据
function query14() {
const sql_data = [{
name: 'xiaoming',
age: 18
}, {
name: 'xiaohong'
}]
const sql_c = translator.parameterInsert('db.dbo.user', sql_data)
console.log(sql_c)
}
真正执行的sql语句
INSERT INTO db.dbo.user(name,age) VALUES('xiaoming','18'),('xiaohong',null)
测试传参是使用别名和异步
function query15() {
const sql_data = [{
name: 'xiaoming',
age: 18
}, {
name: 'xiaohong'
}]
const sql_c = translator.parameterInsert('db.dbo.user', sql_data, {
alisa: {
name1: 'name',
},
extraData: {
gender: '女',
}
})
console.log(sql_c)
}
真正执行的sql语句
INSERT INTO db.dbo.user(age,gender,name1) VALUES('18','女','xiaoming'),(null,'女','xiaohong')
传参的单条数据的插入
function query16() {
const sql_data = {
name: 'xiaoming',
age: 18
}
const sql_c = translator.parameterInsert('db.dbo.user', sql_data)
console.log(sql_c)
}
真正执行的sql语句
INSERT INTO db.dbo.user(name,age) VALUES('xiaoming','18')
测试更新数据使用传参的方式
function query31() {
const tableName = 'Employee'
const sql_c = translator.parameterUpdate(tableName, {
set: {
Name: 'aa',
Tel: 3333
},
where: {
and: {
id: 1
},
orNot: {
name: '123',
Tel: 333
}
}
})
console.log(sql_c)
}
真正执行的sql语句
update [Employee] set Name = 'aa',Tel = '3333' where id = '1'
关联查询1
function query44(){
console.time('计时器1')
const sql = translator.linkedQuery({
leftJoin:'abc',
rightJoin:'ddd',
joinKey:{'abca': 'ddda','test': 'test2'},
joinMode:'left'
},['abc.d','ddd.b'],{where: {
and: {'abc.Id': 123,'ddd.name':'xiaoming'}
},alias:{'abc':'a'}})
const sql_text = `select abc.d,ddd.b from abc left join ddd on abc.abca = ddd.ddda and abc.test = ddd.test2 where abc.Id = 123 and ddd.name = 'xiaoming'`
console.timeEnd('计时器1')
}
真正执行的sql语句
SELECT abc.d,ddd.b FROM abc AS a left join ddd ON abc.abca=ddd.ddda AND abc.test=ddd.test2 WHERE abc.Id=123 AND ddd.name='xiaoming'
关联查询2
function query45(){
console.time('计时器1')
const sql = translator.linkedQuery([{
leftJoin: 'ABC',
rightJoin: 'ddd',
joinKey:{'aaa':'ccc'},
joinMode:'inner',
},{
leftJoin: 'ddd',
rightJoin:'eee',
joinKey:{'accc':'dddd'},
joinMode:'left',
}],['ABC.qwer','eee.oiuy'],{where:{
and:{'ABC.qwer': 'test'},
},alias:{'ddd':'d'}})
console.log(sql)
console.timeEnd('计时器1')
}
真正执行的sql语句
SELECT ABC.qwer,eee.oiuy FROM ABC inner join ddd ON ABC.aaa=ddd.ccc left join eee ON ddd.accc=eee.dddd WHERE ABC.qwer='test'