mysql - MySql 插入 JDBC

Question

我正在尝试使用 INSERT INTO SELECT 在 mysql 中使用 JDBC 执行查询

 try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/new_db?&user=root&password=");
        conn.setAutoCommit(false);
        long start = System.currentTimeMillis();

        String sql
                = "INSERT INTO `tr_tmp`(`id`, `root_group_id`, `IATANO`, `NRPB`, `PlatedCarrierCode`, `PlatedCarrierName`, "
                + "`DateOfIssue`, `DomInt`, `FlownCarrierCode`, `FlownCarrierName`, `FlightNo`, `OperatedCarriercode`, "
                + "`FlightDate`, `Class`, `Origin`, `Destination`, `CashAmt`, `ExCashAmt`, `CreditAmt`, `EXCreditAmt`, "
                + "`EffCommAmt`, `EXEffCommAmt`, `RefCashAmt`, `RefCreditAmt`, `RefEffCommAmt`, "
                + "`ACMCashAmt`, `ACMCreditAmt`, `ACMEffCommAmt`, `ADMCashAmt`, `ADMCreditAmt`, `ADMEffCommAmt`, "
                + "`Farebasis`, `TaxCashAmt`, `EXTaxCashAmt`, "
                + "`TaxCreditAmt`, `EXTaxCreditAmt`, `RefTaxCashAmt`, `RefTaxCreditAmt`, "
                + "`ACMTaxCashAmt`, `ACMTaxCreditAmt`, `ADMTaxCashAmt`, `ADMTaxCreditAmt`, "
                + " `YQTax`, `EXYQtax`, `YRTax`, `EXYRtax`, `Currency`, NFCode) "
                + "(SELECT null, 100 as groupId, a.agtn as `IATANO`, b.nrid as NRPB, m.CarrierCode as PlatedCarrierCode, m.CarrierName as PlatedCarrierName,  "
                + "c.DAIS as DateOfIssue, c.STAT as DomInt,j.CARR as FlownCarrierCode , l.CarrierName as FlownCarrierName, TRIM(LEADING '0' FROM j.FTNR) as FlightNo, j.OP_CARR as OP_CARR, "
                + "j.FLIGHTDT as FlightDate, j.RBKD as Class, j.ORAC AS Origin, j.DSTC AS Destination, k.CashAmt, k.ExCashAmt, k.CreditAmt, k.EXCreditAmt,  "
                + "k.EffCommAmt as EffCommAmt,k.EXEffCommAmt as EXEffCommAmt,  "
                + "k.RefCashAmt as RefCashAmt, k.RefCreditAmt as RefCreditAmt, k.RefEffCommAmt as RefEffCommAmt,  "
                + "k.ACMCashAmt as ACMCashAmt, k.ACMCreditAmt as ACMCreditAmt, k.ACMEffCommAmt as ACMEffCommAmt,  "
                + "k.ADMCashAmt as ADMCashAmt, k.ADMCreditAmt as ADMCreditAmt, k.ADMEffCommAmt as ADMEffCommAmt,  "
                + "j.FBTD as Farebasis,k.TaxCashAmt as TaxCashAmt, k.EXTaxCashAmt as EXTaxCashAmt,  "
                + "k.TaxCreditAmt, k.EXTaxCreditAmt, k.RefTaxCashAmt, k.RefTaxCreditAmt, "
                + "k.ACMTaxCashAmt, k.ACMTaxCreditAmt, k.ADMTaxCashAmt, k.ADMTaxCreditAmt, "
                + "k.YQTax, k.EXYQtax, k.YRTax, k.EXYRtax, k.CUTP as Currency, c.NefCode as NFCode  "
                + "FROM tp_boh03 a  "
                + "INNER JOIN tp_bkt06 b ON a.offheadid= b.offheadid  "
                + "INNER JOIN tp_tickets c ON b.transheadid= c.transheadid  "
                + "LEFT JOIN tp_bks39 f ON c.TicketHeadId = f.TicketHeadId  "
                + "INNER JOIN tp_bki63 j ON c.TicketHeadId = j.TicketHeadId  "
                + "INNER JOIN tp_mileage_fare k ON j.ItinerarySegmentID = k.ItinerarySegmentID  "
                + "INNER JOIN tp_iata ti ON a.AGTN = ti.iata_no  "
                + "LEFT JOIN tp_carriers l ON j.CARR = l.CarrierCode  "
                + "LEFT JOIN tp_carriers m ON b.TACN = m.TicketCode)";

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.execute();
        System.out.println("TIME TAKE : " + (System.currentTimeMillis() - start));

    } catch (Exception ex) {
        ex.printStackTrace();
    }

预期的数据应该插入总计 200 万行，但它并没有插入所有数据，每次运行脚本时插入的行都会发生变化。我尝试直接在 mysql 中运行脚本，并且在几次重新运行后它确实插入了相同数量的行。

标签： mysqljdbc