TypeScript——orm实现

发表于 | 分类于

基于node,typescript封装，安装步骤略。

新建nodejs项目

npm init

npm install –save mysql

简单封装
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
'use strict';
const mysql=require('mysql');
module.exports = {
    config: {
        host: '210.14.139.118',
        port: 44257,
        database: 'jk_newcenter',
        user: 'jk_newcenter',
        password: 'aMF3va%d$cc'
    },
    connection: null,
    //创建连接池并连接
    openConnection: function (callback) {
        this.connection = mysql.createConnection(this.config);
    },
    closeConnection: function () {
        var me = this;
        me.connection.end(function (err) {
            console.log(err);
        });
    },

    execute: function (config) {
        const me = this;
        this.openConnection();
        me.connection.query(config.sql, config.params, function (err, res) {
            if (err) {
                console.log(err);
            } else if (config.callback) {
                config.callback(res);
            }
            // 关闭数据库连接
            me.closeConnection();
        });
    }
};
完整封装

连接池配置connection.ts：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
import * as mysql from "mysql";

const mysqlPool = mysql.createPool({
    host: "172.16.3.34",
    user: "root",
    password: "root",
    port: 3306,
    database: "sonar",
    connectionLimit: 10000
});

const timeout = 4000;

export interface ISqlResults {
    results: Array<any>;
    fields: Object;
}

interface IQueryObject {
    [name: string]: string | number
}

// 实现增删改查基本方法
export const sqlQuery = (sql: mysql.QueryOptions) => new Promise((resolve, reject) =>
    mysqlPool.query(sql, (err, results, fields) => err ? reject(err) : resolve(<ISqlResults>{results, fields}))
);

export const and = (dataObject: IQueryObject) => {
    let queryString = "";
    Object.keys(dataObject).forEach(key => {
        queryString += mysql.escape(key) + "=" + dataObject[key] + " and "
    });
    return queryString.replace(/\sand\s$/, "")
};

export const tableQuery = (table: string, condition: IQueryObject | string) => sqlQuery({
    sql: typeof condition === "string" ?
        mysql.format("select * from ?? where ??", [table, condition]) :
        mysql.format("select * from ?? where ?", [table, condition]),
    timeout
});


export const insertTable = (table: string, data: IQueryObject | Array<string | number>) => sqlQuery({
    sql: Array.isArray(data) ?
        mysql.format("insert into ?? values(??)", [table, (<Array<string | number>>data).join(", ")]) :
        mysql.format("insert into ?? set ?", [table, data]),
    timeout
});

export const updateTable = (table: string, update: IQueryObject, condition: IQueryObject | string) => sqlQuery({
    sql: typeof condition === "string" ?
        mysql.format("update ?? set ? where ??", [table, update, condition]) :
        mysql.format("update ?? set ? where ?", [table, update, condition]),
    timeout
});

export const deleteRow = (table: string, condition: IQueryObject | string) => sqlQuery({
    sql: typeof condition === "string" ?
        mysql.format("delete from ?? where ??", [table, condition]) :
        mysql.format("delete from ?? where ?", [table, condition]),
    timeout
});

export default mysqlPool;

ORM核心操作MyOrm.ts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
import { sqlQuery, insertTable, deleteRow, tableQuery, updateTable, ISqlResults } from "./connection";
import { format } from "mysql";

export interface IField {
    [name: string]: string | number
}

export interface IError {
    err: boolean;
    message?: string;
}

export interface IORM<TableType, TableTypeRest> {
    fieldMap: IField;
    map: IField;
    table: string;
    defaultValue: TableType;

    fetchAll (condition: TableTypeRest | string): Promise<Array<TableType>>;

    fetch (query: TableTypeRest | string): Promise<TableType>;

    insert (data: TableType): Promise<IError>;

    update (data: TableTypeRest, condition: TableTypeRest | string): Promise<IError>;

    delete (condition: TableTypeRest | string): Promise<IError>;
}

//ORM framework
export default class MyOrm<Type, TypeRest> implements IORM<Type, TypeRest> {
    public fieldMap: IField;
    public map: IField;
    public table: string;
    public defaultValue: Type;

    public async delete (condition: TypeRest | string): Promise<IError> {
        const dbCondition: IField | string = typeof condition === "string" ? condition :
            Object.keys(condition).reduce((acc, curKey) => {acc[this.fieldMap[curKey]] = condition[curKey];
                return acc;
            }, {});
        const { results, fields } = <ISqlResults>await deleteRow(this.table, dbCondition);
        return results ? <IError>{ err: false } :
            <IError>{
                err: true,
                message: "you have err on delete from " + this.table
            };
    }

    public async fetch (query: TypeRest | string): Promise<Type> {
        const dbQuery: IField = typeof query === "string" ? query :
            Object.keys(query).reduce((acc, curKey) => {
                acc[this.fieldMap[curKey]] = query[curKey];
                return acc;
            }, {});
        let { results, fields } = <ISqlResults>await tableQuery(this.table, dbQuery);

        results.length || (results = [{}]);
        Array.isArray(results) && (results = results[0]);
        return <Type>Object.keys(results).reduce((acc, curKey) => {
            acc[this.map[curKey]] = results[curKey];
            return acc
        }, {});
    }

    public async fetchAll (condition?: TypeRest | string): Promise<Array<Type>> {

        const { results, fields } = <ISqlResults>await sqlQuery({
            sql: !condition ? format("select * from ??", [this.table]) :
                typeof condition === "string" ?
                    format("select * from ?? where ??", [this.table, condition]) :
                    format("select * from ?? where ?", [this.table, condition]),
            timeout: 2000
        });

        return <Array<Type>>results.map(value => Object.keys(value).reduce((acc, curKey) => {
            acc[this.map[curKey]] = value[curKey];
            return acc;
        }, {}));
    }

    public async insert (data: Type): Promise<IError> {
        const dbData: IField = Object.keys(data).reduce((acc, curKey) => {
            acc[this.fieldMap[curKey]] = data[curKey];
            return acc;
        }, {});
        const { results } = <ISqlResults>await insertTable(this.table, dbData);
        return results ? <IError>{ err: false } : <IError>{ err: true, message: "you have some error in insert data" };
    }

    public async update (data: TypeRest, condition: TypeRest | string): Promise<IError> {
        const dbData: IField = Object.keys(data).reduce((acc, curKey) => {
            acc[this.fieldMap[curKey]] = data[curKey];
            return acc;
        }, {});
        const dbCondition: IField | string = typeof condition === "string" ? condition : Object.keys(condition).reduce((acc, curKey) => {
            acc[this.fieldMap[curKey]] = condition[curKey];
            return acc;
        }, {});
        console.log(dbData,dbCondition);
        const { results } = <ISqlResults>await updateTable(this.table, dbData, dbCondition);
        return results ? <IError>{ err: false } : { err: true, message: "you have message in update" };
    }
}

测试：创建实体：User.ts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

import ORM from "./MyOrm";

export interface IUserType {
    readonly id: number;
    readonly firstName: string;
    readonly lastName: string;
    readonly age: number;
}

export interface IUserTypeRest {
    readonly id?: number;
    readonly firstName?: string;
    readonly lastName?: string;
    readonly age?: number;
}
// 只需要继承ORM类即可
export default class User extends ORM<IUserType, IUserTypeRest> {
    // 类属性与数据库列字段的映射
    public fieldMap = {
        id: "id",
        firstName: "firstName",
        lastName: "lastName",
        age: "age"
    };
    // 默认值
    public defaultValue: IUserType = {
        id: 0,
        firstName: "",
        lastName: "",
        age: 0
    };
    public table: string = "user"; // 指定数据库表名

    public map = Object.keys(this.fieldMap).reduce((acc, curKey) => {
        acc[this.fieldMap[curKey]] = curKey;
        return acc;
    }, {});
}

测试方法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import User, {IUserType} from "./User";

const user = new User();

async function main() {
    const user = new User();
    /*await user.insert(<IUserType>{
        id: 10,
        firstName: "hu",
        lastName: "hu",
        age: 20
    });*/
    const data = await user.fetchAll();
    console.log(data)
}
main();
分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多