MyBatis & 通用Mapper

十毛

发布于 2019-03-27 15:22:58

1.6K0

MyBatis使用简单、灵活，但是有一些特别常用的简单SQL也要手写，很浪费时间。使用通用Mapper后可以免去这种工作，提高开发效率。

快速使用

引入Jar(pom.xml)

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

<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.1.5</version>
</dependency>

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

spring.datasource.url=jdbc:mysql://localhost:3306/tenmao?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
spring.datasource.username=tenmao_user
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

实体类 (Person.java)

@Data
@Table(name = "person")
@NameStyle(Style.camelhumpAndLowercase)
public class Person {
    @javax.persistence.Id
    @KeySql(useGeneratedKeys = true)
    private Integer id;
    private String name;
    private Integer age;
    private Boolean gender;
    @ColumnType(jdbcType = JdbcType.VARCHAR, typeHandler = StringListTypeHandler.class)
    private List<String> hobbies;
}

Mapper接口(PersonMapper.java)

package com.tenmao.tmapper.mapper;

import com.tenmao.tmapper.domain.Person;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

@org.apache.ibatis.annotations.Mapper
public interface PersonMapper extends Mapper<Person> {
    @Select("SELECT * FROM person WHERE name=#{name}")
    Person selectByName(@Param("name") String name);

    List<Person> selectByAge(@Param("age") int age);
}

使用代码

Person person = personMapper.selectByPrimaryKey(1);

优点

为最常见的语句提供了内置接口，不需要写任何SQL语句。比如:
- selectOne
- select
- selectAll
- selectCount
- selectByPrimrayKey
- 方法太多，省略其他...
针对一些稍微高级一点的查询，可以使用Example机制

Example example = new Example(Person.class);
example.createCriteria().andGreaterThan("age", 18);

List<Person> people = personMapper.selectByExample(example);

对应的SQL语句是

SELECT id,name,age,gender FROM person WHERE ( ( age > ? ) )

也支持标准的MyBatis的接口使用方式 注解方式

@org.apache.ibatis.annotations.Mapper
public interface PersonMapper extends Mapper<Person> {
    @Select("SELECT * FROM person WHERE name=#{name}")
    Person selectByName(@Param("name") String name);
}

XML文件方式

mapper/person_mapper.xml

<mapper namespace="com.tenmao.tmapper.mapper.PersonMapper">
    <select id="selectByAge" resultType="com.tenmao.tmapper.domain.Person">
        SELECT * FROM person WHERE age=#{age}
    </select>
</mapper>

application.properties

mybatis.mapper-locations=classpath:mapper/*_mapper.xml

其他配置

与数据库没有对应关系的字段

@Transient
private String otherThings; //非数据库表中字段

配置TypeHandler

@ColumnType(typeHandler = AddressTypeHandler.class)
private Address address;

application.properties

mybatis.type-handlers-package=com.tenmao.handler

常见错误

personMapper.selectByPrimaryKey(1)执行的SQL语句是

Preparing: SELECT id,name,age,gender FROM person WHERE id = ? AND name = ? AND age = ? AND gender = ?
Parameters: 1(Integer), 1(Integer), 1(Integer), 1(Integer)

因为没有配置PrimaryKey，所以在Person的id上配置注解@javax.persistence.Id

@Data
public class Person {
    @javax.persistence.Id @GeneratedValue(generator = "JDBC")
    private Integer id;
    private String name;
    private Integer age;
    private Boolean gender;
}