Hapi+MySql项目实战数据库操作（四）
时间：2022-03-15 07:36
-
数据库访问
下面以Node的ORM框架Sequelize来操作数据库，Mysql为例。
配置数据库连接信息config/db_config.js：
1 //db_config.js
2 module.exports = {
3 database: ‘h_api‘,//库名
4 username: ‘root‘,//用户名
5 password: ‘123456‘,//密码
6 host: ‘localhost‘,//数据库地址
7 dialect: ‘mysql‘//数据库类型
8 }
定义用户模型文件modes/user.js，表映射
//user.js
module.exports = function(sequelize, DataTypes) {
var User = sequelize.define("user", {
id:{
type: DataTypes.INTEGER,
primaryKey:true,
allowNull:false,
autoIncrement: true,
},
name:DataTypes.STRING,
sex:DataTypes.BIGINT,
age:DataTypes.INTEGER,
},{
freezeTableName: true, // Model 对应的表名将与model名相同
timestamps: false
});
return User;
};
创建一个sequelize对象实例,连接数据库models/index.js新增代码如下：
//index.js
const Fs = require("fs");
const Path = require("path");
const Sequelize = require("sequelize");
const Config = require(‘../config/db_config‘);
let db = {};
//创建一个sequelize对象实例,连接数据库
let sequelize = new Sequelize(Config.database, Config.username, Config.password, {
host: Config.host,
dialect: Config.dialect,
pool: {
max: 5,
min: 0,
idle: 30000
}
});
Fs.readdirSync(__dirname).filter(function (file) {
return (file.indexOf(".") !== 0) && (file !== "index.js");
}).forEach(function (file) {
var model = sequelize["import"](Path.join(__dirname, file));
db[model.name] = model;
});
db.sequelize = sequelize;
module.exports = db;
配置server.js，代码如下:
//server.js
const models=require(‘./models‘);
//Connect database
var initDb = function(){
var sequelize = models.sequelize;
//Determine if the database connection is successful
sequelize.sync({force: false}).then(function() {
console.log("connection database successed");
}).catch(function(err){
console.log("connection failed due to error: %s", err);
});
};
initDb();
配置数据库完毕后，在路由handler中使用这个实例
routes文件夹下新建login.js，代码如下：
//login.js
const Joi = require(‘joi‘);
const controllers = require(‘../controllers‘);
let login = {
method: ‘get‘,
path: ‘/login‘,
handler: controllers.user.login
};
module.exports = login;
controllers文件夹新建index.js，遍历指定目录，require()每个文件，并返回一个包含这些模块嵌套的hash结构，代码如下
//index.js const requireDirectory = require(‘require-directory‘); module.exports = requireDirectory(module);
controllers文件夹新建user.js，数据库操作
//user.js
let Models = require(‘../models‘)
module.exports = {
login: function (request, reply) {
return Models.user.findAll({
where: {
name: request.query.name
}
}).then(function (result) {
let reponseMess = {};
if (result !== null) {
reponseMess = {
code: 100,
message: ‘success‘,
data: result
}
} else {
reponseMess = {
code: -100,
message: ‘fail‘,
data: ‘‘
}
}
return reponseMess;
});
}
};
老规矩，配置路由
输入地址：http://localhost:8090/login?name=1， 数据库自己新增一条name=1的记录
输出：json
//module.exports = [
//require(__dirname + ‘/hello.js‘),
//require(__dirname + ‘/staticfile.js‘),
require(__dirname + ‘/login.js‘)
//];
