MyBatis批量插入数据

最新推荐文章于 2024-04-19 05:25:37 发布

烟火缠过客

最新推荐文章于 2024-04-19 05:25:37 发布

阅读量1k

点赞数

文章标签： java

本文链接：https://blog.csdn.net/LuckFairyLuckBaby/article/details/88390917

版权

1.普通for循环插入数据

controller层

package com.stylefeng.guns.modular.business.controller;

import com.etone.common.util.MyJdbcUtil;
import com.stylefeng.guns.entity.MerchantConnectData;
import com.stylefeng.guns.entity.PaymentsBatch;
import com.stylefeng.guns.modular.business.service.O2oImportOrderPaymentsService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导入历史订单支付(支付单号sdb_ectolls_payments)明细数据
 */
@Controller
@RequestMapping("/import")
public class O2oImportOrderPaymentsController {

    @Autowired
    private O2oImportOrderPaymentsService o2oImportOrderPaymentsService;

    private static Logger logger = LoggerFactory.getLogger(O2oImportOrderPaymentsController.class);

    @RequestMapping("/payments_history")
    public void importData(){
        logger.info("导入数据开始>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
        //开始时间
        long startTime = System.currentTimeMillis();
        //查询所有的连接商户库信息
        List<MerchantConnectData> connectDataList = o2oImportOrderPaymentsService.queryConnectMerchantKu();
        //如果数据不为空
        if(connectDataList != null && connectDataList.size()>0){
            //遍历所有的商户链接信息取出对应的数据
            for (int i = 0; i < connectDataList.size(); i++) {
                //获取信息
                String yklBn = connectDataList.get(i).getYklBn();  //商户号   订单号+商户号 组成新的订单号防止重复
                String mHost = connectDataList.get(i).getmHost();  //主数据库地址
                String mUserName = connectDataList.get(i).getmUserName();  //主数据库用户名
                String mUserPassword = connectDataList.get(i).getmUserPassword();  //主数据库密码
                String mDbName = connectDataList.get(i).getmDbName();  //主数据库的名字 和商户对应 一个商户一个库
                String sourceName = o2oImportOrderPaymentsService.querySourceName(yklBn); //商圈名
                logger.info("开始连接数据库【"+mDbName+"】>>>>>>>>>>");
                Connection connection = null;
                PreparedStatement preparedStatement1 = null;
                try {
                    //创建StringBuffer  最终结果是：jdbc:mysql://127.0.0.1:3306/etoneo2o
                    StringBuffer sbf = new StringBuffer();
                    sbf.append("jdbc:mysql://");
                    sbf.append(mHost);
                    sbf.append(":");
                    sbf.append("3306"); //端口都是3306
                    sbf.append("/");
                    sbf.append(mDbName);
                    sbf.append("?useUnicode=true&characterEncoding=utf-8&useSSL=true&autoReconnect=true");
                    //1.加载 驱动程序，获取数据库连接
                    MyJdbcUtil.setJdbcParam("com.mysql.jdbc.Driver",sbf.toString(),mUserName,mUserPassword);
                    //2.通过数据库连接操作数据库，实现增删改查
                    connection =MyJdbcUtil.connect();
                    //因为本次导出历史数据是首次所以不用考虑是否重复直接查询然后插入到数据库中即可
                    String SdbEctoolsPaymentsSql = "select sep.payment_id,sep.money,sep.pay_app_id,sep.t_confirm,seob.rel_id from sdb_ectools_payments as sep,sdb_ectools_order_bills as seob where seob.bill_id = sep.payment_id;";
                    preparedStatement1 = connection.prepareStatement(SdbEctoolsPaymentsSql);
                    ResultSet resultSet = preparedStatement1.executeQuery();
                    List<PaymentsBatch> pbList = new ArrayList<PaymentsBatch>();
                    //遍历数据 set集合无序 不可重复
                    while (resultSet.next()){
                        PaymentsBatch paymentsBatch = new PaymentsBatch();
                        //获取数据
                        String payment_id = resultSet.getString("payment_id");
                        String money = resultSet.getString("money");
                        String pay_app_id = resultSet.getString("pay_app_id");
                        String t_confirm = resultSet.getString("t_confirm");
                        String rel_id = resultSet.getString("rel_id");  //原生的订单id
                        //对rel_id做处理，前面拼接商户号防止重复
                        String order_id = yklBn + rel_id;  //处理后的orderId
                        paymentsBatch.setPaymentId(payment_id);
                        paymentsBatch.setMoney(money);
                        paymentsBatch.setPayAppId(pay_app_id);
                        paymentsBatch.setTconfirm(t_confirm);
                        paymentsBatch.setOrderId(order_id);
                        pbList.add(paymentsBatch);
                        o2oImportOrderPaymentsService.insertEctoolsPayments(payment_id,money,pay_app_id,t_confirm,order_id,sourceName,yklBn);
                    }
//                    o2oImportOrderPaymentsService.batchInsertEctoolsPayments(pbList);
                    long endTime = System.currentTimeMillis();
                    logger.info("插入数据成功总记用时【"+(startTime-endTime)+"】毫秒");
                } catch (Exception e) {
                    logger.info("连接数据库异常" + e.getMessage() + "!");
                    e.printStackTrace();
                }finally{
                    //关闭资源
                    if(preparedStatement1 != null){
                        try {
                            preparedStatement1.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                    if(connection != null){
                        try {
                            connection.close();
                        } catch (SQLException e) {

                        }
                    }
                }

            }
        }else{
            logger.info("没有对应的数据可以处理!");
        }
    }


}

service层

package com.stylefeng.guns.modular.business.service;

import com.stylefeng.guns.entity.MerchantConnectData;
import com.stylefeng.guns.entity.PaymentsBatch;

import java.util.List;

public interface O2oImportOrderPaymentsService {
    List<MerchantConnectData> queryConnectMerchantKu();

    String querySourceName(String yklBn);

    void insertEctoolsPayments(String payment_id, String money, String pay_app_id, String t_confirm, String order_id, String sourceName, String yklBn);

    void batchInsertEctoolsPayments(List<PaymentsBatch> pbList);
}

实现层


    @Override
    public void insertEctoolsPayments(String payment_id, String money, String pay_app_id, String t_confirm, String order_id, String sourceName, String yklBn) {
        o2oImportOrderPaymentsMapper.insertEctoolsPayments(payment_id,money,pay_app_id,t_confirm,order_id,sourceName,yklBn);
    }

mapper

void insertEctoolsPayments(@Param("payment_id") String payment_id, @Param("money") String money, @Param("pay_app_id") String pay_app_id, @Param("t_confirm") String t_confirm, @Param("order_id") String order_id, @Param("sourceName") String sourceName, @Param("yklBn") String yklBn);

mapper.xml

<!--插入数据-->
    <insert id="insertEctoolsPayments"  parameterType="java.lang.String">
        insert into sdb_ectools_payments (payment_id,money,pay_app_id,t_confirm,order_id,source_name,merchant_bn) values(#{payment_id},#{money},#{pay_app_id},#{t_confirm},#{order_id},#{sourceName},#{yklBn})
    </insert>

用时

2.批量插入

controller层

package com.stylefeng.guns.modular.business.controller;

import com.etone.common.util.MyJdbcUtil;
import com.stylefeng.guns.entity.MerchantConnectData;
import com.stylefeng.guns.entity.PaymentsBatch;
import com.stylefeng.guns.modular.business.service.O2oImportOrderPaymentsService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导入历史订单支付(支付单号sdb_ectolls_payments)明细数据
 */
@Controller
@RequestMapping("/import")
public class O2oImportOrderPaymentsController {

    @Autowired
    private O2oImportOrderPaymentsService o2oImportOrderPaymentsService;

    private static Logger logger = LoggerFactory.getLogger(O2oImportOrderPaymentsController.class);

    @RequestMapping("/payments_history")
    public void importData(){
        logger.info("导入数据开始>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
        //开始时间
        long startTime = System.currentTimeMillis();
        //查询所有的连接商户库信息
        List<MerchantConnectData> connectDataList = o2oImportOrderPaymentsService.queryConnectMerchantKu();
        //如果数据不为空
        if(connectDataList != null && connectDataList.size()>0){
            //遍历所有的商户链接信息取出对应的数据
            for (int i = 0; i < connectDataList.size(); i++) {
                //获取信息
                String yklBn = connectDataList.get(i).getYklBn();  //商户号   订单号+商户号 组成新的订单号防止重复
                String mHost = connectDataList.get(i).getmHost();  //主数据库地址
                String mUserName = connectDataList.get(i).getmUserName();  //主数据库用户名
                String mUserPassword = connectDataList.get(i).getmUserPassword();  //主数据库密码
                String mDbName = connectDataList.get(i).getmDbName();  //主数据库的名字 和商户对应 一个商户一个库
                String sourceName = o2oImportOrderPaymentsService.querySourceName(yklBn); //商圈名
                logger.info("开始连接数据库【"+mDbName+"】>>>>>>>>>>");
                Connection connection = null;
                PreparedStatement preparedStatement1 = null;
                try {
                    //创建StringBuffer  最终结果是：jdbc:mysql://127.0.0.1:3306/etoneo2o
                    StringBuffer sbf = new StringBuffer();
                    sbf.append("jdbc:mysql://");
                    sbf.append(mHost);
                    sbf.append(":");
                    sbf.append("3306"); //端口都是3306
                    sbf.append("/");
                    sbf.append(mDbName);
                    sbf.append("?useUnicode=true&characterEncoding=utf-8&useSSL=true&autoReconnect=true");
                    //1.加载 驱动程序，获取数据库连接
                    MyJdbcUtil.setJdbcParam("com.mysql.jdbc.Driver",sbf.toString(),mUserName,mUserPassword);
                    //2.通过数据库连接操作数据库，实现增删改查
                    connection =MyJdbcUtil.connect();
                    //因为本次导出历史数据是首次所以不用考虑是否重复直接查询然后插入到数据库中即可
                    String SdbEctoolsPaymentsSql = "select sep.payment_id,sep.money,sep.pay_app_id,sep.t_confirm,seob.rel_id from sdb_ectools_payments as sep,sdb_ectools_order_bills as seob where seob.bill_id = sep.payment_id;";
                    preparedStatement1 = connection.prepareStatement(SdbEctoolsPaymentsSql);
                    ResultSet resultSet = preparedStatement1.executeQuery();
                    List<PaymentsBatch> pbList = new ArrayList<PaymentsBatch>();
                    //遍历数据 set集合无序 不可重复
                    while (resultSet.next()){
                        PaymentsBatch paymentsBatch = new PaymentsBatch();
                        //获取数据
                        String payment_id = resultSet.getString("payment_id");
                        String money = resultSet.getString("money");
                        String pay_app_id = resultSet.getString("pay_app_id");
                        String t_confirm = resultSet.getString("t_confirm");
                        String rel_id = resultSet.getString("rel_id");  //原生的订单id
                        //对rel_id做处理，前面拼接商户号防止重复
                        String order_id = yklBn + rel_id;  //处理后的orderId
                        paymentsBatch.setPaymentId(payment_id);
                        paymentsBatch.setMoney(money);
                        paymentsBatch.setPayAppId(pay_app_id);
                        paymentsBatch.setTconfirm(t_confirm);
                        paymentsBatch.setOrderId(order_id);
                        pbList.add(paymentsBatch);
                        //o2oImportOrderPaymentsService.insertEctoolsPayments(payment_id,money,pay_app_id,t_confirm,order_id,sourceName,yklBn);
                    }
                    o2oImportOrderPaymentsService.batchInsertEctoolsPayments(pbList);
                    long endTime = System.currentTimeMillis();
                    logger.info("插入数据成功总记用时【"+(startTime-endTime)+"】毫秒");
                } catch (Exception e) {
                    logger.info("连接数据库异常" + e.getMessage() + "!");
                    e.printStackTrace();
                }finally{
                    //关闭资源
                    if(preparedStatement1 != null){
                        try {
                            preparedStatement1.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                    if(connection != null){
                        try {
                            connection.close();
                        } catch (SQLException e) {

                        }
                    }
                }

            }
        }else{
            logger.info("没有对应的数据可以处理!");
        }
    }


}

service层

 void batchInsertEctoolsPayments(List<PaymentsBatch> pbList);

implement层

@Override
    public void batchInsertEctoolsPayments(List<PaymentsBatch> pbList) {
        o2oImportOrderPaymentsMapper.batchInsertEctoolsPayments(pbList);
    }

mapper层

void batchInsertEctoolsPayments(@Param("pbList") List<PaymentsBatch> pbList);

xml

<!--批量插入数据-->
    <insert id="batchInsertEctoolsPayments" parameterType="java.util.List">
        insert into sdb_ectools_payments (payment_id,money,pay_app_id,t_confirm,order_id,source_name,merchant_bn) values
        <foreach collection="pbList" item="item" index="index" separator=",">
            (
                #{item.paymentId,jdbcType=VARCHAR},
                #{item.money,jdbcType=VARCHAR},
                #{item.payAppId,jdbcType=VARCHAR},
                #{item.tconfirm,jdbcType=VARCHAR},
                #{item.orderId,jdbcType=VARCHAR},
                #{item.sourceName,jdbcType=VARCHAR},
                #{item.merchantBn,jdbcType=VARCHAR}
            )
        </foreach>
    </insert>

结果

注意：foreach循环的时候括号写在内部，不可以用open（ close ）的方式否则会报错

烟火缠过客

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MyBatis批量插入数据

1.普通for循环插入数据controller层package com.stylefeng.guns.modular.business.controller;import com.etone.common.util.MyJdbcUtil;import com.stylefeng.guns.entity.MerchantConnectData;import com.stylefen...
复制链接

扫一扫