java ssm批量插入代码

字数 434阅读 260

private String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";

    private String user = "root";

    private String password = "123456";

//    private String msql = "INSERT INTO  `orders`( `wid`, `pid`, `brand_id`, `orderid`, `state`, `pay_type`, `trade_type`, `ordertype`, `vender_id`, `order_source`, `category_id`, `vender_order_date`, `wname`, `uname`, `enc_uname`, `telephone`, `province`, `city`, `ask_price`, `ask_price_max`, `final_amount`, `gain_amount`, `wnum`, `created`, `distribute_time`, `modified`)" +

//            "  VALUES (200118231, 144815, 10201, '1000001454', 9, 3, 1, 4, 200118, 2, 303, '2018-06-25 18:25:35', '奢侈品测试', 'testcai1', 'fzXl4RnftkWNYabXJm8HEA==', '159****5487', '北京', '朝阳区', 123.00, 1231.00, 0.00, 0.00, 1, '2018-06-25 18:25:34', NULL, '2018-06-25 18:40:03')";

    private String msqlvalue1 ="(200155531, 155515, 13331, '1000044454', 9, 3, 1, 4, 200118, 2, 303, '2017-06-25 18:25:35', '奢侈品测试', 'testcai1', 'fzXl4RnftkWNYabXJm8HEA==', '159****5487', '北京', '朝阳区', 123.00, 1231.00, 0.00, 0.00, 1, '2017-06-25 18:25:34', NULL, '2017-06-25 18:40:03')";

    private String msqlvalue2="(202255531, 133515, 11131, '1002244454', 9, 3, 1, 4, 211118, 2, 303, '2017-02-25 18:25:35', '奢侈品测试', 'testcai1', 'fzXl4RnftkWNYabXJm8HEA==', '159****5487', '北京', '朝阳区', 123.00, 1231.00, 0.00, 0.00, 1, '2017-02-25 18:25:34', NULL, '2017-02-25 18:40:03')";

    private String msql = "INSERT into `order` (`updateat`) VALUES ('2015-04-25 18:25:34'), ('2018-01-25 18:25:34'),('2017-02-25 18:25:34')";

    @Test

    public void Test() {

        Connection conn = null;

        Statement pstm = null;

        ResultSet rt = null;

        try {

            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection(url, user, password);

            conn.setAutoCommit(false);

            pstm = conn.createStatement();

            Long startTime = System.currentTimeMillis();

            for(int i = 0 ; i < 3; i++){

                if (i %3 == 0){

                    msql += ",('2016-02-25 18:25:34')";

                }else {

                    msql += ",('2016-11-25 18:25:34')";

                }

}

            for (int i = 1; i <= 2000000; i++) {

                pstm.addBatch(msql);

            }

            pstm.executeBatch();

            conn.commit();

            Long endTime = System.currentTimeMillis();

            System.out.println("OK,用时:" + (endTime - startTime));

        } catch (Exception e) {

            e.printStackTrace();

            throw new RuntimeException(e);

        } finally {

            if (pstm != null) {

                try {

                    pstm.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                    throw new RuntimeException(e);

                }

}

            if (conn != null) {

                try {

                    conn.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                    throw new RuntimeException(e);

                }

}

}

    }

推荐阅读更多精彩内容