IP基础知识与分配实现

  1 package com.ccb.cloud.nw.ip.data.entity;
  2 // default package
  3 
  4 import javax.persistence.Column;
  5 import javax.persistence.Entity;
  6 import javax.persistence.Id;
  7 import javax.persistence.Table;
  8 import javax.persistence.Transient;
  9 
 10 
 11 
 12 /**
 13  * NwCclassPo entity. @author MyEclipse Persistence Tools
 14  */
 15 @Entity
 16 @Table(name="RM_NW_CCLASS")
 17 
 18 public class NwCclassPo  implements java.io.Serializable {
 19 
 20 
 21     // Fields    
 22 
 23      private Long cclassId;
 24      private Long bclassId;
 25      private String cclassTypeCode;
 26      private String secureAreaCode;
 27      private String cclassName;
 28      private String subnetmask;
 29      private String gateway;
 30      private Long vlanId;
 31      private String isActive;
 32      private String secureTierCode;
 33      private Integer aclassIp;
 34      private Integer bclassIp;
 35      private Integer cclassIp;
 36      private Integer ipStart;
 37      private Integer ipEnd;
 38      
 39      private Integer ipTotalCnt;
 40      private Integer ipAvailCnt;
 41      
 42      private Long datacenterId;
 43      private Long moduleId;
 44      private Long vmManagerServerId;
 45      private Long convergeId;
 46      private String routersId;
 47      
 48      @Transient
 49      private Integer useIpNum;
 50      @Transient
 51      private Integer unUseIpNum;
 52      @Transient
 53      private Long resPoolId;
 54      @Transient
 55      private String resPoolType;
 56      @Transient
 57      private String netArea;
 58 //     private RmNwSubnetmaskExtPo rmNwSubnetmaskExtPo;
 59 
 60     // Constructors
 61 
 62     /** default constructor */
 63     public NwCclassPo() {
 64     }
 65 
 66     /** minimal constructor */
 67     public NwCclassPo(Long cclassId, Long bclassId) {
 68         this.cclassId = cclassId;
 69         this.bclassId = bclassId;
 70     }
 71     
 72     /** full constructor */
 73     public NwCclassPo(Long cclassId, Long bclassId, String cclassTypeCode, String secureAreaCode, String cclassName, 
 74             String subnetmask, String gateway, Long vlanId, String isActive, String secureTierCode, Integer aclassIp, 
 75             Integer bclassIp, Integer cclassIp, Integer ipStart, Integer ipEnd,Integer ipTotalCnt,Integer ipAvailCnt,Long datacenterId) {
 76         this.cclassId = cclassId;
 77         this.bclassId = bclassId;
 78         this.cclassTypeCode = cclassTypeCode;
 79         this.secureAreaCode = secureAreaCode;
 80         this.cclassName = cclassName;
 81         this.subnetmask = subnetmask;
 82         this.gateway = gateway;
 83         this.vlanId = vlanId;
 84         this.isActive = isActive;
 85         this.secureTierCode = secureTierCode;
 86         this.aclassIp = aclassIp;
 87         this.bclassIp = bclassIp;
 88         this.cclassIp = cclassIp;
 89         this.ipStart = ipStart;
 90         this.ipEnd = ipEnd;
 91         
 92         this.ipTotalCnt=ipTotalCnt;
 93         this.ipAvailCnt=ipAvailCnt;
 94         this.datacenterId=datacenterId;
 95     }
 96 
 97    
 98     // Property accessors
 99     @Id 
100     
101     @Column(name="CCLASS_ID", unique=true, nullable=false, precision=18, scale=0)
102 
103     public Long getCclassId() {
104         return this.cclassId;
105     }
106     
107     public void setCclassId(Long cclassId) {
108         this.cclassId = cclassId;
109     }
110        
111     @Column(name = "VCENTER_ID", nullable =true, precision = 18, scale = 0)
112     public Long getVmManagerServerId() {
113         return vmManagerServerId;
114     }
115 
116     public void setVmManagerServerId(Long vmManagerServerId) {
117         this.vmManagerServerId = vmManagerServerId;
118     }
119 
120     @Column(name = "MODULE_ID", nullable =true, precision = 18, scale = 0)
121     public Long getModuleId() {
122         return moduleId;
123     }
124 
125     public void setModuleId(Long moduleId) {
126         this.moduleId = moduleId;
127     }
128     
129     @Column(name="BCLASS_ID", nullable=false, precision=18, scale=0)
130 
131     public Long getBclassId() {
132         return this.bclassId;
133     }
134     
135     public void setBclassId(Long bclassId) {
136         this.bclassId = bclassId;
137     }
138     
139     @Column(name="CCLASS_TYPE_CODE", length=32)
140 
141     public String getCclassTypeCode() {
142         return this.cclassTypeCode;
143     }
144     
145     public void setCclassTypeCode(String cclassTypeCode) {
146         this.cclassTypeCode = cclassTypeCode;
147     }
148     
149     @Column(name="SECURE_AREA_CODE", length=32)
150 
151     public String getSecureAreaCode() {
152         return this.secureAreaCode;
153     }
154     
155     public void setSecureAreaCode(String secureAreaCode) {
156         this.secureAreaCode = secureAreaCode;
157     }
158     
159     @Column(name="CCLASS_NAME", length=100)
160 
161     public String getCclassName() {
162         return this.cclassName;
163     }
164     
165     public void setCclassName(String cclassName) {
166         this.cclassName = cclassName;
167     }
168     
169     @Column(name="SUBNETMASK", length=20)
170 
171     public String getSubnetmask() {
172         return this.subnetmask;
173     }
174     
175     public void setSubnetmask(String subnetmask) {
176         this.subnetmask = subnetmask;
177     }
178     
179     @Column(name="GATEWAY", length=20)
180 
181     public String getGateway() {
182         return this.gateway;
183     }
184     
185     public void setGateway(String gateway) {
186         this.gateway = gateway;
187     }
188     
189     @Column(name="VLAN_ID", precision=18, scale=0)
190 
191     public Long getVlanId() {
192         return this.vlanId;
193     }
194     
195     public void setVlanId(Long vlanId) {
196         this.vlanId = vlanId;
197     }
198     
199     @Column(name="IS_ACTIVE", length=1)
200 
201     public String getIsActive() {
202         return this.isActive;
203     }
204     
205     public void setIsActive(String isActive) {
206         this.isActive = isActive;
207     }
208     
209     @Column(name="SECURE_TIER_CODE", length=32)
210 
211     public String getSecureTierCode() {
212         return this.secureTierCode;
213     }
214     
215     public void setSecureTierCode(String secureTierCode) {
216         this.secureTierCode = secureTierCode;
217     }
218     
219     @Column(name="ACLASS_IP", precision=3, scale=0)
220 
221     public Integer getAclassIp() {
222         return this.aclassIp;
223     }
224     
225     public void setAclassIp(Integer aclassIp) {
226         this.aclassIp = aclassIp;
227     }
228     
229     @Column(name="BCLASS_IP", precision=3, scale=0)
230 
231     public Integer getBclassIp() {
232         return this.bclassIp;
233     }
234     
235     public void setBclassIp(Integer bclassIp) {
236         this.bclassIp = bclassIp;
237     }
238     
239     @Column(name="CCLASS_IP", precision=3, scale=0)
240 
241     public Integer getCclassIp() {
242         return this.cclassIp;
243     }
244     
245     public void setCclassIp(Integer cclassIp) {
246         this.cclassIp = cclassIp;
247     }
248     
249     @Column(name="IP_START", precision=3, scale=0)
250 
251     public Integer getIpStart() {
252         return this.ipStart;
253     }
254     
255     public void setIpStart(Integer ipStart) {
256         this.ipStart = ipStart;
257     }
258     
259     @Column(name="IP_END", precision=3, scale=0)
260 
261     public Integer getIpEnd() {
262         return this.ipEnd;
263     }
264     
265     public void setIpEnd(Integer ipEnd) {
266         this.ipEnd = ipEnd;
267     }
268     
269     //
270     @Column(name="IP_TOTAL_CNT", precision=3, scale=0)
271 
272     public Integer getIpTotalCnt() {
273         return this.ipTotalCnt;
274     }
275     
276     public void setIpTotalCnt(Integer ipTotalCnt) {
277         this.ipTotalCnt = ipTotalCnt;
278     }
279    
280     @Column(name="IP_AVAIL_CNT", precision=3, scale=0)
281 
282     public Integer getIpAvailCnt() {
283         return this.ipAvailCnt;
284     }
285 
286     public void setIpAvailCnt(Integer ipAvailCnt) {
287         this.ipAvailCnt = ipAvailCnt;
288     }
289     @Column(name = "DATACENTER_ID",length=18)
290     public Long getDatacenterId() {
291         return datacenterId;
292     }
293 
294     public void setDatacenterId(Long datacenterId) {
295         this.datacenterId = datacenterId;
296     }
297     @Column(name = "CONVERGE_ID", nullable =true, precision = 18, scale = 0)
298     public Long getConvergeId() {
299         return convergeId;
300     }
301 
302     public void setConvergeId(Long convergeId) {
303         this.convergeId = convergeId;
304     }
305 
306     @Column(name="ROUTERS_ID", length=36)
307     public String getRoutersId() {
308         return routersId;
309     }
310 
311     public void setRoutersId(String routersId) {
312         this.routersId = routersId;
313     }
314 
315     @Transient
316     public Integer getUseIpNum() {
317         return useIpNum;
318     }
319 
320     public void setUseIpNum(Integer useIpNum) {
321         this.useIpNum = useIpNum;
322     }
323 
324     @Transient
325     public Integer getUnUseIpNum() {
326         return unUseIpNum;
327     }
328 
329     public void setUnUseIpNum(Integer unUseIpNum) {
330         this.unUseIpNum = unUseIpNum;
331     }
332 
333     @Transient
334     public Long getResPoolId() {
335         return resPoolId;
336     }
337 
338     public void setResPoolId(Long resPoolId) {
339         this.resPoolId = resPoolId;
340     }
341 
342     @Transient
343     public String getResPoolType() {
344         return resPoolType;
345     }
346 
347     public void setResPoolType(String resPoolType) {
348         this.resPoolType = resPoolType;
349     }
350 
351     @Transient
352     public String getNetArea() {
353         return netArea;
354     }
355 
356     public void setNetArea(String netArea) {
357         this.netArea = netArea;
358     }
359     
360     
361 }

  1 package com.ccb.cloud.nw.ip.data.entity;
  2 
  3 import java.io.Serializable;
  4 import javax.persistence.*;
  5 import java.util.Date;
  6 
  7 
  8 /**
  9  * The persistent class for the RM_CDP_IP_ADDRESS database table.
 10  * 
 11  */
 12 @Entity
 13 @Table(name="RM_CDP_IP_ADDRESS")
 14 public class RmCdpIpAddressPo implements Serializable {
 15     private static final long serialVersionUID = 1L;
 16 
 17     @Id
 18     private String ip;
 19 
 20     @Column(name="ALLOCED_STATUS_CODE")
 21     private String allocedStatusCode;
 22 
 23     @Temporal(TemporalType.DATE)
 24     @Column(name="ALLOCED_TIME")
 25     private Date allocedTime;
 26 
 27     @Column(name="APP_DU_ID")
 28     private Long appDuId;
 29 
 30     @Column(name="CCLASS_ID")
 31     private Long cclassId;
 32 
 33     @Column(name="DEVICE_ID")
 34     private Long deviceId;
 35 
 36     @Column(name="IP_TYPE_ID")
 37     private String ipTypeId;
 38 
 39     private String remark;
 40 
 41     @Column(name="RES_CDP_ID")
 42     private Long resCdpId;
 43 
 44     @Column(name="RES_CLUSTER_ID")
 45     private Long resClusterId;
 46 
 47     @Column(name="RES_POOL_ID")
 48     private Long resPoolId;
 49 
 50     private Integer seq;
 51 
 52     public RmCdpIpAddressPo() {
 53     }
 54 
 55     public String getIp() {
 56         return ip;
 57     }
 58 
 59     public void setIp(String ip) {
 60         this.ip = ip;
 61     }
 62 
 63     public String getAllocedStatusCode() {
 64         return allocedStatusCode;
 65     }
 66 
 67     public void setAllocedStatusCode(String allocedStatusCode) {
 68         this.allocedStatusCode = allocedStatusCode;
 69     }
 70 
 71     public Date getAllocedTime() {
 72         return allocedTime;
 73     }
 74 
 75     public void setAllocedTime(Date allocedTime) {
 76         this.allocedTime = allocedTime;
 77     }
 78 
 79     public Long getAppDuId() {
 80         return appDuId;
 81     }
 82 
 83     public void setAppDuId(Long appDuId) {
 84         this.appDuId = appDuId;
 85     }
 86 
 87     public Long getCclassId() {
 88         return cclassId;
 89     }
 90 
 91     public void setCclassId(Long cclassId) {
 92         this.cclassId = cclassId;
 93     }
 94 
 95     public Long getDeviceId() {
 96         return deviceId;
 97     }
 98 
 99     public void setDeviceId(Long deviceId) {
100         this.deviceId = deviceId;
101     }
102 
103     public String getIpTypeId() {
104         return ipTypeId;
105     }
106 
107     public void setIpTypeId(String ipTypeId) {
108         this.ipTypeId = ipTypeId;
109     }
110 
111     public String getRemark() {
112         return remark;
113     }
114 
115     public void setRemark(String remark) {
116         this.remark = remark;
117     }
118 
119     public Long getResCdpId() {
120         return resCdpId;
121     }
122 
123     public void setResCdpId(Long resCdpId) {
124         this.resCdpId = resCdpId;
125     }
126 
127     public Long getResClusterId() {
128         return resClusterId;
129     }
130 
131     public void setResClusterId(Long resClusterId) {
132         this.resClusterId = resClusterId;
133     }
134 
135     public Long getResPoolId() {
136         return resPoolId;
137     }
138 
139     public void setResPoolId(Long resPoolId) {
140         this.resPoolId = resPoolId;
141     }
142 
143     public Integer getSeq() {
144         return seq;
145     }
146 
147     public void setSeq(Integer seq) {
148         this.seq = seq;
149     }
150 
151     public RmCdpIpAddressPo(String ip, String allocedStatusCode, Date allocedTime, Long appDuId, Long cclassId,
152             Long deviceId, String ipTypeId, String remark, Long resCdpId, Long resClusterId, Long resPoolId,
153             Integer seq) {
154         super();
155         this.ip = ip;
156         this.allocedStatusCode = allocedStatusCode;
157         this.allocedTime = allocedTime;
158         this.appDuId = appDuId;
159         this.cclassId = cclassId;
160         this.deviceId = deviceId;
161         this.ipTypeId = ipTypeId;
162         this.remark = remark;
163         this.resCdpId = resCdpId;
164         this.resClusterId = resClusterId;
165         this.resPoolId = resPoolId;
166         this.seq = seq;
167     }
168 
169     
170 
171 }

1 //获取名称对应的子网信息
2     @Query(value="select * from rm_cdp_ip_address a where a.ip = :ip",nativeQuery=true)
3     RmCdpIpAddressPo getIpInfoByIp(@Param("ip") String ip);
4 
5 //获取名称对应的子网信息
6     @Query(value="SELECT t.cclass_id,t.bclass_id,t.cclass_type_code,t.secure_area_code,t.secure_tier_code,t.cclass_name,t.subnetmask,t.gateway,t.vlan_id," + 
7             "t.ip_start,t.ip_end,t.aclass_ip,t.bclass_ip,t.cclass_ip,t.ip_total_cnt,t.ip_avail_cnt,t.is_active,t.datacenter_id,t.module_id,t.vcenter_id,t.converge_id,t.template_id,t.routers_id"
8             + " FROM rm_nw_cclass t WHERE t.cclass_name=:cclassName ",nativeQuery=true)
9     List<NwCclassPo> getBclassByName(@Param("cclassName") String cclassName);

  1 package com.ccb.cloud.nw.ip.service;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.SQLException;
  6 import java.util.ArrayList;
  7 import java.util.Date;
  8 import java.util.List;
  9 import java.util.Map;
 10 
 11 import javax.sql.DataSource;
 12 
 13 import org.slf4j.Logger;
 14 import org.slf4j.LoggerFactory;
 15 import org.springframework.beans.factory.annotation.Autowired;
 16 import org.springframework.jdbc.core.BatchPreparedStatementSetter;
 17 import org.springframework.jdbc.core.JdbcTemplate;
 18 import org.springframework.jdbc.datasource.DataSourceUtils;
 19 import org.springframework.stereotype.Service;
 20 import org.springframework.transaction.annotation.Transactional;
 21 import org.springframework.transaction.support.TransactionSynchronizationManager;
 22 import org.springframework.web.client.RestTemplate;
 23 
 24 import com.ccb.cloud.common.data.DBSeqUtils;
 25 import com.ccb.cloud.common.exception.BizException;
 26 import com.ccb.cloud.common.service.BaseService;
 27 import com.ccb.cloud.common.util.ExDateUtils;
 28 import com.ccb.cloud.nw.ip.constants.IpFwConstants;
 29 import com.ccb.cloud.nw.ip.data.dao.RmCdpIpAddressDAO;
 30 import com.ccb.cloud.nw.ip.data.dao.RmNwCclassDAO;
 31 import com.ccb.cloud.nw.ip.data.entity.NwCclassPo;
 32 import com.ccb.cloud.nw.ip.data.entity.RmCdpIpAddressPo;
 33 import com.ccb.cloud.nw.ip.data.entity.RmNwCclassFreelistPo;
 34 import com.ccb.cloud.nw.ip.data.entity.RmNwSubnetmaskExtPo;
 35 /**
 36  * @author liuqiang.zh
 37  *@version 
 38  */
 39 @Service
 40 @Transactional(readOnly = true)
 41 public class Temp extends BaseService<NwCclassPo> {
 42     private final Logger logger = LoggerFactory.getLogger(this.getClass());
 43     
 44     @Autowired
 45     JdbcTemplate jdbcTemplate;
 46 
 47     @Autowired
 48     RestTemplate restTemplate;
 49     
 50     @Autowired
 51     private RmNwCclassDAO rmNwCclassDAO;
 52     
 53     @Autowired
 54     private RmCdpIpAddressDAO rmCdpIpAddressDAO;
 55 
 56     /**
 57      * 在/24子网池下批量新增按子网掩码和个数分配的子网表
 58      * 在子网表记录创建成功后批量新增ip地址表记录
 59      * @author liuqiang.zh 
 60      * @param paramBody
 61      */
 62     public String batchSaveSubCclassAndCclassField(Map<String, Object> paramBody) throws Exception{
 63         String result = "";
 64         long startTime = System.currentTimeMillis();
 65         //调用通用方法获取C段列表,遍历
 66         String cclassNameTemp = (String)paramBody.get("className");
 67         String[] cclassNameTempList = cclassNameTemp.split("/");
 68         String cclassName = cclassNameTempList[0];
 69         //子网池掩码
 70         String poolSubnetmaskStr = cclassNameTempList[1];
 71         Integer poolSubnetmask = 0;
 72         if(!"".equals(poolSubnetmaskStr)) {
 73             poolSubnetmask = Integer.parseInt(poolSubnetmaskStr);
 74         }
 75         
 76         if(poolSubnetmask < IpFwConstants.BMAXIPUNIT) {
 77             //调用B段启用方法
 78             return batchSaveSubCclassByBName(paramBody);
 79         }
 80         if("".equals(cclassName)) {
 81             throw new BizException("001", "未获取到传入的C段Ip");
 82         }
 83         String[] CIpNumberList = cclassName.split("\\.");
 84         Integer aclassIp = Integer.parseInt(CIpNumberList[0]);
 85         Integer bclassIp = Integer.parseInt(CIpNumberList[1]);        
 86         Integer cclassIp = Integer.parseInt(CIpNumberList[2]);
 87         //获取子网掩码格式，判断？是否小于/24子网(24-32)
 88         Integer subNetMaskUnit  = 0;
 89         Object subnetmaskTempStr = paramBody.get("subnetmask");
 90         if(subnetmaskTempStr !=null && !"".equals(subnetmaskTempStr)) {
 91             subNetMaskUnit = Integer.parseInt(subnetmaskTempStr.toString());
 92         }
 93         if(subNetMaskUnit < IpFwConstants.BMAXIPUNIT || subNetMaskUnit >= IpFwConstants.MAXIPUNIT) {
 94             throw new BizException("001", "输入的子网掩码超出范围");
 95         }
 96         //获取子网个数，判断？当前C段剩余的按子网格式分配的子网个数是否大于选择的子网个数
 97         Integer subNumber  = 0;
 98         Object subNumberTempStr = paramBody.get("subNumber");
 99         if(subNumberTempStr !=null && !"".equals(subNumberTempStr)) {
100             subNumber = Integer.parseInt(subNumberTempStr.toString());
101         }
102         
103         //获取最小单元的位数
104         int subUnit = IpFwConstants.MAXIPUNIT - subNetMaskUnit;
105         //最小单元存放的IP个数
106         int ipUnit = (int)Math.pow(IpFwConstants.BASICUNIT, subUnit);
107         //默认值
108         Long bclassId = 0L;
109         List<NwCclassPo> rmNwCclassList = new ArrayList<>();
110         //批量新增IP表记录
111         List<RmCdpIpAddressPo> ipsList =new ArrayList<>();
112         //若为0，重新开始生成子网，若不为0，取最后一条记录的网关，并将此IP +1作为起始的地址
113         Integer startIp = 0;
114         RmCdpIpAddressPo rmCdpIpAddressPoTemp = null;
115         for(int i=0;i < IpFwConstants.MAXIPNUMBER/ipUnit;i++) {
116             //当前的C段数字
117             int curCIp = i*ipUnit+1;
118             String subCclassName = aclassIp+"."+bclassIp+"."+cclassIp+"."+curCIp;
119             //此处去IP表中查找当前掩码格式下第一个IP，是否存在？若不存在，则取当前起始IP
120             rmCdpIpAddressPoTemp = rmCdpIpAddressDAO.getIpInfoByIp(subCclassName);
121             
122             if(rmCdpIpAddressPoTemp == null) {
123                 startIp = i*ipUnit;
124                 break;
125             }
126             if(i == IpFwConstants.MAXIPNUMBER/ipUnit-1) {
127                 startIp = IpFwConstants.MAXIPNUMBER;
128             }
129         }
130         
131         //获取子网个数，判断？当前C段剩余的按子网格式分配的子网个数是否大于选择的子网个数
132         int availNum = (int)(IpFwConstants.MAXIPNUMBER - startIp)/ipUnit;
133         
134         Date date = ExDateUtils.getCurrentDateTime();
135         //判断已经生成的子网个数
136         int count = 0;
137         RmCdpIpAddressPo rmCdpIpAddressPoTest = null;
138         if(subNumber <= availNum) {
139             for(int i=0;i < availNum;i++) {
140                 //首先需要判断IP是否超出256的最大范围
141                 if((startIp+(i+1)*ipUnit) > IpFwConstants.MAXIPNUMBER) {
142                     throw new BizException("001", "待启用的子网个数不满足选择需求");
143                 }
144                 if(subNumber == count) {
145                     break;
146                 }
147                 //此处重新去IP表中查找第一个IP是否存在
148                 String testCclassName = aclassIp+"."+bclassIp+"."+cclassIp+"."+(startIp+i*ipUnit+1);
149                 rmCdpIpAddressPoTest = rmCdpIpAddressDAO.getIpInfoByIp(testCclassName);
150                 if(rmCdpIpAddressPoTest == null) {
151                     NwCclassPo cclassPo = new NwCclassPo();
152                     Long cclassId = DBSeqUtils.getSeq("IOMP_SEQ");
153                     String subCclassName = aclassIp+"."+bclassIp+"."+cclassIp+"."+(startIp+i*ipUnit);
154                     String gateway = aclassIp+"."+bclassIp+"."+cclassIp+"."+(startIp+(i+1)*ipUnit-IpFwConstants.BASICUNIT);
155                     Integer ipStart = startIp+i*ipUnit + 1;
156                     Integer ipEnd = startIp+(i+1)*ipUnit - IpFwConstants.BASICUNIT;
157                     Integer ipTotalCnt = ipUnit;
158                     Integer ipAvailCnt = ipUnit -IpFwConstants.BASICUNIT;
159                     cclassPo.setCclassId(cclassId);
160                     cclassPo.setBclassId(bclassId);
161                     cclassPo.setCclassName(subCclassName);
162                     cclassPo.setSubnetmask(subNetMaskUnit.toString());
163                     cclassPo.setGateway(gateway);
164                     cclassPo.setIpStart(ipStart);
165                     cclassPo.setIpEnd(ipEnd);
166                     cclassPo.setAclassIp(aclassIp);
167                     cclassPo.setBclassIp(bclassIp);
168                     cclassPo.setCclassIp(cclassIp);
169                     cclassPo.setIpTotalCnt(ipTotalCnt);
170                     cclassPo.setIpAvailCnt(ipAvailCnt);
171                 
172                     rmNwCclassList.add(cclassPo);
173                     
174                     //生成对应的IP表数据
175                     String[] topCclassNameList = subCclassName.split("\\.");
176                     Integer aclassIpAddr = Integer.parseInt(topCclassNameList[0]);
177                     Integer bclassIpAddr = Integer.parseInt(topCclassNameList[1]);        
178                     Integer cclassIpAddr = Integer.parseInt(topCclassNameList[2]);
179                     Integer dclassIpAddr = Integer.parseInt(topCclassNameList[3]);
180                     for(int j=0;j < ipUnit-IpFwConstants.BASICUNIT;j++) {
181                         RmCdpIpAddressPo ipAddressPo = new RmCdpIpAddressPo();
182                         String curIpAddress = aclassIpAddr+"."+bclassIpAddr+"."+cclassIpAddr+"."+(dclassIpAddr+j+1) ;
183                         ipAddressPo.setIp(curIpAddress);
184                         ipAddressPo.setCclassId(cclassId);
185                         ipAddressPo.setSeq(dclassIpAddr+j+1);
186                         //待定字段值ALLOCED_STATUS_CODE
187                         ipAddressPo.setAllocedStatusCode(IpFwConstants.NOTALLOCATE);
188                         ipAddressPo.setAllocedTime(date);
189                         ipsList.add(ipAddressPo);
190                     }
191                     count++;
192                 }
193             }
194         }else {
195             throw new BizException("001", "没有足够的子网可供分配");
196         }
197         
198         Connection connection = null;    
199         DataSource dataSource = null;
200         try {
201             TransactionSynchronizationManager.clearSynchronization();
202             if (!TransactionSynchronizationManager.isSynchronizationActive()) {
203                 TransactionSynchronizationManager.initSynchronization();
204             }
205             dataSource = jdbcTemplate.getDataSource();
206             connection = DataSourceUtils.getConnection(dataSource);
207             connection.setAutoCommit(false);
208             if(rmNwCclassList.size() > 0) {
209                 saveNwCclassPoList(rmNwCclassList);
210             }
211             if(ipsList.size() > 0) {
212                 saveIpAddressList(ipsList);
213                 result += "OK";
214                 connection.commit();
215             }
216             long endTime = System.currentTimeMillis();
217             logger.info("***************启用C段子网耗时:{}",endTime - startTime);
218         } catch(Exception e) {
219             try {
220                 connection.rollback();
221             } catch (SQLException e1) {
222                 logger.error("启用子网报错：", e1);
223             }
224             logger.error("启用子网报错：", e);
225         } finally {
226             try {
227                 TransactionSynchronizationManager.clearSynchronization();
228             } catch (IllegalStateException e) {
229                 logger.error("启用子网报错：", e);
230                 connection.rollback();
231             }
232             TransactionSynchronizationManager.initSynchronization();
233            
234             try {
235                 connection.setAutoCommit(true);
236             } catch (SQLException e) {
237                 logger.error("启用子网报错：", e);
238                 connection.rollback();
239             }
240             
241             if (connection != null) {
242                 DataSourceUtils.releaseConnection(connection, dataSource);
243             }
244         }    
245         return result;
246     }
247     
248     /**
249      * 在/16子网池下批量新增按子网掩码和个数分配的子网表
250      * 在子网表记录创建成功后批量新增ip地址表记录
251      * @author liuqiang.zh 
252      * @param paramBody
253      */
254 //    @Transactional(readOnly = false)
255     public String batchSaveSubCclassByBName(Map<String, Object> paramBody) throws Exception{
256         String result = "";
257         long startTime = System.currentTimeMillis();
258         //调用通用方法获取C段列表,遍历
259         String cclassNameTemp = (String)paramBody.get("className");
260         String[] cclassNameTempList = cclassNameTemp.split("/");
261         String bclassName = cclassNameTempList[0];
262         //启用支持半个B段
263         Integer bclassType = 0;
264         String bclassTypeStr = cclassNameTempList[1];
265         if(bclassTypeStr != null && !"".equals(bclassTypeStr)) {
266             bclassType = Integer.parseInt(bclassTypeStr);
267         }
268         if("".equals(bclassName)) {
269             throw new BizException("001", "未获取到B段ip");
270         }
271         String[] CIpNumberList = bclassName.split("\\.");
272         Integer aclassIp = Integer.parseInt(CIpNumberList[0]);
273         Integer bclassIp = Integer.parseInt(CIpNumberList[1]);
274         //半个B段的起始Ip
275         Integer beginIp = Integer.parseInt(CIpNumberList[2]);
276         //获取子网掩码格式，判断？是否小于/16子网(16-24)
277         Integer subNetMaskUnit  = 0;
278         Object subnetmaskTempStr = paramBody.get("subnetmask");
279         if(subnetmaskTempStr !=null && !"".equals(subnetmaskTempStr)) {
280             subNetMaskUnit = Integer.parseInt(subnetmaskTempStr.toString());
281         }
282         if(subNetMaskUnit < IpFwConstants.BUNIT || subNetMaskUnit > IpFwConstants.BMAXIPUNIT) {
283             throw new BizException("001", "输入的子网掩码超出范围！");
284         }
285         Integer subNumber  = 0;
286         Object subNumberTempStr = paramBody.get("subNumber");
287         if(subNumberTempStr !=null && !"".equals(subNumberTempStr)) {
288             subNumber = Integer.parseInt(subNumberTempStr.toString());
289         }
290         //机房模块，安全区域，安全分层，网络汇聚,VC服务器，数据中心
291         String cclassTypeCode = String.valueOf(paramBody.get("cclassTypeCode"));
292         //获取最小单元的位数selfDefIp
293         int subUnit = IpFwConstants.BMAXIPUNIT - subNetMaskUnit;
294         //最小单元存放的IP个数
295         int ipUnit = (int)Math.pow(IpFwConstants.BASICUNIT, subUnit);
296         //此处先查询B段表信息，更新资源池ID
297         Long bclassId = 0L;
298         //获取起始位子ＩＰ
299         Integer startIp = 0;
300         //判断是否启用的是一个完整B段
301         if(bclassType > IpFwConstants.BUNIT) {
302             startIp = beginIp;
303         }
304         //子网池结束ip
305         int scope = (int)Math.pow(IpFwConstants.BASICUNIT,(IpFwConstants.BMAXIPUNIT-bclassType));
306         int endIp = beginIp+scope;
307         List<NwCclassPo> nwCclassPoTemp = null;
308         for(int i=0;i < scope/ipUnit;i++) {
309             //当前的C段数字
310             int curCIp = startIp+i*ipUnit;
311             String subCclassName = aclassIp+"."+bclassIp+"."+curCIp+".0";
312             //此处去子网表中查找当前子网格式下第一个IP，是否存在？若不存在，则取当前起始IP
313             nwCclassPoTemp = rmNwCclassDAO.getBclassByName(subCclassName);
314             if(nwCclassPoTemp.size() == 0 || nwCclassPoTemp.isEmpty()) {
315                 startIp += i*ipUnit;
316                 break;
317             }
318             if(i == IpFwConstants.MAXIPNUMBER/ipUnit-1) {
319                 startIp = endIp;
320             }
321         }
322         //批量子网
323         List<NwCclassPo> rmNwCclassList = new ArrayList<>();
324         List<RmNwCclassFreelistPo> RmNwCclassFreelist = new ArrayList<>();
325         List<RmNwSubnetmaskExtPo> rmNwSubnetmaskExtList = new ArrayList<>();
326         //批量新增IP表记录
327         List<RmCdpIpAddressPo> ipsList =new ArrayList<>();
328         //获取子网个数，判断，当前C段剩余的按子网格式分配的子网个数是否大于选择的子网个数
329         int availNum = (int)(endIp - startIp)/ipUnit;
330         Date date = ExDateUtils.getCurrentDateTime();
331         //统计可用个数
332         int count = 0;
333         if(subNumber <= availNum) {
334             for(int i=0;i<subNumber;i++) {
335                 //当前的C段数字，允许192.168.0.0/24子网
336                 int curCIp = 0;
337                 curCIp = startIp+i*ipUnit;
338                 if(curCIp > endIp-1) {
339                     throw new BizException("001", "可用的子网个数不足");
340                 }
341                 String subCclassName = aclassIp+"."+bclassIp+"."+curCIp+".0";
342                 //此处去子网表中查找当前子网格式下第一个IP，是否存在？若不存在，则按
343                 nwCclassPoTemp = rmNwCclassDAO.getBclassByName(subCclassName);
344                 if(count == subNumber) {
345                     break;
346                 }
347                 if(nwCclassPoTemp.size() == 0 || nwCclassPoTemp.isEmpty()) {
348                     //子网表记录，这里与C段表为一对一关系
349                     NwCclassPo cclassPo = new NwCclassPo();
350                     RmNwCclassFreelistPo cclassFreePo = new RmNwCclassFreelistPo();
351                     Long cclassId = DBSeqUtils.getSeq("IOMP_SEQ");
352                     String gateway = aclassIp+"."+bclassIp+"."+curCIp+".254";
353                     Integer ipStart = 1;
354                     Integer ipEnd = IpFwConstants.MAXIPNUMBER-IpFwConstants.BASICUNIT;
355                     Integer ipTotalCnt = IpFwConstants.MAXIPNUMBER;
356                     Integer ipAvailCnt = IpFwConstants.MAXIPNUMBER -IpFwConstants.BASICUNIT;
357                     cclassPo.setCclassId(cclassId);
358                     cclassPo.setBclassId(bclassId);
359                     //待定?
360                     cclassPo.setCclassTypeCode(cclassTypeCode);
361                     cclassPo.setCclassName(subCclassName);
362                     cclassPo.setSubnetmask(subNetMaskUnit.toString());
363                     cclassPo.setGateway(gateway);
364                     cclassPo.setIpStart(ipStart);
365                     cclassPo.setIpEnd(ipEnd);
366                     cclassPo.setAclassIp(aclassIp);
367                     cclassPo.setBclassIp(bclassIp);
368                     cclassPo.setCclassIp(curCIp);
369                     cclassPo.setIpTotalCnt(ipTotalCnt);
370                     cclassPo.setIpAvailCnt(ipAvailCnt);
371                     rmNwCclassList.add(cclassPo);
372                     //子网扩展表数据
373                     RmNwSubnetmaskExtPo rmNwSubnetmaskExtPo = new RmNwSubnetmaskExtPo();
374                     rmNwSubnetmaskExtPo.setSubnetmaskId(cclassId);
375                     //新增GatewayIp
376                     rmNwSubnetmaskExtPo.setGatewayIp(gateway);
377                     rmNwSubnetmaskExtPo.setIpVersion(4L);
378                     rmNwSubnetmaskExtList.add(rmNwSubnetmaskExtPo);
379                     //子网空闲表数据
380                     cclassFreePo.setCclassId(cclassId);
381                     cclassFreePo.setSeqStart(ipStart);
382                     cclassFreePo.setSeqEnd(ipEnd);
383                     cclassFreePo.setAvailCnt(ipAvailCnt);
384                     RmNwCclassFreelist.add(cclassFreePo);
385                     
386                     //生成对应的IP表数据
387                     for(int m=0;m < ipUnit;m++) {
388                         for(int j=1;j <IpFwConstants.MAXIPNUMBER-1;j++) {
389                             RmCdpIpAddressPo ipAddressPo = new RmCdpIpAddressPo();
390                             String curIpAddress = aclassIp+"."+bclassIp+"."+(curCIp+m)+"."+j ;
391                             ipAddressPo.setIp(curIpAddress);
392                             ipAddressPo.setCclassId(cclassId);
393                             ipAddressPo.setSeq(j);
394                             //待定字段值ALLOCED_STATUS_CODE
395                             ipAddressPo.setAllocedStatusCode(IpFwConstants.NOTALLOCATE);
396                             ipAddressPo.setAllocedTime(date);
397                             ipsList.add(ipAddressPo);
398                         }
399                     }
400                     count++;
401                 }
402             }
403         }else {
404             throw new BizException("001", "没有足够的子网可供分配");
405         }
406         
407         Connection connection = null;    
408         DataSource dataSource = null;
409         try {
410             TransactionSynchronizationManager.clearSynchronization();
411             if (!TransactionSynchronizationManager.isSynchronizationActive()) {
412                 TransactionSynchronizationManager.initSynchronization();
413             }
414             dataSource = jdbcTemplate.getDataSource();
415             connection = DataSourceUtils.getConnection(dataSource);
416             connection.setAutoCommit(false);
417             if(rmNwCclassList.size() > 0) {
418                 saveNwCclassPoList(rmNwCclassList);
419             }
420             if(ipsList.size() > 0) {
421                 saveIpAddressList(ipsList);
422                 result += "OK";
423                 connection.commit();
424             }
425             long endTime = System.currentTimeMillis();
426             logger.info("***************启用B段子网耗时:{}",endTime - startTime);
427         } catch(Exception e) {
428             try {
429                 connection.rollback();
430             } catch (SQLException e1) {
431                 logger.error("启用子网报错：", e1);
432             }
433             logger.error("启用子网报错：", e);
434         } finally {
435             try {
436                 TransactionSynchronizationManager.clearSynchronization();
437             } catch (IllegalStateException e) {
438                 logger.error("启用子网报错：", e);
439                 connection.rollback();
440             }
441             TransactionSynchronizationManager.initSynchronization();
442            
443             try {
444                 connection.setAutoCommit(true);
445             } catch (SQLException e) {
446                 logger.error("启用子网报错：", e);
447                 connection.rollback();
448             }
449             
450             if (connection != null) {
451                 DataSourceUtils.releaseConnection(connection, dataSource);
452             }
453         }    
454         return result;
455     }
456     /**
457      * jdbcTemplate批量新增NwCclassPo表
458      * @param list
459      * @author liuqiang
460      */
461     private void saveNwCclassPoList(final List<NwCclassPo> list) throws Exception{
462         String sql = "insert into RM_NW_CCLASS (CCLASS_ID,BCLASS_ID,CCLASS_TYPE_CODE,CCLASS_NAME,SUBNETMASK,\n" + 
463                 "GATEWAY,VLAN_ID,IP_START,IP_END,ACLASS_IP,BCLASS_IP,CCLASS_IP,IP_TOTAL_CNT,IP_AVAIL_CNT,IS_ACTIVE,DATACENTER_ID,SECURE_AREA_CODE,MODULE_ID)"
464                 + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
465         jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
466 
467             @Override
468             public int getBatchSize() {
469                 return list.size();
470             }
471 
472             @Override
473             public void setValues(PreparedStatement ps, int i) throws SQLException {
474                 ps.setLong(1, list.get(i).getCclassId());
475                 ps.setLong(2, list.get(i).getBclassId());
476                 ps.setString(3, list.get(i).getCclassTypeCode());
477                 ps.setString(4, list.get(i).getCclassName());
478                 ps.setString(5, list.get(i).getSubnetmask());
479                 ps.setString(6, list.get(i).getGateway());
480                 if (list.get(i).getVlanId() != null) {
481                     ps.setLong(7, list.get(i).getVlanId());
482                 } else {
483                     ps.setLong(7, 0);
484                 }
485                 ps.setInt(8, list.get(i).getIpStart());
486                 ps.setInt(9, list.get(i).getIpEnd());
487                 ps.setInt(10, list.get(i).getAclassIp());
488                 ps.setInt(11, list.get(i).getBclassIp());
489                 ps.setInt(12, list.get(i).getCclassIp());
490                 ps.setInt(13, list.get(i).getIpTotalCnt());
491                 ps.setInt(14, list.get(i).getIpAvailCnt());
492                 ps.setString(15, list.get(i).getIsActive());
493                 Long datacenterId = list.get(i).getDatacenterId();
494                 if(datacenterId != null) {
495                     ps.setLong(16, list.get(i).getDatacenterId());
496                 }else {
497                     ps.setLong(16, 0L);
498                 }
499                 ps.setString(17, list.get(i).getSecureAreaCode());
500                 Long moduleId = list.get(i).getModuleId();
501                 if(moduleId != null) {
502                     ps.setLong(18, list.get(i).getModuleId());
503                 }else {
504                     ps.setLong(18, 0L);
505                 }
506             }
507         });
508     }
509         
510     /**
511      * jdbcTemplate批量新增RmCdpIpAddressPo表
512      * @param list
513      * @author zhangchaoyang
514      */
515     private void saveIpAddressList(final List<RmCdpIpAddressPo> list) throws Exception{
516         String sql = "insert into RM_CDP_IP_ADDRESS (IP,CCLASS_ID,SEQ,IP_TYPE_ID,RES_POOL_ID,ALLOCED_STATUS_CODE,ALLOCED_TIME) "
517                 + "values(?,?,?,?,?,?,?)";
518         this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
519 
520             @Override
521             public int getBatchSize() {
522                 return list.size();
523             }
524 
525             @Override
526             public void setValues(PreparedStatement ps, int i) throws SQLException {
527                 ps.setString(1, list.get(i).getIp());
528                 ps.setLong(2, list.get(i).getCclassId());
529                 ps.setInt(3, list.get(i).getSeq());
530                 ps.setString(4, list.get(i).getIpTypeId());
531                 if (list.get(i).getResPoolId() != null) {
532                     ps.setLong(5, list.get(i).getResPoolId());
533                 } else {
534                     ps.setLong(5, 0);
535                 }
536                 ps.setString(6, list.get(i).getAllocedStatusCode());
537                 ps.setDate(7, new java.sql.Date(list.get(i).getAllocedTime().getTime()));
538             }
539         });
540     }
541 }

 1 package com.ccb.cloud.nw.ip.constants;
 2 
 3 public class IpFwConstants {
 4     //最大可用地址个数
 5     public static final int MAXIPNUMBER = 256;
 6     
 7     //Cip地址总位数
 8     public static final int MAXIPUNIT = 32;
 9     
10     //Bip地址总位数
11     public static final int BMAXIPUNIT = 24;
12     
13     //完整B段位数
14     public static final int BUNIT = 16;
15     
16     //首位地址个数
17     public static final int BASICUNIT = 2;
18     
19     //防火墙网络地址类型
20     public static final String FWCCLASSTYPECODE = "FWINT";
21         
22     //没有激活
23     public static final String NOTAVAILABLE = "N";
24     
25     //激活
26     public static final String AVAILABLE = "Y";
27     
28     //激活
29     public static final String HOLDPOSITION = "H";
30     
31     //激活
32     public static final String NOTALLOCATE = "NA";
33         
34     //最大可用地址个数
35     public static final String BCLASSSUBNETMASK = "255.255.0.0";
36 }

  1 /**
  2  * Copyright (c) 2017, China Construction Bank Co., Ltd. All rights reserved.
  3  * 中国建设银行版权所有.
  4  *
  5  * 审核人：
  6  */
  7 package com.ccb.cloud.common.data;
  8 
  9 import java.sql.Connection;
 10 import java.sql.SQLException;
 11 
 12 import javax.sql.DataSource;
 13 
 14 import org.apache.commons.lang3.StringUtils;
 15 import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 16 import org.springframework.transaction.PlatformTransactionManager;
 17 import org.springframework.transaction.TransactionDefinition;
 18 import org.springframework.transaction.TransactionStatus;
 19 import org.springframework.transaction.support.DefaultTransactionDefinition;
 20 
 21 import com.ccb.cloud.common.spring.SpringContextHolder;
 22 
 23 /**
 24  * 主键序列号生成器，统一数据库主键生成方式 每次获取一定长度的序列号缓存到内存中，提高应用程序获取序列号的效率
 25  * 
 26  * Oracle数据库采用序列生成序列号，MySQL数据库使用数据库表生成序列号
 27  * <p>
 28  * 
 29  * @author 
 30  * @version 1.0 2017年8月31日
 31  * @see
 32  */
 33 public class DBSeqUtils {
 34 
 35     // 默认序列名称
 36     private static final String DEFAULT_SEQ_KEY = "MAIN_SEQ";
 37 
 38     // 内存中序列长度（在Oracle数据中建立序列是，需要将序列的步长设置为500）
 39     private static final long SEQ_LEN = 100;
 40 
 41     // 序列获取控制参数
 42     private static long BEGIN_SEQ = 0 ;// 开始的序列号
 43     private static long CURRENT_SEQ = 0;// 当前序列号
 44 
 45     /**
 46      * 获取默认的最新序列值
 47      * @throws InterruptedException 
 48      * 
 49      */
 50     public static synchronized long getDefaultSeq(){
 51 
 52         long seq;
 53 
 54         if (BEGIN_SEQ == 0 || CURRENT_SEQ == SEQ_LEN) {
 55             // 重新获取序列
 56             BEGIN_SEQ = getCurrentSeq();
 57             CURRENT_SEQ = 0;
 58         }
 59 
 60         seq = BEGIN_SEQ + CURRENT_SEQ;
 61         CURRENT_SEQ++;
 62 
 63         return seq;
 64 
 65     }
 66     
 67     
 68     /**
 69      * 获取序列的最新序列值
 70      * 
 71      * @return
 72      */
 73     public static long getSeq(String seqKey) {
 74 
 75         DataSource dataSource = SpringContextHolder.getBean("dataSource");
 76 
 77         NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(
 78                 dataSource);
 79 
 80         return jdbcTemplate.getJdbcOperations()
 81                 .queryForObject(
 82                         "SELECT " + seqKey + ".nextval FROM dual",
 83                         Long.class);
 84 
 85     }        
 86 
 87     /**
 88      * 获取数据库中最新的序列值
 89      * 
 90      * @return
 91      */
 92     private static long getCurrentSeq() {
 93 
 94         DataSource dataSource = SpringContextHolder.getBean("dataSource");
 95 
 96         String jdbcUrl = getJdbcUrlFromDataSource(dataSource);
 97 
 98         NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(
 99                 dataSource);
100 
101         // 根据jdbc url判断数据库类型
102         if (StringUtils.contains(jdbcUrl, ":mysql:")) {
103             return getMySqlCurrentSeqVal(jdbcTemplate);
104         } else if (StringUtils.contains(jdbcUrl, ":oracle:")) {
105             return jdbcTemplate.getJdbcOperations().queryForObject(
106                     "SELECT " + DEFAULT_SEQ_KEY + ".nextval FROM dual",
107                     Long.class);
108         } else {
109             throw new IllegalArgumentException("数据库驱动配置错误，不支持的类型:" + jdbcUrl);
110         }
111     }
112 
113     /**
114      * 获取mySql的当前序列值
115      * @param jdbcTemplate
116      */
117     private static long getMySqlCurrentSeqVal(
118             NamedParameterJdbcTemplate jdbcTemplate) {
119         
120         long currentSeq = 0;
121 
122         // 更新序列值的大小
123         DefaultTransactionDefinition def = new DefaultTransactionDefinition();
124         def.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
125         def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
126         PlatformTransactionManager txManager = SpringContextHolder
127                 .getBean("transactionManager");
128         TransactionStatus status = txManager.getTransaction(def);
129 
130         try {
131             
132             // 获取当前序列值
133             currentSeq = jdbcTemplate.getJdbcOperations().queryForObject(
134                     "SELECT cur_seq_val FROM admin_seq WHERE seq_name='"
135                             + DEFAULT_SEQ_KEY + "' FOR UPDATE", Long.class);
136             
137             jdbcTemplate.getJdbcOperations().execute(
138                     "UPDATE admin_seq set cur_seq_val="
139                             + (currentSeq + SEQ_LEN) + " WHERE seq_name='"
140                             + DEFAULT_SEQ_KEY + "'");
141             txManager.commit(status);
142             
143         } catch (RuntimeException e) {
144             txManager.rollback(status);
145             throw new RuntimeException("无法生成序列号",e); 
146         }
147         
148         return currentSeq;
149     }
150 
151     /**
152      * 通过数据源获取数据库连接URL
153      * 
154      * @param dataSource
155      * @return
156      */
157     private static String getJdbcUrlFromDataSource(DataSource dataSource) {
158         Connection connection = null;
159         try {
160             connection = dataSource.getConnection();
161             if (connection == null) {
162                 throw new IllegalStateException("数据源无法返回的connection");
163             }
164             return connection.getMetaData().getURL();
165         } catch (SQLException e) {
166             throw new RuntimeException("无法获取数据库的URL", e);
167         } finally {
168             if (connection != null) {
169                 try {
170                     connection.close();
171                 } catch (SQLException e) {
172                 }
173             }
174         }
175     }
176 
177 }