@Transactional(readOnly = true) private Collection<PortForwardingRuleTO> calculateAllRules( Map<String, PortForwardingRuleVO> ruleMap, String vrUuid) { String sql = "select rule.uuid, nic.ip, vip.ip from PortForwardingRuleVO rule, VmNicVO nic, VipVO vip where rule.vmNicUuid = nic.uuid and rule.uuid in (:ruleUuids) and vip.uuid = rule.vipUuid"; TypedQuery<Tuple> q = dbf.getEntityManager().createQuery(sql, Tuple.class); q.setParameter("ruleUuids", ruleMap.keySet()); List<Tuple> privateIps = q.getResultList(); Map<String, PortForwardingRuleTO> tos = new HashMap<String, PortForwardingRuleTO>(); for (Tuple t : privateIps) { String ruleUuid = t.get(0, String.class); PortForwardingRuleTO to = new PortForwardingRuleTO(); to.setPrivateIp(t.get(1, String.class)); PortForwardingRuleVO ruleVO = ruleMap.get(ruleUuid); to.setAllowedCidr(ruleVO.getAllowedCidr()); to.setPrivatePortEnd(ruleVO.getPrivatePortEnd()); to.setPrivatePortStart(ruleVO.getPrivatePortStart()); to.setVipPortEnd(ruleVO.getVipPortEnd()); to.setSnatInboundTraffic( PortForwardingGlobalConfig.SNAT_INBOUND_TRAFFIC.value(Boolean.class)); to.setVipPortStart(ruleVO.getVipPortStart()); to.setVipIp(t.get(2, String.class)); to.setProtocolType(ruleVO.getProtocolType().toString()); tos.put(ruleUuid, to); } assert tos.size() == ruleMap.size(); sql = "select rule.uuid, vrnic.mac from PortForwardingRuleVO rule, VmNicVO vrnic, VmNicVO nic2, ApplianceVmVO vr where vr.uuid = vrnic.vmInstanceUuid and vrnic.l3NetworkUuid = nic2.l3NetworkUuid and nic2.uuid = rule.vmNicUuid and rule.uuid in (:ruleUuids) and vr.uuid = :vrUuid"; TypedQuery<Tuple> privateMacQuery = dbf.getEntityManager().createQuery(sql, Tuple.class); privateMacQuery.setParameter("ruleUuids", ruleMap.keySet()); privateMacQuery.setParameter("vrUuid", vrUuid); List<Tuple> privateMacs = privateMacQuery.getResultList(); for (Tuple t : privateMacs) { String ruleUuid = t.get(0, String.class); PortForwardingRuleTO to = tos.get(ruleUuid); to.setPrivateMac(t.get(1, String.class)); } return tos.values(); }