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.ObjectEventDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override/*  w w  w  . j a  v  a2 s  .  co  m*/
public ObjectEventDTO findEvent(final UUID eventid) {
    StringBuilder sql = new StringBuilder("select oe.object_event_id as eventid, " + "u.email as email, "
            + "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 " + "join users u on u.user_id=oe.user_id "
            + "where oe.object_event_id=:id");

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

    ObjectEventDTO dto = (ObjectEventDTO) session.createSQLQuery(sql.toString())
            .addScalar("eventid", StringType.INSTANCE).addScalar("type", StringType.INSTANCE)
            .addScalar("email", StringType.INSTANCE).addScalar("foldername", StringType.INSTANCE)
            .addScalar("notification", StringType.INSTANCE).addScalar("object", StringType.INSTANCE)
            .addScalar("insertedDate", TimestampType.INSTANCE)
            .setParameter("id", eventid, PostgresUUIDType.INSTANCE)
            .setResultTransformer(new AliasToBeanResultTransformer(ObjectEventDTO.class)).uniqueResult();

    return dto;
}

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

License:Apache License

@SuppressWarnings("resource")
@Override/*from  w  w  w .  j ava  2s .com*/
public int delete(final Map<String, String> messageValues) {

    StringBuilder sb = new StringBuilder("delete from queue_messages where ");

    List<String> keys = new ArrayList<>(messageValues.keySet());

    for (int i = 0; i < keys.size(); i++) {

        if (i > 0) {
            sb.append(" and ");
        }

        String key = keys.get(i);
        sb.append(" message #>> '{" + key + "}'=:value" + i);
    }

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

    SQLQuery q = session.createSQLQuery(sb.toString());

    for (int i = 0; i < keys.size(); i++) {
        String key = keys.get(i);
        String value = messageValues.get(key);
        q.setParameter("value" + i, value);
    }

    return q.executeUpdate();
}

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

License:Apache License

@SuppressWarnings("resource")
@Override/* w  w w .  j  a  v a  2s.  co m*/
public int delete(final String messageid) {

    String sql = "delete from queue_messages where " + " queue_message_id=:id";

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

    int result = session.createSQLQuery(sql).setParameter("id", UUID.fromString(messageid)).executeUpdate();

    return result;
}

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

License:Apache License

@SuppressWarnings("resource")
@Override//from  w w w. j a  v a2  s .com
public int deleteExpiredMessages() {

    Date dayBefore = DateUtils.addDays(this.dateservice.now(), -1);

    String sql = "delete from queue_messages where " + " inserted_date < :date and status=:status";

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

    int result = session.createSQLQuery(sql).setParameter("date", dayBefore)
            .setParameter("status", QueueMessageStatus.TO_BE_DELETED.name()).executeUpdate();

    return result;
}

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

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override/*from   w  w  w. j av  a 2  s. c om*/
public QueueListDTO findMessages(final String queue, final String token) {

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

    StringBuilder sql = new StringBuilder(
            "select u.queue_message_id as uuid " + ", u.message as message, u.status as status "
                    + " from queue_messages u " + " where queue_id=:queue ");

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

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

    List<QueueDTO> list = session.createSQLQuery(sql.toString()).addScalar("uuid", StringType.INSTANCE)
            .addScalar("message", StringType.INSTANCE).addScalar("status", StringType.INSTANCE)
            .setParameter("queue", UUID.fromString(queue), PostgresUUIDType.INSTANCE)
            .setResultTransformer(new AliasToBeanResultTransformer(QueueDTO.class)).list();

    QueueListDTO dto = new QueueListDTO();

    List<QueueDTO> truncate = updatePagination(dto, offset, max, list);
    dto.setMessages(truncate);

    return dto;
}

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

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override//from   w  w w .  j av a 2s .  c o m
public ReportListDTO getReports(final String folderid) {

    String sql = "select data #>> '{name}' as name, " + "data #>> '{formuuid}' as formuuid, "
            + "updated_date as updateddate, " + "inserted_date as inserteddate, " + "report_uuid as uuid, "
            + "sha1_hash as sha1hash " + "from folder_form_reports " + "where folder_id=:folder";

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

    List<ReportDTO> list = session.createSQLQuery(sql.toString()).addScalar("name", StringType.INSTANCE)
            .addScalar("formuuid", StringType.INSTANCE).addScalar("uuid", StringType.INSTANCE)
            .addScalar("sha1hash", StringType.INSTANCE).addScalar("updateddate", TimestampType.INSTANCE)
            .addScalar("inserteddate", TimestampType.INSTANCE)
            .setParameter("folder", UUID.fromString(folderid), PostgresUUIDType.INSTANCE)
            .setResultTransformer(new AliasToBeanResultTransformer(ReportDTO.class)).list();

    ReportListDTO dto = new ReportListDTO();
    dto.setReports(list);
    return dto;
}

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

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override/*from  w w w.jav a 2 s .co m*/
public List<Map<String, Object>> runReport(final ReportInput input) {

    StringBuilder sb = new StringBuilder();

    appendSelectSQL(sb, input);

    appendFromSQL(sb, input);

    appendWhereSQL(sb, input);

    appendWhereDateSQL(sb, input);

    appendGroupBySQL(sb, input);

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

    SQLQuery q = session.createSQLQuery(sb.toString());
    addScalars(q, input);

    Map<Object, Object> map = createParameterMap(input);
    q.setProperties(map);

    List<Object[]> list = q.list();

    return translateToMap(input.getColumns(), list);
}

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

License:Apache License

@SuppressWarnings("resource")
@Override/*from ww  w. j  a va  2  s .c  o  m*/
public int delete(final User user, final String key) {

    String sql = "delete from system_properties where key=:key ";

    if (user != null) {
        sql += " and user_id=:user";
    }

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

    Query q = session.createSQLQuery(sql).setParameter("key", key);

    if (user != null) {
        q.setParameter("user", user.getUserid(), PostgresUUIDType.INSTANCE);
    }

    int result = q.executeUpdate();
    return result;
}

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

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override//from   w ww. j ava 2 s .  c o  m
public List<SystemPropertyDTO> getProperties(final User user, final String key) {

    String sql = "select p.key as key, p.value as value, " + "p.user_id as \"user.uuid\","
            + "u.email as \"user.email\"" + " from system_properties p "
            + " left join users u on u.user_id=p.user_id";

    if (user != null || StringUtils.hasText(key)) {
        sql += " where ";
    }

    if (user != null) {
        sql += " p.user_id=:user";
    }

    if (StringUtils.hasText(key)) {
        sql += user != null ? " and " : " ";
        sql += "key=:key";
    }

    sql += " order by p.key ";

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

    SQLQuery q = session.createSQLQuery(sql).addScalar("key", StringType.INSTANCE)
            .addScalar("value", StringType.INSTANCE).addScalar("user.email", StringType.INSTANCE)
            .addScalar("user.uuid", PostgresUUIDType.INSTANCE);

    if (user != null) {
        q.setParameter("user", user.getUserid(), PostgresUUIDType.INSTANCE);
    }

    if (StringUtils.hasText(key)) {
        q.setParameter("key", key);
    }

    List<Map<String, Object>> maplist = q.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE)
            .list();

    List<SystemPropertyDTO> list = transformMapListToObject(maplist, SystemPropertyDTO.class);

    return list;
}

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

License:Apache License

@SuppressWarnings("resource")
@Override/*w w w  .j  a v  a 2s. com*/
public void deleteUser(final User user) {

    String d0 = "update users set status=:status, updated_date=:date " + " where user_id=:user";

    String d1 = "update folders_access " + "set status=:status, updated_date=:date " + "where user_id=:user";

    Date now = this.dateservice.now();
    Session session = getEntityManager().unwrap(Session.class);

    session.createSQLQuery(d1).setParameter("status", UserStatus.DELETED.name()).setParameter("date", now)
            .setParameter("user", user.getUserid(), PostgresUUIDType.INSTANCE).executeUpdate();

    session.createSQLQuery(d0).setParameter("status", UserStatus.DELETED.name()).setParameter("date", now)
            .setParameter("user", user.getUserid(), PostgresUUIDType.INSTANCE).executeUpdate();
}