文章/答案/技术大牛

发布

社区首页 >专栏 >mapper怎么用_当前余额和通用余额

mapper怎么用_当前余额和通用余额

全栈程序员站长

发布于 2022-11-08 07:13:32

1.9K00

代码可运行

文章被收录于专栏：全栈程序员必看全栈程序员必看

运行总次数：0

代码可运行

通用Mapper之TkMapper的使用

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);
}
}