SpringBoot 系列教程(五十八):SpringBoot整合Mybatis+BootStrap增删改查XML版

一. 前言

在前面的SpringBoot学习过程中,我写过一篇通过SpringBoot整合Mybatis,做一个简单增删改查功能,Mybatis的的SQL语句使用注解形式:SpringBoot 系列教程(三十):SpringBoot整合Mybatis注解版增删改查,今天准备来出一个SpringBoot整合Mybatis增删改查XML版的,Mybatis的的SQL语句使用XML配置的形式,毕竟注解形式适合单表查询和一些简单查询,复杂的查询功能还是在XML里面写,统一管理比较好,看起来也比较舒服,易读;

二.准备工作

目标: 本次目标我们以User(用户)来做例子演示

2.1 创建SpringBoot项目

1.引入pom依赖:

<?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.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.thinkingcao</groupId>
    <artifactId>springboot-mybatis-xml</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot-mybatis-xml</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <!--<scope>runtime</scope>-->
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.29</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- spring-boot-devtools热启动依赖包 start-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
            <scope>runtime</scope>
        </dependency>
        <!-- spring-boot-devtools热启动依赖包 end-->

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>

            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <!--fork :  如果没有该项配置,肯呢个devtools不会起作用,即应用不会restart   这个要手动加进去 -->
                    <fork>true</fork>
                </configuration>
            </plugin>
        </plugins>

    </build>

</project>

2. 项目目录完整截图

2.2 创建数据库表mybatis-xml

1. SQL脚本

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL COMMENT '主键Id',
  `name` varchar(200) DEFAULT NULL COMMENT '姓名',
  `gender` varchar(200) DEFAULT NULL COMMENT '性别',
  `age` int(10) DEFAULT NULL COMMENT '年龄',
  `address` varchar(200) DEFAULT NULL COMMENT '地址',
  `qq` varchar(100) DEFAULT NULL COMMENT 'QQ',
  `email` varchar(200) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '曹某人', '男', '22', '上海', '617271939', '617271839@qq.com');
INSERT INTO `user` VALUES ('2', '曹文哒哒哒哒哒', '女', '100', '上海', '129900555', '129900555@qq.com');
INSERT INTO `user` VALUES ('3', '曹曹', '男', '24', '上海', '289087899', '289087899@qq.com');
INSERT INTO `user` VALUES ('6', '李四', 'option1', '27', '上海市徐汇区徐家汇街道', '78945248', '78945248@qq.com');
INSERT INTO `user` VALUES ('8', '张三', 'option2', '25', '上海市徐汇区徐家汇', '137982901', '137982901@qq.com');
SET FOREIGN_KEY_CHECKS=1;

2. 表结构

2.3 配置application.yml

 1. application.yml配置

#server端口配置
server:
  port: 8081

#数据库连接配置
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mybatis-xml?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
    username: root
    password: 123456
    #数据源配置
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver


  #HTML配置
  thymeleaf:
    prefix: classpath:/templates/
    suffix: .html
  #热部署配置
  devtools:
    livereload:
      enabled: true  #是否支持livereload
    restart:
      enabled: true  #是否支持热部署


#Mybatis配置
mybatis:
  config-location: classpath:config/mybatis-config.xml  #mybatis配置文件位置
  mapper-locations: classpath:mapper/*.xml       #mapper.xml 文件地址
  type-aliases-package: com.thinkingcao.springboot.entity #实体类别名

#日志配置
logging:
  level:
    com.thinkingcao.springboot.mapper: debug   #项目日志配置
    org.springframework.web: DEBUG   #Spring框架日志配置


 2. application.yml截图

2.4 Mybatis相关配置

1. 配置mybatis-config.xml

在SpringBoot的resources目录下新建config文件夹,用于存放Mybatis的全局配置文件mybatis-config.xml

 mybatis-cnfig.xml如下:

<?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>
        <typeAlias alias="Integer" type="java.lang.Integer" />
        <typeAlias alias="Long" type="java.lang.Long" />
        <typeAlias alias="HashMap" type="java.util.HashMap" />
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
        <typeAlias alias="ArrayList" type="java.util.ArrayList" />
        <typeAlias alias="LinkedList" type="java.util.LinkedList" />
    </typeAliases>
</configuration>

2. 配置mybatis的mapper.xml文件存放

在SpringBoot的resources目录下新建mapper文件夹,用于存放Mybatis的sql语句管理文件*mapper.xml

UserMapper.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.thinkingcao.springboot.mapper.UserMapper">

    <!--查询所有用户-->
    <select id="getAllUser" resultType="User">
        select * from user
    </select>

    <!--添加用户-->
    <insert id="InsertUser" parameterType="User">
        insert into
        user(
          id,
          name,
          gender,
          age,
          address,
          qq,
          email)
        values(
          #{id,jdbcType=INTEGER},
          #{name,jdbcType=VARCHAR},
          #{gender,jdbcType=VARCHAR},
          #{age,jdbcType=INTEGER},
          #{address,jdbcType=VARCHAR},
          #{qq,jdbcType=VARCHAR},
          #{email,jdbcType=VARCHAR}
          )
    </insert>

    <!--删除用户-->
    <delete id="delUserById" parameterType="java.lang.Integer">
       delete from user where id=#{id}
    </delete>

    <!--根据id查询用户-->
    <select id="getUserById" resultType="User">
        select * from user where id=#{id}
    </select>

    <!--更改用户信息-->
    <update id="UpdateUser" parameterType="User">
        update user set name=#{name},gender=#{gender},age=#{age},address=#{address},qq=#{qq},email=#{email} where id=#{id}
    </update>

</mapper>

2.5 User实体类配置

package com.thinkingcao.springboot.entity;

import lombok.Data;

/**
 * @desc: User实体类
 * @author: cao_wencao
 * @date: 2019-11-14 17:32
 */
@Data
public class User {
        private Integer id;
        private String name;
        private String gender;
        private Integer age;
        private String address;
        private String qq;
        private String email;
}

2.6  UserMapper接口配置

package com.thinkingcao.springboot.mapper;

import com.thinkingcao.springboot.entity.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @desc: UserMapper持久层
 * @auth: cao_wencao
 * @date: 2019/11/14 17:34
 */
@Mapper
public interface UserMapper {

    //查询所有用户
    // @Select("select * from user")
    public List<User> getAllUser();

    //根据id查询单个用户
   // @Select("select * from user where id=#{id}")
    public User getUserById(Integer id);

    //根据id删除单个用户
   // @Delete("delete from user where id=#{id}")
    public int delUserById(Integer id);

    //更新单个用户
   // @Update("update user set gender=#{gender},age=#{age},address=#{address},qq=#{qq},email=#{email} where name=#{name}")
    public int UpdateUser(User user);

    //新增单个用户
    //@Options(useGeneratedKeys = true, keyProperty = "id")//是指定主键生成的并且主键是id
   // @Insert("insert into user(name,gender,age,address,qq,email) values (#{name},#{gender},#{age},#{address},#{qq},#{email})")
    public int InsertUser(User user);

}

2.7  UserService接口配置

package com.thinkingcao.springboot.service;

import com.thinkingcao.springboot.entity.User;
import com.thinkingcao.springboot.mapper.UserMapper;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 * @desc: UserService业务层
 * @auth: cao_wencao
 * @date: 2019/11/14 17:34
 */
@Service
public class UserService {
    @Resource
    private UserMapper userMapper;

    public List<User> userList(){
        return userMapper.getAllUser();
    }

    public int insert(User user){
        return userMapper.InsertUser(user);
    }

    public int delete(Integer id){
        return userMapper.delUserById(id);
    }

    public int update(User user){
        return userMapper.UpdateUser(user);
    }

    public User getById(Integer id){
        return userMapper.getUserById(id);
    }
}

2.8 UserController配置

package com.thinkingcao.springboot.controller;

import com.thinkingcao.springboot.entity.User;
import com.thinkingcao.springboot.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;

/**
 * @desc: UserController控制层
 * @auth: cao_wencao
 * @date: 2019/11/14 17:35
 */
@Controller
public class UserController {


    @Autowired
    private UserService userService;

    @RequestMapping("/index")
    public String index(Model model) {
        model.addAttribute("users", userService.userList());
        return "list";
    }

    /*查询用户列表*/
    @RequestMapping("/list")
    public String userList(Model model) {
        model.addAttribute("users", userService.userList());
        return "list";
    }

    /*删除用户*/
    @RequestMapping("/del")
    public String deleteUser(Integer id) {
        userService.delete(id);
        return "redirect:/list";
    }

    /*添加用户页面*/
    @RequestMapping("/add")
    public String addUser(ModelMap map) {
        map.addAttribute("user", new User());
        return "add";
    }

    /*更新用户页面*/
    @RequestMapping("/update")
    public String updateUser(Model model,Integer id) {
        User user =userService.getById(id);
        model.addAttribute("user", user);
        return "update";
    }

    /*添加完用户后重定向到list页面*/
    @RequestMapping("/saveI")
    public String saveI(@ModelAttribute User user) {
        userService.insert(user);
        return "redirect:/list";
    }

    /*更新完用户后重定向到list页面*/
    @RequestMapping("/saveU")
    public String saveU(@ModelAttribute User user) {
        userService.update(user);
        return "redirect:/list";
    }
}

2.9 启动类

package com.thinkingcao.springboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringbootMybatisXmlApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringbootMybatisXmlApplication.class, args);
    }

}

2.10 引入BootStrap的JS和CSS文件

引入如下图所示的JS和CSS文件,用户增删改查界面渲染

三、增删改查

1. 用户列表页面

  访问地址: http://127.0.0.1:8081/list

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>用户列表页面</title>
    <link href="../static/css/bootstrap.min.css" th:href="@{/css/bootstrap.min.css}" rel="stylesheet"/>
    <script src="../static/js/bootstrap.min.js" th:src="@{/js/bootstrap.min.js}"></script>
    <script src="../static/js/jquery.min.js" th:src="@{/js/jquery.min.js}"></script>


    <style>
        /*设置表格th标题居中显示*/
        .table th, .table td {
        text-align: center;
        vertical-align: middle!important;
        }
    </style>

</head>
<body>

<fieldset style="width:250px; margin:0px auto">
    <p>
    <h2>学生信息列表</h2>
    </p>
</fieldset>
<div class="table-responsive">
    <table class="table table-bordered" style="width: 70%;margin:0px auto">
        <thead>
        <tr class="success">
            <th><input type="checkbox" id="checkAll"/></th>
            <th style="font-weight: bold">编号</th>
            <th>姓名</th>
            <th>性别</th>
            <th>年龄</th>
            <th>地址</th>
            <th>QQ</th>
            <th>邮箱</th>
            <th>操作</th>
        </tr>
        </thead>

        <tbody id="mytbd">
        <tr th:each="user : ${users}">
            <th><input type="checkbox" id="checkAll"/></th>
            <td th:text="${user.id}"></td>
            <td th:text="${user.name}"></td>
            <td th:text="${user.gender}"></td>
            <td th:text="${user.age}"></td>
            <td th:text="${user.address}"></td>
            <td th:text="${user.qq}"></td>
            <td th:text="${user.email}"></td>
            <td>
                <a class="btn btn-info" th:href="@{/del(id=${user.id})}">删除</a>
                <a class="btn btn-info" th:href="@{/update(id=${user.id})}">修改</a>
                <a class="btn btn-info" th:href="@{/add}">添加</a>
            </td>
            <!--<td>-->
                <!--<ul class="dropdown-menu" role="menu">-->
                    <!--<li><a href="#">修改</a></li>-->
                    <!--<li><a href="#">删除</a></li>-->
                <!--</ul>-->
            <!--</td>-->
        </tr>
        </tbody>
    </table>
</div>
</body>
</html>

效果图

2. 用户添加页面

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>用户添加页面</title>
    <link href="../static/css/bootstrap.min.css" th:href="@{/css/bootstrap.min.css}" rel="stylesheet"/>
    <script src="../static/js/bootstrap.min.js" th:src="@{/js/bootstrap.min.js}"></script>
    <script src="../static/js/jquery.min.js" th:src="@{/js/jquery.min.js}"></script>
</head>
<body>

 <fieldset style="width:180px; margin:0px auto">
    <p>
    <h2>学生信息增加</h2>
    </p>
 </fieldset>

<form class="form-horizontal" th:action="@{/saveI}" th:object="${user}" method="post" style="width: 35%;margin:0px auto">
    <div class="form-group">
        <label for="id" class="col-sm-2 control-label">编号:</label>
        <div class="col-sm-10">
            <input type="number" class="form-control" th:field="*{id}" id="id" placeholder="请输入编号">
        </div>
    </div>
    <div class="form-group">
        <label for="name" class="col-sm-2 control-label">姓名:</label>
        <div class="col-sm-10">
            <input type="text" class="form-control" th:field="*{name}" id="name" placeholder="请输入姓名">
        </div>
    </div>
    <div class="form-group">
        <label for="name" class="col-sm-2 control-label">姓别:</label>
        <div class="col-sm-10">
            <label class="radio-inline">
                <input type="radio" name="gender" th:field="*{gender}" id="boy" value="男" checked>男
            </label>
            <label class="radio-inline">
                <input type="radio" name="gender" th:field="*{gender}" id="gril" value="女">女
            </label>
        </div>
    </div>
    <div class="form-group">
        <label for="age" class="col-sm-2 control-label">年龄:</label>
        <div class="col-sm-10">
            <input type="number" class="form-control" th:field="*{age}" id="age" placeholder="请输入年龄">
        </div>
    </div>
    <div class="form-group">
        <label for="address" class="col-sm-2 control-label">地址:</label>
        <div class="col-sm-10">
            <input type="text" class="form-control" th:field="*{address}" id="address" placeholder="请输入地址">
        </div>
    </div>
    <div class="form-group">
        <label for="qq" class="col-sm-2 control-label">QQ:</label>
        <div class="col-sm-10">
            <input type="text" class="form-control" th:field="*{qq}" id="qq" placeholder="请输入QQ">
        </div>
    </div>
    <div class="form-group">
        <label for="email" class="col-sm-2 control-label">邮箱:</label>
        <div class="col-sm-10">
            <input type="text" class="form-control input-sm" th:field="*{email}" id="email" placeholder="请输入邮箱">
        </div>
    </div>

    <div class="form-group">
        <div class="col-sm-offset-2 col-sm-10">
            <button type="submit" class="btn btn-info btn-lg btn-block">确定</button>
        </div>
    </div>


    <!--编号: <input type="number" name="id"><br>-->
    <!--姓名: <input type="text" id="name" name="name"><br>-->
    <!--性别: <input type="text" id="gender" name="gender"><br>-->
    <!--年龄: <input type="number" id="age" name="age"><br>-->
    <!--地址: <input type="text" id="address" name="address"><br>-->
    <!--QQ: <input type="text" id="qq" name="qq"><br>-->
    <!--邮箱: <input type="text" id="email" name="email"><br>-->
    <!--<input type="submit">-->
</form>
</body>
</html>

效果图

3. 用户更新页面

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>用户更新页面</title>
    <link href="../static/css/bootstrap.min.css" th:href="@{/css/bootstrap.min.css}" rel="stylesheet"/>
    <script src="../static/js/bootstrap.min.js" th:src="@{/js/bootstrap.min.js}"></script>
    <script src="../static/js/jquery.min.js" th:src="@{/js/jquery.min.js}"></script>
</head>
<body>
<!--<form th:action="@{/saveU}" th:object="${user}" method="post">-->
<!--编号: <input type="number" name="id" th:value="*{user.getId()}"><br>-->
<!--姓名: <input type="text" id="name" name="name" th:value="*{user.getName()}"><br>-->
<!--性别: <input type="text" id="gender" name="gender" th:value="*{user.getGender()}"><br>-->
<!--年龄: <input type="number" id="age" name="age" th:value="*{user.getAge()}"><br>-->
<!--地址: <input type="text" id="address" name="address" th:value="*{user.getAddress()}"><br>-->
<!--QQ: <input type="text" id="qq" name="qq" th:value="*{user.getQq()}"><br>-->
<!--邮箱: <input type="text" id="email" name="email" th:value="*{user.getEmail()}"><br>-->
<!--<input type="submit">-->
<!--</form>-->

<fieldset style="width:180px; margin:0px auto">
    <p>
    <h2>学生信息更新</h2>
    </p>
</fieldset>

<form class="form-horizontal" th:action="@{saveU}" method="post" style="width: 35%;margin:0px auto">
    <div class="form-group">
        <label for="id" class="col-sm-2 control-label">编号:</label>
        <div class="col-sm-10">
            <input type="number" class="form-control" th:field="*{user.id}" id="id" readonly="readonly">
        </div>
    </div>
    <div class="form-group">
        <label for="name" class="col-sm-2 control-label">姓名:</label>
        <div class="col-sm-10">
            <input type="text" class="form-control" th:field="*{user.name}" id="name" placeholder="请输入姓名">
        </div>
    </div>
    <div class="form-group">
        <label for="name" class="col-sm-2 control-label">姓别:</label>
        <div class="col-sm-10">
            <label class="radio-inline">
                <input type="radio" name="gender" th:field="*{user.gender}" id="boy" value="男" checked>男
            </label>
            <label class="radio-inline">
                <input type="radio" name="gender" th:field="*{user.gender}" id="gril" value="女">女
            </label>
        </div>
    </div>
    <div class="form-group">
        <label for="age" class="col-sm-2 control-label">年龄:</label>
        <div class="col-sm-10">
            <input type="number" class="form-control" th:field="*{user.age}" id="age" placeholder="请输入年龄">
        </div>
    </div>
    <div class="form-group">
        <label for="address" class="col-sm-2 control-label">地址:</label>
        <div class="col-sm-10">
            <input type="text" class="form-control" th:field="*{user.address}" id="address" placeholder="请输入地址">
        </div>
    </div>
    <div class="form-group">
        <label for="qq" class="col-sm-2 control-label">QQ:</label>
        <div class="col-sm-10">
            <input type="text" class="form-control" th:field="*{user.qq}" id="qq" placeholder="请输入QQ">
        </div>
    </div>
    <div class="form-group">
        <label for="email" class="col-sm-2 control-label">邮箱:</label>
        <div class="col-sm-10">
            <input type="text" class="form-control input-sm" th:field="*{user.email}" id="email" placeholder="请输入邮箱">
        </div>
    </div>

    <div class="form-group" style="text-align: center">
        <a th:href="@{/saveU}">
            <input class="btn btn-info" type="submit" value="提交">
        </a>
        <a href="update.html" th:href="@{/update}">
            <input class="btn btn-info" type="submit" value="重置">
        </a>
        <a href="list.html" th:href="@{/list}">
            <input class="btn btn-info" type="submit" value="返回">
        </a>
    </div>
</form>


</body>
</html>

效果图

 

四 项目主页效果

五 源码

源码已上传GitHub,欢迎Star:https://github.com/Thinkingcao/SpringBootLearning/tree/master/springboot-mybatis-xml

 

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客
应支付9.90元
点击重新获取
扫码支付

支付成功即可阅读