动态更新
<update id="updateElevator" parameterType="com.diantijiang.saas.data.elevator.Elevator" >
update dc_elevator
<set>
<if test="inspectionCode != null">inspection_code = #{inspectionCode,jdbcType=VARCHAR},</if>
<if test="brandCode!=null">brand_code = #{brandCode},</if>
<if test="modelSpecs!=null">model_specs = #{modelSpecs},</if>
<if test="elevatorType!=null">elevator_type=#{elevatorType},</if>
<if test="idDistrict!=null">id_district=#{idDistrict},</if>
<if test="idPropertyCompany!=null">id_property_company=#{idPropertyCompany},</if>
<if test="idServiceCompany!=null">id_service_company=#{idServiceCompany},</if>
<if test="idServiceManager!=null">id_service_manager=#{idServiceManager},</if>
<if test="contractCode!=null">contract_code=#{contractCode},</if>
<if test="runStatus!=null">run_status=#{runStatus},</if>
<if test="qrCodeCheck!=null">qr_code_check=#{qrCodeCheck},</if>
<if test="qrCodeProduct!=null">qr_code_product=#{qrCodeProduct},</if>
<if test="lastAnnualDate!=null">last_annual_date=#{lastAnnualDate},</if>
<if test="nextAnnualDate!=null">next_annual_date=#{nextAnnualDate},</if>
<if test="lastMaintainDate!=null">last_maintain_date=#{lastMaintainDate},</if>
<if test="nextMaintainDate!=null">next_maintain_date=#{nextMaintainDate},</if>
<if test="repairTimes!=null">repair_times=#{repairTimes},</if>
<if test="maintainTimes!=null">maintain_times=#{maintainTimes},</if>
<if test="accessoriesExchanges!=null">accessories_exchanges=#{accessoriesExchanges},</if>
<if test="installDate!=null">Install_date=#{installDate},</if>
<if test="status!=null">status=#{status},</if>
<if test="createdBy!=null">created_by=#{createdBy},</if>
<if test="createdDate!=null">created_date=#{createdDate},</if>
<if test="updatedBy!=null">updated_by=#{updatedBy},</if>
<if test="updatedDate!=null">updated_date=#{updatedDate}</if>
</set>
where
id_elevator = #{idElevator}
</update>
批量动态插入
说明:当前方式在插入一条时正常,插入多条时会出现拼接错误!如果需要多条插入请参照修订方法!
<insert id="batchInsert" parameterType="java.util.List" >
insert into dc_elevator
(
<foreach collection="list" separator="," item="item" index="index">
<if test="item.inspectionCode != null">inspection_code,</if>
<if test="item.brandCode!=null">brand_code,</if>
<if test="item.modelSpecs!=null">model_specs,</if>
<if test="item.elevatorType!=null">elevator_type,</if>
<if test="item.idDistrict!=null">id_district,</if>
<if test="item.idPropertyCompany!=null">id_property_company,</if>
<if test="item.idServiceCompany!=null">id_service_company,</if>
<if test="item.idServiceManager!=null">id_service_manager,</if>
<if test="item.contractCode!=null">contract_code,</if>
<if test="item.runStatus!=null">run_status,</if>
<if test="item.qrCodeCheck!=null">qr_code_check,</if>
<if test="item.qrCodeProduct!=null">qr_code_product,</if>
<if test="item.lastAnnualDate!=null">last_annual_date,</if>
<if test="item.nextAnnualDate!=null">next_annual_date,</if>
<if test="item.lastMaintainDate!=null">last_maintain_date,</if>
<if test="item.nextMaintainDate!=null">next_maintain_date,</if>
<if test="item.repairTimes!=null">repair_times,</if>
<if test="item.maintainTimes!=null">maintain_times,</if>
<if test="item.accessoriesExchanges!=null">accessories_exchanges,</if>
<if test="item.installDate!=null">Install_date,</if>
<if test="item.status!=null">status,</if>
<if test="item.createdBy!=null">created_by,</if>
<if test="item.createdDate!=null">created_date,</if>
<if test="item.updatedBy!=null">updated_by,</if>
<if test="item.updatedDate!=null">updated_date,</if>
id_elevator
</foreach>
)
values
<foreach collection="list" index="index" item="item" separator=",">
(
<if test="item.inspectionCode != null">#{item.inspectionCode},</if>
<if test="item.brandCode!=null">#{item.brandCode},</if>
<if test="item.modelSpecs!=null">#{item.modelSpecs},</if>
<if test="item.elevatorType!=null">#{item.elevatorType},</if>
<if test="item.idDistrict!=null">#{item.idDistrict},</if>
<if test="item.idPropertyCompany!=null">#{item.idPropertyCompany},</if>
<if test="item.idServiceCompany!=null">#{item.idServiceCompany},</if>
<if test="item.idServiceManager!=null">#{item.idServiceManager},</if>
<if test="item.contractCode!=null">#{item.contractCode},</if>
<if test="item.runStatus!=null">#{item.runStatus},</if>
<if test="item.qrCodeCheck!=null">#{item.qrCodeCheck},</if>
<if test="item.qrCodeProduct!=null">#{item.qrCodeProduct},</if>
<if test="item.lastAnnualDate!=null">#{item.lastAnnualDate},</if>
<if test="item.nextAnnualDate!=null">#{item.nextAnnualDate},</if>
<if test="item.lastMaintainDate!=null">#{item.lastMaintainDate},</if>
<if test="item.nextMaintainDate!=null">#{item.nextMaintainDate},</if>
<if test="item.repairTimes!=null">#{item.repairTimes},</if>
<if test="item.maintainTimes!=null">#{item.maintainTimes},</if>
<if test="item.accessoriesExchanges!=null"> #{item.accessoriesExchanges},</if>
<if test="item.installDate!=null">#{item.installDate},</if>
<if test="item.status!=null">#{item.status},</if>
<if test="item.createdBy!=null">#{item.createdBy},</if>
<if test="item.createdDate!=null">#{item.createdDate},</if>
<if test="item.updatedBy!=null">#{item.updatedBy},</if>
<if test="item.updatedDate!=null">#{item.updatedDate},</if>
#{item.idElevator}
)
</foreach>
</insert>
修订方法
实体类
@Repository(value = "log")
public class Log {
private String id;
private String userid;
private String time;
private String type;
private String detail;
private String ip;
dao层
@Service(value = "logDao")
public interface ILogDao {
boolean batchInsert(
@Param("key") Log log,
@Param("value") List<Log> list
);
}
mapper.xml
<insert id="batchInsert" > -- parameterType="java.util.HashMap"
INSERT INTO log(
<if test="null != key.userid"> userid, </if>
<if test="null != key.detail"> detail, </if>
<if test="null != key.ip"> ip, </if>
id
)
VALUES
<foreach collection="value" index="index" item="item" separator=",">
(
<if test="null != item.userid">#{item.userid},</if>
<if test="null != item.detail"> #{item.detail}, </if>
<if test="null != item.ip"> #{item.ip}, </if>
#{item.id}
)
</foreach>
</insert>
工具类:计算对象非空属性的数量
package com.amayadream.webchat.utils;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Map;
public class ObjectFieldUtil {
public static int calculateNotEmptyFieldsNum(Object object) {
int num = 0;
if (object != null) {
Class<?> entity = object.getClass();
Field[] fields = entity.getDeclaredFields();
for (Field field : fields) {
try {
field.setAccessible(true);
if (field.get(object) != null && !"".equals(field.get(object))) {
num ++;
break;
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
return num;
}
}
动态插入-返回主键
<insert id="insertCardWithId" parameterType="com.diantijiang.saas.account.entity.BankCard">
/*先插入,再将主键返回实体*/
<selectKey resultType="java.lang.Long" order="AFTER" keyProperty="cardId">
SELECT LAST_INSERT_ID()
</selectKey>
/*插入*/
INSERT INTO ac_bank_card
<trim prefix="(" suffix=")" suffixOverrides=",">
bank_name,
card_number,
<if test="null != expiryDate and ''!= expiryDate">expiry_date,</if>
ower,
account_id
</trim>
VALUES
<trim prefix="(" suffix=")" suffixOverrides=",">
#{bankName},
#{cardNumber},
<if test="null != expiryDate and ''!=expiryDate">
#{expiryDate},
</if>
#{ower},
#{accountId}
</trim>
</insert>
|