通用Mapper是一个可以实现任意MyBatis通用方法的框架,提供了常规的增删改查操作以及Example相关的单表操作。解决MyBatis使用中90%的基本操作,使用它可以很方便的进行开发,可以节省开发人员大量的时间。
在线官方文档:https://gitee.com/free/Mapper/wikis/Home
在mybatis依赖的基础上,添加通用Mapper的依赖
<!--通用Mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>4.1.5</version>
</dependency>
替换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>
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);
}
}
}
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);
}
根据主键查询:
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);
}
根据主键修改数据,不忽略空值:
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);
}
根据主键删除:
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);
}
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.0</version>
</dependency>
<!-- 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>
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;
}
分页插件配置好后,直接调用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);
}
<!--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>
修改启动类的@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);
}
}
配置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
tkMapper完成了对单表的通用操作的封装,封装在Mapper接口和MySqlMapper接口
要完成对单表的操作,只需自定义Mapper接口继承Mapper接口和MySqlMapper接口
public interface UserMapper extends Mapper<User>, MySqlMapper<User> {
}
@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);
}
}
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/185287.html原文链接:https://javaforall.cn