Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

Introduction

In this page you can find the example usage for org.hibernate SQLQuery addScalar.

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:org.tsm.concharto.service.EventSearchServiceHib.java

License:Apache License

@SuppressWarnings("unchecked")
private Long getCountInternal(Geometry boundingBox, SearchParams params) {
    LapTimer timer = new LapTimer(this.log);
    SQLQuery sqlQuery = createQuery(SQL_PREFIX_GET_COUNT, boundingBox, params);
    List result = sqlQuery.addScalar("count(*)", Hibernate.LONG).list();
    timer.timeIt("count").logInfoTime();
    return (Long) result.get(0);
}

From source file:org.xerela.provider.telemetry.TelemetryProvider.java

License:Mozilla Public License

/** {@inheritDoc} */
@SuppressWarnings({ "unchecked", "nls" })
public MacPageData getMacTable(MacPageData pageData, String ipAddress, String managedNetwork) {
    ZDeviceCore device = getDevice(ipAddress, managedNetwork);
    if (device == null) {
        pageData.setMacEntries(new MacTableEntry[0]);
        pageData.setTotal(0);/*from  w w  w. ja v  a  2 s. com*/
        return pageData;
    }

    boolean ownTransaction = TransactionElf.beginOrJoinTransaction();
    try {
        Session session = TelemetryActivator.getSessionFactory().getCurrentSession();
        String fromClause = "FROM discovery_mac WHERE device_id = " + device.getDeviceId();
        SQLQuery query = session
                .createSQLQuery("SELECT mac_address, interface, vlan " + fromClause + " ORDER BY interface");
        query.addScalar("mac_address", Hibernate.LONG);
        query.addScalar("interface", Hibernate.STRING);
        query.addScalar("vlan", Hibernate.STRING);
        query.setFirstResult(pageData.getOffset()).setMaxResults(pageData.getPageSize());
        query.scroll(ScrollMode.SCROLL_INSENSITIVE);
        List<Object[]> resultList = (List<Object[]>) query.list();
        if (resultList == null || resultList.isEmpty()) {
            pageData.setMacEntries(new MacTableEntry[0]);
            pageData.setTotal(0);
            return pageData;
        } else {
            if (pageData.getOffset() == 0) {
                // Set the total result size into the page data.
                query = session.createSQLQuery("SELECT count(mac_address) " + fromClause);
                pageData.setTotal(getCount(query));
            }

            List<MacTableEntry> macTable = new ArrayList<MacTableEntry>();
            for (Object[] resultEntry : resultList) {
                MacTableEntry entry = new MacTableEntry();
                entry.setMacAddress((Long) resultEntry[0]);
                entry.setPort((String) resultEntry[1]);
                entry.setVlan((String) resultEntry[2]);
                macTable.add(entry);
            }
            pageData.setMacEntries(macTable.toArray(new MacTableEntry[0]));
            return pageData;
        }
    } finally {
        if (ownTransaction) {
            TransactionElf.commit();
        }
    }
}

From source file:org.xerela.provider.telemetry.TelemetryProvider.java

License:Mozilla Public License

/** {@inheritDoc} */
@SuppressWarnings({ "unchecked", "nls" })
public ArpPageData getArpTable(ArpPageData pageData, String ipAddress, String managedNetwork) {
    ZDeviceCore device = getDevice(ipAddress, managedNetwork);
    if (device == null) {
        pageData.setArpEntries(new ArpTableEntry[0]);
        pageData.setTotal(0);//from w w  w  .  j av  a 2  s  .c  om
        return pageData;
    }

    boolean ownTransaction = TransactionElf.beginOrJoinTransaction();
    try {
        Session session = TelemetryActivator.getSessionFactory().getCurrentSession();
        String fromClause = "FROM discovery_arp arp WHERE arp.device_id = " + device.getDeviceId();
        SQLQuery query = session.createSQLQuery(
                "SELECT ip_address, mac_address, interface " + fromClause + " ORDER BY ip_address");
        query.addScalar("ip_address", Hibernate.STRING);
        query.addScalar("mac_address", Hibernate.LONG);
        query.addScalar("interface", Hibernate.STRING);
        query.setFirstResult(pageData.getOffset()).setMaxResults(pageData.getPageSize());
        query.scroll(ScrollMode.SCROLL_INSENSITIVE);
        List<Object[]> resultList = (List<Object[]>) query.list();
        if (resultList == null || resultList.isEmpty()) {
            pageData.setArpEntries(new ArpTableEntry[0]);
            pageData.setTotal(0);
            return pageData;
        } else {
            if (pageData.getOffset() == 0) {
                // Set the total result size into the page data.
                query = session.createSQLQuery("SELECT count(arp.ip_address) " + fromClause);
                pageData.setTotal(getCount(query));
            }

            List<ArpTableEntry> arpTable = new ArrayList<ArpTableEntry>();
            for (Object[] resultEntry : resultList) {
                ArpTableEntry entry = new ArpTableEntry();
                entry.setIpAddress((String) resultEntry[0]);
                entry.setMacAddress((Long) resultEntry[1]);
                entry.setInterfaceName((String) resultEntry[2]);
                arpTable.add(entry);
            }
            pageData.setArpEntries(arpTable.toArray(new ArpTableEntry[0]));
            return pageData;
        }
    } finally {
        if (ownTransaction) {
            TransactionElf.commit();
        }
    }
}

From source file:org.xerela.provider.telemetry.TelemetryProvider.java

License:Mozilla Public License

/**
 * {@inheritDoc}/*ww w .  java  2  s.co  m*/
 */
@SuppressWarnings({ "unchecked", "nls" })
public DeviceArpPageData getArpEntries(DeviceArpPageData pageData, String networkAddress, String sort,
        boolean descending) {
    StringBuilder selectClause = new StringBuilder(
            "SELECT d.ip_address as device, d.network as managedNetwork, arp.device_id as device_id, arp.ip_address as ipAddress, arp.mac_address as macAddress, arp.interface as interfaceName");
    StringBuilder fromClause = new StringBuilder(
            " FROM discovery_arp arp LEFT JOIN device d on arp.device_id = d.device_id");
    if (networkAddress.indexOf('/') > 0) {
        Long[] hiLoRange = NetworkAddressElf.getHiLoRange(networkAddress);
        if (hiLoRange[0] == null) {
            if (NetworkAddressElf.isIPv6AddressOrMask(networkAddress)) {
                String[] ipAndCidr = networkAddress.split("/");
                long[] hiLo = NetworkAddressElf.getHiLo(ipAndCidr[0]);
                fromClause.append(String.format(" WHERE arp.ip_low BETWEEN %d AND %d AND arp.ip_high=%d",
                        hiLoRange[2], hiLoRange[1], hiLo[0]));
            } else {
                fromClause.append(
                        String.format(" WHERE arp.ip_low BETWEEN %d AND %d", hiLoRange[2], hiLoRange[1]));
            }
        } else {
            fromClause
                    .append(String.format(" WHERE arp.ip_high BETWEEN %d AND %d", hiLoRange[1], hiLoRange[0]));
        }
    } else {
        long[] hiLo = NetworkAddressElf.getHiLo(networkAddress);
        fromClause.append(String.format(" WHERE arp.ip_high=%d AND arp.ip_low=%d", hiLo[0], hiLo[1]));
    }

    selectClause.append(fromClause);
    if (sort != null) {
        selectClause.append(" ORDER BY ").append(sort);
        if (descending) {
            selectClause.append(" DESC");
        }
    }

    boolean ownTransaction = TransactionElf.beginOrJoinTransaction();
    try {
        Session session = TelemetryActivator.getSessionFactory().getCurrentSession();
        SQLQuery query = session.createSQLQuery(selectClause.toString());
        query.addScalar("device", Hibernate.STRING);
        query.addScalar("managedNetwork", Hibernate.STRING);
        query.addScalar("device_id", Hibernate.INTEGER);
        query.addScalar("ipAddress", Hibernate.STRING);
        query.addScalar("macAddress", Hibernate.LONG);
        query.addScalar("interfaceName", Hibernate.STRING);

        query.setFirstResult(pageData.getOffset()).setMaxResults(pageData.getPageSize());
        query.scroll(ScrollMode.SCROLL_INSENSITIVE);
        List<Object[]> resultList = (List<Object[]>) query.list();
        if (resultList == null || resultList.isEmpty()) {
            pageData.setArpEntries(new DeviceArpTableEntry[0]);
            pageData.setTotal(0);
            return pageData;
        } else {
            if (pageData.getOffset() == 0) {
                // Set the total result size into the page data.
                query = session.createSQLQuery("SELECT count(arp.ip_address) " + fromClause.toString());
                pageData.setTotal(getCount(query));
            }

            List<DeviceArpTableEntry> arpTable = new ArrayList<DeviceArpTableEntry>();
            for (Object[] resultEntry : resultList) {
                DeviceArpTableEntry entry = new DeviceArpTableEntry();
                entry.setDevice((String) resultEntry[0]);
                entry.setManagedNetwork((String) resultEntry[1]);
                entry.setDeviceId((Integer) resultEntry[2]);
                entry.setIpAddress((String) resultEntry[3]);
                entry.setMacAddress((Long) resultEntry[4]);
                entry.setInterfaceName((String) resultEntry[5]);
                arpTable.add(entry);
            }
            pageData.setArpEntries(arpTable.toArray(new DeviceArpTableEntry[0]));
            return pageData;
        }
    } finally {
        if (ownTransaction) {
            TransactionElf.commit();
        }
    }
}

From source file:org.xerela.provider.telemetry.TelemetryProvider.java

License:Mozilla Public License

/**
 * Retrieve neighbors from the routing table
 * @param neighborList the list of neighbors so far
 * @param session//from ww  w  .ja  v  a2  s .  co  m
 * @param device 
 */
@SuppressWarnings({ "unchecked", "nls" })
private void addRoutingNeighbors(List<Neighbor> neighborList, Session session, ZDeviceCore device) {
    SQLQuery query = session.createSQLQuery(
            "SELECT protocol, remote_ip_address, router_id_ip_address, interface FROM discovery_routing WHERE device_id="
                    + device.getDeviceId() + " ORDER by remote_ip_address");
    query.addScalar("protocol", Hibernate.STRING);
    query.addScalar("remote_ip_address", Hibernate.STRING);
    query.addScalar("router_id_ip_address", Hibernate.STRING);
    query.addScalar("interface", Hibernate.STRING);
    List<Object[]> resultList = (List<Object[]>) query.list();
    for (Object[] resultEntry : resultList) {
        Neighbor neighbor = new Neighbor();
        neighbor.setProtocol((String) resultEntry[0]);
        neighbor.setIpAddress((String) resultEntry[1]);
        neighbor.setOtherId(NetworkAddressElf.fromDatabaseString((String) resultEntry[2]));
        neighbor.setLocalInterface((String) resultEntry[3]);
        neighborList.add(neighbor);
    }
}

From source file:org.xerela.provider.telemetry.TelemetryProvider.java

License:Mozilla Public License

/**
 * Add discovery protocol neighbors//from www .j  ava2 s  . c  o m
 * @param neighborList
 * @param session
 * @param device
 */
@SuppressWarnings({ "unchecked", "nls" })
private void addDiscoveryProtocolNeighbors(List<Neighbor> neighborList, Session session, ZDeviceCore device) {
    SQLQuery query = session.createSQLQuery(
            "SELECT protocol, ip_address, local_interface, remote_interface, sys_name FROM discovery_xdp WHERE device_id="
                    + device.getDeviceId() + " ORDER by ip_address");
    query.addScalar("protocol", Hibernate.STRING);
    query.addScalar("ip_address", Hibernate.STRING);
    query.addScalar("local_interface", Hibernate.STRING);
    query.addScalar("remote_interface", Hibernate.STRING);
    query.addScalar("sys_name", Hibernate.STRING);
    List<Object[]> resultList = (List<Object[]>) query.list();
    for (Object[] resultEntry : resultList) {
        Neighbor neighbor = new Neighbor();
        neighbor.setProtocol((String) resultEntry[0]);
        neighbor.setIpAddress((String) resultEntry[1]);
        neighbor.setLocalInterface((String) resultEntry[2]);
        neighbor.setRemoteInterface((String) resultEntry[3]);
        neighbor.setOtherId((String) resultEntry[4]);
        neighborList.add(neighbor);
    }
}

From source file:org.xerela.provider.telemetry.TelemetryProvider.java

License:Mozilla Public License

/**
 * Find the best matching MAC forwarding entry for the provided MAC address
 * @param targetMacAddress what to look for
 * @return/*  ww  w  . j av  a2  s .c  o  m*/
 */
@SuppressWarnings({ "unchecked", "nls" })
private DeviceMacTableEntry findMacTableEntry(MACAddress targetMacAddress) {
    boolean ownTransaction = TransactionElf.beginOrJoinTransaction();
    try {
        Session session = TelemetryActivator.getSessionFactory().getCurrentSession();
        String queryString = "SELECT d.ip_address as device, d.network as net, m.device_id as device_id, m.mac_address as mac, m.interface as interface, m.vlan as vlan FROM discovery_mac m LEFT JOIN device d on m.device_id = d.device_id where m.mac_address="
                + targetMacAddress.getMacLong();
        SQLQuery query = session.createSQLQuery(queryString);
        query.addScalar("device", Hibernate.STRING);
        query.addScalar("net", Hibernate.STRING);
        query.addScalar("device_id", Hibernate.INTEGER);
        query.addScalar("mac", Hibernate.LONG);
        query.addScalar("interface", Hibernate.STRING);
        query.addScalar("vlan", Hibernate.STRING);
        List<Object[]> resultList = (List<Object[]>) query.list();
        List<DeviceMacTableEntry> possibleMatches = new ArrayList<DeviceMacTableEntry>();
        for (Object[] resultEntry : resultList) {
            DeviceMacTableEntry entry = new DeviceMacTableEntry();
            entry.setDevice((String) resultEntry[0]);
            entry.setManagedNetwork((String) resultEntry[1]);
            entry.setDeviceId((Integer) resultEntry[2]);
            entry.setMacAddress((Long) resultEntry[3]);
            entry.setPort((String) resultEntry[4]);
            entry.setVlan((String) resultEntry[5]);
            possibleMatches.add(entry);
        }

        int portMacCount = 0;
        DeviceMacTableEntry leader = null;
        for (DeviceMacTableEntry macEntry : possibleMatches) {
            int frequency = getMacFrequency(macEntry);
            if (leader == null || portMacCount > frequency) {
                portMacCount = frequency;
                leader = macEntry;
            }
        }
        return leader;
    } finally {
        if (ownTransaction) {
            TransactionElf.commit();
        }
    }
}

From source file:org.xerela.provider.telemetry.TelemetryProvider.java

License:Mozilla Public License

/**
 * Gets the MAC address for the {@link IPAddress} provided using the discovery_xdp table.
 * This should be called when working with an IPv6 address as NDP neighbors can be in the X
 * @param ipAddress//from  w  w  w  .  j av a  2s  .  co m
 * @return
 */
@SuppressWarnings({ "unchecked", "nls" })
private DeviceArpTableEntry findNdpEntry(IPAddress ipAddress) {
    long[] hiLo = ipAddress.getHiLo();
    Session session = TelemetryActivator.getSessionFactory().getCurrentSession();
    String queryString = "SELECT d.ip_address as device, d.network as net, x.device_id as device_id, x.ip_address as ip, x.mac_address as mac, x.local_interface as interface";
    String fromClause = String.format(
            " FROM discovery_xdp x LEFT JOIN device d on x.device_id = d.device_id WHERE x.ip_high=%d AND x.ip_low=%d AND x.protocol='NDP'",
            hiLo[0], hiLo[1]);

    SQLQuery query = session.createSQLQuery(queryString + fromClause);
    query.addScalar("device", Hibernate.STRING);
    query.addScalar("net", Hibernate.STRING);
    query.addScalar("device_id", Hibernate.INTEGER);
    query.addScalar("ip", Hibernate.STRING);
    query.addScalar("mac", Hibernate.LONG);
    query.addScalar("interface", Hibernate.STRING);

    List<Object[]> resultList = (List<Object[]>) query.list();
    // pick the first one, which there should only be one anyways
    for (Object[] resultEntry : resultList) {
        DeviceArpTableEntry entry = new DeviceArpTableEntry();
        entry.setDevice((String) resultEntry[0]);
        entry.setManagedNetwork((String) resultEntry[1]);
        entry.setDeviceId((Integer) resultEntry[2]);
        entry.setIpAddress((String) resultEntry[3]);
        entry.setMacAddress((Long) resultEntry[4]);
        entry.setInterfaceName((String) resultEntry[5]);
        return entry;
    }
    return null;
}

From source file:oscar.appt.status.dao.hibernate.AppointmentStatusDAOHibernate.java

License:Open Source License

public int checkStatusUsuage(List allStatus) {
    int iUsuage = 0;
    SQLQuery query = null;
    AppointmentStatus apptStatus = null;
    String sql = null;/*  w  ww.  j a va 2s.c om*/
    for (int i = 0; i < allStatus.size(); i++) {
        apptStatus = (AppointmentStatus) allStatus.get(i);
        if (apptStatus.getActive() == 1)
            continue;
        sql = "select count(*) as total from appointment where status like" + "'" + apptStatus.getStatus()
                + "%' ";
        sql = sql + "collate latin1_general_cs";
        query = getSession().createSQLQuery(sql);
        query.addScalar("total", Hibernate.INTEGER);
        iUsuage = (Integer) query.uniqueResult();
        if (iUsuage > 0) {
            iUsuage = i;
            break;
        }
    }
    return iUsuage;
}

From source file:ubic.gemma.persistence.service.analysis.expression.diff.DifferentialExpressionResultDaoImpl.java

License:Apache License

@Override
public List<Double> findGeneInResultSets(Gene gene, ExpressionAnalysisResultSet resultSet,
        Collection<Long> arrayDesignIds, Integer limit) {

    StopWatch timer = new StopWatch();
    timer.start();/* w ww . j ava2s  .  c o m*/

    List<Double> results;

    Session session = this.getSessionFactory().getCurrentSession();
    org.hibernate.SQLQuery queryObject = session
            .createSQLQuery(DifferentialExpressionResultDaoImpl.fetchResultsByResultSetAndGeneQuery);

    queryObject.setLong("gene_id", gene.getId());
    queryObject.setLong("rs_id", resultSet.getId());

    if (limit != null) {
        queryObject.setMaxResults(limit);
    }

    queryObject.addScalar("CORRECTED_PVALUE", new DoubleType());
    //noinspection unchecked
    results = queryObject.list();

    timer.stop();
    if (AbstractDao.log.isDebugEnabled())
        AbstractDao.log.debug("Fetching probeResults from resultSet " + resultSet.getId() + " for gene "
                + gene.getId() + "and " + arrayDesignIds.size() + "arrays took : " + timer.getTime() + " ms");

    return results;
}