SpringBoot 使用Mybatis操作mysql示例

创建数据库
create databases baodanjia;

创建帐号
create user ‘baodanjia‘@‘%‘ identified by ‘123456‘

grant all privileges on baodanjia.* to ‘baodanjia‘@‘%‘;

flush privileges;

创建表
mysql -ubaodanjia -p123456

use baodanjia;

CREATE TABLE `admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `user_pwd` varchar(50) DEFAULT NULL,
  `nick_name` varchar(50) DEFAULT NULL,
  `remark` varchar(50) DEFAULT NULL,
  `err_count` int(11) DEFAULT NULL,
  `gmt_create` datetime DEFAULT NULL,
  `gmt_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.inslife</groupId>
  <artifactId>baodanjia</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>baodanjia</name>
  <url>http://bdj.in-s-life.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.9.RELEASE</version>
    <relativePath/>
  </parent>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
    </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>
    </dependency>
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>1.3.2</version>
    </dependency>
    <dependency>
      <groupId>commons-lang</groupId>
      <artifactId>commons-lang</artifactId>
      <version>2.6</version>
    </dependency>
    <!--swagger-ui-->
    <dependency>
      <groupId>io.springfox</groupId>
      <artifactId>springfox-swagger2</artifactId>
      <version>2.2.2</version>
    </dependency>
    <dependency>
      <groupId>io.springfox</groupId>
      <artifactId>springfox-swagger-ui</artifactId>
      <version>2.2.2</version>
    </dependency>

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <executions>
          <execution>
            <goals>
              <goal>repackage</goal>
            </goals>
          </execution>
        </executions>
      </plugin>
    </plugins>
  </build>
</project>

server:
  port: 9090
spring:
  application:
    name: baodanjia
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/baodanjia?useUnicode=true&characterEncoding=UTF-8
    username: baodanjia
    password: 123456
  thymeleaf:
    cache: false
    encoding: utf-8
logging:
  level:
      com.inslife.mapper: debug
mybatis:
  mapper-locations: classpath:mapper/*.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.inslife.mapper.AdminMapper">
    <resultMap id="BaseResultMap" type="com.inslife.model.Admin">
        <id property="id" column="id" jdbcType="INTEGER"/>
        <result property="user_name" column="user_name" jdbcType="VARCHAR"/>
        <result property="user_pwd" column="user_pwd" jdbcType="VARCHAR"/>
        <result property="nick_name" column="nick_name" jdbcType="VARCHAR"/>
        <result property="remark" column="remark" jdbcType="VARCHAR"/>
        <result property="err_count" column="err_count" jdbcType="INTEGER"/>
        <result property="gmt_create" column="gmt_create" jdbcType="TIMESTAMP"/>
        <result property="gmt_modified" column="gmt_modified" jdbcType="TIMESTAMP"/>
    </resultMap>
    <insert id="insert" parameterType="com.inslife.model.Admin">
    insert into admin
    (user_name,user_pwd,nick_name,remark,err_count,gmt_create)
    values (
    #{user_name},#{user_pwd},#{nick_name},#{remark},#{err_count},#{gmt_create}
    )
    </insert>
    <update id="update" parameterType="com.inslife.model.Admin">
      update admin
      set
      user_name=#{user_name},
      user_pwd=#{user_pwd},
      nick_name=#{nick_name},
      remark=#{remark},
      err_count=#{err_count},
      gmt_modified=#{gmt_modified}
      where id =#{id}
    </update>
    <update id="updateUserPwd">
        update admin set user_pwd =#{user_pwd} where user_name=#{user_name}
    </update>
    <update id="updateErrCount">
        update admin set err_count =#{err_count} where user_name=#{user_name}
    </update>
    <select id="infoById" resultMap="BaseResultMap">
        select * from admin where id = #{id}
    </select>
    <select id="infoByUserName" resultMap="BaseResultMap">
        select * from admin where user_name = #{user_name}
    </select>
    <select id="login" resultMap="BaseResultMap">
        select * from admin where user_name=#{user_name} and user_pwd=#{user_pwd}
    </select>
    <select id="listAll" resultMap="BaseResultMap">
        select * from admin
    </select>

</mapper>

package com.inslife;

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

@SpringBootApplication
public class App 
{
    public static void main( String[] args )
    {
        SpringApplication app = new SpringApplication(App.class);
        //关闭banner
        app.setBannerMode(Banner.Mode.OFF);
        app.run(args);

        System.out.println( "OK" );
    }
}

package com.inslife.util;

import java.util.HashMap;
import java.util.Map;

/**
 * 输出帮助类
 */
public class ResUtil {

    public static Map<String,Object> OK(String desc){
        return Result("0",desc,null);
    }
    public static Map<String,Object> OK(String desc,Object data){
        return Result("0",desc,data);
    }

    public  static Map<String,Object> Error(String desc){
        return Result("1",desc,null);
    }

    public  static Map<String,Object> Error(String desc,Object data){
        return Result("1",desc,data);
    }

    public static Map<String,Object> Result(String code,String desc,Object data){
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("code",code);
        map.put("desc",desc);
        map.put("data",data);
        return map;
    }
}

package com.inslife.model;

import java.util.Date;

public class Admin {

    private Integer id;
    private String user_name;
    private String user_pwd;
    private String nick_name;
    private String remark;
    private Integer err_count;
    private Date gmt_create;
    private Date gmt_modified;

    //getter setter

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUser_pwd() {
        return user_pwd;
    }

    public void setUser_pwd(String user_pwd) {
        this.user_pwd = user_pwd;
    }

    public String getNick_name() {
        return nick_name;
    }

    public void setNick_name(String nick_name) {
        this.nick_name = nick_name;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public Integer getErr_count() {
        return err_count;
    }

    public void setErr_count(Integer err_count) {
        this.err_count = err_count;
    }

    public Date getGmt_create() {
        return gmt_create;
    }

    public void setGmt_create(Date gmt_create) {
        this.gmt_create = gmt_create;
    }

    public Date getGmt_modified() {
        return gmt_modified;
    }

    public void setGmt_modified(Date gmt_modified) {
        this.gmt_modified = gmt_modified;
    }
}

package com.inslife.mapper;

import com.inslife.model.Admin;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface AdminMapper {

    /**
     * 注解
     */
    @Select("select * from admin where user_name = #{user_name}")
    Admin findByUserName(@Param("user_name") String user_name);

    /**
     * xml
     */
    int update(Admin admin);

    Admin insert(Admin admin);

    int updateUserPwd(@Param("user_name") String user_name, @Param("user_pwd") String user_pwd);

    int updateErrCount(@Param("user_name") String user_name, @Param("err_count") Integer err_count);

    Admin infoByUserName(@Param("user_name") String user_name);

    Admin infoById(@Param("id") Integer id);

    Admin login(@Param("user_name") String user_name, @Param("user_pwd") String user_pwd);

    List<Admin> listAll();

}

package com.inslife.controller;

import com.inslife.mapper.AdminMapper;
import com.inslife.model.Admin;
import com.inslife.util.ResUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.thymeleaf.util.StringUtils;

import java.util.List;

@Api(value = "用户管理")
@RequestMapping(value = "/api")
@RestController
public class IndexController {

    @Autowired
    private AdminMapper adminMapper;

    @ApiOperation(value = "查看详细", notes="查看详细")
    @GetMapping("/admin/{id}")
    public Object getAdmin(@PathVariable("id") Integer id) {
        Admin query = adminMapper.infoById(id);
        return ResUtil.OK("ok", query);
    }

    @ApiOperation(value = "用户添加", notes="用户添加")
    @PostMapping("/admin")
    public Object getAdmin(@RequestBody Admin admin) {
        Admin data = adminMapper.insert(admin);
        return ResUtil.OK("ok", data);
    }

    @ApiOperation(value = "用户列表", notes="用户列表")
    @GetMapping("/admin")
    public Object listAll() {
        List<Admin> data = adminMapper.listAll();
        return ResUtil.OK("ok", data);
    }

    @ApiOperation(value = "用户登录", notes="用户登录")
    @PostMapping("/login")
    public Object login(@RequestBody Admin admin) {

        if (admin == null || StringUtils.isEmpty(admin.getUser_name()) || StringUtils.isEmpty(admin.getUser_pwd())) {
            return ResUtil.Error("用户名或密码为空");
        }

        Admin tryCount = adminMapper.infoByUserName(admin.getUser_name());
        if(tryCount!=null&&tryCount.getErr_count()!=null&&tryCount.getErr_count()>=5){
            return ResUtil.Error("登录错误"+tryCount.getErr_count()+"次,用户已被锁定");
        }

        Admin query = adminMapper.login(admin.getUser_name(), admin.getUser_pwd());
        if(query==null){
            tryCount = adminMapper.infoByUserName(admin.getUser_name());
            if(tryCount!=null){
                int count = tryCount.getErr_count()==null ?0:tryCount.getErr_count();
                count ++;
                return ResUtil.Error("用户名或密码错误"+count+"次,错误5次后将被锁定");
            }
            return ResUtil.Error("用户名或密码错误");
        }

        adminMapper.updateErrCount(admin.getUser_name(),0);

        return ResUtil.OK("ok", query);
    }

}

package com.inslife.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.cors.CorsConfiguration;
import org.springframework.web.cors.UrlBasedCorsConfigurationSource;
import org.springframework.web.filter.CorsFilter;

/**
 * 跨域设置
 */
@Configuration
public class CorsConfig {

    private CorsConfiguration buildConfig() {
        CorsConfiguration corsConfiguration = new CorsConfiguration();
        corsConfiguration.addAllowedOrigin("*"); //允许任何域名
        corsConfiguration.addAllowedHeader("*"); //允许任何头
        corsConfiguration.addAllowedMethod("*"); //允许任何方法
        return corsConfiguration;
    }

    @Bean
    public CorsFilter corsFilter() {
        UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
        source.registerCorsConfiguration("/**", buildConfig()); //注册
        return new CorsFilter(source);
    }

}

package com.inslife.config;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

/**
 * swagger生成api说明
 */
@Configuration
@EnableSwagger2
public class Swagger2 {
    /**
     * 创建API应用
     * apiInfo() 增加API相关信息
     * 通过select()函数返回一个ApiSelectorBuilder实例,用来控制哪些接口暴露给Swagger来展现，
     * 本例采用指定扫描的包路径来定义指定要建立API的目录。
     *
     * @return
     */
    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.basePackage("com.inslife.controller"))
                .paths(PathSelectors.any())
                .build();
    }

    /**
     * 创建该API的基本信息（这些基本信息会展现在文档页面中）
     * 访问地址：http://项目实际地址/swagger-ui.html
     *
     * @return
     */
    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("Spring Boot中使用Swagger2构建RESTful APIs")
                .description("描述")
                .termsOfServiceUrl("https://in-s-life.com")
                .contact("admin")
                .version("1.0")
                .build();
    }
}