spring boot mybatis 简单应用示例

1. 工程结构

2. 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 http://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.2.RELEASE</version>
        <relativePath/> <!-- JDK>=1.8 -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>11</java.version>
    </properties>

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

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </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>
            </plugin>
        </plugins>
    </build>

</project>

3. yml配置

# DataSource Config
spring:
  datasource:
    driver-class-name: org.h2.Driver
    # spring提供的功能,用来初始化数据库
    schema: classpath:db/schema-h2.sql
    # spring提供的功能,用来初始化数据库
    data: classpath:db/data-h2.sql
    url: jdbc:h2:mem:test
    username: root
    password: test
# mybatis配置
mybatis:
  # 声明实体别名
  type-aliases-package: com.example.mybatis.domain
  # mapper xml 扫描范围
  mapper-locations: classpath:mapper/*.xml

4. 启动类

package com.example.mybatis;

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

@SpringBootApplication
//限定mapper扫描的范围,默认值和@SpringBootApplication范围相同
@MapperScan("com.example.mybatis.mapper")
//开启mybatis事务
@EnableTransactionManagement
public class MybatisApplication {

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

}

5. 实体类(模型层)

package com.example.mybatis.domain;

import lombok.Data;
import lombok.ToString;

@Data
@ToString
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

6. 映射类(持久层)

package com.example.mybatis.mapper;

import com.example.mybatis.domain.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

//声明这是一个mapper
@Mapper
//mapper中,注解和XML可以同时存在
//但一般地,使用XML就不要使用注解,避免混淆,
//普通注解和动态SQL注解(Provider)混用较为常见,
//复杂sql动态生成,简单sql直接写在注解中
public interface UserMapper {

    //使用注解定义SQL语句
    @Select("select * from user")
    List<User> selectUserList();

    @Select("select * from user where id=#{id}")
    User findUserById(@Param("id") Long id);

    //使用动态生成的sql
    @SelectProvider(type = UserMapperProvider.class, method = "findByAge")
    List<User> findUserByAge(Integer age);

    @SelectProvider(type = UserMapperProvider.class, method = "findByName")
    List<User> findUserByName(String name);

    //使用XML定义的SQL
    int deleteByPrimaryKey(Long id);

    int insert(User record);

    int insertSelective(User record);

    User selectByPrimaryKey(Long id);

    int updateByPrimaryKeySelective(User record);

    int updateByPrimaryKey(User record);

    //动态SQL类,一般单独写在某处,写在这里只为方便
    class UserMapperProvider{

        public String findByAge(Integer age){
            return "SELECT id, name, age, email FROM user WHERE age = ".concat(String.valueOf(age));
        }

        public String findByName(String name){
            //使用mybatis提供的工具类来准备相同的 SQL 语句
            return new SQL(){
                {
                    SELECT("id, name, age, email");
                    FROM("user");
                    WHERE("name = '".concat(name).concat("'"));
                }
            }.toString();
        }
    }
}

7. 映射配置(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.example.mybatis.mapper.UserMapper" >
    <resultMap id="BaseResultMap" type="User" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="age" property="age" jdbcType="INTEGER" />
        <result column="email" property="email" jdbcType="VARCHAR" />
    </resultMap>

    <!-- sql片段 -->
    <sql id="Base_Column_List" >
        id, name, age, email
    </sql>

    <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="long" >
        select
        <include refid="Base_Column_List" />
        from user
        where id = #{id,jdbcType=INTEGER}
    </select>

    <delete id="deleteByPrimaryKey" parameterType="long" >
        delete from user
        where id = #{id,jdbcType=INTEGER}
    </delete>

    <insert id="insert" parameterType="User" >
        insert into user (id, name, age,
        email)
        values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER},
        #{email,jdbcType=VARCHAR})
    </insert>

    <insert id="insertSelective" parameterType="User" >
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides="," >
            <if test="id != null" >
                id,
            </if>
            <if test="name != null" >
                name,
            </if>
            <if test="age != null" >
                age,
            </if>
            <if test="email != null" >
                email,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides="," >
            <if test="id != null" >
                #{id,jdbcType=INTEGER},
            </if>
            <if test="name != null" >
                #{name,jdbcType=VARCHAR},
            </if>
            <if test="age != null" >
                #{age,jdbcType=INTEGER},
            </if>
            <if test="email != null" >
                #{email,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="User" >
        update user
        <set >
            <if test="name != null" >
                name = #{name,jdbcType=VARCHAR},
            </if>
            <if test="age != null" >
                age = #{age,jdbcType=INTEGER},
            </if>
            <if test="email != null" >
                email = #{email,jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>
    <update id="updateByPrimaryKey" parameterType="User" >
        update user
        set name = #{name,jdbcType=VARCHAR},
        age = #{age,jdbcType=INTEGER}
        email = #{email,jdbcType=VARCHAR},
        where id = #{id,jdbcType=INTEGER}
    </update>
</mapper>

8. service类

package com.example.mybatis.service;

import com.example.mybatis.domain.User;
import com.example.mybatis.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

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

    //使用事物,可以在web层,也可在service层
    @Transactional
    public void addUsers(){
        User user1 = new User();
        user1.setId(6L);
        user1.setAge(12);
        user1.setName("shier");
        user1.setEmail("shier@demo.com");
        User user2 = new User();
        user2.setId(7L);
        user2.setAge(1234);
        user2.setName("dalao");
        user2.setEmail("dalao@demo.com");
        userMapper.insert(user1);
        userMapper.insert(user2);
        String err = null;
        System.out.println("事物回滚,addUsers()将一个用户也不能添加");
        err.indexOf('e');
    }
}

@Transational具体的参数及解释

属性	类型	描述
value	String	可选的限定描述符，指定使用的事务管理器
propagation	enum: Propagation	可选的事务传播行为设置
isolation	enum: Isolation	可选的事务隔离级别设置
readOnly	boolean	读写或只读事务，默认读写
timeout	int (in seconds granularity)	事务超时时间设置
rollbackFor	Class对象数组，必须继承自Throwable	导致事务回滚的异常类数组
rollbackForClassName	类名数组，必须继承自Throwable	导致事务回滚的异常类名字数组
noRollbackFor	Class对象数组，必须继承自Throwable	不会导致事务回滚的异常类数组
noRollbackForClassName	类名数组，必须继承自Throwable	不会导致事务回滚的异常类名字数组

9. 数据库初始化配置

9.1 schema-h2.sql

DROP TABLE IF EXISTS user;

CREATE TABLE user
(
	id BIGINT(20) NOT NULL COMMENT '主键ID',
	name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
	age INT(11) NULL DEFAULT NULL COMMENT '年龄',
	email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (id)
);

9.2 data-h2.sql

DELETE FROM user;

INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, 'test1@demo.com'),
(2, 'Jack', 20, 'test2@demo.com'),
(3, 'Tom', 28, 'test3@demo.com'),
(4, 'Sandy', 21, 'test4@demo.com'),
(5, 'Billie', 24, 'test5@demo.com');

10. 测试类

package com.example.mybatis;

import com.example.mybatis.domain.User;
import com.example.mybatis.mapper.UserMapper;
import com.example.mybatis.service.UserService;
import org.junit.After;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisApplicationTests {

    @Autowired
    UserMapper userMapper;

    @Autowired
    UserService userService;

    @Test(expected = NullPointerException.class)
    public void contextLoads() {
        //使用XML定义的SQL
        User user1 = userMapper.selectByPrimaryKey(1L);
        //使用注解定义的SQL
        User user2 = userMapper.findUserById(2L);
        //使用注解定义的动态SQL
        List<User> user3 = userMapper.findUserByAge(21);
        System.out.println("XML定义的按主键查询用户\n"+user1);
        System.out.println("annotation定义的按ID查询用户\n"+user2);
        System.out.println("annotation定义的按年龄查询用户\n"+user3);

        System.out.println("---------事物---------");

        userService.addUsers();

    }

    @After
    public void after(){
        System.out.println("--------查询所有用户---------");
        //注解定义的SQL能自动转换类型为实体类型
        List<User> users = userMapper.selectUserList();
        users.forEach(System.out::println);
    }
}

11. 控制台输出

XML定义的按主键查询用户
User(id=1, name=Jone, age=18, email=test1@demo.com)
annotation定义的按ID查询用户
User(id=2, name=Jack, age=20, email=test2@demo.com)
annotation定义的按年龄查询用户
[User(id=4, name=Sandy, age=21, email=test4@demo.com)]
---------事物---------
事物回滚,addUsers()将一个用户也不能添加
--------查询所有用户---------
User(id=1, name=Jone, age=18, email=test1@demo.com)
User(id=2, name=Jack, age=20, email=test2@demo.com)
User(id=3, name=Tom, age=28, email=test3@demo.com)
User(id=4, name=Sandy, age=21, email=test4@demo.com)
User(id=5, name=Billie, age=24, email=test5@demo.com)

转载于:https://my.oschina.net/u/4075062/blog/3008019