Mybatis基本应用

导入依赖

1
2
3
4
5
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>

application.yaml中配置数据

1
2
3
4
5
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.demo.entity
  configuration:
    map-underscore-to-camel-case: true

Xml配置Mybatis

编写Mapper

1
2
3
4
5
6
7
8
9
@Mapper
@Repository
public interface UserMappers {
    public int saveUser(User user);
    public List<User> getAllUser();
    public int updateUser(User user);
    public int deleteUser(int id);
    public User selectById(@PathVariable("id") int id);
}

classpath中配置元数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?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.demo.mapper.UserMappers">
    <select id="selectById" parameterType="int" resultType="User">
        select * from user where id = #{id}
    </select>
    <select id="getAllUser" resultType="User">
        select * from user
    </select>
    <update id="updateUser" parameterType="User">
        update user set username = #{username},email = #{email},is_active=#{isActive},birthday=#{birthday} where id = #{id}
    </update>
    <insert id="saveUser" parameterType="User">
        insert into user (username,email,birthday,is_active) values (#{username},#{email},#{birthday},#{isActive})
    </insert>
    <delete id="deleteUser" parameterType="int">
        delete from user where id = #{id}
    </delete>
</mapper>

测试一下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Autowired
private UserMapper userMappers;
@Test
void insertOnes(){
    User user = new User();
    user.setUsername("13125214254");
    user.setBirthday("2005-3-5");
    user.setEmail("822807553@qq.com");
    user.setIsActive(1);
    int i = userMappers.saveUser(user);
    if (i > 0 ){
        System.out.println("添加成功!");
    }else{
        System.out.println("添加失败!");
    }
}

注解配置Mybatis

编写Mapper并配置Mybatis

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Mapper
@Repository
public interface UserMapper {

    @Insert("insert into user(username,email,birthday,is_active) values(#{username},#{email},#{birthday},#{isActive})")
    public int saveUser(User user);

    @Select("select * from user")
    public List<User> getAllUser();

    @Update("update user set username = #{username},email=#{email},birthday=#{birthday},is_active=#{isActive}")
    public int updateUser(User user);

    @Delete("delete from user where id =#{id}")
    public int deleteUser(int id);

    @Select("select * from user where id = #{id}")
    public User selectById(Integer id);
}

测试一下

1
2
3
4
5
6
7
8
9
@Autowired
private UserMapper userMapper;
@Test
void selectAll(){
    List<User> allUser = userMapper.getAllUser();
    for (User u : allUser){
        System.out.println(u);
    }
}

分页查询

编写dao

1
2
3
4
5
6
7
@Mapper
@Repository
public interface PositionDao {
    @Select("select * from user limit #{curPage},#{pageSize}")
    public List<User> getAllPosition(@Param("curPage")Integer curPage,@Param("pageSize")Integer pageSize);
}

编写service接口

1
2
3
public interface PositionService {
    public List<User> getAllPosition(Integer curPage, Integer pageSize);
}

实现service接口

1
2
3
4
5
6
7
8
9
@Service
public class PositionServiceImp implements PositionService{
    @Autowired
    private PositionDao positionDao;
    @Override
    public List<User> getAllPosition(Integer curPage, Integer pageSize) {
        return positionDao.getAllPosition(curPage,pageSize);
    }
}

编写controller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Autowier
private PositionService positionService
///////////////////////
//省略部分代码
///////////////////////
@GetMapping("/position")
    public Object getAllPositionLimit(@RequestParam("curPage")Integer curPage,@RequestParam("pageSize")Integer pageSize){
        Map<String,Object> map = new HashMap<>();
        List<User> list = positionService.getAllPosition((curPage-1)*pageSize,pageSize);
        if(list!=null&&list.size()>0){
            map.put("code",200);
            map.put("msg","查询成功");
            map.put("data",list);
        }else{
            map.put("code",100);
            map.put("msg","暂时没有数据");
        }
        return map;
    }

测试一下

运行springboot访问position路径并且携带curPage和pageSize数据(http://localhost:8080/position?curPage=1&pageSize=5)

Mybatis分页插件

Mybatis 一对一

创建实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
@Component
public class Emp {
    private int id;
    private String username;
    private String password;
    private String name;
    private int gender;
    private String image;
    private String job;
    private String entrydate;
    private int deptId;
    private String createTime;
    private String updateTime;
    private Dept dept;
    private Company company;

    public Company getCompany() {
        return company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", name='" + name + '\'' +
                ", gender=" + gender +
                ", image='" + image + '\'' +
                ", job='" + job + '\'' +
                ", entrydate='" + entrydate + '\'' +
                ", deptId=" + deptId +
                ", createTime='" + createTime + '\'' +
                ", updateTime='" + updateTime + '\'' +
                ", dept=" + dept +
                ", company=" + company +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getGender() {
        return gender;
    }

    public void setGender(int gender) {
        this.gender = gender;
    }

    public String getImage() {
        return image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public String getEntrydate() {
        return entrydate;
    }

    public void setEntrydate(String entrydate) {
        this.entrydate = entrydate;
    }

    public int getDeptId() {
        return deptId;
    }

    public void setDeptId(int deptId) {
        this.deptId = deptId;
    }

    public String getCreateTime() {
        return createTime;
    }

    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }

    public String getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(String updateTime) {
        this.updateTime = updateTime;
    }
}

创建mapper文件

1
2
3
4
5
@Mapper
@Repository
public interface EmpMapper {
    public List<Emp> selectAll();
}

创建mapper配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?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.demo.mapper.EmpMapper">
    <resultMap id="All" type="com.example.demo.entity.Emp">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="name" property="name"></result>
        <result column="gender" property="gender"></result>
        <result column="image" property="image"></result>
        <result column="job" property="job"></result>
        <result column="entrydate" property="entrydate"></result>
        <result column="dept_id" property="deptId"></result>
        <result column="create_time" property="createTime"></result>
        <result column="update_time" property="updateTime"></result>
        <association property="dept" javaType="com.example.demo.entity.Dept">
            <id column="did" property="id"></id>
            <result column="dname" property="name"></result>
            <result column="dcreate_time" property="createTime"></result>
            <result column="dupdate_time" property="updateTime"></result>
        </association>
        <association property="company" javaType="com.example.demo.entity.Company">
            <id column="cid" property="id"></id>
            <result column="deptment" property="deptment"></result>
        </association>
    </resultMap>
    <select id="selectAll" resultMap="All">
        select e.id,e.username,e.`password`,e.`name`,e.gender,e.image,e.job,e.entrydate,e.dept_id,e.update_time,d.id as did,d.`name` as dname,d.create_time as dcreate_time,d.update_time as dupdate_time,c.id as cid,c.deptment from emp e,dept d,company c where e.job = d.id and c.id = e.dept_id
    </select>
</mapper>

Mybatis 一对多

创建实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
@Component
public class Dept {
    private int id;
    private String name;
    private String createTime;
    private String updateTime;
    private List<Emp> emps;

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", createTime='" + createTime + '\'' +
                ", updateTime='" + updateTime + '\'' +
                ", emps=" + emps +
                '}';
    }

    public List<Emp> getEmps() {
        return emps;
    }

    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCreateTime() {
        return createTime;
    }

    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }

    public String getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(String updateTime) {
        this.updateTime = updateTime;
    }
}

创建mapper文件

1
2
3
4
5
@Mapper
@Repository
public interface DeptMapper {
    public List<Dept> selectAll();
}

创建mapper配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?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.demo.mapper.DeptMapper">
    <resultMap id="All" type="com.example.demo.entity.Dept">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="create_time" property="createTime"></result>
        <result column="update_time" property="updateTime"></result>
        <collection property="emps" ofType="com.example.demo.entity.Emp">
            <id column="eid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="ename" property="name"></result>
            <result column="gender" property="gender"></result>
            <result column="image" property="image"></result>
            <result column="job" property="job"></result>
            <result column="entrydate" property="entrydate"></result>
            <result column="dept_id" property="deptId"></result>
            <result column="ecreate_time" property="createTime"></result>
            <result column="eupdate_time" property="updateTime"></result>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="All">
        select d.id,d.name,d.create_time,d.update_time,e.id as eid,e.username,e.`password`,e.`name` as ename,e.gender,e.image,e.job,e.entrydate,e.dept_id,e.create_time as ecreate_time,e.update_time as eupdate_time  from dept d,emp e where d.id = e.dept_id
    </select>
</mapper>

Mybatis-plus

导入依赖

1
2
3
4
5
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.2</version>
</dependency>

创建接口

1
2
3
@Mapper
public interface CompanyMapper extends BaseMapper<Company> {
}

介绍BaseMapper接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

public interface BaseMapper<T> extends Mapper<T> {
    int insert(T entity);

    int deleteById(Serializable id);

    int deleteByMap(@Param("cm") Map<String, Object> columnMap);

    int delete(@Param("ew") Wrapper<T> queryWrapper);

    int deleteBatchIds(@Param("coll") Collection<? extends Serializable> idList);

    int updateById(@Param("et") T entity);

    int update(@Param("et") T entity, @Param("ew") Wrapper<T> updateWrapper);

    T selectById(Serializable id);

    List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList);

    List<T> selectByMap(@Param("cm") Map<String, Object> columnMap);

    T selectOne(@Param("ew") Wrapper<T> queryWrapper);

    Integer selectCount(@Param("ew") Wrapper<T> queryWrapper);

    List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);

    List<Map<String, Object>> selectMaps(@Param("ew") Wrapper<T> queryWrapper);

    List<Object> selectObjs(@Param("ew") Wrapper<T> queryWrapper);

    <E extends IPage<T>> E selectPage(E page, @Param("ew") Wrapper<T> queryWrapper);

    <E extends IPage<Map<String, Object>>> E selectMapsPage(E page, @Param("ew") Wrapper<T> queryWrapper);
}

继承该接口可直接使用该接口里的方法