# pg-dynamic-query

**Repository Path**: zhanghao161512/pg-dynamic-query

## Basic Information

- **Project Name**: pg-dynamic-query
- **Description**: No description available
- **Primary Language**: JavaScript
- **License**: MIT
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2022-03-15
- **Last Updated**: 2024-09-04

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

<!-- START doctoc generated TOC please keep comment here to allow auto update -->
<!-- DON'T EDIT THIS SECTION, INSTEAD RE-RUN doctoc TO UPDATE -->
**Table of Contents**  *generated with [DocToc](https://github.com/thlorenz/doctoc)*

- [使用例子](#%E4%BD%BF%E7%94%A8%E4%BE%8B%E5%AD%90)
  - [Postgresql 例子](#postgresql-%E4%BE%8B%E5%AD%90)
  - [MySql例子](#mysql%E4%BE%8B%E5%AD%90)
- [详细说明](#%E8%AF%A6%E7%BB%86%E8%AF%B4%E6%98%8E)
  - [DynamicQuery](#dynamicquery)
    - [DynamicQuery用到的参数类](#dynamicquery%E7%94%A8%E5%88%B0%E7%9A%84%E5%8F%82%E6%95%B0%E7%B1%BB)
    - [Query要怎么写?](#query%E8%A6%81%E6%80%8E%E4%B9%88%E5%86%99)
      - [基本用法](#%E5%9F%BA%E6%9C%AC%E7%94%A8%E6%B3%95)
      - [操作符](#%E6%93%8D%E4%BD%9C%E7%AC%A6)
      - [另一种or,and 和in](#%E5%8F%A6%E4%B8%80%E7%A7%8Dorand-%E5%92%8Cin)
    - [导航查询](#%E5%AF%BC%E8%88%AA%E6%9F%A5%E8%AF%A2)
    - [事务](#%E4%BA%8B%E5%8A%A1)
  - [switchSqlLog()](#switchsqllog)
  - [PageResponse](#pageresponse)
  - [TableConfig](#tableconfig)
    - [ParentConfig](#parentconfig)
  - [DbClient,Postgresql,MySql](#dbclientpostgresqlmysql)

<!-- END doctoc generated TOC please keep comment here to allow auto update -->

## 使用例子

### Postgresql 例子

```javascript
const {Postgresql, DynamicQuery, PageResponse, TableConfig} = require('pg-dynamic-query');

(async () => {
    //配置postgresql连接
    let postgresql = new Postgresql({
        "host": "****",
        "port": 1921,
        "database": "****",
        "user": "****",
        "password": "****"
    });  

  await postgresql.connect();
  const userConfig = new TableConfig("user_info", [
    "id",
    "name",
    "age"
  ]);
  const pageResponse = new PageResponse()
  const dynamicQuery = new DynamicQuery(userConfig, postgresql);
  const r = await dynamicQuery.save({name: 'zh', age: 13});
  await dynamicQuery.remove([r.id]);
  await dynamicQuery.find({}, pageResponse)
  console.log("pageResponse:")
  console.log(pageResponse)
})()
```

控制台输出如下

```shell
create
insert into "user_info"("name","age") values('zh',13) RETURNING id
findById
select *
                 from user_info
                 where id = '12'
remove
delete
               from user_info
               where id in (12)
findBySql countSql
select count(1)
 from user_info
findBySql findSql
select * 
 from user_info
 order by id  desc  limit 10 offset 0
pageResponse:
PageResponse {
  totalElements: 3,
  page: 0,
  size: 10,
  content: [
    { id: 3, name: 'zh', age: 13 },
    { id: 2, name: 'zh', age: 12 },
    { id: 1, name: 'zh', age: 13 }
  ],
  orderBy: 'id',
  direction: 'desc',
  totalPages: 1
}
```

### MySql例子

```javascript
const {MySql, DynamicQuery, PageResponse, switchSqlLog, TableConfig} = require('pg-dynamic-query');

(async () => {

  let mySql = new MySql({
      "host": "****",
      "port": 3306,
      "database": "****",
      "user": "****",
      "password": "****"
  });

  await mySql.connect();
  const userConfig = new TableConfig("user_info", [
    "id",
    "name",
    "age"
  ]);
  const pageResponse = new PageResponse()
  const dynamicQuery = new DynamicQuery(userConfig, mySql);
  const r = await dynamicQuery.save({id: 2, name: 'zh1', age: 13});
  await dynamicQuery.find({}, pageResponse)
  console.log("pageResponse:")
  console.log(pageResponse)
})()

```



## 详细说明

### DynamicQuery

```typescript
export class DynamicQuery {
    /**
     * 构造方法需要传入TableConfig和Postgresql.client属性
     */
    constructor(tableConfig: TableConfig, client: DbClient);

    /**
     * 传入query对象,和表别名(可不传),返回一个转化后的sql条件数组
     */
    getConditions(query: Query, tableAlias?: string): string[];

    /**
     *  传入query对象,和表别名(可不传),返回where sql 语句
     */
    getWhere(query: Query, tableAlias?: string): string

    /**
     * 传入OrderBy对象,返回order by sql 语句
     */
    static getOrderBy(orderBy: OrderBy): string

    /**
     * 单表分页查询方法,数据会在PageResponse.content属性中
     */
    find(query: Query, page: PageResponse): Promise<void>

    /**
     * 导航分页查询方法,前提需在TableConfig配置parents信息
     */
    navigationFind(query: Query, page: PageResponse): Promise<void>

    /**
     * 通过Sql对象分页查询
     */
    findBySql(sql: Sql, page: PageResponse): Promise<void>

    /**
     * 通过sql查询
     */
    findAllBySql(querySql: string): Promise<any[]>

    /**
     * 事务方法,func中的所有数据库操作都会在一个事务中
     */
    tx(func: () => Promise<any>): Promise<void>

    /**
     * 传入Query对象,和OrderBy,返回所有符合条件记录
     */
    findAll(query: Query, orderBy?: OrderBy): Promise<any>

    /**
     * 导航查询全部
     */
    navigationFindAll(query: Query, orderBy?: OrderBy): Promise<any>

    /**
     * 导航查询,返回第一个
     */
    navigationFindOne(query: Query): Promise<any>

    /**
     * 传入Query对象,返回第一个符合的对象
     */
    findOne(query: Query): Promise<any>

    /**
     * 传入sql,返回第一个记录
     */
    findOneBySql(sql: string): Promise<any>

    /**
     * 传入Query,返回符合条件的数目
     */
    count(query: Query): Promise<number>

    /**
     * 导航查询数目
     */
    navigationCount(query: Query): Promise<number>

    /**
     * 传入from where sql,返回符合数目
     */
    countBySql(sql): Promise<number>

    /**
     * 创建新记录,hasReturn是否返回创建的记录,默认不返回
     */
    create(data: object, hasReturn?: boolean): Promise<any>

    /**
     * 保存方法,有则更新,无则创建,只能用于单主键,会返回结果
     */
    save(data: object): Promise<any>

    /**
     *批量保存
     */
    saveAll(entities: object[]): Promise<object[]>;

    /**
     * 更新方法,isAllUpdate为true为全部更新,false为部分更新,默认为true,会返回结果
     */
    update(data: object, isAllUpdate: boolean): Promise<any>

    /**
     * 根据ids更新,会返回更新结果集
     */
    updateByIds(data: object, ids: any[], isAllUpdate: boolean): Promise<any[]>

    /**
     * 根据Query更新数据,不会返回结果集
     */
    updateByQuery(data: object, query: Query, isAllUpdate: boolean): Promise<void>

    /**
     * 根据Query更新数据,返回结果集
     */
    updateByQueryWithResult(data: object, query: Query, isAllUpdate: boolean): Promise<any[]>

    /**
     * 根据id查询
     */
    findById(id: any): Promise<any>

    /**
     * 根据ids查询
     */
    findByIds(ids: any[]): Promise<any[]>

    /**
     * 根据ids删除
     */
    remove(ids: any[]): Promise<void>

    /**
     * 根据Query查询,删除相应记录
     */
    removeByQuery(query: Query): Promise<void>
}
```

#### DynamicQuery用到的参数类

```typescript
export type Sql = {
    selectSql: string,
    formWhereSql: string,
    orderBySql: string
}

export type OrderBy = {
    orderBy: string,
    direction: Direction
}

export type Query = object;

export type PageRequest = {
    page: number, size: number, orderBy: string, direction: Direction
}

export type Direction = 'asc' | 'desc'

/**
 * DynamicQuery.tx传入的callback中获得
 */
export class Transaction {

}
```

#### Query要怎么写?

假定表格User,数据如下:

```json
[
  {
    "id": 1,
    "name": "张三",
    "age": 10
  },
  {
    "id": 2,
    "name": "李四",
    "age": 12
  }
]
```

**ps:Query的段名必须与数据库中字段名一样**

##### 基本用法

```js
// 表示 name = "张三"的记录
let query = {name: "张三"}
// 表示 name like "%张%"
query = {name: "%张%"}
// 表示 name is null
query = {name: "$null"}
// 表示 name is not null
query = {name: "$nn"}
```

##### 操作符

```js
// 表示 name in ["张三"]
query = {name: {$in: ["张三"]}}
// 表示 name not in ["张三"]
query = {name: {$nin: ["张三"]}}
// 表示 name = "张三"
query = {name: {$eq: "张三"}}
// 表示 name != "张三"
query = {name: {$ne: "张三"}}
// 表示 age >= 18
query = {age: {$gte: 18}}
// 表示 age > 18
query = {age: {$gt: 18}}
// 表示 age <= 18
query = {age: {$lte: 18}}
// 表示 age < 18
query = {age: {$lt: 18}}
// 表示 age between 0 and 10
query = {age: {$between: [0, 10]}}
// 表示 name = "张三" or age = 18
query = {
  $or: {
    name: "张三",
    age: 18
  }
}
// 表示 name = "张三" and age = 18
query = {
  $and: {
    name: "张三",
    age: 18
  }
}
```

##### 另一种or,and 和in

```js
// 表示 (name = "张三") or (age = 18)
query = {
  $or: [
    {
      name: "张三"
    },
    {
      age: 18
    }
  ]
}
// 表示 (name = "张三") and (age = 18)
query = {
  $and: [
    {
      name: "张三"
    },
    {
      age: 18
    }
  ]
}
// 表示 name in ["张三"]
query = {
  name: ["张三"]
}

```

#### 导航查询

以navigation开始的方法都是导航查询

使用导航查询需在**TableConfig**中配置好ParentConfig,例子:

假定表数据如下

table_user:

```json
[
  {
    "id": 1,
    "name": "张三",
    "age": 10,
    "department_id": 1
  },
  {
    "id": 2,
    "name": "李四",
    "age": 12,
    "department_id": 2
  }
]
```

table_department

```json
[
  {
    "id": 1,
    "name": "部门1"
  },
  {
    "id": 2,
    "name": "部门2"
  }
]
```

TableConfig配置如下

```js
const UserConfig = new TableConfig('table_user', [
  'id',
  'name',
  'age',
  'department_id',
]);
UserConfig.parents.push({
  parentId: 'department_id',
  parentIdName: 'id',
  parentObject: 'department',
  parentTable: 'table_department'
})
```

导航查询可对于父表字段进行过滤,query如下:

```js
// 表示 department.name = "部门1"
query = {
  department: {
    name: "部门1",
  }
}
```

#### 事务

```javascript
// 将方法传入DynamicQuery实例的tx方法中,方法中会获得transaction对象,然后将它传入需要在同一事务的方法中
await dynamicQuery.tx(async transaction => {
    const r = await dynamicQuery.save({name: 'zh', age: 13}, transaction);
    throw new Error("test")
    await dynamicQuery.remove([r.id], transaction);
    await dynamicQuery.find({}, pageResponse, transaction)
    console.log("pageResponse:")
    console.log(pageResponse)
})
```



### switchSqlLog()

```typescript
/**
 * 设置是否打印sql
 */
export function switchSqlLog(b: boolean);
```

### PageResponse

```typescript
export class PageResponse {
    /**
     * 分页查询所使用的类
     * totalElements 总条数
     * page 页码,从0开始
     * size 每页展示数据条数
     * content 具体数据的数组
     * totalPages 总页数
     * orderBy 根据什么字段排序,多字段会以','隔开
     * direction 正逆序,值为:'asc' | 'desc'
     */
    totalElements: number;
    page: number;
    size: number;
    content: any[];
    totalPages: number;
    orderBy: string;
    direction: Direction;

    constructor()

    /**
     * of方法可从req.query获取
     * PageRequest对象{page: number, size: number, orderBy: string, direction: Direction},
     * 并返回一个PageResponse对象
     */
    static of(req): PageResponse

    /**
     * 从req.query获取PageRequest对象
     */
    static getPageAndSize(req): PageRequest
}
```

### TableConfig

```ts
export class TableConfig {
    /**
     * table 是设置表名
     * columnSet 是设置所有字段名
     * idName 是设置id字段名,默认为id
     * jsonColumn 是设置json格式的所有字段名
     * createTime 设置创建时间字段名,设置后,会自动添加创建时间
     * updateTime 设置更新时间字段名,设置后,会自动添加或更新更新时间
     * parents 用来配置父表相关信息,这些信息会在ParentConfig中说明,这些信息是用来导航查询用的
     */
    table: string;
    columnSet: string[];
    idName: string;
    jsonColumn: string[]
    createTime: string;
    updateTime: string;
    parents: ParentConfig[];

    constructor(table: string, columnSet: string[]);
}
```

#### ParentConfig

```typescript
export type ParentConfig = {
    /**
     * parentId 设置表中的父表的id字段,也即外键字段
     * parentObject 设置导航查询中,父表数据所在字段
     * parentTable 设置父表表名
     * parentIdName 设置父表主键字段名
     */
    parentId: string;
    parentObject: string;
    parentTable: string;
    parentIdName: string;
}

```

### DbClient,Postgresql,MySql

```typescript
// Postgresql是DbClient具体实现
export class Postgresql extends DbClient {

}

// MySql是DbClient具体实现,构造方法,可以额外配置connectionLimit参数
export class MySql extends DbClient {
    constructor({host, port, database, user, password, connectionLimit})
}

export class DbClient {
    /**
     * host, port, database, user, password用来配置连接信息
     */
    host: string
    port: number
    database: string
    user: string
    password: string

    constructor({host, port, database, user, password})

    /**
     * 连接方法
     */
    connect(): Promise<any>
}
```