创建连接数据库的文件dbOpne
const mysql=require("mysql"); //引入数据库模块
function query(sql,arr,callback) {
//与数据库建立连接
const db=mysql.createConnection({
host:'localhost',//数据库地址
user:'root', //账号
password:'',//密码
port:3306,//端口号
database:'property'
})
db.connect();//连接
db.query(sql,arr,callback);
db.end();
}
module.exports = {
query:query
};
在需要操作的页面引入
const dbOpen = require('./dbOpen');//创建数据库对象
页面的增加
//新增
hactivity.post("/addhactivity",async (req,res)=>{//“/addhactivity”接口名称
let ActName = req.body.ActName;
let ActImag = req.body.ActImag;
let Content =req.body.Content
let Actaddress = req.body.Actaddress;
let StartTime = req.body.StartTime;
let EndTime =req.body.EndTime
let ActState = req.body.ActState;
let ActSend = req.body.ActSend;
let addResult = await insertShopCart(ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend);
//用异步函数等待数据返回
if(addResult>0){
res.send({code:200,message:"添加成功"})
}else{
res.send({code:201,message:"加入失败"})
}
});
//新增操作数据库
function insertShopCart(ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend){
return new Promise((resolve, reject)=>{
let sql ="INSERT INTO hactivity(ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend) VALUES(?,?,?,?,?,?,?,?)";
//有几个字段就要有几个?sql语句字段名字一定要与传过来的数组顺序一样
dbOpen.query(sql,[ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend],(err,data)=>{
if(err){
console.log(err);
reject(err);
}else{
resolve(data.affectedRows);
}
})
})
}
删除
//删除
hactivity.get("/delhactivity",async (req,res)=>{
let Id = req.query.Id;
let result = await delShopCartProduct(Id)
if(result>0){
res.send({code:200,message:" 删除成功 "});
}else {
res.send({code:201,message:" 删除失败"});
}
});
//删除操作数据库
function delShopCartProduct(Id){
return new Promise((resolve,reject)=>{
let sql = "delete from hactivity where Id = ?";
dbOpen.query(sql,[Id],(err,data)=>{
if (err){
console.log(err);
reject(err);
}else {
resolve(data.affectedRows);
}
})
})
}
修改
//修改
hactivity.post("/updatehactivity",async (req,res)=>{
let ActName = req.body.ActName;
let ActImag = req.body.ActImag;
let Content =req.body.Content
let Actaddress = req.body.Actaddress;
let StartTime = req.body.StartTime;
let EndTime =req.body.EndTime
let ActState = req.body.ActState;
let ActSend = req.body.ActSend;
let id = req.body.id ;
let result = await updateShopCardNum(id,ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend);
if(result>0){
res.send({code:200,message:"修改成功"});
}else{
res.send({code:201,message:"修改失败"});
}
})
//修改操作数据库
function updateShopCardNum(id,ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend){
return new Promise((resolve, reject)=>{
let sql = "update hactivity set ActName=?,ActImag=?,Content=?,Actaddress=?,StartTime=?,EndTime=?,ActState=?,ActSend=? where Id=?";
dbOpen.query(sql,[id,ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend],(err,data)=>{
if(err){
reject(err);
}else{
resolve(data.affectedRows);
}
})
})
}
查询
//查询
hactivity.post('/hactivityselect',function (req,res) {
let act = req.body.ActName
let ActName = '%'+act+'%';//模糊查询参数
let sql=`select * from hactivity WHERE ActName LIKE ?`//模糊查询
dbOpen.query(sql,[ActName],function (err,data) {
if(err){
console.log(err);
res.send({code:201,message:"查询失败"});
}else{
res.send({code:200,data:data});
}
})
})
页面完整代码
const myexpress = require("express");//引入express
const hactivity = myexpress.Router(); //创建router对象
const dbOpen = require('./dbOpen');//创建数据库对象
//渲染页面
hactivity.post('/hactivitysearchAll',function (req,res) {
//
let sql=`select * from hactivity`
dbOpen.query(sql,[],function (err,data) {
if(err){
console.log(err);
res.send("数据库出错,请联系管理员");
}else{
res.send({code:200,data:data});
}
})
})
//查询
hactivity.post('/hactivityselect',function (req,res) {
let act = req.body.ActName
let ActName = '%'+act+'%'
let sql=`select * from hactivity WHERE ActName LIKE ?`
dbOpen.query(sql,[ActName],function (err,data) {
if(err){
console.log(err);
res.send({code:201,message:"查询失败"});
}else{
res.send({code:200,data:data});
}
})
})
//添加
function insertShopCart(ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend){
return new Promise((resolve, reject)=>{
let sql ="INSERT INTO hactivity(ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend) VALUES(?,?,?,?,?,?,?,?)";
dbOpen.query(sql,[ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend],(err,data)=>{
if(err){
console.log(err);
reject(err);
}else{
resolve(data.affectedRows);
}
})
})
}
//修改操作数据库
function updateShopCardNum(id,ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend){
return new Promise((resolve, reject)=>{
let sql = "update hactivity set ActName=?,ActImag=?,Content=?,Actaddress=?,StartTime=?,EndTime=?,ActState=?,ActSend=? where Id=?";
dbOpen.query(sql,[id,ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend],(err,data)=>{
if(err){
reject(err);
}else{
resolve(data.affectedRows);
}
})
})
}
//删除操作数据库
function delShopCartProduct(Id){
return new Promise((resolve,reject)=>{
let sql = "delete from hactivity where Id = ?";
dbOpen.query(sql,[Id],(err,data)=>{
if (err){
console.log(err);
reject(err);
}else {
resolve(data.affectedRows);
}
})
})
}
//新增
hactivity.post("/addhactivity",async (req,res)=>{
let ActName = req.body.ActName;
let ActImag = req.body.ActImag;
let Content =req.body.Content
let Actaddress = req.body.Actaddress;
let StartTime = req.body.StartTime;
let EndTime =req.body.EndTime
let ActState = req.body.ActState;
let ActSend = req.body.ActSend;
let addResult = await insertShopCart(ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend);
if(addResult>0){
res.send({code:200,message:"添加成功"})
}else{
res.send({code:201,message:"加入失败"})
}
});
//删除
hactivity.get("/delhactivity",async (req,res)=>{
let Id = req.query.Id;
let result = await delShopCartProduct(Id)
if(result>0){
res.send({code:200,message:" 删除成功 "});
}else {
res.send({code:201,message:" 删除失败"});
}
});
//修改
hactivity.post("/updatehactivity",async (req,res)=>{
let ActName = req.body.ActName;
let ActImag = req.body.ActImag;
let Content =req.body.Content
let Actaddress = req.body.Actaddress;
let StartTime = req.body.StartTime;
let EndTime =req.body.EndTime
let ActState = req.body.ActState;
let ActSend = req.body.ActSend;
let id = req.body.id ;
let result = await updateShopCardNum(id,ActName,ActImag,Content,Actaddress,StartTime,EndTime,ActState,ActSend);
if(result>0){
res.send({code:200,message:"修改成功"});
}else{
res.send({code:201,message:"修改失败"});
}
})
module.exports=hactivity;