mybatis-plus+Vue代码实现增删改查Excel导入导出(第一篇)
在Navicat中导入sql(复制下来然后在查询中粘贴运行就可以了)
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80020
Source Host : localhost:3306
Source Schema : the_sound
Target Server Type : MySQL
Target Server Version : 80020
File Encoding : 65001
Date: 25/04/2022 20:02:49
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
`username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '用户名',
`password` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '密码',
`nickname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '昵称',
`email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '邮箱',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '电话',
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '地址',
`create_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
`avatar_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '头像',
`role` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '角色',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 45 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES (1, 'admin', 'admin', '程序员青戈', 'admin@qq.com', '13988997788', '安徽合肥', '2022-01-22 21:10:27', 'http://localhost:9090/file/8d966b0e6cf84fe191a72a58b8293b23.png', 'ROLE_ADMIN');
INSERT INTO `sys_user` VALUES (16, 'xiaoqiao', 'xiaoqiao', '小乔', 'xiaoqiao@qq.com', '18009800098', '河北', '2022-02-26 22:10:14', NULL, 'ROLE_TEACHER');
INSERT INTO `sys_user` VALUES (18, 'hanxin', 'hanxin', '韩信', 'hanxin@qq.com', '17809827734', '山东', '2022-03-29 16:59:44', '', 'ROLE_STUDENT');
INSERT INTO `sys_user` VALUES (19, 'yase', 'yase', '亚瑟', 'yase@qq.com', '19708374665', '山西', '2022-04-29 16:59:44', '', 'ROLE_STUDENT');
INSERT INTO `sys_user` VALUES (20, 'lixin', 'lixin', '李信', 'lixin@qq.com', '18273645535', '福建', '2022-05-29 17:12:04', '2', 'ROLE_STUDENT');
INSERT INTO `sys_user` VALUES (25, 'anqila', 'anqila', '安琪拉', 'anqila@qq.com', '18277364554', '山东太原', '2022-06-08 17:00:47', NULL, 'ROLE_STUDENT');
INSERT INTO `sys_user` VALUES (26, 'daji', 'daji', '妲己', 'daji@qq.com', '18763466653', '山东日照', '2022-07-08 17:20:01', NULL, 'ROLE_STUDENT');
INSERT INTO `sys_user` VALUES (28, 'yvji', 'yvji', '虞姬', 'yvji@qq.com', '19876544474', '武汉', '2022-11-09 10:41:07', 'http://localhost:9090/file/7de0e50f915547539db12023cf997276.jpg', 'ROLE_STUDENT');
INSERT INTO `sys_user` VALUES (29, 'sunbin', 'sunbin', '孙膑', 'sunbin@qq.com', '19800987655', '山东', '2022-12-10 11:53:31', NULL, 'ROLE_STUDENT');
INSERT INTO `sys_user` VALUES (30, 'nezha', 'nezha', '哪吒', 'nezha@qq.com', '18099876778', '河北', '2022-04-19 20:53:57', NULL, NULL);
INSERT INTO `sys_user` VALUES (31, 'zhenji', 'zhenji', '甄姬', 'zhenji@qq.com', '19888765534', '山东', '2022-04-19 20:54:16', NULL, NULL);
INSERT INTO `sys_user` VALUES (32, 'tangseng', 'tangseng', '唐僧', 'tangseng@qq.com', '12999897767', '东土大唐', '2022-04-20 15:53:46', NULL, NULL);
INSERT INTO `sys_user` VALUES (33, 'zhubajie', 'zhubajie', '猪八戒', 'zhubajie@qq.com', '13387876655', '高老庄', '2022-04-20 15:56:02', NULL, NULL);
INSERT INTO `sys_user` VALUES (34, 'shaseng', 'shaseng', '沙僧', 'shaseng@qq.com', '18744332273', '流沙河', '2022-04-20 16:00:26', NULL, NULL);
INSERT INTO `sys_user` VALUES (35, 'bailongma', 'bailongma', '白龙马', 'bailongma@qq.com', '17787675844', '东海', '2022-04-20 16:01:55', NULL, NULL);
SET FOREIGN_KEY_CHECKS = 1;
前端vue
创建vue项目后运行
安装vue：npm install vue
安装axios： npm install axios
安装ElementUI：npm i element-ui -S
main.js
import Vue from 'vue'
import App from './App.vue'
import router from './router'
import store from './store'
import ElementUI from 'element-ui';
import 'element-ui/lib/theme-chalk/index.css';
import './assets/gloable.css';
import request from "@/utils/request";
Vue.use(ElementUI)
Vue.config.productionTip = false
// 从utils中引入request对象，然后在页面上就可以使用了
Vue.prototype.request = request
new Vue({
router,
store,
render: h => h(App)
}).$mount('#app')
在views中创建Manager.vue
<template>
<div class="home">
<el-container style="height: 100%;">
<!--左侧边栏-->
<el-aside :width="sideWidth + 'px'" style="background-color: rgb(238, 241, 246);height: 100%; box-shadow:2px 0 6px rgb(0, 21, 41, 0.35)">
<Aside :isCollapse="isCollapse" :logoTextShow="logoTextShow"/>
</el-aside>
<!--右边-->
<el-container>
<!--头部-->
<el-header style="border-bottom: 1px solid #ccc;">
<Header :collapse="isCollapse" :collapseBtnClass="collapseBtnClass"/>
</el-header>
<!--主体-->
<el-main>
<!-- 表示当前页面的子路由会在 router-view 里面展示 -->
<router-view/>
</el-main>
</el-container>
</el-container>
</div>
</template>
<script>
// @ is an alias to /src
import HelloWorld from '@/components/HelloWorld.vue'
import Header from "@/components/Header"
import Aside from "@/components/Aside";
// import request from "@/utils/request";
export default {
name: 'Manage',
components: {Aside,Header},
data() {
return {
collapseBtnClass: 'el-icon-s-fold', // 收缩图标控制 默认展开
isCollapse: false, // 点击收缩图标控制 默认是展开 false
sideWidth: 200, // 导航栏宽度 动态 初始值是200
logoTextShow: true, // 默认是显示的标题 当收缩是为false
}
},
methods: {
collapse() { // 点击收缩按钮时会触发按钮
this.isCollapse = !this.isCollapse
if (this.isCollapse) { // 收缩
this.sideWidth = 65
this.collapseBtnClass = 'el-icon-s-unfold' // 缩起图标控制
this.logoTextShow = false // 收缩是不显示文字
} else { // 展开
this.sideWidth = 200
this.collapseBtnClass = 'el-icon-s-fold' // 展开图标控制
this.logoTextShow = true // 显示文字
}
}
}
}
</script>
<style>
.home {
height: 100%;
}
</style>
在views中创建User.vue
<template>
<div>
<!-- <div style="margin-bottom: 20px">
<el-breadcrumb separator="/">
<el-breadcrumb-item :to="{ path: '/' }">首页</el-breadcrumb-item>
</el-breadcrumb>
<el-breadcrumb-item>用户管理</el-breadcrumb-item>
</div>-->
<div class="mg-10">
<el-input v-model="username" style="width: 200px" suffix-icon="el-icon-search" placeholder="请输入名称"></el-input>
<el-input v-model="email" style="width: 200px" suffix-icon="el-icon-message" placeholder="请输入邮箱" class="ml-5"></el-input>
<el-input v-model="address" style="width: 200px" suffix-icon="el-icon-position" placeholder="请输入地址" class="ml-5"></el-input>
<el-button class="ml-5" type="primary" @click="findPage()">搜索</el-button>
<el-button class="ml-5" type="warning" @click="reset()">重置</el-button>
</div>
<div class="mg-10">
<el-button type="primary" @click="handleAdd()">新增 <i class="el-icon-circle-plus-outline"></i></el-button>
<el-popconfirm
confirm-button-text='确定'
cancel-button-text='不删了'
icon="el-icon-info"
icon-color="red"
title="您确定删除选中信息吗？"
@confirm="delBatch()"
class="ml-5">
<el-button type="danger" slot="reference">批量删除 <i class="el-icon-remove-outline"></i></el-button>
</el-popconfirm>
<el-upload action="http://localhost:8081/user/import" :show-file-list="false" accept="xlsx" :on-success="handleExcelImportSuccess" style="display: inline-block">
<el-button type="primary" class="ml-5">导入 <i class="el-icon-bottom"></i></el-button>
</el-upload>
<el-button type="primary" @click="exp()" class="ml-5">导出 <i class="el-icon-top"></i></el-button>
</div>
<el-table :data="tableData" border stripe :header-cell-class-name="headerBg" @selection-change="handleSelectionChange">
<el-table-column type="selection" width="55"></el-table-column>
<el-table-column prop="id" label="ID" width="60" align="center"></el-table-column>
<el-table-column prop="username" label="用户名" width="120" align="center"></el-table-column>
<el-table-column prop="nickname" label="昵称" width="120" align="center"></el-table-column>
<el-table-column prop="email" label="邮箱" width="200" align="center"></el-table-column>
<el-table-column prop="phone" label="电话" width="120" align="center"></el-table-column>
<el-table-column prop="address" label="地址" width="250" align="center"></el-table-column>
<el-table-column label="操作" width="205">
<template slot-scope="scope">
<el-button type="success" @click="handleEdit(scope.row)">编辑 <i class="el-icon-edit"></i></el-button>
<el-popconfirm
confirm-button-text='确定'
cancel-button-text='不删了'
icon="el-icon-info"
icon-color="red"
title="您确定删除吗？"
@confirm="handleDel(scope.row.id)"
class="ml-5">
<el-button type="danger" slot="reference">删除 <i class="el-icon-remove-outline"></i></el-button>
</el-popconfirm>
</template>
</el-table-column>
</el-table>
<!-- 分页
分页数据和表格数据产生了联动
前端传的值：
:current-page="pageNum" 页码
:page-size="pageSize" 每页个数
后端传的值：
:total="total" 总条数
-->
<div style="padding: 10px 0">
<el-pagination
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page="pageNum"
:page-sizes="[ 2, 5, 10, 15]"
:page-size="pageSize"
layout="total, sizes, prev, pager, next, jumper"
:total="total">
</el-pagination>
</div>
<el-dialog title="用户信息" :visible.sync="dialogFormVisible" width="40%">
<el-form>
<el-form-item label="用户名" :label-width="formLabelWidth">
<el-input v-model="userForm.username" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="用户昵称" :label-width="formLabelWidth">
<el-input v-model="userForm.nickname" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="电话" :label-width="formLabelWidth">
<el-input v-model="userForm.phone" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="邮箱" :label-width="formLabelWidth">
<el-input v-model="userForm.email" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="地址" :label-width="formLabelWidth">
<el-input v-model="userForm.address" autocomplete="off"></el-input>
</el-form-item>
</el-form>
<div slot="footer" class="dialog-footer">
<el-button @click="dialogFormVisible = false">取 消</el-button>
<el-button type="primary" @click="save()">确 定</el-button>
</div>
</el-dialog>
</div>
</template>
<script>
export default {
name: "User",
data() {
return {
tableData: [],
total: 0, // 分页总条数
pageNum: 1, // 页数
pageSize: 5, // 条数
username: "", // 查询绑定username
email: "", // 查询绑定email
address: "", // 查询绑定address
nickname: "", // 绑定用户昵称
userForm: {}, // 绑定一个对象，form表单中有用到
dialogFormVisible: false, // 用户信息弹出框控制
formLabelWidth: '120px', // 绑定input框宽
headerBg: 'headerBg',
multipleSelection: [] // 绑定多选按钮
}
},
created() {
this.findPage()
},
methods: {
handleSizeChange(pageSize) {
console.log(`每页 ${pageSize} 条`);
// 传给我们上面绑定的pageSize
this.pageSize = pageSize
// 请求数据，请求数据之后它会自动更新 pageSize
this.findPage()
},
handleCurrentChange(pageNum) {
console.log(`当前页: ${pageNum}`);
// 传给我们上面绑定的pageNum
this.pageNum = pageNum
// 请求数据，请求数据之后它会自动更新 tableData
this.findPage()
},
findPage() { // 搜索按钮
this.request.get("http://localhost:8081/user/page", {
params: {
pageNum: this.pageNum,
pageSize: this.pageSize,
username: this.username,
email: this.email,
address: this.address
}
}).then(res => {
console.log(res)
this.tableData = res.records
this.total = res.total
})
// 请求分页查询数据 fetch api 有以下几个参数，第一个参数是url
// .then 返回一个res，之后对他进行一个json的处理，因为他返回的res是一个字符串，需要把字符串转成json
// 通过res.json()函数 这个方法，把他转成json,再.then 这个res就是json类型的了
// 拼接url路径使用es6的语法：`http://localhost:8081/user/findPage/${pageNum}`
/*
// 有了request后这些内容就被替换掉了,我们替换的一个是API的替换，还有一个就是复制方式的一个替换
fetch("http://localhost:8081/user/findPage?pageNum="+this.pageNum+"&pageSize="+this.pageSize+"&username="+this.username+"&email="+this.email+"&address="+this.address).then(res => res.json()).then(res => {
console.log(res)
this.tableData = res.data
this.total = res.total
// 查询完成后清空输入框
this.username = ""
this.email = ""
this.address = ""
})*/
},
reset() { // 重置按钮
// 清空输入框
this.username = ""
this.email = ""
this.address = ""
this.findPage()
},
handleAdd() { // 添加按钮
// 打开弹窗
this.dialogFormVisible = true
// 将from表单内容置空
this.userForm = {}
},
save() { // 添加/修改弹窗点击确定按钮
this.request.post("http://localhost:8081/user", this.userForm).then(res => {
if (res) {
this.$message.success("操作成功")
this.dialogFormVisible = false
this.findPage()
} else {
this.$message.error("操作失败")
}
})
},
handleEdit(row){
// this.userForm = JSON.parse(JSON.stringify(row)) // 如果出现编辑未确认就改变的问题，就使用它
// 也可以使用 将row拷贝到空对象中
// this.userForm = Object.assign({},row)
this.userForm = {...row}
// this.userForm = row
this.dialogFormVisible = true
},
handleDel(id) { // 点击删除按钮执行
this.request.delete("http://localhost:8081/user/" + id).then(res => {
if (res) {
this.$message.success("删除成功")
this.findPage()
} else {
this.$message.error("删除失败")
}
})
},
// 批量删除
handleSelectionChange(val) {
console.log(val)
this.multipleSelection = val; // 将选中的对象数组赋进去
},
delBatch() {
// 因为我们需要的是数组，所以我们通过map来把一个对象的数组变成了一个传id的一个数组
let ids = this.multipleSelection.map(v => v.id) // [{},{},{}] => [1,2,3]
// 因为delete不能传对象过去，所以改成post
this.request.post("http://localhost:8081/user/del/batch", ids).then(res => {
if (res) {
this.$message.success("批量删除成功")
this.findPage()
} else {
this.$message.error("批量删除失败")
}
})
},
// 导出
exp() {
window.open("http://localhost:8081/user/export")
},
// 导入
handleExcelImportSuccess() {
this.$message.success("导入成功")
this.findPage()
},
}
}
</script>
<style scoped>
.el-menu-item,.el-menu-item-group, .el-submenu__title{
background-color: rgb(48, 65, 86);
}
.headerBg {
background: #eee!important;
}
</style>在components文件夹中创建Aside.vue
<template>
<el-menu :default-openeds="['1', '3']" style="height: 100%;background-color: rgb(48, 65, 86);overflow-x: hidden"
default-active="2"
background-color="#304156FF"
text-color="#fff"
active-text-color="#ffd04b"
:collapse-transition="false"
:collapse="isCollapse"
class="el-menu-vertical-demo"
router
@select="handleSelect"
>
<div style="height: 60px;line-height: 60px;text-align: center">
<img src="../assets/logo.png" alt="" style="width: 20px;position: relative;top: 5px;margin-right: 5px"/>
<b style="color: #fff" v-show="logoTextShow">后台管理系统</b>
</div>
<el-menu-item>
<template slot="title">
<el-menu-item index="/"><i class="el-icon-s-home"></i> 主页</el-menu-item>
</template>
</el-menu-item>
<el-submenu index="2">
<template slot="title">
<i class="el-icon-menu"></i>
<span slot="title">系统管理</span>
</template>
<el-menu-item-group>
<el-menu-item index="/user"><i class="el-icon-s-custom"></i> 用户管理</el-menu-item>
</el-menu-item-group>
</el-submenu>
</el-menu>
</template>
<script>
export default {
name: "Aside",
props: {
isCollapse: Boolean,
logoTextShow: Boolean
}
}
</script>
<style scoped>
</style>在components文件夹中创建Header.vue
<template>
<div style=" line-height: 60px;display: flex">
<div style="flex: 1;font-size: 20px">
<span :class="collapseBtnClass" style="cursor: pointer" @click="collapse"></span>
<el-breadcrumb separator="/" style="display: inline-block; margin-left: 10px" >
<el-breadcrumb-item :to="'/'">首页</el-breadcrumb-item>
<el-breadcrumb-item>{{ currentPathName }}</el-breadcrumb-item>
</el-breadcrumb>
</div>
<el-dropdown style="width: 70px;cursor: pointer">
<span>王小虎<i class="el-icon-arrow-down" style="margin-left: 5px"></i></span>
<el-dropdown-menu slot="dropdown">
<el-dropdown-item>个人信息</el-dropdown-item>
<el-dropdown-item>退出</el-dropdown-item>
</el-dropdown-menu>
</el-dropdown>
</div>
</template>
<script>
export default {
name: "Header",
props: {
collapseBtnClass: String
},
computed: {
currentPathName(){
// 在这里取出我们在route里面设置的当前路由信息currentPathName
return this.$store.state.currentPathName // 需要监听的数据
}
},
data() {
return {
}
},
}
</script>
<style scoped>
</style>在assets文件夹中创建gloable.css
html,body {
height: 100%;
}
html,body,div {
margin: 0px;
padding: 0px;
}
/*
使用方法
class="ml-5"
*/
.ml-5 {
margin-left: 5px;
}
.mr-5 {
margin-right: 5px;
}
.mg-10 {
margin: 10px 0;
}
.pd-10 {
padding: 10px 0;
}
在router文件夹中操作index.js文件
import Vue from 'vue'
import VueRouter from 'vue-router'
import store from "@/store";
Vue.use(VueRouter)
const routes = [
// {
// path: '/',
// name: 'home',
// component: HomeView
// },
{
path: '/',
name: 'Manage',
component: () => import('../views/Manage.vue'),
redirect: "/home",
children: [
{ path: 'home', name: '首页', component: () => import('../views/Home.vue') },
{ path: 'user', name: '用户管理', component: () => import('../views/User.vue') }
]
},
{
path: '/about',
name: 'about',
// route level code-splitting
// this generates a separate chunk (about.[hash].js) for this route
// which is lazy-loaded when the route is visited.
component: () => import(/* webpackChunkName: "about" */ '../views/AboutView.vue')
}
]
const router = new VueRouter({
// mode: "history",
// base: process.env.BASE_URL,
routes
})
router.beforeEach((to, from, next) => { // 路由守卫
localStorage.setItem("currentPathName", to.name) // 设置当前路由名称
store.commit("setPath") // 触发store的数据更新
next()
})
export default router
在src中创建store文件夹然后在文件夹中创建index.js
import Vue from 'vue'
import Vuex from 'vuex'
Vue.use(Vuex)
const store = new Vuex.Store({
state: {
currentPathName: ''
},
mutations: {
setPath(state) {
state.currentPathName = localStorage.getItem("currentPathName")
}
}
})
export default store
在src中创建utils文件夹然后在文件夹中创建request.js
import axios from 'axios'
// 首先从axios导入axios对象
// 然后通过axios去create(添加)一个request对象
const request = axios.create({
// 注意！！ 这里是全局统一加上了 '/api' 前缀，也就是说所有接口都会加上'/api'前缀在，页面里面写接口的时候就不要加 '/api'了，
// 否则会出现2个'/api'，类似 '/api/api/user'这样的报错，切记！！！
baseURL: '/api',
timeout: 5000
})
// 然后request 给它设置了一个 拦截器
// 对你发送的对象进行一个 拦截
// 可以自请求发送前对请求做一些处理
// 比如统一加token，对请求参数统一加密
request.interceptors.request.use(config => {
config.headers['Content-Type'] = 'application/json;charset=utf-8';
// 然后可以在里面设置一些请求头
// config.headers['token'] = user.token; // 设置请求头
return config
}, error => {
return Promise.reject(error)
});
// response 拦截器
// 可以在接口响应后统一处理结果
// 这里就是设置了从后台请求过来的数据之后，对它的一个处理
request.interceptors.response.use(
response => {
// 首先获取到response里面的data，这个data是axios自己封装的，所以data是统一的，这是固定写法
let res = response.data;
// 如果是返回的文件，直接return
if (response.config.responseType === 'blob') {
return res
}
// 兼容服务端返回的字符串数据，如果是String的话，它就会给他一个parse，转换成json对象，然后把它return出来，因为String一般会返回一个json字符串
if (typeof res === 'string') {
// parse作用是将json字符串转换成json对象
res = res ? JSON.parse(res) : res
}
return res;
},
error => { // 如果出现error的话就打印error日志
console.log('err' + error) // for debug
return Promise.reject(error)
}
)
// 最后request出来
export default request
到这里我们的前端vue代码就完成了，其中路由有点问题，还没有解决，但是不影响功能的使用
我们写Excel用到的hutool工具网页
https://www.hutool.cn/docs/#/poi/Excel%E5%B7%A5%E5%85%B7-ExcelUtil
后端springboot项目中
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.6</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.boot</groupId>
<artifactId>springboot</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>Idea-boot</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<!-- 代码生成器 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.5.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.velocity/velocity -->
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity</artifactId>
<version>1.7</version>
</dependency>
<!-- mybatis-plus代码生成器 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity</artifactId>
<version>1.7</version>
</dependency>
<!-- hutool -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.20</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!--swagger-->
<!-- <dependency>-->
<!-- <groupId>io.springfox</groupId>-->
<!-- <artifactId>springfox-swagger2</artifactId>-->
<!-- <version>2.7.0</version>-->
<!-- </dependency>-->
<!-- <dependency>-->
<!-- <groupId>io.springfox</groupId>-->
<!-- <artifactId>springfox-swagger-ui</artifactId>-->
<!-- <version>2.7.0</version>-->
<!-- </dependency>-->
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
<!-- 阿里云仓库配置 -->
<repositories>
<repository>
<id>aliyun</id>
<url>https://maven.aliyun.com/repository/public</url>
<releases>
<enabled>true</enabled>
</releases>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
</repositories>
<pluginRepositories>
<pluginRepository>
<id>aliyun-plugin</id>
<url>https://maven.aliyun.com/repository/public</url>
<releases>
<enabled>true</enabled>
</releases>
<snapshots>
<enabled>false</enabled>
</snapshots>
</pluginRepository>
</pluginRepositories>
</project>
在resources文件夹中创建application.yml
# 修改端口地址
server:
port: 8081
spring:
datasource:
# 数据库的驱动名称
driver-class-name: com.mysql.cj.jdbc.Driver
# 连接数据库的url serverTimezone=GMT%2b8 %2b是+ 设置时间为中巴区
url: jdbc:mysql://localhost:3306/the_sound?serverTimezone=GMT%2b8
# 配置数据库用户名
username: root
# 配置数据库密码
password: root
mybatis:
# mybatis 的 mapper.xlm配置 扫描所有mybatis的xml文件
mapper-locations: classpath:mapper/*.xml
# 最终 sql 拼接 打印log日志
# configuration:
# log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
在config文件夹中创建CorsConfig.java解决跨域问题
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.cors.CorsConfiguration;
import org.springframework.web.cors.UrlBasedCorsConfigurationSource;
import org.springframework.web.filter.CorsFilter;
/**
* @author ls
* @version 1.0
* @description: 解决跨域问题的工具类
* @date 2022/4/19 10:46
*/
@Configuration
public class CorsConfig {
// 当前跨域请求最大有效时长。这里默认1天
private static final long MAX_AGE = 24 * 60 * 60;
@Bean
public CorsFilter corsFilter() {
UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
CorsConfiguration corsConfiguration = new CorsConfiguration();
// 允许任何的源地址、源请求头、源请求方法访问接口
corsConfiguration.addAllowedOrigin("http://localhost:8080"); // 1 设置访问源地址
corsConfiguration.addAllowedHeader("*"); // 2 设置访问源请求头
corsConfiguration.addAllowedMethod("*"); // 3 设置访问源请求方法
corsConfiguration.setMaxAge(MAX_AGE);
source.registerCorsConfiguration("/**", corsConfiguration); // 4 对接口配置跨域设置
return new CorsFilter(source);
}
}配置mybatis-plus
这个是它的文件位置（SysUser开头的不用管）
在utils文件夹中的CodeGenerator.java文件配置mybatis-plus生成器代码
import com.baomidou.mybatisplus.generator.FastAutoGenerator;
import com.baomidou.mybatisplus.generator.config.OutputFile;
import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine;
import java.util.Collections;
/**
* @author ls
* @version 1.0
* @description: mybatis-plus代码生成器
* @date 2022/4/21 16:42
*/
public class CodeGenerator {
public static void main(String[] args) {
generate();
}
private static void generate() {
FastAutoGenerator.create("jdbc:mysql://localhost:3306/the_sound?serverTimezone=GMT%2b8", "root", "root")
.globalConfig(builder -> {
builder.author("theSound") // 设置作者
// .enableSwagger() // 开启 swagger 模式
.fileOverride() // 覆盖已生成文件
.outputDir("E:\\ideaProject\\Idea-boot\\src\\main\\java\\"); // 指定输出目录
})
.packageConfig(builder -> {
builder.parent("com.boot.springboot") // 设置父包名
.moduleName("") // 设置父包模块名
.pathInfo(Collections.singletonMap(OutputFile.mapperXml, "E:\\ideaProject\\Idea-boot\\src\\main\\resources\\mapper\\")); // 设置mapperXml生成路径
})
.strategyConfig(builder -> {
builder.entityBuilder().enableLombok(); // 生成的entity使用lombok
builder.mapperBuilder().enableMapperAnnotation().build(); // 添加mapper注解，相当于在每一个Mapper上面加一个@Mapper
builder.controllerBuilder().enableHyphenStyle() // 开启驼峰转连字符
.enableRestStyle(); // 开启生成@RestController 控制器
builder.addInclude("sys_user") // 设置需要生成的表名
.addTablePrefix("t_", "sys_"); // 设置过滤表前缀
})
// .templateEngine(new FreemarkerTemplateEngine()) // 使用Freemarker引擎模板，默认的是Velocity引擎模板
.execute();
}
}
在config文件夹中新建MybatisPlusConfig.java文件
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* @author ls
* @version 1.0
* @description: TODO
* @date 2022/4/19 19:45
*/
//@Configuration 这个注解是springboot提供配置的一个注解
//@MapperScan("com.qingge.springboot.mapper") 这个注释是注入mapper的一个注解
@Configuration
@MapperScan("com.boot.springboot.mapper")
public class MybatisPlusConfig {
// 最新版
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// DbType是数据库类型，我们使用的是MySQL
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
根据步骤找到controller.java.vm这个文件，然后复制到resources中创建的templates文件夹中
package ${package.Controller};
##// 导入我们需要的包
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.springframework.web.bind.annotation.*;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import javax.annotation.Resource;
import java.util.List;
##// 这是一种模板引擎的语法 导入我们的包
import ${package.Service}.${table.serviceName};
import ${package.Entity}.${entity};
#if(${restControllerStyle})
import org.springframework.web.bind.annotation.RestController;
#else
import org.springframework.stereotype.Controller;
#end
#if(${superControllerClassPackage})
import ${superControllerClassPackage};
#end
/**
* <p>
* $!{table.comment} 前端控制器
* </p>
*
* @author ${author}
* @since ${date}
*/
#if(${restControllerStyle})
@RestController
#else
@Controller
#end
@RequestMapping("#if(${package.ModuleName})/${package.ModuleName}#end/#if(${controllerMappingHyphenStyle})${controllerMappingHyphen}#else${table.entityPath}#end")
#if(${kotlin})
class ${table.controllerName}#if(${superControllerClass}) : ${superControllerClass}()#end
#else
#if(${superControllerClass})
public class ${table.controllerName} extends ${superControllerClass} {
#else
public class ${table.controllerName} {
## 在这里导入service包
#end
## // 这里的table.serviceName就是我们的userService
@Resource
private ${table.serviceName} ${table.entityPath}Service;
## // 在前端传入json数据时，@RequestBody可以把json映射成user对象
## // 这里的SysUser是动态的 ${entity} sysUser也是动态的 ${table.entityPath}
@PostMapping
public boolean saveOrUpdateSysUser(@RequestBody ${entity} ${table.entityPath}) {
## // 新增或者更新
return ${table.entityPath}Service.saveOrUpdate(${table.entityPath});
}
## // 删除数据
## // @PathVariable 是表示url路径的参数 {}里的id和参数列表里的id是一一对应的
@DeleteMapping("/{id}")
public boolean delete(@PathVariable Integer id) {
return ${table.entityPath}Service.removeById(id);
}
## // 批量删除数据
@PostMapping("/del/batch")
public boolean deleteBatch(@RequestBody List<Integer> ids) {
return ${table.entityPath}Service.removeByIds(ids);
}
## // 查询所有数据
@GetMapping
public List<${entity}> findAll(){
return ${table.entityPath}Service.list();
}
## // 根据id查询单查
@GetMapping("/{id}")
public ${entity} findOne(@PathVariable Integer id) {
return ${table.entityPath}Service.getById(id);
}
## // 分页查询
@GetMapping("/page")
public Page<${entity}> findPage(@RequestParam Integer pageNum, @RequestParam Integer pageSize) {
QueryWrapper<${entity}> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("id");
return ${table.entityPath}Service.page(new Page<>(pageNum, pageSize), queryWrapper);
}
}
#end生成后按需求添加条件及逻辑就可以了！！！
使用导入导出时在生成的User.java中加入一个注解
package com.boot.springboot.entity;
import cn.hutool.core.annotation.Alias;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.time.LocalDateTime;
import lombok.Getter;
import lombok.Setter;
/**
* <p>
*
* </p>
*
* @author theSound
* @since 2022-04-22
*/
@Getter
@Setter
@TableName("sys_user")
public class User implements Serializable {
private static final long serialVersionUID = 1L;
/**
* id
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 用户名
* 导入 hutool 下的 Alias 注解，它的作用是直接给表赋别名，导入导出时就不用再担心有不对应的问题了
*/
@Alias("用户名")
private String username;
/**
* 密码
*/
@Alias("密码")
private String password;
/**
* 昵称
*/
@Alias("昵称")
private String nickname;
/**
* 邮箱
*/
@Alias("邮箱")
private String email;
/**
* 电话
*/
@Alias("电话")
private String phone;
/**
* 地址
*/
@Alias("地址")
private String address;
/**
* 创建时间
*/
@Alias("创建时间")
private LocalDateTime createTime;
/**
* 头像
*/
@Alias("头像")
private String avatarUrl;
/**
* 角色
*/
@Alias("角色")
private String role;
}在生成的UserController.java中
package com.boot.springboot.controller;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.springframework.web.bind.annotation.*;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import com.boot.springboot.service.IUserService;
import com.boot.springboot.entity.User;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
/**
* <p>
* 前端控制器
* </p>
*
* @author theSound
* @since 2022-04-22
*/
@RestController
@RequestMapping("/user")
public class UserController {
/**
* @description: 导入
* @param: file
* @return: java.lang.Boolean
* @author ls
* @date: 2022/4/25
*/
@PostMapping("/import")
public Boolean imp(MultipartFile file) throws Exception { // MultipartFile 对象，文件上传后会转换成这样一个file对象
InputStream inputStream = file.getInputStream(); // 然后我们从这个file文件对象中就能获取到inputStream
ExcelReader reader = ExcelUtil.getReader(inputStream); // 在new这个ExcelReader中将inputStream存放进去
// 方式1：(推荐) 通过 javabean的方式读取Excel内的对象，但是要求表头必须是英文，跟javabean的属性要对应起来
List<User> list = reader.readAll(User.class);
// 如果不在 com.boot.springboot.entity.User 中加入@Alias("别名")注解
// 就使用方式2：忽略表头的中文，直接读取表的内容
// List<List<Object>> list = reader.read(1);
// List<User> users = CollUtil.newArrayList();
// for (List<Object> row : list) {
// User user = new User();
// user.setUsername(row.get(0).toString());
// user.setPassword(row.get(1).toString());
// user.setNickname(row.get(2).toString());
// user.setEmail(row.get(3).toString());
// user.setPhone(row.get(4).toString());
// user.setAddress(row.get(5).toString());
// user.setAvatarUrl(row.get(6).toString());
// users.add(user);
// }
userService.saveBatch(list);
return true;
}
/**
* @description: 导出接口
* @param: response
* @return: void
* @author ls
* @date: 2022/4/25
*/
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception {
// 首先从数据库查询出所有的数据
List<User> list = userService.list();
// 通过工具类创建writer 写出到磁盘路径
// ExcelWriter writer = ExcelUtil.getWriter(filesUploadPath + "/用户信息.xlsx");
// 第一步定义一个writer对象，然后往writer中写入数据
// 在内存操作，写出到浏览器
ExcelWriter writer = ExcelUtil.getWriter(true);
// 第二步 自定义标题别名 如果不在 com.boot.springboot.entity.User 中加入@Alias("别名")注解
// 这一步就让我们实体类中的字段和他的意思对应起来
// writer.addHeaderAlias("username", "用户名");
// writer.addHeaderAlias("password", "密码");
// writer.addHeaderAlias("nickname", "昵称");
// writer.addHeaderAlias("email", "邮箱");
// writer.addHeaderAlias("phone", "电话");
// writer.addHeaderAlias("address", "地址");
// writer.addHeaderAlias("createTime", "创建时间");
// writer.addHeaderAlias("avatarUrl", "头像");
// writer.addHeaderAlias("role", "角色");
// 第三步 将我们上面查出的list对象，通过write()方法，写入writer面去
// 一次性写出list内的对象到excel，使用默认样式，强制输出标题
writer.write(list, true);
// 设置浏览器响应的格式(格式基本固定)
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
String fileName = URLEncoder.encode("用户信息", "UTF-8"); // 将这个文件的名称，用encode这个方法进行编码
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx"); // 然后设置到response的Header中
// 通过response对象，获取输出流
ServletOutputStream out = response.getOutputStream();
// 然后将这个writer对象里的东西刷新到这个输出流中，然后是通过输出流返回到浏览器
writer.flush(out, true);
// 最后将流关闭
out.close();
writer.close();
}
@Resource
private IUserService userService;
@PostMapping
public boolean saveOrUpdateSysUser(@RequestBody User user) {
return userService.saveOrUpdate(user);
}
@DeleteMapping("/{id}")
public boolean delete(@PathVariable Integer id) {
return userService.removeById(id);
}
@PostMapping("/del/batch")
public boolean deleteBatch(@RequestBody List<Integer> ids) {
return userService.removeByIds(ids);
}
@GetMapping
public List<User> findAll(){
return userService.list();
}
@GetMapping("/{id}")
public User findOne(@PathVariable Integer id) {
return userService.getById(id);
}
/**
* @description: 分页+模糊查
* @param: pageNum,pageSize,username,email,address
* @return: com.baomidou.mybatisplus.extension.plugins.pagination.Page<com.boot.springboot.entity.User>
* @author ls
* @date: 2022/4/26
*/
@GetMapping("/page")
public Page<User> findPage(@RequestParam Integer pageNum, @RequestParam Integer pageSize,
@RequestParam(defaultValue = "") String username, @RequestParam(defaultValue = "") String email, @RequestParam(defaultValue = "") String address) {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
if (!"".equals(username)){
queryWrapper.like("username",username);
}
if (!"".equals(email)){
queryWrapper.like("email",email);
}
if (!"".equals(address)){
queryWrapper.like("address",address);
}
queryWrapper.orderByDesc("id");
return userService.page(new Page<>(pageNum, pageSize), queryWrapper);
}
}
浙公网安备 33010602011771号