一.普通查询
1.selectOne
UserRelateCaruserRelateCar=userRelateCarMapper.selectOne(Wrappers.<UserRelateCar>lambdaQuery().eq(UserRelateCar::getXcxUserId, driverXcxUserId)
.eq(UserRelateCar::getNumber, number));
2.selectList
List<ShipmentOrderDetailGoodsInfo>shipmentOrderDetailGoodsInfos=shipmentOrderDetailGoodsInfoMapper.selectList(Wrappers.<ShipmentOrderDetailGoodsInfo>lambdaQuery()
.eq(ShipmentOrderDetailGoodsInfo::getShipmentOrderDetailId, shipmentDetailId));
### 3.selectPage 分页
@GetMapping(value="/selectAllInPage")
publicList<Teacher>selectAllInPage(intpageNumber,intpageSize){
Page<Teacher>page=newPage<>(pageNumber,pageSize);
EntityWrapper<Teacher>entityWrapper=newEntityWrapper<>();
entityWrapper.ge("id", 1);
returnteacherMapper.selectPage(page,entityWrapper);
}
LambdaUpdateWrapper<AdsDayCityOrrCalendarDo>wrapper=Wrappers.lambdaUpdate(AdsDayCityOrrCalendarDo.class)
.eq(AdsDayCityOrrCalendarDo::getBrandDetailNo, query.getBrandDetailNo());
finalPage<AdsDayCityOrrCalendarDo>citys=adsDayCityOrrCalendarMapper.selectPage(newPage<>(query.getPage(), query.getSize()), wrapper);
4.排序
可以使用 QueryWrapper 的 orderByDesc 方法来指定字段降序查询,示例代码如下:
.orderByDesc(AdsDayCityOrrCalendarDo::getPeriodSdate);
//其中,"age" 是需要降序排列的字段名。
QueryWrapper<User>wrapper=newQueryWrapper<>();
wrapper.orderByDesc("age"); // 按照 age 字段降序排列
List<User>userList=userMapper.selectList(wrapper);
5.条件查询
//条件查询-等于-like-小于-大于-排序
private Wrapper<TaskRecordDO> getTaskRecordWrapper(ListTaskRecordRequestDTO query) {
Date startExecuteTime = query.getStartExecuteTime();
Date endExecuteTime = query.getEndExecuteTime();
Calendar calendar = new GregorianCalendar();
if (endExecuteTime != null) {
calendar.setTime(endExecuteTime);
calendar.add(Calendar.DATE, 1);
}
return new QueryWrapper<TaskRecordDO>()
.lambda()
.eq(StringUtils.isNotBlank(query.getExecuteStatus()), TaskRecordDO::getExecStatus, query.getExecuteStatus())
.like(StringUtils.isNotBlank(query.getKeyword()), TaskRecordDO::getOutNodeName, query.getKeyword())
.lt(endExecuteTime != null, TaskRecordDO::getStartRunTime, calendar.getTime())
.ge(startExecuteTime != null, TaskRecordDO::getStartRunTime, startExecuteTime)
.eq(query.getTaskId() != null, TaskRecordDO::getTaskId, query.getTaskId())
.orderByDesc(TaskRecordDO::getId);
}
//条件查询-左边模糊匹配-右边模糊匹配
@Override
public boolean checkExist(String tableName) {
Wrapper<DagConfDO> queryWrapper = Wrappers.<DagConfDO>query().lambda()
.likeLeft(DagConfDO::getConf, tableName)
.likeRight(DagConfDO::getConf, tableName)
.orderByDesc(DagConfDO::getId)
.groupBy(DagConfDO::getTaskId);
dagConfMapper.selectList(queryWrapper);
return false;
}
二.高阶查询
1.复杂多条件
@GetMapping(value = "/selectAllByWrapper4")
public List<Teacher> selectAllByWrapper4(){
EntityWrapper entity=new EntityWrapper();
entity.gt("id","0");
entity.le("id",11);
entity.ne("teacher_name","null_name");
entity.like("teacher_name","tt");
entity.notLike("teacher_pwd","sadas");
entity.orderBy("id");
return teacherMapper.selectList(entity);
}
2.and 条件
QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>().
eq("attr_id",key).
eq("catelog_id",catelogId);
或者
QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>().
eq("attr_id",key);
queryWrapper.and(qr -> qr.eq("catelog_id", catelogId));
3.or 条件
QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>().
eq("attr_id",key).
or().
eq("catelog_id",catelogId);
4.优先级连接
QueryWrapper<AttrEntity> queryWrapper = new QueryWrapper<AttrEntity>().eq("attr_type", "base".equalsIgnoreCase(type) ? 1 : 0);
queryWrapper.and(qr ->
qr.eq("attr_id", key).
or().
like("attr_name", key)
);
queryWrapper.and(qr -> qr.eq("catelog_id", catelogId));
select ...
WHERE (attr_type = ? AND ( (attr_id = ? OR attr_name LIKE ?) ) AND ( (catelog_id = ?) ))
...;
由此还可见 or(Consumer consumer),and(Consumer consumer)这两个方法参数为 Consumer 时,会在连接处生成 2 对括号,以此提高优先级。
5.service 实现
LambdaUpdateWrapper<AdsDo> wrapper = Wrappers.lambdaUpdate(AdsDo.class)
.eq(AdsDo::getBrandDetailNo, query.getBrandDetailNo())
.between(AdsDo::getPeriodSdate,query.getStartDate(), query.getEndDate())
.eq(AdsDo::getManagingCityNo, query.getManagingCityNo())
.eq(StringUtils.isNotBlank(query.getGenderName()), AdsDo::getGenderName, query.getGenderName());
final List<AdsDo> citys = adsDayCityOrrCalendarMapper.selectList(wrapper);
二.update
1.update
userRelateCarMapper.update(new UserRelateCar(), Wrappers.<UserRelateCar>lambdaUpdate()
.set(UserRelateCar::getIsDefault, DefaultCarEnum.YES.getType())
.eq(UserRelateCar::getNumber, carNumber)
.eq(UserRelateCar::getXcxUserId, xcxUserId));
2.remove
shipmentOrderRelateUserService.remove(Wrappers.<ShipmentOrderRelateUser>lambdaQuery()
.eq(ShipmentOrderRelateUser::getRoleId, RoleEnum.DRIVER.getType())
.eq(ShipmentOrderRelateUser::getShipmentOrderId, id));
三.Mapper 文件
1.if-else
mybaits 中没有 else 要用 chose when otherwise 代替
<select id="selectSelective" resultMap="xxx" parameterType="xxx">
select
<include refid="Base_Column_List"/>
from xxx
where del_flag=0
<choose>
<when test="xxx !=null and xxx != ''">
and xxx like concat(concat('%', #{xxx}), '%')
</when>
<otherwise>
and xxx like '**%'
</otherwise>
</choose>
</select>
2.in-for 循环
在 mapper.xml 文件中使用 for 循环当做 in 的条件
<if test="query.storeNameList != null and query.storeNameList.size > 0">
and organ_key in
<foreach item="item" index="index" collection="query.storeNameList"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
3.choose-when
注意双引号的位置
<select>
SELECT COUNT(1) FROM TABLE1 WHERE ID_NO = #{idNo}
<choose>
<when test="flag == '0'">
AND CONT_ADDR LILE CONCAT(#{contAddr},'%')
</when>
<when test="flag == '1'">
AND CONT_ADDR NOT LILE CONCAT(#{contAddr},'%')
</when>
<otherwise></otherwise>
</choose>
</select>
<select>
SELECT COUNT(1) FROM TABLE1 WHERE ID_NO = #{idNo}
<choose>
<when test='flag == "0"'>
AND CONT_ADDR LILE CONCAT(#{contAddr},'%')
</when>
<when test='flag == "1"'>
AND CONT_ADDR NOT LILE CONCAT(#{contAddr},'%')
</when>
<otherwise></otherwise>
</choose>
</select>
4.or 多条件
<select id="getUserByOr" resultType="com.kwan.springbootkwan.entity.User">
SELECT *
FROM user
WHERE 1 = 1
<if test="invFlag != null and invFlag.size>0">
<foreach collection="invFlag" item="flag" open="AND (" close=")" separator="or">
<if test="flag==1">
id <= 1
</if>
<if test="flag==2">
id >= 2 and id < 4
</if>
<if test="flag==3">
id >= 5 and id < 6
</if>
<if test="flag==4">
id >= 8 and id < 9
</if>
</foreach>
</if>
</select>
5.转义符号
使用 CDATA
< 和 & 这些符号在 xml 文件中有特殊含义,所以要用 CDATA 来定义,如
<![CDATA[ ...]]>
#<=可以
<![CDATA[ <= ]]>
6.内存分页
List<AssetsDirectoriesVo> list = service.queryPageList(bo); //获取的数据集合
PageInfo<AssetsDirectoriesVo> pageInfo = new PageInfo<>(); //创建一个分页对象
pageInfo.setTotal(list.size());
pageInfo.setPages(list.size()/pageBaseReq.getPageSize()+(list.size()%pageBaseReq.getPageSize()==0?0:1)); //pageBaseReq为分页参数对象
pageInfo.setPageNum(pageBaseReq.getPageNum());
pageInfo.setPageSize(pageBaseReq.getPageSize());
List<AssetsDirectoriesVo> res = list.stream()
.skip((pageBaseReq.getPageNum() - 1) *pageBaseReq.getPageSize()).limit(pageBaseReq.getPageSize()).collect(Collectors.toList()); //开始分页
pageInfo.setList(res);
7.xml 等于条件
<when test="query.queryField == 'sal_qty_store_rate'">
,if(sum(size_store_day) is NULL OR SUM(size_store_day) =
0,0,round(sum(sal_qty)/sum(size_store_day),4)) as salQtyStoreRate
</when>
8.特殊字符
SELECT * FROM order order by createDate #{sortType} //报错
SELECT * FROM order order by createDate ${sortType} //正常
这种情况下,就需要把 sortType 搞成白名单了。不就一个 ASC 和 DESC 了
/**
* 是否含有特殊字符
*
* @param str
* @return
*/
static boolean isSpecialChar(String str) {
String regEx = "[ _`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~!@#¥%……&*()——+|{}【】‘;:”“’。,、?]|\n|\r|\t";
Pattern p = Pattern.compile(regEx);
Matcher m = p.matcher(str);
return m.find();
}
直接用动态参数生成,不会排序:
<if test="orderColumn!=null and orderColumn !=''">
ORDER BY #{orderColumn} #{orderDir}
</if>
需要将 #改为 $:
<if test="orderColumn!=null and orderColumn !=''">
ORDER BY ${orderColumn} ${orderDir}
</if>
9.resultType
返回类型处理
<select id="selectAllSkus" resultType="java.lang.String">
10.fetchSize
大数据查询较慢,可以使用 fetchSize,加快查询
<select id="selectAssignableDeviceInfo" fetchSize="1000" parameterType="java.lang.String" resultMap="result">
select
i.UUID,
i.FACT_SN,
i.FACT_ID,
i.BRAND_ID,
i.GOODS_ORDER_ID,
o.ORDER_TYPE as DEVICE_TYPE
from TABLE_INFO i
left join ORDER_INFO o on i.GOODS_ORDER_ID = o.GOODS_ORDER_ID
where i.DEVICE_STATUS = '00'
and nvl(i.HAPPY_SEND_ACTIVE_STATUS,'00') = '00'
and i.ASSIGN_FLAG = '00'
and i.USER_ID = #{userId,jdbcType=VARCHAR}
and i.GOODS_ORDER_ID = #{goodsOrderId,jdbcType=VARCHAR}
</select>
11.逻辑判断
<choose>
<when test="query.dateDimension != null and query.dateDimension != '' and '月'.toString()==query.dateDimension">
financial_year as financial_year
,financial_year_month as financial_year_month
</when>
<when test="query.dateDimension != null and query.dateDimension != '' and '周'.toString()==query.dateDimension">
financial_year as financial_year
,financial_year_week as financial_year_week
</when>
<otherwise>
period_sdate as period_sdate
</otherwise>
</choose>
<choose>
<when test="query.organKey != null and query.organKey != '' and query.organKey != '-1'">
,organ_key as organ_key
,managing_city_no as managing_city_no
,region_no as region_no
,store_name as store_name
,store_level_name as store_level_name
,store_status as store_status
</when>
<when test="query.managingCityNo != null and query.managingCityNo != '' and query.managingCityNo != '-1'">
,managing_city_no as managing_city_no
,region_no as region_no
</when>
<when test="query.regionNo != null and query.regionNo != '' and query.regionNo != '-1'">
,region_no as region_no
</when>
<otherwise>
</otherwise>
</choose>
四.配置
1.实现类写法
@Service
public class StoreBaseServiceImpl extends ServiceImpl<StoreBaseMapper, StoreBase> implements IStoreBaseService {
}
public interface IStoreBaseService extends IService<StoreBase> {
}
2.注解
@Api(tags = "外部系统(供应链)交互API") //controller
@ApiOperation("信息软删除") //方法
@ApiModelProperty(value = "对接系统") //属性
@ApiModel(value = "任务统计") //返回的对象
3.只打印 SQL 语句
Mybatis 配置
mybatis:
configuration:
### 开启打印sql配置
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
### 开启驼峰配置
map-underscore-to-camel-case:true
MybatisPlus 配置
mybatis-plus:
configuration:
### 开启打印sql配置
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
### 开启驼峰配置
map-underscore-to-camel-case:true
生产关闭日志
mybatis-plus:
configuration:
#关闭sql日志
log-impl: org.apache.ibatis.logging.nologging.NoLoggingImpl
其他配置
### mybatis 相关配置
mybatis:
config-location: classpath:mybatis.cfg.xml # mybatis主配置文件所在路径
type-aliases-package: com.example.entity # 定义所有操作类的别名所在包
mapper-locations: classpath:mapper/*.xml # 所有的mapper映射文件
configuration:
# 开启驼峰uName自动映射到u_name
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
### mybatis-plus 相关配置
mybatis-plus:
type‐aliases‐package: com.example.entity # 定义所有操作类的别名所在包
# xml扫描,多个目录用逗号或者分号分隔(告诉 Mapper 所对应的 XML 文件位置)
mapper-locations: classpath:mapper/*.xml
# 以下配置均有默认值,可以不设置
global-config:
banner: false # 是否 mybatis-plus 在控制台输出的logo
db-config:
#主键类型 AUTO:"数据库ID自增" INPUT:"用户输入ID",ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID";
id-type: auto
#字段策略 IGNORED:"忽略判断" NOT_NULL:"非 NULL 判断") NOT_EMPTY:"非空判断"
field-strategy: NOT_EMPTY
#数据库类型
db-type: MYSQL
logic-delete-field: deleted # 全局逻辑删除的实体字段名
logic-delete-value: 1 # 逻辑已删除值(默认为 1)
logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
configuration:
# 是否开启自动驼峰命名规则映射:从数据库列名到Java属性驼峰命名的类似映射
map-underscore-to-camel-case: true
# 如果查询结果中包含空值的列,则 MyBatis 在映射的时候,不会映射这个字段
call-setters-on-nulls: true
# 这个配置会将执行的sql打印出来,在开发或测试的时候可以用
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
4.驼峰设置
mybatis 默认是属性名和数据库字段名一一对应的,即
- 数据库表列:user_name
- 实体类属性:user_name
但是 java 中一般使用驼峰命名
- 数据库表列:user_name
- 实体类属性:userName
在 Springboot 中,可以通过设置 map-underscore-to-camel-case 属性为 true 来开启驼峰功能。application.yml 中:
mybatis:
configuration:
map-underscore-to-camel-case: true
application.properties 中:
mybatis.configuration.map-underscore-to-camel-case:=true
5.创建时间
/**
* 创建时间
*/
@TableField(value = "create_time", fill = FieldFill.INSERT)
private Date createTime;
/**
* 修改时间
*/
@TableField(value = "edit_time", fill = FieldFill.INSERT_UPDATE)
private Date editTime;
6.数据库不存在字段
@TableField(exist = false)
7.更新字段
如果您是在使用 MyBatis-Plus 进行数据库操作,可以使用以下代码将 id 为 1 的数据的 is_delete 字段改为 1:
QueryWrapper<Chatbot> wrapper = new QueryWrapper<>();
wrapper.eq("id", 1);
Chatbot chatbot = new Chatbot();
chatbot.setIsDelete(1);
return Result.ok(this.chatbotService.update(chatbot, wrapper));
在上述代码中,我们先创建了一个 QueryWrapper
对象,通过 eq
方法指定查询条件为 id = 1
和 is_delete = 1
。然后,我们创建了一个 Chatbot
对象,设置其 is_delete
字段为 1
。最后,我们调用 update
方法,将修改后的 Chatbot
对象和查询条件 QueryWrapper
对象传递给 update
方法,完成数据更新操作。 需要注意的是,如果您要更新的数据不存在,update
方法会返回 false
,否则会返回 true
。如果您需要返回更新后的数据,可以使用 updateById
方法,该方法会返回更新后的完整实体对象。
8.@TableField
如果您在使用 MyBatis-Plus 进行数据库操作,可以使用 @TableField
注解来标记实体类中的字段,指定该字段不在数据库中对应的列。以下是一个示例代码:
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
@TableName("chatbot")
public class Chatbot {
private Long id;
private String name;
private Integer age;
@TableField(exist = false)
private String otherField;
// 省略 getter 和 setter 方法
}
在上述代码中,我们使用 @TableField
注解来标记 otherField
字段,指定该字段不在数据库中对应的列。其中,exist
属性指定该字段是否在数据库表中存在。如果 exist
属性设置为 false
,则表示该字段不在数据库表中存在,否则表示该字段在数据库表中存在。在本例中,我们将 exist
属性设置为 false
,表示 otherField
字段不在数据库表中存在。
需要注意的是,如果您在实体类中使用了 @TableField
注解,那么在进行查询、更新、删除等操作时,MyBatis-Plus 会忽略该字段。如果您需要在查询中使用该字段,可以使用 select
方法指定查询的字段列表。