需求:客户信息的增删改查,包括省市区三级联动,前后端分离式开发。
思路:前端使用Vue+ElementUI框架,后端为Springboot+Mybatis。
后台
0、创建实体类SysAddress.java、修改SysUser。
public class SysAddress {
private Integer id;
private String cityname;
}
public class SysUser extends BaseEntity
{
略。。。。。。。。。。
private String province;
private String city;
private String distinct;
}
1、在 SysUserMapper.xml
中添加 selectCityByPid
,此处city为创建的新表,包括id、cityname、pid三个字段。同时更改查询、增加、更改用户的sql语句。
SysUserMapper.xml
selectCityByPid
-
查询城市mapper文件。
<resultMap id="addressResult" type="SysAddress"> <id column="id" property="id"/> <result column="cityname" property="cityname"/> </resultMap> <select id="selectCityByPid" resultMap="addressResult"> select id,cityname from city where pid= #{id} </select>
-
增加用户mapper文件。
<insert id="insertUser" parameterType="SysUser" useGeneratedKeys="true" keyProperty="userId"> insert into sys_user( 略。。。。。。。。。。。。。 <if test="province != null and province != ''">province,</if> <if test="city != null and city != ''">city,</if> <if test="distinct != null and distinct != ''">`distinct`,</if> create_time )values( 略。。。。。。。。。。。。。 <if test="province != null and province != ''">#{province},</if> <if test="city != null and city != ''">#{city},</if> <if test="distinct != null and distinct != ''">#{distinct},</if> sysdate() ) </insert>
-
查询用户mapper文件。
<select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult"> select u.user_id, u.dept_id, u.nick_name, u.user_name, u.email, u.avatar, u.phonenumber, u.password, u.sex, u.status, u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark,city1.cityname as province0,city2.cityname as city0,city3.cityname as distinct0, d.dept_name, d.leader from sys_user u left join sys_dept d on u.dept_id = d.dept_id left join city city1 on u.province=city1.id left join city city2 on u.city=city2.id left join city city3 on u.distinct=city3.id where u.del_flag = '0' 略。。。。。。。。。。。。。 <if test="province != null and province != ''"> AND u.province like concat('%', #{province}, '%') </if> <if test="city != null and city != ''"> AND u.city like concat('%', #{city}, '%') </if> <if test="distinct != null and distinct != ''"> AND u.distinct like concat('%', #{distinct}, '%') </if> <!-- 数据范围过滤 --> ${params.dataScope} </select> <resultMap type="SysUser" id="SysUserResult"> 略。。。。。。。。。。。。。。。。 <result property="province" column="province0" /> <result property="city" column="city0" /> <result property="distinct" column="distinct0" /> <association property="dept" column="dept_id" javaType="SysDept" resultMap="deptResult" /> <collection property="roles" javaType="java.util.List" resultMap="RoleResult" /> </resultMap>
-
更改用户mapper文件。
<update id="updateUser" parameterType="SysUser"> update sys_user <set> 略。。 <if test="province != null">province = #{province},</if> <if test="city != null">city = #{city},</if> <if test="distinct != null">`distinct` = #{distinct},</if> update_time = sysdate() </set> where user_id = #{userId} </update>
2、在 SysUserMapper.java
中添加对应方法。
SysUserMapper.java
3、在 ISysUserService
以及 SysUserServiceImpl
添加查询方法。
ISysUserService
SysUserServiceImpl
public List<SysAddress> FindCityByPid(Integer pid)
{
return userMapper.selectCityByPid(pid);
}
4、于 XcmgUserController
中利用Ajax与前台交换数据。
XcmgUserController
@GetMapping("/city")
public AjaxResult selectCityById(@RequestParam Integer cityId)
{
AjaxResult ajax = AjaxResult.success();
Integer pid=userService.selectCityById(cityId);
List<SysAddress> sysAddress = userService.selectCityByPid(pid);
ajax.put("addresslist", userService.selectCityByPid(pid));
return ajax;
}
运行项目,使用postman工具测试是否能获取省市区数据。
如图成功获取市区数据!
前台
0、编写api
export function getCity(CityId) {
return request({
url: '/xcmg/user/city/?cityId=' + CityId,
method: 'get'
})
}
1、在查询列表、显示表单、添加\修改对话框中添加省市区标签
-
查询列表
<el-form :model="queryParams" ref="queryForm" :inline="true" v-show="showSearch" label-width="68px">
<el-form-item label="省" prop="province">
<el-select v-model="queryParams.province" placeholder="请选择省" v-on="{focus:seeProvince,change:seeCity}">
<el-option
v-for="item in ProvinceList"
:key="item.id"
:label="item.cityname"
:value="item.id"
:disabled="item.status == 1"
></el-option>
</el-select>
</el-form-item>
<el-form-item label="市" prop="city">
<el-select v-model="queryParams.city" placeholder="请选择市" @change="seeDistinct">
<el-option
v-for="item in CityList"
:key="item.id"
:label="item.cityname"
:value="item.id"
:disabled="item.status == 1"
></el-option>
</el-select>
</el-form-item>
<el-form-item label="区" prop="distinct">
<el-select v-model="queryParams.distinct" placeholder="请选择区" >
<el-option
v-for="item in DistinctList"
:key="item.id"
:label="item.cityname"
:value="item.id"
:disabled="item.status == 1"
></el-option>
</el-select>
</el-form-item>
-
显示表单
<el-table v-loading="loading" :data="userList" @selection-change="handleSelectionChange">
<el-table-column type="selection" width="50" align="center" />
<el-table-column label="用户编号" align="center" key="userId" prop="userId" v-if="columns[0].visible" />
<el-table-column label="用户名称" align="center" key="userName" prop="userName" v-if="columns[1].visible" :show-overflow-tooltip="true" />
<el-table-column label="手机号码" align="center" key="phonenumber" prop="phonenumber" v-if="columns[2].visible" width="120" />
<el-table-column label="省" align="center" key="province" prop="province" v-if="columns[3].visible" :show-overflow-tooltip="true" />
<el-table-column label="市" align="center" key="city" prop="city" v-if="columns[4].visible" :show-overflow-tooltip="true" />
<el-table-column label="区" align="center" key="distinct" prop="distinct" v-if="columns[5].visible" :show-overflow-tooltip="true" />
</el-table>
-
添加/修改对话框
<el-dialog :title="title" :visible.sync="open" width="600px" append-to-body>
<el-form ref="form" :model="form" :rules="rules" label-width="80px">
<el-col :span="12">
<el-form-item label="省" >
<el-select v-model="form.province" placeholder="请选择省" v-on="{focus:formProvince,change:formCity}">
<el-option
v-for="item in FormProvinceList"
:key="item.id"
:label="item.cityname"
:value="item.id"
></el-option>
</el-select>
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="市" >
<el-select v-model="form.city" placeholder="请选择市" @change="formDistinct">
<el-option
v-for="item in FormCityList"
:key="item.id"
:label="item.cityname"
:value="item.id"
></el-option>
</el-select>
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="区" >
<el-select v-model="form.distinct" placeholder="请选择区" >
<el-option
v-for="item in FormDistinctList"
:key="item.id"
:label="item.cityname"
:value="item.id"
></el-option>
</el-select>
</el-form-item>
</el-col>
</el-form>
</el-dialog>
2、设置查询列表、显示表单、添加\修改对话框中的省市区标签中的数据。
data() {
return {
CityList: null,
ProvinceList: null,
DistinctList: null,
FormCityList: null,
FormProvinceList: null,
FormDistinctList: null,
form: {},
queryParams: {
pageNum: 1,
pageSize: 10,
province:undefined,
city:undefined,
distinct:undefined,
userName: undefined,
phonenumber: undefined,
fromWhere:undefined,
status: undefined,
deptId: undefined
},
columns: [
{ key: 0, label: `用户编号`, visible: true },
{ key: 1, label: `用户名称`, visible: true },
{ key: 2, label: `手机号码`, visible: true },
{ key: 3, label: `区`, visible: true },
{ key: 4, label: `市`, visible: true },
{ key: 5, label: `区`, visible: true },
],
}
}
3、设置查询列表、显示表单、添加\修改对话框中的省市区标签中的动作。
methods: {
seeProvince(){
const ProvinceId=1;
getCity(ProvinceId).then(response =>{this.ProvinceList=response.addresslist});
},
seeCity(){
getCity(this.queryParams.province).then(response =>{this.CityList=response.addresslist});
this.queryParams.city=null;
this.queryParams.distinct=null;
},
seeDistinct(){
getCity(this.queryParams.city).then(response =>{this.DistinctList=response.addresslist});
this.queryParams.distinct=null;
},
formProvince(){
const ProvinceId=1;
getCity(ProvinceId).then(response =>{this.FormProvinceList=response.addresslist});
},
formCity(){
getCity(this.form.province).then(response =>{this.FormCityList=response.addresslist});
this.from.city=null;
this.form.distinct=null;
},
formDistinct(){
getCity(this.form.city).then(response =>{this.FormDistinctList=response.addresslist});
// this.form.distinct=null;
}
}
至此省市区级联功能得以实现!