Java tutorial
/*************************************************************************** * Copyright (C) 2005 Global Biodiversity Information Facility Secretariat. * All Rights Reserved. * The contents of this file are subject to the Mozilla Public * License Version 1.1 (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.mozilla.org/MPL/ * Software distributed under the License is distributed on an "AS * IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or * implied. See the License for the specific language governing * rights and limitations under the License. ***************************************************************************/ package org.gbif.portal.dao.resources.impl.hibernate; import org.gbif.portal.dao.resources.DataResourceDAO; import org.gbif.portal.dto.util.TaxonRankType; import org.gbif.portal.model.occurrence.BasisOfRecord; import org.gbif.portal.model.resources.DataProvider; import org.gbif.portal.model.resources.DataResource; import org.gbif.portal.model.resources.ResourceNetwork; import java.math.BigInteger; import java.util.ArrayList; import java.util.Collections; import java.util.Date; import java.util.Iterator; import java.util.List; import org.apache.commons.lang.StringUtils; import org.hibernate.Criteria; import org.hibernate.FetchMode; import org.hibernate.Hibernate; import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.hibernate.criterion.MatchMode; import org.hibernate.criterion.Projections; import org.hibernate.criterion.Restrictions; import org.springframework.orm.hibernate3.HibernateCallback; import org.springframework.orm.hibernate3.HibernateTemplate; import org.springframework.orm.hibernate3.support.HibernateDaoSupport; /** * A hibernate based DAO implementation for DataResourceDAO. * * @author Dave Martin */ public class DataResourceDAOImpl extends HibernateDaoSupport implements DataResourceDAO { protected List<String> resourceNamePrefixes; /** * @see org.gbif.portal.dao.resources.DataProviderDAO#findDataProviders(java.lang.String, boolean, int, int) */ @SuppressWarnings("unchecked") public Long countDataResources(final String nameStub, final boolean fuzzy, final DataProvider dataProvider, final BasisOfRecord basisOfRecord, final Date modifiedSince) { HibernateTemplate template = getHibernateTemplate(); return ((Integer) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Criteria criteria = session.createCriteria(DataResource.class, "dr"); if (nameStub != null) { if (fuzzy) { criteria = criteria.add(Restrictions.like("dr.name", nameStub, MatchMode.START)); } else { criteria = criteria.add(Restrictions.eq("dr.name", nameStub)); } } if (dataProvider != null) { criteria = criteria.add(Restrictions.eq("dr.dataProvider", dataProvider)); } if (basisOfRecord != null) { criteria = criteria.add(Restrictions.eq("dr.basisOfRecord", basisOfRecord)); } if (modifiedSince != null) { criteria = criteria.add(Restrictions.ge("dp.modified", modifiedSince)); } criteria = criteria.add(Restrictions.isNull("dr.deleted")); criteria.setFetchMode("dr.dataProvider", FetchMode.JOIN); criteria.setProjection(Projections.rowCount()); return criteria.uniqueResult(); } })).longValue(); } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#findDataResources(java.lang.String, boolean, java.lang.Integer, * java.lang.Integer) */ @SuppressWarnings("unchecked") public List<DataResource> findDataResources(final String nameStub, final boolean fuzzy, final DataProvider dataProvider, final BasisOfRecord basisOfRecord, final Date modifiedSince, final int startIndex, final int maxResults) { HibernateTemplate template = getHibernateTemplate(); return (List<DataResource>) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Criteria criteria = session.createCriteria(DataResource.class, "dr"); if (nameStub != null) { if (fuzzy) { criteria = criteria.add(Restrictions.like("dr.name", nameStub, MatchMode.START)); } else { criteria = criteria.add(Restrictions.eq("dr.name", nameStub)); } } if (dataProvider != null) { criteria = criteria.add(Restrictions.eq("dr.dataProvider", dataProvider)); } if (basisOfRecord != null) { criteria = criteria.add(Restrictions.eq("dr.basisOfRecord", basisOfRecord)); } if (modifiedSince != null) { criteria = criteria.add(Restrictions.ge("dp.modified", modifiedSince)); } criteria = criteria.add(Restrictions.isNull("dr.deleted")); criteria.setFetchMode("dr.dataProvider", FetchMode.JOIN); criteria.setCacheable(true); criteria.setMaxResults(maxResults); criteria.setFirstResult(startIndex); return criteria.list(); } }); } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#findDataResourcesAndProviders(java.lang.String, boolean, * java.lang.Integer, java.lang.Integer) */ @SuppressWarnings("unchecked") public List findDataResourcesAndProvidersAndNetworks(final String nameStub, final boolean fuzzy, final boolean anyOccurrence, final boolean includeCountrySearch, final int startIndex, final int maxResults) { List modelObjects = new ArrayList(); try { HibernateTemplate template = getHibernateTemplate(); List results = (List) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { String searchString = nameStub; if (fuzzy) searchString = searchString + '%'; String anyPartNameString = "%" + searchString; StringBuffer sb = new StringBuffer( "Select 'provider', dp.id, dp.name, 0 as shared_taxonomy, dp.occurrence_count, dp.occurrence_coordinate_count, dp.concept_count, dp.species_count, dp.data_resource_count, dp.id as provider_id, dp.name as provider_name, dp.iso_country_code, dp.logo_url from data_provider dp"); sb.append(" where (dp.name like :nameStub "); if (anyOccurrence) { sb.append(" or dp.name like '" + anyPartNameString + "'"); } for (String prefix : resourceNamePrefixes) sb.append(" or dp.name like '" + prefix + " " + searchString + "'"); sb.append(") and dp.deleted is null"); sb.append(" UNION "); sb.append( " Select 'resource', dr.id, dr.display_name, dr.shared_taxonomy as shared_taxonomy, dr.occurrence_count, dr.occurrence_coordinate_count, dr.concept_count, dr.species_count, dr.species_count, dp.id as provider_id, dp.name as provider_name, 'XX', dr.logo_url from data_resource dr inner join data_provider dp on dr.data_provider_id=dp.id"); sb.append(" where ( dr.display_name like :nameStub "); if (anyOccurrence) { sb.append(" or dr.display_name like '" + anyPartNameString + "'"); } for (String prefix : resourceNamePrefixes) { sb.append(" or dr.display_name like '"); sb.append(prefix); sb.append(' '); sb.append(searchString); sb.append("'"); } sb.append(") and dr.deleted is null"); sb.append(" UNION "); sb.append( " Select 'network', rn.id, rn.name, 0 as shared_taxonomy, rn.occurrence_count, rn.occurrence_coordinate_count, rn.concept_count, rn.species_count, rn.data_resource_count, rn.id as provider_id, rn.name as provider_name, rn.code, rn.logo_url from resource_network rn left join country_name cn on rn.code = cn.iso_country_code"); sb.append(" where ( rn.name like :nameStub "); if (includeCountrySearch) { sb.append("or rn.code like :nameStub or cn.name like :nameStub"); } if (anyOccurrence) { sb.append(" or rn.name like '" + anyPartNameString + "'"); } for (String prefix : resourceNamePrefixes) { sb.append(" or rn.name like '"); sb.append(prefix); sb.append(' '); sb.append(searchString); sb.append("'"); } sb.append(") and rn.deleted is null"); Query query = session.createSQLQuery(sb.toString()); query.setParameter("nameStub", searchString); query.setMaxResults(maxResults); query.setFirstResult(startIndex); query.setCacheable(true); return query.list(); } }); for (Iterator iter = results.iterator(); iter.hasNext();) { Object[] result = (Object[]) iter.next(); Object modelObject = null; Long id = null; if (result[1] instanceof BigInteger) id = ((BigInteger) result[1]).longValue(); else if (result[1] instanceof Integer) id = ((Integer) result[1]).longValue(); else if (result[1] instanceof Long) id = (Long) result[1]; if (result[0].equals("resource")) { modelObject = new DataResource(id, (String) result[2], ((BigInteger) result[3]).intValue() == 1, (Integer) result[4], (Integer) result[5], (Integer) result[6], (Integer) result[7], (Integer) result[9], (String) result[10]); } else if (result[0].equals("provider")) { modelObject = new DataProvider(id, (String) result[2], (Integer) result[4], (Integer) result[5], (Integer) result[6], (Integer) result[7], (Integer) result[8], (String) result[11], (String) result[12]); } else { modelObject = new ResourceNetwork(id, (String) result[2], (String) result[11], (Integer) result[4], (Integer) result[5], (Integer) result[6], (Integer) result[7], (Integer) result[8]); } modelObjects.add(modelObject); } } catch (Exception e) { logger.error(e.getMessage(), e); } return modelObjects; } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#findDataResourcesAndProviders(java.lang.String, boolean, * java.lang.Integer, java.lang.Integer) */ @SuppressWarnings("unchecked") public List findDataResourcesAndProvidersAndNetworksFromPlots(final String nameStub, final boolean fuzzy, final boolean anyOccurrence, final boolean includeCountrySearch, final int startIndex, final int maxResults) { List modelObjects = new ArrayList(); try { HibernateTemplate template = getHibernateTemplate(); List results = (List) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { String searchString = nameStub; if (fuzzy) searchString = searchString + '%'; String anyPartNameString = "%" + searchString; StringBuffer sb = new StringBuffer( "Select 'provider', dp.id, dp.name, 0 as shared_taxonomy, dp.occurrence_count, dp.occurrence_coordinate_count, dp.concept_count, dp.species_count, dp.data_resource_count, dp.id as provider_id, dp.name as provider_name, dp.iso_country_code, dp.logo_url from data_provider dp"); sb.append(" where (dp.name like :nameStub "); if (anyOccurrence) { sb.append(" or dp.name like '" + anyPartNameString + "'"); } for (String prefix : resourceNamePrefixes) sb.append(" or dp.name like '" + prefix + " " + searchString + "'"); sb.append(") and dp.deleted is null"); sb.append(" UNION "); sb.append( " Select 'resource', dr.id, dr.display_name, dr.shared_taxonomy as shared_taxonomy, dr.occurrence_count, dr.occurrence_coordinate_count, dr.concept_count, dr.species_count, dr.species_count, dp.id as provider_id, dp.name as provider_name, 'XX', dr.logo_url from data_resource dr inner join data_provider dp on dr.data_provider_id=dp.id"); sb.append(" where ( dr.display_name like :nameStub "); if (anyOccurrence) { sb.append(" or dr.display_name like '" + anyPartNameString + "'"); } for (String prefix : resourceNamePrefixes) { sb.append(" or dr.display_name like '"); sb.append(prefix); sb.append(' '); sb.append(searchString); sb.append("'"); } sb.append(") and dr.deleted is null and dr.is_from_plots is true"); sb.append(" UNION "); sb.append( " Select 'network', rn.id, rn.name, 0 as shared_taxonomy, rn.occurrence_count, rn.occurrence_coordinate_count, rn.concept_count, rn.species_count, rn.data_resource_count, rn.id as provider_id, rn.name as provider_name, rn.code, rn.logo_url from resource_network rn left join country_name cn on rn.code = cn.iso_country_code"); sb.append(" where ( rn.name like :nameStub "); if (includeCountrySearch) { sb.append("or rn.code like :nameStub or cn.name like :nameStub"); } if (anyOccurrence) { sb.append(" or rn.name like '" + anyPartNameString + "'"); } for (String prefix : resourceNamePrefixes) { sb.append(" or rn.name like '"); sb.append(prefix); sb.append(' '); sb.append(searchString); sb.append("'"); } sb.append(") and rn.deleted is null"); Query query = session.createSQLQuery(sb.toString()); query.setParameter("nameStub", searchString); query.setMaxResults(maxResults); query.setFirstResult(startIndex); query.setCacheable(true); return query.list(); } }); for (Iterator iter = results.iterator(); iter.hasNext();) { Object[] result = (Object[]) iter.next(); Object modelObject = null; Long id = null; if (result[1] instanceof BigInteger) id = ((BigInteger) result[1]).longValue(); else if (result[1] instanceof Integer) id = ((Integer) result[1]).longValue(); else if (result[1] instanceof Long) id = (Long) result[1]; if (result[0].equals("resource")) { modelObject = new DataResource(id, (String) result[2], ((BigInteger) result[3]).intValue() == 1, (Integer) result[4], (Integer) result[5], (Integer) result[6], (Integer) result[7], (Integer) result[9], (String) result[10]); } else if (result[0].equals("provider")) { modelObject = new DataProvider(id, (String) result[2], (Integer) result[4], (Integer) result[5], (Integer) result[6], (Integer) result[7], (Integer) result[8], (String) result[11], (String) result[12]); } else { modelObject = new ResourceNetwork(id, (String) result[2], (String) result[11], (Integer) result[4], (Integer) result[5], (Integer) result[6], (Integer) result[7], (Integer) result[8]); } modelObjects.add(modelObject); } } catch (Exception e) { logger.error(e.getMessage(), e); } return modelObjects; } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#getAllDataResources() */ public List<DataResource> getAllDataResources() { HibernateTemplate template = getHibernateTemplate(); List results = (List) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery("from DataResource dr where dr.deleted is null order by name"); return query.list(); } }); return results; } /** * @param dataResourceId * @param geoRefOnly * @return */ @SuppressWarnings("unchecked") public List<Object[]> getCountryCountsForDataResource(final Long dataResourceId, final boolean geoRefOnly) { HibernateTemplate template = getHibernateTemplate(); return (List) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { StringBuffer sb = new StringBuffer( "select c.id as country_id, c.iso_country_code as iso_country_code, c.region as region,"); if (geoRefOnly) { sb.append(" rc.occurrence_coordinate_count as the_count"); } else { sb.append(" rc.count as the_count"); } sb.append( " from resource_country rc inner join country c on c.iso_country_code=rc.iso_country_code"); if (dataResourceId != null) { sb.append(" where rc.data_resource_id=:dataResourceId "); } if (geoRefOnly) { sb.append(" and rc.occurrence_coordinate_count>0"); } sb.append(" order by c.iso_country_code"); SQLQuery query = session.createSQLQuery(sb.toString()); if (dataResourceId != null) query.setParameter("dataResourceId", dataResourceId); query.setCacheable(true); query.addScalar("country_id", Hibernate.LONG); query.addScalar("iso_country_code", Hibernate.STRING); query.addScalar("region", Hibernate.STRING); query.addScalar("the_count", Hibernate.INTEGER); return query.list(); } }); } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#getDataResourceCountsForCountry(java.lang.Long) */ @SuppressWarnings("unchecked") public List<Object[]> getDataResourceCountsForCountry(final String isoCountryCode, final boolean geoRefOnly) { HibernateTemplate template = getHibernateTemplate(); return (List) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { StringBuffer sb = new StringBuffer("select dr.id, dr.display_name as dr_name, dp.name as dp_name,"); if (geoRefOnly) { sb.append(" rc.occurrence_coordinate_count "); } else { sb.append(" rc.count "); } sb.append("from resource_country rc" + " inner join data_resource dr on rc.data_resource_id=dr.id" + " inner join data_provider dp on dr.data_provider_id=dp.id" + " where "); if (isoCountryCode != null) { sb.append("rc.iso_country_code=:isoCountryCode and "); } sb.append("dr.deleted is null"); if (geoRefOnly) { sb.append(" and rc.occurrence_coordinate_count>0"); } sb.append(" order by dr_name"); Query query = session.createSQLQuery(sb.toString()); if (isoCountryCode != null) query.setParameter("isoCountryCode", isoCountryCode); query.setCacheable(true); return query.list(); } }); } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#getDataResourceFor(long) */ public DataResource getDataResourceFor(final long dataResourceId) { HibernateTemplate template = getHibernateTemplate(); return (DataResource) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session .createQuery("from DataResource dr left join fetch dr.dataProvider where dr.id = ?"); query.setParameter(0, dataResourceId); query.setCacheable(true); return query.uniqueResult(); } }); } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#getDataResourceList(java.lang.Long) */ @SuppressWarnings("unchecked") public List getDataResourceList(final Long dataProviderId) { HibernateTemplate template = getHibernateTemplate(); return (List) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery( "select dr.id, dr.name from DataResource dr where dr.dataProvider.id = ? and dr.deleted is null order by dr.name"); query.setParameter(0, dataProviderId); query.setCacheable(true); return query.list(); } }); } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#getDataResourceForProvider(java.lang.Long) */ @SuppressWarnings("unchecked") public List<DataResource> getDataResourcesForProvider(final long dataProviderId) { HibernateTemplate template = getHibernateTemplate(); return (List<DataResource>) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery( "from DataResource dr inner join fetch dr.dataProvider dp where dp.id = ? and dr.deleted is null order by dr.name"); query.setParameter(0, dataProviderId); query.setCacheable(true); return query.list(); } }); } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#getDataResourcesForResourceNetwork(long) */ @SuppressWarnings("unchecked") public List<DataResource> getDataResourcesForResourceNetwork(final long resourceNetworkId) { HibernateTemplate template = getHibernateTemplate(); return (List<DataResource>) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery("select nm.dataResource from NetworkMembership nm" + " join nm.resourceNetwork rn" + " left join fetch nm.dataResource.dataProvider" + " where rn.id = ? and nm.dataResource.deleted is null" + " order by nm.dataResource.name"); query.setParameter(0, resourceNetworkId); query.setCacheable(true); return query.list(); } }); } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#getDataResourcesWithNonSharedTaxonomies() */ @SuppressWarnings("unchecked") public List<DataResource> getDataResourcesWithNonSharedTaxonomies() { HibernateTemplate template = getHibernateTemplate(); return (List<DataResource>) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery("from DataResource dr inner join fetch dr.dataProvider" + " where dr.sharedTaxonomy=false and dr.conceptCount>0" + " and dr.deleted is null" + " order by dr.name"); query.setCacheable(true); return query.list(); } }); } /** * TODO This may need refactoring to use a join table similar to resource_country or taxon_country * * @see org.gbif.portal.dao.resources.DataResourceDAO#getDataResourceWithOccurrencesFor(java.lang.Long, * java.lang.String, boolean) */ @SuppressWarnings("unchecked") public List<Object[]> getDataResourceWithOccurrencesFor(final Long taxonConceptId, final String rank, final boolean georeferenced) { HibernateTemplate template = getHibernateTemplate(); return (List) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { StringBuffer sb = new StringBuffer( "select oc.dataResource.id, oc.dataResource.name, oc.dataProvider.name, count(oc.id) " + "from OccurrenceRecord oc where oc."); if (TaxonRankType.isRecognisedMajorRank(rank)) { sb.append(rank); sb.append("ConceptId=:taxonConceptId"); } else { sb.append("nubTaxonConceptId=:taxonConceptId"); } if (georeferenced) { sb.append(" and oc.cellId is not null and oc.geospatialIssue=0"); } sb.append(" group by oc.dataResourceId"); Query query = session.createQuery(sb.toString()); query.setParameter("taxonConceptId", taxonConceptId); query.setCacheable(true); return query.list(); } }); } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#getDatasetAlphabet() */ @SuppressWarnings("unchecked") public List<Character> getDatasetAlphabet() { HibernateTemplate template = getHibernateTemplate(); List<String> dataResourceChars = (List<String>) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createSQLQuery( "select distinct(SUBSTRING(display_name,1,1)) from data_resource where display_name is not null and deleted is null order by display_name"); return query.list(); } }); List<String> dataProviderChars = (List<String>) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createSQLQuery( "select distinct(SUBSTRING(name,1,1)) from data_provider where name is not null order by name"); return query.list(); } }); /* * SIB Colombia Edit, do not search resourceNetworks */ /* * List<String> resourceNetworksChars = (List<String>) template.execute(new HibernateCallback() { * public Object doInHibernate(Session session) { * Query query = * session * .createSQLQuery("select distinct(SUBSTRING(name,1,1)) from resource_network where name is not null order by name") * ; * return query.list(); * } * }); */ ArrayList<Character> chars = new ArrayList<Character>(); for (String result : dataResourceChars) { if (StringUtils.isNotEmpty(result)) chars.add(new Character(Character.toUpperCase(result.charAt(0)))); } for (String result : dataProviderChars) { if (StringUtils.isNotEmpty(result)) { Character theChar = new Character(Character.toUpperCase(result.charAt(0))); if (!chars.contains(theChar)) chars.add(theChar); } } /* * SIB Colombia Edit, do not search resourceNetworks */ /* * for (String result : resourceNetworksChars) { * if (StringUtils.isNotEmpty(result)) { * Character theChar = new Character(Character.toUpperCase(result.charAt(0))); * if (!chars.contains(theChar)) * chars.add(theChar); * } * } */ Collections.sort(chars); return chars; } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#getLastDataResourceAdded() */ public DataResource getLastDataResourceAdded() { HibernateTemplate template = getHibernateTemplate(); return (DataResource) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery( "from DataResource dr left join fetch dr.dataProvider where dr.deleted is null order by dr.created desc"); query.setMaxResults(1); query.setCacheable(true); return query.uniqueResult(); } }); } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#getTotalDataResourceCount() */ public int getTotalDataResourceCount() { HibernateTemplate template = getHibernateTemplate(); Object result = template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery( "select count(dr.id) from DataResource dr where dr.deleted is null and dr.dataProvider.deleted is null" + " and dr.occurrenceCount>=10 and dr.dataProvider.gbifApprover is not null"); return query.uniqueResult(); } }); if (result instanceof Integer) return ((Integer) result).intValue(); if (result instanceof Long) return ((Long) result).intValue(); return 0; } /** * @param resourceNamePrefixes the resourceNamePrefixes to set */ public void setResourceNamePrefixes(List<String> resourceNamePrefixes) { this.resourceNamePrefixes = resourceNamePrefixes; } }