Myabtis-Plus(高级查询)
函数名 | 说明 | 例子 |
---|---|---|
eq | 等于 = | 例:eq(“name”,“张三”) :name = ‘张三’ |
ne | 不等于<> | 例: eq(“name”,“老王”)—>name = ‘老王’ |
gt | 大于> | 例:gt(“age”,18)—>age > 18 |
ge | 大于等于>= | 例:ge(“age”,18)—>age >= 18 |
lt | 小于< | 例:lt(“age”,18)—>age < 18 |
le | 小于<= | 例:le(“age”,18)—>age <= 18 |
between | BETWEEN值1 AND值2 | 例:between(“age”,18,30)—>age between 18 and 30 |
notBetween | NOT BETWEEN值1 AND值2 | 例: notBetween(“age”,18,30)—>age not between 18 and 30 |
like | LIKE ‘%值%’ | 例: like(“name”,“王”)—–>name like '%王%’ |
notLike | NOT LIKE ‘%值%’ | 例: notLike (“name”,“王”)—>name not like '%王%’ |
likeLeft | LIKE '%值’ | 例:likeLeft (“name”,“王”)—–>name like '%王’ |
likeRight | LIKE’值%’ | 例: likeRight(“name”,“王”)—>name like ‘王%’ |
isNull | 字段IS NULL | 例: isNul1 (“name”)—>name is null |
isNotNull | 字段IS NOT NULL | 例: isNotNull(“name”)—>name is not null |
in | 字段IN (v0, v1,…) | 例: in(“age”,{1,2,3} )—–>age in (1,2,3) |
notIn | 字段NOT IN (v0, v1,…) | 例: notIn(“age”,1,2,3)—>age not in (1,2,3) |
inSql | 字段IN ( sql语句) | inSql(“id”, “select id from table where id < 3”) —–>id in (select id from table where id < 3) |
notInSql | 字段NOT IN ( sql语句) | notInSql(“id”, “select id from table where id < 3”) —–>age not in (select id from table where id < 3) |
groupBy | 分组:GROUP BY 字段,… | 例: groupBy(“id”, “name”)—>group by id, name |
orderByAsc | 排序:ORDER BY字段,… ASC | 例: orderByAsc(“id”, “name”)—>order by id ASC, name ASC |
orderByDesc | 排序:ORDER BY 字段,…DESC | 例: orderByDesc(“id”, “name”)—>order by id DESC, name DESC |
orderBy | 排序:ORDER BY字段,… | 例: orderBy(true,true,“id”,“name”)—–>order by id ASC, name ASC |
having | HAVING ( sql语句) | having(“sum(age) >{0}”,11)—>having sum(age) > 11 |
or | 拼接OR | 主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)例:eq(“id”,1).or().eq(“name”,“老王”)—>id = 1 or name = '老王 |
and | AND嵌套 | 例: and(i -> i.eq(“name”,“李白”).ne(“status”,“活着”))—>and (name ='李白’ and status ’活着’) |
apply | 拼接sql | 该方法可用于数据库函数动态入参的params对应前面sqlHaving内部的{index}部分.这样是不会有sql注入风险的,反之会有! 例: apply(“date_format(dateColumn, ‘%Y一%m-%d’) ={0}”, “2023-08-08”)—>date_format(dateColumn,’%Y一%m-%d’) = ‘2023-08-08’") |
last | 无视优化规则直接拼接到sql 的最后 | 无视优化规则直接拼接到sql 的最后只能调用一次,多次调用以最后一次为准有sql注入的风险,请谨慎使用例: last(“limit 1”) |
exists | 拼接EXISTS ( sql语句) | exists (select id from table where age = 1) 例: notExists(“select id from table where age = 1”) —>exists (select id from table where age = 1) |
notExists | 拼接NOT EXISTS ( sql语句) | 例: notExists(“select id from table where age = 1”)—–>not exists (select id from table where age = 1) |
nested | 正常嵌套不带AND或者 OR | 正常嵌套不带AND或者OR例: nested(i -> i.eq(“name”,“李白”).ne(“status”,“高兴”))—>(name = '李白’and status 高兴’) |
一、列投影(select):
只想看到查询到的数据中部分字段数据。
例: 查询所有学生信息,返回name age 列。
QueryWrapper<USer> wrapper = new QueryWrapper<>();
1.wrapper.select("name","age");//---------->查询列集合,将查询数据作为两例。
2.wrapper.select("name,age");//---------->查询列集合,将查询数据作为一例。
usermapper.selectlist(wrapper)
二、排序与分组:
1.1、排序(orderByAsc/orderByDesc):
orderByAsc: 正序排序
等价SQL: select ..from table ORDER BY 字段, ... ASC
orderByDesc :倒序排序
wrapper方法:
orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)
效果:
orderByAsc("id", "name")--->order by id ASC,name ASC
列:查询所有员工信息按age正序排, 如果age一样, 按id正序排
@Test
public void Test0(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.orderByAsc("age", "id");
employeeMapper.selectList(wrapper);
}
//对应sql语句:
SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id ASC
orderByDesc 跟 orderByAsc用法一样, 不过是倒序排
1.2、定制排序(orderBy):
orderBy:定制排序
等价SQL: select ..from table ORDER BY 字段,
//参数1:布尔表达式:控制是否进行排序操作
//参数2:控制是不是正序
orderBy(boolean condition, boolean isAsc, R... columns)
排序:ORDER BY 字段, ...
&效果:
orderBy(true, true, "id", "name")--->order by id ASC,name ASC
例:查询所有员工信息按age正序排, 如果age一样, 按id正序排
@Test
public void Test1(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//apper.orderByAsc("age", "id");
//等价于:
wrapper.orderBy(true, true, "age", "id");
employeeMapper.selectList(wrapper);
}
//sql语句
SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id ASC
列:查询所有员工信息按age正序排, 如果age一样, 按id倒序排
@Test
public void Test2(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.orderByAsc("age");
wrapper.orderByDesc("id");
employeeMapper.selectList(wrapper);
}
//对应sql语句
SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id DESC
三、分组查询(groupBy):
方法:
(1):groupBy : 分组:GROUP BY 字段, ...
(2):groupBy(R... columns)
(3):groupBy(boolean condition, R... columns)
&效果:
groupBy("id", "name")--->group by id,name
例: 以部门id进行分组查询,查每个部门员工个数
@Test
public void Test3(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.groupBy("dept_id");
wrapper.select("dept_id", "count(id) count");
employeeMapper.selectMaps(wrapper);
}
//对应sql语句
SELECT dept_id,count(id) count FROM employee GROUP BY dept_id
四、过滤条件(having):
方法:
(1): having : HAVING ( sql语句 )
(2): having(String sqlHaving, Object... params)
//参数1:布尔表达式:控制是否进行过滤操作
//参数2:以哪个字段为过滤
//参数3:以哪个条件作为过滤条件
(3): having(boolean condition, String sqlHaving, Object... params)
例: having("sum(age) > 10")--->having sum(age) > 10
例: having("sum(age) > {0}", 11)--->having sum(age) > 11
需求: 以部门id进行分组查询,查每个部门员工个数, 将大于3人的部门过滤出来
@Test
public void test5(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.groupBy("dept_id")
.select("dept_id", "count(id) count")
//.having("count > {0}", 3)
.having("count >3");
employeeMapper.selectMaps(wrapper);
}
//对应sql语句:
SELECT dept_id,count(id) count FROM employee GROUP BY dept_id HAVING count >3
五、比较运算符:
1. 等于 不等于
1):allEq:全能匹配;即:所有条件都得等;
例 查询 name=’张三‘ age=’18‘的学生信息
步骤:
(1): Map(string ,object)mp = new HashMap();
mp.put("name","张三");
mp.put("age","18");
(2): QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.allEq(mp);
(3):userMapper.selelist(wrapper);
2): eq:当个参数判断是否相等。---------->对应SQL中 “等于=”
列:eq("name","zhnagsan")----------->name="zhangsanE"
3): ne: 不等于---------->对应SQL中 “不等于< >”
列:ne("name","张三")------------->name< >'张三嗯'
2.大于 等于 小于
(1):get :大于 >
列:get("age",18)----------->age>18
(2):ge :大于等于 >=
列:ge("age",18)----------->age>=18
(3):lt :小于 <
列:lt("age",18)----------->age<18
(4):le :小于等于 <=
列:le("age",18)----------->age<=18
六、批量插入或更新数据
方式一:mybatis-plus 的saveOrUpdateBatch方法
继承BaseMapper即可,泛型使用当前要操作类
@Mapper
public interface HhChainCustomerInfoMapper extends BaseMapper<HhChainCustomerInfo> {
/**
* 使用mybatis-plus方式调用saveOrUpdateBatch不需要写这个接口
boolean saveOrUpdateBatch(@Param("entities") Collection<HhChainCustomerInfo> hhChainCustomerInfos);
*/
}
Service 层继承 IService即可,泛型使用当前要操作类
/**
* 链路客户信息Service接口
*
* @author js
* @date 2023-09-10
*/
public interface IHhChainCustomerInfoService extends IService<HhChainCustomerInfo> {
}
Service 实现类 层继承 ServiceImpl即可,泛型使用当前要操作类
@Service
public class HhChainCustomerInfoServiceImpl extends ServiceImpl<HhChainCustomerInfoMapper, HhChainCustomerInfo> implements IHhChainCustomerInfoService {
/*@Override
public boolean saveOrUpdateBatch(Collection<HhChainCustomerInfo> entityList) {
return hhChainCustomerInfoMapper.saveOrUpdateBatch(entityList);
}*/
}
七、(单表)分页+高级查询:
步骤:
第一步:定义一个QueryObject类:
@Setter
@Getter
public class QueryObject {
private int CurrentPage=1;
private int PageSize=1;
}
第二步:定义一个QueryObject类:
@Setter
@Getter
public class EmployeeQuery extends QueryObject{
}
第三步:在启动类中配置分页插件:
步骤:配置分页插件
//分页
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
paginationInnerInterceptor.setOverflow(true); //合理化
interceptor.addInnerInterceptor(paginationInnerInterceptor);
return interceptor;
}
第四步:在Service接口层中定义分页方法:
public interface IEmployeeService extends IService<Employee>{//----------->泛型里为实体类
//------->必须继承 IService<T>
//分页方法:
public IPage<Employee> query(EmployeeQuery qo);
}
第五步:在Service接口实现类中实现分页方法:
public class EmployeeServiceImpl extends Service<EmployeeMapper,Employee> implements IEmployeeService{
//------------>Service<EmployeeMapper,Employee>:实体类Mapper接口;实体类
//------->必须继承 Service<TMapper,T>
@Override
public IPage<Employee> query(EmployeeQuery qo) {
IPage<Employee> page = new Page<>(qo.getCurrentPage(), qo.getPageSize()); //设置分页信息
QueryWrapper<Employee> wrapper = Wrappers.<Employee>query(); //拼接条件
//有条件约束写在这里:
return super.page(page,wrapper);
}
}
列:查询第2页员工信息, 每页显示3条, 按id排序
@Test
public void testPage(){
EmployeeQuery qo = new EmployeeQuery();
qo.setPageSize(3);
qo.setCurrentPage(2);
IPage<Employee> page = employeeService.query(qo);
System.out.println("当前页:" + page.getCurrent());
System.out.println("总页数:" + page.getPages());
System.out.println("每页显示条数:" + page.getSize());
System.out.println("总记录数:" + page.getTotal());
System.out.println("当前页显示记录:" + page.getRecords());
}
//sql语句
SELECT id,name,password,email,age,admin,dept_id FROM employee where (name like ?) LIMIT ?,?
八、联表分页查询+条件查询:
对于一对一,一对多,多对一,还是两表之间通过主外键关联查询,都可以;
总结:
第一步:不管是那种情况,首先将响应给前端的数据封装成一个类并继承(PageQuery)
第二步:按以下步骤做
步骤:
1.编写:请求Dto层:
import lombok.Data;
@Data
public class PageQuery {
/**
* 当前页
*/
private Integer curPage;
/**
* 页大小
*/
private Integer pageSize;
}
2.编写封装响应给前端数据to
import com.baomidou.mybatisplus.annotation.TableId;
import com.zhecre.jx.business.model.index.request.PageQuery;
import lombok.Data;
import java.util.Date;
@Data
public class CompensateInfo extends PageQuery {
/**
* 方案状态(1:未提交|2:已提交|3:已驳回|4:已审核)
*/
private String status;
/**
* 方案编号
*/
@TableId
private String caseid;
/**
* 方案名称
*/
private String casename;
/**
* 医院名称
*/
private String hosname;
private String casetype;
private String remark;
private Date commitdate;
private String type;
}
3.定义controller层:
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.zhecre.jx.business.model.index.response.CompensateInfo;
import com.zhecre.jx.business.service.cases.ICompensateInfoService;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
@RequiredArgsConstructor
@RequestMapping("/Compensation")
public class CompensationSchemesController {
@Autowired
private final ICompensateInfoService compensateInfoService;
@GetMapping("/index/query")
public IPage<CompensateInfo> CompensatePage( CompensateInfo Info){
//调用service层
IPage<CompensateInfo> query = compensateInfoService.query(Info,Info.getCasename(),Info.getCasetype());
return query;
}
}
4.编写service 层:
public interface ICompensateInfoService extends IService<CompensateInfo> {
//分页方法:
IPage<CompensateInfo> query(CompensateInfo info, String casename, String casetype);
}
5.实现service层:
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zhecre.jx.business.mapper.cases.CompensateInfoMapper;
import com.zhecre.jx.business.model.index.response.CompensateInfo;
import com.zhecre.jx.business.service.cases.ICompensateInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class CompensateInfoServiceImpl extends ServiceImpl<CompensateInfoMapper, CompensateInfo> implements ICompensateInfoService {
@Autowired
private CompensateInfoMapper compensateInfoMapper;
@Override
public IPage<CompensateInfo> query(CompensateInfo qo, String casename, String casetype) {
Page<CompensateInfo> Page = new Page<>(qo.getCurPage(), qo.getPageSize());//调用mybatisplus分页插件
IPage<CompensateInfo> queryVoPage = compensateInfoMapper.findByPage(Page, qo,casename,casetype);//定义分页方法
return queryVoPage;
}
}
6.定义Mapper层:
public interface CompensateInfoMapper extends BaseMapper<CompensateInfo> {
//自定义分页方法
IPage<CompensateInfo> findByPage(Page<CompensateInfo> page, CompensateInfo qo,
@Param("casename") String casename,@Param("casetype") String casetype);//这是条件查询参数
}
7.编写Mapper.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhecre.jx.business.mapper.cases.CompensateInfoMapper">
//将查寻结果对象字段属性和数据库字段对应
<resultMap id="CompensateInfoMapper" type="com.zhecre.jx.business.model.index.response.CompensateInfo">
<id property="caseid" column="CASEID"></id>
<result property="status" column="STATUS"></result>
<result property="casename" column="CASENAME"></result>
<result property="casetype" column="CASETYPE"></result>
<result property="remark" column="REMARK"></result>
<result property="hosname" column="HOSNAME"></result>
<result property="commitdate" column="COMMITDATE"></result>
<result property="type" column="TYPE"></result>
</resultMap>
//查询语句:得到结果集
<select id="findByPage" parameterType="com.zhecre.jx.business.model.index.response.CompensateInfo" resultMap="CompensateInfoMapper">
SELECT
c.STATUS,
c.CASEID,
c.CASENAME,
c.CASETYPE,
p.HOSNAME,
c.REMARK,
c.COMMITDATE,
c.TYPE
FROM
PAS_CASE c,
PAS_CASE_HOSPITAL p
<where>
and c.CASEID = p.CASEID
<if test="casename !=null and casename != ''">
and c.CASENAME = #{casename}//传入的条件查数;如果传入的参数是对象的话要取对象中的某个属性,这个地方参数直接写成属性名即可,但是对象所属的类 必须提供getter方法;如果是普通变量字段,条件直接写成传入得字段
</if>
</if>
<if test="casetype !=null and casetype != ''">
and c.CASETYPE = #{casetype}
</if>
</where>
</select>
</mapper>