nest QueryBuilder

使用 connection 创建一

createConnection().then(async (connection) => {
	const user = connection.getRepository()
		.createQueryBuilder()
		// 需要选择查询的字段，如果想要全部查询可以不加select
		.select(["user.id", "user.username"]) 
		// 从哪张表， 并且定义别名为user
		.from(User, "user")
		// 过滤条件
		.where("(user.id=:id)", { id: 1 })
		.getOne()
	console.log(user)
}).catch(error => console.log(error))

使用 connection 创建二

createConnection().then(async (connection) => {
	const user = connection.getRepository()
		.createQueryBuilder(User, "user")
		.select(["user.id", "user.username"])
		.where("(user.id=:id)", { id: 1 })
		.getOne()
	console.log(user)
}).catch(error => console.log(error))

使用 entity manager 创建

createConnection().then(async (connection) => {
	const user = await getManager()
		.createQueryBuilder(User, "user")
		.select("user")
		.getMany()
	console.log(user)
}).catch(error => console.log(error))

使用repository

createConnection().then(async (connection) => {
	const user = await getRepository(User)
		.createQueryBuilder("user")
		.getMany()
	console.log(user)
}).catch(error => console.log(error))

QueryBuilder 五种类型

• 查询数据

const user = await getConnection()
	.createQueryBuilder(User, "user")
	.select(["user.id", "user.username"])
	.where("(user.id=:id)", { id: 1})
	.getOne()

• 插入数据

const result = await getConnection()
	.createQueryBuilder()
	.insert() // 插入数据的时候要知名插入到哪个实体类
	.into(User)
	.values([
		{ username: "张三", password: "123456" },
		{ username: "李四", password: "123456" }
	])
	.execute()

• 更新数据

const result = await getConnection()
	.createQueryBuilder()
	.update(User)
	.set({ username: "王五" })
	.where("id=:id", { id: 1 })
	.execute()

• 删除数据

const result = await getConnection()
	.createQueryBuilder()
	.delete()
	.from(User)
	.where("id=:id", { id: 3 })
	.execute()

• 创建关系查询

const result = await getConnection()
	.createQueryBuilder(User, "user")
	.leftJoinAndMapMany("user.posts", Posts, "posts", "user.id=posts.userId")
	.getMany()
console.log(JSON.stringify(result))

查询数据方法

• getOne: 查询一条数据
• getMany: 查询多条数据
• getRawOne: 使用聚合函数的时候查询下一条数据
• getRawMany: 使用聚合函数的时候查询多条数据

const result = await getConnection()
	.createQueryBuilder(User, "user")
	.select("SUM(user.id)", "sum")
	.getRawOne()

传递参数

• 直接使用

.where("user.username = :username", { username: "张三"})

• 使用setParameter填充值

.where("user.username = :username")
.setParameter("username", "张三")

• LINK 模糊查询

.where("user.username like :username", { username: "% %{username} %"})

• IN 查询

.where("user.username IN (:...username)", { username: [ "Timber", "Cristal", "Lina"]})