通用Mapper之TkMapper的使用

CodeDevMaster

已于 2022-06-24 14:35:13 修改

阅读量3.5k

点赞数 2

分类专栏： # Mybatis 文章标签： mybatis

于 2021-06-20 13:29:56 首次发布

本文链接：https://blog.csdn.net/qq_38628046/article/details/109107845

版权

Mybatis 专栏收录该内容

10 篇文章 2 订阅

订阅专栏

1.通用Mapper概述

通用Mapper是一个可以实现任意MyBatis通用方法的框架，提供了常规的增删改查操作以及Example相关的单表操作。解决MyBatis使用中90%的基本操作，使用它可以很方便的进行开发，可以节省开发人员大量的时间。

在线官方文档：https://gitee.com/free/Mapper/wikis/Home

2.Spring整合TkMapper

1.引入依赖包

在mybatis依赖的基础上，添加通用Mapper的依赖

<!--通用Mapper-->
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper</artifactId>
    <version>4.1.5</version>
</dependency>

2.进行配置

替换MyBatis集成Spring的包扫描bean

 <!-- 配置扫描dao的包 -->
    <bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.ybzy.dao"/>
    </bean>

    <!--集成通用Mapper-->
    <bean class="tk.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.ybzy.dao"/>
        <!--注册通用Mapper,该接口包含通用的增删改查-->
        <property name="properties">
            <prop key="mappers">tk.mybatis.mapper.common.Mapper</prop>
        </property>
    </bean>

3.测试通用Mapper

public interface UserMapper extends Mapper<User> {
 
}

继承 tk.mybatis.mapper.common.Mapper 接口后，在接口上指定泛型类型 User。当继承了Mapper 接口后，此时就已经有了针对User的大量方法，如下：
在这里插入图片描述
自定义方法，手写SQL

public class MapperTest {
    private ApplicationContext ac;
    @Before
    public void init(){
        ac = new ClassPathXmlApplicationContext("classpath:spring.xml");
    }
    
    @Test
    public void testSpring(){
      UserMapper  userMapper = ac.getBean(UserMapper.class);
        List<User> userList= userMapper.getAllUser();
        for (User user : userList) {
            System.out.println("user = " + user);
        }
    }
}

3.通用Mapper的CURD

1.增加操作

insert，不忽略空值，空值插入时为null ：

INSERT INTO tb_user ( id,name,phone) VALUES( ?,?,? )

    @Test
    public void testInsert(){
        User user= new User();
        brand.setName("小白");
        userMapper.insert(user);
    }

insertSelective,忽略空值，只插入非空的值 ：

INSERT INTO tb_user ( name ) VALUES( ? )

    @Test
    public void testInsertSelective(){
        User user = new User();
        user.setName("小黑");
        userMapper.insertSelective(user);
    }

2.查询操作

 根据主键查询：
 
 SELECT id,name,phone FROM tb_user  WHERE id = ?

@Test
public void testSelectByPrimaryKey(){
    long id = 2L;
    User user = userMapper.selectByPrimaryKey(id);
    System.out.println(user );
}

根据Example条件查询：

SELECT id,name,phone FROM tb_user WHERE ( id in ( ? , ? , ? , ? ) )

 @Test
    public void testExample(){
        Example example = new Example(User.class);
        Example.Criteria criteria = example.createCriteria();
        List<Long> ids = new ArrayList<Long>();
        ids.add(1L);
        ids.add(2L);
        ids.add(3L);
        //第二个参数是集合对象，这里对应的类型虽然是Object，但是要和数据库对应的类型保持一致
        criteria.andIn("id",ids);
        
        List<User> users = userMapper.selectByExample(example);
        for (User user : users) {
            System.out.println(user);
        }
    }

根据JavaBean条件查询：

SELECT id,name,phone FROM tb_user WHERE id = ? AND name = ?

@Test
public void testSelect(){
    User user= new User();
    user.setId(5L);
    user.setName("大白白");
    //把User作为查询条件，会忽略空值
    List<User> users= brandMapper.select(brand);
    for (User user: users) {
        System.out.println(user);
    }
}

查询单条记录：

SELECT id,name,phone FROM tb_user WHERE id = ? AND name = ? 

注意：符合该条件的数据必须<=1条，如果大于了1条数据，则会报错

TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2

  @Test
    public void testSelectOne(){
        User user = new User();
        user.setId( 3L);
        User userinfo = userMapper.selectOne(user);

        System.out.println(userinfo);
    }

查询所有：
	
SELECT id,name,phone FROM tb_user

    @Test
    public void testSelectAll(){
        //执行查询
        List<User> users = userMapper.selectAll();

        for (User user : users) {
            System.out.println(user);
        }
    }

统计查询：

SELECT COUNT(id) FROM tb_user

  @Test
    public void testSelectCount(){
        //查询总记录数
        int count = userMapper.selectCount(null);
        System.out.println(count);
    }

3.修改操作

根据主键修改数据，不忽略空值：

UPDATE tb_user SET name = ?,phone= ? WHERE id = ?

    @Test
    public void testUpdateByPrimaryKey(){
        User user = new User();
        user.setId(5L);
        user.setName("Name");
        int count = userMapper.updateByPrimaryKey(user);
        System.out.println(count);
    }

根据主键修改数据，忽略空值：

UPDATE tb_user SET name = ? WHERE id = ?

  @Test
    public void testUpdateByPrimaryKeySelective(){
        User user = new User();
        user.setId(5L);
        user.setName("Name");
        int count = userMapper.updateByPrimaryKeySelective(user);
        System.out.println(count);
    }

构造条件修改数据，不忽略空值：

UPDATE tb_user  SET name = ?,phone= ? WHERE ( phone= ? )

这里phone为空，SQL语句仍然会执行修改操作。

    @Test
    public void testUpdateByExample(){
        User user = new User();
        user.setName("白白");
        //创建Example对象
        Example example = new Example(User.class);
        //Criteria 用来构造约束条件
        Example.Criteria criteria = example.createCriteria();

        //第一个参数是User对应的属性，第二个参数是属性约束值   相当于 where phone='6666'
        criteria.andEqualTo("phone","6666");

        //条件修改数据
        int count = userMapper.updateByExample(user,example);
        System.out.println(count);
    }

构造条件修改数据，忽略空值：

UPDATE tb_user SET phone= ? WHERE ( name = ? ) 

这里name为空，SQL语句不会做出修改操作

    @Test
    public void testUpdateByExampleSelective(){
        User user = new User();
        user.setPhone("5555");
        //创建Example对象
        Example example = new Example(Brand.class);
        //Criteria构造约束条件
        Example.Criteria criteria = example.createCriteria();

        /**
         * 1.User对应的属性
         * 
         * 2.属性约束值,相当于 where name='白白'
         */
        criteria.andEqualTo("name","白白");

        int count = userMapper.updateByExampleSelective(user,example);
        System.out.println(count);
    }

4.删除操作

根据主键删除：
	
DELETE FROM tb_user WHERE id = ?

   @Test
    public void testDeleteByPrimaryKey(){
        Long id=1L;
        userMapper.deleteByPrimaryKey(id);
    }

Example条件删除：

DELETE FROM tb_user WHERE ( id between ? and ? )

    @Test
    public void testDeleteByExample(){
        Example example = new Example(User.class);
        Example.Criteria criteria = example.createCriteria();

        List<Long> ids = new ArrayList<Long>();
        ids.add(2L);
        ids.add(5L);
        criteria.andIn("id",ids);

        userMapper.deleteByExample(example);
    }

JavaBean条件删除：

DELETE FROM tb_user WHERE name = ?

    @Test
    public void testDelete(){
        User user = new User();
        user.setName("白白");
        userMapper.delete(user);
    }

4.分页插件pagehelper

1.引入依赖

 <dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.0</version>
</dependency>

2.配置mybatis拦截器

    <!-- SqlSessionFactoryBean -->
    <bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <!--添加插件，拦截器插件-->
        <property name="plugins">
            <array>
                <bean class="com.github.pagehelper.PageInterceptor">
                    <property name="properties">
                        <props>
                            <!--数据库方言：选择数据库类型-->
                            <prop key="helperDialect">mysql</prop>
                            <!--分页合理化参数-->
                            <prop key="reasonable">true</prop>
                        </props>
                    </property>
                </bean>
            </array>
        </property>
    </bean>

3.分页类PageInfo

public class PageInfo<T> implements Serializable {
    private static final long serialVersionUID = 1L;
    //当前页
    private int pageNum;
    //每页的数量
    private int pageSize;
    //当前页的数量
    private int size;
    //当前页面第一个元素在数据库中的行号
    private int startRow;
    //当前页面最后一个元素在数据库中的行号
    private int endRow;
    //总记录数
    private long total;
    //总页数
    private int pages;
    //结果集
    private List<T> list;
    //前一页
    private int prePage;
    //下一页
    private int nextPage;
    //是否为第一页
    private boolean isFirstPage = false;
    //是否为最后一页
    private boolean isLastPage = false;
    //是否有前一页
    private boolean hasPreviousPage = false;
    //是否有下一页
    private boolean hasNextPage = false;
    //导航页码数
    private int navigatePages;
    //所有导航页号
    private int[] navigatepageNums;
    //导航条上的第一页
    private int navigateFirstPage;
    //导航条上的最后一页
    private int navigateLastPage;
    }

4.使用分页插件

分页插件配置好后，直接调用PageHelper的静态方法startPage即可实现分页，在调用startPage的方法时，该方法必须写在执行查询selectAll()前面，否则分页无效。

    @Test
    public void testSelectAll(){
         //page 当前页    size 每页显示多少条
   		 int page = 1,size=10;
   		 //分页处理,只需要调用PageHelper.startPage静态方法即可
        PageHelper.startPage(page ,size);
		//查询
        List<User> users = userMapper.selectAll();
        for (User user : users) {
            System.out.println(user);
        }

        //获取分页信息
        PageInfo<User> pageInfo = new PageInfo<User>(users);
        System.out.println(pageInfo);
    }

5.SpringBoot整合TkMapper

1.添加依赖

		<!--mybatis-->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.4</version>
		</dependency>
		<!--通用mapper-->
		<dependency>
			<groupId>tk.mybatis</groupId>
			<artifactId>mapper-spring-boot-starter</artifactId>
			<version>2.1.5</version>
		</dependency>
		<!--pagehelper 分页插件-->
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.3.0</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

2.启动类添加@MapperScan

修改启动类的@MapperScan注解的包

import tk.mybatis.spring.annotation.MapperScan;

@SpringBootApplication
@MapperScan("cn.ybzy.demo.dao")
public class DemoApplication {

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

3.配置application.yml

配置MyBatis

mybatis:
  # type-aliases扫描路径
  type-aliases-package: cn.ybzy.demo.model
  # mapper xml实现扫描路径
  mapper-locations: classpath:mapper/*.xml

配置通用Mapper

通用Mapper配置参考: https://gitee.com/free/Mapper/wikis

#mappers 多个接口时使用逗号隔开
mapper:
  mappers:  cn.ybzy.demo.dao
  not-empty: false
  identity: MYSQL

配置PageHelper

PageHelper项目地址: https://github.com/pagehelper/Mybatis-PageHelper

pagehelper: 
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql

4.创建Mapper接口

tkMapper完成了对单表的通用操作的封装，封装在Mapper接口和MySqlMapper接口

要完成对单表的操作，只需自定义Mapper接口继承Mapper接口和MySqlMapper接口

public interface UserMapper extends Mapper<User>, MySqlMapper<User> {
}

5.测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication .class)
public class UserMapperTest {

    @Autowired
    private UserMapper userMapper ;

    @Test
    public void test(){
        User user = new User();
        user.setUsername("小白");
        user.setPassword("123");
        int i = userMapper .insert(user);
        System.out.println(i);
    }

	/**
	 * 使用PageHelper进行分页
	 */
	@Test
    public void test1(){
        //带条件分页
        Example example = new Example(User.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("status",0);
        //分页
        int pageNum = 1;
        int pageSize = 3;
        int start = (pageNum-1)*pageSize;
        RowBounds rowBounds = new RowBounds(start,pageSize);

        List<User> userList= userMapper.selectByExampleAndRowBounds(example,rowBounds);
        for (User user: userList) {
            System.out.println(user);
        }

        //查询总记录数（满足条件）
        int i = userMapper.selectCountByExample(example);
        System.out.println(i);
    }
    
	/**
	 * 使用PageHelper进行分页
	 */
	@Test
	public void test2(){
		PageHelper.startPage(1, 3);
		List<User> list = userMapper.selectAll();
		PageInfo<User> pageInfo = new PageInfo<User>(list);
		List<User> result = pageInfo.getList();
		for (User u : result) {
			System.out.println(u.toString());
		}
	}

	/**
	 * 关联查询
	 * 所有的关联查询都可以通过多个单表操作实现
	 * 查询用户同时查询订单
	 */
	@Test
    public void test3(){
	    //根据用户名查询用户
		Example example = new Example(User.class);
		Example.Criteria criteria = example.createCriteria();
		criteria.andEqualTo("username","小白");
		//先根据用户名查询用户信息
		List<User> users = userMapper.selectByExample(example);
		User user = users.get(0);
		
		//再根据用户id到订单表查询订单
		Example example1 = new Example(Order.class);
		Example.Criteria criteria1 = example1.createCriteria();
		criteria1.andEqualTo("userId",user.getUserId());
		
		List<Orders> ordersList = userMapper.selectByExample(example1);
		 for (Order order: ordersList) {
            System.out.println(order);
        }
		System.out.println(user);
    }
}