目录
(3)获取数据库连接对象:pool.getConnection(function(err,conn){})
一、Node操作MySQL数据库
1、基本过程
(1)导入mysql模块
(2)创建连接配置:数据库服务器地址、端口号、用户名、密码、数据库名
(3)创建数据库连接对象
(4)通过数据库连接对象执行sql语句:query方法
(5)关闭数据库连接对象:end方法
2、数据库连接池技术
在程序启动时建立足够多的数据库连接对象.应用程序需要操作数据库时可以从连接池中申请连接对象
(1)导入mysql模块
(2)创建数据库连接池
mysql.createPool({
connectionLimit: 最大连接数,默认为0,
multipleStatements:是否允许执行多条sql语句,默认为false
host: 数据库服务器地址,
database: 数据库名,
user:用户名,
password: 密码
})
const mysql = require('mysql');
//创建数据库连接池
const pool = mysql.createPool({
connectionLimit: 20,
host: 'localhost',
port: 3306,
user: 'root',
password: '123456',
database: 'dbms'
});
//将数据库连接池对象导出
module.exports = pool;
(3)获取数据库连接对象:pool.getConnection(function(err,conn){})
(4)释放数据库连接对象:conn.release()
const express = require('express');
const pool = require('../db/config/mydb');
const router = express.Router();
/*
创建路由接口:用于查询employee表的所有信息
http://localhost:3000/dbtest/employees
*/
router.get('/employees',(req, res) => {
//通过数据库连接池对象来获取数据库的连接对象
pool.getConnection(function (err,conn) {
if (err){
console.log('获取连接对象失败!')
}else{
let sql = 'select * from employees'
conn.query(sql,function (err,results) {
if (err){
console.log('SQL语句执行失败!',err)
}else{
res.send(results)
}
})
}
})
});
/*
插入数据的路由接口:http://localhost:3000/dbtest/addemployee
*/
router.post('/addemployee',(req, res) => {
//1.获取客户端的请求数据
let params = {
id: null,
name: req.body.employee_name,
gender: req.body.gender,
birthday: req.body.birthday,
phone: req.body.phone,
address:req.body.address
}
console.log("请求参数:",params);
module.exports = router;
(5)示例:CRUD
① 插入信息sql语句:'insert into employee set ?'
② 删除信息sql语句:'delete from employees where id=?'
③ 更新信息sql语句:'update employees set birthday=?,phone=?,address=? where id=?'
const express = require('express');
const pool = require('../db/config/mydb');
const router = express.Router();
/*
创建路由接口:用于查询employee表的所有信息
http://localhost:3000/dbtest/employees
*/
router.get('/employees',(req, res) => {
//通过数据库连接池对象来获取数据库的连接对象
pool.getConnection(function (err,conn) {
if (err){
console.log('获取连接对象失败!')
}else{
let sql = 'select * from employees'
conn.query(sql,function (err,results) {
if (err){
console.log('SQL语句执行失败!',err)
}else{
res.send(results)
}
})
}
})
});
/*
插入数据的路由接口:http://localhost:3000/dbtest/addemployee
*/
router.post('/addemployee',(req, res) => {
//1.获取客户端的请求数据
let params = {
id: null,
name: req.body.employee_name,
gender: req.body.gender,
birthday: req.body.birthday,
phone: req.body.phone,
address:req.body.address
}
console.log("请求参数:",params);
//2.将数据插入到数据库中
let sql = 'insert into employees set ?';
pool.getConnection(function (err,conn) {
if (err){
console.log('获取数据库连接对象失败!')
}else{
conn.query(sql,params,function (err,result) {
if (err){
console.log('SQL语句执行失败!',err)
}else{
console.log(result)
res.send("插入成功!")
}
})
}
})
});
/*
删除数据接口:http://localhost:3000/dbtest/del
*/
router.delete('/del',(req, res) => {
//1.获取用户请求参数
let emp_id = req.body.id
//2.进行删除操作
pool.getConnection(function (err,conn){
if (err){
throw err
}else {
let sql = 'delete from employees where id=?';
conn.query(sql,emp_id,function (err,result) {
if (err){
console.log("SQL语句执行失败!",err)
}else {
res.send({
code: result.affectedRows,
msg: '删除成功!'
})
}
})
}
})
})
/*
更新数据接口:http://localhost:3000/dbtest/update
*/
router.put('/update',(req, res) => {
let params = [req.body.birthday,req.body.phone,req.body.address,req.body.id]
pool.getConnection(function (err,conn) {
if (err){
throw err
}else {
let sql = 'update employees set birthday=?,phone=?,address=? where id=?'
conn.query(sql,params,function (err,result) {
if(err){
console.log('SQL语句执行错误!',err)
}else{
res.send({
code: result.affectedRows,
msg: '更新成功'
})
}
})
}
})
})
module.exports = router;