Java tutorial
/** * ============================================================================= * * ORCID (R) Open Source * http://orcid.org * * Copyright (c) 2012-2014 ORCID, Inc. * Licensed under an MIT-Style License (MIT) * http://orcid.org/open-source-license * * This copyright and license information (including a link to the full license) * shall be included in its entirety in all copies or substantial portion of * the software. * * ============================================================================= */ package org.orcid.persistence.dao.impl; import java.math.BigInteger; import java.util.ArrayList; import java.util.List; import javax.persistence.Query; import javax.persistence.TypedQuery; import org.apache.commons.lang3.tuple.ImmutablePair; import org.apache.commons.lang3.tuple.Pair; import org.orcid.jaxb.model.message.Iso3166Country; import org.orcid.persistence.dao.OrgDisambiguatedDao; import org.orcid.persistence.jpa.entities.IndexingStatus; import org.orcid.persistence.jpa.entities.OrgDisambiguatedEntity; import org.springframework.cache.annotation.Cacheable; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; /** * * @author Will Simpson * */ public class OrgDisambiguatedDaoImpl extends GenericDaoImpl<OrgDisambiguatedEntity, Long> implements OrgDisambiguatedDao { public OrgDisambiguatedDaoImpl() { super(OrgDisambiguatedEntity.class); } @Override public OrgDisambiguatedEntity findBySourceIdAndSourceType(String sourceId, String sourceType) { TypedQuery<OrgDisambiguatedEntity> query = entityManager.createQuery( "from OrgDisambiguatedEntity where sourceId = :sourceId and sourceType = :sourceType", OrgDisambiguatedEntity.class); query.setParameter("sourceId", sourceId); query.setParameter("sourceType", sourceType); List<OrgDisambiguatedEntity> results = query.getResultList(); return results.isEmpty() ? null : results.get(0); } @Override public List<OrgDisambiguatedEntity> getChunk(int firstResult, int maxResults) { // Order by id so that we can page through in a predictable way TypedQuery<OrgDisambiguatedEntity> query = entityManager .createQuery("from OrgDisambiguatedEntity order by id", OrgDisambiguatedEntity.class); query.setFirstResult(firstResult); query.setMaxResults(maxResults); return query.getResultList(); } @Override public OrgDisambiguatedEntity findByNameCityRegionCountryAndSourceType(String name, String city, String region, Iso3166Country country, String sourceType) { TypedQuery<OrgDisambiguatedEntity> query = entityManager.createQuery( "from OrgDisambiguatedEntity where name = :name and city = :city and (region = :region or (region is null and :region is null)) and country = :country and sourceType = :sourceType", OrgDisambiguatedEntity.class); query.setParameter("name", name); query.setParameter("city", city); query.setParameter("region", region); query.setParameter("country", country); query.setParameter("sourceType", sourceType); List<OrgDisambiguatedEntity> results = query.getResultList(); return results.isEmpty() ? null : results.get(0); } @Override public List<OrgDisambiguatedEntity> findByName(String name) { TypedQuery<OrgDisambiguatedEntity> query = entityManager.createQuery( "from OrgDisambiguatedEntity where lower(name) = lower(:name)", OrgDisambiguatedEntity.class); query.setParameter("name", name); List<OrgDisambiguatedEntity> results = query.getResultList(); return results.isEmpty() ? null : results; } @SuppressWarnings("unchecked") @Override @Cacheable("orgs") public List<OrgDisambiguatedEntity> getOrgs(String searchTerm, int firstResult, int maxResults) { String qStr = "select od.*, COUNT(*) as countAll from org_disambiguated od left join org_affiliation_relation oa on od.id = oa.org_id" + " where lower(name) like '%' || lower(:searchTerm) || '%' group by od.id " + " order by position(lower(:searchTerm) in lower(name)), char_length(name), countAll DESC, od.name"; Query query = entityManager.createNativeQuery(qStr, OrgDisambiguatedEntity.class); query.setParameter("searchTerm", searchTerm); query.setFirstResult(firstResult); query.setMaxResults(maxResults); return query.getResultList(); } @Override public List<OrgDisambiguatedEntity> findOrgsByIndexingStatus(IndexingStatus indexingStatus, int firstResult, int maxResult) { TypedQuery<OrgDisambiguatedEntity> query = entityManager.createQuery( "from OrgDisambiguatedEntity where indexingStatus = :indexingStatus", OrgDisambiguatedEntity.class); query.setParameter("indexingStatus", indexingStatus); query.setFirstResult(0); query.setMaxResults(maxResult); return query.getResultList(); } @Override @Transactional public void updateIndexingStatus(Long orgDisambiguatedId, IndexingStatus indexingStatus) { String queryString = null; if (IndexingStatus.DONE.equals(indexingStatus)) { queryString = "update OrgDisambiguatedEntity set indexingStatus = :indexingStatus, lastIndexedDate = now() where id = :orgDisambiguatedId"; } else { queryString = "update OrgDisambiguatedEntity set indexingStatus = :indexingStatus where id = :orgDisambiguatedId"; } Query query = entityManager.createQuery(queryString); query.setParameter("orgDisambiguatedId", orgDisambiguatedId); query.setParameter("indexingStatus", indexingStatus); query.executeUpdate(); } @Override public List<Pair<Long, Integer>> findDisambuguatedOrgsWithIncorrectPopularity(int maxResults) { Query query = entityManager .createNativeQuery("SELECT od1.id, actual.popularity FROM org_disambiguated od1 JOIN" + " (SELECT od2.id id, COUNT(*) popularity FROM org_disambiguated od2 JOIN org o ON o.org_disambiguated_id = od2.id JOIN org_affiliation_relation oar ON oar.org_id = o.id GROUP BY od2.id)" + " actual ON actual.id = od1.id WHERE od1.popularity <> actual.popularity"); query.setMaxResults(maxResults); @SuppressWarnings("unchecked") List<Object[]> results = query.getResultList(); List<Pair<Long, Integer>> pairs = new ArrayList<>(); for (Object[] row : results) { Long id = ((BigInteger) row[0]).longValue(); Integer popularity = ((BigInteger) row[1]).intValue(); Pair<Long, Integer> pair = new ImmutablePair<Long, Integer>(id, popularity); pairs.add(pair); } return pairs; } @Override @Transactional public void updatePopularity(Long orgDisambiguatedId, Integer popularity) { Query query = entityManager.createQuery( "update OrgDisambiguatedEntity set indexingStatus = 'PENDING', popularity = :popularity where id = :orgDisambiguatedId"); query.setParameter("orgDisambiguatedId", orgDisambiguatedId); query.setParameter("popularity", popularity); query.executeUpdate(); } @Override @Transactional(propagation = Propagation.REQUIRED) public void replace(long deletedOrgDisambiguatedId, long replacementOrgDisambiguatedId) { Query query = entityManager.createQuery( "update OrgEntity set orgDisambiguated.id = :replacementOrgDisambiguatedId where orgDisambiguated.id = :deletedOrgDisambiguatedId"); query.setParameter("deletedOrgDisambiguatedId", deletedOrgDisambiguatedId); query.setParameter("replacementOrgDisambiguatedId", replacementOrgDisambiguatedId); query.executeUpdate(); } @Override @Transactional public void dropUniqueConstraint() { Query query = entityManager.createNativeQuery( "ALTER TABLE org_disambiguated DROP CONSTRAINT IF EXISTS org_disambiguated_unique_constraints"); query.executeUpdate(); } @Override @Transactional public void createUniqueConstraint() { Query query = entityManager.createNativeQuery( "ALTER TABLE org_disambiguated ADD CONSTRAINT org_disambiguated_unique_constraints UNIQUE (name, city, region, country, source_type)"); query.executeUpdate(); } @Override public List<OrgDisambiguatedEntity> findDuplicates() { TypedQuery<OrgDisambiguatedEntity> query = entityManager .createNamedQuery(OrgDisambiguatedEntity.FIND_DUPLICATES, OrgDisambiguatedEntity.class); return query.getResultList(); } }