CRUD操作
application.properties以及log4j.properties与上述一直,因此不再贴入;
User对象(@TableField注解挺有意思的,有想了解的朋友可百度)
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("user")
public class User {
/** id */
@TableId(type = IdType.AUTO)
private Long id;
/** name */
private String name;
/** age */
private Integer age;
/** email */
@TableField(value = "email",select = false)//value:解决字段名不一致的问题.select:查询不返回字段的值
private String mail;
@TableField(exist = false)//该字段在数据库表中不存在
private String address;
}
UserMapper
public interface UserMapper extends BaseMapper<User> {
}
test(select)
selectList
@RunWith(SpringRunner.class)
@SpringBootTest
class YunMpSpringbootApplicationTests {
@Resource
private UserMapper mapper;
@Test
void selectList() {
List<User> users = mapper.selectList(null);
users.forEach(System.out::println);
}
}
运行结果
User(id=1, name=Jone, age=18, mail=null, address=null)
User(id=2, name=Jack, age=20, mail=null, address=null)
User(id=3, name=Tom, age=28, mail=null, address=null)
User(id=4, name=Sandy, age=21, mail=null, address=null)
User(id=5, name=Billie, age=24, mail=null, address=null)
User(id=6, name=wsy, age=35, mail=null, address=null)
selectById
@Test
void selectById(){
User user = mapper.selectById(1l);
System.out.println(user);
}
selectBatchIds
@Test
void selectBatchIds(){
List<User> userList = mapper.selectBatchIds(Arrays.asList(1l, 2l));
userList.forEach(System.out::println);
}
selectOne
@Test
void selectOne(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name","Jone");
//根据条件查询一条记录, 如果查询结果超过一条,则会报错
User user = mapper.selectOne(queryWrapper);
System.out.println(user);
}
selectCount
@Test
void selectCount(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//查询年林大于25岁的人的总条数
queryWrapper.gt("age",20);
Long count = mapper.selectCount(queryWrapper);
List<User> userList = mapper.selectList(queryWrapper);
System.out.println(count);
userList.forEach(System.out::println);
}
test(add)
@Test
void testAdd(){
User user = new User();
user.setName("wsy");
user.setAge(10);
user.setMail("wsy@123.com");
int result = mapper.insert(user);
System.out.println(result);
System.out.println("id值为:"+user.getId());
}
test(update)
update分为两种,一种是根据id查询,一种是根据条件查询
根据id查询:
@Test
void updateById(){
//1,更新的字段
User user = new User();
user.setId(6l);
user.setAge(35);
int i = mapper.updateById(user);
System.out.println("修改的行数:"+i);
根据条件查询(QueryWrapper):
@Test
void update(){
//1,更新的字段
User user = new User();
user.setAge(35);
//2,更新的条件
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name","wsy");
int i = mapper.update(user, queryWrapper);
System.out.println("修改的行数:"+i);
}
根据条件查询(UpdateWrapper):
@Test
void update(){
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.eq("id",6).set("age",40);
int i = mapper.update(null, updateWrapper);
System.out.println("修改的行数:"+i);
}
test(delete)
deleteById
@Test
void deleteById(){
int i = mapper.deleteById(6l);
System.out.println("修改的行数:"+i);
}
deleteByMap
@Test
void deleteByMap(){
HashMap<String, Object> map = new HashMap<>();
map.put("name","wsy1");
map.put("age",10);
//将columnMap中的元素设置为删除条件,多个条件是and的关系,
int i = mapper.deleteByMap(map);
System.out.println("修改的行数:"+i);
}
delete
@Test
void delete(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name","wsy2").eq("age",10);
int i = mapper.delete(queryWrapper);
System.out.println("修改的行数:"+i);
}
也可以写成
@Test
void delete(){
User user = new User();
user.setName("wsy2");
user.setAge(10);
QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
// queryWrapper.eq("name","wsy2").eq("age",10);
int i = mapper.delete(queryWrapper);
System.out.println("修改的行数:"+i);
}
deleteBatchIds 进行批量删除
@Test
void deleteBatchIds(){
int i = mapper.deleteBatchIds(Arrays.asList(11l,12l));
System.out.println("修改的行数:"+i);
}
分页查询
方法定义
/**
* 根据 entity 条件,查询全部记录(并翻页)
*
* @param page 分页查询条件(可以为 RowBounds.DEFAULT)
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
<P extends IPage<T>> P selectPage(P page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
@Configuration
public class MybatisPlusConfig {
/**
* 分页插件
* @return
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
/**
* 分页查询
*/
@Test
void PageSelect(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("age","18");
//第一个参数当前页,第二个参数,每页显示条数
Page<User> page = new Page<>(1, 2);
IPage<User> userIPage = mapper.selectPage(page, queryWrapper);
System.out.println("总条数:" + userIPage.getTotal());
System.out.println("总页数:" + userIPage.getPages());
System.out.println("分页数据:" + userIPage.getRecords());
}
条件构建器
在MP中,Wrappper接口的实现类有AbstractWrapper和AbstractChainWrapper,我们重点看一下AbstractWrapper
学习这三个之前,咱们先看一下allEq
allEq(Map<R,V> params)
allEq(Map<R,V>,boolean null2IsNull)
allEq(boolean condition,Map<R,V> params,boolean null2IsNull)
示例
allEq(Map<R,V> params)
@Test
void testAllEq(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//构建map
Map<String, String> map = new HashMap<>();
map.put("name","jack");
map.put("age",null);
//where name = ? and age is null
queryWrapper.allEq(map);
List<User> users = mapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
allEq(Map<R,V>,boolean null2IsNull)
@Test
void testAllEq(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//构建map
Map<String, String> map = new HashMap<>();
map.put("name","jack");
map.put("age",null);
//where name = ?
queryWrapper.allEq(map,false);
List<User> users = mapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
allEq(boolean condition,Map<R,V> params,boolean null2IsNull)
boolean condition==>表示该条件是否加入最后生成的sql中
@Test
void testAllEq(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//构建map
Map<String, String> map = new HashMap<>();
map.put("name","jack");
map.put("age",null);
//第一个false表示没有where条件生成,相当于select * from user
queryWrapper.allEq(false,map,false);
List<User> users = mapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
void testAllEq(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//构建map
Map<String, String> map = new HashMap<>();
map.put("name","jack");
map.put("age",null);
//where age is null
queryWrapper.allEq((k,v) -> k.equals("name"),map);
List<User> users = mapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
条件构造器比较
基本比较操作
-
eq --> =
-
ne --> <>
-
gt --> >
-
ge --> >=
-
lt --> <
-
le --> <=
-
between --> between 值1 and 值2
-
notbetweem --> not between 值1 and 值2
-
in --> in(v1,v2)
-
notln --> not in (v1,b2)
-
like --> like '%V%'
-
notLike --> not like '%V%'
-
likeLife --> like '%V'
-
li'keRight --> like 'V%'
-
orderBy
-
orderByAes
-
orderByDesc
-
or --> 拼接or
-
and and嵌套
示例
@Test
void testWrapper(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//where email = ? and age >= ? and name in(?,?)
queryWrapper.eq("email","admin@wsy.com")
.ge("age",30)
.in("name","jack","rose");
List<User> users = mapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
void testWrapper2(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//where email = ? or age = ?
queryWrapper.eq("email","admin@wsy.com")
.or()
.eq("age",20);
List<User> users = mapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
void testWrapper2(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//where email = ? or age = ?
queryWrapper.eq("email","admin@wsy.com")
.or()
.eq("age",20).select("name");//加select 表示select后面的字段只有name
List<User> users = mapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
开启AR模式
ActiveRecord(简称AR),属于ORM(对象关系映射)层,表映射到记录,记录映射到对象,字段映射到对象属性,配合遵循的命名和配置惯例,能够很大程度的快速实现模型的操作,而且简洁易懂;
主要思想:
-
每一个数据库表对应的创建一个类,类的每一个对象实例对应于数据库中表的一行记录,通常表的每一个字段在类中都有相对应的Field;
-
AR同时负责把自己持久化,在AR中封装了对数据库的访问,即CRUD;
-
AR是一种领域模型,封装了部分业务逻辑;
/**
* ActiveRecord 模式 CRUD
* <p>
* 必须存在对应的原始mapper并继承baseMapper并且可以使用的前提下
* 才能使用此 AR 模式 !!!
* </p>
*
* @param <T>
* @author hubin
* @since 2016-11-06
*/
public abstract class Model<T extends Model<?>> implements Serializable {}
-- 在MP中,开启AR模式非常简单.只需要将实体继承Model<>即可!
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("user")
public class User extends Model<User> {}
示例
@RunWith(SpringRunner.class)
@SpringBootTest
public class testAR {
/**
* 在AR模式下,完成根据主键查询
*/
@Test
void testARGetById(){
User user = new User();
user.setId(1L);
User user1 = user.selectById();
System.out.println(user1);
}
/**
* 在AR模式下,根据条件进行 select
*/
@Test
void testARGetAll(){
QueryWrapper<User> query = new QueryWrapper<>();
query.ge("age",10);
User user = new User();
List<User> users = user.selectList(query);
users.forEach(System.out::println);
}
/**
* 在AR模式下,完成 insert
*/
@Test
void testARInsert(){
User user = new User();
user.setName("111");
user.setAge(12);
user.setMail("xxx@xxx.com");
boolean user1 = user.insert();
System.out.println(user1);
}
/**
* 在AR模式下,完成 update
*/
@Test
void testARUpdate(){
User user = new User();
user.setId(1L);
user.setName("111");
user.setAge(12);
user.setMail("xxx@xxx.com");
//boolean update = user.updateById();
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.eq("name","jack").set("age",12);
boolean update = user.update(updateWrapper);
System.out.println(update);
}
/**
* 在AR模式下,完成 delete
*/
@Test
void testARDelete(){
User user = new User();
user.setName("111");
user.setAge(12);
user.setMail("xxx@xxx.com");
//boolean delete = user.deleteById(1L);
QueryWrapper<User> query = new QueryWrapper<>();
query.eq("id",1L);
boolean delete = user.delete(query);
System.out.println(delete);
}
}