cloudstack-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d...@apache.org
Subject [1/5] git commit: updated refs/heads/master to 118e954
Date Mon, 03 Aug 2015 20:02:18 GMT
Repository: cloudstack
Updated Branches:
  refs/heads/master 8151f7f2e -> 118e954d0


http://git-wip-us.apache.org/repos/asf/cloudstack/blob/119f6b0b/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22.java
index 3f19c54..28cf622 100644
--- a/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22.java
+++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22.java
@@ -61,11 +61,8 @@ public class Upgrade218to22 implements DbUpgrade {
     }
 
     protected void upgradeStoragePools(Connection conn) {
-        PreparedStatement pstmt;
-        try {
-            pstmt = conn.prepareStatement("UPDATE storage_pool SET status='Up'");
+        try (PreparedStatement pstmt = conn.prepareStatement("UPDATE storage_pool SET status='Up'");) {
             pstmt.executeUpdate();
-            pstmt.close();
         } catch (SQLException e) {
             throw new CloudRuntimeException("Can't upgrade storage pool ", e);
         }
@@ -77,8 +74,7 @@ public class Upgrade218to22 implements DbUpgrade {
         String insertSql =
             "INSERT INTO network_offerings (name, display_text, nw_rate, mc_rate, concurrent_connections, traffic_type, tags, system_only, specify_vlan, service_offering_id, created, removed, `default`, availability, dnsService, gatewayService, firewallService, lbService, userdataService, vpnService, dhcpService) "
                 + "VALUES (?,    ?,            NULL,    NULL,    NULL,                   ?,            NULL, ?,           0,            NULL,                now(),   NULL,    ?,       ?,            ?,           ?,               ?,                ?,          ?,                ?,           ?)";
-        try {
-            PreparedStatement pstmt = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
+        try (PreparedStatement pstmt = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);) {
             int i = 1;
             pstmt.setString(i++, name);
             pstmt.setString(i++, displayText);
@@ -94,32 +90,30 @@ public class Upgrade218to22 implements DbUpgrade {
             pstmt.setBoolean(i++, vpnService);
             pstmt.setBoolean(i++, dhcpService);
             pstmt.executeUpdate();
-            ResultSet rs = pstmt.getGeneratedKeys();
-            rs.next();
-            long id = rs.getLong(1);
-            rs.close();
-            pstmt.close();
-            return id;
+            try (ResultSet rs = pstmt.getGeneratedKeys();) {
+                rs.next();
+                long id = rs.getLong(1);
+                return id;
+            }
         } catch (SQLException e) {
             throw new CloudRuntimeException("Unable to insert network offering ", e);
         }
     }
 
     protected void upgradeInstanceGroups(Connection conn) {
-        try {
+        try (
+                PreparedStatement globalSelect = conn.prepareStatement("SELECT DISTINCT v.group, v.account_id from vm_instance v where v.group is not null");
+                ResultSet globalResult = globalSelect.executeQuery();
+            ) {
 
             // Create instance groups - duplicated names are allowed across accounts
-            PreparedStatement pstmt = conn.prepareStatement("SELECT DISTINCT v.group, v.account_id from vm_instance v where v.group is not null");
-            ResultSet rs = pstmt.executeQuery();
             ArrayList<Object[]> groups = new ArrayList<Object[]>();
-            while (rs.next()) {
+            while (globalResult.next()) {
                 Object[] group = new Object[10];
-                group[0] = rs.getString(1); // group name
-                group[1] = rs.getLong(2); // accountId
+                group[0] = globalResult.getString(1); // group name
+                group[1] = globalResult.getLong(2); // accountId
                 groups.add(group);
             }
-            rs.close();
-            pstmt.close();
 
             for (Object[] group : groups) {
                 String groupName = (String)group[0];
@@ -128,24 +122,25 @@ public class Upgrade218to22 implements DbUpgrade {
             }
 
             // update instance_group_vm_map
-            pstmt =
+            try (
+                    PreparedStatement detailSelect =
                 conn.prepareStatement("SELECT g.id, v.id from vm_instance v, instance_group g where g.name=v.group and g.account_id=v.account_id and v.group is not null");
-            rs = pstmt.executeQuery();
-            ArrayList<Object[]> groupVmMaps = new ArrayList<Object[]>();
-            while (rs.next()) {
-                Object[] groupMaps = new Object[10];
-                groupMaps[0] = rs.getLong(1); // vmId
-                groupMaps[1] = rs.getLong(2); // groupId
-                groupVmMaps.add(groupMaps);
+                    ResultSet detailResult = detailSelect.executeQuery();
+                ) {
+                ArrayList<Object[]> groupVmMaps = new ArrayList<Object[]>();
+                while (detailResult.next()) {
+                    Object[] groupMaps = new Object[10];
+                    groupMaps[0] = detailResult.getLong(1); // vmId
+                    groupMaps[1] = detailResult.getLong(2); // groupId
+                    groupVmMaps.add(groupMaps);
+                }
+                for (Object[] groupMap : groupVmMaps) {
+                    Long groupId = (Long)groupMap[0];
+                    Long instanceId = (Long)groupMap[1];
+                    createInstanceGroupVmMaps(conn, groupId, instanceId);
+                }
             }
-            rs.close();
-            pstmt.close();
 
-            for (Object[] groupMap : groupVmMaps) {
-                Long groupId = (Long)groupMap[0];
-                Long instanceId = (Long)groupMap[1];
-                createInstanceGroupVmMaps(conn, groupId, instanceId);
-            }
         } catch (SQLException e) {
             throw new CloudRuntimeException("Can't update instance groups ", e);
         }
@@ -153,375 +148,371 @@ public class Upgrade218to22 implements DbUpgrade {
     }
 
     protected void createInstanceGroups(Connection conn, String groupName, long accountId) throws SQLException {
-        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO instance_group (account_id, name, created) values (?, ?, now()) ");
-        pstmt.setLong(1, accountId);
-        pstmt.setString(2, groupName);
-        pstmt.executeUpdate();
-        pstmt.close();
+        try (PreparedStatement pstmt = conn.prepareStatement("INSERT INTO instance_group (account_id, name, created) values (?, ?, now()) ");) {
+            pstmt.setLong(1, accountId);
+            pstmt.setString(2, groupName);
+            pstmt.executeUpdate();
+        }
     }
 
     protected void createInstanceGroupVmMaps(Connection conn, long groupId, long instanceId) throws SQLException {
-        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO instance_group_vm_map (group_id, instance_id) values (?, ?) ");
-        pstmt.setLong(1, groupId);
-        pstmt.setLong(2, instanceId);
-        pstmt.executeUpdate();
-        pstmt.close();
+        try (PreparedStatement pstmt = conn.prepareStatement("INSERT INTO instance_group_vm_map (group_id, instance_id) values (?, ?) ");) {
+            pstmt.setLong(1, groupId);
+            pstmt.setLong(2, instanceId);
+            pstmt.executeUpdate();
+        }
     }
 
     protected long insertNic(Connection conn, long networkId, long instanceId, boolean running, String macAddress, String ipAddress, String netmask, String strategy,
         String gateway, String vnet, String guru, boolean defNic, int deviceId, String mode, String reservationId) throws SQLException {
-        PreparedStatement pstmt =
-            conn.prepareStatement(
+        try (
+                PreparedStatement pstmt = conn.prepareStatement(
                 "INSERT INTO nics (instance_id, network_id, mac_address, ip4_address, netmask, strategy, ip_type, broadcast_uri, mode, reserver_name, reservation_id, device_id, update_time, isolation_uri, ip6_address, default_nic, created, removed, state, gateway) "
                     + "VALUES (?,           ?,          ?,           ?,           ?,       ?,        'Ip4',   ?,             ?,    ?,             ?,              ?,         now(),       ?,          NULL,         ?,          now(),   NULL,    ?,     ?)",
                 Statement.RETURN_GENERATED_KEYS);
-        int i = 1;
-        String isolationUri = null;
-
-        String broadcast = null;
-        if (vnet != null) {
-            broadcast = "vlan://" + vnet;
-            if (vnet.equalsIgnoreCase("untagged")) {
-                isolationUri = "ec2://" + vnet;
-            } else {
-                isolationUri = broadcast;
+            ) {
+            int i = 1;
+            String isolationUri = null;
+
+            String broadcast = null;
+            if (vnet != null) {
+                broadcast = "vlan://" + vnet;
+                if (vnet.equalsIgnoreCase("untagged")) {
+                    isolationUri = "ec2://" + vnet;
+                } else {
+                    isolationUri = broadcast;
+                }
+            }
+            pstmt.setLong(i++, instanceId);
+            pstmt.setLong(i++, networkId);
+            pstmt.setString(i++, macAddress);
+            pstmt.setString(i++, ipAddress);
+            pstmt.setString(i++, netmask);
+            pstmt.setString(i++, strategy);
+            pstmt.setString(i++, broadcast);
+            pstmt.setString(i++, mode);
+            pstmt.setString(i++, guru);
+            pstmt.setString(i++, reservationId);
+            pstmt.setInt(i++, deviceId);
+            pstmt.setString(i++, isolationUri);
+            pstmt.setBoolean(i++, defNic);
+            pstmt.setString(i++, running ? "Reserved" : "Allocated");
+            pstmt.setString(i++, gateway);
+            pstmt.executeUpdate();
+            try (ResultSet rs = pstmt.getGeneratedKeys();) {
+                long nicId = 0;
+                if (!rs.next()) {
+                    throw new CloudRuntimeException("Unable to get id for nic");
+                }
+                nicId = rs.getLong(1);
+                return nicId;
             }
         }
-        pstmt.setLong(i++, instanceId);
-        pstmt.setLong(i++, networkId);
-        pstmt.setString(i++, macAddress);
-        pstmt.setString(i++, ipAddress);
-        pstmt.setString(i++, netmask);
-        pstmt.setString(i++, strategy);
-        pstmt.setString(i++, broadcast);
-        pstmt.setString(i++, mode);
-        pstmt.setString(i++, guru);
-        pstmt.setString(i++, reservationId);
-        pstmt.setInt(i++, deviceId);
-        pstmt.setString(i++, isolationUri);
-        pstmt.setBoolean(i++, defNic);
-        pstmt.setString(i++, running ? "Reserved" : "Allocated");
-        pstmt.setString(i++, gateway);
-        pstmt.executeUpdate();
-        ResultSet rs = pstmt.getGeneratedKeys();
-        long nicId = 0;
-        if (!rs.next()) {
-            throw new CloudRuntimeException("Unable to get id for nic");
-        }
-        nicId = rs.getLong(1);
-        rs.close();
-        pstmt.close();
-        return nicId;
     }
 
     protected void upgradeDomR(Connection conn, long dcId, long domrId, Long publicNetworkId, long guestNetworkId, long controlNetworkId, String zoneType, String vnet)
         throws SQLException {
         s_logger.debug("Upgrading domR" + domrId);
-        PreparedStatement pstmt =
+        try (
+                PreparedStatement pstmt =
             conn.prepareStatement("SELECT vm_instance.id, vm_instance.state, vm_instance.private_mac_address, vm_instance.private_ip_address, vm_instance.private_netmask, domain_router.public_mac_address, domain_router.public_ip_address, domain_router.public_netmask, domain_router.guest_mac_address, domain_router.guest_ip_address, domain_router.guest_netmask, domain_router.vnet, domain_router.gateway FROM vm_instance INNER JOIN domain_router ON vm_instance.id=domain_router.id WHERE vm_instance.removed is NULL AND vm_instance.id=?");
-        pstmt.setLong(1, domrId);
-        ResultSet rs = pstmt.executeQuery();
-
-        if (!rs.next()) {
-            throw new CloudRuntimeException("Unable to find router " + domrId);
-        }
-
-        // long id = rs.getLong(1);
-        String state = rs.getString(2);
-        boolean running = state.equals("Running") | state.equals("Starting") | state.equals("Stopping");
-        String privateMac = rs.getString(3);
-        String privateIp = rs.getString(4);
-        String privateNetmask = rs.getString(5);
-        String publicMac = rs.getString(6);
-        String publicIp = rs.getString(7);
-        String publicNetmask = rs.getString(8);
-        String guestMac = rs.getString(9);
-        String guestIp = rs.getString(10);
-        String guestNetmask = rs.getString(11);
-        String gateway = rs.getString(13);
-        rs.close();
-        pstmt.close();
-
-        pstmt = conn.prepareStatement("SELECT v.vlan_id from vlan v, user_ip_address u where v.id=u.vlan_db_id and u.public_ip_address=?");
-        pstmt.setString(1, publicIp);
-        rs = pstmt.executeQuery();
-
-        String publicVlan = null;
-        while (rs.next()) {
-            publicVlan = rs.getString(1);
-        }
-
-        // Control nic is the same for all types of networks
-        long controlNicId =
-            insertNic(conn, controlNetworkId, domrId, running, privateMac, privateIp, privateNetmask, "Start", "169.254.0.1", null, "ControlNetworkGuru", false, 1,
-                "Static", privateIp != null ? (domrId + privateIp) : null);
-        if (privateIp != null) {
-            pstmt = conn.prepareStatement("UPDATE op_dc_link_local_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");
-            pstmt.setLong(1, controlNicId);
-            pstmt.setString(2, privateIp);
-            pstmt.setLong(3, dcId);
-            pstmt.executeUpdate();
-            pstmt.close();
-        }
+            ) {
+            pstmt.setLong(1, domrId);
+            try (ResultSet rs = pstmt.executeQuery();) {
 
-        if (zoneType.equalsIgnoreCase("Basic")) {
-            insertNic(conn, guestNetworkId, domrId, running, guestMac, guestIp, guestNetmask, "Create", gateway, vnet, "DirectPodBasedNetworkGuru", true, 0, "Dhcp", null);
-        } else if (publicIp != null) {
-            // update virtual domR
-            insertNic(conn, publicNetworkId, domrId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "PublicNetworkGuru", true, 2, "Static",
-                null);
-            insertNic(conn, guestNetworkId, domrId, running, guestMac, guestIp, guestNetmask, "Start", null, vnet, "ExternalGuestNetworkGuru", false, 0, "Dhcp", null);
-        } else {
-            // update direct domR - dhcp case
-            insertNic(conn, guestNetworkId, domrId, running, guestMac, guestIp, guestNetmask, "Create", gateway, vnet, "DirectNetworkGuru", true, 0, "Dhcp", null);
+                if (!rs.next()) {
+                    throw new CloudRuntimeException("Unable to find router " + domrId);
+                }
+                // long id = rs.getLong(1);
+                String state = rs.getString(2);
+                boolean running = state.equals("Running") | state.equals("Starting") | state.equals("Stopping");
+                String privateMac = rs.getString(3);
+                String privateIp = rs.getString(4);
+                String privateNetmask = rs.getString(5);
+                String publicMac = rs.getString(6);
+                String publicIp = rs.getString(7);
+                String publicNetmask = rs.getString(8);
+                String guestMac = rs.getString(9);
+                String guestIp = rs.getString(10);
+                String guestNetmask = rs.getString(11);
+                String gateway = rs.getString(13);
+                try (PreparedStatement vlanStatement = conn.prepareStatement("SELECT v.vlan_id from vlan v, user_ip_address u where v.id=u.vlan_db_id and u.public_ip_address=?");) {
+                    vlanStatement.setString(1, publicIp);
+                    try (ResultSet vlanResult = vlanStatement.executeQuery();) {
+                        String publicVlan = null;
+                        while (vlanResult.next()) {
+                            publicVlan = vlanResult.getString(1);
+                        }
+                        // Control nic is the same for all types of networks
+                        long controlNicId =
+                            insertNic(conn, controlNetworkId, domrId, running, privateMac, privateIp, privateNetmask, "Start", "169.254.0.1", null, "ControlNetworkGuru", false, 1,
+                                "Static", privateIp != null ? (domrId + privateIp) : null);
+                        if (privateIp != null) {
+                            try (PreparedStatement updateStatement = conn.prepareStatement("UPDATE op_dc_link_local_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");) {
+                                updateStatement.setLong(1, controlNicId);
+                                updateStatement.setString(2, privateIp);
+                                updateStatement.setLong(3, dcId);
+                                updateStatement.executeUpdate();
+                            }
+                        }
+                        if (zoneType.equalsIgnoreCase("Basic")) {
+                            insertNic(conn, guestNetworkId, domrId, running, guestMac, guestIp, guestNetmask, "Create", gateway, vnet, "DirectPodBasedNetworkGuru", true, 0, "Dhcp", null);
+                        } else if (publicIp != null) {
+                            // update virtual domR
+                            insertNic(conn, publicNetworkId, domrId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "PublicNetworkGuru", true, 2, "Static",
+                                null);
+                            insertNic(conn, guestNetworkId, domrId, running, guestMac, guestIp, guestNetmask, "Start", null, vnet, "ExternalGuestNetworkGuru", false, 0, "Dhcp", null);
+                        } else {
+                            // update direct domR - dhcp case
+                            insertNic(conn, guestNetworkId, domrId, running, guestMac, guestIp, guestNetmask, "Create", gateway, vnet, "DirectNetworkGuru", true, 0, "Dhcp", null);
+                        }
+                    }
+                }
+            }
         }
-
     }
 
     protected void upgradeSsvm(Connection conn, long dataCenterId, long publicNetworkId, long managementNetworkId, long controlNetworkId, String zoneType)
         throws SQLException {
         s_logger.debug("Upgrading ssvm in " + dataCenterId);
-        PreparedStatement pstmt =
+        //select instance
+        try (
+                PreparedStatement selectInstance =
             conn.prepareStatement("SELECT vm_instance.id, vm_instance.state, vm_instance.private_mac_address, vm_instance.private_ip_address, vm_instance.private_netmask, secondary_storage_vm.public_mac_address, secondary_storage_vm.public_ip_address, secondary_storage_vm.public_netmask, secondary_storage_vm.guest_mac_address, secondary_storage_vm.guest_ip_address, secondary_storage_vm.guest_netmask, secondary_storage_vm.gateway, vm_instance.type FROM vm_instance INNER JOIN secondary_storage_vm ON vm_instance.id=secondary_storage_vm.id WHERE vm_instance.removed is NULL AND vm_instance.data_center_id=? AND vm_instance.type='SecondaryStorageVm'");
-        pstmt.setLong(1, dataCenterId);
-        ResultSet rs = pstmt.executeQuery();
-
-        if (!rs.next()) {
-            s_logger.debug("Unable to find ssvm in data center " + dataCenterId);
-            return;
-        }
-
-        long ssvmId = rs.getLong(1);
-        String state = rs.getString(2);
-        boolean running = state.equals("Running") | state.equals("Starting") | state.equals("Stopping");
-        String privateMac = rs.getString(3);
-        String privateIp = rs.getString(4);
-        String privateNetmask = rs.getString(5);
-        String publicMac = rs.getString(6);
-        String publicIp = rs.getString(7);
-        String publicNetmask = rs.getString(8);
-        String guestMac = rs.getString(9);
-        String guestIp = rs.getString(10);
-        String guestNetmask = rs.getString(11);
-        String gateway = rs.getString(12);
-        String type = rs.getString(13);
-        rs.close();
-        pstmt.close();
+            ) {
+            selectInstance.setLong(1, dataCenterId);
+            try (ResultSet instanceResult = selectInstance.executeQuery();) {
 
-        pstmt =
-            conn.prepareStatement("SELECT host_pod_ref.gateway from host_pod_ref INNER JOIN vm_instance ON vm_instance.pod_id=host_pod_ref.id WHERE vm_instance.removed is NULL AND vm_instance.data_center_id=? AND vm_instance.type='SecondaryStorageVm'");
-        pstmt.setLong(1, dataCenterId);
-        rs = pstmt.executeQuery();
-
-        if (!rs.next()) {
-            s_logger.debug("Unable to find ssvm in data center " + dataCenterId);
-            return;
-        }
-
-        String podGateway = rs.getString(1);
-        rs.close();
-        pstmt.close();
-
-        pstmt = conn.prepareStatement("SELECT v.vlan_id from vlan v, user_ip_address u where v.id=u.vlan_db_id and u.public_ip_address=?");
-        pstmt.setString(1, publicIp);
-        rs = pstmt.executeQuery();
-
-        String publicVlan = null;
-        while (rs.next()) {
-            publicVlan = rs.getString(1);
-        }
+                if (!instanceResult.next()) {
+                    s_logger.debug("Unable to find ssvm in data center " + dataCenterId);
+                    return;
+                }
 
-        rs.close();
-        pstmt.close();
+                long ssvmId = instanceResult.getLong(1);
+                String state = instanceResult.getString(2);
+                boolean running = state.equals("Running") | state.equals("Starting") | state.equals("Stopping");
+                String privateMac = instanceResult.getString(3);
+                String privateIp = instanceResult.getString(4);
+                String privateNetmask = instanceResult.getString(5);
+                String publicMac = instanceResult.getString(6);
+                String publicIp = instanceResult.getString(7);
+                String publicNetmask = instanceResult.getString(8);
+                String guestMac = instanceResult.getString(9);
+                String guestIp = instanceResult.getString(10);
+                String guestNetmask = instanceResult.getString(11);
+                String gateway = instanceResult.getString(12);
+//                String type = instanceResult.getString(13);
+                // select host
+                try (PreparedStatement selectHost =
+                    conn.prepareStatement("SELECT host_pod_ref.gateway from host_pod_ref INNER JOIN vm_instance ON vm_instance.pod_id=host_pod_ref.id WHERE vm_instance.removed is NULL AND vm_instance.data_center_id=? AND vm_instance.type='SecondaryStorageVm'");) {
+                    selectHost.setLong(1, dataCenterId);
+                    try (ResultSet hostResult = selectHost.executeQuery();) {
+
+                        if (!hostResult.next()) {
+                            s_logger.debug("Unable to find ssvm in data center " + dataCenterId);
+                            return;
+                        }
 
-        if (zoneType.equalsIgnoreCase("Basic")) {
-            insertNic(conn, publicNetworkId, ssvmId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "DirectPodBasedNetworkGuru", true, 2,
-                "Dhcp", null);
+                        String podGateway = hostResult.getString(1);
+                        // select vlan
+                        try (PreparedStatement selectVlan = conn.prepareStatement("SELECT v.vlan_id from vlan v, user_ip_address u where v.id=u.vlan_db_id and u.public_ip_address=?");) {
+                            selectVlan.setString(1, publicIp);
+                            try (ResultSet vlanResult = selectVlan.executeQuery();) {
+                                String publicVlan = null;
+                                while (vlanResult.next()) {
+                                    publicVlan = vlanResult.getString(1);
+                                }
+                                if (zoneType.equalsIgnoreCase("Basic")) {
+                                    insertNic(conn, publicNetworkId, ssvmId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "DirectPodBasedNetworkGuru", true, 2,
+                                        "Dhcp", null);
+
+                                } else {
+                                    insertNic(conn, publicNetworkId, ssvmId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "PublicNetworkGuru", true, 2, "Static",
+                                        null);
+                                }
+                            }
+                        }
 
-        } else {
-            insertNic(conn, publicNetworkId, ssvmId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "PublicNetworkGuru", true, 2, "Static",
-                null);
-        }
 
-        long controlNicId =
-            insertNic(conn, controlNetworkId, ssvmId, running, guestMac, guestIp, guestNetmask, "Start", "169.254.0.1", null, "ControlNetworkGuru", false, 0, "Static",
-                guestIp != null ? (ssvmId + guestIp) : null);
-        if (guestIp != null) {
-            pstmt = conn.prepareStatement("UPDATE op_dc_link_local_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");
-            pstmt.setLong(1, controlNicId);
-            pstmt.setString(2, guestIp);
-            pstmt.setLong(3, dataCenterId);
-            pstmt.executeUpdate();
-            pstmt.close();
-        }
+                        long controlNicId =
+                            insertNic(conn, controlNetworkId, ssvmId, running, guestMac, guestIp, guestNetmask, "Start", "169.254.0.1", null, "ControlNetworkGuru", false, 0, "Static",
+                                guestIp != null ? (ssvmId + guestIp) : null);
+                        if (guestIp != null) {
+                            try (PreparedStatement updateLinkLocal = conn.prepareStatement("UPDATE op_dc_link_local_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");) {
+                                updateLinkLocal.setLong(1, controlNicId);
+                                updateLinkLocal.setString(2, guestIp);
+                                updateLinkLocal.setLong(3, dataCenterId);
+                                updateLinkLocal.executeUpdate();
+                            }
+                        }
 
-        long mgmtNicId =
-            insertNic(conn, managementNetworkId, ssvmId, running, privateMac, privateIp, privateNetmask, "Start", podGateway, null, "PodBasedNetworkGuru", false, 1,
-                "Static", null);
-        if (privateIp != null) {
-            pstmt = conn.prepareStatement("UPDATE op_dc_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");
-            pstmt.setLong(1, mgmtNicId);
-            pstmt.setString(2, privateIp);
-            pstmt.setLong(3, dataCenterId);
-            pstmt.executeUpdate();
-            pstmt.close();
+                        long mgmtNicId =
+                            insertNic(conn, managementNetworkId, ssvmId, running, privateMac, privateIp, privateNetmask, "Start", podGateway, null, "PodBasedNetworkGuru", false, 1,
+                                "Static", null);
+                        if (privateIp != null) {
+                            try (PreparedStatement updateIp = conn.prepareStatement("UPDATE op_dc_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");) {
+                                updateIp.setLong(1, mgmtNicId);
+                                updateIp.setString(2, privateIp);
+                                updateIp.setLong(3, dataCenterId);
+                                updateIp.executeUpdate();
+                            }
+                        }
+                    }
+                }
+            }
         }
     }
 
     protected void upgradeConsoleProxy(Connection conn, long dcId, long cpId, long publicNetworkId, long managementNetworkId, long controlNetworkId, String zoneType)
         throws SQLException {
         s_logger.debug("Upgrading cp" + cpId);
-        PreparedStatement pstmt =
-            conn.prepareStatement("SELECT vm_instance.id, vm_instance.state, vm_instance.private_mac_address, vm_instance.private_ip_address, vm_instance.private_netmask, console_proxy.public_mac_address, console_proxy.public_ip_address, console_proxy.public_netmask, console_proxy.guest_mac_address, console_proxy.guest_ip_address, console_proxy.guest_netmask, console_proxy.gateway, vm_instance.type FROM vm_instance INNER JOIN console_proxy ON vm_instance.id=console_proxy.id WHERE vm_instance.removed is NULL AND vm_instance.id=?");
-        pstmt.setLong(1, cpId);
-        ResultSet rs = pstmt.executeQuery();
-
-        if (!rs.next()) {
-            throw new CloudRuntimeException("Unable to find cp " + cpId);
-        }
-
-        long id = rs.getLong(1);
-        String state = rs.getString(2);
-        boolean running = state.equals("Running") | state.equals("Starting") | state.equals("Stopping");
-        String privateMac = rs.getString(3);
-        String privateIp = rs.getString(4);
-        String privateNetmask = rs.getString(5);
-        String publicMac = rs.getString(6);
-        String publicIp = rs.getString(7);
-        String publicNetmask = rs.getString(8);
-        String guestMac = rs.getString(9);
-        String guestIp = rs.getString(10);
-        String guestNetmask = rs.getString(11);
-        String gateway = rs.getString(12);
-        String type = rs.getString(13);
-        rs.close();
-        pstmt.close();
-
-        pstmt =
-            conn.prepareStatement("SELECT host_pod_ref.gateway from host_pod_ref INNER JOIN vm_instance ON vm_instance.pod_id=host_pod_ref.id WHERE vm_instance.id=?");
-        pstmt.setLong(1, cpId);
-        rs = pstmt.executeQuery();
-
-        if (!rs.next()) {
-            throw new CloudRuntimeException("Unable to find cp " + cpId);
-        }
-
-        String podGateway = rs.getString(1);
-        rs.close();
-        pstmt.close();
-
-        pstmt = conn.prepareStatement("SELECT v.vlan_id from vlan v, user_ip_address u where v.id=u.vlan_db_id and u.public_ip_address=?");
-        pstmt.setString(1, publicIp);
-        rs = pstmt.executeQuery();
-
-        String publicVlan = null;
-        while (rs.next()) {
-            publicVlan = rs.getString(1);
-        }
+        try (PreparedStatement pstmt =
+            conn.prepareStatement("SELECT vm_instance.id, vm_instance.state, vm_instance.private_mac_address, vm_instance.private_ip_address, vm_instance.private_netmask, console_proxy.public_mac_address, console_proxy.public_ip_address, console_proxy.public_netmask, console_proxy.guest_mac_address, console_proxy.guest_ip_address, console_proxy.guest_netmask, console_proxy.gateway, vm_instance.type FROM vm_instance INNER JOIN console_proxy ON vm_instance.id=console_proxy.id WHERE vm_instance.removed is NULL AND vm_instance.id=?");) {
+            pstmt.setLong(1, cpId);
+            try (ResultSet rs = pstmt.executeQuery();) {
 
-        rs.close();
-        pstmt.close();
+                if (!rs.next()) {
+                    throw new CloudRuntimeException("Unable to find cp " + cpId);
+                }
 
-        if (zoneType.equalsIgnoreCase("Basic")) {
-            insertNic(conn, publicNetworkId, cpId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "DirectPodBasedNetworkGuru", true, 2,
-                "Dhcp", null);
-        } else {
-            insertNic(conn, publicNetworkId, cpId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "PublicNetworkGuru", true, 2, "Static",
-                null);
-        }
+                long id = rs.getLong(1);
+                String state = rs.getString(2);
+                boolean running = state.equals("Running") | state.equals("Starting") | state.equals("Stopping");
+                String privateMac = rs.getString(3);
+                String privateIp = rs.getString(4);
+                String privateNetmask = rs.getString(5);
+                String publicMac = rs.getString(6);
+                String publicIp = rs.getString(7);
+                String publicNetmask = rs.getString(8);
+                String guestMac = rs.getString(9);
+                String guestIp = rs.getString(10);
+                String guestNetmask = rs.getString(11);
+                String gateway = rs.getString(12);
+                String type = rs.getString(13);
+                try (
+                        PreparedStatement selectHost =
+                        conn.prepareStatement("SELECT host_pod_ref.gateway from host_pod_ref INNER JOIN vm_instance ON vm_instance.pod_id=host_pod_ref.id WHERE vm_instance.id=?");
+                    ) {
+                    selectHost.setLong(1, cpId);
+                    try (ResultSet hostResult = selectHost.executeQuery();) {
+
+                        if (!hostResult.next()) {
+                            throw new CloudRuntimeException("Unable to find cp " + cpId);
+                        }
 
-        long controlNicId =
-            insertNic(conn, controlNetworkId, cpId, running, guestMac, guestIp, guestNetmask, "Start", "169.254.0.1", null, "ControlNetworkGuru", false, 0, "Static",
-                guestIp != null ? (cpId + guestIp) : null);
-        if (guestIp != null) {
-            pstmt = conn.prepareStatement("UPDATE op_dc_link_local_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");
-            pstmt.setLong(1, controlNicId);
-            pstmt.setString(2, guestIp);
-            pstmt.setLong(3, dcId);
-            pstmt.executeUpdate();
-            pstmt.close();
-        }
-        long mgmtNicId =
-            insertNic(conn, managementNetworkId, cpId, running, privateMac, privateIp, privateNetmask, "Start", podGateway, null, "PodBasedNetworkGuru", false, 1,
-                "Static", privateIp != null ? (cpId + privateIp) : null);
-        if (privateIp != null) {
-            pstmt = conn.prepareStatement("UPDATE op_dc_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");
-            pstmt.setLong(1, mgmtNicId);
-            pstmt.setString(2, privateIp);
-            pstmt.setLong(3, dcId);
-            pstmt.executeUpdate();
-            pstmt.close();
+                        String podGateway = hostResult.getString(1);
+                        try (PreparedStatement selectVlan = conn.prepareStatement("SELECT v.vlan_id from vlan v, user_ip_address u where v.id=u.vlan_db_id and u.public_ip_address=?");) {
+                            selectVlan.setString(1, publicIp);
+                            try (ResultSet vlanResult = selectVlan.executeQuery();) {
+
+                                String publicVlan = null;
+                                while (vlanResult.next()) {
+                                    publicVlan = vlanResult.getString(1);
+                                }
+                                if (zoneType.equalsIgnoreCase("Basic")) {
+                                    insertNic(conn, publicNetworkId, cpId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "DirectPodBasedNetworkGuru", true, 2,
+                                        "Dhcp", null);
+                                } else {
+                                    insertNic(conn, publicNetworkId, cpId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "PublicNetworkGuru", true, 2, "Static",
+                                        null);
+                                }
+
+                                long controlNicId =
+                                    insertNic(conn, controlNetworkId, cpId, running, guestMac, guestIp, guestNetmask, "Start", "169.254.0.1", null, "ControlNetworkGuru", false, 0, "Static",
+                                        guestIp != null ? (cpId + guestIp) : null);
+                                if (guestIp != null) {
+                                    try (PreparedStatement update = conn.prepareStatement("UPDATE op_dc_link_local_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");) {
+                                        update.setLong(1, controlNicId);
+                                        update.setString(2, guestIp);
+                                        update.setLong(3, dcId);
+                                        update.executeUpdate();
+                                    }
+                                }
+                                long mgmtNicId =
+                                    insertNic(conn, managementNetworkId, cpId, running, privateMac, privateIp, privateNetmask, "Start", podGateway, null, "PodBasedNetworkGuru", false, 1,
+                                        "Static", privateIp != null ? (cpId + privateIp) : null);
+                                if (privateIp != null) {
+                                    try (PreparedStatement update = conn.prepareStatement("UPDATE op_dc_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");) {
+                                        update.setLong(1, mgmtNicId);
+                                        update.setString(2, privateIp);
+                                        update.setLong(3, dcId);
+                                        update.executeUpdate();
+                                    }
+                                }
+                            }
+                        }
+                    }
+                }
+            }
         }
     }
 
     protected void upgradeUserVms(Connection conn, long domainRouterId, long networkId, String gateway, String vnet, String guruName, String strategy)
         throws SQLException {
-        PreparedStatement pstmt =
-            conn.prepareStatement("SELECT vm_instance.id, vm_instance.private_mac_address, vm_instance.private_ip_address, vm_instance.private_netmask, vm_instance.state, vm_instance.type FROM vm_instance INNER JOIN user_vm ON vm_instance.id=user_vm.id WHERE user_vm.domain_router_id=? and vm_instance.removed IS NULL");
-        pstmt.setLong(1, domainRouterId);
-        ResultSet rs = pstmt.executeQuery();
-        List<Object[]> vms = new ArrayList<Object[]>();
-        while (rs.next()) {
-            Object[] vm = new Object[10];
-            vm[0] = rs.getLong(1); // vm id
-            vm[1] = rs.getString(2); // mac address
-            vm[2] = rs.getString(3); // ip address
-            vm[3] = rs.getString(4); // netmask
-            vm[4] = rs.getString(5); // vm state
-            vms.add(vm);
-        }
-        rs.close();
-        pstmt.close();
-
-        s_logger.debug("Upgrading " + vms.size() + " vms for router " + domainRouterId);
-
         int count = 0;
-        for (Object[] vm : vms) {
-            String state = (String)vm[4];
+        try(
+                PreparedStatement pstmt =
+            conn.prepareStatement("SELECT vm_instance.id, vm_instance.private_mac_address, vm_instance.private_ip_address, vm_instance.private_netmask, vm_instance.state, vm_instance.type FROM vm_instance INNER JOIN user_vm ON vm_instance.id=user_vm.id WHERE user_vm.domain_router_id=? and vm_instance.removed IS NULL");
+           ) {
+            pstmt.setLong(1, domainRouterId);
+            try (ResultSet rs = pstmt.executeQuery();) {
+                List<Object[]> vms = new ArrayList<Object[]>();
+                while (rs.next()) {
+                    Object[] vm = new Object[10];
+                    vm[0] = rs.getLong(1); // vm id
+                    vm[1] = rs.getString(2); // mac address
+                    vm[2] = rs.getString(3); // ip address
+                    vm[3] = rs.getString(4); // netmask
+                    vm[4] = rs.getString(5); // vm state
+                    vms.add(vm);
+                }
+                s_logger.debug("Upgrading " + vms.size() + " vms for router " + domainRouterId);
+                for (Object[] vm : vms) {
+                    String state = (String)vm[4];
+
+                    boolean running = false;
+                    if (state.equals("Running") || state.equals("Starting") || state.equals("Stopping")) {
+                        running = true;
+                        count++;
+                    }
 
-            boolean running = false;
-            if (state.equals("Running") || state.equals("Starting") || state.equals("Stopping")) {
-                running = true;
-                count++;
+                    insertNic(conn, networkId, (Long)vm[0], running, (String)vm[1], (String)vm[2], (String)vm[3], strategy, gateway, vnet, guruName, true, 0, "Dhcp", null);
+                }
             }
-
-            insertNic(conn, networkId, (Long)vm[0], running, (String)vm[1], (String)vm[2], (String)vm[3], strategy, gateway, vnet, guruName, true, 0, "Dhcp", null);
         }
-
-        pstmt = conn.prepareStatement("SELECT state FROM vm_instance WHERE id=?");
-        pstmt.setLong(1, domainRouterId);
-        rs = pstmt.executeQuery();
-        rs.next();
-        String state = rs.getString(1);
-        if (state.equals("Running") || state.equals("Starting") || state.equals("Stopping")) {
-            count++;
+        try (PreparedStatement pstmt = conn.prepareStatement("SELECT state FROM vm_instance WHERE id=?");) {
+            pstmt.setLong(1, domainRouterId);
+            try (ResultSet rs = pstmt.executeQuery();) {
+                rs.next();
+                String state = rs.getString(1);
+                if (state.equals("Running") || state.equals("Starting") || state.equals("Stopping")) {
+                    count++;
+                }
+            }
         }
-        rs.close();
-        pstmt.close();
 
         Long originalNicsCount = 0L;
-        pstmt = conn.prepareStatement("SELECT nics_count from op_networks where id=?");
-        pstmt.setLong(1, networkId);
-        ResultSet originalCountRs = pstmt.executeQuery();
-
-        if (originalCountRs.next()) {
-            originalNicsCount = originalCountRs.getLong(1);
-        }
+        try (PreparedStatement selectNicsCount = conn.prepareStatement("SELECT nics_count from op_networks where id=?");) {
+            selectNicsCount.setLong(1, networkId);
+            try (ResultSet originalCountRs = selectNicsCount.executeQuery();) {
 
-        Long resultCount = originalNicsCount + count;
-        originalCountRs.close();
-        pstmt.close();
+                if (originalCountRs.next()) {
+                    originalNicsCount = originalCountRs.getLong(1);
+                }
 
-        pstmt = conn.prepareStatement("UPDATE op_networks SET nics_count=?, check_for_gc=? WHERE id=?");
-        pstmt.setLong(1, resultCount);
-        if (count == 0) {
-            pstmt.setBoolean(2, false);
-        } else {
-            pstmt.setBoolean(2, true);
+                Long resultCount = originalNicsCount + count;
+                try (PreparedStatement updateNetworks = conn.prepareStatement("UPDATE op_networks SET nics_count=?, check_for_gc=? WHERE id=?");) {
+                    updateNetworks.setLong(1, resultCount);
+                    if (count == 0) {
+                        updateNetworks.setBoolean(2, false);
+                    } else {
+                        updateNetworks.setBoolean(2, true);
+                    }
+                    updateNetworks.setLong(3, networkId);
+                    updateNetworks.executeUpdate();
+                }
+            }
         }
-        pstmt.setLong(3, networkId);
-        pstmt.executeUpdate();
-        pstmt.close();
     }
 
     protected long insertNetwork(Connection conn, String name, String displayText, String trafficType, String broadcastDomainType, String broadcastUri, String gateway,
@@ -532,62 +523,59 @@ public class Upgrade218to22 implements DbUpgrade {
         String insertNetworkSql =
             "INSERT INTO networks(id, name, display_text, traffic_type, broadcast_domain_type, gateway, cidr, mode, network_offering_id, data_center_id, guru_name, state, domain_id, account_id, dns1, dns2, guest_type, shared, is_default, created, network_domain, related, reservation_id, broadcast_uri) "
                 + "VALUES(?,  ?,    ?,            ?,            ?,                     ?,       ?,    ?,    ?,                   ?,              ?,         ?,     ?,         ?,          ?,    ?,    ?,          ?,      ?,          now(),   ?,              ?,       ?,              ?)";
-        try {
-            PreparedStatement pstmt = conn.prepareStatement(getNextNetworkSequenceSql);
-            ResultSet rs = pstmt.executeQuery();
+        try (
+                PreparedStatement getNextNetworkSequence = conn.prepareStatement(getNextNetworkSequenceSql);
+                ResultSet rs = getNextNetworkSequence.executeQuery();
+            ) {
             rs.next();
             long seq = rs.getLong(1);
-            rs.close();
-            pstmt.close();
-
-            pstmt = conn.prepareStatement(advanceNetworkSequenceSql);
-            pstmt.executeUpdate();
-            pstmt.close();
-
-            pstmt = conn.prepareStatement(insertNetworkSql);
-            int i = 1;
-            pstmt.setLong(i++, seq);
-            pstmt.setString(i++, name);
-            pstmt.setString(i++, displayText);
-            pstmt.setString(i++, trafficType);
-            pstmt.setString(i++, broadcastDomainType);
-            pstmt.setString(i++, gateway);
-            pstmt.setString(i++, cidr);
-            pstmt.setString(i++, mode);
-            pstmt.setLong(i++, networkOfferingId);
-            pstmt.setLong(i++, dataCenterId);
-            pstmt.setString(i++, guruName);
-            pstmt.setString(i++, state);
-            pstmt.setLong(i++, domainId);
-            pstmt.setLong(i++, accountId);
-            pstmt.setString(i++, dns1);
-            pstmt.setString(i++, dns2);
-            pstmt.setString(i++, guestType);
-            pstmt.setBoolean(i++, shared);
-            pstmt.setBoolean(i++, isDefault);
-            pstmt.setString(i++, networkDomain);
-            pstmt.setLong(i++, seq);
-            pstmt.setString(i++, reservationId);
-            pstmt.setString(i++, broadcastUri);
-            pstmt.executeUpdate();
-
-            pstmt = conn.prepareStatement("INSERT INTO op_networks(id, mac_address_seq, nics_count, gc, check_for_gc) VALUES(?, ?, ?, ?, ?)");
-            pstmt.setLong(1, seq);
-            pstmt.setLong(2, 0);
-            pstmt.setLong(3, 0);
-            if (trafficType.equals("Guest")) {
-                pstmt.setBoolean(4, true);
-            } else {
-                pstmt.setBoolean(4, false);
+            try (PreparedStatement insertNetworkSequence = conn.prepareStatement(advanceNetworkSequenceSql);) {
+                insertNetworkSequence.executeUpdate();
+            }
+            try (PreparedStatement insertNetwork = conn.prepareStatement(insertNetworkSql);) {
+                int i = 1;
+                insertNetwork.setLong(i++, seq);
+                insertNetwork.setString(i++, name);
+                insertNetwork.setString(i++, displayText);
+                insertNetwork.setString(i++, trafficType);
+                insertNetwork.setString(i++, broadcastDomainType);
+                insertNetwork.setString(i++, gateway);
+                insertNetwork.setString(i++, cidr);
+                insertNetwork.setString(i++, mode);
+                insertNetwork.setLong(i++, networkOfferingId);
+                insertNetwork.setLong(i++, dataCenterId);
+                insertNetwork.setString(i++, guruName);
+                insertNetwork.setString(i++, state);
+                insertNetwork.setLong(i++, domainId);
+                insertNetwork.setLong(i++, accountId);
+                insertNetwork.setString(i++, dns1);
+                insertNetwork.setString(i++, dns2);
+                insertNetwork.setString(i++, guestType);
+                insertNetwork.setBoolean(i++, shared);
+                insertNetwork.setBoolean(i++, isDefault);
+                insertNetwork.setString(i++, networkDomain);
+                insertNetwork.setLong(i++, seq);
+                insertNetwork.setString(i++, reservationId);
+                insertNetwork.setString(i++, broadcastUri);
+                insertNetwork.executeUpdate();
+            }
+            try (PreparedStatement insertNetworks = conn.prepareStatement("INSERT INTO op_networks(id, mac_address_seq, nics_count, gc, check_for_gc) VALUES(?, ?, ?, ?, ?)");) {
+                insertNetworks.setLong(1, seq);
+                insertNetworks.setLong(2, 0);
+                insertNetworks.setLong(3, 0);
+                if (trafficType.equals("Guest")) {
+                    insertNetworks.setBoolean(4, true);
+                } else {
+                    insertNetworks.setBoolean(4, false);
+                }
+                insertNetworks.setBoolean(5, false);
+                insertNetworks.executeUpdate();
+            }
+            try (PreparedStatement insertAccountNetworkRef = conn.prepareStatement("INSERT INTO account_network_ref (account_id, network_id, is_owner) VALUES (?,    ?,  1)");) {
+                insertAccountNetworkRef.setLong(1, accountId);
+                insertAccountNetworkRef.setLong(2, seq);
+                insertAccountNetworkRef.executeUpdate();
             }
-            pstmt.setBoolean(5, false);
-            pstmt.executeUpdate();
-
-            pstmt = conn.prepareStatement("INSERT INTO account_network_ref (account_id, network_id, is_owner) VALUES (?,    ?,  1)");
-            pstmt.setLong(1, accountId);
-            pstmt.setLong(2, seq);
-            pstmt.executeUpdate();
-
             return seq;
         } catch (SQLException e) {
             throw new CloudRuntimeException("Unable to create network", e);
@@ -1117,82 +1105,81 @@ public class Upgrade218to22 implements DbUpgrade {
     }
 
     private void updateUserStats(Connection conn) {
-        try {
-
+        try (
             // update device_type information
-            PreparedStatement pstmt = conn.prepareStatement("UPDATE user_statistics SET device_type='DomainRouter'");
+                PreparedStatement pstmt = conn.prepareStatement("UPDATE user_statistics SET device_type='DomainRouter'");
+            ){
             pstmt.executeUpdate();
-            pstmt.close();
             s_logger.debug("Upgraded userStatistcis with device_type=DomainRouter");
 
             // update device_id infrormation
-            pstmt = conn.prepareStatement("SELECT id, account_id, data_center_id FROM user_statistics");
-            ResultSet rs = pstmt.executeQuery();
-
-            while (rs.next()) {
-                Long id = rs.getLong(1); // user stats id
-                Long accountId = rs.getLong(2); // account id
-                Long dataCenterId = rs.getLong(3); // zone id
-
-                pstmt = conn.prepareStatement("SELECT networktype from data_center where id=?");
-                pstmt.setLong(1, dataCenterId);
-
-                ResultSet dcSet = pstmt.executeQuery();
-
-                if (!dcSet.next()) {
-                    s_logger.error("Unable to get data_center information as a part of user_statistics update");
-                    throw new CloudRuntimeException("Unable to get data_center information as a part of user_statistics update");
-                }
-
-                String dataCenterType = dcSet.getString(1);
-
-                if (dataCenterType.equalsIgnoreCase("basic")) {
-                    accountId = 1L;
-                }
-
-                pstmt = conn.prepareStatement("SELECT id from vm_instance where account_id=? AND data_center_id=? AND type='DomainRouter'");
-                pstmt.setLong(1, accountId);
-                pstmt.setLong(2, dataCenterId);
-                ResultSet rs1 = pstmt.executeQuery();
-
-                Long deviceId = 0L;
-                if (!rs1.next()) {
-                    // check if there are any non-removed user vms existing for this account
-                    // if all vms are expunged, and there is no domR, just skip this record
-                    pstmt = conn.prepareStatement("SELECT * from vm_instance where account_id=? AND data_center_id=? AND removed IS NULL");
-                    pstmt.setLong(1, accountId);
-                    pstmt.setLong(2, dataCenterId);
-                    ResultSet nonRemovedVms = pstmt.executeQuery();
-
-                    if (nonRemovedVms.next()) {
-                        s_logger.warn("Failed to find domR for for account id=" + accountId + " in zone id=" + dataCenterId +
-                            "; will try to locate domR based on user_vm info");
-                        //try to get domR information from the user_vm belonging to the account
-                        pstmt =
-                            conn.prepareStatement("SELECT u.domain_router_id from user_vm u, vm_instance v where u.account_id=? AND v.data_center_id=? AND v.removed IS NULL AND u.domain_router_id is NOT NULL");
-                        pstmt.setLong(1, accountId);
-                        pstmt.setLong(2, dataCenterId);
-                        ResultSet userVmSet = pstmt.executeQuery();
-                        if (!userVmSet.next()) {
-                            s_logger.warn("Skipping user_statistics upgrade for account id=" + accountId + " in datacenter id=" + dataCenterId);
-                            continue;
+            try (
+                    PreparedStatement selectUserStatistics = conn.prepareStatement("SELECT id, account_id, data_center_id FROM user_statistics");
+                    ResultSet rs = selectUserStatistics.executeQuery();
+                ) {
+                while (rs.next()) {
+                    Long id = rs.getLong(1); // user stats id
+                    Long accountId = rs.getLong(2); // account id
+                    Long dataCenterId = rs.getLong(3); // zone id
+
+                    try (PreparedStatement selectNetworkType = conn.prepareStatement("SELECT networktype from data_center where id=?");) {
+                        selectNetworkType.setLong(1, dataCenterId);
+                        try (ResultSet dcSet = selectNetworkType.executeQuery();) {
+                            if (!dcSet.next()) {
+                                s_logger.error("Unable to get data_center information as a part of user_statistics update");
+                                throw new CloudRuntimeException("Unable to get data_center information as a part of user_statistics update");
+                            }
+                            String dataCenterType = dcSet.getString(1);
+                            if (dataCenterType.equalsIgnoreCase("basic")) {
+                                accountId = 1L;
+                            }
+                        }
+                    }
+                    try (PreparedStatement selectDomainRouterIds = conn.prepareStatement("SELECT id from vm_instance where account_id=? AND data_center_id=? AND type='DomainRouter'");) {
+                        selectDomainRouterIds.setLong(1, accountId);
+                        selectDomainRouterIds.setLong(2, dataCenterId);
+                        try (ResultSet domainRouterIdResult = selectDomainRouterIds.executeQuery();) {
+                            Long deviceId = 0L;
+                            if (!domainRouterIdResult.next()) {
+                                // check if there are any non-removed user vms existing for this account
+                                // if all vms are expunged, and there is no domR, just skip this record
+                                try (PreparedStatement selectnonRemovedVms = conn.prepareStatement("SELECT * from vm_instance where account_id=? AND data_center_id=? AND removed IS NULL");) {
+                                    selectnonRemovedVms.setLong(1, accountId);
+                                    selectnonRemovedVms.setLong(2, dataCenterId);
+                                    try (ResultSet nonRemovedVms = selectnonRemovedVms.executeQuery();) {
+                                        if (nonRemovedVms.next()) {
+                                            s_logger.warn("Failed to find domR for for account id=" + accountId + " in zone id=" + dataCenterId +
+                                                    "; will try to locate domR based on user_vm info");
+                                            //try to get domR information from the user_vm belonging to the account
+                                            try (PreparedStatement selectNetworkType =
+                                                    conn.prepareStatement("SELECT u.domain_router_id from user_vm u, vm_instance v where u.account_id=? AND v.data_center_id=? AND v.removed IS NULL AND u.domain_router_id is NOT NULL");) {
+                                                selectNetworkType.setLong(1, accountId);
+                                                selectNetworkType.setLong(2, dataCenterId);
+                                                try (ResultSet userVmSet = selectNetworkType.executeQuery();) {
+                                                    if (!userVmSet.next()) {
+                                                        s_logger.warn("Skipping user_statistics upgrade for account id=" + accountId + " in datacenter id=" + dataCenterId);
+                                                        continue;
+                                                    }
+                                                    deviceId = userVmSet.getLong(1);
+                                                }
+                                            }
+                                        } else {
+                                            s_logger.debug("Account id=" + accountId + " doesn't own any user vms and domRs, so skipping user_statistics update");
+                                            continue;
+                                        }
+                                    }
+                                }
+                            } else {
+                                deviceId = domainRouterIdResult.getLong(1);
+                            }
+                            try (PreparedStatement updateUserStatistics = conn.prepareStatement("UPDATE user_statistics SET device_id=? where id=?");) {
+                                updateUserStatistics.setLong(1, deviceId);
+                                updateUserStatistics.setLong(2, id);
+                                updateUserStatistics.executeUpdate();
+                            }
                         }
-                        deviceId = userVmSet.getLong(1);
-                    } else {
-                        s_logger.debug("Account id=" + accountId + " doesn't own any user vms and domRs, so skipping user_statistics update");
-                        continue;
                     }
-                } else {
-                    deviceId = rs1.getLong(1);
                 }
-
-                pstmt = conn.prepareStatement("UPDATE user_statistics SET device_id=? where id=?");
-                pstmt.setLong(1, deviceId);
-                pstmt.setLong(2, id);
-                pstmt.executeUpdate();
-
-                pstmt = conn.prepareStatement("");
-
             }
             s_logger.debug("Upgraded userStatistcis with deviceId(s)");
 
@@ -1202,10 +1189,11 @@ public class Upgrade218to22 implements DbUpgrade {
     }
 
     public void upgradePortForwardingRules(Connection conn) {
-        try {
-            PreparedStatement pstmt =
-                conn.prepareStatement("SELECT id, public_ip_address, public_port, private_ip_address, private_port, protocol FROM ip_forwarding WHERE forwarding=1");
-            ResultSet rs = pstmt.executeQuery();
+        try (
+                PreparedStatement pstmt =
+                    conn.prepareStatement("SELECT id, public_ip_address, public_port, private_ip_address, private_port, protocol FROM ip_forwarding WHERE forwarding=1");
+                ResultSet rs = pstmt.executeQuery();
+            ) {
             ArrayList<Object[]> rules = new ArrayList<Object[]>();
             while (rs.next()) {
                 Object[] rule = new Object[10];
@@ -1217,8 +1205,6 @@ public class Upgrade218to22 implements DbUpgrade {
                 rule[5] = rs.getString(6); // rule protocol
                 rules.add(rule);
             }
-            rs.close();
-            pstmt.close();
 
             if (!rules.isEmpty()) {
                 s_logger.debug("Found " + rules.size() + " port forwarding rules to upgrade");
@@ -1228,84 +1214,81 @@ public class Upgrade218to22 implements DbUpgrade {
                     String protocol = (String)rule[5];
                     String publicIp = (String)rule[1];
 
-                    pstmt = conn.prepareStatement("SELECT id, account_id, domain_id, network_id FROM user_ip_address WHERE public_ip_address=?");
-                    pstmt.setString(1, publicIp);
-                    rs = pstmt.executeQuery();
+                    try (PreparedStatement selectUserIpAddressData = conn.prepareStatement("SELECT id, account_id, domain_id, network_id FROM user_ip_address WHERE public_ip_address=?");) {
+                        selectUserIpAddressData.setString(1, publicIp);
+                        try (ResultSet userIpAddressData = selectUserIpAddressData.executeQuery();) {
 
-                    if (!rs.next()) {
-                        s_logger.error("Unable to find public IP address " + publicIp);
-                        throw new CloudRuntimeException("Unable to find public IP address " + publicIp);
+                            if (!userIpAddressData.next()) {
+                                s_logger.error("Unable to find public IP address " + publicIp);
+                                throw new CloudRuntimeException("Unable to find public IP address " + publicIp);
+                            }
+                            int ipAddressId = userIpAddressData.getInt(1);
+                            long accountId = userIpAddressData.getLong(2);
+                            long domainId = userIpAddressData.getLong(3);
+                            long networkId = userIpAddressData.getLong(4);
+                            String privateIp = (String)rule[3];
+
+                            // update port_forwarding_rules table
+                            s_logger.trace("Updating port_forwarding_rules table...");
+                            try (PreparedStatement selectInstanceId = conn.prepareStatement("SELECT instance_id FROM nics where network_id=? AND ip4_address=?");) {
+                                selectInstanceId.setLong(1, networkId);
+                                selectInstanceId.setString(2, privateIp);
+                                try (ResultSet selectedInstanceId = selectInstanceId.executeQuery();) {
+
+                                    if (!selectedInstanceId.next()) {
+                                        // the vm might be expunged already...so just give the warning
+                                        s_logger.warn("Unable to find vmId for private ip address " + privateIp + " for account id=" + accountId + "; assume that the vm is expunged");
+                                        // throw new CloudRuntimeException("Unable to find vmId for private ip address " + privateIp +
+                                        // " for account id=" + accountId);
+                                    } else {
+                                        long instanceId = selectedInstanceId.getLong(1);
+                                        s_logger.debug("Instance id is " + instanceId);
+                                        // update firewall_rules table
+                                        s_logger.trace("Updating firewall_rules table as a part of PF rules upgrade...");
+                                        try (
+                                                PreparedStatement insertFirewallRules =
+                                                conn.prepareStatement("INSERT INTO firewall_rules (id, ip_address_id, start_port, end_port, state, protocol, purpose, account_id, domain_id, network_id, xid, is_static_nat, created) VALUES (?,    ?,      ?,      ?,      'Active',        ?,     'PortForwarding',       ?,      ?,      ?,      ?,       0,     now())");
+                                            ) {
+                                            insertFirewallRules.setLong(1, id);
+                                            insertFirewallRules.setInt(2, ipAddressId);
+                                            insertFirewallRules.setInt(3, Integer.parseInt(sourcePort.trim()));
+                                            insertFirewallRules.setInt(4, Integer.parseInt(sourcePort.trim()));
+                                            insertFirewallRules.setString(5, protocol);
+                                            insertFirewallRules.setLong(6, accountId);
+                                            insertFirewallRules.setLong(7, domainId);
+                                            insertFirewallRules.setLong(8, networkId);
+                                            insertFirewallRules.setString(9, UUID.randomUUID().toString());
+                                            insertFirewallRules.executeUpdate();
+                                            s_logger.trace("firewall_rules table is updated as a part of PF rules upgrade");
+                                        }
+                                        String privatePort = (String)rule[4];
+                                        try (PreparedStatement insertPortForwardingRules = conn.prepareStatement("INSERT INTO port_forwarding_rules VALUES (?,    ?,      ?,      ?,       ?)");) {
+                                            insertPortForwardingRules.setLong(1, id);
+                                            insertPortForwardingRules.setLong(2, instanceId);
+                                            insertPortForwardingRules.setString(3, privateIp);
+                                            insertPortForwardingRules.setInt(4, Integer.parseInt(privatePort.trim()));
+                                            insertPortForwardingRules.setInt(5, Integer.parseInt(privatePort.trim()));
+                                            insertPortForwardingRules.executeUpdate();
+                                        }
+                                        s_logger.trace("port_forwarding_rules table is updated");
+                                    }
+                                }
+                            }
+                        }
                     }
-
-                    int ipAddressId = rs.getInt(1);
-                    long accountId = rs.getLong(2);
-                    long domainId = rs.getLong(3);
-                    long networkId = rs.getLong(4);
-                    String privateIp = (String)rule[3];
-
-                    rs.close();
-                    pstmt.close();
-
-                    // update port_forwarding_rules table
-                    s_logger.trace("Updating port_forwarding_rules table...");
-                    pstmt = conn.prepareStatement("SELECT instance_id FROM nics where network_id=? AND ip4_address=?");
-                    pstmt.setLong(1, networkId);
-                    pstmt.setString(2, privateIp);
-                    rs = pstmt.executeQuery();
-
-                    if (!rs.next()) {
-                        // the vm might be expunged already...so just give the warning
-                        s_logger.warn("Unable to find vmId for private ip address " + privateIp + " for account id=" + accountId + "; assume that the vm is expunged");
-                        // throw new CloudRuntimeException("Unable to find vmId for private ip address " + privateIp +
-                        // " for account id=" + accountId);
-                    } else {
-                        long instanceId = rs.getLong(1);
-                        s_logger.debug("Instance id is " + instanceId);
-                        // update firewall_rules table
-                        s_logger.trace("Updating firewall_rules table as a part of PF rules upgrade...");
-                        pstmt =
-                            conn.prepareStatement("INSERT INTO firewall_rules (id, ip_address_id, start_port, end_port, state, protocol, purpose, account_id, domain_id, network_id, xid, is_static_nat, created) VALUES (?,    ?,      ?,      ?,      'Active',        ?,     'PortForwarding',       ?,      ?,      ?,      ?,       0,     now())");
-                        pstmt.setLong(1, id);
-                        pstmt.setInt(2, ipAddressId);
-                        pstmt.setInt(3, Integer.parseInt(sourcePort.trim()));
-                        pstmt.setInt(4, Integer.parseInt(sourcePort.trim()));
-                        pstmt.setString(5, protocol);
-                        pstmt.setLong(6, accountId);
-                        pstmt.setLong(7, domainId);
-                        pstmt.setLong(8, networkId);
-                        pstmt.setString(9, UUID.randomUUID().toString());
-                        pstmt.executeUpdate();
-                        pstmt.close();
-                        s_logger.trace("firewall_rules table is updated as a part of PF rules upgrade");
-
-                        rs.close();
-                        pstmt.close();
-
-                        String privatePort = (String)rule[4];
-                        pstmt = conn.prepareStatement("INSERT INTO port_forwarding_rules VALUES (?,    ?,      ?,      ?,       ?)");
-                        pstmt.setLong(1, id);
-                        pstmt.setLong(2, instanceId);
-                        pstmt.setString(3, privateIp);
-                        pstmt.setInt(4, Integer.parseInt(privatePort.trim()));
-                        pstmt.setInt(5, Integer.parseInt(privatePort.trim()));
-                        pstmt.executeUpdate();
-                        pstmt.close();
-                        s_logger.trace("port_forwarding_rules table is updated");
-
-                    }
-
-                }
-            }
-            s_logger.debug("Port forwarding rules are updated");
-        } catch (SQLException e) {
-            throw new CloudRuntimeException("Can't update port forwarding rules ", e);
-        }
-    }
+                }
+            }
+            s_logger.debug("Port forwarding rules are updated");
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Can't update port forwarding rules ", e);
+        }
+    }
 
     public void upgradeLoadBalancingRules(Connection conn) {
-        try {
-            PreparedStatement pstmt = conn.prepareStatement("SELECT name, ip_address, public_port, private_port, algorithm, id FROM load_balancer");
-            ResultSet rs = pstmt.executeQuery();
+        try (
+                PreparedStatement pstmt = conn.prepareStatement("SELECT name, ip_address, public_port, private_port, algorithm, id FROM load_balancer");
+                ResultSet rs = pstmt.executeQuery();
+            ) {
             ArrayList<Object[]> lbs = new ArrayList<Object[]>();
             while (rs.next()) {
                 Object[] lb = new Object[10];
@@ -1317,20 +1300,18 @@ public class Upgrade218to22 implements DbUpgrade {
                 lb[5] = rs.getLong(6); // lb Id
                 lbs.add(lb);
             }
-            rs.close();
-            pstmt.close();
 
             if (!lbs.isEmpty()) {
                 s_logger.debug("Found " + lbs.size() + " lb rules to upgrade");
-                pstmt = conn.prepareStatement("SELECT id FROM firewall_rules order by id");
-                rs = pstmt.executeQuery();
                 long newLbId = 0;
-                while (rs.next()) {
-                    newLbId = rs.getLong(1);
+                try (
+                        PreparedStatement selectFWRules = conn.prepareStatement("SELECT max(id) FROM firewall_rules order by id");
+                        ResultSet fwRules = selectFWRules.executeQuery();
+                    ) {
+                    if (rs.next()) {
+                        newLbId = rs.getLong(1);
+                    }
                 }
-                rs.close();
-                pstmt.close();
-
                 for (Object[] lb : lbs) {
                     String name = (String)lb[0];
                     String publicIp = (String)lb[1];
@@ -1340,81 +1321,80 @@ public class Upgrade218to22 implements DbUpgrade {
                     Long originalLbId = (Long)lb[5];
                     newLbId = newLbId + 1;
 
-                    pstmt = conn.prepareStatement("SELECT id, account_id, domain_id, network_id FROM user_ip_address WHERE public_ip_address=?");
-                    pstmt.setString(1, publicIp);
-                    rs = pstmt.executeQuery();
-
-                    if (!rs.next()) {
-                        s_logger.warn("Unable to find public IP address " + publicIp + "; skipping lb rule id=" + originalLbId +
-                            " from update. Cleaning it up from load_balancer_vm_map and load_balancer table");
-                        pstmt = conn.prepareStatement("DELETE from load_balancer_vm_map where load_balancer_id=?");
-                        pstmt.setLong(1, originalLbId);
-                        pstmt.executeUpdate();
-
-                        pstmt = conn.prepareStatement("DELETE from load_balancer where id=?");
-                        pstmt.setLong(1, originalLbId);
-                        pstmt.executeUpdate();
-
-                        continue;
+                    try (PreparedStatement selectIpData = conn.prepareStatement("SELECT id, account_id, domain_id, network_id FROM user_ip_address WHERE public_ip_address=?");) {
+                        selectIpData.setString(1, publicIp);
+                        try (ResultSet ipData = selectIpData.executeQuery();) {
+
+                            if (!ipData.next()) {
+                                s_logger.warn("Unable to find public IP address " + publicIp + "; skipping lb rule id=" + originalLbId +
+                                        " from update. Cleaning it up from load_balancer_vm_map and load_balancer table");
+                                try (PreparedStatement deleteLbVmMap = conn.prepareStatement("DELETE from load_balancer_vm_map where load_balancer_id=?");) {
+                                    deleteLbVmMap.setLong(1, originalLbId);
+                                    deleteLbVmMap.executeUpdate();
+                                }
+                                try (PreparedStatement deleteLoadBalancer = conn.prepareStatement("DELETE from load_balancer where id=?");) {
+                                    deleteLoadBalancer.setLong(1, originalLbId);
+                                    deleteLoadBalancer.executeUpdate();
+                                }
+                                continue;
+                            }
+                            int ipAddressId = ipData.getInt(1);
+                            long accountId = ipData.getLong(2);
+                            long domainId = ipData.getLong(3);
+                            long networkId = ipData.getLong(4);
+                            // update firewall_rules table
+                            s_logger.trace("Updating firewall_rules table as a part of LB rules upgrade...");
+                            try (PreparedStatement insertFirewallRules =
+                                conn.prepareStatement("INSERT INTO firewall_rules (id, ip_address_id, start_port, end_port, state, protocol, purpose, account_id, domain_id, network_id, xid, is_static_nat, created) VALUES (?,    ?,      ?,      ?,      'Active',        ?,     'LoadBalancing',       ?,      ?,      ?,      ?,       0,       now())");) {
+                                insertFirewallRules.setLong(1, newLbId);
+                                insertFirewallRules.setInt(2, ipAddressId);
+                                insertFirewallRules.setInt(3, Integer.parseInt(sourcePort));
+                                insertFirewallRules.setInt(4, Integer.parseInt(sourcePort));
+                                insertFirewallRules.setString(5, "tcp");
+                                insertFirewallRules.setLong(6, accountId);
+                                insertFirewallRules.setLong(7, domainId);
+                                insertFirewallRules.setLong(8, networkId);
+                                insertFirewallRules.setString(9, UUID.randomUUID().toString());
+                                insertFirewallRules.executeUpdate();
+                            }
+                            s_logger.trace("firewall_rules table is updated as a part of LB rules upgrade");
+                        }
                     }
 
-                    int ipAddressId = rs.getInt(1);
-                    long accountId = rs.getLong(2);
-                    long domainId = rs.getLong(3);
-                    long networkId = rs.getLong(4);
-
-                    rs.close();
-                    pstmt.close();
-
-                    // update firewall_rules table
-                    s_logger.trace("Updating firewall_rules table as a part of LB rules upgrade...");
-                    pstmt =
-                        conn.prepareStatement("INSERT INTO firewall_rules (id, ip_address_id, start_port, end_port, state, protocol, purpose, account_id, domain_id, network_id, xid, is_static_nat, created) VALUES (?,    ?,      ?,      ?,      'Active',        ?,     'LoadBalancing',       ?,      ?,      ?,      ?,       0,       now())");
-                    pstmt.setLong(1, newLbId);
-                    pstmt.setInt(2, ipAddressId);
-                    pstmt.setInt(3, Integer.parseInt(sourcePort));
-                    pstmt.setInt(4, Integer.parseInt(sourcePort));
-                    pstmt.setString(5, "tcp");
-                    pstmt.setLong(6, accountId);
-                    pstmt.setLong(7, domainId);
-                    pstmt.setLong(8, networkId);
-                    pstmt.setString(9, UUID.randomUUID().toString());
-                    pstmt.executeUpdate();
-                    pstmt.close();
-                    s_logger.trace("firewall_rules table is updated as a part of LB rules upgrade");
 
                     // update load_balancing_rules
                     s_logger.trace("Updating load_balancing_rules table as a part of LB rules upgrade...");
-                    pstmt = conn.prepareStatement("INSERT INTO load_balancing_rules VALUES (?,      ?,      NULL,      ?,       ?,      ?)");
-                    pstmt.setLong(1, newLbId);
-                    pstmt.setString(2, name);
-                    pstmt.setInt(3, Integer.parseInt(destPort));
-                    pstmt.setInt(4, Integer.parseInt(destPort));
-                    pstmt.setString(5, algorithm);
-                    pstmt.executeUpdate();
-                    pstmt.close();
+                    try (PreparedStatement insertLoadBalancer = conn.prepareStatement("INSERT INTO load_balancing_rules VALUES (?,      ?,      NULL,      ?,       ?,      ?)");) {
+                        insertLoadBalancer.setLong(1, newLbId);
+                        insertLoadBalancer.setString(2, name);
+                        insertLoadBalancer.setInt(3, Integer.parseInt(destPort));
+                        insertLoadBalancer.setInt(4, Integer.parseInt(destPort));
+                        insertLoadBalancer.setString(5, algorithm);
+                        insertLoadBalancer.executeUpdate();
+                    }
                     s_logger.trace("load_balancing_rules table is updated as a part of LB rules upgrade");
 
                     // update load_balancer_vm_map table
                     s_logger.trace("Updating load_balancer_vm_map table as a part of LB rules upgrade...");
-                    pstmt = conn.prepareStatement("SELECT instance_id FROM load_balancer_vm_map WHERE load_balancer_id=?");
-                    pstmt.setLong(1, originalLbId);
-                    rs = pstmt.executeQuery();
-                    ArrayList<Object[]> lbMaps = new ArrayList<Object[]>();
-                    while (rs.next()) {
-                        Object[] lbMap = new Object[10];
-                        lbMap[0] = rs.getLong(1); // instanceId
-                        lbMaps.add(lbMap);
+                    try (
+                            PreparedStatement selectInstance = conn.prepareStatement("SELECT instance_id FROM load_balancer_vm_map WHERE load_balancer_id=?");
+                        ) {
+                        selectInstance.setLong(1, originalLbId);
+                        try (ResultSet selectedInstance = selectInstance.executeQuery();) {
+                            ArrayList<Object[]> lbMaps = new ArrayList<Object[]>();
+                            while (selectedInstance.next()) {
+                                Object[] lbMap = new Object[10];
+                                lbMap[0] = selectedInstance.getLong(1); // instanceId
+                                lbMaps.add(lbMap);
+                            }
+                        }
                     }
-                    rs.close();
-                    pstmt.close();
-
-                    pstmt = conn.prepareStatement("UPDATE load_balancer_vm_map SET load_balancer_id=? WHERE load_balancer_id=?");
-                    pstmt.setLong(1, newLbId);
-                    pstmt.setLong(2, originalLbId);
-                    pstmt.executeUpdate();
-                    pstmt.close();
 
+                    try (PreparedStatement updateLoadBalancer = conn.prepareStatement("UPDATE load_balancer_vm_map SET load_balancer_id=? WHERE load_balancer_id=?");) {
+                        updateLoadBalancer.setLong(1, newLbId);
+                        updateLoadBalancer.setLong(2, originalLbId);
+                        updateLoadBalancer.executeUpdate();
+                    }
                     s_logger.trace("load_balancer_vm_map table is updated as a part of LB rules upgrade");
                 }
             }
@@ -1425,71 +1405,73 @@ public class Upgrade218to22 implements DbUpgrade {
     }
 
     private void upgradeHostMemoryCapacityInfo(Connection conn) {
-        try {
-            // count user_vm memory info (M Bytes)
-            PreparedStatement pstmt =
-                conn.prepareStatement("select h.id, sum(s.ram_size) from host h, vm_instance v, service_offering s where h.type='Routing' and v.state='Running' and v.`type`='User' and v.host_id=h.id  and v.service_offering_id = s.id group by h.id");
-
-            ResultSet rs = pstmt.executeQuery();
-            Map<Long, Long> hostUsedMemoryInfo = new HashMap<Long, Long>();
+        Map<Long, Long> hostUsedMemoryInfo = new HashMap<Long, Long>();
+        // count user_vm memory info (M Bytes)
+        try (
+                PreparedStatement pstmt =
+                    conn.prepareStatement("select h.id, sum(s.ram_size) from host h, vm_instance v, service_offering s where h.type='Routing' and v.state='Running' and v.`type`='User' and v.host_id=h.id  and v.service_offering_id = s.id group by h.id");
+
+                ResultSet rs = pstmt.executeQuery();
+            ) {
             while (rs.next()) {
                 hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2));
             }
-            rs.close();
-            pstmt.close();
-
             int proxyRamSize = NumbersUtil.parseInt(getConfigValue(conn, "consoleproxy.ram.size"), 1024); // ConsoleProxyManager.DEFAULT_PROXY_VM_RAMSIZE);
             int domrRamSize = NumbersUtil.parseInt(getConfigValue(conn, "router.ram.size"), 128); // VpcVirtualNetworkApplianceManager.DEFAULT_ROUTER_VM_RAMSIZE);
             int ssvmRamSize = NumbersUtil.parseInt(getConfigValue(conn, "secstorage.vm.ram.size"), 256); // SecondaryStorageVmManager.DEFAULT_SS_VM_RAMSIZE);
 
-            pstmt =
+            try(
+                PreparedStatement selectConsoleProxyHostInfo =
                 conn.prepareStatement("select h.id, count(v.id) from host h, vm_instance v where h.type='Routing' and v.state='Running' and v.`type`='ConsoleProxy' and v.host_id=h.id group by h.id");
-            rs = pstmt.executeQuery();
-            while (rs.next()) {
-                if (hostUsedMemoryInfo.get(rs.getLong(1)) != null) {
-                    Long usedMem = hostUsedMemoryInfo.get(rs.getLong(1));
-                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * proxyRamSize + usedMem);
-                } else {
-                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * proxyRamSize);
+                ResultSet consoleProxyHostInfo = selectConsoleProxyHostInfo.executeQuery();
+               ) {
+                while (consoleProxyHostInfo.next()) {
+                    if (hostUsedMemoryInfo.get(consoleProxyHostInfo.getLong(1)) != null) {
+                        Long usedMem = hostUsedMemoryInfo.get(consoleProxyHostInfo.getLong(1));
+                        hostUsedMemoryInfo.put(consoleProxyHostInfo.getLong(1), consoleProxyHostInfo.getLong(2) * proxyRamSize + usedMem);
+                    } else {
+                        hostUsedMemoryInfo.put(consoleProxyHostInfo.getLong(1), consoleProxyHostInfo.getLong(2) * proxyRamSize);
+                    }
                 }
             }
-            rs.close();
-            pstmt.close();
 
-            pstmt =
+            try (
+                PreparedStatement selectDomainRouterHostInfo =
                 conn.prepareStatement("select h.id, count(v.id) from host h, vm_instance v where h.type='Routing' and v.state='Running' and v.`type`='DomainRouter' and v.host_id=h.id group by h.id");
-            rs = pstmt.executeQuery();
-            while (rs.next()) {
-                if (hostUsedMemoryInfo.get(rs.getLong(1)) != null) {
-                    Long usedMem = hostUsedMemoryInfo.get(rs.getLong(1));
-                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * domrRamSize + usedMem);
-                } else {
-                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * domrRamSize);
+                ResultSet domainrouterHostInfo = selectDomainRouterHostInfo.executeQuery();
+                ) {
+                while (domainrouterHostInfo.next()) {
+                    if (hostUsedMemoryInfo.get(domainrouterHostInfo.getLong(1)) != null) {
+                        Long usedMem = hostUsedMemoryInfo.get(domainrouterHostInfo.getLong(1));
+                        hostUsedMemoryInfo.put(domainrouterHostInfo.getLong(1), domainrouterHostInfo.getLong(2) * domrRamSize + usedMem);
+                    } else {
+                        hostUsedMemoryInfo.put(domainrouterHostInfo.getLong(1), domainrouterHostInfo.getLong(2) * domrRamSize);
+                    }
                 }
             }
-            rs.close();
-            pstmt.close();
 
-            pstmt =
+            try (
+                PreparedStatement selectSsvmHostInfo =
                 conn.prepareStatement("select h.id, count(v.id) from host h, vm_instance v where h.type='Routing' and v.state='Running' and v.`type`='SecondaryStorageVm' and v.host_id=h.id group by h.id");
-            rs = pstmt.executeQuery();
-            while (rs.next()) {
-                if (hostUsedMemoryInfo.get(rs.getLong(1)) != null) {
-                    Long usedMem = hostUsedMemoryInfo.get(rs.getLong(1));
-                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * ssvmRamSize + usedMem);
-                } else {
-                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * ssvmRamSize);
+                ResultSet ssvmHostInfo = selectSsvmHostInfo.executeQuery();
+                ) {
+                while (ssvmHostInfo.next()) {
+                    if (hostUsedMemoryInfo.get(ssvmHostInfo.getLong(1)) != null) {
+                        Long usedMem = hostUsedMemoryInfo.get(ssvmHostInfo.getLong(1));
+                        hostUsedMemoryInfo.put(ssvmHostInfo.getLong(1), ssvmHostInfo.getLong(2) * ssvmRamSize + usedMem);
+                    } else {
+                        hostUsedMemoryInfo.put(ssvmHostInfo.getLong(1), ssvmHostInfo.getLong(2) * ssvmRamSize);
+                    }
                 }
             }
-            rs.close();
-            pstmt.close();
 
             for (Map.Entry<Long, Long> entry : hostUsedMemoryInfo.entrySet()) {
-                pstmt = conn.prepareStatement

<TRUNCATED>

Mime
View raw message