上篇文章说到了不用mysql连接的增删改查
现在发一篇连接数据库的增删改查,这次是一个学习的这种连接的思路和增删改查的思路
以及一个分层的思路
当然还是先看页面,但是由于是功能的实现就没有写多好看
增删改查一应俱全,调用的数据库
由于下面的文件在不同文件夹里面,所以给一张文件夹的图,自己研究研究就好了
欢迎留言哦
app.js
const myexpress = require("express");
const studentRouter=require("./router/studentRouter");
const classRouter=require("./router/classRouter");
const logger = require("morgan");
const bodyParser=require('body-parser');
const app = myexpress();
app.use(logger('dev'));
app.use(myexpress.static(__dirname + "/public", { index: "/student.html" }));
app.use(bodyParser.json({linit:'2024kb'}));
app.use(bodyParser.urlencoded({extended:false}));
app.use(studentRouter);
app.use(classRouter);
app.listen(8888, function () {
console.log("服务已经启动");
})
路由
const myexpress=require("express");
const classController=require("../controller/classController");
const classRouter=myexpress.Router();
classRouter.get("/getClassList",classController.getClassList);
module.exports=classRouter;
这个是班级表的
const myexpress=require("express");
const studentController=require("../controller/studentController");
const studentRouter=myexpress.Router();
studentRouter.get("/getStudentList",studentController.getStudentList);
studentRouter.get("/delStudentById",studentController.delStudentById)
studentRouter.post("/addStudent",studentController.addStudent);
studentRouter.get("/getStudentInfoById",studentController.getStudentInfoById);
studentRouter.post("/updateStudentById",studentController.updateStudentById);
module.exports=studentRouter;
//这个是学生表单的
控制层的
const classModel = require("../model/classModel")
function getClassList(req, res) {
classModel.getClassList(function (err, data) {
if (err) {
res.send({ code: 500, message: "数据库出错" });
return
}
res.send({ code: 200, data });
})
}
module.exports = {
getClassList
}
const studentModel = require("../model/studentModel");
function getStudentList(req, res) {
let { studentName, classId } = req.query;
studentModel.getStudentList(studentName, classId, function (err, data) {
if (err) {
//错误日志,
console.log(err);
res.send({ code: 500, message: "数据库出错" })
return
}
// console.log(data);
res.send(data);
})
}
function delStudentById(req, res) {
let { id } = req.query;
studentModel.delStudentById(id, function (err, data) {
if (err) {
//错误日志,
res.send({ code: 500, message: "数据库出错" })
return
}
console.log(id)
if (data.affectedRows == 0) {
res.send({ code: 201, massage: "删除失败" })
} else {
res.send({ code: 200, massage: "删除成功" })
}
})
}
function addStudent(req, res) {
let { stuNO, stuName, address, sex, classId, age } = req.body;
studentModel.addStudent(stuNO, stuName, address, sex, classId, age, (err, data) => {
if (err) {
//错误日志,
console.log(err);
res.send({ code: 500, message: "数据库出错" })
return
}
// console.log(data);
if (data.affectedRows == 0) {
res.send({ code: 201, massage: "添加失败" })
console.log(234);
} else {
res.send({ code: 200, massage: "添加成功" })
console.log(235);
}
})
}
function getStudentInfoById(req, res) {
let { id } = req.query;
studentModel.getStudentInfoById(id, function (err, data) {
if (err) {
//错误日志,
res.send({ code: 500, message: "数据库出错" })
return;
}
res.send({ code: 200, data })
})
}
function updateStudentById(req, res) {
let { id, stuName, address, sex, classId, age } = req.body;
studentModel.updateStudentById(id, stuName, address, sex, classId, age, function (err, data) {
if (err) {
//错误日志,
console.log(err);
res.send({ code: 500, message: "数据库出错" })
return
}
// console.log(data);
if (data.affectedRows == 0) {
res.send({ code: 201, massage: "修改失败" })
} else {
res.send({ code: 200, massage: "修改成功" })
}
})
}
module.exports = {
getStudentList,
delStudentById,
addStudent,
getStudentInfoById,
updateStudentById
}
模型层的
const db = require("./dbConfig")
function getClassList(callback) {
let sql = "select *from class ";
db.query(sql, [], callback);
}
module.exports={
getClassList
}
const mysql=require("mysql");
function query(sql,params,callback){
const connect=mysql.createConnection({
host:"localhost",
user:"root",
password:"",
database:"nodejs",
port:3306
})
connect.connect();
connect.query(sql,params,callback);
//params 没有参数给空数组
connect.end();
}
module.exports={
query
}
const db = require("./dbConfig");
//查询和渲染
function getStudentList(studentName,classId,callback) {
let sql = `SELECT s.* ,c.className FROM student s join class c on s.classId=c.classId where 1=1
`;
let params=[];
if(studentName.trim().length!=0){
sql+="and stuName like ?";
params.push("%"+studentName+"%");
}
if(classId!="-1"){
sql+="and s.classId=?";
params.push(classId);
}
db.query(sql,params, callback)
}
//删除
function delStudentById(id, callback) {
let sql = "delete from student where id=?";
db.query(sql, [id], callback)
}
//获取
function addStudent(stuNO, stuName, address, sex, classId, age, callback) {
let sql = "INSERT INTO student (stuNO,stuName,address,sex,classId,age) VALUES (?,?,?,?,?,?)"
db.query(sql, [stuNO, stuName, address, sex, classId, age], callback)
}
//修改
function getStudentInfoById(id,callback){
let sql = `SELECT s.* ,c.className FROM student s join class c on s.classId=c.classId where s.id=?
`;
db.query(sql,[id],callback);
}
function updateStudentById(id, stuName, address, sex, classId, age, callback){
let sql=`
update student set stuName =?, address=? , sex=?, classId=? , age=? where id=? `;
db.query(sql,[stuName, address, sex, classId, age,id],callback);
}
module.exports = {
getStudentList,
delStudentById,
addStudent,
getStudentInfoById,
updateStudentById
}
静态public里面的 Ajax也在里面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Document</title>
<link rel="stylesheet" href="./css/bootstrap.css" />
<link rel="stylesheet" href="js/layui-v2.6.8/layui/css/layui.css" />
<style>
.red {
background-color: red;
color: white;
border-radius: 3px;
}
.green {
background-color: green;
color: white;
border-radius: 3px;
}
#tbStudent:empty::after {
content: "查询无数据";
color: red;
display: block;
}
.zhuti {
width: 100%;
height: 80px;
background-color: coral;
text-align: center;
color: #fff;
}
</style>
</head>
<body>
<div class="zhuti"><h1>学生管理</h1></div>
<div>
<input type="text" id="txtSearchName" placeholder="姓名" />
班级:<select name="selClass" id="selClass"></select>
<button class="btn btn-primary" id="btnSearch">搜索</button>
<button class="btn btn-default" id="btnAdd">添加</button>
</div>
<table class="table table-respose">
<thead>
<tr>
<th>id</th>
<th>学号</th>
<th>姓名</th>
<th>性别</th>
<th>班级</th>
<th>年龄</th>
<th>状态</th>
<th>时间</th>
<th>地址</th>
<th>操作</th>
</tr>
</thead>
<tbody id="tbStudent"></tbody>
</table>
<!-- 模态框 编辑 -->
<div class="modal fade" id="studentModel" tabindex="-1" role="dialog">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<button
type="button"
class="close"
data-dismiss="modal"
aria-label="Close"
>
<span aria-hidden="true">×</span>
</button>
<h4 class="modal-title">学生信息</h4>
</div>
<div class="modal-body">
<form action="#">
<input type="hidden" name="" id="txtId" />
<p><input type="text" placeholder="学号" id="txtStuNo" /></p>
<p><input type="text" placeholder="姓名" id="txtStuName" /></p>
<p><input type="text" placeholder="年龄" id="txtAge" /></p>
<p>
<select name="selSex" id="selSex">
<option>女</option>
<option>男</option>
</select>
</p>
<p>
<input type="text" name="" id="txtAddress" placeholder="地址" />
</p>
<p><select name="selClass" id="selEditClass"></select></p>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">
关闭
</button>
<button type="button" class="btn btn-primary" id="btnSave">
保存
</button>
</div>
</div>
</div>
</div>
<script src="js/jquery.js"></script>
<script src="js/bootstrap.js"></script>
<script src="js/layui-v2.6.8/layui/layui.js"></script>
<script>
let studentName = "";
let classId = "-1";
$(document).ready(function () {
getStudentList();
addDelEvent();
getClassList();
addSearchEvent();
addEditEvent();
addSaveEvent();
addTianjia();
//搜索
function addSearchEvent() {
$("#btnSearch").click(function () {
studentName = $("#txtSearchName").val();
classId = $("#selClass").val();
getStudentList();
});
}
function displayData(data) {
$("#tbStudent").html("");
let fragment = document.createDocumentFragment();
for (let i = 0; i < data.length; i++) {
$(fragment).append(` <tr>
<td>${data[i].id}</td>
<td>${data[i].stuNO}</td>
<td>${data[i].stuName}</td>
<td>${data[i].sex}</td>
<td>${data[i].className}</td>
<td>${data[i].age}</td>
<td>${
data[i].state == 1
? "<span class='green'>启用</span>"
: "<span class='red'>禁用</span>"
}</td>
<td>${new Date(data[i].create_time).toLocaleDateString()}</td>
<td>${data[i].address}</td>
<td>
<button class="del btn btn-danger" data-id='${
data[i].id
}'>删除</button>
<button class="edit btn btn-primary" data-id='${
data[i].id
}'>修改</button>
</td>
</tr>`);
}
$("#tbStudent").append(fragment);
}
//给删除按钮添加事件
function addDelEvent() {
$("#tbStudent").on("click", ".del", function (e) {
let id = $(e.target).attr("data-id");
$.ajax({
method: "get",
url: "/delStudentById",
data: { id },
success: function (data) {
if (data.code == 200) {
layer.msg(data.massage);
getStudentList();
} else {
layer.msg(data.massage);
}
},
});
});
}
function getStudentList() {
//发起Ajax请求
$.ajax({
method: "get",
url: "/getStudentList",
data: { studentName, classId },
success: function (data) {
displayData(data);
},
});
}
//下拉框的配置
function displayClassData(data) {
$("#selClass").html("<option value='-1'>全选</option>");
$("#selEditClass").html("");
let frag = document.createDocumentFragment();
data.forEach((item) => {
$(frag).append(
`<option value="${item.classId}">${item.className} </option>`
);
});
$("#selEditClass").append(frag.cloneNode(true));
$("#selClass").append(frag);
}
function getClassList() {
$.ajax({
method: "get",
url: "/getClassList",
success: function (data) {
if (data.code == 200) {
displayClassData(data.data);
} else {
layer.msg(data.massage);
}
},
});
}
//给编辑按钮添加事件
function addEditEvent() {
$("#tbStudent").on("click", ".edit", function (e) {
let id = $(e.target).attr("data-id");
$("#studentModel").modal("show");
displayStudentInfoById(id);
});
}
//点击保存按钮做一个判断,可以实现修改和添加
function addSaveEvent() {
$("#btnSave").click(function () {
let id = $("#txtId").val();
let stuNO = $("#txtStuNo").val();
let stuName = $("#txtStuName").val();
let age = $("#txtAge").val();
let address = $("#txtAddress").val();
let sex = $("#selSex").val();
let classId = $("#selEditClass").val();
$("#studentModel").modal("hide");
if ($("#txtId").val() != "") {
//给保存按钮做判断
console.log(123);
$.ajax({
method: "post",
url: "/updateStudentById",
data: { id, stuName, age, address, sex, classId },
success: function (data) {
if (data.code == 200) {
getStudentList();
} else {
layer.msg(data.massage);
}
},
});
} else {
$.ajax({
method: "post",
url: "/addStudent",
data: { stuNO, stuName, address, sex, classId, age },
success: function (data) {
if (data.code == 200) {
getStudentList();
} else {
layer.msg(data.massage);
console.log(stuNO);
}
},
});
}
});
}
//获取input框的信息
function displayStudentInfoById(id) {
//获取信息
$.ajax({
method: "get",
url: "/getStudentInfoById",
data: { id },
success: function (data) {
if (data.code == 200) {
$("#txtId").val(data.data[0].id);
$("#txtStuNo").val(data.data[0].stuNO);
$("#txtStuName").val(data.data[0].stuName);
$("#txtAge").val(data.data[0].age);
$("#txtAddress").val(data.data[0].address);
$("#selSex").val(data.data[0].sex);
$("#selEditClass").val(data.data[0].classId);
} else {
layer.msg(data.massage);
}
},
});
}
//点击添加按钮发生的事件
function addTianjia() {
$("#btnAdd").click(function () {
$("#txtId").val("");
$("#txtStuNo").val("");
$("#txtStuName").val("");
$("#txtAge").val("");
$("#txtAddress").val("");
$("#studentModel").modal("show");
});
}
});
</script>
</body>
</html>