Example usage for org.hibernate SQLQuery scroll

List of usage examples for org.hibernate SQLQuery scroll

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. scroll

Introduction

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

Prototype

ScrollableResults scroll(ScrollMode scrollMode);

Source Link

Document

Return the query results as ScrollableResults.

Usage

From source file:com.aw.core.dao.DAOHbm.java

License:Open Source License

public List listAbortable(SQLQuery sqlQuery) {
    ScrollableResults ss = sqlQuery.scroll(ScrollMode.FORWARD_ONLY);
    AWQueryAbortable queryAbortable = AWQueryAbortable.instance();
    List results = new ArrayList(AWQueryAbortable.DEF_LIST_SIZE);
    queryAbortable.resetRowCount();/*from   w  w w. j ava  2  s . c  o  m*/
    while (ss.next()) {
        if (queryAbortable.isAborted())
            break;
        results.add(ss.get());
        queryAbortable.incRowCount();
    }
    return results;
}

From source file:edu.scripps.fl.pubchem.app.AssayDownloader.java

License:Apache License

protected Set<Long> getAIDsfromLocalDB() {
    SQLQuery query = PubChemDB.getSession().createSQLQuery("select assay_aid from pcassay");
    ScrollableResults scroll = query.scroll(ScrollMode.FORWARD_ONLY);
    Iterator<Long> iterator = new ScrollableResultsIterator<Long>(Long.class, scroll);
    Set<Long> set = new HashSet();
    while (iterator.hasNext())
        set.add(iterator.next());/*  w ww.  j  a  v  a 2 s .  c  o  m*/
    return set;
}

From source file:edu.scripps.fl.pubchem.app.AssayDownloader.java

License:Apache License

public void process() throws Exception {
    if (notInDb) {
        SQLQuery query = PubChemDB.getSession().createSQLQuery("select assay_aid from pcassay");
        ScrollableResults scroll = query.scroll(ScrollMode.FORWARD_ONLY);
        Iterator<Long> iterator = new ScrollableResultsIterator<Long>(Long.class, scroll);
        Set<Long> aids = PubChemFactory.getInstance().getAIDs();
        while (iterator.hasNext())
            aids.remove(iterator.next());
        process(aids);/* w  ww .j  a  v a  2 s .  co  m*/
    } else if (this.mlpcn) {
        Set<Long> aids = PubChemFactory.getInstance()
                .getAIDs("\"NIH Molecular Libraries Program\"[SourceCategory] OR \"hasonhold\"[Filter]");
        //         for(long id = 1; id < 1788; id++)
        //            aids.remove(id);
        process(aids);
    } else if (this.days != null)
        process(PubChemFactory.getInstance().getAIDs(this.days));
    else
        process(PubChemFactory.getInstance().getAIDs());
}

From source file:edu.scripps.fl.pubchem.app.CIDDownloader.java

License:Apache License

public static void main(String[] args) throws Exception {
    CIDDownloader fetcher = new CIDDownloader();

    CommandLineHandler clh = new CommandLineHandler() {
        public void configureOptions(Options options) {
            options.addOption(OptionBuilder.withLongOpt("input_file").withType("").withValueSeparator('=')
                    .hasArg().create());
            options.addOption(OptionBuilder.withLongOpt("output_file").withType("").withValueSeparator('=')
                    .hasArg().isRequired().create());
        }//w  w  w  .  j  ava 2  s.  c  o m
    };
    args = clh.handle(args);
    String inputFile = clh.getCommandLine().getOptionValue("input_file");
    String outputFile = clh.getCommandLine().getOptionValue("output_file");

    fetcher.setOutputFile(outputFile);
    Iterator<?> iterator;
    if (null == inputFile) {
        if (args.length == 0) {
            log.info("Running query to find CIDs in PCAssayResult but not in PCCompound");
            SQLQuery query = PubChemDB.getSession().createSQLQuery(
                    "select distinct r.cid from pcassay_result r left join pccompound c on r.cid = c.cid where (r.cid is not null and r.cid > 0 ) and c.cid is null order by r.cid");
            ScrollableResults scroll = query.scroll(ScrollMode.FORWARD_ONLY);
            iterator = new ScrollableResultsIterator<Integer>(Integer.class, scroll);
        } else {
            iterator = Arrays.asList(args).iterator();
        }
    } else if ("-".equals(inputFile)) {
        log.info("Reading CIDs (one per line) from STDIN");
        iterator = new LineIterator(new InputStreamReader(System.in));
    } else {
        log.info("Reading CIDs (one per line) from " + inputFile);
        iterator = new LineIterator(new FileReader(inputFile));
    }
    fetcher.process(iterator);
    System.exit(0);
}

From source file:nl.strohalm.cyclos.dao.accounts.AccountDAOImpl.java

License:Open Source License

public IteratorList<AccountDailyDifference> iterateDailyDifferences(final MemberAccount account,
        final Period period) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("accountId", account.getId());
    QueryParameter beginParameter = HibernateHelper.getBeginParameter(period);
    QueryParameter endParameter = HibernateHelper.getEndParameter(period);
    if (beginParameter != null) {
        params.put("begin", beginParameter.getValue());
    }//from ww  w .  jav a  2s  . c  o  m
    if (endParameter != null) {
        params.put("end", endParameter.getValue());
    }
    StringBuilder sql = new StringBuilder();
    sql.append(" select type, date(d.date) as date, sum(amount) as amount ");
    sql.append(" from ( ");
    sql.append("     select 'B' as type, t.process_date as date, ");
    sql.append("         case when t.chargeback_of_id is null then ");
    sql.append("             case when t.from_account_id = :accountId then -t.amount else t.amount end ");
    sql.append("         else ");
    sql.append("             case when t.to_account_id = :accountId then t.amount else -t.amount end ");
    sql.append("         end as amount ");
    sql.append("      from transfers t ");
    sql.append("      where (t.from_account_id = :accountId or t.to_account_id = :accountId) ");
    sql.append("      and t.process_date is not null ");
    if (beginParameter != null) {
        sql.append("  and t.process_date " + beginParameter.getOperator() + " :begin");
    }
    if (endParameter != null) {
        sql.append("  and t.process_date " + endParameter.getOperator() + " :end");
    }
    sql.append("      union ");
    sql.append("      select 'R', r.date, r.amount ");
    sql.append("      from amount_reservations r ");
    sql.append("      where r.account_id = :accountId ");
    if (beginParameter != null) {
        sql.append("  and r.date " + beginParameter.getOperator() + " :begin");
    }
    if (endParameter != null) {
        sql.append("  and r.date " + endParameter.getOperator() + " :end");
    }
    sql.append(" ) d ");
    sql.append(" group by type, date(d.date) ");
    sql.append(" order by date(d.date) ");
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.addScalar("type", StandardBasicTypes.STRING);
    query.addScalar("date", StandardBasicTypes.CALENDAR_DATE);
    query.addScalar("amount", StandardBasicTypes.BIG_DECIMAL);
    getHibernateQueryHandler().setQueryParameters(query, params);
    ScrollableResults results = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
    return new IteratorListImpl<AccountDailyDifference>(new DiffsIterator(results));
}

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);//  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_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);/*w  ww. ja  va  2s  .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}//from  www .j  av  a 2 s. c  o 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:ubic.gemma.persistence.service.association.phenotype.PhenotypeAssociationDaoImpl.java

License:Apache License

/**
 * find category terms currently used in the database by evidence
 */// w w w .  j a  va 2  s. c  o m
@Override
public Collection<CharacteristicValueObject> findEvidenceCategoryTerms() {

    Collection<CharacteristicValueObject> mgedCategory = new TreeSet<>();

    String queryString = "SELECT DISTINCT CATEGORY_URI, category FROM PHENOTYPE_ASSOCIATION "
            + "JOIN INVESTIGATION ON PHENOTYPE_ASSOCIATION.EXPERIMENT_FK = INVESTIGATION.ID "
            + "JOIN CHARACTERISTIC ON CHARACTERISTIC.INVESTIGATION_FK= INVESTIGATION.ID";
    org.hibernate.SQLQuery queryObject = this.getSessionFactory().getCurrentSession()
            .createSQLQuery(queryString);

    ScrollableResults results = queryObject.scroll(ScrollMode.FORWARD_ONLY);
    while (results.next()) {

        CharacteristicValueObject characteristicValueObject = new CharacteristicValueObject(-1L);
        characteristicValueObject.setCategoryUri((String) results.get(0));
        characteristicValueObject.setCategory((String) results.get(1));
        mgedCategory.add(characteristicValueObject);
    }
    results.close();

    return mgedCategory;
}

From source file:ubic.gemma.persistence.service.association.phenotype.PhenotypeAssociationDaoImpl.java

License:Apache License

/**
 * return the list of the owners that have evidence in the system
 *///from  w ww .j av  a  2 s . c o  m
@Override
public Collection<String> findEvidenceOwners() {

    Set<String> owners = new HashSet<>();

    String sqlQuery = "SELECT DISTINCT sid.PRINCIPAL FROM ACLOBJECTIDENTITY aoi JOIN ACLENTRY ace ON ace.OBJECTIDENTITY_FK = "
            + "aoi.ID JOIN ACLSID sid ON sid.ID = aoi.OWNER_SID_FK WHERE aoi.OBJECT_CLASS " + "IN  "
            + PhenotypeAssociationDaoImpl.DISCRIMINATOR_CLAUSE;

    SQLQuery queryObject = this.getSessionFactory().getCurrentSession().createSQLQuery(sqlQuery);

    ScrollableResults results = queryObject.scroll(ScrollMode.FORWARD_ONLY);

    while (results.next()) {
        String owner = (String) results.get(0);
        owners.add(owner);
    }

    return owners;
}