-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathMySqlTest.java
More file actions
238 lines (215 loc) · 9.2 KB
/
MySqlTest.java
File metadata and controls
238 lines (215 loc) · 9.2 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
import org.junit.Test;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Random;
/**
* @author xiaotiaowa
* @version 1.0
* @date 2020/2/10 18:30
*/
public class MySqlTest {
@Test
public void test2(){
System.out.println(Integer.toBinaryString("无假货".hashCode()));
}
@Test
public void Test(){
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "111";
Connection conn = null;
PreparedStatement pstm =null;
ResultSet rt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
String sql = "INSERT INTO ab values(?,?)";
pstm = conn.prepareStatement(sql);
Long startTime = System.currentTimeMillis();
conn.setAutoCommit(false);
for (int i = 1; i <= 100000; i++) {
pstm.setInt(1, i);
pstm.setString(2, "123");
pstm.executeQuery();
if (i%10000==0){
System.out.println("第"+i+"个");
}
}
conn.commit();
Long endTime = System.currentTimeMillis();
System.out.println("用时：" + (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);
}
}
}
}
@Test
public void Test2(){
String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";
String user = "root";
String password = "111";
Connection conn = null;
PreparedStatement pstm =null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
String sql = "INSERT INTO ab values(?,?,?,?,?,?)";
pstm = conn.prepareStatement(sql);
Long startTime = System.currentTimeMillis();
conn.setAutoCommit(false);
for (int i = 1; i <= 5000000; i++) {
//编号
pstm.setInt(1, i);
//姓名
pstm.setString(2, getName().substring(1));
//sex性别
pstm.setString(3, i%2==0?"男":"女");
int i1 = (int)(Math.random()*100);
pstm.setInt(4, i1);
pstm.setDate(5, new Date(i1));
pstm.setString(6,getRandomID());
pstm.addBatch();
if (i%100000==0){
System.out.println("第"+i+"个");
pstm.executeBatch();
conn.commit();
}
}
pstm.executeBatch();
conn.commit();
Long endTime = System.currentTimeMillis();
System.out.println("用时：" + (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);
}
}
}
}
public String getName() {
Random random = new Random();
String[] Surname = {"赵", "钱", "孙", "李", "周", "吴", "郑", "王", "冯", "陈", "褚", "卫", "蒋", "沈", "韩", "杨", "朱", "秦", "尤", "许",
"何", "吕", "施", "张", "孔", "曹", "严", "华", "金", "魏", "陶", "姜", "戚", "谢", "邹", "喻", "柏", "水", "窦", "章", "云", "苏", "潘", "葛", "奚", "范", "彭", "郎",
"鲁", "韦", "昌", "马", "苗", "凤", "花", "方", "俞", "任", "袁", "柳", "酆", "鲍", "史", "唐", "费", "廉", "岑", "薛", "雷", "贺", "倪", "汤", "滕", "殷",
"罗", "毕", "郝", "邬", "安", "常", "乐", "于", "时", "傅", "皮", "卞", "齐", "康", "伍", "余", "元", "卜", "顾", "孟", "平", "黄", "和",
"穆", "萧", "尹", "姚", "邵", "湛", "汪", "祁", "毛", "禹", "狄", "米", "贝", "明", "臧", "计", "伏", "成", "戴", "谈", "宋", "茅", "庞", "熊", "纪", "舒",
"屈", "项", "祝", "董", "梁", "杜", "阮", "蓝", "闵", "席", "季"};
String girl = "秀娟英华慧巧美娜静淑惠珠翠雅芝玉萍红娥玲芬芳燕彩春菊兰凤洁梅琳素云莲真环雪荣爱妹霞香月莺媛艳瑞凡佳嘉琼勤珍贞莉桂娣叶璧璐娅琦晶妍茜秋珊莎锦黛青倩婷姣婉娴瑾颖露瑶怡婵雁蓓纨仪荷丹蓉眉君琴蕊薇菁梦岚苑婕馨瑗琰韵融园艺咏卿聪澜纯毓悦昭冰爽琬茗羽希宁欣飘育滢馥筠柔竹霭凝晓欢霄枫芸菲寒伊亚宜可姬舒影荔枝思丽 ";
String boy = "伟刚勇毅俊峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘";
int index = random.nextInt(Surname.length - 1);
String name = Surname[index]; //获得一个随机的姓氏
int i = random.nextInt(3);//可以根据这个数设置产生的男女比例
if(i==2){
int j = random.nextInt(girl.length()-2);
if (j % 2 == 0) {
name = "女-" + name + girl.substring(j, j + 2);
} else {
name = "女-" + name + girl.substring(j, j + 1);
}
}
else{
int j = random.nextInt(girl.length()-2);
if (j % 2 == 0) {
name = "男-" + name + boy.substring(j, j + 2);
} else {
name = "男-" + name + boy.substring(j, j + 1);
}
}
return name;
}public String getRandomID() {
String id = "420222199204179999";
// 随机生成省、自治区、直辖市代码 1-2
String provinces[] = { "11", "12", "13", "14", "15", "21", "22", "23",
"31", "32", "33", "34", "35", "36", "37", "41", "42", "43",
"44", "45", "46", "50", "51", "52", "53", "54", "61", "62",
"63", "64", "65", "71", "81", "82" };
String province = randomOne(provinces);
// 随机生成地级市、盟、自治州代码 3-4
String city = randomCityCode(18);
// 随机生成县、县级市、区代码 5-6
String county = randomCityCode(28);
// 随机生成出生年月 7-14
String birth = randomBirth(20, 50);
// 随机生成顺序号 15-17(随机性别)
String no = new Random().nextInt(899) + 100+"";
// 随机生成校验码 18
String checks[] = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9",
"X" };
String check = randomOne(checks);
// 拼接身份证号码
id = province + city + county + birth + no + check;
return id;
}
/**
* 从String[] 数组中随机取出其中一个String字符串
*
* @author mingzijian
* @param s
* @return
*/
public String randomOne(String s[]) {
return s[new Random().nextInt(s.length - 1)];
}
/**
* 随机生成两位数的字符串（01-max）,不足两位的前面补0
*
* @author mingzijian
* @param max
* @return
*/
public String randomCityCode(int max) {
int i = new Random().nextInt(max) + 1;
return i > 9 ? i + "" : "0" + i;
}
/**
* 随机生成minAge到maxAge年龄段的人的生日日期
*
* @author mingzijian
* @param minAge
* @param maxAge
* @return
*/
public String randomBirth(int minAge, int maxAge) {
SimpleDateFormat dft = new SimpleDateFormat("yyyyMMdd");// 设置日期格式
Calendar date = Calendar.getInstance();
date.setTime(new java.util.Date());// 设置当前日期
// 随机设置日期为前maxAge年到前minAge年的任意一天
int randomDay = 365 * minAge
+ new Random().nextInt(365 * (maxAge - minAge));
date.set(Calendar.DATE, date.get(Calendar.DATE) - randomDay);
return dft.format(date.getTime());
}
}