数据库配置

运行环境配置：

系统：win10  运行内存：16GB

方案介绍

使用for循环批量插入

        优势：JDBC中的PreparedStatement有预编译功能，预编译会缓存起来，后面的SQL执行会比较快。

        劣势：很多时候SQL服务和应用服务可能不是同一台服务器，所有必须要考虑网络IO，如果网络IO比较费时间的话，就可能拖慢SQL执行的速度。

准备工作

通过随机生成人的姓名、年龄、性别、电话、email、地址 ，向mysql数据库大量插入数据，便于用大量的数据测试。

随机生成用户信息工具类：

import java.util.Random;/ * @author 小影 * @create 2022-06-14  * @describe： */public class RandomInfo {    //复姓出现的几率(0--100)    private static int surnameProbability = 5;    private static Random random = new Random();    private static String familyOneName = "赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻水云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任" +     "袁柳鲍史唐费岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅卞齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计成戴宋茅庞熊纪舒屈项祝董粱杜阮" +     "席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田胡凌霍万柯卢莫房缪干解应宗丁宣邓郁单杭洪包诸左石崔吉龚程邢滑裴陆荣翁荀羊甄家封芮储靳邴" +     "松井富乌焦巴弓牧隗山谷车侯伊宁仇祖武符刘景詹束龙叶幸司韶黎乔苍双闻莘劳逄姬冉宰桂牛寿通边燕冀尚农温庄晏瞿茹习鱼容向古戈终居衡步都耿满弘" +     "国文东殴沃曾关红游盖益桓公晋楚闫";    private static String familyTwoName = "欧阳太史端木上官司马东方独孤南宫万俟闻人夏侯诸葛尉迟公羊赫连澹台皇甫宗政濮阳公冶太叔申屠公孙慕容仲孙钟离长孙宇" +     "文司徒鲜于司空闾丘子车亓官司寇巫马公西颛孙壤驷公良漆雕乐正宰父谷梁拓跋夹谷轩辕令狐段干百里呼延东郭南门羊舌微生公户公玉公仪梁丘公仲公上" +     "公门公山公坚左丘公伯西门公祖第五公乘贯丘公皙南荣东里东宫仲长子书子桑即墨达奚褚师吴铭";    private static String boyName = "伟刚勇毅俊峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达" +     "安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽" +     "晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘";    private static String girlName = "秀娟英华慧巧美娜静淑惠珠翠雅芝玉萍红娥玲芬芳燕彩春菊兰凤洁梅琳素云莲真环雪荣爱妹霞香月莺媛艳瑞凡佳嘉琼勤珍贞莉桂娣叶璧" +     "璐娅琦晶妍茜秋珊莎锦黛青倩婷姣婉娴瑾颖露瑶怡婵雁蓓纨仪荷丹蓉眉君琴蕊薇菁梦岚苑婕馨瑗琰韵融园艺咏卿聪澜纯毓悦昭冰爽琬茗羽希宁欣飘育滢馥" +     "筠柔竹霭凝晓欢霄枫芸菲寒伊亚宜可姬舒影荔枝思丽";    private static String phoneTwoNum = "3578";    private static String[] province = {"河北省","山西省","辽宁省","吉林省","黑龙江省","江苏省","浙江省","安徽省","福建省","江西省","山东省","河南省","湖北省","湖南省","广东省","海南省","四川省","贵州省","云南省","陕西省","甘肃省","青海省","台湾省",};    private static String[] city = {"安康市","安庆市","安顺市","安阳市","鞍山市","巴彦淖尔市","巴中市","白城市","白山市","白银市","百色市","蚌埠市","包头市","宝鸡市","保定市","保山市","北海市","本溪市","滨州市","沧州市","昌都地区","长春市","长沙市","长治市","常德市","常州市","巢湖市","朝阳市","潮州市","郴州市","成都市","承德市","池州市","赤峰市","崇左市","滁州市","达州市","大连市","大庆市","大同市","丹东市","德阳市","德州市","定西市","东莞市","东营市","鄂尔多斯市","鄂州市","防城港市","佛山市","福州市","抚顺市","抚州市","阜新市","阜阳市","甘南州","赣州市","固原市","广安市","广元市","广州市","贵港市","贵阳市","桂林市","哈尔滨市","哈密地区","海北藏族自治州","海东地区","海口市","邯郸市","汉中市","杭州市","毫州市","合肥市","河池市","河源市","菏泽市","贺州市","鹤壁市","鹤岗市","黑河市","衡水市","衡阳市","呼和浩特市","呼伦贝尔市","湖州市","葫芦岛市","怀化市","淮安市","淮北市","淮南市","黄冈市","黄山市","黄石市","惠州市","鸡西市","吉安市","吉林市","济南市","济宁市","佳木斯市","嘉兴市","嘉峪关市","江门市","焦作市","揭阳市","金昌市","金华市","锦州市","晋城市","晋中市","荆门市","荆州市","景德镇市","九江市","酒泉市","开封市","克拉玛依市","昆明市","拉萨市","来宾市","莱芜市","兰州市","廊坊市","乐山市","丽江市","丽水市","连云港市","辽阳市","辽源市","聊城市","临沧市","临汾市","临沂市","柳州市","六安市","六盘水市","龙岩市","陇南市","娄底市","泸州市","吕梁市","洛阳市","漯河市","马鞍山市","茂名市","眉山市","梅州市","绵阳市","牡丹江市","内江市","南昌市","南充市","南京市","南宁市","南平市","南通市","南阳市","宁波市","宁德市","攀枝花市","盘锦市","平顶山市","平凉市","萍乡市","莆田市","濮阳市","普洱市","七台河市","齐齐哈尔市","钦州市","秦皇岛市","青岛市","清远市","庆阳市","曲靖市","衢州市","泉州市","日照市","三门峡市","三明市","三亚市","汕头市","汕尾市","商洛市","商丘市","上饶市","韶关市","邵阳市","绍兴市","深圳市","沈阳市","十堰市","石家庄市","石嘴山市","双鸭山市","朔州市","四平市","松原市","苏州市","宿迁市","宿州市","绥化市","随州市","遂宁市","台州市","太原市","泰安市","泰州市","唐山市","天水市","铁岭市","通化市","通辽市","铜川市","铜陵市","铜仁市","吐鲁番地区","威海市","潍坊市","渭南市","温州市","乌海市","乌兰察布市","乌鲁木齐市","无锡市","吴忠市","芜湖市","梧州市","武汉市","武威市","西安市","西宁市","锡林郭勒盟","厦门市","咸宁市","咸阳市","湘潭市","襄樊市","孝感市","忻州市","新乡市","新余市","信阳市","兴安盟","邢台市","徐州市","许昌市","宣城市","雅安市","烟台市","延安市","盐城市","扬州市","阳江市","阳泉市","伊春市","伊犁哈萨克自治州","宜宾市","宜昌市","宜春市","益阳市","银川市","鹰潭市","营口市","永州市","榆林市","玉林市","玉溪市","岳阳市","云浮市","运城市","枣庄市","湛江市","张家界市","张家口市","张掖市","漳州市","昭通市","肇庆市","镇江市","郑州市","中山市","中卫市","舟山市","周口市","株洲市","珠海市","驻马店市","资阳市","淄博市","自贡市","遵义市",};    private static String[] area = {"伊春区","带岭区","南岔区","金山屯区","西林区","美溪区","乌马河区","翠峦区","友好区","新青区","上甘岭区","五营区","红星区","汤旺河区","乌伊岭区","榆次区"};    private static String[] road = {"黄河路","中原路","安波路","新四路","安汾路","安福路","安国路","安化路","安澜路","安龙路","安仁路","安顺路","安亭路","安图路","安业路","安义路","安远路","鞍山路","鞍山支路","澳门路","八一路","巴林路","白城路","白城南路","白渡路","白渡桥","白兰路","白水路","白玉路","百安路（方泰镇）","百官街","百花街","百色路","板泉路","半淞园路","包头路","包头南路","宝安公路","宝安路","宝昌路","宝联路","宝林路","宝祁路","宝山路","宝通路","宝杨路","宝源路","保德路","保定路","保屯路","保屯路","北艾路",};    private static String[] home = {"金色家园","耀江花园","阳光翠竹苑","东新大厦","溢盈河畔别墅","真新六街坊","和亭佳苑","协通公寓","博泰新苑","菊园五街坊","住友嘉馨名园","复华城市花园","爱里舍花园"};    private static String passwordForm = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789abcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+{}|?";    /     * 生成随机数     */    public static int randomInt() { return random.nextInt();    }    /     * 生成随机数(最大值限制)     */    public static int randomInt(int maxNum) { return random.nextInt(maxNum);    }    /     * 获取随机男生姓名     */    public static String getRandomBoyName(){ int bodNameIndexOne = randomInt(boyName.length()); int bodNameIndexTwo = randomInt(boyName.length()); if (randomInt(100) > surnameProbability){     int familyOneNameIndex = randomInt(familyOneName.length());     return familyOneName.substring(familyOneNameIndex, familyOneNameIndex+1) +      boyName.substring(bodNameIndexOne, bodNameIndexOne+1) +      boyName.substring(bodNameIndexTwo, bodNameIndexTwo+1); }else {     int familyTwoNameIndex = randomInt(familyTwoName.length());     familyTwoNameIndex = familyTwoNameIndex%2 == 0 ? familyTwoNameIndex : familyTwoNameIndex-1;     return familyTwoName.substring(familyTwoNameIndex, familyTwoNameIndex+2) +      boyName.substring(bodNameIndexOne, bodNameIndexOne+1) +      boyName.substring(bodNameIndexTwo, bodNameIndexTwo+1); }    }    /     * 获取女生姓名     */    public static String getRandomGirlName(){ int bodNameIndexOne = randomInt(girlName.length()); int bodNameIndexTwo = randomInt(girlName.length()); if (randomInt(100) > surnameProbability){     int familyOneNameIndex = randomInt(familyOneName.length());     return familyOneName.substring(familyOneNameIndex, familyOneNameIndex+1) +      girlName.substring(bodNameIndexOne, bodNameIndexOne+1) +      girlName.substring(bodNameIndexTwo, bodNameIndexTwo+1); }else {     int familyTwoNameIndex = randomInt(familyTwoName.length());     familyTwoNameIndex = familyTwoNameIndex%2 == 0 ? familyTwoNameIndex : familyTwoNameIndex-1;     return familyTwoName.substring(familyTwoNameIndex, familyTwoNameIndex+2) +      girlName.substring(bodNameIndexOne, bodNameIndexOne+1) +      girlName.substring(bodNameIndexTwo, bodNameIndexTwo+1); }    }    /     * 获取随机手机号     */    public static String getRandomPhone(){ int phoneTwoRandomIndex = randomInt(4); return "1" + phoneTwoNum.substring(phoneTwoRandomIndex, phoneTwoRandomIndex+1) + (100000000 + randomInt(899999999));    }    /     * 获取随机qq邮箱     */    public static String getRandomQQEmail(){ return (""+random.nextLong()).substring(10) + "@qq.com";    }    /     * 获取随机性别     */    public static String getRandomSex(){ return randomInt(2)%2 == 0 ? "男" : "女";    }    /     * 获取随机年龄     */    public static int getRandomAge(int min, int max){ return min + random.nextInt(max-min);    }    /     * 获取随机年龄(18-25)     */    public static int getRandomAge(){ return getRandomAge(18, 25);    }    /     * 获取随机住址     */    public static String getRandomAddress(){ return province[randomInt(province.length)] +  city[randomInt(city.length)] +  area[randomInt(area.length)] +  road[randomInt(road.length)] +  home[randomInt(home.length)];    }    /     * 获取随机密码(指定长度)     */    public static String getRandomPassword(int length){ StringBuilder springBuilder = new StringBuilder(); for (int i = 0; i < length; i++) {     int index = randomInt(passwordForm.length());     springBuilder.append(passwordForm.substring(index, index+1)); } return springBuilder.toString();    }    /     * 获取随机密码(指定长度)     */    public static String getRandomPassword(){ return getRandomPassword(12);    }    public static void main(String[] args) { for (int i = 0; i < 100; i++) {     //获取性别和name     if (randomInt(2)%2 == 0){  System.out.print("男: "+getRandomBoyName()+"   ");     }else {  System.out.print("女: "+getRandomGirlName()+"   ");     }     //获取年龄     System.out.print("年龄: "+getRandomAge()+"   ");     //获取密码     System.out.print("密码: "+getRandomPassword()+"   ");     //获取邮箱     System.out.print("邮箱: "+getRandomQQEmail()+"   ");     //获取地址     System.out.print("地址: "+getRandomAddress()+"   ");     System.out.println(); }    }}

数据库表

CREATE TABLE `user` (  `id` int NOT NULL,  `name` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,  `sex` varchar(2) NOT NULL,  `age` tinyint NOT NULL,  `email` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,  `phone` varchar(13) NOT NULL,  `address` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

MyBatis写法：

Mapper：

@Mapperpublic interface UserMapper {    @Insert("insert into `user` (id,`name`,sex,age,email,phone,address) values (#{user.id},#{user.name},#{user.sex},#{user.age},#{user.email},#{user.phone},#{user.address})")    int insert(@Param("user")User user);}

测试类： 

@Autowiredprivate SqlSessionFactory sqlSessionFactory;@Testvoid inserts() {    List list = new ArrayList();    for (int i = 0; i < 1000000; i++) { User user = new User(); user.setId(i); user.setName(RandomInfo.getRandomBoyName()); user.setSex(RandomInfo.getRandomSex()); user.setAge(RandomInfo.getRandomAge(1, 100)); user.setEmail(RandomInfo.getRandomQQEmail()); user.setPhone(RandomInfo.getRandomPhone()); user.setAddress(RandomInfo.getRandomAddress()); //执行插入语句 list.add(user);    }    System.out.println("数据准备完毕,开始导入...");    insertusers(list);}private void insertusers(List users) {    SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);    UserMapper um = session.getMapper(UserMapper.class);    long startTime = System.currentTimeMillis();    users.forEach(user -> { um.insert(user);    });    session.commit();    long endTime = System.currentTimeMillis();    long l = endTime - startTime;    System.out.printf("一条条插入 %s SQL 耗费时间 =( %d ) 毫秒", users.size() + "条",l );}

虽然是一条一条的插入，但是我们要开启批处理模式（BATCH），这样前前后后就只用这一个 SqlSession，如果不采用批处理模式，反反复复的获取 Connection 以及释放 Connection 会耗费大量时间，效率奇低，这种效率奇低的方式就测试了。

测试结果：74秒，可能会因为网络、数据结构、数据复杂度导致每个人耗费时间不一致，仅供参考

JDBC写法

BaseDao：数据库连接 、释放连接

import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class BaseDao {  //  静态工具类，用于创建数据库连接对象和释放资源，方便调用    //    导入驱动jar包或添加Maven依赖（这里使用的是Maven，Maven依赖代码附在文末）    static { try {     Class.forName("com.mysql.cj.jdbc.Driver"); } catch (ClassNotFoundException e) {     e.printStackTrace(); }    }     //  获取数据库连接对象    public static Connection getConn() { Connection conn = null; try {     //  rewriteBatchedStatements=true,一次插入多条数据，只插入一次     conn = DriverManager.getConnection("jdbc:mysql://数据库连接地址:3306/test_db?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true", "账号", "密码"); } catch (SQLException throwables) {     throwables.printStackTrace(); } return conn;    }     //  释放资源    public static void closeAll(AutoCloseable... autoCloseables) { for (AutoCloseable autoCloseable : autoCloseables) {     if (autoCloseable != null) {  try {      autoCloseable.close();  } catch (Exception e) {      // TODO Auto-generated catch block      e.printStackTrace();  }     } }    }}

测试类：

@Testpublic void m2() {    Connection conn = BaseDao.getConn(); //  调用刚刚写好的用于获取连接数据库对象的静态工具类    String sql = "insert into `user` values (?,?,?,?,?,?,?)";  //  要执行的sql语句    PreparedStatement ps = null;    long start = 0L;    try { ps = conn.prepareStatement(sql);    //  获取PreparedStatement对象 // 生产数据 List list = new ArrayList(); for (int i = 1000000; i  0) {     System.out.println("已成功添加一百万条数据！！"); }    } catch (SQLException throwables) { throwables.printStackTrace();    } finally { BaseDao.closeAll(conn, ps);  //  调用刚刚写好的静态工具类释放资源    }    long end = System.currentTimeMillis();  //  再次获取系统时间    System.out.println("所用时长:" + (end - start) / 1000 + "秒"); }

测试结果：68秒

多线程JDBC：

import com.ying.model.User;import com.ying.utils.RandomInfo;import org.junit.jupiter.api.Test;import org.springframework.boot.test.context.SpringBootTest;import javax.annotation.Resource;import javax.sql.DataSource;import java.util.ArrayList;import java.util.List;import java.util.concurrent.Callable;import java.util.concurrent.ExecutorService;import java.util.concurrent.Executors;import java.util.concurrent.Future;import java.util.stream.Collectors;import java.util.stream.Stream;@SpringBootTestpublic class TestThread {    @Resource    DataSource dataSource;    @Test    void main2() { // 创建一个线程池,容量为5 ExecutorService pool = Executors.newFixedThreadPool(9); //   List<Future> list = new LinkedList(); // 存储每次任务执行结果 // 生产数据 List result = createData(); long start = System.currentTimeMillis(); System.out.println("随机生成用户信息完毕"); // 数据分成多组 int applyIdSelectSize = 1000;//分多少次执行 int limit = (result.size() + applyIdSelectSize - 1) / applyIdSelectSize; //每次处理多少条数据 Stream.iterate(0, n -> n + 1).limit(limit).forEach(a -> {     List paperEntityList = result.stream().skip(a * applyIdSelectSize).limit(applyIdSelectSize).collect(Collectors.toList());     Callable c = new MyCallable(paperEntityList,dataSource);// 放入线程     // 执行任务并获取Future对象     Future f = pool.submit(c); // 提交任务,并返回结果     // list.add(f);// 收集任务结果 }); // 关闭线程池：会在所有任务执行完成后关闭线程池，但并不会阻塞程序运行 pool.shutdown(); // 等待所有子线程执行完毕 while (true) {     if (pool.isTerminated()) {// 如果所有子线程全部执行完毕结束程序  System.err.println(pool.getClass().getName() + " end");  break;     }     try {  Thread.sleep(1);     } catch (InterruptedException e) {  e.printStackTrace();     } } long end = System.currentTimeMillis(); // 获取所有并发任务的运行结果 //for (Future f : list) { //    System.out.println(">>>" + f.get()); //} System.out.println("程序执行完毕耗时：" + (end - start) + "毫秒");    }    /     * 生产数据     *     * @return     */    static List createData() { List list = new ArrayList(); for (int i = 0; i < 1000000; i++) {     User user = new User();     user.setId(i);     user.setName(RandomInfo.getRandomBoyName());     user.setSex(RandomInfo.getRandomSex());     user.setAge(new Byte(String.valueOf(RandomInfo.getRandomAge(1, 100))));     user.setEmail(RandomInfo.getRandomQQEmail());     user.setPhone(RandomInfo.getRandomPhone());     user.setAddress(RandomInfo.getRandomAddress());     //执行插入语句     list.add(user); } return list;    }}

配置数据源：

@Configurationpublic class DruidConfig {    / * 配置绑定 * @return */    @Bean    @ConfigurationProperties(prefix = "spring.datasource")    public DruidDataSource druid() { return new DruidDataSource();    }}

YML：

spring:  datasource:    username: 数据库账号    password: 数据库密码    driver-class-name: com.mysql.jdbc.Driver    url: jdbc:mysql://数据库连接地址:3306/test_db?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&useSSL=false    type: com.alibaba.druid.pool.DruidDataSource    druid:      initial-size: 50 # 连接池的最大数据库连接数。设为0表示无限制。      max-active: 200 # 最大建立连接等待时间。如果超过此时间将接到异常。设为-1表示无限制。      max-wait: 600000  # 连接池中的最小空闲连接数，Druid会定时扫描连接池的连接，如果空闲的连接数大于该值，则关闭多余的连接，反之则创建更多的连接以满足最小连接数要求。      min-idle: 5      timeBetweenEvictionRunsMillis: 60000 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接      validationQuery: SELECT 1 FROM DUAL      testWhileIdle: true      testOnBorrow: false      testOnReturn: false      #最大PSCache连接      maxPoolPreparedStatementPerConnectionSize: 20      useGlobalDataSourceStat: true

线程类：

import com.ying.demo.BaseDao;import com.ying.model.User;import javax.sql.DataSource;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.List;import java.util.concurrent.Callable;class MyCallable implements Callable {    private DataSource dataSource;    private List users;    MyCallable(List users, DataSource dataSource) { this.users = users; this.dataSource = dataSource;    }    public Object call() throws Exception { Connection conn = dataSource.getConnection();      //  Connection conn = BaseDao.getConn(); //  调用刚刚写好的用于获取连接数据库对象的静态工具类 String sql = "insert into `user` values (?,?,?,?,?,?,?)";  //  要执行的sql语句 PreparedStatement ps = null; try {     ps = conn.prepareStatement(sql);    //  获取PreparedStatement对象     //  不断产生sql     for (User user : users) {  ps.setInt(1, user.getId());  ps.setString(2, user.getName());  ps.setString(3, user.getSex());  ps.setByte(4, user.getAge());  ps.setString(5, user.getEmail());  ps.setString(6, user.getPhone());  ps.setString(7, user.getAddress());  ps.addBatch();  //  将一组参数添加到此 PreparedStatement 对象的批处理命令中。     }     int[] ints = ps.executeBatch();//   将一批命令提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。     //  如果数组长度不为0，则说明sql语句成功执行，即百万条数据添加成功！     //if (ints.length > 0) {     //    System.out.println("添加一页完成！！");     //} } catch (SQLException throwables) {     throwables.printStackTrace(); } //finally {   //  BaseDao.closeAll(conn, ps);  //  调用刚刚写好的静态工具类释放资源      //  } return "ok";    }}

测试结果： 41秒

这是小编在开发学习使用和总结，  这中间或许也存在着不足，希望可以得到大家的理解和建议。如有侵权联系小编！