deno如何连接mysql_Deno MySQL 增删查改 (包含页面)

一起来完成以下步骤:

使用之前工程代码(09.MySQL增删查改API)),如果你还没有看过09视频，建议看一下

添加CRUD页面，使用ejs 页面模板

修改Controller相应的CRUD代码(Service到Repository层无需修改代码)

测试

controllers/employeeController.ts

//后台redirect

import { Context, REDIRECT_BACK } from "https://deno.land/x/oak/mod.ts";

import { Employee } from "../models/employee.ts";

import employeeService from "../services/employeeService.ts";

//解析Form

import { multiParser } from 'https://raw.githubusercontent.com/deligenius/multiparser/master/mod.ts'

const { cwd } = Deno;

class Controller {

static async list(ctx: any) {

//cwd获取当前工程目录

//注意 ' !== `

//获取所有员工

const employees = await employeeService.findAll();

ctx.render(`${cwd()}/views/list.ejs`, {

data: { employees }

});

}

//新增

static async new(ctx: any) {

let employee = {} as Employee;

ctx.render(`${cwd()}/views/form.ejs`, {

data: employee

});

}

//保存

static async save(ctx: Context) {

const strEmployee = JSON.stringify(await multiParser(ctx.request.serverRequest));

const saveEmployee: Employee = JSON.parse(strEmployee);

//如果ID是空，将保存。否则修改

if (!saveEmployee.id) {

const result = await employeeService.save(saveEmployee);

} else {

const result = await employeeService.update(saveEmployee);

}

//保存成功后调转到list页面

ctx.response.redirect(REDIRECT_BACK, "/");

}

//修改页面

static async edit(ctx: any) {

const id = ctx.params.id;

//根据ID获取员工

const employees = await employeeService.findById(id);

const updateEmployee = employees[0];

ctx.render(`${cwd()}/views/form.ejs`, {

data: updateEmployee

});

}

//删除

static async delete(ctx: any) {

const id = ctx.params.id;

//根据ID获取员工

const result = await employeeService.deleteById(id);

//删除成功后调转到list页面

ctx.response.redirect(REDIRECT_BACK, "/");

}

}

export default Controller;

database/database.ts

import { Client } from "https://deno.land/x/mysql/mod.ts"

const client = await new Client().connect({

hostname: "127.0.0.1",

username: "root",

db: "deno",

poolSize: 3,

password: "123456"

});

export default client;

models/employee.ts

export interface Employee{

id: number,

name: string,

age: number

}

routers/employeeRouter.ts

import { Router } from "https://deno.land/x/oak/mod.ts";

import Controller from "../controllers/employeeController.ts";

const router = new Router();

router.get("/",Controller.list);

//新增页面

router.get("/form",Controller.new);

//修改页面

router.get("/form/:id",Controller.edit);

//保存

router.post("/save",Controller.save);

//删除

router.get("/delete/:id",Controller.delete);

export default router;

services/employeeService.ts

import { Employee } from "../models/employee.ts";

import emlpoyeeRepo from "../repositories/employeeRepo.ts";

class employeeService{

static async save(employee: Employee){

return emlpoyeeRepo.save(employee);

}

static async update(employee: Employee){

return emlpoyeeRepo.update(employee);

}

static async findById(id: number){

return emlpoyeeRepo.findById(id);

}

static async findAll(){

return emlpoyeeRepo.findAll();

}

static async deleteById(id: number){

return emlpoyeeRepo.deleteById(id);

}

}

export default employeeService;

repositories/employeeRepo.ts

import client from "../database/database.ts";

import { Employee } from "../models/employee.ts";

class employeeRepo {

//新增

static async save(employee: Employee) {

let result = await client.execute(

`insert into employee(name,age) values(?,?)`,

[employee.name, employee.age]);

employee.id = Number(result.lastInsertId);

console.log(result);

return employee;

}

//更新

static async update(employee: Employee) {

let result = await client.execute(`update employee set name = ?,age= ? where id = ?`,

[employee.name, employee.age, employee.id]);

console.log(result);

return employee;

}

//根据ID获取

static async findById(id: number) {

const employee = await client.query(

"select * from employee where id = ?",

[id]

);

console.log(employee);

return employee;

}

//获取所有

static async findAll() {

const employees = await client.query(

"select * from employee"

);

console.log(employees);

return employees;

}

//根据ID删除

static async deleteById(id: number) {

let result = await client.execute(`delete from employee where id = ?`, [id]);

console.log(result);

return true;

}

}

export default employeeRepo;

main.ts

import { Application } from "https://deno.land/x/oak/mod.ts"

import {viewEngine,engineFactory,adapterFactory} from "https://deno.land/x/view_engine/mod.ts";

import router from "./routers/employeeRouter.ts";

const ejsEngine = engineFactory.getEjsEngine();

const oakAdapter = adapterFactory.getOakAdapter();

const app = new Application();

app.use(viewEngine(oakAdapter,ejsEngine));

app.use(router.routes());

app.use(router.allowedMethods());

console.log("Server Port 8000");

await app.listen({port: 8000 })

views/form.html

Add/Edit Employee

Name

Age

Address

Cancel

Manage Employees

class="material-icons"> Add New Employee

#NameAgeEmailAddressPhoneActions

thomashardy@mail.com89 Chiaroscuro Rd, Portland, USA(171) 555-2222

title="Edit">

data-toggle="tooltip" title="Delete">