JDBC批量插入Demo-Toy模板网

这篇具有很好参考价值的文章主要介绍了JDBC批量插入Demo。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

无参的addBatch()方法，用于将最近一次调用 prepareStatement() 准备的SQL语句添加到批处理命令中。在添加到批处理中之前，需要设置SQL语句中的参数。
有参的addBatch(String sql)方法，用于将指定的SQL语句添加到批处理命令中。这种形式的addBatch()方法不需要调用 prepareStatement()，但是需要在SQL语句中设置参数。

/**
 * @author hrui
 * @date 2023/4/21 4:25
 */
public class InsertData {
    private static final int BATCH_SIZE = 10000;
    private static final int TOTAL_ROWS = 1000000;
    private static final String URL = "";
    private static final String USER = "";
    private static final String PASSWORD = "";
    public static void main(String[] args) {
        List<Person> list=InsertData.getPersonWithArg();
        System.out.println(list.size());
        for(Person p:list){
            System.out.println(p);
        }


        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            connection.setAutoCommit(false);
            StringBuilder sqlBuilder = new StringBuilder("INSERT INTO person (name,age,gender,qq) VALUES ");
            try (PreparedStatement statement = connection.prepareStatement(sqlBuilder.toString())) {
                for (int i = 1; i <= TOTAL_ROWS; i++) {
                    sqlBuilder.append("('" + list.get(i-1).getName()+"','"+list.get(i-1).getAge() + "','"+list.get(i-1).getGender() + "','"+list.get(i-1).getQq()+ "'),");//这么其实也有SQL注入风险(sql注入不单指SELECT,个人理解)
                    if (i % BATCH_SIZE == 0) {
                        sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
                        statement.addBatch(sqlBuilder.toString());
                        statement.executeBatch();/没必要clear 分号可以不加
                        connection.commit();
                        System.out.println("提交"+i/BATCH_SIZE+"次");
                        sqlBuilder = new StringBuilder("INSERT INTO person (name,age,gender,qq) VALUES ");
                    }
                }
                if (TOTAL_ROWS % BATCH_SIZE != 0) {
                    sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
                    statement.addBatch(sqlBuilder.toString());
                    statement.executeBatch();/
                    connection.commit();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();

if(connection!=null){
    try {
        connection.rollback();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

        }

    }


    // 模拟数据
    public static List<Person> getPersonWithArg(){
        List<Person> list=new ArrayList<>();
        //由一个StringBuffer来代替之前4个String的反复字符串操作。
        //StringBuffer sb = new StringBuffer("");// String = "";
        StringBuilder sb=new StringBuilder("");
        for (int r = 0; r < 1000000 ; r++) {
            Person person = new Person();
            //随机产生姓名
            for (int i = 0; i < 3; i++) {
                sb.append((char) (0x4e00 + (Math.random() * (0x9fa5 - 0x4e00 + 1))));
            }
            person.setName(sb.toString());
            //用完后清楚里面的内容，以供下一次使用
            sb.delete(0,sb.length());

            //随机产生性别
            int sexf = (int)(Math.random() * 2);//[0,2)
            if(sexf==0){
                sb.append("男");
            }else if( sexf == 1){
                sb.append("女");
            }else {
                sb.append("Unknow");
            }
            person.setGender(sb.toString());
            sb.delete(0,sb.length());
            //随机产生年龄
            int d;
            for (int i = 0; i < 2; i++) {
                d = (int)(Math.random()*10);
                sb.append(d);
            }
            person.setAge(sb.toString());
            sb.delete(0,sb.length());

            // 随机产生qq
            int j;
            for (int i = 0; i < 10; i++) {
                d = (int)(Math.random()*10);
                sb.append(d);
            }
            person.setQq(sb.toString());
            sb.delete(0,sb.length());

            list.add(person);
        }
        return list;
    }

}
class Person {
    private String id;
    private String name;
    private String age;
    private String qq;
    private String gender;

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getQq() {
        return qq;
    }

    public void setQq(String qq) {
        this.qq = qq;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Person person = (Person) o;
        return Objects.equals(id, person.id) && Objects.equals(name, person.name) && Objects.equals(age, person.age) && Objects.equals(qq, person.qq) && Objects.equals(gender, person.gender);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, name, age, qq, gender);
    }

    @Override
    public String toString() {
        return "Person{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", age='" + age + '\'' +
                ", qq='" + qq + '\'' +
                ", gender='" + gender + '\'' +
                '}';
    }
}

又测试了一个请指正文章来源地址https://www.toymoban.com/news/detail-424359.html

public class InsertData {
    private static final int BATCH_SIZE = 10000;
    private static final int TOTAL_ROWS = 1000000;
    private static final String URL = "";
    private static final String USER = "";
    private static final String PASSWORD = "";
    public static void main(String[] args) {

        List<Person> list = InsertData.getPersonWithArg();
        System.out.println(list.size());
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            connection.setAutoCommit(false);
            StringBuilder sqlBuilder = new StringBuilder("INSERT INTO person (name,age,gender,qq) VALUES ");
            for (int i = 1; i <= TOTAL_ROWS; i++) {
                sqlBuilder.append("(?, ?, ?, ?),");
            }
            sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
            try (PreparedStatement statement = connection.prepareStatement(sqlBuilder.toString())) {
                int parameterIndex = 1; // 设置参数位置
                for (int i = 1; i <= TOTAL_ROWS; i++) {

                    statement.setString(parameterIndex++, list.get(i-1).getName());
                    statement.setString(parameterIndex++, list.get(i-1).getAge());
                    statement.setString(parameterIndex++, list.get(i-1).getGender());
                    statement.setString(parameterIndex++, list.get(i-1).getQq());

                }
                statement.addBatch();
                statement.executeBatch();
                connection.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();

if(connection!=null){
    try {
        connection.rollback();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

        }

    }


    // 模拟数据
    public static List<Person> getPersonWithArg(){
        List<Person> list=new ArrayList<>();
        //由一个StringBuffer来代替之前4个String的反复字符串操作。
        //StringBuffer sb = new StringBuffer("");// String = "";
        StringBuilder sb=new StringBuilder("");
        for (int r = 0; r < 1000000 ; r++) {
            Person person = new Person();
            //随机产生姓名
            for (int i = 0; i < 3; i++) {
                sb.append((char) (0x4e00 + (Math.random() * (0x9fa5 - 0x4e00 + 1))));
            }
            person.setName(sb.toString());
            //用完后清楚里面的内容，以供下一次使用
            sb.delete(0,sb.length());

            //随机产生性别
            int sexf = (int)(Math.random() * 2);//[0,2)
            if(sexf==0){
                sb.append("男");
            }else if( sexf == 1){
                sb.append("女");
            }else {
                sb.append("Unknow");
            }
            person.setGender(sb.toString());
            sb.delete(0,sb.length());
            //随机产生年龄
            int d;
            for (int i = 0; i < 2; i++) {
                d = (int)(Math.random()*10);
                sb.append(d);
            }
            person.setAge(sb.toString());
            sb.delete(0,sb.length());

            // 随机产生qq
            int j;
            for (int i = 0; i < 10; i++) {
                d = (int)(Math.random()*10);
                sb.append(d);
            }
            person.setQq(sb.toString());
            sb.delete(0,sb.length());

            list.add(person);
        }
        return list;
    }

}

到了这里，关于JDBC批量插入Demo的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！