Mybatis之通用Mappper

Mybatis之通用Mappper

  1. 通用 Mapper 介绍
    通用 Mapper 是一个辅助 MyBatis 单表开发的组件,它使我们能极其方便的使用 MyBatis 单表的增删改查。

  2. 快速入门
    2.1 搭建环境
    创建数据库 mapper,和 table_user 表

DROP TABLE IF EXISTS table_user;
CREATE TABLE table_user (
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(20) DEFAULT NULL,
user_gender char(2) DEFAULT ‘未知’,
user_address varchar(30) DEFAULT NULL,
user_birthday date DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;


– Records of table_user


INSERT INTO table_user VALUES (‘1’, ‘张三’, ‘男’, ‘北京’, ‘2020-01-15’);
INSERT INTO table_user VALUES (‘2’, ‘李四’, ‘男’, ‘上海’, ‘2020-01-14’);
INSERT INTO table_user VALUES (‘3’, ‘王五’, ‘女’, ‘广州’, ‘2020-01-12’);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
创建工程

打开 IDEA --> Create New Project --> Empty Project --> 填写项目名 --> Finish
New Module --> Maven --> Next --> 填写项目信息 --> Finish
引入依赖

<?xml version="1.0" encoding="UTF-8"?>


4.0.0

<groupId>com.zt</groupId>
<artifactId>mapper_demo1</artifactId>
<version>1.0-SNAPSHOT</version>

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.1.RELEASE</version>
</parent>

<dependencies>
    <!--jdbc的启动器,默认使用 HikariCP 连接池-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <!--mysql驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <!--mybatis-->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.2</version>
    </dependency>
    <!--SpringBoot-test-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>
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 配置 application.properties

连接四大参数

spring.datasource.url=jdbc:mysql://localhost:3306/mapper?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=123456

1
2
3
4
5
创建引导类

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

1
2
3
4
5
6
7
创建测试类,测试能否获取数据库连接

@RunWith(SpringRunner.class)
@SpringBootTest(classes = MapperApplication.class)
public class ConnectionTest {
@Autowired
private DataSource dataSource;

@Test
public void testGetConnection() throws SQLException {
    Connection connection = dataSource.getConnection();
    System.out.println(connection);
}

}
1
2
3
4
5
6
7
8
9
10
11
12
运行结果:

HikariProxyConnection@737756161 wrapping com.mysql.cj.jdbc.ConnectionImpl@2532b351
1
2.2 集成通用 Mapper
引入依赖

tk.mybatis mapper-spring-boot-starter 2.1.5 1 2 3 4 5 6 创建实体类 User

/**

  • 建立数据库与实体类之间的关系
    */
    @Table(name = “table_user”)
    public class User implements Serializable {
    private Integer userId;
    private String userName;
    private String userGender;
    private String userAddress;
    private Date userBirthday;

    public User() {
    }

    public User(Integer userId, String userName, String userGender, String userAddress, Date userBirthday) {
    this.userId = userId;
    this.userName = userName;
    this.userGender = userGender;
    this.userAddress = userAddress;
    this.userBirthday = userBirthday;
    }

    public Integer getUserId() {
    return userId;
    }

    public void setUserId(Integer userId) {
    this.userId = userId;
    }

    public String getUserName() {
    return userName;
    }

    public void setUserName(String userName) {
    this.userName = userName;
    }

    public String getUserGender() {
    return userGender;
    }

    public void setUserGender(String userGender) {
    this.userGender = userGender;
    }

    public String getUserAddress() {
    return userAddress;
    }

    public void setUserAddress(String userAddress) {
    this.userAddress = userAddress;
    }

    public Date getUserBirthday() {
    return userBirthday;
    }

    public void setUserBirthday(Date userBirthday) {
    this.userBirthday = userBirthday;
    }

    @Override
    public String toString() {
    return “User{” +
    “userId=” + userId +
    “, userName=’” + userName + ‘’’ +
    “, userGender=’” + userGender + ‘’’ +
    “, userAddress=’” + userAddress + ‘’’ +
    “, userBirthday=” + userBirthday +
    ‘}’;
    }
    }

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
创建持久层 UserMapper

/**

  • 具体操作数据库的 Mapper 接口,需要继承通用 mapper 提供的核心接口:Mapper
  • 泛型类型就是实体类的类型
    */
    @org.apache.ibatis.annotations.Mapper
    public interface UserMapper extends Mapper {

}

1
2
3
4
5
6
7
8
9
创建 UserMapper 测试类

@RunWith(SpringRunner.class)
@SpringBootTest(classes = MapperApplication.class)
public class MapperTest {
@Autowired
private UserMapper userMapper;

@Test
public void testSelectOne() {
    User user = new User(null, "张三", null, null, null);
    System.out.println(userMapper.selectOne(user));
}

}

1
2
3
4
5
6
7
8
9
10
11
12
13
运行结果:

User{userId=1, userName=‘张三’, userGender=‘男’, userAddress=‘北京’, userBirthday=Wed Jan 15 00:00:00 CST 2020}
1
3. 常用注解
3.1 @Table
作用:建立实体类和数据库表之间的对应关系。

默认规则:实体类类名首字母小写作为表名。User 类 --> user 表。

实例:

@Table(name = “table_user”)
public class User implements Serializable {
1
2
3.2 @Column
作用:建立实体类字段和数据库表字段之间的对应关系。

默认规则:

实体类字段:驼峰式命名

数据库表字段:使用 “_” 区分各个单词

实例:

@Column(name = “user_id”)
private Integer id;
1
2
3.3 @Id
作用:建立实体类主键字段和数据库表主键字段之间的对应关系。

实例:

@Id
private Integer userId;
1
2
3.4 @GeneratedValue
作用:让通用 Mapper 在执行 insert 操作之后将数据库自动生成的主键值回写到实体类对象中。

实例:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer userId;
1
2
3
@Test
public void testInsert() {
User user = new User(null, “小红”, “女”, “杭州”, new java.util.Date());
userMapper.insert(user);

System.out.println(user);

}
1
2
3
4
5
6
7
运行结果:

User{userId=10, userName=‘小红’, userGender=‘女’, userAddress=‘杭州’, userBirthday=Sat Jan 18 14:52:38 CST 2020}
1
3.5 @Transient
作用:用于标记不与数据库表字段对应的实体类字段。

实例:

@Transient
private String otherThings; //非数据库表中字段
1
2
4. 基础方法
4.1 selectOne 方法
说明:根据实体中的属性进行查询,只能有一个返回值,有多个结果是抛出异常,查询条件使用等号

实例:

@Test
public void testSelectOne() {
User user = new User(null, “张三”, null, null, null);
System.out.println(userMapper.selectOne(user));
}
1
2
3
4
5
运行结果:

User{id=1, userName=‘张三’, userGender=‘男’, userAddress=‘北京’, userBirthday=Wed Jan 15 00:00:00 CST 2020}
1
4.2 select 方法
说明:根据实体中的属性值进行查询,查询条件使用等号

实例:

@Test
public void testSelect() {
User user = new User(null, null, “男”, null, null);
List users = userMapper.select(user);
for (User user1 : users) {
System.out.println(user1);
}
}
1
2
3
4
5
6
7
8
运行结果:

User{userId=1, userName=‘张三’, userGender=‘男’, userAddress=‘北京’, userBirthday=Wed Jan 15 00:00:00 CST 2020}
User{userId=2, userName=‘李四’, userGender=‘男’, userAddress=‘上海’, userBirthday=Tue Jan 14 00:00:00 CST 2020}
User{userId=7, userName=‘小明’, userGender=‘男’, userAddress=‘深圳’, userBirthday=Sat Jan 18 00:00:00 CST 2020}

1
2
3
4
4.3 selectAll 方法
说明:查询全部结果,select(null)方法能达到同样的效果

实例:

@Test
public void testSelectAll() {
List users = userMapper.selectAll();
for (User user : users) {
System.out.println(user);
}
}
1
2
3
4
5
6
7
运行结果:

User{userId=1, userName=‘张三’, userGender=‘男’, userAddress=‘北京’, userBirthday=Wed Jan 15 00:00:00 CST 2020}
User{userId=2, userName=‘李四’, userGender=‘男’, userAddress=‘上海’, userBirthday=Tue Jan 14 00:00:00 CST 2020}
User{userId=3, userName=‘王五’, userGender=‘女’, userAddress=‘广州’, userBirthday=Sun Jan 12 00:00:00 CST 2020}
User{userId=7, userName=‘小明’, userGender=‘男’, userAddress=‘深圳’, userBirthday=Sat Jan 18 00:00:00 CST 2020}

1
2
3
4
5
4.4 selectByPrimaryKey 方法
说明:根据主键字段进行查询,方法参数必须包含完整的主键属性,查询条件使用等号

注意:该方法需要使用 @Id 指定实体类中的主键,否则通用 Mapper 会默认将所有实体类字段作为联合主键

实例:

@Test
public void testSelectByPrimaryKey() {
System.out.println(userMapper.selectByPrimaryKey(3));
}
1
2
3
4
运行结果:

User{userId=3, userName=‘王五’, userGender=‘女’, userAddress=‘广州’, userBirthday=Sun Jan 12 00:00:00 CST 2020}
1
4.5 selectCount 方法
说明:根据实体中的属性查询总数,查询条件使用等号

实例:

@Test
public void testSelectCount() {
User user = new User(null, null, “男”, null, null);
System.out.println(userMapper.selectCount(user));
}
1
2
3
4
5
运行结果:

3
1
4.6 insert 方法
说明:保存一个实体,null 的属性也会保存,不会使用数据库默认值

实例:

@Test
public void testInsert() {
User user = new User(null, “小红”, “女”, “杭州”, new java.util.Date());
userMapper.insert(user);
System.out.println(userMapper.selectOne(new User(null,“小红”,null,null,null)));
}
1
2
3
4
5
6
运行结果:

User{userId=14, userName=‘小红’, userGender=‘女’, userAddress=‘杭州’, userBirthday=Sat Jan 18 00:00:00 CST 2020}
1
4.7 insertSelective 方法
说明:保存一个实体,null 的属性不会保存,会使用数据库默认值

实例:

@Test
public void testInsertSelective() {
User user = new User(null, “小美”, null, “南京”, new java.util.Date());
userMapper.insertSelective(user);
System.out.println(userMapper.selectOne(new User(null,“小美”,null,null,null)));
}
1
2
3
4
5
6
运行结果:

User{userId=15, userName=‘小美’, userGender=‘未知’, userAddress=‘南京’, userBirthday=Sat Jan 18 00:00:00 CST 2020}
1
4.8 updateByPrimaryKey 方法
说明:根据主键更新实体全部字段,null 值会被更新

实例:

@Test
public void testUpdateByPrimaryKey() {
User user = new User(3, “建国”, null, null, null);
userMapper.updateByPrimaryKey(user);
System.out.println(userMapper.selectOne(new User(3, null, null, null, null)));
}
1
2
3
4
5
6
运行结果:

User{userId=3, userName=‘建国’, userGender=‘null’, userAddress=‘null’, userBirthday=null}

1
2
4.9 updateByPrimaryKeySelective 方法
说明:根据主键更新属性不为 null 的值

实例:

@Test
public void testUpdateByPrimaryKeySelective() {
User user = new User(2, “建军”, null, null, null);
userMapper.updateByPrimaryKeySelective(user);
System.out.println(userMapper.selectOne(new User(2, null, null, null, null)));
}
1
2
3
4
5
6
运行结果:

User{userId=2, userName=‘建军’, userGender=‘男’, userAddress=‘上海’, userBirthday=Tue Jan 14 00:00:00 CST 2020}
1
4.10 delete 方法
说明:根据实体属性作为条件进行删除,查询条件使用等号

注意:如果所有实体属性都为空,会删除表中所有记录

实例:

@Test
public void testDelete() {
User user = new User(null, “小美”, null, null, null);
userMapper.delete(user);
}
1
2
3
4
5
4.11 deleteByPrimaryKey 方法
说明:根据主键字段进行删除,方法参数必须包含完整的主键属性

实例:

@Test
public void testDeleteByPrimaryKey() {
userMapper.deleteByPrimaryKey(14);
}
1
2
3
4
5. Example 方法
Mybatis 的逆向工程中会生成实例及实例对应的 example,example 用于添加条件,相当 where 后面的部分

5.1 selectByExample 方法
说明:根据 Example 条件进行查询

实例:

/**

  • 相当于:select * from table_user where (user_address = “北京”) or (user_name like “%王%”);
    */
    @Test
    public void testSelectByExample() {
    // 1. 创建 Example 对象
    Example example = new Example(User.class);
    // 2. 通过 Example 对象创建 Criteria 对象
    Example.Criteria criteria1 = example.createCriteria();
    Example.Criteria criteria2 = example.createCriteria();

    // 3. 在两个 Criteria 对象中分别设置查询条件
    criteria1.andEqualTo(“userAddress”,“北京”);
    criteria2.andLike(“userName”,"%王%");

    // 4. 使用 or 关键词组装两个 Criteria 对象
    example.or(criteria2);

    // 5. 执行查询
    List users = userMapper.selectByExample(example);
    for (User user : users) {
    System.out.println(user);
    }
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    运行结果:

User{userId=1, userName=‘张三’, userGender=‘男’, userAddress=‘北京’, userBirthday=Wed Jan 15 00:00:00 CST 2020}
User{userId=3, userName=‘王五’, userGender=‘女’, userAddress=‘广州’, userBirthday=Sun Jan 12 00:00:00 CST 2020}
1
2
5.2 selectCountByExample 方法
说明:根据 Example 条件进行查询总数

实例:

/**

  • 相当于:select COUNT(*) from table_user where user_gender = “男”;
    */
    @Test
    public void testSelectCountByExample() {
    Example example = new Example(User.class);
    Example.Criteria criteria = example.createCriteria();

    criteria.andEqualTo(“userGender”, “男”);

    System.out.println(userMapper.selectCountByExample(example));
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    运行结果:

3
1
5.3 updateByExample 方法
说明:根据 Example 条件更新实体包含的全部属性,null 值会被更新

实例:

/**

  • 相当于:update table_user set user_name = “小红”,user_gender = null,user_address = null,user_birthday = null where user_id = 7;
    */
    @Test
    public void testUpdateByExample() {
    User user = new User(null, “小红”, null, null, null);
    Example example = new Example(User.class);
    Example.Criteria criteria = example.createCriteria();
    criteria.andEqualTo(“userId”, 7);

    userMapper.updateByExample(user, example);
    System.out.println(userMapper.selectOne(user));
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    运行结果:

User{userId=7, userName=‘小红’, userGender=‘null’, userAddress=‘null’, userBirthday=null}
1
5.4 updateByExampleSelective 方法
说明:根据 Example 条件更新实体包含的不是 null 的属性值

实例:

/**

  • 相当于:update table_user set user_name = “小花” where user_id = 3;
    */
    @Test
    public void testUpdateByExampleSelective() {
    User user = new User(null, “小花”, null, null, null);
    Example example = new Example(User.class);
    Example.Criteria criteria = example.createCriteria();
    criteria.andEqualTo(“userId”, 3);

    userMapper.updateByExampleSelective(user,example);
    System.out.println(userMapper.selectOne(user));
    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    运行结果:

User{userId=3, userName=‘小花’, userGender=‘女’, userAddress=‘广州’, userBirthday=Sun Jan 12 00:00:00 CST 2020}
1
5.5 deleteByExample 方法
说明:根据 Example 条件删除数据

实例:

/**

  • 相当于:delete from table_user where user_name = “小红”;
    */
    @Test
    public void testDeleteByExample() {
    Example example = new Example(User.class);
    Example.Criteria criteria = example.createCriteria();
    criteria.andEqualTo(“userName”,“小红”);

     userMapper.deleteByExample(example);
    

    }

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值