原生jdbc生成百万级测试数据

朝闻道H
已于 2023-07-17 23:25:00 修改
阅读量117
点赞数
文章标签： java 开发语言数据库
于 2023-07-12 11:33:58 首次发布
本文链接：https://blog.csdn.net/weixin_75260656/article/details/131678001
版权
根据自己的需求，相应的更改
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.util.Random;

class BatchInsertExample {

    public static final Random random = new Random();
    private static Logger logger = LoggerFactory.getLogger(BatchInsertExample.class);
    private static final String DB_URL = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&serverTimezone=UTC&useSSL=false&rewriteBatchedStatements=true";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    public static void main(String[] args) {
        LocalDateTime startTime = LocalDateTime.of(2020, 1, 1, 0, 0, 0);
        LocalDateTime endTime = LocalDateTime.of(2023, 7, 10, 23, 59, 59);

        long start = System.currentTimeMillis();//开始计时【单位：毫秒】

        Connection connection = null;
        PreparedStatement statement = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(DB_URL, USER, PASSWORD);
            connection.setAutoCommit(false); // 关闭自动提交

            String insertQuery = "INSERT INTO practitioners(practitioners_id, practitioners_name,practitioners_Idcard,practitioners_tel,practitioners_address,practitioners_sex,create_time) VALUES (null, ?,?,?,?,?,?)";
            statement = connection.prepareStatement(insertQuery);

            int batchSize = 1000; // 每次批量提交的记录数
            int count = 0;

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

                statement.setString(1, generateRandomName());
                statement.setString(2, generateRandomID());
                statement.setString(3, generateRandomPhoneNumber());
                statement.setString(4, generateRandomAddress());
                statement.setString(5, generateRandomGender());
                statement.setString(6, generateRandomCreationTime(startTime, endTime));
                statement.addBatch();

                if (i % 1000 == 0) {
                    statement.executeBatch(); // 执行最后一批（不满batchSize的记录）
                    connection.commit(); // 提交事务
                }

            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            if (connection != null) {
                try {
                    connection.rollback(); // 回滚事务
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        logger.info("百万条数据插入用时：" + (System.currentTimeMillis() - start) + "【单位：毫秒】");
    }
//==============================================================================================================
 // 姓氏列表
    private static final String[] surnames = {
            "赵", "钱", "孙", "李", "周", "吴", "郑", "王", "冯", "陈", "褚", "卫", "蒋", "沈", "韩", "杨", "朱", "秦", "尤", "许",
            "何", "吕", "施", "张", "孔", "曹", "严", "华", "金", "魏", "陶", "姜", "戚", "谢", "邹", "喻", "柏", "水", "窦", "章",
            "云", "苏", "潘", "葛", "奚", "范", "彭", "郎", "鲁", "韦", "昌", "马", "苗", "凤", "花", "方", "俞", "任", "袁", "柳",
            "邓", "鲍", "史", "唐", "费", "廉", "岑", "薛", "雷", "贺", "倪", "汤", "滕", "殷", "罗", "毕", "郝", "邬", "安", "常",
            "乐", "于", "时", "傅", "皮", "卞", "齐", "康", "伍", "余", "元", "卜", "顾", "孟", "平", "黄", "和", "穆", "萧", "尹",
            "姚", "邵", "湛", "汪", "祁", "毛", "禹", "狄", "米", "贝", "明", "臧", "计", "伏", "成", "戴", "谈", "宋", "茅", "庞",
            "熊", "纪", "舒", "屈", "项", "祝", "董", "梁"
    }
    // 随机生成姓名
    public static String generateRandomName() {
        Random random = new Random();
        String surname = surnames[random.nextInt(surnames.length)];
        String givenName = getRandomChineseCharacters(random.nextInt(2) + 1); // 生成1-2个汉字的名字
        return surname + givenName;
    }

    // 随机生成指定长度的汉字字符串
    private static String getRandomChineseCharacters(int length) {
        StringBuilder stringBuilder = new StringBuilder();
        for (int i = 0; i < length; i++) {
            stringBuilder.append(getRandomChineseCharacter());
        }
        return stringBuilder.toString();
    }

    // 随机生成一个汉字字符
    private static char getRandomChineseCharacter() {
        String str = "";
        int highPos, lowPos;
        Random random = new Random();
        highPos = (176 + Math.abs(random.nextInt(39)));
        lowPos = (161 + Math.abs(random.nextInt(93)));
        byte[] bArr = new byte[2];
        bArr[0] = (new Integer(highPos)).byteValue();
        bArr[1] = (new Integer(lowPos)).byteValue();
        try {
            str = new String(bArr, "GB2312");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return str.charAt(0);
    }
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// 随机生成地区码
private static String getRandomAreaCode() {
    // 根据实际需求返回合法的地区码，如根据规则选择特定省份的地区码
    return "610900"; // 北京市
}

    // 随机生成生日日期（格式：yyyyMMdd）
    private static String getRandomBirthday() {
        int year = getRandomNumberInRange(1900, 2023);
        int month = getRandomNumberInRange(1, 12);
        int day = getRandomNumberInRange(1, 28); // 假设每个月都取28号
        return String.format("%04d%02d%02d", year, month, day);
    }

    // 随机生成顺序码（格式：三位数字）
    private static String getRandomSequenceCode() {
        int sequence = getRandomNumberInRange(1, 999);
        return String.format("%03d", sequence);
    }

    // 通过前17位生成校验位
    private static char getCheckDigit(String prefix17) {
        int[] coefficient = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};
        int sum = 0;
        for (int i = 0; i < prefix17.length(); i++) {
            char c = prefix17.charAt(i);
            int digit = Character.digit(c, 10);
            sum += coefficient[i] * digit;
        }
        int remainder = sum % 11;
        char[] checkDigits = {'1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'};
        return checkDigits[remainder];
    }

    // 获取指定范围内的随机数
    private static int getRandomNumberInRange(int min, int max) {
        Random random = new Random();
        return random.nextInt(max - min + 1) + min;
    }

    // 生成随机的身份证号码
    public static String generateRandomID() {
        String areaCode = getRandomAreaCode();
        String birthday = getRandomBirthday();
        String sequenceCode = getRandomSequenceCode();
        String prefix17 = areaCode + birthday + sequenceCode;
        char checkDigit = getCheckDigit(prefix17);
        return prefix17 + checkDigit;
    }
    //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    // 随机生成手机号码
    public static String generateRandomPhoneNumber() {
        // 手机号码前三位
        String[] phonePrefix = { "130", "131", "132", "133", "134", "135", "136", "137", "138", "139",
                "150", "151", "152", "153", "155", "156", "157", "158", "159",
                "186", "187", "188", "189" };

        Random random = new Random();

        String prefix = phonePrefix[random.nextInt(phonePrefix.length)];
        String suffix = String.format("%08d", random.nextInt(100000000));

        return prefix + suffix;
    }
    //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    // 城市列表
    private static final String[] cities = {
            "旬阳县"
    };

    // 街道名称列表
    private static final String[] streets = {
            "中山路", "人民路", "建设路", "解放路", "和平路", "文化路", "学府路", "工业路", "胜利街", "新华街", "长江街", "光明街", "迎宾街",
            "友谊街", "环城路", "幸福路", "自由路", "红旗路", "民主路", "广场街", "平安街", "凯旋路", "太阳路", "世纪街", "教育路", "科技路",
            "鼓楼街", "东风路", "金海路", "朝阳路", "海滨路", "山东路", "济南街", "水木街", "湖光路", "荣华街", "双龙路", "新城街", "幸福街",
            "和平街", "白云路", "西安街", "长安街", "青年街", "人民街", "红星路", "珠江路", "黄河路", "昆仑路", "长城街"
    };

    // 随机生成住址
    public static String generateRandomAddress() {
        Random random = new Random();
        String city = cities[random.nextInt(cities.length)];
        String street = streets[random.nextInt(streets.length)];
        int buildingNumber = random.nextInt(100) + 1;
        int unitNumber = random.nextInt(20) + 1;
        int roomNumber = random.nextInt(10) + 1;
        return city + street + buildingNumber + "号" + unitNumber + "单元" + roomNumber + "室";
    }
    //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    // 随机生成性别
    public static String generateRandomGender() {
        Random random = new Random();
        int genderCode = random.nextInt(2); // 0代表男性，1代表女性
        if (genderCode == 0) {
            return "男";
        } else {
            return "女";
        }
    }
//    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


        // 随机生成指定时间段内的创建时间
        public static String generateRandomCreationTime(LocalDateTime startTime, LocalDateTime endTime) {
            Random random = new Random();

            long startMillis = startTime.toInstant(ZoneOffset.UTC).toEpochMilli();
            long endMillis = endTime.toInstant(ZoneOffset.UTC).toEpochMilli();

            // 随机生成开始时间和结束时间之间的时间毫秒数
            long randomMillis = startMillis + (long) (random.nextDouble() * (endMillis - startMillis));

            return String.valueOf(LocalDateTime.ofInstant(Instant.ofEpochMilli(randomMillis), ZoneId.systemDefault()));
        }



}