Example usage for org.hibernate Session createSQLQuery

List of usage examples for org.hibernate Session createSQLQuery

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Session
  6. createSQLQuery

Introduction

In this page you can find the example usage for org.hibernate Session createSQLQuery.

Prototype

@Override
    NativeQuery createSQLQuery(String queryString);

Source Link

Usage

From source file:com.formkiq.core.dao.FolderDaoImpl.java

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override/*from   w w w  .j  a va  2 s . c  om*/
public String findFormData(final String folder, final String uuid) {

    String sql = "select data from folder_forms f " + "where f.folder_id=:folder and f.uuid=:uuid";

    Session session = getEntityManager().unwrap(Session.class);

    List<String> list = session.createSQLQuery(sql).addScalar("data", StringType.INSTANCE)
            .setParameter("folder", UUID.fromString(folder), PostgresUUIDType.INSTANCE)
            .setParameter("uuid", UUID.fromString(uuid), PostgresUUIDType.INSTANCE).list();

    return !list.isEmpty() ? list.get(0) : "";
}

From source file:com.formkiq.core.dao.FolderDaoImpl.java

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override// w  w w.ja  va2  s .c o m
public FolderFormsListDTO findForms(final String folder, final String uuid,
        final FolderFormsSearchCriteria criteria, final String token) {

    int offset = Strings.getOffset(token);
    int max = Strings.getMaxResults(token, DEFAULT_MAX_RESULTS);

    StringBuilder sql = new StringBuilder(
            "select type, status, data #>> '{name}' as name, " + "data #>> '{uuid}' as uuid, "
                    + "data #>> '{label1}' as label1, " + "data #>> '{label2}' as label2, "
                    + "data #>> '{label3}' as label3, " + "data #>> '{updated_date}' as updatedDate, "
                    + "sha1_hash as sha1hash " + "from folder_forms where folder_id=:folder "
                    + "and type != :workflowformtype " + "and status in :status ");

    if (!StringUtils.isEmpty(uuid)) {
        sql.append("and parent_uuid = :parent ");
    } else {
        sql.append("and parent_uuid is null ");
    }

    if (criteria.getType() != null) {
        sql.append("and type = :type ");
    }

    String text = criteria.getSearchText();
    if (!StringUtils.isEmpty(text)) {
        sql.append("and (data #>> '{label1}' ilike :txt " + " or data #>> '{label2}' ilike :txt "
                + " or data #>> '{label3}' ilike :txt " + ") ");
    }

    List<FolderFormStatus> status = criteria.getStatus();
    FormOrderByField orderby = criteria.getOrderby();
    SortDirection direction = criteria.getSorter();
    sql.append("order by " + orderby.name().toLowerCase() + " " + direction + " OFFSET " + offset
            + " FETCH FIRST " + (max + 1) + " ROWS ONLY");

    Session session = getEntityManager().unwrap(Session.class);

    org.hibernate.Query query = session.createSQLQuery(sql.toString())
            .setParameter("folder", UUID.fromString(folder), PostgresUUIDType.INSTANCE)
            .setParameter("workflowformtype", ClientFormType.WORKFLOW_FORM.name())
            .setParameterList("status", status.stream().map(s -> s.name()).collect(Collectors.toList()))
            .setResultTransformer(new AliasToBeanResultTransformer(FormDTO.class));

    if (!StringUtils.isEmpty(uuid)) {
        query.setParameter("parent", UUID.fromString(uuid), PostgresUUIDType.INSTANCE);
    }

    if (criteria.getType() != null) {
        query.setParameter("type", criteria.getType().name());
    }

    if (!StringUtils.isEmpty(text)) {
        query.setParameter("txt", text.toLowerCase() + "%");
    }

    List<FormDTO> list = query.list();

    FolderFormsListDTO dto = searchFormsTransform(list, offset, max);

    return dto;
}

From source file:com.formkiq.core.dao.FolderDaoImpl.java

License:Apache License

/**
 * Find Workflow Steps.//from  www . jav  a2s. c  om
 * @param folder {@link String}
 * @param uuid {@link String}
 * @return {@link List}
 */
@SuppressWarnings({ "unchecked", "resource" })
private List<String> findWorkflowSteps(final String folder, final String uuid) {

    String sql = "select jsonb_array_elements_text(ff.data->'steps') as s "
            + "from folder_forms ff where folder_id=:folder and uuid=:uuid";

    Session session = getEntityManager().unwrap(Session.class);

    List<String> steps = session.createSQLQuery(sql.toString()).addScalar("s", StringType.INSTANCE)
            .setParameter("folder", UUID.fromString(folder), PostgresUUIDType.INSTANCE)
            .setParameter("uuid", UUID.fromString(uuid), PostgresUUIDType.INSTANCE).list();

    return steps;
}

From source file:com.formkiq.core.dao.FolderDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override//  w w  w  . j  a va  2s.co  m
public int getFolderAccessCount(final String folder) {
    String sql = "select count(*) as count from folders_access " + " where folder_id=:folder";

    Session session = getEntityManager().unwrap(Session.class);

    Integer count = (Integer) session.createSQLQuery(sql).addScalar("count", IntegerType.INSTANCE)
            .setParameter("folder", UUID.fromString(folder), PostgresUUIDType.INSTANCE).setMaxResults(1)
            .uniqueResult();

    return count.intValue();
}

From source file:com.formkiq.core.dao.FolderDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override//from  ww w. ja v a2 s . c  o m
public boolean hasFiles(final String folder) {
    String sql = "select count(*) as count from folder_forms " + " where folder_id=:folder";

    Session session = getEntityManager().unwrap(Session.class);

    Integer count = (Integer) session.createSQLQuery(sql).addScalar("count", IntegerType.INSTANCE)
            .setParameter("folder", UUID.fromString(folder), PostgresUUIDType.INSTANCE).setMaxResults(1)
            .uniqueResult();

    return count.intValue() > 0;
}

From source file:com.formkiq.core.dao.FolderDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override/*from  w  w  w .  j a  v a 2s. c  om*/
public boolean hasFormChildren(final String folder, final String uuid) {

    String sql = "select count(*) as count from folder_forms "
            + " where folder_id=:folder and parent_uuid=:uuid ";

    Session session = getEntityManager().unwrap(Session.class);

    Integer count = (Integer) session.createSQLQuery(sql).addScalar("count", IntegerType.INSTANCE)
            .setParameter("folder", UUID.fromString(folder), PostgresUUIDType.INSTANCE)
            .setParameter("uuid", UUID.fromString(uuid), PostgresUUIDType.INSTANCE).setMaxResults(1)
            .uniqueResult();

    return count.intValue() > 0;
}

From source file:com.formkiq.core.dao.MigrationDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override//from  w  w  w. j a  v a2  s.com
public int findAssetCount() {
    String sql = "select count(*) as count from assets ";
    Session session = getEntityManager().unwrap(Session.class);

    Integer count = (Integer) session.createSQLQuery(sql).addScalar("count", IntegerType.INSTANCE)
            .uniqueResult();
    return count.intValue();
}

From source file:com.formkiq.core.dao.MigrationDaoImpl.java

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override/*w  ww . j  ava 2s . c  o  m*/
public MapPaginationDTO findFolderAssetList(final String token) {

    int offset = Strings.getOffset(token);
    int max = Strings.getMaxResults(token, DEFAULT_MAX_RESULTS);

    StringBuilder sql = new StringBuilder("select folder_id as folder, asset_id as asset "
            + "from folder_forms " + "where folder_id is not null and asset_id is not null ");

    sql.append(" OFFSET " + offset + " FETCH FIRST " + (max + 1) + " ROWS ONLY");

    Session session = getEntityManager().unwrap(Session.class);

    List<Map<String, String>> list = session.createSQLQuery(sql.toString())
            .addScalar("folder", StringType.INSTANCE).addScalar("asset", StringType.INSTANCE)
            .setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();

    MapPaginationDTO dto = new MapPaginationDTO();

    List<Map<String, String>> truncate = updatePagination(dto, offset, max, list);

    dto.setData(truncate);

    return dto;
}

From source file:com.formkiq.core.dao.ObjectEventDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override/* w w  w. j av  a2  s .  co  m*/
public boolean deleteEvent(final User user, final String object, final EventType type) {

    String sql = "delete from object_events where " + " user_id=:user and object_id=:object and type=:type";

    Session session = getEntityManager().unwrap(Session.class);

    int result = session.createSQLQuery(sql).setParameter("user", user.getUserid(), PostgresUUIDType.INSTANCE)
            .setParameter("object", UUID.fromString(object), PostgresUUIDType.INSTANCE)
            .setParameter("type", type.name()).executeUpdate();

    return result > 0;
}

From source file:com.formkiq.core.dao.ObjectEventDaoImpl.java

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override/*from   ww  w  .j a va 2 s.c o  m*/
public ObjectEventListDTO list(final User user) {

    StringBuilder sql = new StringBuilder("select oe.object_event_id as eventid, oe.type, "
            + "f.name as foldername, oe.notification, "
            + " oe.object_id as object, oe.inserted_date as insertedDate " + "from object_events oe "
            + "join folders f on f.folder_id=oe.object_id " + "where oe.user_id=:user " + "order by f.name");

    Session session = getEntityManager().unwrap(Session.class);

    List<ObjectEventDTO> list = session.createSQLQuery(sql.toString()).addScalar("eventid", StringType.INSTANCE)
            .addScalar("type", StringType.INSTANCE).addScalar("foldername", StringType.INSTANCE)
            .addScalar("notification", StringType.INSTANCE).addScalar("object", StringType.INSTANCE)
            .addScalar("insertedDate", TimestampType.INSTANCE)
            .setParameter("user", user.getUserid(), PostgresUUIDType.INSTANCE)
            .setResultTransformer(new AliasToBeanResultTransformer(ObjectEventDTO.class)).list();

    ObjectEventListDTO dto = new ObjectEventListDTO();
    dto.setEvents(list);

    return dto;
}