elementUI实现数据增删改查

数据：login_user表，结构如下：

实现流程及代码如下

【由于修改与根据id删除用户在同一行，实践起来是一样的，这里省略】

1、创建对应的UserInfo 实体类和分页需要的实体类PageInfo

@NoArgsConstructor
@AllArgsConstructor
@Setter
@Getter
@ToString
public class UserInfo {
    //对应表中的id
    private Integer id;
    //对应表中的user_name
    private String userName;
    //对应表中的user_pwd
    private String userPwd;
    //对应表中的role
    private Integer role;
    //对应表中的description
    private String description;
    //页面显示的用户角色，值由role确定
    private String roleStr;

    public void setRoleStr() {
        if (role == 0) roleStr = "用户";
        else if (role == 1) roleStr = "管理员";
    }

    public String getRoleStr() {
        return roleStr;
    }
}

PageInfo实体类：

@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@ToString
//分页查询的实体类
public class PageInfo<T> {
    //总条数
    private Integer totalCount;
    //当前页面显示的数据集合
    private List<T> users;
}

2、在mybatis-config.xml中加载sql映射文件路径

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <package name="com.demo1.pojo"></package>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///study_db?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--加载sql映射文件-->
        <mapper resource="com/demo1/mapper/UserInfoMapper.xml"/>
    </mappers>
</configuration>

3、创建表的sql映射的配置文件，路径要跟上面的mapper路径一致如下

<?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">
<!--namespace：名称空间-->
<mapper namespace="com.demo1.mapper.UserInfoMapper">
    <resultMap id="userRs" type="UserInfo">
        <result column="user_name" property="userName"></result>
        <result column="user_pwd" property="userPwd"></result>
    </resultMap>
    <insert id="add">
        insert into login_user values (null,#{userName},#{userPwd},#{role},#{description});
    </insert>
    <update id="update">
        update login_user
        <set>
            <if test="userName!= null">user_name=#{userName},</if>
            <if test="userPwd!= null">user_pwd=#{userPwd},</if>
        </set>
        where id=#{id}
    </update>
    <delete id="delete">
        delete from login_user where id=#{id}
    </delete>
    <delete id="deleteAll">
        delete from login_user where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>
    <select id="selectAll" resultMap="userRs">
        select * from login_user;
    </select>
    <select id="select" resultMap="userRs">
        select * from login_user where id=#{id}
    </select>
    <select id="selectInPage" resultMap="userRs">
        select * from login_user limit #{currentPage},#{showNum}
    </select>
    <select id="selectInPageAndCondition" resultMap="userRs">
        select * from login_user
        <where>
            <if test="user.userName!=null">
                and user_name like #{user.userName}
            </if>
            <if test="user.role!=null">
                and role=#{user.role}
            </if>
        </where>
        limit #{currentPage},#{showNum}
        ;
    </select>
    <select id="getTotalCountByCondition" resultType="java.lang.Integer">
        select count(*) from login_user
        <where>
            <if test="userName!=null">
                and user_name like #{userName}
            </if>
            <if test="role!=null">
                and role=#{role}
            </if>
        </where>
        ;
    </select>
</mapper>

4、创建配置文件映射到的接口，文件创建路径需要一致：

并且添加需要用到的增删改查方法：

public interface UserInfoMapper {
    /**
     * 添加用户
     *
     * @param userInfo
     * @return
     */
    int add(UserInfo userInfo);

    /**
     * 删除用户
     *
     * @param id
     * @return
     */
    int delete(int id);

    /**
     * 批量删除
     *
     * @param id
     */
    void deleteAll(@Param("ids") Integer[] id);

    /**
     * 根据条件分页查询
     * @param userInfo
     * @param currentPage 当前页码
     * @param showNum 每页显示条数
     * @return
     */
    List<UserInfo> selectInPageAndCondition(@Param("user")UserInfo userInfo,@Param("currentPage") int currentPage, @Param("showNum") int showNum);

    /**
     * 模糊查询数据库总条数
     *
     * @return
     */
    int getTotalCountByCondition(UserInfo userInfo);
}

5、创建service层接口和它的实现类：

public interface UserService {
    /**
     * 新增用户
     * @param userInfo
     */
    void add(UserInfo userInfo);

    /**
     * 根据id删除用户
     * @param id
     */
    void delete(int id);

    /**
     * 根据id数组删除一组用户
     * @param ids
     */
    void deleteAll(Integer[] ids);

    /**
     * 根据id查找用户
     * @param id
     * @return
     */
    UserInfo select(int id);

    /**
     * 模糊查询并实现分页显示
     * @param currentPage 当前页码
     * @param showNum 每页显示条数
     * @param userInfo 模糊查询的条件
     * @return
     */
    PageInfo<UserInfo> formSelect(int currentPage, int showNum, UserInfo userInfo);
}

实现类：

public class UserServiceImpl implements UserService {

    SqlSessionFactory ssf = SqlSF.getSSF();
    @Override
    public void add(UserInfo userInfo) {
        SqlSession sqlSession = ssf.openSession();
        UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
        mapper.add(userInfo);
        sqlSession.commit();
    }
    @Override
    public void delete(int id) {
        SqlSession sqlSession = ssf.openSession();
        UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
        mapper.delete(id);
        sqlSession.commit();
    }
    @Override
    public void deleteAll(Integer[] ids) {
        SqlSession sqlSession = ssf.openSession();
        UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
        mapper.deleteAll(ids);
        sqlSession.commit();
    }

    @Override
    public UserInfo select(int id) {
        SqlSession sqlSession = ssf.openSession();
        UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
        return mapper.select(id);
    }
    @Override
    public PageInfo<UserInfo> formSelect(int currentPage, int showNum, UserInfo userInfo) {
        SqlSession sqlSession = ssf.openSession();
        UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
        int begin = (currentPage - 1) * showNum;
        System.out.println(userInfo);
        if (userInfo!=null&&userInfo.getUserName() != null && userInfo.getUserName() != "")
            userInfo.setUserName("%" + userInfo.getUserName() + "%");
        else if(userInfo.getUserName()==""&&userInfo.getRole()==null){
            userInfo=null;
        }
        System.out.println(userInfo);
        List<UserInfo> userInfos = mapper.selectInPageAndCondition(userInfo, begin, showNum);
        for (UserInfo info : userInfos) {
            info.setRoleStr();
        }
        System.out.println("符合条件数据："+userInfos);
        int totalCount = mapper.getTotalCountByCondition(userInfo);
        System.out.println("条件查询总条数："+totalCount);
        PageInfo<UserInfo> userInfoPageInfo = new PageInfo<>(totalCount, userInfos);
        sqlSession.close();
        return userInfoPageInfo;
    }
}

6、工具类：

获取sql工厂

public class SqlSF {
    private static SqlSessionFactory sqlSessionFactory;

    private SqlSF() {

    }
    public static SqlSessionFactory getSSF() {
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            return sqlSessionFactory;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}

7、servlet层-->自定义BaseServlet，在service层获取路径，根据路径不同执行对应的方法

public class BaseServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //这里uri没有带项目名称，可以看自己服务器那边的配置有没有加项目名称
        String requestURI = req.getRequestURI();
        String methodName = requestURI.substring(requestURI.lastIndexOf('/') + 1);
        //获取方法并且执行
        Class<? extends BaseServlet> aClass = this.getClass();
        try {
            Method method = aClass.getMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);
            method.invoke(this,req,resp);
        } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
            e.printStackTrace();
        }
    }
}

8、创建子类继承它，访问路径使用通配符

@WebServlet("/user/*")
public class UserServlet extends BaseServlet {
    public void selectByPageAndCondition(HttpServletRequest req, HttpServletResponse response) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        String currentPage = req.getParameter("currentPage");
        String pageSize = req.getParameter("pageSize");
        String s = req.getReader().readLine();
        System.out.println(s);
        UserInfo userInfo = JSON.parseObject(s, UserInfo.class);
        PageInfo<UserInfo> userInfoPageInfo = userService.formSelect(Integer.parseInt(currentPage), Integer.parseInt(pageSize), userInfo);
        System.out.println(userInfoPageInfo);
        response.setContentType("text/json;charset=utf-8");
        response.getWriter().write(JSON.toJSONString(userInfoPageInfo));
    }


    UserService userService = new UserServiceImpl();

    public void deleteAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //请求参数需要修改编码格式
        request.setCharacterEncoding("utf-8");
        String s = request.getReader().readLine();
        System.out.println(s);
        Integer[] ids = JSON.parseObject(s, Integer[].class);
        userService.deleteAll(ids);
        System.out.println(Arrays.toString(ids));
        response.getWriter().write("deleted");
    }

    public void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //请求参数需要修改编码格式
        request.setCharacterEncoding("utf-8");
        String s = request.getReader().readLine();
        //将json数据转成对象
        UserInfo userInfo = JSON.parseObject(s, UserInfo.class);
        userService.add(userInfo);
        response.getWriter().write("added");
    }
    public void update(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("user..update");
    }
    public void delete(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        String s = req.getReader().readLine();
        System.out.println(s);
        Integer id = JSON.parseObject(s, Integer.class);
        userService.delete(id);
        resp.getWriter().write("deleted");
        System.out.println("delete");
    }
}

9、创建前端页面，并且实现对应的功能：

<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <style>
        .el-table .warning-row {
            background: oldlace;
        }

        .el-table .success-row {
            background: #f0f9eb;
        }
    </style>
</head>
<body>
<link href="element-ui/lib/theme-chalk/index.css" rel="stylesheet">
<script src="js/axios.js"></script>
<script src="js/vue.js"></script>
<script src="element-ui/lib/index.js"></script>


<div id="app">
    <!--查询表单-->
    <el-form :inline="true" :model="user" class="demo-form-inline">
        <el-form-item label="用户名">
            <el-input v-model="user.username" placeholder="username"></el-input>
        </el-form-item>
        <el-form-item label="角色">
            <el-select v-model="user.role" placeholder="role">
                <el-option label="管理员" value="1"></el-option>
                <el-option label="用户" value="0"></el-option>
            </el-select>
        </el-form-item>
        <el-form-item>
            <el-button type="primary" @click="onSubmit">查询</el-button>
        </el-form-item>
    </el-form>
    <!--新增删除按钮-->
    <el-row>
        <el-button type="primary" plain @click="dialogVisible = true">新增</el-button>
        <el-button type="danger" plain @click="deleteAll">批量删除</el-button>
    </el-row>
    <el-dialog
            title="新增用户"
            :visible.sync="dialogVisible"
            width="50%">
        <el-form ref="form" :model="user" label-width="80px">
            <el-form-item label="用户名">
                <el-input v-model="user.username"></el-input>
            </el-form-item>
            <el-form-item label="密码">
                <el-input v-model="user.userpwd"></el-input>
            </el-form-item>
            <el-form-item label="管理员">
                <el-switch v-model="user.role"
                           active-value="1"
                           inactive-value="0"></el-switch>
            </el-form-item>
            <el-form-item label="备注">
                <el-input type="textarea" v-model="user.description"></el-input>
            </el-form-item>
            <el-form-item>
                <el-button type="primary" @click="addUser">确定新增</el-button>
                <el-button @click="dialogVisible = false">取消</el-button>
            </el-form-item>
        </el-form>
    </el-dialog>
    <!--展示表格-->
    <template>
        <el-table
                ref="userTable"
                :data="userList"
                style="width: 100%"
                highlight-current-row
                :row-class-name="tableRowClassName"
                @selection-change="handleSelectionChange"
                @current-change="handleCurrentRow">
            <!--复选框-->
            <el-table-column
                    type="selection">
            </el-table-column>
            <el-table-column
                    label="排序"
                    type="index"
                    align="center"
            >
            </el-table-column>
            <!--注意列名与json获得的数据名需要一致-->
            <el-table-column
                    prop="userName"
                    label="姓名"
                    align="center"
            >
            </el-table-column>
            <el-table-column
                    prop="userPwd"
                    label="密码"
                    align="center"
            >
            </el-table-column>
            <el-table-column
                    prop="roleStr"
                    align="center"
                    label="角色">
            </el-table-column>
            <el-table-column
                    prop="description"
                    align="center"
                    label="备注">
            </el-table-column>
            <el-table-column align="center" label="操作">
                <template slot-scope="scope">
                    <el-button type="primary">修改</el-button>
                    <el-button type="danger" @click="removeUserById(scope.row.id)">删除</el-button>
                </template>
            </el-table-column>
        </el-table>
    </template>
    <!--完整分页-->
    <el-pagination
            @size-change="handleSizeChange"
            @current-change="handleCurrentChange"
            :current-page="currentPage"
            :page-sizes="[8, 16, 24, 32]"
            :page-size="8"
            layout="total, sizes, prev, pager, next, jumper"
            :total="totalCount">
    </el-pagination>
</div>

<script>
    new Vue({
        el: "#app",
        data() {
            return {
                //当前页码
                currentPage: 1,
                //总条数
                totalCount: 100,
                //每页显示条数
                pageSize: 8,
                //控制弹出新增表单是否可见
                dialogVisible: false,
                //复选框数组数据
                multipleSelection: [],
                //用户挂载对象
                user: {
                    username: '',
                    userpwd: '',
                    role: '',
                    description: ''
                },
                //用户数据
                userList: [],
                currentRow: '',
                selectUser: []
            }
        },
        //页面加载完成之后加载数据库的数据
        mounted() {
            this.selectAll();
        },
        methods: {
            setCurrent(row) {
                this.$refs.userTable.setCurrentRow(row);
            },
            //分页查询数据
            selectAll() {
                var thisVue = this;
                axios({
                    method: "post",
                    url: "http://localhost:8088/user/selectByPageAndCondition?currentPage=" + this.currentPage + "&pageSize=" + this.pageSize,
                    data: this.user
                }).then(function (resp) {
                    thisVue.userList = resp.data.users;
                    thisVue.totalCount = resp.data.totalCount;
                })
                //上面可以优化成下面：可以直接使用this代表外面的thisVue
                // then(resp => {
                //     this.userList = resp.data.users;
                //     this.totalCount = resp.data.totalCount;
                // })
            },
            //获取当前选中行的对象值
            handleCurrentRow(val) {
                this.currentRow = val;

            },
            //点击批量删除按钮触发事件,弹出确认是否删除框
            deleteAll() {
                this.$confirm('此操作将永久删除该数据, 是否继续?', '提示', {
                    confirmButtonText: '确定',
                    cancelButtonText: '取消',
                    type: 'warning'
                }).then(() => {
                    //用户点击确认按钮
                    var thisVue = this;
                    var objList = this.multipleSelection;
                    for (let i = 0; i < objList.length; i++) {
                        this.selectUser[i] = objList[i].id;
                    }
                    var users = this.selectUser;
                    axios.post("http://localhost:8088/user/deleteAll", users).then(function (resp) {
                        if (resp.data == "deleted") {
                            thisVue.$message({
                                showClose: true,
                                message: '删除成功'
                            });
                            thisVue.selectAll();
                        }
                    })
                }).catch(() => {
                    //用户点击取消按钮
                    this.$message({
                        type: 'info',
                        message: '已取消删除'
                    })
                })
            },
            //控制表格颜色
            tableRowClassName({row, rowIndex}) {
                if (rowIndex === 1) {
                    return 'warning-row';
                } else if (rowIndex === 3) {
                    return 'success-row';
                }
                return '';
            },
            // 表单条件查询按钮单击事件
            onSubmit() {
                this.selectAll();
            },
            //获取复选框选中的数组
            handleSelectionChange(val) {
                this.multipleSelection = val
            },
            //单击确定新增之后，获取新增之后的用户信息
            addUser() {
                var thisVue = this;
                var obj = this.user;
                axios.post("http://localhost:8088/user/add", obj).then(function (resp) {
                    if (resp.data == "added") {
                        thisVue.dialogVisible = false;
                        var clear={
                            username: '',
                            userpwd: '',
                            role: '',
                            description: ''
                        }
                        thisVue.user=clear;

                        //弹出添加成功消息提示框
                        thisVue.$message({
                            message: '添加成功',
                            type: 'success'
                        });
                        thisVue.selectAll();
                    }
                })
            },
            //获取每页的显示的条数
            handleSizeChange(val) {
                this.pageSize = val;
                this.selectAll();
            },
            //获取当前页码
            handleCurrentChange(val) {
                this.currentPage = val;
                this.selectAll();
            },
            removeUserById(id) {
                var thisVue=this;
                console.log(id)
                this.$confirm('此操作将永久删除该用户, 是否继续?', '提示', {
                    confirmButtonText: '确定',
                    cancelButtonText: '取消',
                    type: 'warning'
                }).then(() => {
                    axios.post("http://localhost:8088/user/delete", id).then(function (resp) {
                        if (resp.data == "deleted") {
                            thisVue.$message({
                                showClose: true,
                                message: '删除成功'
                            });
                            thisVue.selectAll();
                        }
                    })
                }).catch(() => {
                    //用户点击取消按钮
                    this.$message({
                        type: 'info',
                        message: '已取消删除'
                    })
                })
            }
        },
    })
</script>
</body>
</html>

下面记录实现每一步的过程...