62,612
社区成员
发帖
与我相关
我的任务
分享
StringBuffer sql = new StringBuffer();
sql.append("insert into ").append(tableName)
.append(" values(null,?,?,?,?,?,?,?,?,?,?,?,?);");
ByteArrayOutputStream baos = null;
DataOutputStream dos = null;
ByteArrayInputStream bais = null;
try {
preStmt = conn.prepareStatement(sql.toString(),ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
//循环遍历pool,找出对应的regin以及IP段
//插入到域名表中
for (RegionBean region : regions) {
//获取pool的属性
int regionId = (int)region.getId();
int poolId = region.getPoolId();
String regionName = region.getName();
PoolBean pool = queryPool(poolId, conn);
String poolName = pool.getName();
byte lbId = pool.getLbId();
byte type = pool.getType();
int ttl = pool.getTtl();
int returnNum = pool.getMaximum();
String cname = pool.getCname();
byte verify = pool.getVerify();
List<PoolmemberBean> members = null;
if (GlobalUtil.POOL_TYPE_A == type) {
//如果不需要探测vip的可用性
members = queryAvaMemberByPoolType(poolId, lbId, verify, conn);
if (null == members) {
continue;
}
}
//取得与pool关联上的region以及region下的ip段
Date date1 = new Date();
List<IpsetBean> ipsets = queryIpsetByRegion(regionId, conn);
Date date2 = new Date();
System.out.println("get ipsets :" + (date2.getTime() - date1.getTime()));
if (null == ipsets) {
continue;
}
Date date3 = new Date();
int register = 0;
for (IpsetBean ipset : ipsets) {
register ++;
try {
long beginIp = ipset.getBeginIp();
long endIp = ipset.getEndIp();
//如果ip段被之前插入的ip包含了, 则此ip段不需要插入
if (ipIsIncludedBefore(beginIp, endIp, beginIps, endIps)) {
continue;
} else {
beginIps.add(beginIp);
endIps.add(endIp);
}
preStmt.setLong(1, beginIp);
preStmt.setLong(2, endIp);
preStmt.setInt(3, poolId);
preStmt.setString(4, poolName);
preStmt.setByte(5, lbId);
preStmt.setByte(6, type);
preStmt.setInt(7, ttl);
//如果类型是A记录,即cname为空
if (GlobalUtil.POOL_TYPE_A == type) {
//data字段按照格式插入该pool下可用的poolmember的ip和权重组合
if (GlobalUtil.POOL_LB_GA != lbId) {
//重新指定poolmember的ratio值
//计算方法是,每一个poolmember的ratio/所有poolmember的ratio综合*10000
resortRatio(members);
}
baos = new ByteArrayOutputStream();
dos = new DataOutputStream(baos);
for (PoolmemberBean bean : members) {
String ip = bean.getIp();
int ratio = bean.getRatio();
int position = bean.getPosition();
int intIp = GlobalUtil.ipToInt(ip);
short weight = 0;
if (GlobalUtil.POOL_LB_GA == lbId) {
weight = (short)position;
} else {
weight = (short)ratio;
}
dos.write(GlobalUtil.intToByteArray(intIp));
dos.write(GlobalUtil.shortToByteArray(weight));
}
bais = new ByteArrayInputStream(baos.toByteArray());
//取poolmember和pool的maximum的最小一个
preStmt.setInt(8, Math.min(members.size(), returnNum));
preStmt.setBinaryStream(9, bais, baos.size());
} else {
//否则的话, data字段直接插入域名就行
//return_num为1
preStmt.setInt(8, 1);
preStmt.setString(9, cname);
}
preStmt.setInt(10, zoneId);
preStmt.setInt(11, regionId);
preStmt.setString(12, regionName);
preStmt.addBatch();
if (register % 100 == 0) {
preStmt.executeBatch();
conn.commit();
preStmt.clearBatch();
}
} catch (IOException ioe) {
ioe.printStackTrace();
return false;
} finally {
if (null != bais) {
try {
bais.close();
} catch (IOException e) {
e.printStackTrace();
return false;
}
}
if (null != dos) {
try {
dos.close();
} catch (IOException e) {
e.printStackTrace();
return false;
}
}
if (null != baos) {
try {
baos.close();
} catch (IOException e) {
e.printStackTrace();
return false;
}
}
}
}
Date date4 = new Date();
System.out.println("add batch :" + (date4.getTime() - date3.getTime()));
Date date5 = new Date();
preStmt.executeBatch();
Date date6 = new Date();
System.out.println("execute batch :" + (date6.getTime() - date5.getTime()));
}
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
if (null != preStmt) {
try {
preStmt.close();
preStmt = null;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
}
测试结果:
数据量(条) 插入所需时间(ms)
1 0
10 15
100 62
1,000 422
10,000 2,922
100,000 26,922
1000,000 272,219