Example usage for org.hibernate SQLQuery list

Introduction

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

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

From source file:ca.myewb.controllers.actions.csv.RenewalList.java

License:Open Source License

public void handle(Context ctx) throws Exception {
    if (urlParams.getParam() == null && !currentUser.isAdmin()) {
        throw getSecurityException("You don't have the right permissions to view a full upgrades list!",
                path + "/chapter/ChapterList");
    }//ww w .j  ava 2  s .co  m

    GroupModel chapter = null;

    if (urlParams.getParam() != null) {
        chapter = (GroupModel) getAndCheckFromUrl(GroupModel.class);

        if (!Permissions.canAdministerGroupMembership(currentUser, chapter)) {
            throw getSecurityException("You don't have the right permissions to view an upgrades list!",
                    path + "/chapter/ChapterInfo/" + chapter.getId());
        }
    }

    Vector<String[]> csvData = new Vector<String[]>();
    csvData.add(new String[] { "Upgrade Date", "First name", "Last name", "Email", "Phone", "Address1", "Suite",
            "Address2", "City", "Province", "Postal Code", "Country", "Renewal?", "Student?", "Upgraded by",
            "Language" });
    String query = "select " + "r.start, " + "u.firstname, " + "u.lastname, " + "u.email, " + "u.phone, "
            + "u.address1, " + "u.suite, " + "u.address2, " + "u.city, " + "u.province, " + "u.postalcode, "
            + "u.country, " + "(case when prev.id is null then 'no' else 'yes' end) as renewal, "
            + "(case when u.student = 'n' then 'no' else 'yes' end) as student, "
            + "(case when u.upgradelevel = 'n' then 'National' when u.upgradelevel='c' then 'Chapter' else 'Unknown' end) as upgradedby, "
            + "(case when u.language = 'fr' then 'fr' else 'en' end) as language " + "from users u "
            + "left join roles prev on prev.userid = u.id and prev.end is not null and prev.groupid=6 "
            + "join roles r on r.userid=u.id and r.groupid=6 and r.end is null ";

    if (chapter != null) {
        query += "join roles chapter on chapter.userid=u.id and chapter.groupid=? and chapter.end is null and chapter.level='m' ";
    }

    query += "group by u.id order by r.start desc limit 0, 800";

    SQLQuery hibQuery = hibernateSession.createSQLQuery(query).addScalar("start", Hibernate.STRING)
            .addScalar("firstname", Hibernate.STRING).addScalar("lastname", Hibernate.STRING)
            .addScalar("email", Hibernate.STRING).addScalar("phone", Hibernate.STRING)
            .addScalar("address1", Hibernate.STRING).addScalar("suite", Hibernate.STRING)
            .addScalar("address2", Hibernate.STRING).addScalar("city", Hibernate.STRING)
            .addScalar("province", Hibernate.STRING).addScalar("postalcode", Hibernate.STRING)
            .addScalar("country", Hibernate.STRING).addScalar("renewal", Hibernate.STRING)
            .addScalar("student", Hibernate.STRING).addScalar("upgradedby", Hibernate.STRING)
            .addScalar("language", Hibernate.STRING);

    if (chapter != null) {
        hibQuery.setInteger(0, chapter.getId());
    }

    List memberList = hibQuery.list();

    Iterator members = memberList.iterator();

    while (members.hasNext()) {
        csvData.add(toStringArray((Object[]) members.next()));
    }

    try {
        this.setInterpageVar("csvData", csvData);
        this.setInterpageVar("csvFileName", "upgradeslist.csv");
    } catch (IllegalStateException e) {
        //session timeout!
        throw new IllegalStateException("Session timeout on CSV!", e);
    }

    throw new RedirectionException(path + "/csvfile/upgradeslist.csv");
}

From source file:celepsa.rrcc.da.StakeholderDA.java

public Tmstakepersona obtenerDocumento(Tmstakepersona objStakeholder) throws Exception {
    logger.debug("obtenerDocumento");
    try {//from  ww  w . j a va2 s  .  c  om
        String sQuery = " from Tmstakepersona WHERE id = :id ";

        SQLQuery query = session.createSQLQuery(sQuery);
        query.setInteger("id", (objStakeholder.getId()));
        return (Tmstakepersona) query.list().get(0);

    } catch (Exception e) {
        e.printStackTrace();
        throw e;
    }
}

From source file:ch.algotrader.dao.AbstractDaoTest.java

License:Open Source License

@Test
public void testFindBySQLQueryAsEntity() throws Exception {

    GenericItem stuff1 = new GenericItem("this");
    stuff1.setActive(true);/*w  ww.j a  v a 2 s  . c  om*/
    stuff1.setBroker(Broker.DC.name());

    this.dao.save(stuff1);

    long id1 = stuff1.getId();

    Assert.assertNotEquals(0, id1);

    this.dao.flush();

    SQLQuery query = this.dao.prepareSQLQuery(null, "select s.* from GenericItem s where s.active = ?",
            Boolean.TRUE);
    query.addEntity(GenericItem.class);

    final List<?> list1 = query.list();

    Assert.assertNotNull(list1);

    Assert.assertEquals(1, list1.size());

    GenericItem stuff = (GenericItem) list1.get(0);

    Assert.assertSame(stuff, stuff1);
}

From source file:ch.algotrader.dao.trade.OrderStatusDaoImpl.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override//w ww  .  j  a v  a 2s  .  c  o  m
public List<OrderStatus> findPending() {

    SQLQuery query = (SQLQuery) prepareQuery(null, "OrderStatus.findPending", QueryType.BY_NAME);
    query.addEntity(OrderStatusImpl.class);
    return query.list();
}

From source file:ch.astina.hesperid.dao.hibernate.AssetDAOHibernate.java

License:Apache License

@SuppressWarnings("unchecked")
public List<Asset> getAllRelatedAssets(Asset asset) {
    List<Asset> assets = new ArrayList<Asset>();

    String sql = "SELECT a.* FROM client_hierarchy ch INNER JOIN asset a ON a.id = ch.first_asset  WHERE first_asset = ?";
    SQLQuery query = session.createSQLQuery(sql);
    query.setLong(0, asset.getId());/*from  ww  w  .j  a v a2 s  .  c  o  m*/
    query.addEntity(Asset.class);

    assets.addAll(query.list());

    sql = "SELECT a.* FROM client_hierarchy ch INNER JOIN asset a ON a.id = ch.first_asset  WHERE second_asset = ?";
    query = session.createSQLQuery(sql);
    query.setLong(0, asset.getId());
    query.addEntity(Asset.class);

    assets.addAll(query.list());

    return assets;
}

From source file:cn.edu.xjtu.manage.dao.LicenseDao.java

License:Open Source License

public List<License> getLicense() {
    SQLQuery q = session.createSQLQuery("SELECT t.id,t.license,t.expires,t.isvalid FROM t_license t ");
    q.setFirstResult(0).setMaxResults(1);
    List l = q.list();
    List<License> re = new ArrayList<License>();
    for (int i = 0; i < l.size(); i++) {
        Object[] row = (Object[]) l.get(i);
        ;//w  w w .j  a  v  a  2s .co m
        Integer id = (Integer) row[0];
        String licensestr = (String) row[1];
        Date expires = (Date) row[2];
        int isvalid = (Integer) row[3];
        License license = new License(id, licensestr, expires, isvalid);
        re.add(license);
    }
    return re;
}

From source file:cn.trymore.core.dao.impl.DAOGenericImpl.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Object> findListByNativeSQL(final String nativeSql) throws DAOException {
    SQLQuery query = getSession().createSQLQuery(nativeSql);
    // query.setCacheable(true);
    return query.list();
}

From source file:cognition.common.data.BaseDao.java

License:Apache License

public List getSQLResultFromSource(String sqlQuery) {
    SessionWrapper sessionWrapper = createSourceSession();
    try {/*from  ww  w . j a  v a  2s . c  om*/
        SQLQuery query = sessionWrapper.createSQLQuery(sqlQuery);
        return query.list();
    } finally {
        sessionWrapper.closeSession();
    }
}

From source file:cognition.common.data.BaseDao.java

License:Apache License

public List getSQLResultFromTarget(String sqlQuery) {
    SessionWrapper sessionWrapper = createTargetSession();
    try {/*from  w w  w  . j ava 2s. co  m*/
        SQLQuery query = sessionWrapper.createSQLQuery(sqlQuery);
        return query.list();
    } finally {
        sessionWrapper.closeSession();
    }
}

From source file:cognition.common.data.BaseDaoTest.java

License:Apache License

@Test
public void shouldCreateTable() {
    Session session = baseDao.getSourceSessionFactory().openSession();
    SQLQuery sqlQuery = session.createSQLQuery("create table TestTable(id int)");
    sqlQuery.executeUpdate();//from w w w .j a va 2  s. c o  m

    session.createSQLQuery("insert into TestTable values(1)").executeUpdate();
    session.createSQLQuery("insert into TestTable values(2)").executeUpdate();

    SQLQuery selectQuery = session.createSQLQuery("select * from TestTable");
    List list = selectQuery.list();

    assertThat(list.size(), equalTo(2));
}