TypeORM查询
DB
https://github.com/typeorm/typeorm 是 node.js 现有社区最成熟的对象关系映射器（ORM ）
初始化
yarn init -y
yarn add typeorm
npx typeorm init
TypeORM 查询操作
查询所有 find()
const userRepository = connection.getRepository(User);
const result = await userRepository.find();
console.log(result);
==
select * from user;
查询指定字段 select
const userRepository = connection.getRepository(User);
const result = await userRepository.find({ select: ['id', 'username'] });
console.log(result);
==
select id, username from user;
条件查询 where
const userRepository = connection.getRepository(User);
const result = await userRepository.find({ where: { id: 1 } });
console.log(result);
==
select * from user where user.id = 1;
条件查询 and关系 where 对象写法
const userRepository = connection.getRepository(User);
const result = await userRepository.find({ where: { id: 1, username: 'xx' } });
console.log(result);
==
select * from user where user.id = 1 and user.username = 'xx';
条件查询 o r关系 where 数组写法
const userRepository = connection.getRepository(User);
const result = await userRepository.find({ where: [{ id: 1 }, { username: 'xx' }] });
console.log(result);
==
select * from user where user.id = 1 or user.username = 'xx';
排序
const userRepository = await connection.getRepository(User);
const result = await userRepository.find({
order: {
'id': 'DESC', // ASC(升序) || DESC(降序)
}
});
query: SELECT * FROM `user` ORDER BY `User`.`id` DESC
分页
userRepository.find({
skip: 5 // skip - 偏移（分页）
});
userRepository.find({
take: 10 // take - limit (分页) - 得到的最大实体数
});
relations关系查询(前提是要先有外键关联关系)
const userRepository = connection.getRepository(User);
const result = await userRepository.find({ relations: ['userDetail'] });
console.log(result);
==
select a.*,b.* from user as a left join user_extend as b on a.id = b.userId;
join关系查询
const userRepository = connection.getRepository(User);
const result = await userRepository.find({
join: {
alias: 'user',
leftJoinAndSelect: {
detail: 'user.userDetail',
posts: 'user.posts'
}
}
});
console.log(JSON.stringify(result));
排序
const userRepository = connection.getRepository(User);
const result = await userRepository.find({
order: {
id: 'DESC',
username: 'ASC'
}
});
console.log(result);
==
select * from user order by username asc, id desc;
分页查询
skip偏移(表示从哪里开始) take查询多少条数据
const userRepository = connection.getRepository(User);
const result = await userRepository.find({
skip: 0,
take: 10,
})
console.log(result);
==
select * from user limit 0,10;
Not
const userRepository = connection.getRepository(User);
const result = await userRepository.find({
username: Not('王五')
});
console.log(result);
==
select * from user where username != '王五';
- LessThan小于,一般用于数字
- LessThanOrEqual小于或者等于
- MoreThan大于
- MoreThanOrEqual大于等于
- Equal等于
- Like模糊查询 xx: LIKE(’%yy%’)
- Between两个范围之间xx: Between(1,10)
- In在什么里面xx: In(’’,’’)
find其他方法：
1、find查找返回一个数组 2、findOne查询返回一个对象,直接传递一个数字进去,会根据id去查询 3、findAndCount查询返回数量data, count 4、findByIds()根据id数组查询
使用 Query Builder 查询
QueryBuilder是 TypeORM 最强大的功能之一 ，它允许你使用优雅便捷的语法构建 SQL 查询，执行并获得自动转换的实体。
条件查询
import "reflect-metadata";
import {createConnection} from "typeorm";
import { Photo } from "./entity/Photo";
import {User} from "./entity/User";
createConnection().then(async connection => {
console.log('================================');
const users = await connection
.createQueryBuilder(User, 'user')
.where({ // 条件查询
firstName: '1'
})
.orderBy('user.id', 'DESC') // 排序
.getMany();
console.log(users);
}).catch(error => console.log(error));
==
SELECT * FROM `user` `user` WHERE `user`.`firstName` = `1` ORDER BY `user`.`id` DESC
计算所有人年龄
import "reflect-metadata";
import {createConnection} from "typeorm";
import { Photo } from "./entity/Photo";
import {User} from "./entity/User";
createConnection().then(async connection => {
console.log('================================');
const users = await connection
.createQueryBuilder(User, 'user')
.where({ // 条件查询
firstName: '1'
})
.orderBy('user.id', 'DESC') // 排序
.select("SUM(user.age)", "totalAge")
.getRawOne();
console.log(users);
}).catch(error => console.log(error));
==
SELECT SUM(`user`.`age`) FROM `user` `user` WHERE `user`.`firstName` = 1 ORDER BY `user`.`id` DESC
const userTotalCash = await getConnection()
.createQueryBuilder(YkMemberMoneyLog, 'log')
.where('log.type = 2 AND log.status = 1')
.select('SUM(log.money)', 'sum')
.getRawOne();
==
SELECT SUM(`log`.`money`) AS `sum` FROM `yk_member_money_log` `log` WHERE `log`.`type` = 2 AND `log`.`status` = 1
删除操作
await connection.manager.remove(user);
添加操作
import "reflect-metadata";
import {createConnection} from "typeorm";
import {User} from "./entity/User";
createConnection().then(async connection => {
console.log('================================');
let user = new User();
user.firstName = "Jony"
user.lastName = "HH"
user.age = 22
const userRepository = await connection.manager.save(user);
console.log(userRepository)
}).catch(error => console.log(error));
表关系
一对一
@JoinColumn装饰器，它指示关系的这一侧将拥有该关系。关系可以是单向或双向的。关系只有一方可以拥有。
import "reflect-metadata";
import {createConnection} from "typeorm";
import { Photo } from "./entity/Photo";
import {User} from "./entity/User";
createConnection().then(async connection => {
console.log('================================');
const userRepository = await connection.getRepository(User);
const user = new User();
const photo = new Photo();
photo.name = '测试';
user.firstName = '1'
user.lastName = '2'
user.photo = photo;
user.age = 12
await connection.manager.save(photo);
await connection.manager.save(user);
}).catch(error => console.log(error));
一对多
