1.修改完善代码
1.将mapper接口中的注解改为xml映射语句
修改后的ComponentMapper.xml文件如下:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.liuxiaocan.finalwork.Mapper.ComponentMapper">
<select id="selectall" resultType="com.liuxiaocan.finalwork.Pojo.Component">
select * from component
</select>
<select id="SelectPart" resultType="com.liuxiaocan.finalwork.Pojo.Component">
select * from component where CNo=#{cno}
</select>
<update id="Update" >
update Component set CName=#{cname},CSize=#{csize},CPrice=#{cprice},CText=#{ctext} where CNo=#{cno}
</update>
<insert id="Insert">
insert into Component values (#{cno},#{cname},#{csize},#{cprice},#{ctext})
</insert>
<delete id="Delete">
delete from Component where CNo=#{cno}
</delete>
</mapper>
2.动态sql语句
1.if标签
利用动态sql语句实现依据关键字的查询功能。首先编写xml文件,在xml文件中编写对应模糊查询的sql语句,在mapper接口中编写对应方法实现模糊查询。service层需要增加查询和分页的接口以及对应实现类,在service层实现了数据的分页,任何把模糊查询的结果封装到 PageInfo中,再生成pagebean对象。在controller层中cno、cname、csize参数是可选的,pagenumber和pagesize参数采用路径参数的传递方法进行参数传递,这是不可选的,必须传递否则报错。
ComponentMapper.xml:
<select id="Chaxun" resultType="com.liuxiaocan.finalwork.Pojo.Component">
select * from component
<where>
<if test="cno!=null">
CNo LIKE CONCAT('%', #{cno}, '%')
</if>
<if test="cname!=null">
and CName LIKE CONCAT('%', #{cname}, '%')
</if>
<if test="csize!=null">
and CSize LIKE CONCAT('%', #{csize}, '%')
</if>
</where>
</select>
ComponentMapper.interface:
public List<Component> Chaxun(String cno,String cname,String csize);
ComponentService.interface:
public PageBean Chaxun( Integer pageNumber,Integer pageSize,String cno,String cname,String csize);
ComponentServiceA.class:
@Override
public PageBean Chaxun(Integer pageNumber, Integer pageSize, String cno, String cname, String csize) {
PageHelper.startPage(pageNumber,pageSize);
List<Component> c=componentMapper.Chaxun(cno,cname,csize);
PageInfo<Component> p = new PageInfo<>(c);
PageBean page= new PageBean(p.getTotal(),p.getPages(),p.getPageNum(),p.getPageSize(),p.getList());
return page;
}
ComponentController.class:
@RequestMapping("/search/{PageSize}/{PageNumber}")
public Result Search(@PathVariable Integer PageSize,
@PathVariable Integer PageNumber,
@RequestParam(required = false) String cno,
@RequestParam(required = false) String cname,
@RequestParam(required = false) String csize)
{
PageBean page=componentService.Chaxun(PageNumber,PageSize,cno,cname,csize);
return Result.success(page);
}
show.html:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="./js/vue.js"></script>
<script src="./js/axios-0.18.0.js"></script>
<link rel="stylesheet" href="element-ui/index.css">
<script src="./element-ui/index.js"></script>
</head>
<body>
<h1 style="text-align: center">零件信息表</h1>
<div id="app">
<div style="text-align:center">
<el-button type="primary" plain v-on:click="JumpOff">添加零件</el-button>
<p align="center">
<el-form :inline="true" :model="form" class="demo-form-inline">
<el-form-item label="零件号">
<el-input v-model="form.cno" placeholder="零件号"></el-input>
</el-form-item>
<el-form-item label="零件名">
<el-input v-model="form.cname" placeholder="零件名"></el-input>
</el-form-item>
<el-form-item label="尺寸">
<el-input v-model="form.csize" placeholder="尺寸"></el-input>
</el-form-item>
<el-form-item>
<el-button type="primary" @click="SSubmit">查询</el-button>
</el-form-item>
</el-form>
</p>
</div>
<el-table
:data="ComponentData"
style="width: 80%;margin: auto " >
<el-table-column
align="center"
label="零件号"
prop="cno">
</el-table-column>
<el-table-column
align="center"
label="零件名"
prop="cname">
</el-table-column>
<el-table-column
align="center"
label="尺寸"
prop="csize">
</el-table-column>
<el-table-column
align="center"
label="单价"
prop="cprice">
</el-table-column>
<el-table-column
align="center"
label="描述"
prop="ctext">
</el-table-column>
<el-table-column
align="center"
label="操作">
<!-- <template slot="header" slot-scope="scope">-->
<!-- <el-input-->
<!-- v-model="search"-->
<!-- size="mini"-->
<!-- placeholder="输入关键字搜索"/>-->
<!-- </template>-->
<template slot-scope="scope">
<el-button
size="mini"
type="success"
@click="handleEdit(scope.row)">编辑</el-button>
<el-button
size="mini"
type="danger"
@click="handleDelete(scope.row)">删除</el-button>
</template>
</el-table-column>
</el-table>
<div style="text-align: center">
<el-pagination
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page.sync="currentPage"
:page-sizes="[5, 10, 15, 20,25]"
:page-size="pageSize"
layout="sizes, prev, pager, next"
:total="total">
</el-pagination>
</div>
</div>
<script>
new Vue({
el:"#app",
data(){
return{
ComponentData:[],
search:'',
currentPage: 1,// 当前页码
pageSize:5,
total:100,
index:'',
form:{
cno:'',
cname:'',
csize:''
}
}
},
methods:{
handleEdit(row) {
window.location.href="update.html?con="+row.cno;
},
handleDelete(row) {
if(window.confirm("你确定要删除这条数据吗?")){
axios.post(`/delete/${row.cno}`).then(r=>{
if(r.data.statusCode==1){
alert("删除成功!")
this.show();
}
}).catch(error=>{
console.error(error);
})
}
},
handleSizeChange(val) {
this.pageSize=val;
this.show();
},
handleCurrentChange(val) {
this.currentPage =val;
this.show();
},
show(){
axios(`/page/${this.pageSize}/${this.currentPage}`).then(r=>{
if(r.data.statusCode==1){
this.ComponentData=r.data.data.rows;
this.total=r.data.data.total;
}
}).catch(error=>{
console.error(error);
})
},
JumpOff(){
window.location.href="insert.html"
},
SSubmit(){
axios.get(`/search/${this.pageSize}/${this.currentPage}?cno=${this.form.cno}&cname=${this.form.cname}&csize=${this.form.csize}`).then(r=>{
if(r.data.statusCode==1){
this.ComponentData=r.data.data.rows;
this.total=r.data.data.total;
}
}).catch(error=>{
console.error(error);
})
}
},
created(){
this.show();
}
})
</script>
</body>
</html>
2.foreach标签
利用foreach标签完成前端批量删除的功能,实现一次删除多个数据。foreach标签中collection绑定选取参数的列表,separator是每个取值之间的间隔符,close和open是起始标识和结尾标识。
ComponentMapper.xml的部分代码如下:
<!-- 批量删除员工
collection:取值的集合
item:取出来的值的名字
separator:分隔符
close:取完值以后接着的符号
open:取值之前的符号-->
<delete id="DeleteAll">
delete from component where cno in
<foreach collection="cnos" item="cno" separator="," close=")" open="(">
#{cno}
</foreach>
</delete>
对应的ComponentMapper.interface部分:
public boolean DeleteAll(List<String> cnos);
对应的ComponentService.interface部分:
public boolean DeleteAll(List<String> cnos);
对应的ComponentServiceA.class部分:
@Override
public boolean DeleteAll(List<String> cnos) {
return componentMapper.DeleteAll(cnos);
}
对应的ComponentController.class部分:
@RequestMapping("/DeleteAll")
public Result DeleteAll(@RequestBody List<String> cnos){
boolean r=componentService.DeleteAll(cnos);
if(r){
return Result.success();
}
else{
return Result.error();
}
}
对应的show.html部分:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="./js/vue.js"></script>
<script src="./js/axios-0.18.0.js"></script>
<link rel="stylesheet" href="element-ui/index.css">
<script src="./element-ui/index.js"></script>
</head>
<body>
<h1 style="text-align: center">零件信息表</h1>
<div id="app">
<div style="text-align:center">
<p align="center">
<el-form :inline="true" :model="form" class="demo-form-inline">
<el-form-item label="零件号">
<el-input v-model="form.cno" placeholder="零件号"></el-input>
</el-form-item>
<el-form-item label="零件名">
<el-input v-model="form.cname" placeholder="零件名"></el-input>
</el-form-item>
<el-form-item label="尺寸">
<el-input v-model="form.csize" placeholder="尺寸"></el-input>
</el-form-item>
<el-form-item>
<el-button type="primary" @click="SSubmit">查询</el-button>
</el-form-item>
</el-form>
</p>
</div>
<el-table
:data="ComponentData"
style="width: 80%;margin: auto " ref="multipleTable" @selection-change="handleSelectionChange">
<el-table-column type="selection" width="55" align="center">
</el-table-column>
<el-table-column
align="center"
label="零件号"
prop="cno">
</el-table-column>
<el-table-column
align="center"
label="零件名"
prop="cname">
</el-table-column>
<el-table-column
align="center"
label="尺寸"
prop="csize">
</el-table-column>
<el-table-column
align="center"
label="单价"
prop="cprice">
</el-table-column>
<el-table-column
align="center"
label="描述"
prop="ctext">
</el-table-column>
<el-table-column
align="center"
label="操作">
<!-- <template slot="header" slot-scope="scope">-->
<!-- <el-input-->
<!-- v-model="search"-->
<!-- size="mini"-->
<!-- placeholder="输入关键字搜索"/>-->
<!-- </template>-->
<template slot-scope="scope">
<el-button
size="mini"
type="success"
@click="handleEdit(scope.row)">编辑</el-button>
<el-button
size="mini"
type="danger"
@click="handleDelete(scope.row)">删除</el-button>
</template>
</el-table-column>
</el-table>
<div style="width: 80%;margin: auto;margin-top: 10px" ><el-button type="primary" plain v-on:click="JumpOff">添加零件</el-button>
<el-button type="danger" plain v-on:click="deleteall">批量删除</el-button>
</div>
<div style="text-align: center">
<el-pagination
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page.sync="currentPage"
:page-sizes="[5, 10, 15, 20,25]"
:page-size="pageSize"
layout="sizes, prev, pager, next"
:total="total">
</el-pagination>
</div>
</div>
<script>
new Vue({
el:"#app",
data(){
return{
ComponentData:[],
search:'',
currentPage: 1,// 当前页码
pageSize:5,
total:100,
index:'',
form:{
cno:'',
cname:'',
csize:''
},
multipleSelection: [],
List:[]
}
},
methods:{
handleEdit(row) {
window.location.href="update.html?con="+row.cno;
},
handleDelete(row) {
if(window.confirm("你确定要删除这条数据吗?")){
axios.post(`/delete/${row.cno}`).then(r=>{
if(r.data.statusCode==1){
alert("删除成功!")
this.show();
}
}).catch(error=>{
console.error(error);
})
}
},
handleSizeChange(val) {
this.pageSize=val;
this.show();
},
handleCurrentChange(val) {
this.currentPage =val;
this.show();
},
show(){
axios(`/page/${this.pageSize}/${this.currentPage}`).then(r=>{
if(r.data.statusCode==1){
this.ComponentData=r.data.data.rows;
this.total=r.data.data.total;
}
}).catch(error=>{
console.error(error);
})
},
JumpOff(){
window.location.href="insert.html"
},
SSubmit(){
axios.get(`/search/${this.pageSize}/${this.currentPage}?cno=${this.form.cno}&cname=${this.form.cname}&csize=${this.form.csize}`).then(r=>{
if(r.data.statusCode==1){
this.ComponentData=r.data.data.rows;
this.total=r.data.data.total;
}
}).catch(error=>{
console.error(error);
})
},
deleteall(){
if(window.confirm("确定要删除吗?")){
axios.post("/DeleteAll",
this.List,{headers: {
'Content-Type': 'application/json'
}}).then(r=>{
if(r.data.statusCode==1){
window.alert("删除成功!")
this.show();
}
}).catch(error=>{
console.error(error);
})
}
},
handleSelectionChange(val){
this.multipleSelection=val;
this.List = this.multipleSelection.map(m => m.cno);
}
},
created(){
this.show();
}
})
</script>
</body>
</html>
实现效果:
3.sql标签和include标签
将sql语句中经常使用的部分封装起来放在sql中,在其他语句中通过include引用sql的部分。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.liuxiaocan.finalwork.Mapper.ComponentMapper">
<sql id="All">
select * from component
</sql>
<select id="selectall" resultType="com.liuxiaocan.finalwork.Pojo.Component">
<include refid="All"></include>
</select>
<select id="SelectPart" resultType="com.liuxiaocan.finalwork.Pojo.Component">
<include refid="All"></include> where CNo=#{cno}
</select>
<update id="Update" >
update Component set CName=#{cname},CSize=#{csize},CPrice=#{cprice},CText=#{ctext} where CNo=#{cno}
</update>
<insert id="Insert">
insert into Component values (#{cno},#{cname},#{csize},#{cprice},#{ctext})
</insert>
<delete id="Delete">
delete from Component where CNo=#{cno}
</delete>
<select id="Chaxun" resultType="com.liuxiaocan.finalwork.Pojo.Component">
<include refid="All"></include>
<where>
<if test="cno!=null">
CNo LIKE CONCAT('%', #{cno}, '%')
</if>
<if test="cname!=null">
and CName LIKE CONCAT('%', #{cname}, '%')
</if>
<if test="csize!=null">
and CSize LIKE CONCAT('%', #{csize}, '%')
</if>
</where>
</select>
<!-- 批量删除员工
collection:取值的集合
item:取出来的值的名字
separator:分隔符
close:取完值以后接着的符号
open:取值之前的符号-->
<delete id="DeleteAll">
delete from component where cno in
<foreach collection="cnos" item="cno" separator="," close=")" open="(">
#{cno}
</foreach>
</delete>
</mapper>