Java tutorial
/*************************************************************************** * Copyright (C) 2006 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.geospatial.impl.hibernate; import org.gbif.portal.dao.geospatial.CountryDAO; import org.gbif.portal.util.request.IPUtils; import java.util.ArrayList; import java.util.List; import java.util.Locale; import java.util.Map; import org.apache.commons.lang.StringUtils; import org.apache.commons.lang.builder.ToStringBuilder; import org.hibernate.Hibernate; import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.springframework.orm.hibernate3.HibernateCallback; import org.springframework.orm.hibernate3.HibernateTemplate; import org.springframework.orm.hibernate3.support.HibernateDaoSupport; /** * The DAO for the Country Model object. * * @author dmartin */ public class CountryDAOImpl extends HibernateDaoSupport implements CountryDAO { /** * Default ISO code to use when locale is null * TODO Need to move to somewhere more accessible */ protected String defaultISOLanguageCode = "en"; /** The list of currently support locales - dynamically loaded on application start up */ protected List<String> supportedLocales = null; /** * A map of Character to lists of ISO country codes. * Used to bring back countries in alphabetical searches. */ protected Map<Character, List<String>> characterIsoCodeMap; /** * @see org.gbif.portal.dao.geospatial.CountryDAO#findCountriesFor(java.lang.String, boolean, java.util.Locale) */ @SuppressWarnings("unchecked") public List findAllCountries(final Locale locale) { return (List) getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery( "from Country c inner join c.countryNames cn where cn.locale=:locale order by cn.name"); query.setString("locale", getLocaleForQuery(locale)); return query.list(); } }); } /** * @see org.gbif.portal.dao.geospatial.CountryDAO#findCountriesFor(java.lang.String, boolean, java.util.Locale) */ @SuppressWarnings("unchecked") public List findCountriesFor(final String nameStub, final boolean fuzzy, final boolean anyOccurrence, final boolean searchLocaleOnly, final Locale locale, final int startIndex, final int maxResults) { return (List) getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) { StringBuffer sb = null; if (searchLocaleOnly) { sb = new StringBuffer( "select c, cn.name from Country c inner join c.countryNames cn where (cn.name like :name "); if (anyOccurrence) { sb.append(" or cn.name like :anyPartName"); } sb.append(") and cn.locale=:locale"); sb.append(" order by cn.name"); } else { sb = new StringBuffer( "select distinct c, cns.name from CountryName cn inner join cn.country c inner join c.countryNames cns where (cns.name like :name"); if (anyOccurrence) { sb.append(" or cns.name like :anyPartName"); } sb.append(")"); // sb.append(" and cn.locale=:locale"); sb.append(" order by cn.name"); } Query query = session.createQuery(sb.toString()); String searchString = nameStub; if (fuzzy) searchString = searchString + '%'; if (anyOccurrence) query.setString("anyPartName", "% " + searchString); query.setString("name", searchString); if (searchLocaleOnly) { query.setString("locale", getLocaleForQuery(locale)); } query.setFirstResult(startIndex); query.setMaxResults(maxResults); return query.list(); } }); } /** * @see org.gbif.portal.dao.geospatial.CountryDAO#getCountriesFor(java.lang.String, boolean, java.util.Locale) */ @SuppressWarnings("unchecked") public List getCountriesFor(final char theChar, final boolean allowAdditionalSorting, final Locale locale) { return (List) getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) { List<String> extraIsoCodes = null; StringBuffer sb = new StringBuffer( "from Country c inner join c.countryNames cn where (cn.name like :name "); if (allowAdditionalSorting && characterIsoCodeMap != null && characterIsoCodeMap.containsKey(theChar)) { extraIsoCodes = characterIsoCodeMap.get(theChar); for (String isoCode : extraIsoCodes) { sb.append(" or cn.isoCountryCode='"); sb.append(isoCode); sb.append("'"); } } sb.append(") "); sb.append("and cn.locale=:locale order by cn.name"); Query query = session.createQuery(sb.toString()); String searchString = theChar + "%"; query.setString("name", searchString); query.setString("locale", getLocaleForQuery(locale)); return query.list(); } }); } /** * @see org.gbif.portal.dao.geospatial.CountryDAO#getCountriesForRegion(java.lang.String, java.util.Locale) */ @SuppressWarnings("unchecked") public List<Object[]> getCountriesForRegion(final String regionCode, final Locale locale) { return (List<Object[]>) getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery( "from Country c inner join c.countryNames cn where c.region=:regionCode and cn.locale=:locale"); query.setString("regionCode", regionCode); query.setString("locale", getLocaleForQuery(locale)); return query.list(); } }); } /** * @see org.gbif.portal.dao.geospatial.CountryDAO#getCountryAlphabet(java.util.Locale) */ @SuppressWarnings("unchecked") public List<Character> getCountryAlphabet(final Locale locale) { HibernateTemplate template = getHibernateTemplate(); List<String> results = (List<String>) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createSQLQuery( "select distinct(SUBSTRING(name,1,1)) from country_name where locale=? and name!=' %' order by name"); query.setParameter(0, getLocaleForQuery(locale)); return query.list(); } }); ArrayList<Character> chars = new ArrayList<Character>(); for (String result : results) { if (StringUtils.isNotEmpty(result)) chars.add(new Character(result.charAt(0))); } return chars; } /** * @see org.gbif.portal.dao.resources.DataResourceDAO#getCountryCountsForCountry(java.lang.Long) */ @SuppressWarnings("unchecked") public List<Object[]> getCountryCountsForCountry(final String isoCountryCode, final boolean geoRefOnly, final Locale locale) { HibernateTemplate template = getHibernateTemplate(); return (List) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { StringBuffer sb = new StringBuffer( "select dp.iso_country_code as the_iso_country_code, cn.name as cn_name,"); if (geoRefOnly) { sb.append(" sum(rc.occurrence_coordinate_count) as the_count 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 " + " inner join country_name cn on dp.iso_country_code=cn.iso_country_code" + " where "); } else { sb.append(" sum(rc.count) as the_count 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 " + " inner join country_name cn on dp.iso_country_code=cn.iso_country_code" + " where "); } if (isoCountryCode != null) { sb.append(" rc.iso_country_code=:isoCountryCode and cn.locale=:locale"); } if (geoRefOnly) { sb.append(" and rc.occurrence_coordinate_count>0"); } sb.append(" and dr.deleted is null"); sb.append(" group by cn_name"); SQLQuery query = session.createSQLQuery(sb.toString()); if (isoCountryCode != null) { query.setParameter("isoCountryCode", isoCountryCode); query.setParameter("locale", getLocaleForQuery(locale)); query.addScalar("the_iso_country_code", Hibernate.STRING); query.addScalar("cn_name", Hibernate.STRING); query.addScalar("the_count", Hibernate.INTEGER); } query.setCacheable(true); logger.debug("query is: " + sb.toString()); return query.list(); } }); } /** * @see org.gbif.portal.dao.taxonomy.TaxonConceptDAO#getCountryCountsForTaxonConcept(java.lang.Long) */ @SuppressWarnings("unchecked") public List<Object[]> getCountryCountsForTaxonConcept(final long taxonConceptId, final Locale locale) { HibernateTemplate template = getHibernateTemplate(); return (List) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { SQLQuery query = session.createSQLQuery("select tc.iso_country_code the_iso_country_code, " + " tc.iso_country_code the_iso_country_code2, cn.name as cn_name, tc.count as the_count from taxon_country tc" + " inner join country_name cn on tc.iso_country_code=cn.iso_country_code" + " where tc.taxon_concept_id=:taxonConceptId and cn.locale=:locale order by cn_name"); query.setParameter("taxonConceptId", taxonConceptId); query.setParameter("locale", getLocaleForQuery(locale)); query.addScalar("the_iso_country_code", Hibernate.STRING); query.addScalar("the_iso_country_code2", Hibernate.STRING); query.addScalar("the_count", Hibernate.INTEGER); query.setCacheable(true); return query.list(); } }); } /** * @see org.gbif.portal.dao.geospatial.CountryDAO#getCountryFor(long, java.util.Locale) */ public Object getCountryFor(final long countryId, final Locale locale) { return getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery( "from Country c inner join c.countryNames cn where c.countryId=:countryId and cn.locale=:locale"); query.setLong("countryId", countryId); query.setString("locale", getLocaleForQuery(locale)); return query.uniqueResult(); } }); } /** * @see org.gbif.portal.dao.geospatial.CountryDAO#getCountryForIP(java.lang.String, java.util.Locale) */ public Object getCountryForIP(final String ipAddress, final Locale locale) { return getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) { String localeStr = getLocaleForQuery(locale); Query query = session.createQuery( "select distinct c, cn from IPCountry ic inner join ic.country c inner join c.countryNames cn where ic.startLong<=:convertedIP and ic.endLong>=:convertedIP and cn.locale=:locale"); query.setParameter("locale", localeStr); query.setLong("convertedIP", IPUtils.convertIPtoLong(ipAddress)); return query.uniqueResult(); } }); } /** * @see org.gbif.portal.dao.geospatial.CountryDAO#getCountryForIsoCountryCode(java.lang.String, java.util.Locale) */ public Object getCountryForIsoCountryCode(final String isoCountryCode, final Locale locale) { return getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery( "from Country c inner join c.countryNames cn where c.isoCountryCode=:isoCountryCode and cn.locale=:locale"); query.setString("isoCountryCode", isoCountryCode); query.setString("locale", getLocaleForQuery(locale)); return query.uniqueResult(); } }); } /** * @see org.gbif.portal.dao.geospatial.CountryDAO#getCountryList() */ public List getCountryList() { HibernateTemplate template = getHibernateTemplate(); List results = (List) template.execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery("select c.isoCountryCode, cn.name " + " from Country c, CountryName cn" + " where c.isoCountryCode = cn.isoCountryCode and cn.locale='en' order by cn.name"); return query.list(); } }); return results; } /** * @see org.gbif.portal.dao.geospatial.CountryDAO#getHostCountryISOCountryCodes() */ @SuppressWarnings("unchecked") public List getHostCountryISOCountryCodes() { return (List) getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery("select distinct isoCountryCode from DataProvider"); return query.list(); } }); } /** * Retrieves the locale using the default if the supplied locale is null or not * supported. * * @param locale * @return language code e.g. "en" */ @SuppressWarnings("unchecked") protected String getLocaleForQuery(Locale locale) { if (supportedLocales == null) { logger.debug("retrieving supported locales"); supportedLocales = (List<String>) getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(final Session session) { // initialise supported locales final Query query = session.createQuery("select distinct cn.locale from CountryName cn"); return query.list(); } }); if (logger.isDebugEnabled()) logger.debug("Supported locales: " + ToStringBuilder.reflectionToString(supportedLocales)); } if (locale != null) { if (supportedLocales.contains(locale.getLanguage())) return locale.getLanguage(); } return defaultISOLanguageCode; } /* * ("select c, cn.name from Country c inner join c.countryNames cn where (cn.name like :name "); * Query query = session.createQuery( * "from TaxonConcept tc" + * " inner join fetch tc.taxonName" + * " inner join fetch tc.remoteConcepts as remoteConcept" + * " left join fetch tc.dataResource" + * " left join fetch tc.dataProvider" + * " left join fetch tc.parentConcept" + * " left join fetch tc.kingdomConcept left join fetch tc.kingdomConcept.taxonNameLite" + * " left join fetch tc.phylumConcept left join fetch tc.phylumConcept.taxonNameLite" + * " left join fetch tc.orderConcept left join fetch tc.orderConcept.taxonNameLite" + * " left join fetch tc.classConcept left join fetch tc.classConcept.taxonNameLite" + * " left join fetch tc.familyConcept left join fetch tc.familyConcept.taxonNameLite" + * " left join fetch tc.genusConcept left join fetch tc.genusConcept.taxonNameLite" + * " left join fetch tc.speciesConcept left join fetch tc.speciesConcept.taxonNameLite" + * " where remoteConcept.remoteId = ?"); * query.setParameter(0, remoteId); * query.setCacheable(true); * return query.list(); */ /** * @see org.gbif.portal.dao.geospatial.CountryDAO#getTotalCountryCount() */ public int getTotalCountryCount() { Long count = (Long) getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) { Query query = session.createQuery("select count(c.id) from Country c"); return query.uniqueResult(); } }); return count.intValue(); } /** * @param characterIsoCodeMap the characterIsoCodeMap to set */ public void setCharacterIsoCodeMap(Map<Character, List<String>> characterIsoCodeMap) { this.characterIsoCodeMap = characterIsoCodeMap; } /** * @param defaultISOLanguageCode the defaultISOLanguageCode to set */ public void setDefaultISOLanguageCode(String defaultISOLanguageCode) { this.defaultISOLanguageCode = defaultISOLanguageCode; } /** * @param supportedLocales the supportedLocales to set */ public void setSupportedLocales(List<String> supportedLocales) { this.supportedLocales = supportedLocales; } }