springboot和mybatis结合

目录

• 遇到的问题
  • 1 实体类日期格式化
  • 2 Mybatis JdbcType与Oracle、MySql数据类型对应列表
  • 3 thymeleaf模板引擎
  • 4 lombok @Accessors 链式访问
  • 5 mybatis添加返回id
  • 6 thymeleaf th:href 带多个参数
  • 7 Tymplate if
  • 8 Spring Boot 配置 Security 密码加密
  • 9 SQLNonTransientConnectionException: CLIENT_PLUGIN_AUTH is required
  • 10 数据库层面自动填充创建时间和更新时间
• springboot-mybatisDemo
  • 总体架构
  • 代码
    • 1 pom
    • 2 EquipmentController
    • 3 UserController
    • 4 Equipment
    • 5 User
    • 6 AdminWebConfig
    • 7 LoginInterceptor
    • 8 EquipmentMapper
    • 9 UserMapper
    • 10 EquipmentService
    • 11 UserService
    • 12 UserServiceImpl
    • 13 DemoApplication
    • 14 EquipmentMapper.xml
    • 15 UserMapper.xml
    • 16 listE.css
    • 17 styles.css
    • 18 equipmentInsert.html
    • 19 equipmentManage.html
    • 20 listE.html
    • 21 listU.html
    • 22 login.html
    • 23 userInsert.html
    • 24 userManage.html
    • 25 application.yml
    • 26 application-dev.yml

参考: https://blog.csdn.net/shengshenglalalala/article/details/100576106
如何将项目上传到git: https://www.cnblogs.com/shenwen/p/9149478.html
IntelliJ IDEA中关于GIT问题处理Cannot Run Git File not found:git.exe: https://blog.csdn.net/qq_38483094/article/details/103237599

遇到的问题

1 实体类日期格式化

https://www.cnblogs.com/mracale/p/9828346.html
pom依赖

        <!-- @DateTimeFormat-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.9.9</version>
        </dependency>
        <!--  @JsonFormat -->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
            <version>2.8.8</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.8.8</version>
        </dependency>
        <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-mapper-asl</artifactId>
            <version>1.9.13</version>
        </dependency>

实体类中加

    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm")//页面写入数据库时格式化
    @JsonFormat(pattern="yyyy-MM-dd HH:mm",timezone="GMT+8")//数据库导出页面时json格式化, timezone：是时间设置为东八区，避免时间在转换中有误差
    private Date e_updateTime;

时间完整格式

  yyyy-MM-dd HH:mm:ss SSS

2 Mybatis JdbcType与Oracle、MySql数据类型对应列表

jdbcType=TIMESTAMP
https://mybatis.org/mybatis-3/apidocs/reference/org/apache/ibatis/type/JdbcType.html

3 thymeleaf模板引擎

https://mp.weixin.qq.com/s/Uvv1q3iQn2IwAB1crHWS1g

4 lombok @Accessors 链式访问

@Accessors(chain=true) 链式访问 生成setter方法返回this（也就是返回的是对象），代替了默认的返回void。

https://www.jianshu.com/p/67a15b2e4a92

5 mybatis添加返回id

https://blog.csdn.net/qq_45085954/article/details/102781741
mybatis添加用户返回id
在添加用户时，常把一个表的id设为主键，自增，以至于再插入数据的时候，无法直接获取用户id，如果下面操作还需要用到id，那么还要通过查询来获取，浪费资源
selectKey标签

<insert id="insertUser" parameterType="com.zhj.domain.User">
	<!-- AFTER：此中语句在插入语句之后执行
         resultType=“int”: 此中语句执行后的返回类型是 int
         keyProperty="id": 此中语句返回值要 传递给当前方法参数中的id属性 (com.zhj.domain.User的id属性)
         select last_insert_id()：mysql特性语句，返回当前事务中，最近一次插入的数据的id-->        
    <selectKey resultType="int" keyProperty="id" order="AFTER">
        select last_insert_id()
    </selectKey>
    insert into t_user (name,gender,create_time)
    values(#{name},#{gender},#{createTime})
</insert>

6 thymeleaf th:href 带多个参数

http://www.yayihouse.com/yayishuwu/chapter/1989

<a th:href="@{/mobileSign/signDetails(id=${id},name=${name})}"></a>

7 Tymplate if

通过session获取用户权限来判断button是否隐藏

        <div class="col-xs-6" th:if= "${session.power == 1}">
            <form th:action="@{/toUser}"><button type="submit"  class="btn btn-primary">管理用户</button></form>
        </div>

8 Spring Boot 配置 Security 密码加密

https://www.cnblogs.com/loveer/p/11424821.html

9 SQLNonTransientConnectionException: CLIENT_PLUGIN_AUTH is required

https://blog.csdn.net/u012052268/article/details/99541669
mysql5.1版本太老,需要自定义mysql-connector-java和druid版本

10 数据库层面自动填充创建时间和更新时间

DBeaver:

1 添加缺省

缺省就是默认的意思

CURRENT_TIMESTAMP

2. 创建触发器

CREATE TRIGGER trigger_update
before update
ON `user` FOR EACH ROW
begin
	set new.create_time = now(), new.update_time = now();
end

springboot-mybatisDemo

总体架构

---

代码

1 pom

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.yu</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>


    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!--    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>
        <!--mybatis相关-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>
        <!--mysql相关-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.3</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>
            <version>1.18.22</version>
        </dependency>
        <!-- Spring Security-->
<!--        <dependency>-->
<!--            <groupId>org.springframework.boot</groupId>-->
<!--            <artifactId>spring-boot-starter-security</artifactId>-->
<!--        </dependency>-->
    </dependencies>

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

2 EquipmentController

@Controller
@RequestMapping("/equipment")
public class EquipmentController {
    @Autowired
    private EquipmentService equipmentService;

//    @RequestMapping("/index")
//    public String list(Model model){
//        List<Equipment> equipments = equipmentService.getAllEquipment();
//        model.addAttribute("equips", equipments);
//        return "emp/list";
//    }

    /**
     * 根据id查询设备
     */
    @GetMapping("/getEquipment")
    public String GetEquipment(Model model, @RequestParam Integer eid){
        System.out.println("要查询的设备id为"+eid);
        Equipment equip = equipmentService.getEquipmentInfo(eid);
        System.out.println("查询成功");
        System.out.println(equip);

        model.addAttribute("equipments",equip);
        return "listE";
    }

    /**
     * to新增设备
     * @return
     */
    @RequestMapping("/toSaveEquipment")
    public String toInsertManage() {
        return "equipmentInsert";
    }

    /**
     * 新增设备
     */
    @GetMapping("/saveEquipment")
    public String saveEquipment(Equipment equipment, HttpSession session){
        String username = (String) session.getAttribute("username");
        System.out.println("已获取到当前用户名"+username);
        equipment.setE_updateTime(new Date());
        equipment.setU_name(username);
        System.out.println("准备添加设备:");
        System.out.println(equipment);
        equipmentService.saveEquipment(equipment);
        int e_id = equipment.getE_id();
        System.out.println("添加设备成功,设备id为"+e_id);
        return "redirect:/equipment/getEquipment?eid="+e_id;
    }

    /**
     * 根据id删除设备
     */
    @GetMapping("/deleteEquipment")
    public String delEquipment(HttpServletRequest request){
        Integer eid = Integer.valueOf(request.getParameter("eid"));
        System.out.println("要删除设备的id为"+eid);
        equipmentService.deleteEquipmentById(eid);
        return "redirect:/equipment/getAllEquipment";
    }

    /**
     * To更新设备
     * @param eid
     * @param request
     * @return
     */
    @GetMapping("/toUpdateEquipment")
    public String toUpdateEquipment(Integer eid,String e_name,HttpServletRequest request) {
        System.out.println("要更新的设备id为"+eid);
        request.setAttribute("eid",eid);
        request.setAttribute("e_name",e_name);
        return "equipmentManage";
    }

    /**
     * 根据id更新设备
     */
    @GetMapping("/updateEquipment")
    public String updateEquipment(Equipment equipment){
        equipment.setE_updateTime(new Date());
        System.out.println("准备更新设备:");
        System.out.println(equipment);
        equipmentService.updateEquipment(equipment);
        int id = equipment.getE_id();
        System.out.println("更新设备成功,设备id为"+id);
        return "redirect:/equipment/getEquipment?eid="+id;
    }

    /**
     * 查询所有设备
     */
    @RequestMapping("/getAllEquipment")
    public String getAllEquipment(Model model,HttpSession session){
        int power = (Integer) session.getAttribute("power");
        if(power == 1){
            System.out.println("您为管理员!!");
        }
        List<Equipment> equipments = equipmentService.getAllEquipment();
        model.addAttribute("equipments",equipments);
        System.out.println("已获取到所有设备");
        return "listE";
    }


}

3 UserController

@Controller
public class UserController {
    @Autowired
    private UserServiceImpl userService;

    @RequestMapping("/")
    public String hello(){
        return "login";
    }
    @RequestMapping("/index")
    public String index(){
        return "login";
    }

    @RequestMapping("/login")
    public String login(Model model, User user, HttpSession session){
        User user1 = userService.userLogin(user.getU_name());
        if(user1!=null){//如果用户名存在
            model.addAttribute("name",user.getU_name());
            if(user1.getU_password().equals(user.getU_password())){
                System.out.println("登录成功");
                session.setAttribute("username",user1.getU_name());
                session.setAttribute("power",user1.getU_power());
                return "redirect:/equipment/getAllEquipment";
            }else{
                model.addAttribute("data","密码不正确");
                return "login";
            }


        }else{
            System.out.println("用户名不存在");
            model.addAttribute("data", "用户名不存在");
            return "login";
        }
    }


    @GetMapping("/getAllUser")
    public String getAllUser(Model model){

        List<User> users = userService.getAllUser();
        model.addAttribute("users",users);
        System.out.println("已获取到所有用户");
        return "listU";
    }

    /**
     * 根据name查询用户
     * @param model
     * @param u_name
     * @return
     */
    @GetMapping("/getUserByName")
    public String getUserByName(Model model, @RequestParam String u_name){
        System.out.println("要查询的用户name为"+u_name);
        User user = userService.userLogin(u_name);
        System.out.println("查询成功");
        System.out.println(user);

        model.addAttribute("users",user);
        return "listU";
    }

    /**
     * to修改用户信息
     * @return
     */
    @GetMapping("/toUpdateUser")
    public String toUpdateUser(User user, HttpServletRequest request){
        System.out.println("要更新的用户name为"+user.getU_name());
        request.setAttribute("u_name",user.getU_name());
        request.setAttribute("u_password",user.getU_password());
        request.setAttribute("u_power",user.getU_power());
        return "userManage";
    }

    /**
     * 更新用户
     * @param user
     * @param request
     * @return
     */
    @GetMapping("/updateUser")
    public String updateUser(User user,HttpServletRequest request){
        System.out.println("准备更新用户");
        System.out.println(user);
        userService.updateUser(user);
        String name = user.getU_name();
        System.out.println("更新用户成功,用户name为"+name);
//        request.setAttribute("u_name",name);
        return "forward:/getUserByName";
    }

    /**
     * 根据name删除用户
     * @param u_name
     * @return
     */
    @GetMapping("/deleteUser")
    public String deleteUser(@RequestParam String u_name){
        System.out.println("要删除的用户name为" + u_name);
        userService.deleteUserByName(u_name);
        System.out.println("删除"+u_name+"成功");
        return "redirect:/getAllUser";
    }

    /**
     * toSaveUser
     * @return
     */
    @GetMapping("/toSaveUser")
    public String toSaveUser(){
        return "userInsert";
    }

    /**
     * 新增用户
     * @param user
     * @return
     */
    @GetMapping("/saveUser")
    public String saveUser(User user){
        System.out.println("要新增的用户为");
        System.out.println(user);
        userService.saveUser(user);
        System.out.println("新增用户成功");
        return "redirect:/getAllUser";
    }
}

4 Equipment

@Data
@ToString
@EqualsAndHashCode
@AllArgsConstructor
@NoArgsConstructor
public class Equipment {
    private Integer e_id;
    private String e_name;
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm")//页面写入数据库时格式化
    @JsonFormat(pattern="yyyy-MM-dd HH:mm",timezone="GMT+8")//数据库导出页面时json格式化
    private Date e_updateTime;
    private String u_name;
}

5 User

@Data
@ToString
@EqualsAndHashCode
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private String u_name;
    private String u_password;
    private Integer u_power;
}

6 AdminWebConfig

/**
 * 1、编写一个拦截器实现 HandlerInterceptor 接口
 * 2、拦截器注册到容器中（实现 WebMvcConfigurer 的 addInterceptors 方法）
 * 3、指定拦截规则（如果是拦截所有，静态资源也会被拦截）
 */
@Configuration
public class AdminWebConfig implements WebMvcConfigurer {
    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor (new LoginInterceptor ())
                .addPathPatterns ("/**")  // 所有请求都被拦截包括静态资源
                .excludePathPatterns ("/","/login","/index","/css/**","/img/**","/js/**"); // 放行的请求

    }
}

7 LoginInterceptor

**
 * 登录检查：
 * 1、配置好拦截器要拦截哪些请求；
 * 2、把这些配置放在容器中。
 */
@Slf4j
public class LoginInterceptor implements HandlerInterceptor {
    //目标方法执行之前

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        String requestURI = request.getRequestURI();
        log.info("preHandle 拦截的请求路径是 {}",requestURI);

        //登录检查逻辑
        HttpSession session = request.getSession();
        Object power = session.getAttribute("power");
        if(power != null){
            //放行
            log.info("放行");
            return true;
        }

        //未登录,拦截请求,然后跳转到登录页
        request.setAttribute("data","请先登录");
        request.getRequestDispatcher("/").forward(request,response);
        log.info("没有登录,已拦截");
        return false;
    }

}

8 EquipmentMapper

@Repository
//@Mapper
public interface EquipmentMapper {
    /**
     * 根据id查询设备
     * http://localhost:8080/equipment/getEquipment/1
     * @param id
     * @return
     */
    Equipment getEquipmentInfo(int id);

    /**
     * 新增设备
     * @param equipment
     * @return
     */
    int saveEquipment(Equipment equipment);

    /**
     * 根据id删除设备
     * @param id
     * @return
     */
    int deleteEquipmentById(int id);

    /**
     * 根据id更新设备
     * @param equipment
     * @return
     */
    int updateEquipment(Equipment equipment);

    /**
     * 查询所有设备
     * @return
     */
    List<Equipment> getAllEquipment();
}

9 UserMapper

@Mapper
@Repository
public interface UserMapper {
    /**
     *  根据u_name查询用户
     * @param u_name
     * @return
     */
    User userLogin(String u_name);

    /**
     * 获取所有用户
     * @return 用户User
     */
    List<User> getAllUser();

    /**
     * 新增用户
     * @param user
     * @return
     */
    int saveUser(User user);

    /**
     * 根据name删除用户
     * @param name
     * @return
     */
    int deleteUserByName(String name);

    /**
     * 根据name更新用户
     * @param user
     * @return
     */
    int updateUser(User user);
}

10 EquipmentService

@Service
public class EquipmentService {
    @Autowired
    private EquipmentMapper equipmentMapper;

    /**
     * 根据id查询设备
     * @param id
     * @return
     */
    public Equipment getEquipmentInfo(int id){
        return equipmentMapper.getEquipmentInfo(id);
    }


    /**
     * 新增设备
     * @param equipment
     * @return
     */
    public int saveEquipment(Equipment equipment){

        return equipmentMapper.saveEquipment(equipment);
    }

    /**
     * 根据id删除设备
     * @param id
     * @return
     */
    public int deleteEquipmentById(int id){
        return equipmentMapper.deleteEquipmentById(id);
    }

    /**
     * 根据id更新设备
     * @param equipment
     * @return
     */
    public int updateEquipment(Equipment equipment){
//        equipment.setE_updateTime(new Date());
        //更新设备时不能对“添加设备的用户名”进行设置
        equipment.setU_name(null);
        return equipmentMapper.updateEquipment(equipment);
    }

    /**
     * 查询所有设备
     * @return
     */
    public List<Equipment> getAllEquipment(){
        return equipmentMapper.getAllEquipment();
    }


}

11 UserService

public interface UserService {
    /**
     *  根据u_name查询用户
     * @param u_name
     * @return
     */
    User userLogin(String u_name);

    /**
     * 获取所有用户
     * @return 用户User
     */
    List<User> getAllUser();

    /**
     * 新增用户
     * @param user
     * @return
     */
    int saveUser(User user);

    /**
     * 根据name删除用户
     * @param name
     * @return
     */
    int deleteUserByName(String name);

    /**
     * 根据name更新用户
     * @param user
     * @return
     */
    int updateUser(User user);
}

12 UserServiceImpl

@Service
public class UserServiceImpl implements UserService{
    @Autowired
    private UserMapper userMapper;


    @Override
    public User userLogin(String u_name) {

        return userMapper.userLogin(u_name);
    }

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

    @Override
    public int saveUser(User user) {
        return userMapper.saveUser(user);
    }

    @Override
    public int deleteUserByName(String name) {
        return userMapper.deleteUserByName(name);
    }

    @Override
    public int updateUser(User user) {
        return userMapper.updateUser(user);
    }
}

13 DemoApplication

@MapperScan("com.yu.demo.mapper")
@SpringBootApplication
public class DemoApplication {

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

}

14 EquipmentMapper.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.yu.demo.mapper.EquipmentMapper">
    <resultMap id="BaseResultMap" type="com.yu.demo.entity.Equipment">
        <result column="e_id" jdbcType="INTEGER" property="e_id"/>
        <result column="e_name" jdbcType="VARCHAR" property="e_name"/>
        <result column="e_updateTime" jdbcType="TIMESTAMP" property="e_updateTime"/>
        <result column="e_user" jdbcType="VARCHAR" property="e_user"/>
    </resultMap>
    <!--    根据id查询设备-->
    <select id="getEquipmentInfo" resultType="com.yu.demo.entity.Equipment">
        select * from equipments where e_id = #{id}
    </select>

    <!--    新增设备-->
    <insert id="saveEquipment" parameterType="com.yu.demo.entity.Equipment">
        <!-- AFTER：此中语句在插入语句之后执行
         resultType=“int”: 此中语句执行后的返回类型是 int
         keyProperty="id": 此中语句返回值要 传递给当前方法参数中的id属性 (com.zhj.domain.User的id属性)
         select last_insert_id()：mysql特性语句，返回当前事务中，最近一次插入的数据的id-->
        <selectKey resultType="int" keyProperty="e_id" order="AFTER">
            select last_insert_id()
        </selectKey>

        insert into equipments
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="e_id != null">
                e_id,
            </if>
            <if test="e_name != null">
                e_name,
            </if>
            <if test="e_updateTime != null">
                e_updateTime,
            </if>
            <if test="u_name != null">
                u_name
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="e_id != null">
                #{e_id,jdbcType=INTEGER},
            </if>
            <if test="e_name != null">
                #{e_name,jdbcType=VARCHAR},
            </if>
            <if test="e_updateTime != null">
                #{e_updateTime,jdbcType=TIMESTAMP},
            </if>
            <if test="u_name != null">
                #{u_name,jdbcType=VARCHAR}
            </if>
        </trim>
    </insert>

    <!--    根据id删除设备-->
    <delete id="deleteEquipmentById" parameterType="int">
        delete from equipments where e_id = #{id}
    </delete>

    <!--根据id更新设备-->
    <update id="updateEquipment" parameterType="com.yu.demo.entity.Equipment">
        update equipments
        <set>
            <if test="e_name != null">
                e_name = #{e_name,jdbcType=VARCHAR},
            </if>
            <if test="e_updateTime != null">
                e_updateTime = #{e_updateTime,jdbcType=TIMESTAMP},
            </if>
            <if test="u_name != null">
                u_name = #{u_name,jdbcType=VARCHAR}
            </if>
        </set>
        where e_id = #{e_id,jdbcType=INTEGER}
    </update>

    <!--    查询所有设备-->
    <select id="getAllEquipment" resultType="Equipment">
        select * from equipments
    </select>

</mapper>

15 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.yu.demo.mapper.UserMapper">

    <!--定义一个名为BaseResultMap的返回类型-->
    <resultMap id="UserResultMap" type="com.yu.demo.entity.User">
        <result column="u_name" property="u_name" jdbcType="VARCHAR"></result>
        <result column="u_password" property="u_password" jdbcType="VARCHAR"></result>
    </resultMap>

<!--    根据u_name查询用户-->
    <select id="userLogin" parameterType="java.lang.String" resultType="com.yu.demo.entity.User">
        select *
        from user
        where u_name = #{u_name,jdbcType=VARCHAR}
    </select>
<!--获取所有用户-->
    <select id="getAllUser" resultType="User">
        select *from user
    </select>


    <!--    新增用户-->
    <insert id="saveUser" parameterType="com.yu.demo.entity.User">
        <!-- AFTER：此中语句在插入语句之后执行
         resultType=“int”: 此中语句执行后的返回类型是 int
         keyProperty="id": 此中语句返回值要 传递给当前方法参数中的id属性 (com.zhj.domain.User的id属性)
         select last_insert_id()：mysql特性语句，返回当前事务中，最近一次插入的数据的id-->
        <selectKey resultType="String" keyProperty="u_name" order="AFTER">
            select last_insert_id()
        </selectKey>

        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="u_name != null">
                u_name,
            </if>
            <if test="u_password != null">
                u_password,
            </if>
            <if test="u_power != null">
                u_power
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="u_name != null">
                #{u_name,jdbcType=VARCHAR},
            </if>
            <if test="u_password != null">
                #{u_password,jdbcType=VARCHAR},
            </if>
            <if test="u_power != null">
                #{u_power,jdbcType=INTEGER}
            </if>
        </trim>
    </insert>

    <!--    根据name删除用户-->
    <delete id="deleteUserByName" parameterType="java.lang.String">
        delete from user where u_name = #{u_name}
    </delete>

    <!--根据name更新用户-->
    <update id="updateUser" parameterType="com.yu.demo.entity.User">
        update user
        <set>
            <if test="u_power != null">
                u_power = #{u_power,jdbcType=INTEGER},
            </if>
            <if test="u_password != null">
                u_password = #{u_password,jdbcType=VARCHAR},
            </if>
        </set>
        where u_name = #{u_name,jdbcType=VARCHAR}
    </update>
</mapper>

16 listE.css

*{
    margin: 0;
    padding: 0;
}

.nav1{
    height: 60px;
    line-height: 60px;
}
.nav1>div{
    height: 60px;
    line-height: 60px;
    margin: 0 7px;
}
.form-group{
    padding: 10px 0;
}
.nav1>div input{
    height: 40px;
}

.backhome{
    box-sizing: border-box;
    display: block;
    width:40px;
    height: 40px;
    padding: 10px 0;
}
.backhome>a{
    display: inline-block;
    width: 40px;
    height: 40px;
    background-size:100% 100%;
}
.home{
    background: url("../img/home.png") no-repeat;

}.user{
     background: url("../img/user.png") no-repeat;
 }

17 styles.css

body {
	font-family: 'Montserrat', sans-serif;
	text-rendering : optimizeLegibility;
	-webkit-font-smoothing : antialiased;
}


#login-bg.container-fluid {
	padding: 0;
	height: 100%;
	position: absolute;
}

/* Background image an color divs*/

.bg-img , .bg-color {
	min-width: 100%;
	vertical-align: top;
	padding: 0;
	margin-left: 0;
	height: 100%;
	background-color: #CAF0D3;
	display: inline-block;
	overflow: hidden;
}

.bg-color {
	margin-left: -5px;
}

.bg-img {
	background: url(/img/bg-image.jpeg) repeat;
	background-size: cover;
}

#login{
	padding-top: 10%;
	text-align: center;
	text-transform: uppercase;
}


.login {
	width: 90%;
	height: 500px;
	background-color: #fff;
	padding: 15px;
	padding-top: 30px;
}

.login h1 {
	margin-top: 30px;
	font-weight: bold;
	font-size: 60px;
	letter-spacing: 3px;
}

.login form {
	max-width: 420px;
	margin: 30px auto;
}

.login .btn {
	border-radius: 50px;
	text-transform: uppercase;
	font-weight: bold;
	letter-spacing: 2px;
	font-size: 20px;
	padding: 14px;
	background-color: #00B72E;
}

.form-group input {
	font-size: 20px;
	font-weight: lighter;
	border: none;
	background-color: #F0F0F0;
	color: #465347!important;
	padding: 26px 30px;
	border-radius: 50px;
	transition : 0.2s;
}




/* Form check styles*/

.form-check {
	padding: 0;
	text-align: left;
}

.form-check label {
	vertical-align: top;
	padding-top: 5px;
	padding-left: 5px;
	font-size: 15px;
	color: #606060;
	font-size: 14px;
}

.forgot-password {
	text-align: right;
	float: right;
	font-weight: bold;
}

.forgot-password a {
	color: #00B72E;
	opacity: 0.6;
}

.forgot-password a:hover {
	opacity: 1;
}


/* Switch styles */

.switch {
  position: relative;
  display: inline-block;
  width: 60px;
  height: 30px;
}

/* Hide default HTML checkbox */
.switch input {display:none;}

/* The slider */
.slider {
  position: absolute;
  cursor: pointer;
  top: 0;
  left: 0;
  right: 0;
  bottom: 0;
  background-color: #F0F0F0;
  -webkit-transition: .4s;
  transition: .4s;
  border-radius: 30px;
}

.slider:before {
  position: absolute;
  content: "";
  height: 22px;
  width: 22px;
  left: 4px;
  bottom: 4px;
  background-color: white;
  -webkit-transition: .4s;
  transition: .4s;
  border-radius: 50%;
}

input:checked + .slider {
  background-color: #00B72E;
}

input:focus + .slider {
  box-shadow: 0 0 1px #00B72E;
}

input:checked + .slider:before {
  -webkit-transform: translateX(30px);
  -ms-transform: translateX(30px);
  transform: translateX(30px);
}



/* Media queries */

@media(max-width: 500px) {
	.bg-img , .bg-color {
	min-width: 100%;
	height: 50%;
	margin: 0;
	}

	.forgot-password {
	text-align: right;
	float: left;
	padding: 20px 0;
	}


	#login {
		padding-top: 50px;
	}

}

18 equipmentInsert.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">

<head>
    <title>设备信息</title>
    <link rel="stylesheet" href="/css/bootstrap.min.css" type="text/css"/>
    <link rel="stylesheet" href="/css/listE.css" type="text/css"/>
    <script type="text/javascript" src="/js/jquery.js"></script>
    <script type="text/javascript" src="/js/bootstrap.js"></script>
</head>
<body>
<div id="body" style="width: 40%;margin: 0 auto">
    <h1>欢迎您! <span th:if="${session.username != null}" th:text="${session.username}"/></h1>
    <div class="row nav1">
        <div class="backhome">
            <a href="/equipment/getAllEquipment" class="home" title="返回设备列表"></a>
        </div>
        <div>
            <!-- <div class="col-xs-6">-->
            <form th:action="@{/equipment/getEquipment}" class="form-inline" role="form">
                <div class="form-group">
                    <input type="text" class="form-control" name="eid" placeholder="请输入设备id">
                </div>
                <button type="submit" class="btn btn-default">查询</button>
            </form>
        </div>


        <div class="col-xs-6" th:if= "${session.power == 1}">
            <form th:action="@{/getAllUser}">
                <button type="submit" class="btn btn-primary">管理用户</button>
            </form>
        </div>
    </div>


    <form th:action="@{/equipment/saveEquipment}" class="form-horizontal" role="form">
        <div class="form-group">
            <label for="e_name" class="col-sm-2 control-label">设备名称</label>
            <div class="col-sm-10">
                <input style="width: 40%" type="text" class="form-control" id="e_name" name="e_name" placeholder="请输入设备名称">
            </div>
        </div>
        <div class="form-group">
            <div class="col-sm-offset-2 col-sm-10">
                <button type="submit" class="btn btn-default">确认添加</button>
            </div>
        </div>
    </form>
</div>
</body>
</html>

19 equipmentManage.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">

<head>
    <title>设备信息</title>
    <link rel="stylesheet" href="/css/bootstrap.min.css" type="text/css"/>
    <link rel="stylesheet" href="/css/listE.css" type="text/css"/>
    <script type="text/javascript" src="/js/jquery.js"></script>
    <script type="text/javascript" src="/js/bootstrap.js"></script>
</head>
<body>
<div id="body" style="width: 40%;margin: 0 auto">
    <h1>欢迎您! <span th:if="${session.username != null}" th:text="${session.username}"/></h1>
    <div class="row nav1">
        <div class="backhome">
            <a href="/equipment/getAllEquipment" class="home" title="返回设备列表"></a>
        </div>
        <div>
            <!-- <div class="col-xs-6">-->
            <form th:action="@{/equipment/getEquipment}" class="form-inline" role="form">
                <div class="form-group">
                    <input type="text" class="form-control" name="eid" placeholder="请输入设备id">
                </div>
                <button type="submit" class="btn btn-default">查询</button>
            </form>
        </div>
        <div>
            <form th:action="@{/equipment/toSaveEquipment}">
                <button type="submit" class="btn btn-info">添加设备</button>
            </form>
        </div>

        <div class="col-xs-6" th:if= "${session.power == 1}">
            <form th:action="@{/getAllUser}">
                <button type="submit" class="btn btn-primary">管理用户</button>
            </form>
        </div>
    </div>

    <form th:action="@{/equipment/updateEquipment}" class="form-horizontal" role="form">
        <div class="form-group">
            <label for="eid" class="col-sm-2 control-label">设备编号</label>
            <div class="col-sm-10">
                <input readOnly style="width: 40%" type="text" class="form-control" id="eid" name="e_id" th:value="${eid}">
            </div>
        </div>
        <div class="form-group">
            <label for="e_name" class="col-sm-2 control-label">设备名称</label>
            <div class="col-sm-10">
                <input style="width: 40%" type="text" class="form-control" id="e_name" name="e_name" th:value="${e_name}" placeholder="请输入要修改的名称">
            </div>
        </div>
        <div class="form-group">
            <div class="col-sm-offset-2 col-sm-10">
                <button type="submit" class="btn btn-default">确认修改</button>
            </div>
        </div>
    </form>
</div>
</body>
</html>

20 listE.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>设备列表</title>
    <link rel="stylesheet" href="/css/bootstrap.min.css" type="text/css"/>
    <link rel="stylesheet" href="/css/listE.css" type="text/css"/>
    <script type="text/javascript" src="/js/jquery.js"></script>
    <script type="text/javascript" src="/js/bootstrap.js"></script>
</head>
<body>
<div id="body" style="width: 60%;margin: 0 auto">
    <h1>欢迎您! <span th:if="${session.username != null}" th:text="${session.username}"/></h1>
    <div class="row nav1">
        <div class="backhome">
            <a href="/equipment/getAllEquipment" class="home" title="返回设备列表"></a>
        </div>
        <div>
            <!-- <div class="col-xs-6">-->
            <form th:action="@{/equipment/getEquipment}" class="form-inline" role="form">
                <div class="form-group">
                    <input type="text" class="form-control" name="eid" placeholder="请输入设备id">
                </div>
                <button type="submit" class="btn btn-default">查询</button>
            </form>
        </div>
        <div>
            <form th:action="@{/equipment/toSaveEquipment}">
                <button type="submit" class="btn btn-info">添加设备</button>
            </form>
        </div>

        <div class="col-xs-6" th:if= "${session.power == 1}">
            <form th:action="@{/getAllUser}">
                <button type="submit" class="btn btn-primary">管理用户</button>
            </form>
        </div>
    </div>
    <table class="table table-bordered">
        <thead class="thead-dark">
        <tr style="text-align: center">
            <th scope="col">设备id</th>
            <th scope="col">设备名称</th>
            <th scope="col">设备更新时间</th>
            <th scope="col">添加设备的用户名</th>
            <th scope="col">操作</th>
        </tr>
        </thead>
        <tbody>
        <tr scope="row" th:each="equipment:${equipments}">
            <td th:text="${{equipment.getE_id}}">暂无数据</td>
            <td th:text="${{equipment.getE_name}}"></td>
            <td th:text="${{equipment.getE_updateTime}}"></td>
            <td th:text="${{equipment.getU_name()}}"></td>
            <td>
                <a th:href="@{/equipment/toUpdateEquipment(eid=${equipment.getE_id()},e_name=${equipment.getE_name()})}"
                   style="margin-right: 30px">更改</a>
                <a th:href="@{/equipment/deleteEquipment(eid=${equipment.getE_id()})}">删除</a>
            </td>
        </tr>
        </tbody>
    </table>
    <div id="page">
        <div id="page-button" style="float: right">
            <ul class="pagination">
                <li><a href="#">&laquo;</a></li>
                <li class="active"><a href="#" th:text=1></a></li>
                <li><a href="#">&raquo;</a></li>
            </ul>
        </div>
    </div>
</div>
</body>
</html>

21 listU.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>用户列表</title>
    <link rel="stylesheet" href="/css/bootstrap.min.css" type="text/css"/>
    <script type="text/javascript" src="/js/jquery.js"></script>
    <link rel="stylesheet" href="/css/listE.css" type="text/css"/>
    <script type="text/javascript" src="/js/bootstrap.js"></script>
</head>
<body>
<div id="body" style="width: 60%;margin: 0 auto">
    <h1>欢迎您!管理员 <span th:if="${session.username != null}" th:text="${session.username}"/></h1>
    <div class="row nav1">
        <div class="backhome">
            <a href="/equipment/getAllEquipment" class="home" title="返回设备列表"></a>
        </div>
        <div class="backhome">
            <a href="/getAllUser" class="user" title="返回用户管理列表"></a>
        </div>
        <div class="col-xs-6">
            <form th:action="@{/getUserByName}" class="form-inline" role="form">
                <div class="form-group">
                    <input type="text" class="form-control" name="u_name" placeholder="请输入用户name">
                </div>
                <button type="submit" class="btn btn-default">查询</button>
            </form>
        </div>
        <div class="col-xs-6">
            <form th:action="@{/toSaveUser}">
                <button type="submit" class="btn btn-info">添加用户</button>
            </form>
        </div>
    </div>
    <table class="table table-bordered">
        <thead class="thead-dark">
        <tr style="text-align: center">
            <th scope="col">用户名称</th>
            <th scope="col">用户密码</th>
            <th scope="col">用户权限</th>
            <th scope="col">操作</th>
        </tr>
        </thead>
        <tbody>
        <tr scope="row" th:each="user:${users}">
            <td th:text="${{user.getU_name()}}">暂无数据</td>
            <td th:text="${{user.getU_password()}}"></td>
            <td th:switch="${user.getU_power()}">
                <div th:case="1" style="display: inline">管理员</div>
                <div th:case="0" style="display: inline">普通用户</div>
            </td>
            <td>
                <a th:href="@{/toUpdateUser(u_name=${user.getU_name()},u_password=${user.getU_password()},u_power=${user.getU_power()})}"
                   style="margin-right: 30px">更改</a>
                <a th:href="@{/deleteUser(u_name=${user.getU_name()})}">删除</a>
            </td>
        </tr>
        </tbody>
    </table>
    <div id="page">
        <div id="page-button" style="float: right">
            <ul class="pagination">
                <li><a href="#">&laquo;</a></li>
                <li class="active"><a href="#" th:text=1></a></li>
                <li><a href="#">&raquo;</a></li>
            </ul>
        </div>
    </div>
</div>
</body>
</html>

22 login.html

<!doctype html>
<html xmlns:th="http://www.thymeleaf.org">

<head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="/css/bootstrap.min.css">

    <!-- Loding font -->
    <link href="https://fonts.googleapis.com/css?family=Montserrat:300,700" rel="stylesheet">

    <!-- Custom Styles -->
    <link rel="stylesheet" type="text/css" href="/css/styles.css">

    <title>登录</title>
</head>
<body>

<!-- Backgrounds -->

<div id="login-bg" class="container-fluid">

    <div class="bg-img"></div>
<!--    <div class="bg-color"></div>-->
</div>

<!-- End Backgrounds -->

<div class="container" id="login">
    <div class="row justify-content-center">
        <div class="col-lg-8">
            <div class="login">

                <h1>用户登录</h1>
                <p th:text="${data}" style="text-align: center"></p>
                <!-- Loging form -->
                <form action="/login" method="post">
                    <div class="form-group">

                        <input type="text"  class="form-control" name="u_name" id="u_name" required="true" th:value="${{name}}" placeholder="UserName">

                    </div>
                    <div class="form-group">
                        <input type="password" class="form-control" name="u_password" id="u_password"  required="true" placeholder="Password">
                    </div>


                    <br>
                    <button type="submit" class="btn btn-lg btn-block btn-success">登录</button>
                </form>
                <!-- End Loging form -->

            </div>
        </div>
    </div>

</div>

<p class="copyright">Copyright &copy; 2022.HIKVISION All rights reserved.</p>
</body>
<style>
    body{
    }
    .copyright{
        position: absolute;
        bottom: 50px;
        left: 50%;
        transform: translate(-50%,0);
        display: block;
        height: 60px;
        font-size: 12px;
        color: white;
        z-index: 999999999999999999999999;
    }
</style>
</html>

23 userInsert.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">

<head>
    <title>新增用户</title>
    <link rel="stylesheet" href="/css/bootstrap.min.css" type="text/css"/>
    <link rel="stylesheet" href="/css/listE.css" type="text/css"/>
    <script type="text/javascript" src="/js/jquery.js"></script>
    <script type="text/javascript" src="/js/bootstrap.js"></script>
</head>
<body>
<div id="body" style="width: 40%;margin: 0 auto">
    <h1>欢迎您!管理员 <span th:if="${session.username != null}" th:text="${session.username}"/></h1>
    <div class="row nav1">
        <div class="backhome">
            <a href="/equipment/getAllEquipment" class="home" title="返回设备列表"></a>
        </div>
        <div class="backhome">
            <a href="/getAllUser" class="user" title="返回用户管理列表"></a>
        </div>
        <div class="col-xs-6">
            <form th:action="@{/getUserByName}" class="form-inline" role="form">
                <div class="form-group">
                    <input type="text" class="form-control" name="u_name" placeholder="请输入用户name">
                </div>
                <button type="submit" class="btn btn-default">查询</button>
            </form>
        </div>
    </div>


    <form th:action="@{/saveUser}" class="form-horizontal" role="form">
        <div class="form-group">
            <label for="u_name" class="col-sm-2 control-label">用户名称</label>
            <div class="col-sm-10">
                <input style="width: 40%" type="text" class="form-control" id="u_name" name="u_name" placeholder="请输入用户名称">
            </div>

            <label for="u_password" class="col-sm-2 control-label">用户密码</label>
            <div class="col-sm-10">
                <input style="width: 40%" type="text" class="form-control" id="u_password" name="u_password" placeholder="请输入用户密码">
            </div>

            <label  class="col-sm-8 control-label">用户权限</label>
<!--            <div class="col-sm-10">-->
<!--                <input style="width: 40%" type="text" class="form-control" id="u_power" name="u_power" placeholder="请输入用户权限">-->
<!--            </div>-->

            <div class="col-sm-6">
                <select name="u_power" >
                    <option value ="0">普通用户</option>
                    <option value ="1">管理员</option>
                </select>
            </div>
        </div>
        <div class="form-group">
            <div class="col-sm-offset-2 col-sm-10">
                <button type="submit" class="btn btn-default">确认添加</button>
            </div>
        </div>
    </form>
</div>
</body>
</html>

24 userManage.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">

<head>
    <title>用户信息</title>
    <link rel="stylesheet" href="/css/bootstrap.min.css" type="text/css"/>
    <link rel="stylesheet" href="/css/listE.css" type="text/css"/>
    <script type="text/javascript" src="/js/jquery.js"></script>
    <script type="text/javascript" src="/js/bootstrap.js"></script>
</head>
<body>
<div id="body" style="width: 40%;margin: 0 auto">
    <h1>欢迎您!管理员 <span th:if="${session.username != null}" th:text="${session.username}"/></h1>
    <div class="row nav1">
        <div class="backhome">
            <a href="/equipment/getAllEquipment" class="home" title="返回设备列表"></a>
        </div>
        <div class="backhome">
            <a href="/getAllUser" class="user" title="返回用户管理列表"></a>
        </div>
        <div class="col-xs-6">
            <form th:action="@{/getUserByName}" class="form-inline" role="form">
                <div class="form-group">
                    <input type="text" class="form-control" name="u_name" placeholder="请输入用户name">
                </div>
                <button type="submit" class="btn btn-default">查询</button>
            </form>
        </div>
        <div class="col-xs-6">
            <form th:action="@{/toSaveUser}">
                <button type="submit" class="btn btn-info">添加用户</button>
            </form>
        </div>
    </div>


    <form th:action="@{/updateUser}" class="form-horizontal" role="form">
        <div class="form-group">
            <label for="u_name" class="col-sm-2 control-label">用户名称</label>
            <div class="col-sm-10">
                <input readOnly style="width: 40%" type="text" class="form-control" id="u_name" name="u_name" th:value="${u_name}">
            </div>
        </div>
        <div class="form-group">
            <label for="u_password" class="col-sm-2 control-label">用户密码</label>
            <div class="col-sm-10">
                <input style="width: 40%" type="text" class="form-control" id="u_password" name="u_password" th:value="${u_password}" placeholder="请输入要修改的名称">
            </div>
        </div>
        <div class="form-group">
            <label  class="col-sm-8 control-label">用户权限</label>
            <div class="col-sm-6">
                <select name="u_power" >
                    <option value ="0">普通用户</option>
                    <option value ="1">管理员</option>
                </select>
            </div>
        </div>
        <div class="form-group">
            <div class="col-sm-offset-2 col-sm-10">
                <button type="submit" class="btn btn-default">确认修改</button>
            </div>
        </div>
    </form>
</div>
</body>
</html>

25 application.yml

spring:
  profiles:
    active: dev

26 application-dev.yml

server:
  port: 8080
  servlet:
    context-path: /

spring:
  datasource:
    username: root
    password: Admin12345
    url: jdbc:mysql://10.195.64.182:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
  thymeleaf:
    #缓冲的配置
    cache: false
    check-template: true
    check-template-location: true
    #开启MVC thymeleaf 视图解析
    enabled: true
    encoding: utf-8
    mode: HTML
    prefix: classpath:/templates/
    suffix: .html


mybatis:
  #  扫描映射文件
  mapper-locations: classpath:mapping/*.xml
  #  配置别名扫描的包
  type-aliases-package: com.yu.demo.entity
#showSql
logging:
  level:
    root: info
    com.yu.demo.mapper: debug