egg中数据库的应用
Opened this issue · 0 comments
kekobin commented
什么是 ORM ？
对象关系映射（英语：Object Relational Mapping，简称 ORM，或 O/RM，或 O/R mapping），是一种程序设计技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。从效果上说，它其实是创建了一个可在编程语言里使用的“虚拟对象数据库”。
类似于 J2EE 中的 DAO 设计模式，将程序中的数据对象自动地转化为关系型数据库中对应的表和列，数据对象间的引用也可以通过这个工具转化为表。这样就可以很好的解决我遇到的那个问题，对于表结构修改和数据对象操作是两个独立的部分，从而使得代码更好维护。其实是否选择 ORM 框架，和以前前端是选择模板引擎还是手动拼字符串一样，ORM 框架避免了在开发的时候手动拼接 SQL 语句，可以防止 SQL 注入，另外也将数据库和数据 CRUD 解耦，更换数据库也相对更容易。
sequelize / egg-sequelize
sequelize 是 Node.js 社区比较流行的一个 ORM 框架
定义 Model ：
const User = sequelize.define("user", {
username: {
type: Sequelize.STRING
},
password: {
type: Sequelize.STRING
}
});
Sequelize 默认会添加 createdAt 和 updatedAt，这样可以很方便的知道数据创建和更新的时间。如果不想使用可以通过设置 attributes 的 timestamps: false；
Sequelize 支持丰富的数据类型，例如：STRING、CHAR、TEXT、INTEGER、FLOAT、DOUBLE、BOOLEAN、DATE、UUID、JSON 等多种不同的数据类型
Model 对象
find：搜索数据库中的一个特定元素，可以通过 findById 或 findOne；
findOrCreate：搜索特定元素或在不可用时创建它；
findAndCountAll：搜索数据库中的多个元素，返回数据和总数；
findAll：在数据库中搜索多个元素；
复杂的过滤/ OR / NOT 查询；
使用 limit(限制)，offset(偏移量)，order(顺序)和 group(组)操作数据集;
count：计算数据库中元素的出现次数；
max：获取特定表格中特定属性的最大值；
min：获取特定表格中特定属性的最小值；
sum：特定属性的值求和；
create：创建数据库 Model 实例；
update：更新数据库 Model 实例；
destroy：销毁数据库 Model 实例。
具体用法： Sequelize-Model
Migrations
在数据库操作设计中，我们一般是通过脚本提前生成表结构，如果手动写创建表的 SQL，每次修改表结构其实是一件麻烦事。Sequelize 提供了Migrations 帮助创建或迁移数据库，egg-sequelize 里面也提供了方便的方法。
egg中mysql使用
由于egg-sequelize是支持mysql的，所以可以结合一起使用，即可以使用上mysql数据库，又可以通过egg-sequelize操作数据库，屏蔽了写sql的麻烦(对于大多数前端来说，写sql很麻烦、容易出错、也不安全).
- 1.安装
npm i -S egg-sequelize
npm i -S mysql2
- 2.配置
// /config/config.local.js
// ORM base configuration
exports.sequelize = {
dialect: 'mysql', // 链接的数据库类型，也可以是mongodb等其他数据库
host: '127.0.0.1',
port: 3306,
username: 'root',
password: 'root',
database: 'test_db',
define: { // model的全局配置
// timestamps: true, // 添加create,update,delete时间戳
// paranoid: true, // 添加软删除
// freezeTableName: true, // 防止修改表名为复数
underscored: false, // 防止驼峰式字段被默认转为下划线
},
timezone: '+8:00', // 由于orm用的UTC时间，这里必须加上东八区，否则取出来的时间相差8小时
dialectOptions: { // 让读取date类型数据时返回字符串而不是UTC时间
dateStrings: true,
typeCast(field, next) {
if (field.type === 'DATETIME') {
return field.string();
}
return next();
},
},
};
- 3.定义model(即表结构)
// 如：/app/model/user.js
module.exports = app => {
const { STRING, INTEGER } = app.Sequelize;
const User = app.model.define('user', {
id: { type: INTEGER, allowNull: false, autoIncrement: true, primaryKey: true },
username: { type: STRING(30), allowNull: false, unique: true, field: 'user_name' },
email: { type: STRING(30), allowNull: false },
}, {
createdAt: 'created_at',
updatedAt: 'updated_at',
});
return User;
};
注意：定义的名称为user，但是必须先在数据库中创建表users，即表默认必须是负数名，结构为单数名。
- 4.Service中使用
// /app/service/user.js
async getUsers() {
const { ctx } = this;
return ctx.model.User.findAndCountAll();
}
egg中oracle使用
egg-sequelize是不支持oracle，而且市面上也没有支持它的orm模型，所以只能自己手写了。
- 1.安装
npm i -S egg-oracle
npm i -S oracledb
- 2.配置
// /config/config.local.js
config.oracle = {
user: 'xx',
password: 'xx',
connectString: '//xxxx/ORCL', //oracle db
};
- 3.新建oracle目录，并定义基类，相当于之前的model
// /app/oracle/index.js
// https://oracle.github.io/node-oracledb/doc/api.html
const oracledb = require('oracledb');
class Oracle {
constructor(app) {
this.config = app.config.oracle;
}
/**
* @description sql语句执行
* @param sqlFuncName 自定义sql语句函数名
* @param dialectData 数据
*/
async excute(sqlFuncName, dialectData) {
oracledb.autoCommit = true;
const connection = await oracledb.getConnection(this.config);
const sql = this[sqlFuncName](dialectData);
const result = await connection.execute(sql);
connection.close();
const data = [];
const { metaData, rows, rowsAffected } = result;
// 非查询操作
if (!rows) {
return !!rowsAffected;
}
// 查询操作
for (let i = 0; i < rows.length; i++) {
const row = {};
for (let j = 0; j < metaData.length; j++) {
const key = this.camelKey(metaData[j].name);
row[key] = rows[i][j];
}
data.push(row);
}
return data;
}
/**
* @description 工具函数 将key转为驼峰
* @param "str"
*/
camelKey(str) {
const strArr = str.toLowerCase().split('_');
for (let i = 1; i < strArr.length; i++) {
strArr[i] = strArr[i].charAt(0).toUpperCase() + strArr[i].substring(1);
}
return strArr.join('');
}
}
module.exports = Oracle;
- 4.定义oracle的model
这里以user为例，定义它的CRUD模型操作
// /app/oracle/user.js
const Oracle = require('../index.js');
const SQL_MAP = {
name: 'NAME',
age: 'AGE',
male: 'MALE',
};
class userDialect extends Oracle {
create({ id, name, age, male, updateTime }) {
return `INSERT INTO BGT_AUTH ("ID","NAME","AGE","MALE","UPDATE_TIME") VALUES ('${id}','${name}','${age}','${male}',TO_DATE('${createTime}', 'yyyy-mm-dd HH24:mi:ss'))`;
}
// 查询总数
searchAll() {
return 'SELECT e."ID",e."NAME",e."AGE",e."MALE",e."UPDATE_TIME", COUNT(*) OVER () total FROM USER e';
}
search(data) {
const keys = Object.keys(data);
let { pageSize = 20, pageIndex = 1 } = data;
pageSize = parseInt(pageSize);
pageIndex = parseInt(pageIndex) < 1 ? 1 : parseInt(pageIndex);
let sql = 'SELECT emp.* FROM ( SELECT e."ID",e."NAME",e."AGE",e."MALE",e."UPDATE_TIME", ROWNUM rowno FROM USER e';
keys.forEach((key, index) => {
if (!data[key] || key === 'pageSize' || key === 'pageIndex') return;
let signal = '=';
// 模糊查找类name的用户
let value;
if (key === 'name') {
signal = ' LIKE ';
value = `'%${data[key]}%'`;
} else {
value = `'${data[key]}'`;
}
sql += ` ${index === 0 ? 'WHERE' : 'AND'} "${SQL_MAP[key]}"${signal}${value}`;
});
sql += ` ${sql.indexOf('WHERE') > 0 ? 'AND' : 'WHERE'} ROWNUM <= ${pageSize * pageIndex} ) emp WHERE emp.rowno > ${pageSize * (pageIndex - 1)}`;
return sql;
}
del({ id }) {
return `DELETE FROM USER WHERE "ID"='${id}'`;
}
edit({ id, name, age, male, updateTime }) {
return `UPDATE USER SET ${name ? `"NAME"='${name}',` : ''}${age ? `"AGE"='${age}',` : ''}${male ? `"MALE"='${male}',` : ''}${updateTime ? `"UPDATE_TIME"=TO_DATE('${updateTime}', 'yyyy-mm-dd HH24:mi:ss')` : ''} WHERE ID='${id}'`;
}
}
module.exports = userDialect;
注意：
1.查询时，最好使用主表-子表方式去查，分页和查询总数是要分开的。
2.对于时间，需要使用oracle自带函数:TO_DATE('${updateTime}', 'yyyy-mm-dd HH24:mi:ss')
3.sql语句中注意字段是string还是int，为string必须加上单引号，为int不能加，sql是强类型的，类型不对就会报错。
- 5.引入到全局context中
// /app.js
async didLoad() {
const { app } = this;
// 例如：加载自定义的目录
app.config.loaderDirs.forEach(dir => {
app.loader.loadToContext(
path.join(__dirname, `app/${dir}`),
`${dir}`,
{
fieldClass: `${dir}Classes`,
initializer(factory) {
if (typeof factory === 'function') {
return new factory(app);
}
},
}
);
});
}
现在就可以通过 this.ctx.oracle.user访问到user模型了。
-
- Service中使用
// /app/service/user.js
const Service = require('egg').Service;
class UserService extends Service {
// 新建账号
async create(body) {
return await this.ctx.oracle.user.excute('create', body);
}
async search(body) {
const dialect = this.ctx.oracle.user;
return dialect.excute('search', body)
}
async del(id) {
const dialect = this.ctx.oracle.user;
return await dialect.excute('del', { id });
}
async edit(body) {
const dialect = this.ctx.oracle.user;
const { id, name, age, male } = body;
const { format } = this.ctx.helper;
return await dialect.excute('edit', { id, name, age, male, updateTime: format(null, 'yyyy-MM-dd hh:mm:ss') });
}
}
module.exports = UserService;
Oracle深坑
oracle在insert插入时，必须commit一下，否则执行的命令是成功的，但数据库中却没有插进去。
具体解决：const oracledb = require('oracledb'); oracledb.autoCommit = true; // 关键是这句 const connection = await oracledb.getConnection({ user: '', password: '', connectString: '',// connect url }); const result = await connection.execute(sql); connection.close();
分页和总数目查询
SELECT
emp.*
FROM
(
SELECT
temp.*, ROWNUM AS rowIndex
FROM
(
SELECT
E .*, COUNT (*) OVER () AS total
FROM
USERS E
WHERE
(NAME = 'kebin' AND AGE = 20)
OR
(NAME = 'kate' AND AGE = 24)
AND ROWNUM > 0
ORDER BY
CREATE_TIME DESC
) temp
WHERE
ROWNUM > 0
) emp
WHERE
emp.rowIndex > 0
AND emp.rowIndex <= 20
代码演示
// /app/model/user.js
findDetail(data) {
const { pageSize = 20, pageIndex = 1, name, age } = data;
return `SELECT emp.*
FROM (
SELECT temp.*, ROWNUM AS rowIndex
FROM (
SELECT E.*, COUNT(*) OVER () AS total
FROM USERS E
WHERE
${name ? `NAME = '${name}' AND` : ''}
${age ? `AGE LIKE '${age}' AND` : ''}
ROWNUM > 0 ORDER BY CREATE_TIME DESC
) temp
WHERE ROWNUM > 0
) emp
WHERE
emp.rowIndex > ${pageSize * (pageIndex - 1)} AND
emp.rowIndex <= ${pageSize * pageIndex}`;
}
// /app/service/user.js
async search(body) {
const UserModel = this.ctx.model.user;
return new Promise(async (resolve, reject) => {
const result = await UserModel.findDeail(body).catch(e => {
reject(e);
});
resolve(data);
});
}
// /app/controller/user.js
async search() {
const { ctx } = this;
const { body } = ctx.request;
const { pageSize = 20, pageIndex = 1 } = body;
const result = await ctx.service.user.search(body);
// 拿到总数
let total = 0;
if (result[0]) {
total = result[0].total;
}
return ctx.success({
data: {
pageSize,
pageIndex,
total,
list: result,
},
});
}
参考
基于 Egg.js 框架的 Node.js 服务构建之用户管理设计
egg商城--权限管理篇
权限管理设计
rbac之管理员crud