Example usage for org.hibernate Session 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.ClientDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override/*from   w  w w .j ava 2 s.co  m*/
public ClientDTO findClient(final User user, final String client) {

    String sql = "select fl.client_id as client, " + " additional_information as clientname, "
            + " authorized_grant_types as granttypesasstring" + " from oauth_client_details fl "
            + " where fl.client_id=:client";

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

    SQLQuery query = session.createSQLQuery(sql.toString());

    ClientDTO dto = (ClientDTO) query.setString("client", client)
            .setResultTransformer(new AliasToBeanResultTransformer(ClientDTO.class)).uniqueResult();

    if (dto != null) {
        dto.setClientname(getClientnameFromString(dto.getClientname()));
    }

    return dto;
}

---

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

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override/*from  ww  w .  java 2 s .com*/
public ClientListDTO findClients(final String token) {

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

    StringBuilder sql = new StringBuilder("select u.client_id as client, "
            + "u.additional_information as clientname, " + "u.authorized_grant_types as granttypesasstring"
            + " from oauth_client_details u order by clientname");

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

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

    List<ClientDTO> list = session.createSQLQuery(sql.toString())
            .setResultTransformer(new AliasToBeanResultTransformer(ClientDTO.class)).list();

    ClientListDTO dto = new ClientListDTO();

    List<ClientDTO> truncate = updatePagination(dto, offset, max, list);
    dto.setClients(truncate);

    for (ClientDTO cd : truncate) {
        cd.setClientname(getClientnameFromString(cd.getClientname()));
    }

    return dto;
}

---

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

License:Apache License

@SuppressWarnings("resource")
@Override/*from  w ww  . ja v a2  s  . c  om*/
public void deleteFolder(final String folder) {

    UUID folderId = UUID.fromString(folder);
    String sql0 = "delete from folders where folder_id=:folder";
    String sql1 = "delete from folders_access where folder_id=:folder";

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

    session.createSQLQuery(sql1).setParameter("folder", folderId, PostgresUUIDType.INSTANCE).executeUpdate();

    session.createSQLQuery(sql0).setParameter("folder", folderId, PostgresUUIDType.INSTANCE).executeUpdate();
}

---

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

License:Apache License

@SuppressWarnings("resource")
@Override//  w w  w  .j a v a2s . c o m
public void deleteForm(final String folder, final String uuid, final boolean isparentuuid) {

    String sql = "update folder_forms set status=:status " + " where folder_id=:folder and uuid=:uuid";

    if (isparentuuid) {
        sql = "update folder_forms set status=:status " + " where folder_id=:folder and parent_uuid=:uuid";
    }

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

    session.createSQLQuery(sql).setParameter("status", FolderFormStatus.DELETED.name())
            .setParameter("folder", UUID.fromString(folder), PostgresUUIDType.INSTANCE)
            .setParameter("uuid", UUID.fromString(uuid), PostgresUUIDType.INSTANCE).executeUpdate();
}

---

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

License:Apache License

@SuppressWarnings("resource")
@Override//from w  ww.ja v  a2  s  . c  o  m
public FolderDTO findFolderDTO(final User user, final String folderId) {

    String sql = "select f.folder_id as folder, f.name as foldername, "
            + " fa.permissions as permissionsasstring" + " from users u "
            + " join folders_access fa on u.user_id=fa.user_id "
            + " join folders f on f.folder_id=fa.folder_id " + " and f.folder_id=:folder"
            + " where u.user_id=:user" + " order by f.name ";

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

    FolderDTO folder = (FolderDTO) session.createSQLQuery(sql).addScalar("folder", StringType.INSTANCE)
            .addScalar("foldername", StringType.INSTANCE).addScalar("permissionsasstring", StringType.INSTANCE)
            .setParameter("user", user.getUserid(), PostgresUUIDType.INSTANCE)
            .setParameter("folder", UUID.fromString(folderId), PostgresUUIDType.INSTANCE)
            .setResultTransformer(new AliasToBeanResultTransformer(FolderDTO.class)).uniqueResult();

    return folder;
}

---

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

License:Apache License

/**
 * Finds Folder List.// www .ja v a2 s.  co  m
 * @param user {@link User}
 * @param text {@link String}
 * @param token {@link String}
 * @param sql {@link String}
 * @return {@link FolderListDTO}
 */
@SuppressWarnings({ "unchecked", "resource" })
private FolderListDTO findFolderList(final User user, final String text, final String token, final String sql) {

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

    StringBuilder sb = new StringBuilder(sql);

    if (user != null || hasText(text)) {
        sb.append(" where");
    }

    if (hasText(text)) {
        sb.append(" f.name like :text");
        if (user != null) {
            sb.append(" and");
        }
    }

    if (user != null) {
        sb.append(" fa.user_id=:user and");
        sb.append(" fa.status != :status");
    }

    sb.append(" order by foldername");

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

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

    org.hibernate.Query query = session.createSQLQuery(sb.toString()).addScalar("folder", StringType.INSTANCE)
            .addScalar("foldername", StringType.INSTANCE).addScalar("permissionsasstring", StringType.INSTANCE);

    if (user != null) {
        query = query.setParameter("status", FolderStatus.DELETED.name()).setParameter("user", user.getUserid(),
                PostgresUUIDType.INSTANCE);
    }

    if (hasText(text)) {
        query = query.setParameter("text", text + "%");
    }

    List<FolderDTO> list = query.setResultTransformer(new AliasToBeanResultTransformer(FolderDTO.class)).list();

    FolderListDTO dto = new FolderListDTO();

    List<FolderDTO> truncate = updatePagination(dto, offset, max, list);
    dto.setFolders(truncate);

    return dto;
}

---

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

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override/* w w  w  .j  a v a2 s  .  co  m*/
public List<FolderDTO> findFoldersDTO(final String email, final FolderStatus status) {

    String sql = "select f.folder_id as folder, f.name as foldername, "
            + " fa.permissions as permissionsasstring " + " from users u "
            + " join folders_access fa on u.user_id=fa.user_id " + " and fa.status=:status "
            + " join folders f on f.folder_id=fa.folder_id " + " where u.email=:email" + " order by f.name ";

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

    List<FolderDTO> folders = session.createSQLQuery(sql).addScalar("folder", StringType.INSTANCE)
            .addScalar("foldername", StringType.INSTANCE).addScalar("permissionsasstring", StringType.INSTANCE)
            .setParameter("email", email).setParameter("status", status.name())
            .setResultTransformer(new AliasToBeanResultTransformer(FolderDTO.class)).list();

    return folders;
}

---

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

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override//from   w  w w  .ja  va 2  s .  c  o  m
public FolderUserListDTO findFolderUsers(final String folder, final String token) {

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

    String sql = "select u.email as \"email\"," + " u.user_id as \"uuid\"," + " f.folder_id as \"folder.uuid\","
            + " f.name as \"folder.name\"," + " fa.permissions as \"folder.permissionsasstring\""
            + " from folders_access fa" + " join folders f on f.folder_id=fa.folder_id"
            + " join users u on u.user_id=fa.user_id" + " where fa.folder_id=:folder";

    StringBuilder sb = new StringBuilder(sql);
    sb.append(" order by u.email");
    sb.append(" OFFSET " + offset + " FETCH FIRST " + (max + 1) + " ROWS ONLY");

    FolderUserListDTO dto = new FolderUserListDTO();

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

    List<Map<String, Object>> list = session.createSQLQuery(sb.toString())
            .addScalar("folder.uuid", PostgresUUIDType.INSTANCE).addScalar("folder.name", StringType.INSTANCE)
            .addScalar("uuid", PostgresUUIDType.INSTANCE).addScalar("email", StringType.INSTANCE)
            .addScalar("folder.permissionsasstring", StringType.INSTANCE)
            .setParameter("folder", UUID.fromString(folder), PostgresUUIDType.INSTANCE)
            .setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();

    List<UserDTO> transform = transformMapListToObject(list, UserDTO.class);
    List<UserDTO> truncate = updatePagination(dto, offset, max, transform);
    dto.setUsers(truncate);

    return dto;
}

---

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

License:Apache License

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

    FormDTO dto = null;

    String sql = "select asset_id as assetid, " + "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 uuid=:uuid";

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

    Object[] objs = (Object[]) session.createSQLQuery(sql.toString()).addScalar("assetid", StringType.INSTANCE)
            .addScalar("type", StringType.INSTANCE).addScalar("status", StringType.INSTANCE)
            .addScalar("name", StringType.INSTANCE).addScalar("uuid", StringType.INSTANCE)
            .addScalar("label1", StringType.INSTANCE).addScalar("label2", StringType.INSTANCE)
            .addScalar("label3", StringType.INSTANCE).addScalar("updatedDate", StringType.INSTANCE)
            .addScalar("sha1hash", StringType.INSTANCE)
            .setParameter("folder", UUID.fromString(folder), PostgresUUIDType.INSTANCE)
            .setParameter("uuid", UUID.fromString(uuid), PostgresUUIDType.INSTANCE).uniqueResult();

    int i = 0;
    if (objs != null && objs.length > 0) {

        String assetId = objs[i++].toString();

        dto = new FormDTO();
        dto.setType((String) objs[i++]);
        dto.setStatus((String) objs[i++]);
        dto.setName((String) objs[i++]);
        dto.setUUID((String) objs[i++]);

        dto.setLabel1(extractLabelAndValue((String) objs[i++]).getLeft());
        dto.setLabel2(extractLabelAndValue((String) objs[i++]).getLeft());
        dto.setLabel3(extractLabelAndValue((String) objs[i++]).getLeft());

        dto.setUpdateddate((String) objs[i++]);
        dto.setSha1hash((String) objs[i++]);

        AssetDTO assetDTO = new AssetDTO();
        assetDTO.setUUID(assetId);
        dto.setAsset(assetDTO);
    }

    return dto;
}

---

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

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override/*from w w w  .j ava2  s  . co m*/
public List<FolderFormLedgerDTO> findFormLedger(final String folder, final String uuid) {

    List<FolderFormLedgerDTO> results = new ArrayList<>();

    List<String> steps = findWorkflowSteps(folder, uuid);

    if (CollectionUtils.isEmpty(steps)) {
        steps = Arrays.asList(uuid);
    }

    for (String step : steps) {

        String sql = "select ffl.folder_id as \"folder.uuid\"," + "u.user_id as \"user.uuid\","
                + "u.email as \"user.email\"," + "ff.type as \"form.type\","
                + "ff.data #>> '{\"label1\"}' as \"form.label1\","
                + "ff.data #>> '{\"label2\"}' as \"form.label2\","
                + "ff.data #>> '{\"label3\"}' as \"form.label3\","
                + "ff.data #>> '{\"name\"}' as \"form.name\"," + "ff.data #>> '{\"uuid\"}' as \"form.uuid\","
                + "ff.data #>> '{\"inserted_date\"}' as \"form.inserteddate\","
                + "ff.data #>> '{\"updated_date\"}' as \"form.updateddate\"" + "from folder_form_ledgers ffl "
                + "join folder_forms ff on " + "ff.folder_id=ffl.folder_id and ff.uuid=ffl.uuid "
                + "join users u on u.user_id=ffl.user_id " + "where ffl.folder_id=:folder and ffl.uuid=:uuid";

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

        List<Map<String, Object>> list = session.createSQLQuery(sql)
                .addScalar("folder.uuid", PostgresUUIDType.INSTANCE)
                .addScalar("user.uuid", PostgresUUIDType.INSTANCE).addScalar("user.email", StringType.INSTANCE)
                .addScalar("form.type", StringType.INSTANCE).addScalar("form.label1", StringType.INSTANCE)
                .addScalar("form.label2", StringType.INSTANCE).addScalar("form.label3", StringType.INSTANCE)
                .addScalar("form.name", StringType.INSTANCE).addScalar("form.uuid", StringType.INSTANCE)
                .addScalar("form.inserteddate", StringType.INSTANCE)
                .addScalar("form.updateddate", StringType.INSTANCE)
                .setParameter("folder", UUID.fromString(folder), PostgresUUIDType.INSTANCE)
                .setParameter("uuid", UUID.fromString(step), PostgresUUIDType.INSTANCE)
                .setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();

        List<FolderFormLedgerDTO> transform = transformMapListToObject(list, FolderFormLedgerDTO.class);

        results.addAll(transform);
    }

    return results;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »