【JAVA基础】Mybatis示例
固定时间范围查询
<select id="selectPaidList" resultType="com.hand.htms.ifp.entity.IfpShipment">
SELECT
*
FROM
ifp_shipment t1
WHERE (t1.creation_date > #{beginDate} and t1.creation_date<#{endDate})
and ( t1.shipper_pay_status = 'PAID' or t1.shipper_pay_status = 'PARTIALPAID' )
<if test="tenantId !=null and tenantId != 1">
and t1.tenant_id = #{tenantId}
</if>
</select>
<select id="selectShipment" resultType="com.hand.htms.ifp.entity.IfpShipment">
SELECT
*
FROM
ifp_shipment t1
WHERE t1.shipment_status =#{status}
<if test="status != null and status == 'TO_LOAD'">
and (t1.creation_date > #{beginDate} and t1.creation_date<#{endDate})
</if>
<if test="status != null and status == 'TO_UNLOAD'">
and (t1.load_date > #{beginDate} and t1.load_date<#{endDate})
</if>
<if test="status != null and status == 'TO_SIGN'">
and (t1.unload_date > #{beginDate} and t1.unload_date<#{endDate})
</if>
<if test="status != null and status == 'SIGN'">
and (t1.sign_date > #{beginDate} and t1.sign_date<#{endDate})
</if>
<if test="tenantId !=null and tenantId != 1">
and t1.tenant_id = #{tenantId}
</if>
</select>
模糊查询
<select id="selectlist" resultType="com.hand.htms.ifp.dto.IfpShipmentAppDTO">
select
<include refid = "BaseSql"/>
from
ifp_shipment iil
where
iil.driver_gid = #{driverGid}
and
iil.shipment_status = #{shipmentStatus}
<if test="xid != null">
<bind name="xidLike" value="'%'+xid+'%'"/>
and iil.xid like #{xidLike}
</if>
<if test="vehicleGid != null">
and iil.vehicle_gid = #{vehicleGid}
</if>
<if test="sourceName != null">
<bind name="sourceNameLike" value="'%'+sourceName+'%'"/>
and iil.source_name like #{sourceNameLike}
</if>
<if test="sourcePhone != null">
<bind name="sourcePhoneLike" value="'%'+sourcePhone+'%'"/>
and iil.source_phone like #{sourcePhoneLike}
</if>
<if test="destName != null">
<bind name="destNameLike" value="'%'+destName+'%'"/>
and iil.dest_name like #{destNameLike}
</if>
<if test="destPhone != null">
<bind name="destPhoneLike" value="'%'+destPhone+'%'"/>
and iil.dest_phone like #{destPhoneLike}
</if>
</select>
精确查询
<select id="listProject" resultType="com.inja.spot.domain.entity.Project">
SELECT
sbp.project_name,
sbp.factory_meaning,
sbp.project_code,
sbp.project_id,
sbp.remark,
delete_flag
FROM
spot_bd_project sbp
WHERE
tenant_id = #{organizationId}
AND
delete_flag = 'N'
<if test="projectName != null and projectName != ''">
and sbp.project_name like concat('%',#{projectName},'%')
</if>
<if test="projectCode != null">
and sbp.project_code = #{projectCode}
</if>
<if test="factoryMeaning != null and factoryMeaning != ''">
and sbp.factory_meaning like concat('%',#{factoryMeaning},'%')
</if>
ORDER BY sbp.creation_date DESC
</select>
String型需要校验不为null和不为空，Long型只需要校验不为null；需要使用concat拼接，否则会有SQL注入的风险。
连表查询
https://blog.csdn.net/Java_is_NO_1/article/details/117933024
insert操作
<!--id属性:表示映射的接口中方法的名称,直接标签的内容部来编写SQL语句-->
<!--useGeneratedKeys="true"表示开启某个字段的值递增(大部分都是主键递增)
keyProperty="uid"表示将表中哪个字段进行递增
-->
<insert id="insert" useGeneratedKeys="true" keyProperty="aid">
INSERT INTO t_address (
uid, `name`, province_name, province_code, city_name, city_code, area_name, area_code, zip,
address, phone, tel,tag, is_default, created_user, created_time, modified_user, modified_time
) VALUES (
#{uid}, #{name}, #{provinceName}, #{provinceCode}, #{cityName}, #{cityCode}, #{areaName},
#{areaCode}, #{zip}, #{address}, #{phone}, #{tel}, #{tag}, #{isDefault}, #{createdUser},
#{createdTime}, #{modifiedUser}, #{modifiedTime}
)
</insert>
单一参数的情况
<select id="listRegion" resultType="com.inja.spot.domain.entity.Region">
SELECT
sbr.region_id,
sbr.country_name,
sbr.country_id,
sbr.region_name,
sbr.prov_name,
sbr.prov_id,
sbr.city_name,
sbr.city_id,
sbr.region_meaning
FROM
spot_bd_region sbr
WHERE
sbr.delete_flag = 'N'
<if test="_parameter != null and _parameter != ''">
and sbr.region_name like concat('%',#{_parameter },'%')
</if>
ORDER BY sbr.creation_date DESC
</select>
不进入Mapper查询
if (null != lovVlaues && lovVlaues.size() > 0) {
//将lovVlaues中的value值（租户Num）取出，并进行非空校验
List<String> tags = lovVlaues.stream().filter(lovValueDTO -> lovValueDTO.getTag() != null)
.map(lovValueDTO -> lovValueDTO.getTag()).collect(Collectors.toList());
if (null != tags && tags.size() > 0) {
//取自营店铺的租户id
sellerTenantId = Long.parseLong(tags.get(0));
//取自营店铺（卖家）店铺信息
sellerShop = shopRepository.selectByCondition(Condition.builder(Shop.class)
.andWhere(Sqls.custom().andEqualTo(Shop.FIELD_TENANT_ID, sellerTenantId)).build())
.stream().findFirst().orElse(null);
}
}
值集示例
SELECT
ifp_shipment.id,
ifp_shipment.xid,
ic.name as shipper_name,
id.name as driver_name,
iv.lpn
FROM
ifp_shipment
left join ifp_company ic on ifp_shipment.shipper_company_id = ic.id
left join ifp_driver id on ifp_shipment.driver_gid = id.id
left join ifp_vehicle iv on ifp_shipment.vehicle_gid = iv.id
WHERE
1=1
<if test='organizationId != null'>
and ifp_shipment.tenant_id = #{organizationId}
</if>
<if test='xid != null and xid != ""'>
AND ifp_shipment.xid LIKE concat('%', concat(#{xid}, '%'))
</if>
<if test='shipperName!= null and shipperName!= ""'>
AND ic.name LIKE concat('%', concat(#{shipperName}, '%'))
</if>
<if test='driverName!= null and driverName!= ""'>
AND id.name LIKE concat('%', concat(#{driverName}, '%'))
</if>
selectOptional查询
@Override
public ReturnT execute(final Map<String, String> map, final SchedulerTool tool) {
IfpShipment ifpShipment = new IfpShipment();
Criteria criteria = new Criteria(ifpShipment);
criteria.setWhereSql("A.pro_platform_shipment_status = 'N' AND A.vehicle_status = 'normal' AND (A.locating_information_status = 'normal' or A.locating_information_status = 'abnormal') AND A.unload_time_status = 'normal' AND A.unload_quantity_status = 'normal'");
List<IfpShipment> ifpShipmentList = ifpShipmentService.selectOptional(ifpShipment,criteria);
if(!CollectionUtils.isEmpty(ifpShipmentList)){
cuzIfpShipmentService.shipmentListPush(ifpShipmentList);
}
return ReturnT.SUCCESS;
}
