SQL语言在TypeORM框架中的应用 SQL语言与TypeScript的现代数据库开发

QueryBuilder

queryRunner.query()

repository.query()

QueryBuilder

queryRunner.query()

repository.query()

import { getManager, getRepository } from "typeorm";
import { User } from "./entity/User"; // 假设你有User实体

async function executeRawSql() {
    const entityManager = getManager(); // 获取EntityManager
    const userRepository = getRepository(User); // 获取特定实体的Repository

    // 方式一：使用entityManager执行任意SQL
    const result1 = await entityManager.query(`SELECT id, name FROM user WHERE age > ?`, [25]);
    console.log("Raw query result (entityManager):", result1);

    // 方式二：使用repository执行SQL，返回结果通常是对象数组
    const result2 = await userRepository.query(`SELECT * FROM user WHERE email LIKE ?`, ['%@example.com']);
    console.log("Raw query result (repository):", result2);

    // 复杂查询示例：使用JOIN和聚合
    const complexResult = await entityManager.query(`
        SELECT u.name, COUNT(p.id) AS post_count
        FROM user u
        LEFT JOIN post p ON u.id = p.userId
        GROUP BY u.id
        HAVING COUNT(p.id) > ?
    `, [5]);
    console.log("Complex query result:", complexResult);
}

?

$1

createQueryBuilder()

import { getRepository } from "typeorm";
import { User } from "./entity/User";
import { Post } from "./entity/Post"; // 假设有Post实体

async function useQueryBuilder() {
    const userRepository = getRepository(User);

    // 使用QueryBuilder构建复杂查询
    const usersWithPosts = await userRepository
        .createQueryBuilder("user") // "user" 是别名
        .leftJoinAndSelect("user.posts", "post") // 关联并选择posts
        .where("user.age > :minAge", { minAge: 30 })
        .andWhere("post.createdAt > :date", { date: new Date('2023-01-01') })
        .orderBy("user.name", "ASC")
        .getMany(); // 获取多个结果

    console.log("Users with posts (QueryBuilder):", usersWithPosts);

    // QueryBuilder中插入原生SQL片段
    const customFunctionResult = await userRepository
        .createQueryBuilder("user")
        .select("user.name", "userName")
        .addSelect("LOWER(user.email)", "lowerEmail") // 使用原生SQL函数
        .where("user.id IN (:...ids)", { ids: [1, 2, 3] })
        .getRawMany(); // 获取原始数据，不映射到实体

    console.log("Custom function result (QueryBuilder raw):", customFunctionResult);
}

getRawMany()

getRawOne()

// 定义原生SQL查询结果的接口
interface UserPostCount {
    userName: string;
    postCount: number;
}

async function getAggregatedData(): Promise<UserPostCount[]> {
    const entityManager = getManager();
    const result = await entityManager.query<UserPostCount[]>(`
        SELECT u.name AS userName, COUNT(p.id) AS postCount
        FROM user u
        LEFT JOIN post p ON u.id = p.userId
        GROUP BY u.id
        ORDER BY postCount DESC
    `);
    return result;
}

async function processData() {
    const data = await getAggregatedData();
    // 此时，data是UserPostCount[]类型，你可以安全地访问data[0].userName或data[0].postCount
    data.forEach(item => {
        console.log(`${item.userName} has ${item.postCount} posts.`);
    });
}