Java tutorial
/* * Copyright (C) 2016 FormKiQ Inc. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.formkiq.core.dao; import static com.formkiq.core.util.Strings.extractLabelAndValue; import static org.springframework.util.StringUtils.hasText; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.List; import java.util.Map; import java.util.UUID; import java.util.stream.Collectors; import javax.persistence.Query; import org.apache.commons.lang3.tuple.Pair; import org.hibernate.Session; import org.hibernate.transform.AliasToBeanResultTransformer; import org.hibernate.transform.AliasToEntityMapResultTransformer; import org.hibernate.type.IntegerType; import org.hibernate.type.PostgresUUIDType; import org.hibernate.type.StringType; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import org.springframework.util.CollectionUtils; import org.springframework.util.StringUtils; import com.formkiq.core.domain.Folder; import com.formkiq.core.domain.FolderAccess; import com.formkiq.core.domain.FolderForm; import com.formkiq.core.domain.FolderFormLedger; import com.formkiq.core.domain.User; import com.formkiq.core.domain.type.AssetDTO; import com.formkiq.core.domain.type.ClientFormType; import com.formkiq.core.domain.type.FolderDTO; import com.formkiq.core.domain.type.FolderFormLedgerDTO; import com.formkiq.core.domain.type.FolderFormStatus; import com.formkiq.core.domain.type.FolderFormsListDTO; import com.formkiq.core.domain.type.FolderFormsSearchCriteria; import com.formkiq.core.domain.type.FolderListDTO; import com.formkiq.core.domain.type.FolderPermission; import com.formkiq.core.domain.type.FolderStatus; import com.formkiq.core.domain.type.FolderUserListDTO; import com.formkiq.core.domain.type.FormDTO; import com.formkiq.core.domain.type.FormOrderByField; import com.formkiq.core.domain.type.SortDirection; import com.formkiq.core.domain.type.UserDTO; import com.formkiq.core.domain.type.UserRole; import com.formkiq.core.util.DateService; import com.formkiq.core.util.Strings; /** * Implementation of Folder Dao. * */ @Repository public class FolderDaoImpl extends AbstractDaoImpl implements FolderDao { /** DateService. */ @Autowired private DateService dateservice; @SuppressWarnings("resource") @Override 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(); } @Override public void deleteFolderAccess(final FolderAccess fa) { getEntityManager().remove(fa); } @SuppressWarnings("resource") @Override 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(); } @Override public Folder findFolder(final String uuid) { return getEntityManager().find(Folder.class, UUID.fromString(uuid)); } @Override public FolderAccess findFolderAccess(final User user, final String folder) { UUID folderuuid = UUID.fromString(folder); String jql = "select fa from FolderAccess fa " + " where fa.userid=:user and fa.folderid=:folder"; Query query = getEntityManager().createQuery(jql).setParameter("user", user.getUserid()) .setParameter("folder", folderuuid); FolderAccess fa = (FolderAccess) getSingleResult(query); if (fa == null && UserRole.ROLE_ADMIN.equals(user.getRole())) { fa = new FolderAccess(); fa.setPermissions(Arrays.asList(FolderPermission.PERM_FORM_ADMIN)); } return fa; } @SuppressWarnings("resource") @Override 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; } @Override public FolderListDTO findFolderList(final String text, final String token) { String sql = "select distinct f.folder_id as folder," + " f.name as foldername," + " null as permissionsasstring" + " from folders f"; return findFolderList(null, text, token, sql); } @Override public FolderListDTO findFolderList(final User user, final String text, final String token) { String sql = "select distinct f.folder_id as folder," + " f.name as foldername," + " fa.permissions as permissionsasstring" + " from folders_access fa" + " join folders f on f.folder_id=fa.folder_id"; return findFolderList(user, text, token, sql); } /** * Finds Folder List. * @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; } @SuppressWarnings({ "unchecked", "resource" }) @Override 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; } @SuppressWarnings({ "unchecked", "resource" }) @Override 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; } @Override public FolderForm findForm(final String folder, final String form) { String jql = "select c from FolderForm c where c.folderid=:folder" + " and c.uuid=:form"; Query query = getEntityManager().createQuery(jql).setParameter("folder", UUID.fromString(folder)) .setParameter("form", UUID.fromString(form)); return (FolderForm) getSingleResult(query); } @Override public Pair<FolderForm, FolderAccess> findForm(final User user, final String folder, final String form) { FolderForm folderForm = null; FolderAccess access = findFolderAccess(user, folder); if (access != null) { folderForm = findForm(folder, form); } return Pair.of(folderForm, access); } @SuppressWarnings("resource") @Override 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; } @SuppressWarnings({ "unchecked", "resource" }) @Override 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; } @SuppressWarnings({ "unchecked", "resource" }) @Override 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) : ""; } @SuppressWarnings({ "unchecked", "resource" }) @Override 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; } /** * Find Workflow Steps. * @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; } @SuppressWarnings("resource") @Override 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(); } @SuppressWarnings("resource") @Override 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; } @SuppressWarnings("resource") @Override 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; } @Override public Folder saveFolder(final Folder folder) { Date now = this.dateservice.now(); if (folder.getFolderid() == null) { folder.setFolderid(UUID.randomUUID()); folder.setInsertedDate(now); getEntityManager().persist(folder); } else { getEntityManager().merge(folder); } return folder; } @Override public FolderAccess saveFolderAccess(final FolderAccess access) { Date now = this.dateservice.now(); access.setUpdatedDate(now); if (access.getFolderaccessid() == null) { access.setFolderaccessid(UUID.randomUUID()); access.setInsertedDate(now); getEntityManager().persist(access); } else { getEntityManager().merge(access); } return access; } @Override public FolderForm saveForm(final User user, final FolderForm form, final String sha256) { Date now = this.dateservice.now(); if (StringUtils.isEmpty(form.getFolderformid())) { form.setFolderformid(UUID.randomUUID()); getEntityManager().persist(form); } else { getEntityManager().merge(form); } if (form.getParentUUID() != null) { FolderFormLedger ledger = new FolderFormLedger(); ledger.setUserid(user.getUserid()); ledger.setFolderformledgerid(UUID.randomUUID()); ledger.setFolderid(form.getFolderid()); ledger.setUUID(form.getUUID()); ledger.setInsertedDate(now); ledger.setSha256(sha256); getEntityManager().persist(ledger); } return form; } /** * Transforms a FormDTO to a FolderFormsListDTO. * @param list {@link List} * @param offset int * @param max int * @return {@link FolderFormsListDTO} */ private FolderFormsListDTO searchFormsTransform(final List<FormDTO> list, final int offset, final int max) { for (FormDTO dto : list) { dto.setLabel1(extractLabelAndValue(dto.getLabel1()).getLeft()); dto.setLabel2(extractLabelAndValue(dto.getLabel2()).getLeft()); dto.setLabel3(extractLabelAndValue(dto.getLabel3()).getLeft()); } FolderFormsListDTO dto = new FolderFormsListDTO(); List<FormDTO> truncated = updatePagination(dto, offset, max, list); dto.setForms(truncated); return dto; } }