Example usage for org.hibernate SQLQuery addEntity

List of usage examples for org.hibernate SQLQuery addEntity

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

Introduction

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

Prototype

SQLQuery<T> addEntity(Class entityType);

Source Link

Document

Declare a "root" entity, without specifying an alias.

Usage

From source file:org.jcvi.ometa.hibernate.dao.SampleDAO.java

License:Open Source License

public List<Sample> getAllSamples(String projectIds, String attributeNames, String sSearch, String sortType,
        String sortCol, String sortDir, Session session) throws DAOException {
    List<Sample> sampleList = new ArrayList<Sample>();
    String defaultAttributes[] = { Constants.ATTR_PROJECT_NAME, Constants.ATTR_SAMPLE_NAME,
            Constants.ATTR_PARENT_SAMPLE_NAME };

    try {/*from w w  w  . j  a v  a 2 s  .c  o m*/
        List results = null;
        boolean isInt = (sSearch != null && Pattern.compile("\\d+").matcher(sSearch).matches());
        boolean isSearch = (sSearch != null && !sSearch.isEmpty());
        boolean isSort = (sortCol != null && !sortCol.isEmpty() && sortDir != null && !sortDir.isEmpty());

        String sql = null;
        String sub_sql = null;
        String sql_s_default = "select #selector# "
                + "  from project p left join project p_1 on p.projet_projet_parent_id=p_1.projet_id "
                + "    left join sample s on p.projet_id=s.sample_projet_id left join sample s_1 on s.sample_id=s_1.sample_id"
                + "  where p.projet_id in (#projectIds#) #opt# ";

        String sql_p = "select #selector# #p_attr#"
                + "  from sample s left join project p on s.sample_projet_id=p.projet_id "
                + "    left join project p_1 on p.projet_projet_parent_id=p_1.projet_id "
                + "    left join project_attribute pa on p.projet_id=pa.projea_projet_id "
                + "    left join lookup_value lv on pa.projea_lkuvlu_attribute_id=lv.lkuvlu_id "
                + "  where p.projet_id in (#projectIds#) and #p_opt# ";
        String sql_p_attr = " ,CONCAT(IFNULL(pa.projea_attribute_str''),',',IFNULL(pa.projea_attribute_date,''),',',IFNULL(pa.projea_attribute_int,'')) attr ";
        String sql_p_wsearch = " ( "
                + "    p.projet_name like #sSearch# or p_1.projet_name like #sSearch# or ( "
                + "      (pa.projea_attribute_str like #sSearch# "
                + "        or date(pa.projea_attribute_date) like #sSearch# "
                + (isInt ? " or pa.projea_attribute_int=#i_sSearch# " : "")
                + ") and lv.lkuvlu_name in (#attributes#) " + "    ) " + "  ) ";

        String sql_s = "select #selector# #s_attr# "
                + "  from sample s left join sample s_1 on s.sample_sample_parent_id=s_1.sample_id "
                + "    left join project p on s.sample_projet_id=p.projet_id "
                + "    left join sample_attribute sa on s.sample_id=sa.sampla_sample_id "
                + "    left join lookup_value lv on sa.sampla_lkuvlu_attribute_id=lv.lkuvlu_id "
                + "  where p.projet_id in (#projectIds#) and #s_opt# ";
        String sql_s_attr = " , CONCAT(IFNULL(sa.sampla_attribute_str,''),',',IFNULL(sa.sampla_attribute_date,''),',',IFNULL(sa.sampla_attribute_int,'')) attr ";
        String sql_s_wsearch = " ( " + "   s.sample_name like #sSearch# or s_1.sample_name like #sSearch# or ( "
                + "     (sa.sampla_attribute_str like #sSearch# "
                + "       or date(sa.sampla_attribute_date) like #sSearch# "
                + (isInt ? "or sa.sampla_attribute_int=#i_sSearch# " : "") + ")"
                + "     and lv.lkuvlu_name in (#attributes#) " + "   ) " + " ) ";

        String sql_e = "select #selector# #e_attr# " + "  from " + "    (select e1.* from event e1 "
                + "       where e1.event_projet_id in (#projectIds#) " + "         and e1.event_create_date=( "
                + "           select max(e2.event_create_date) from event e2 "
                + "             where e1.event_projet_id=e2.event_projet_id and e1.event_sampl_id=e2.event_sampl_id and e1.event_type_lkuvl_id=e2.event_type_lkuvl_id "
                + "             group by e2.event_type_lkuvl_id) "
                + "    ) as e left join sample s on e.event_sampl_id=s.sample_id "
                + "    left join event_attribute ea on e.event_id=ea.eventa_event_id "
                + "    left join lookup_value lv on ea.eventa_lkuvlu_attribute_id=lv.lkuvlu_id "
                + "  where #e_opt# ";
        String sql_e_attr = " , CONCAT(IFNULL(ea.eventa_attribute_str,''),',',IFNULL(ea.eventa_attribute_date,''),',',IFNULL(ea.eventa_attribute_int,'')) attr ";
        String sql_e_wsearch = " (ea.eventa_attribute_str like #sSearch# or date(ea.eventa_attribute_date) like #sSearch# "
                + (isInt ? " or ea.eventa_attribute_int=#i_sSearch# " : "")
                + ") and lv.lkuvlu_name in (#attributes#) ";

        String sql_wsort = " #sortOpt# and lv.lkuvlu_name in (#sortCol#) order by attr #sortDir# ";
        String sql_wsort_s = " s.sample_id in (#sampleIds#) ";
        String sql_wsort_p = " s.sample_projet_id in (#projectIds#)";

        if (isSearch) {
            sub_sql = sql_p.replaceFirst("#p_attr#", "").replaceFirst("#p_opt#", sql_p_wsearch);
            sub_sql += " union " + sql_s.replaceFirst("#s_attr#", "").replaceFirst("#s_opt#", sql_s_wsearch);
            sub_sql += " union " + sql_e.replaceFirst("#e_attr#", "").replaceFirst("#e_opt#", sql_e_wsearch);
            sub_sql = sub_sql.replaceAll("#sSearch#", "'%" + sSearch.toLowerCase().replaceAll("'", "''") + "%'")
                    .replaceAll("#i_sSearch#", sSearch).replaceAll("#attributes#",
                            "'" + attributeNames.replaceAll("'", "''").replaceAll(",", "','") + "'");
        }
        if (isSort) {
            String optSelector = "";
            List<String> defaults = Arrays.asList(defaultAttributes);
            if (defaults.contains(sortCol)) {
                String temp_sql = "";
                if (isSearch)
                    temp_sql = " and " + sql_wsort_s.replaceFirst("#sampleIds#",
                            sub_sql.replaceAll("#selector#", "s.sample_id"));
                temp_sql += " order by ";
                if (sortCol.equals(Constants.ATTR_PROJECT_NAME))
                    temp_sql += "p.projet_name ";
                else if (sortCol.equals(Constants.ATTR_SAMPLE_NAME))
                    temp_sql += "s.sample_name ";
                else if (sortCol.equals("Parent Project"))
                    temp_sql += "p_1.project_name ";
                else if (sortCol.equals(Constants.ATTR_PARENT_SAMPLE_NAME))
                    temp_sql += "s_1.sample_name ";
                sql = sql_s_default.replace("#opt#", temp_sql + " #sortDir# ");
            } else {
                if (sortType != null) {
                    if (sortType.equals("p")) {
                        sql = sql_p.replaceFirst("#p_attr#", sql_p_attr);
                        optSelector = "#p_opt#";
                    } else if (sortType.equals("s")) {
                        sql = sql_s.replaceFirst("#s_attr#", sql_s_attr);
                        optSelector = "#s_opt#";
                    } else if (sortType.equals("e")) {
                        sql = sql_e.replaceFirst("#e_attr#", sql_e_attr);
                        optSelector = "#e_opt#";
                    }
                }
                sql = sql.replaceAll(optSelector,
                        sql_wsort
                                .replaceFirst("#sortOpt#",
                                        isSearch ? sql_wsort_s.replaceFirst("#sampleIds#",
                                                sub_sql.replaceAll("#selector#", "s.sample_id")) : sql_wsort_p)
                                .replaceFirst("#sortCol#", "'" + sortCol + "'"));
            }

            sql = sql.replaceFirst("#sortDir#", sortDir);
        }
        sql = sql == null ? sub_sql : sql;
        sql = sql.replaceAll("#projectIds#", projectIds).replaceAll("#selector#", "s.*");

        SQLQuery query = session.createSQLQuery(sql);
        query.addEntity(Sample.class);
        results = query.list();

        if (results != null) {
            for (Object result : results) {
                sampleList.add((Sample) result);
            }
        }
    } catch (Exception ex) {
        throw new DAOException(ex);
    }

    return sampleList;
}

From source file:org.jessma.dao.hbn.HibernateFacade.java

License:Apache License

/**
 * //  www  . j a va 2  s  .c o m
 * ?SQL?EntitiesJoins
 * 
 * @param firstResult   : 
 * @param maxResults   : 
 * @param sql         : SQL ?
 * @param entities      : ??
 * @param joins         : ??
 * @param params      : ?
 * @return            : 
 * 
 */
protected <T> List<T> sqlQuery4(int firstResult, int maxResults, String sql, KV<String, Object>[] entities,
        KV<String, String>[] joins, Object... params) {
    SQLQuery sqlQuery = getSession().createSQLQuery(sql);

    for (int i = 0; i < params.length; i++)
        sqlQuery.setParameter(i, params[i]);

    if (entities != null) {
        for (int i = 0; i < entities.length; i++) {
            KV<String, Object> entity = entities[i];

            String key = entity.getKey();
            Object value = entity.getValue();
            Class<?> v1 = (value instanceof Class) ? (Class<?>) value : null;
            String v2 = v1 == null ? (String) value : null;

            if (key == null || key.length() == 0) {
                if (v1 != null)
                    sqlQuery.addEntity(v1);
                else
                    sqlQuery.addEntity(v2);
            } else {
                if (v1 != null)
                    sqlQuery.addEntity(key, v1);
                else
                    sqlQuery.addEntity(key, v2);
            }
        }
    }

    if (joins != null) {
        for (KV<String, String> join : joins)
            sqlQuery.addJoin(join.getKey(), (String) join.getValue());
    }

    if (firstResult > 0)
        sqlQuery.setFirstResult(firstResult);
    if (maxResults > 0)
        sqlQuery.setMaxResults(maxResults);

    return sqlQuery.list();
}

From source file:org.openiam.idm.srvc.qry.service.QueryDataServiceImpl.java

License:Open Source License

public List<Object> executeQuery(Query qry) {

    Session session = sessionFactory.getCurrentSession();

    SQLQuery sqlQry = session.createSQLQuery(qry.getSql());
    sqlQry.addEntity(qry.getObjectClass());

    // add the parameters for the query if they exists
    List<QueryParam> paramList = qry.getParamList();
    if (paramList != null && paramList.size() > 0) {
        for (QueryParam param : paramList) {
            if (param.getParamType() == ParameterTypeEnum.STRING) {
                System.out.println(" - Setting parameters in query service.");
                sqlQry.setString(param.getParamName(), (String) param.getParamValue());
            }/*from   ww  w  .  j ava 2s .  c  o m*/
        }
    }

    if (qry.getMaxRowCount() != 0) {
        sqlQry.setFetchSize(qry.getMaxRowCount());
        sqlQry.setMaxResults(qry.getMaxRowCount());
    }
    return sqlQry.list();

}

From source file:org.openxdata.server.dao.hibernate.HibernateEditableDAO.java

License:Apache License

@Override
@SuppressWarnings("unchecked")
@Secured("Perm_View_Form_Data")
public List<FormDataHeader> getFormData(Integer formDefId, Integer userId, Date fromDate, Date toDate) {
    Session session = getSession();/* ww w.  j a v  a  2s.co  m*/

    String sql = "select d.form_data_id,d.form_definition_version_id, "
            + "fd.name as formName, fdv.name as versionName, u.user_name as creator, "
            + "d.date_created, u2.user_name as changed_by, d.date_changed,d.description "
            + "from form_data d inner join users u on u.user_id=d.creator "
            + "inner join form_definition_version fdv on fdv.form_definition_version_id=d.form_definition_version_id "
            + "inner join form_definition fd on fd.form_definition_id=fdv.form_definition_id "
            + "left join users u2 on u2.user_id=d.changed_by ";

    String filter = "";
    if (formDefId != null) {
        filter += " d.form_definition_version_id = :formDefId";
    }
    if (userId != null) {
        if (!filter.equals(""))
            filter += " and";
        filter += " d.creator = :userId";
    }
    if (fromDate != null) {
        if (!filter.equals(""))
            filter += " and";
        filter += " d.date_created >= :fromDate";
    }
    if (toDate != null) {
        if (!filter.equals(""))
            filter += " and";
        filter += " d.date_created <= :toDate";
    }
    if (!filter.equals("")) {
        filter = "where " + filter;
        sql += filter;
    }

    sql += " order by d.date_changed desc, d.date_created desc";

    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(FormDataHeader.class);
    if (formDefId != null) {
        query.setInteger("formDefId", formDefId);
    }
    if (userId != null) {
        query.setInteger("userId", userId);
    }
    if (fromDate != null) {
        query.setDate("fromDate", fromDate);
    }
    if (toDate != null) {
        query.setDate("toDate", toDate);
    }

    List<FormDataHeader> items = query.list();

    return items;
}

From source file:org.smallmind.persistence.orm.hibernate.HibernateDao.java

License:Open Source License

public <T> T findBySQLQuery(Class<T> returnType, SQLQueryDetails sqlQueryDetails) {

    SQLQuery sqlQuery;

    sqlQuery = constructSQLQuery(sqlQueryDetails);

    if (Durable.class.isAssignableFrom(returnType)) {

        return returnType.cast(sqlQuery.addEntity(returnType).uniqueResult());
    } else if (!SqlType.isKnownType(returnType)) {

        return returnType
                .cast(sqlQuery.setResultTransformer(Transformers.aliasToBean(returnType)).uniqueResult());
    } else {//from  w w w.j av  a  2  s .  com

        Object obj;

        if ((obj = sqlQuery.uniqueResult()) != null) {

            return returnType.cast(obj);
        }

        return null;
    }
}

From source file:org.smallmind.persistence.orm.hibernate.HibernateDao.java

License:Open Source License

public <T> List<T> listBySQLQuery(Class<T> returnType, SQLQueryDetails sqlQueryDetails) {

    SQLQuery sqlQuery;

    sqlQuery = constructSQLQuery(sqlQueryDetails);

    if (Durable.class.isAssignableFrom(returnType)) {

        return Collections.checkedList(sqlQuery.addEntity(returnType).list(), returnType);
    } else if (!SqlType.isKnownType(returnType)) {

        return Collections.checkedList(
                sqlQuery.setResultTransformer(Transformers.aliasToBean(returnType)).list(), returnType);
    } else {// www. j a v a2  s.c om

        LinkedList<T> returnList = new LinkedList<T>();

        for (Object obj : sqlQuery.list()) {
            returnList.add(returnType.cast(obj));
        }

        return returnList;
    }
}

From source file:org.snaker.engine.access.hibernate.HibernateAccess.java

License:Apache License

@SuppressWarnings("unchecked")
public <T> T queryObject(Class<T> T, String sql, Object... args) {
    SQLQuery query = getSession().createSQLQuery(sql);
    query.addEntity(T);
    for (int i = 0; i < args.length; i++) {
        query.setParameter(i, args[i]);//from  www.java2s .c om
    }
    return (T) query.uniqueResult();
}

From source file:org.snaker.engine.access.hibernate.HibernateAccess.java

License:Apache License

@SuppressWarnings("unchecked")
public <T> List<T> queryList(Class<T> clazz, String sql, Object... args) {
    SQLQuery query = getSession().createSQLQuery(sql);
    query.addEntity(clazz);
    for (int i = 0; i < args.length; i++) {
        query.setParameter(i, args[i]);/*from ww  w. j  a va  2s  . co  m*/
    }
    return (List<T>) query.list();
}

From source file:org.snaker.engine.access.hibernate3.HibernateAccess.java

License:Apache License

@SuppressWarnings("unchecked")
@Override/*ww w  .  j a va  2  s  .  com*/
public <T> T queryObject(Class<T> T, String sql, Object... args) {
    SQLQuery query = getSession().createSQLQuery(sql);
    query.addEntity(T);
    for (int i = 0; i < args.length; i++) {
        query.setParameter(i, args[i]);
    }
    return (T) query.uniqueResult();
}

From source file:org.snaker.engine.access.hibernate3.HibernateAccess.java

License:Apache License

@SuppressWarnings("unchecked")
@Override//from  w  ww .j  av a  2  s  . c om
public <T> List<T> queryList(Class<T> T, String sql, Object... args) {
    SQLQuery query = getSession().createSQLQuery(sql);
    query.addEntity(T);
    for (int i = 0; i < args.length; i++) {
        query.setParameter(i, args[i]);
    }
    return (List<T>) query.list();
}