express.js--连接数据库，并且增删改查(四)

使用数据库需要在电脑安装mysql，然后使用navicat

我没有下载mysql,我使用的是小皮里面的数据库，需要破解版的navicat可以私信我

安装mysql

npm i mysql

数据库的基本信息，我是直接写到配置文件里面的

config/index.js

module.exports = {
    app: {
        port: process.PORT || 3000,
        //jwt密钥
        jwtSecret: 'jwtSecret',
        //token过期时间
        expiresIn: 60 * 60 * 24,
    },
    db: {
        host: 'localhost', // 主机
        port: '3306', //端⼝，mysql默认端⼝就是3306
        user: 'root', // ⽤户名
        password: 'root', // 密码
        database: 'test1', // 数据库
    }
}

数据库连接查询

db/index.js

const { db } = require('../config')
const mysql = require('mysql');

// 连接mysql
function connect() {
    return mysql.createConnection(db)
}
// 新建查询连接
function querySql(sql) {
    const conn = connect();
    return new Promise((resolve, reject) => {
        try {
            conn.query(sql, (err, res) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(res);
                }
            })
        } catch (e) {
            reject(e);
        } finally {
            // 释放连接
            conn.end();
        }
    })
}
module.exports = querySql

controller/User.js

const jwt = require('jsonwebtoken')
const { app } = require('../config')
const db = require('../db');
const { decodeToken } = require("../middleware");
/* 生成token */
const generateToken = (data) => {
    const created = Math.floor(Date.now() / 1000);
    const token = jwt.sign(data, app.jwtSecret, {
        expiresIn: app.expiresIn,
    });
    return token;
}

/* 用户注册 */
const register = async (req, res, next) => {
    try {
        console.log(req.body);
        const { username, password } = req.body;
        if (!username || !password) {
            return res.status(200).json({
                code: 400,
                data: [],
                msg: "用户名或密码不能为空"
            });
        }
        //查询 user 表，根据username是否相同，判断用户是否存在
        const sql = `select * from user where username='${username}'`;
        const result = await db(sql);
        if (result.length) {
            return res.status(200).json({
                code: 400,
                data: [],
                msg: "用户已存在"
            });
        } else {
            //插入数据
            const insertSql = `insert into user (username,password) values ('${username}','${password}')`;
            await db(insertSql);
            const userId = await db(`select id from user where username='${username}'`);
            //把userId传入生成token的函数中
            return res.status(200).json({
                code: 200,
                data: {
                    token: generateToken({ userId: userId[0].id }),
                },
                msg: "注册成功"
            })
        }
    } catch (error) {
        next(error);
    }
}


/* 修改用户信息 */
const updateUser = async (req, res, next) => {
    try {
        let { userId } = decodeToken(req.headers.authorization)
        let { username } = req.body;
        if (!username || !userId) {
            return res.status(200).json({
                code: 400,
                data: [],
                msg: "操作失败"
            });
        }
        const sql = `update user set username='${username}' where id=${userId}`;
        await db(sql);
        return res.status(200).json({
            code: 200,
            data: [],
            msg: "修改成功"
        });
    } catch (error) {
        next(error);
    }
}
module.exports = {
    register,
    updateUser
}

routes/user.js

const router = require("express").Router()
const { register, updateUser } = require("../controller/User")
const { checkToken, decodeToken } = require("../middleware")
//注册
router.post("/register", register)
//修改个人信息
router.post("/update", checkToken, updateUser)
module.exports = router