[Java知识库] Springboot框架下的Thymeleaf和通用Mapper对数据库的操作

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> Java知识库 -> Springboot框架下的Thymeleaf和通用Mapper对数据库的操作 -> 正文阅读

[Java知识库]Springboot框架下的Thymeleaf和通用Mapper对数据库的操作

?首先导入依赖

<dependencies>
            <!-- 分页插件 -->
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.10</version>
            </dependency>
            <!--mysql-->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.46</version>
            </dependency>
            <!--mybatis 通用mapper-->
            <dependency>
                <groupId>tk.mybatis</groupId>
                <artifactId>mapper-spring-boot-starter</artifactId>
                <version>2.1.5</version>
            </dependency>
            <!--jdbc 包含hikariCP-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            <!--thymeleaf-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-thymeleaf</artifactId>
            </dependency>
            <!--web-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <!--热部署-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-devtools</artifactId>
                <scope>runtime</scope>
                <optional>true</optional>
            </dependency>
            <!--属性注入bean-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-configuration-processor</artifactId>
                <optional>true</optional>
            </dependency>
            <!--lombok-->
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
            <!--test-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
                <exclusions>
                    <exclusion>
                        <groupId>org.junit.vintage</groupId>
                        <artifactId>junit-vintage-engine</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
            <!--security-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-security</artifactId>
            </dependency>
            <!--安全测试-->
            <dependency>
                <groupId>org.springframework.security</groupId>
                <artifactId>spring-security-test</artifactId>
                <scope>test</scope>
            </dependency>
            <!-- thymeleaf结合security实现页面控制的启动器  -->
            <dependency>
                <groupId>org.thymeleaf.extras</groupId>
                <artifactId>thymeleaf-extras-springsecurity5</artifactId>
            </dependency>
        </dependencies>

MAPPER部分

public interface SanGuoMapperTK extends Mapper {

//selectAll
@Select(“select * from sanguo”)
List selectAll();
@Insert("insert into sanguo values(#{id},#{name},#{password},#{type},#{birth},#{headImg},#{sex})")
void addSanGuo(SanGuoTK sanGuoTK);

@Update("update sanguo set name=#{name},password=#{password},type=#{type},birth=#{birth},headImg=#{headImg},sex=#{sex} where id=#{id}")
void upDateSanGuo(SanGuoTK sanGuoTK);

@Select("select * from sanguo where id = #{id}")
List<SanGuoTK> selectSanGuoByTd(int id);

@Select("select * from sanguo where id = #{id}")
SanGuoTK selectSanGuoByTd1(int id);

@Select("select * from sanguo where name like concat('%',#{name},'%')")
List<SanGuoTK> selectSanGuoByName(String name);


}

}
这里的模糊查询比较坑，需要使用字符串拼接。不然会因为参数传递时会受到自己带有引号的影响。

CONTROLLER部分

@Controller
@RequestMapping("/user")
public class UserControall {

    @Autowired
    private SanGuoMapperTK sanGuoMapperTK;

    @RequestMapping("/showuser")//分页查询
    public String show(Model model,
                       @RequestParam(defaultValue = "1") Integer pageNo,
                       @RequestParam(defaultValue = "3") Integer pageSize){
        //1.分页查询
        PageHelper.startPage(pageNo,pageSize);
        List<SanGuoTK> sanGuoTKS = sanGuoMapperTK.selectAll();
        PageInfo<SanGuoTK> pageInfo = new PageInfo<>(sanGuoTKS);
        //2.存进model
        model.addAttribute("pageInfo",pageInfo);
        //3.跳转到users下的show页面
        return "admin/user/showuser";
    }



    @RequestMapping("/delete/{id}")//删除
    public String deleteById(@PathVariable Integer id){
        //使用rest风格接收参数，调用mapper实现删除，返回到index.html
        sanGuoMapperTK.deleteByPrimaryKey(id);
        return "admin/admin";
    }

    //新增用户：1.跳转到新增页面 2.执行用户数据新增
    @RequestMapping("/add_user")
    public String addUser(){
        return "admin/user/add_user";
    }

    @RequestMapping("/add")//用户添加
    public String addSanGuo(SanGuoTK sanGuoTK){
        System.out.println(sanGuoTK);
        sanGuoMapperTK.addSanGuo(sanGuoTK);
        return "redirect:/admin";
    }


    @RequestMapping("/update/{id}")
    public String updateSanGuo(Model model,@PathVariable Integer id){
        SanGuoTK sanGuoTK=sanGuoMapperTK.selectSanGuoByTd1(id);
        model.addAttribute("sanGuoTK",sanGuoTK);
        return "admin/user/update_user";
    }

    @RequestMapping("/updateUser")//用户更新
    public String updateSanGuo(SanGuoTK sanGuoTK){
        System.out.println(sanGuoTK);
        sanGuoMapperTK.upDateSanGuo(sanGuoTK);
        return "redirect:/admin";
    }

    @RequestMapping("/selectById")
    public String selectSanGuoById(Model model,SanGuoTK sanGuoTK,
                                   @RequestParam(defaultValue = "1") Integer pageNo,
                                   @RequestParam(defaultValue = "3") Integer pageSize){
        //1.分页查询
        PageHelper.startPage(pageNo,pageSize);
        List<SanGuoTK> sanGuoTKS = sanGuoMapperTK.selectSanGuoByTd(sanGuoTK.getId());
        PageInfo<SanGuoTK> pageInfo = new PageInfo<>(sanGuoTKS);
        //2.存进model
        model.addAttribute("pageInfo",pageInfo);
        //3.跳转到users下的show页面
        return "admin/user/showuser";
    }


    @RequestMapping("/selectByName")
    public String selectSanGuoByName(Model model,SanGuoTK sanGuoTK,
                                     @RequestParam(defaultValue = "1") Integer pageNo,
                                     @RequestParam(defaultValue = "3") Integer pageSize){
        //1.分页查询
        PageHelper.startPage(pageNo,pageSize);
        List<SanGuoTK> sanGuoTKS = sanGuoMapperTK.selectSanGuoByName(sanGuoTK.getName());
        PageInfo<SanGuoTK> pageInfo = new PageInfo<>(sanGuoTKS);
        //2.存进model
        model.addAttribute("pageInfo",pageInfo);
        //3.跳转到users下的show页面
        return "admin/user/showuser";
    }

}

实体类部分

@Data
@Table(name="sanguo")
public class SanGuoTK {
    @Id//主键
    @KeySql(useGeneratedKeys = true)//表示在insert语句中，可将自动生成的主键id返回
    private Integer id;
    private String name;
    private String password;
    private String type;
    @DateTimeFormat(pattern="yyyy-MM-dd")
    private Date birth;
    @Column(name = "headImg")
    private String headImg;
    private String sex;

}

这里的生日是data类型的数据，从前端获取的是String类型，需要注释才可以使用
table后面是数据库表名用于存放实体类的数据

HTML部分

ADMIN

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>admin</title>
    <link rel="stylesheet" th:href="@{/amazeui/assets/css/amazeui.css}"/>
    <script  th:src="@{/amazeui/assets/js/jquery.min.js}"></script>
    <script  th:src="@{/amazeui/assets/js/amazeui.js}"></script>
    <script  th:src="@{/amazeui/pagination/amazeui-pagination.js}"></script>

</head>
<body>

<!-- 链接触发器， href 属性为目标元素 ID -->
<a href="#doc-oc-demo1" data-am-offcanvas>菜单</a>

<!-- 侧边栏内容 -->
<div id="doc-oc-demo1" class="am-offcanvas">
    <div class="am-offcanvas-bar">
        <div class="am-offcanvas-content">
            <p><a href="javascript:void(0)" onclick="showUser(1)">个人信息维护</a></p>
            <p><a href="javascript:void(0)" onclick="showHouse(2)">个人房源维护</a></p>
            <p><a href="javascript:void(0)" onclick="showHouse(1)">个人房源维护</a></p>
        </div>
    </div>
</div>

<div id="admin-content"></div>

</body>
<script>
    /*自动出现菜单*/
    $('#doc-oc-demo1').offCanvas({effect: 'push'});
/*无参用//包围有就像下面*/
    function showUser(pageNo) {
        $("#admin-content").load("/user/showuser?pageNo="+pageNo)
    }
    function showHouse(pageNo) {
        $("#admin-content").load("/house/show?pageNo="+pageNo)
    }
</script>
</html>

上面是完整的admin.HTML文件下面不展示完全，使用了前端框架这里附上链接http://amazeui.shopxo.net/

SHOWUSER

<body>
<h1 style="text-align: center">用户信息表</h1>
<p><a th:href="@{'/user/add_user'}">添加</a></p>
<form class="am-form am-form-horizontal" th:action="@{/user/selectById}" th:method="post">
    <div class="am-form-group">
        <label for="doc-ipt-pwd-9" class="am-u-sm-2 am-form-label">按id查询</label>
        <div class="am-u-sm-10">
            <input type="text" th:name="id" id="doc-ipt-pwd-9" placeholder="输入编号">
        </div>
    </div>
    <div class="am-form-group">
        <div class="am-u-sm-10 am-u-sm-offset-2">
            <button type="submit" class="am-btn am-btn-default">查询</button>
        </div>
    </div>
</form>

<form class="am-form am-form-horizontal" th:action="@{/user/selectByName}" th:method="post">
    <div class="am-form-group">
        <label for="doc-ipt-pwd-9" class="am-u-sm-2 am-form-label">按姓名查询</label>
        <div class="am-u-sm-10">
            <input type="text" th:name="name" id="doc-ipt-pwd-1" placeholder="输入姓名">
        </div>
    </div>
    <div class="am-form-group">
        <div class="am-u-sm-10 am-u-sm-offset-2">
            <button type="submit" class="am-btn am-btn-default">查询</button>
        </div>
    </div>
</form>
<!--相关属性见 http://amazeui.shopxo.net/css/grid/ -->
<div class="am-g">
    <div class="am-u-lg-8 am-u-lg-centered">
        <!--相关属性见 http://amazeui.shopxo.net/css/table/ -->
        <table class="am-table am-table-bordered am-table-striped  am-table-hover am-table-centered">
            <tr>
                <th>编号</th>
                <th>姓名</th>
                <th>密码</th>
                <th>生日</th>
                <th>头像</th>
                <th>等级</th>
                <th>修改</th>
                <th>删除</th>
            </tr>
            <tr th:each="sanGuoTKS:${pageInfo.list}">
                <td th:text="${sanGuoTKS.id}"></td>
                <td th:text="${sanGuoTKS.name}"></td>
                <td th:text="${sanGuoTKS.password}"></td>
                <td th:text="${sanGuoTKS.birth}"></td>
                <td><img th:src="${sanGuoTKS.headImg}" th:width="100px" th:height="100px"></td>
                <td th:text="${sanGuoTKS.type}"></td>
                <td ><a th:href="@{'user/update/'+${sanGuoTKS.id}}">修改</a></td>
                <td ><a th:href="@{'/delete?id='+${sanGuoTKS.id}}">删除</a></td>
            </tr>
        </table>
    </div>
</div>

<!-- 分页位标  区域 http://amazeui.shopxo.net/css/pagination/  -->

<div class="am-container">
    <ul class="am-pagination am-pagination-centered">
    </ul>
</div>

<input type="hidden" id="pages"   th:value="${pageInfo.pages}" />
<input type="hidden" id="pageNum" th:value="${pageInfo.pageNum}" />
</body>
<script>
    var pages=$("#pages").val();
    var pageNum=$("#pageNum").val();

    var pagination = new Pagination({
        wrap: $('.am-pagination'),//存放分页内容
        count: parseInt(pages),//总页数
        current: parseInt(pageNum),//当前页
        prevText: '上一页', // prev 按钮的文本内容
        nextText: '下一页', // next 按钮的文本内容
        callback: function (current) { // 每一个页数按钮的回调事件
            console.log(current)
            $("#admin-content").load("/user/showuser?pageNo="+current);
           // location.href="/user/showuser?pageNo="+current;
        }
    });

</script>

UPDATE_USER

<body>
<div><h3><a th:href="@{/admin}">返回</a></h3></div>

<form class="am-form am-form-horizontal" th:action="@{/user/updateUser}" th:method="post">
    <div class="am-form-group">
        <label for="doc-ipt-pwd-5" class="am-u-sm-2 am-form-label"></label>
        <div class="am-u-sm-10">
            <input type="hidden" th:name="id" id="doc-ipt-pwd-5" th:field="${sanGuoTK.id}" placeholder="用户编号">
        </div>
    </div>
    <div class="am-form-group">
        <label for="doc-ipt-pwd-6" class="am-u-sm-2 am-form-label">姓名</label>
        <div class="am-u-sm-10">
            <input type="text" th:name="name" id="doc-ipt-pwd-6" th:field="${sanGuoTK.name}" placeholder="用户姓名">
        </div>
    </div>
    <div class="am-form-group">
        <label for="doc-ipt-pwd-7" class="am-u-sm-2 am-form-label">密码</label>
        <div class="am-u-sm-10">
            <input type="text" th:field="${sanGuoTK.password}" th:name="password" id="doc-ipt-pwd-7" placeholder="用户密码">
        </div>
    </div>
    <div class="am-form-group">
        <label for="doc-select-1" class="am-u-sm-2 am-form-label">等级</label>
        <div class="am-u-sm-10">
            <select id="doc-select-1" th:name="type" th:value="${sanGuoTK.type}">
                <option th:value="群主" th:text="群主" ></option>
                <option th:value="群员" th:text="群员" ></option>
            </select>
            <span class="am-form-caret"></span>
        </div>
    </div>

    <div class="am-form-group">
        <label for="doc-ipt-pwd-7" class="am-u-sm-2 am-form-label">出生日期</label>
        <div class="am-u-sm-10">
            <input type="text" th:name="birth" id="doc-ipt-pwd-1" th:field="${sanGuoTK.birth}" placeholder="按0000-00-00格式输入">
        </div>
    </div>
    <div class="am-form-group">
        <label for="doc-select-1" class="am-u-sm-2 am-form-label">性别</label>
        <div class="am-u-sm-10">
            <select id="doc-select-2" th:name="sex" >
                <option th:value="男" th:text="男" ></option>
                <option th:value="女" th:text="女" ></option>
            </select>
            <span class="am-form-caret"></span>
        </div>
    </div>
    <div class="am-form-group">
        <label for="doc-ipt-pwd-9" class="am-u-sm-2 am-form-label">图片</label>
        <div class="am-u-sm-10">
            <input type="text" th:name="headImg" id="doc-ipt-pwd-9" th:field="${sanGuoTK.headImg}" placeholder="图片路径">
        </div>
    </div>
    <div class="am-form-group">
        <div class="am-u-sm-10 am-u-sm-offset-2">
            <button type="submit" class="am-btn am-btn-default">提交用户信息</button>
        </div>
    </div>
</form>
</body>

ADD_USER

<div><h3><a th:href="@{/admin}">返回</a></h3></div>

<form class="am-form am-form-horizontal" th:action="@{/user/add}" th:method="post">
    <div class="am-form-group">
        <label for="doc-ipt-pwd-5" class="am-u-sm-2 am-form-label">编号</label>
        <div class="am-u-sm-10">
            <input type="text" th:name="id" id="doc-ipt-pwd-5" placeholder="用户编号">
        </div>
    </div>
    <div class="am-form-group">
        <label for="doc-ipt-pwd-6" class="am-u-sm-2 am-form-label">姓名</label>
        <div class="am-u-sm-10">
            <input type="text" th:name="name" id="doc-ipt-pwd-6" placeholder="用户姓名">
        </div>
    </div>
    <div class="am-form-group">
        <label for="doc-ipt-pwd-7" class="am-u-sm-2 am-form-label">密码</label>
        <div class="am-u-sm-10">
            <input type="text" th:name="password" id="doc-ipt-pwd-7" placeholder="用户密码">
        </div>
    </div>
    <div class="am-form-group">
        <label for="doc-select-1" class="am-u-sm-2 am-form-label">等级</label>
        <div class="am-u-sm-10">
            <select id="doc-select-1" th:name="type">
                <option th:value="群主" th:text="群主" ></option>
                <option th:value="群员" th:text="群员" ></option>
            </select>
            <span class="am-form-caret"></span>
        </div>
    </div>

    <div class="am-form-group">
        <label for="doc-ipt-pwd-7" class="am-u-sm-2 am-form-label">出生日期</label>
        <div class="am-u-sm-10">
            <input type="text" th:name="birth" id="doc-ipt-pwd-1" placeholder="按0000-00-00格式输入">
        </div>
    </div>
    <div class="am-form-group">
        <label for="doc-select-1" class="am-u-sm-2 am-form-label">性别</label>
        <div class="am-u-sm-10">
            <select id="doc-select-2" th:name="sex">
                <option th:value="男" th:text="男" ></option>
                <option th:value="女" th:text="女" ></option>
            </select>
            <span class="am-form-caret"></span>
        </div>
    </div>
    <div class="am-form-group">
        <label for="doc-ipt-pwd-9" class="am-u-sm-2 am-form-label">图片</label>
        <div class="am-u-sm-10">
            <input type="text" th:name="headImg" id="doc-ipt-pwd-9" placeholder="图片路径">
        </div>
    </div>
    <div class="am-form-group">
        <div class="am-u-sm-10 am-u-sm-offset-2">
            <button type="submit" class="am-btn am-btn-default">提交用户信息</button>
        </div>
    </div>
</form>

使用了较多的前端框架内容导致html文件过于臃肿
这是项目的结构图有一些在创建项目时会自动生成