先上代码:
controller:
result = service.insertTRbXdhjLendYdData(params);
service:
List<TRbXdhjLendDTO> dtoList = (List<TRbXdhjLendDTO>) paramMap.get("jsonList");
//批量插入
dao.insertYdData(dtoList);
dao:
/**
* 存储从工厂库抽取的易贷数据的放款信息
* @param paramMap
* @return
*/
public int insertYdData(List<TRbXdhjLendDTO> list);
mapper:
<!-- 插入从工厂抽取的易贷数据的放款计划信息 -->
<insert id="insertYdData" parameterType="java.util.List">
insert into t_rb_xdhj_lend
(
id ,
submit_date ,
company_no ,
company_name ,
rgu_org_id ,
rgu_org_name ,
version ,
source_code ,
batch_num ,
loan_code ,
loan_start_time ,
loan_end_time ,
loan_name ,
user_idcard ,
user_idcard_hash ,
loan_use ,
type ,
loan_describe ,
amount ,
rate ,
term_type ,
term ,
pay_type ,
service_cost ,
risk_margin ,
loan_type ,
collateral_desc ,
create_user_id ,
create_date ,
validate_state ,
corporation_id ,
send_time ,
remark ,
product_code
)
select seq_SYS_APP_ERROR_INFO.nextval id,A.* from(
<foreach collection="list" item="dto" index="index" separator="UNION">
select
#{dto.submitDate,jdbcType=DATE} submit_date
,#{dto.companyNo,jdbcType=VARCHAR} company_no
,#{dto.companyName,jdbcType=VARCHAR} company_name
,#{dto.rguOrgId,jdbcType=DECIMAL} rgu_org_id
,#{dto.rguOrgName,jdbcType=VARCHAR} rgu_org_name
,#{dto.version,jdbcType=VARCHAR} version
,#{dto.sourceCode,jdbcType=VARCHAR} source_code
,#{dto.batchNum,jdbcType=VARCHAR} batch_num
,#{dto.loanCode,jdbcType=VARCHAR} loan_code
,#{dto.loanStartTime,jdbcType=VARCHAR} loan_start_time
,#{dto.loanEndTime,jdbcType=VARCHAR} loan_end_time
,#{dto.loanName,jdbcType=VARCHAR} loan_name
,#{dto.userIdcard,jdbcType=VARCHAR} user_idcard
,#{dto.userIdcardHash,jdbcType=VARCHAR} user_idcard_hash
,#{dto.loanUse,jdbcType=VARCHAR} loan_use
,#{dto.type,jdbcType=VARCHAR} type
,#{dto.loanDescribe,jdbcType=VARCHAR} loan_describe
,#{dto.amount,jdbcType=VARCHAR} amount
,#{dto.rate,jdbcType=VARCHAR} rate
,#{dto.termType,jdbcType=VARCHAR} term_type
,#{dto.term,jdbcType=VARCHAR} term
,#{dto.payType,jdbcType=VARCHAR} pay_type
,#{dto.serviceCost,jdbcType=VARCHAR} service_cost
,#{dto.riskMargin,jdbcType=VARCHAR} risk_margin
,#{dto.loanType,jdbcType=VARCHAR} loan_type
,#{dto.collateralDesc,jdbcType=VARCHAR} collateral_desc
,#{dto.createUserId,jdbcType=DECIMAL} create_user_id
,systimestamp create_date
,'1' validate_state
,#{dto.corporationId,jdbcType=DECIMAL} corporation_id
,#{dto.sendTime,jdbcType=TIMESTAMP} send_time
,#{dto.remark,jdbcType=VARCHAR} remark
,#{dto.productCode,jdbcType=VARCHAR} product_code
from dual
</foreach>
) A
</insert>
1、新增的数据含有ORACLE自增长序列;
2、union all 会报ORA:02287-此处不允许序号;