springboot mybatis crud

原创转载请注明出处：http://agilestyle.iteye.com/blog/2429411

 

Project Directory

 

Maven Dependency

<?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>org.fool.springboot</groupId>
    <artifactId>hello-springboot</artifactId>
    <version>1.0-SNAPSHOT</version>

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

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <spring.boot.version>1.5.15.RELEASE</spring.boot.version>
        <spring-kafka.version>1.3.6.RELEASE</spring-kafka.version>
        <guava.version>23.0</guava.version>
        <druid.version>1.0.31</druid.version>
        <mybatis.spring.version>1.3.2</mybatis.spring.version>
        <kafka.version>0.11.0.3</kafka.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-tomcat</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jetty</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.eclipse.jetty.websocket</groupId>
                    <artifactId>websocket-server</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.eclipse.jetty.websocket</groupId>
                    <artifactId>javax-websocket-server-impl</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

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

        <dependency>
            <groupId>org.springframework.kafka</groupId>
            <artifactId>spring-kafka</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient</artifactId>
        </dependency>

        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjrt</artifactId>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
        </dependency>

        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-access</artifactId>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.kafka</groupId>
            <artifactId>kafka-clients</artifactId>
            <version>${kafka.version}</version>
        </dependency>

        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>${guava.version}</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.6</version>
                <configuration>
                    <configurationFile>generatorConfig.xml</configurationFile>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>org.mybatis.generator</groupId>
                        <artifactId>mybatis-generator-core</artifactId>
                        <version>1.3.6</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>
</project>

Note: 可以使用 mvn dependency:tree 查看jar包版本是否冲突

 

schema.sql

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  `nickname` varchar(20) NOT NULL,
  `email` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
)

 

mybatis generatorConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <classPathEntry
            location="/Users/${user.name}/.m2/repository/mysql/mysql-connector-java/5.1.46/mysql-connector-java-5.1.46.jar" />

    <context id="hello-springboot-mybatis_generator" targetRuntime="MyBatis3" defaultModelType="flat">
        <commentGenerator>
            <property name="suppressAllComments" value="false" />
        </commentGenerator>

        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
            connectionURL="jdbc:mysql://localhost:3306/springboot?useUnicode=true&amp;characterEncoding=UTF-8"
            userId="root" password="12345678">
        </jdbcConnection>

        <javaModelGenerator targetPackage="org.fool.springboot.model" targetProject="./src/main/java">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <sqlMapGenerator targetPackage="mapper" targetProject="./src/main/resources">
            <property name="enableSubPackages" value="true" />
        </sqlMapGenerator>

        <javaClientGenerator targetPackage="org.fool.springboot.dao.mapper" targetProject="./src/main/java" type="XMLMAPPER">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>

        <table tableName="user" domainObjectName="User"
            enableCountByExample="false" enableUpdateByExample="false"
            enableDeleteByExample="false" enableSelectByExample="false"
            selectByExampleQueryId="false">
        </table>

    </context>

</generatorConfiguration>

Note：使用 mybatis-generator:generate -e 自动生成相关schema对应的mapper文件包括xml和java文件

 

src/main/resources 

application.properties

server.port=8088

 

jdbc.properties

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=12345678
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize=1
spring.datasource.maxActive=5
spring.datasource.minIdle=1
spring.datasource.maxIdle=20
spring.datasource.maxWait=60000

mybatis.mapper-locations=classpath*:mapper/*Mapper.xml,classpath*:mapper/extension/*MapperExt.xml
mybatis.type-aliases-package=org.fool.springboot.model

 

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="org.fool.springboot.dao.mapper.UserMapper">
  <resultMap id="BaseResultMap" type="org.fool.springboot.model.User">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Aug 25 13:50:55 CST 2018.
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="nickname" jdbcType="VARCHAR" property="nickname" />
    <result column="email" jdbcType="VARCHAR" property="email" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Aug 25 13:50:55 CST 2018.
    -->
    id, username, password, nickname, email
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Aug 25 13:50:55 CST 2018.
    -->
    select 
    <include refid="Base_Column_List" />
    from user
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Aug 25 13:50:55 CST 2018.
    -->
    delete from user
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="org.fool.springboot.model.User">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Aug 25 13:50:55 CST 2018.
    -->
    insert into user (id, username, password, 
      nickname, email)
    values (#{id,jdbcType=INTEGER}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, 
      #{nickname,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="org.fool.springboot.model.User">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Aug 25 13:50:55 CST 2018.
    -->
    insert into user
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="username != null">
        username,
      </if>
      <if test="password != null">
        password,
      </if>
      <if test="nickname != null">
        nickname,
      </if>
      <if test="email != null">
        email,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=INTEGER},
      </if>
      <if test="username != null">
        #{username,jdbcType=VARCHAR},
      </if>
      <if test="password != null">
        #{password,jdbcType=VARCHAR},
      </if>
      <if test="nickname != null">
        #{nickname,jdbcType=VARCHAR},
      </if>
      <if test="email != null">
        #{email,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="org.fool.springboot.model.User">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Aug 25 13:50:55 CST 2018.
    -->
    update user
    <set>
      <if test="username != null">
        username = #{username,jdbcType=VARCHAR},
      </if>
      <if test="password != null">
        password = #{password,jdbcType=VARCHAR},
      </if>
      <if test="nickname != null">
        nickname = #{nickname,jdbcType=VARCHAR},
      </if>
      <if test="email != null">
        email = #{email,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="org.fool.springboot.model.User">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Aug 25 13:50:55 CST 2018.
    -->
    update user
    set username = #{username,jdbcType=VARCHAR},
      password = #{password,jdbcType=VARCHAR},
      nickname = #{nickname,jdbcType=VARCHAR},
      email = #{email,jdbcType=VARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

 

UserMapperExt.xml

Note: *MapperExt.xml即项目需求需要扩展的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="org.fool.springboot.dao.mapper.extension.UserMapperExt">
  <resultMap id="BaseResultMap" type="org.fool.springboot.model.User">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="nickname" jdbcType="VARCHAR" property="nickname" />
    <result column="email" jdbcType="VARCHAR" property="email" />
  </resultMap>
  <sql id="Base_Column_List">
    id, username, password, nickname, email
  </sql>
  <select id="selectAll" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from user
  </select>
</mapper>

 

src/main/java

Server.java

package org.fool.springboot;

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

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

或者（加上@MapperScan的Server，MyBatis生成的Mapper就不需要加@Mapper注解了）

package org.fool.springboot;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("org.fool.springboot.dao.mapper")
public class Server {
    public static void main(String[] args) {
        SpringApplication.run(Server.class, args);
    }
}

  

 

Model

User.java

package org.fool.springboot.model;

public class User {
    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column user.id
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    private Integer id;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column user.username
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    private String username;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column user.password
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    private String password;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column user.nickname
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    private String nickname;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column user.email
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    private String email;

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column user.id
     *
     * @return the value of user.id
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    public Integer getId() {
        return id;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column user.id
     *
     * @param id the value for user.id
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column user.username
     *
     * @return the value of user.username
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    public String getUsername() {
        return username;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column user.username
     *
     * @param username the value for user.username
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    public void setUsername(String username) {
        this.username = username == null ? null : username.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column user.password
     *
     * @return the value of user.password
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    public String getPassword() {
        return password;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column user.password
     *
     * @param password the value for user.password
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    public void setPassword(String password) {
        this.password = password == null ? null : password.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column user.nickname
     *
     * @return the value of user.nickname
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    public String getNickname() {
        return nickname;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column user.nickname
     *
     * @param nickname the value for user.nickname
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    public void setNickname(String nickname) {
        this.nickname = nickname == null ? null : nickname.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column user.email
     *
     * @return the value of user.email
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    public String getEmail() {
        return email;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column user.email
     *
     * @param email the value for user.email
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    public void setEmail(String email) {
        this.email = email == null ? null : email.trim();
    }
}

 

Mapper

Note: mybatis-generate-maven-plugin自动生成的代码需要额外添加@Mapper才能加入spring容器

或者@Mapper可以不加，在启动类中加上 @MapperScan("org.fool.springboot.dao.mapper")

UserMapper.java

package org.fool.springboot.dao.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.fool.springboot.model.User;

@Mapper
public interface UserMapper {
    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    int deleteByPrimaryKey(Integer id);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    int insert(User record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    int insertSelective(User record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    User selectByPrimaryKey(Integer id);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    int updateByPrimaryKeySelective(User record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbg.generated Sat Aug 25 13:50:55 CST 2018
     */
    int updateByPrimaryKey(User record);
}

 

UserMapperExt.java

Note: *Ext.java即项目需求需要扩展的类，需要开发人员自己实现维护

package org.fool.springboot.dao.mapper.extension;

import org.apache.ibatis.annotations.Mapper;
import org.fool.springboot.model.User;

import java.util.List;

@Mapper
public interface UserMapperExt {
    List<User> selectAll();
}

 

Service

UserService.java

package org.fool.springboot.service;

import org.fool.springboot.model.User;

import java.util.List;

public interface UserService {
    List<User> getAllUsers();

    User getUserById(Integer id);
}

 

UserServiceImpl.java

package org.fool.springboot.service.impl;

import org.fool.springboot.dao.mapper.UserMapper;
import org.fool.springboot.dao.mapper.extension.UserMapperExt;
import org.fool.springboot.model.User;
import org.fool.springboot.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private UserMapperExt userMapperExt;

    @Override
    public List<User> getAllUsers() {
        return userMapperExt.selectAll();
    }

    @Override
    public User getUserById(Integer id) {
        return userMapper.selectByPrimaryKey(id);
    }
}

 

Controller

UserController.java

package org.fool.springboot.controller;

import org.fool.springboot.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/v1")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/users")
    public Object users() {
        return userService.getAllUsers();
    }

    @GetMapping("/users/{id}")
    public Object users(@PathVariable Integer id) {
        return userService.getUserById(id);
    }

}

 

Test

启动server

-Dlogging.config=/Users/admin/IdeaProjects/hello-springboot/src/main/resources/logback.xml -Dspring.config.location=/Users/admin/IdeaProjects/hello-springboot/src/main/resources/application.properties,/Users/admin/IdeaProjects/hello-springboot/src/main/resources/jdbc.properties

curl http://localhost:8088/v1/users

 

curl http://localhost:8088/v1/users/2