数据库配置
| 阿里云配置数据库RDS |
|
| 存储类型 |
ESSD PL1 云盘 |
| 数据库内存 |
1024 M |
| 数据库类型 |
MySQL8.0 |
| CPU |
1 核 |
| 最大连接数 |
2000 |
| 最大IOPS(每秒读写速度) |
2800 |
运行环境配置：
系统：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：
@Mapper
public 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);
}测试类：
@Autowired
private SqlSessionFactory sqlSessionFactory;
@Test
void inserts() {
List<User> 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<User> 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();
}
}
}
}
}测试类：
@Test
public 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<User> list = new ArrayList<>();
for (int i = 1000000; i < 2000000; 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);
}
start = System.currentTimeMillis(); // 获取系统当前时间，方法开始执行前记录
// 不断产生sql
for (User user : list) {
ps.setString(1, String.valueOf(user.getId()));
ps.setString(2, user.getName());
ps.setString(3, user.getSex());
ps.setString(4, String.valueOf(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); // 调用刚刚写好的静态工具类释放资源
}
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;
@SpringBootTest
public class TestThread {
@Resource
DataSource dataSource;
@Test
void main2() {
// 创建一个线程池,容量为5
ExecutorService pool = Executors.newFixedThreadPool(9);
// List<Future<String>> list = new LinkedList<>(); // 存储每次任务执行结果
// 生产数据
List<User> 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<User> 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<String> f : list) {
// System.out.println(">>>" + f.get());
//}
System.out.println("程序执行完毕耗时：" + (end - start) + "毫秒");
}
/**
* 生产数据
*
* @return
*/
static List<User> createData() {
List<User> list = new ArrayList<User>();
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;
}
}配置数据源：
@Configuration
public 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<Object> {
private DataSource dataSource;
private List<User> users;
MyCallable(List<User> 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秒
这是小编在开发学习使用和总结， 这中间或许也存在着不足，希望可以得到大家的理解和建议。如有侵权联系小编！