将TypeORM中Repository中的API解析成SQL字符串并导出:
import { EntityManager, EntityMetadata, ObjectType, Repository } from "typeorm";
type QueryOptions<T> = {
entity: ObjectType<T> | EntityMetadata,
method: string,
args: any[],
};
/**
* 解析 TypeORM 中 Repository 中的 API 方法参数并返回对应的 SQL 语句
* @param options {QueryOptions} - 需要解析的方法信息
* @param entityManager {EntityManager} - 实体管理器,用于获取数据库连接
* @returns {Promise<string>} - 返回 Promise,解析出来的 SQL 字符串
*/
async function parseTypeORMRepositoryQuery<T>(options: QueryOptions<T>, entityManager: EntityManager): Promise<string> {
const repository = entityManager.getRepository(options.entity);
switch (options.method) {
case "save":
return repository.createQueryBuilder().insert().into(repository.target).values(options.args[0]).getQuery();
case "remove":
return repository.createQueryBuilder().delete().from(repository.target).where(options.args[0]).getQuery();
case "find":
return repository.createQueryBuilder().select().from(repository.target, "entity").where(options.args[0]).getQuery();
default:
throw new Error(`Unsupported method: ${options.method}`);
}
}
export default parseTypeORMRepositoryQuery;
函数返回值为一个 Promise 对象,解析出来的 SQL 字符串在 Promise 中被返回。
使用示例:
import { getConnection } from "typeorm";
import parseTypeORMRepositoryQuery from "./parseTypeORMRepositoryQuery";
const queryOptions = {
entity: User, // 实体类名或元数据对象
method: "find",
args: [{ id: 1 }], // 函数参数数组
};
const entityManager = getConnection().manager;
const sql = await parseTypeORMRepositoryQuery(queryOptions, entityManager);
console.log(sql);
如果不使用typeorm可写成类的形式
class SqlBuilder<T> {
private tableName: string;
constructor(private entityName: string) {
this.tableName = entityName.toLowerCase();
}
// 插入数据
save(entity: T): string {
const columns = Object.keys(entity).join(",");
const values = Object.values(entity)
.map((v) => `'${v}'`)
.join(",");
return `INSERT INTO ${this.tableName} (${columns}) VALUES (${values})`;
}
// 删除数据
remove(conditions: Partial<T>): string {
const whereClause = Object.entries(conditions)
.map(([key, value]) => `${key} = '${value}'`)
.join(" AND ");
return `DELETE FROM ${this.tableName} WHERE ${whereClause}`;
}
// 查找数据
find(conditions: Partial<T>): string {
const whereClause = Object.entries(conditions)
.map(([key, value]) => `${key} = '${value}'`)
.join(" AND ");
return `SELECT * FROM ${this.tableName} WHERE ${whereClause}`;
}
// 查找单个数据
findOne(conditions: Partial<T>): string {
const whereClause = Object.entries(conditions)
.map(([key, value]) => `${key} = '${value}'`)
.join(" AND ");
return `SELECT * FROM ${this.tableName} WHERE ${whereClause} LIMIT 1`;
}
// 根据id查找数据
findByIds(ids: number[]): string {
const idClause = ids.join(",");
return `SELECT * FROM ${this.tableName} WHERE id IN (${idClause})`;
}
}
// 使用示例
const builder = new SqlBuilder<User>("users");
const user = { name: "张三", age: 18 };
console.log(builder.save(user));
// 输出: INSERT INTO users (name,age) VALUES ('张三','18')
console.log(builder.remove({ id: 1 }));
// 输出: DELETE FROM users WHERE id = '1'
console.log(builder.find({ age: 18 }));
// 输出: SELECT * FROM users WHERE age = '18'
console.log(builder.findOne({ name: "张三" }));
// 输出: SELECT * FROM users WHERE name = '张三' LIMIT 1
console.log(builder.findByIds([1, 2, 3]));
// 输出: SELECT * FROM users WHERE id IN (1,2,3)