Index: /trunk/mpdb-server/src/edu/rpi/metpetdb/server/ExcelServlet.java =================================================================== --- /trunk/mpdb-server/src/edu/rpi/metpetdb/server/ExcelServlet.java (revision 946) +++ /trunk/mpdb-server/src/edu/rpi/metpetdb/server/ExcelServlet.java (revision 1033) @@ -60,6 +60,6 @@ response.getWriter().write("\n"); for (Sample s : samples){ - response.getWriter().write(s.getNumber() + "\t"); - response.getWriter().write(s.isPublicData() + "\t"); + response.getWriter().write(nullToEmptyString(s.getNumber()) + "\t"); + response.getWriter().write(boolToString(s.isPublicData()) + "\t"); response.getWriter().write(s.getSubsampleCount() + "\t"); response.getWriter().write(s.getImageCount() + "\t"); @@ -67,6 +67,6 @@ response.getWriter().write(s.getOwner().getName() + "\t"); response.getWriter().write(setToString(s.getRegions(),RegionProperty.name) + "\t"); - response.getWriter().write(s.getCountry() + "\t"); - response.getWriter().write(s.getRockType() + "\t"); + response.getWriter().write(nullToEmptyString(s.getCountry()) + "\t"); + response.getWriter().write(nullToEmptyString(s.getRockType()) + "\t"); response.getWriter().write(setToString(s.getMetamorphicGrades(),MetamorphicGradeProperty.name) + "\t"); response.getWriter().write(setSampleMineralsToString(s.getMinerals()) + "\t"); @@ -74,8 +74,8 @@ response.getWriter().write(formatlatlng(((Point)s.getLocation()).y) +"\t"); response.getWriter().write(formatlatlng(((Point)s.getLocation()).x) +"\t"); - response.getWriter().write(s.getSesarNumber() + "\t"); - response.getWriter().write(s.getCollector() + "\t"); + response.getWriter().write(nullToEmptyString(s.getSesarNumber()) + "\t"); + response.getWriter().write(nullToEmptyString(s.getCollector()) + "\t"); response.getWriter().write(Sample.dateToString(s.getCollectionDate(), s.getDatePrecision()) + "\t"); - response.getWriter().write(s.getLocationText() + "\t"); + response.getWriter().write(nullToEmptyString(s.getLocationText()) + "\t"); response.getWriter().write("\n"); } @@ -85,4 +85,12 @@ return; + } + + private String boolToString(final Boolean b){ + return (b) ? "yes" : "no"; + } + + private String nullToEmptyString(final Object o){ + return (o == null) ? "" : o.toString(); } @@ -101,5 +109,5 @@ String text = ""; for (SampleMineral sm : minerals){ - text += sm.getName() + " (" + sm.getAmount()+ "), "; + text += sm.toString() + ", "; } if (!text.equals("")){ Index: /trunk/mpdb-server/src/edu/rpi/metpetdb/server/search/SearchDb.java =================================================================== --- /trunk/mpdb-server/src/edu/rpi/metpetdb/server/search/SearchDb.java (revision 1031) +++ /trunk/mpdb-server/src/edu/rpi/metpetdb/server/search/SearchDb.java (revision 1033) @@ -81,33 +81,20 @@ // Get the subsample ids that correspond to the chemical analyses. System.out.println("Search Query Project Subsample Ids:" + chemQuery.getQueryString()); - final List projectedSubsampleIds = chemQuery.list(); + // no chemical analysis fit search, no reason to try finding samples with "no matches" - if (projectedSubsampleIds.size() == 0) { + String subsampleIds = getLongIdStringForList(chemQuery.list()); + if (subsampleIds == null) { return new Results(0, new ArrayList()); } - String subsampleIds = ""; - for (Long id : projectedSubsampleIds){ - subsampleIds += ((Long)id).toString() + ","; - } - if (subsampleIds != ""){ - subsampleIds = subsampleIds.substring(0, subsampleIds.length()-1); - } + // Get samples third... will need to incorporate top two pieces fullQuery = getSamplesQuery(searchSamp, userSearching, session); FullTextQuery sampleQuery = fullTextSession.createFullTextQuery(fullQuery, Sample.class); System.out.println("Search Query Project Sample Ids:" + fullQuery.toString()); - final List projectedSampleIds = sampleQuery.setProjection("id").list(); - if (projectedSampleIds.size() == 0) { + String sampleIds = getLongIdString(sampleQuery.setProjection("id").list()); + if (sampleIds == null) { return new Results(0, new ArrayList()); } - String sampleIds = ""; - for (Object[] o : projectedSampleIds){ - for (Object id : o){ - sampleIds += ((Long)id).toString() + ","; - } - } - if (sampleIds != ""){ - sampleIds = sampleIds.substring(0, sampleIds.length()-1); - } + org.hibernate.Query hql = session.createQuery("from Sample s where s.id in (" + sampleIds + ") and exists (select 1 from Subsample ss where ss.sampleId = s.id and ss.id in (" + @@ -115,11 +102,5 @@ org.hibernate.Query sizeQuery = session.createQuery("select count(*) " + hql.getQueryString()); System.out.println("Search Query Find Samples by Id and SubsampleId:" + hql.getQueryString()); - if (p != null) { - hql.setFirstResult(p.getFirstResult()); - hql.setMaxResults(p.getMaxResults()); - } else { - hql.setFirstResult(0); - hql.setMaxResults(100); - } + hql = setPagination(p,hql); try { //this is here in order to convert java.util.Collection$EmptyList into @@ -137,42 +118,32 @@ System.out.println("Search Query:" + fullQuery.toString()); - final FullTextQuery hibQuery = fullTextSession.createFullTextQuery( - fullQuery, Sample.class); - - if (p != null) { - hibQuery.setFirstResult(p.getFirstResult()); - hibQuery.setMaxResults(p.getMaxResults()); - } else { - //place an upper limit of 100 search results - hibQuery.setFirstResult(0); - hibQuery.setMaxResults(100); - } - try { - //this is here in order to convert java.util.Collection$EmptyList into - //the correct representation so it can be serialized by GWT - List list = hibQuery.list(); - if (list.size() == 0) - list = new ArrayList(); - int resultSize = 0; + try { if (session.getEnabledFilter("boundingBox")!=null){ FullTextQuery sizeHibQuery = fullTextSession.createFullTextQuery( fullQuery, Sample.class); - final List projectedSampleIds = sizeHibQuery.setProjection("id").list(); - String sampleIds = ""; - for (Object[] o : projectedSampleIds){ - for (Object id : o){ - sampleIds += ((Long)id).toString() + ","; - } + String sampleIds = getLongIdString(sizeHibQuery.setProjection("id").list()); + if (sampleIds == null) { + return new Results(0, new ArrayList()); } - if (sampleIds != ""){ - sampleIds = sampleIds.substring(0, sampleIds.length()-1); - } - org.hibernate.Query sizeQuery = session.createQuery("select count(*) from Sample s where s.id in (" + sampleIds + ")"); - resultSize = ((Long)sizeQuery.uniqueResult()).intValue(); + org.hibernate.Query resultQuery = session.createQuery("from Sample s where s.id in (" + sampleIds + ")"); + org.hibernate.Query sizeQuery = session.createQuery("select count(*) " + resultQuery.getQueryString()); + resultQuery = setPagination(p,resultQuery); + List list = resultQuery.list(); + if (list.size() == 0) + list = new ArrayList(); + final Results results = new Results(((Long)sizeQuery.uniqueResult()).intValue(),list); + return results; } else { - resultSize = hibQuery.getResultSize(); - } - final Results results = new Results(resultSize, list); - return results; + FullTextQuery hibQuery = fullTextSession.createFullTextQuery( + fullQuery, Sample.class); + hibQuery = setPagination(p,hibQuery); + //this is here in order to convert java.util.Collection$EmptyList into + //the correct representation so it can be serialized by GWT + List list = hibQuery.list(); + if (list.size() == 0) + list = new ArrayList(); + final Results results = new Results(hibQuery.getResultSize(), list); + return results; + } } catch (CallbackException e) { throw ConvertSecurityException.convertToException(e); @@ -201,12 +172,5 @@ System.out.println("Search Query:" + fullQuery.toString()); - if (p != null) { - fullQuery.setFirstResult(p.getFirstResult()); - fullQuery.setMaxResults(p.getMaxResults()); - } else { - //place an upper limit of 100 search results - fullQuery.setFirstResult(0); - fullQuery.setMaxResults(100); - } + fullQuery = setPagination(p,fullQuery); try { //this is here in order to convert java.util.Collection$EmptyList into @@ -229,31 +193,15 @@ .createFullTextQuery(tempSampleQuery, Subsample.class); System.out.println("Search Query Project Subsample Ids:" + sampleQuery.toString()); - final List projectedSubsampleIds = sampleQuery.setProjection("id").list(); - // no samples fit search, no reason to try finding chemical analyses with "no matches" - if (projectedSubsampleIds.size() == 0) { + String subsampleIds = getLongIdString(sampleQuery.setProjection("id").list()); + if (subsampleIds == null) { return new Results(0, new ArrayList()); - } - String subsampleIds = ""; - for (Object[] o : projectedSubsampleIds){ - for (Object id : o){ - subsampleIds += ((Long)id).toString() + ","; - } - } - if (subsampleIds != ""){ - subsampleIds = subsampleIds.substring(0, subsampleIds.length()-1); } // Get chemical analyses third... will need to incorporate top two pieces org.hibernate.Query chemQuery = getChemicalsQuery(searchSamp, userSearching, session, RETURN_CHEMICAL_ANALYSIS_ID); System.out.println("Search Query Project Chemical Analysis Ids:" + chemQuery.getQueryString()); - final List projectedChemIds = chemQuery.list(); - if (projectedChemIds.size() == 0){ + + String chemIds = getIntIdStringForList(chemQuery.list()); + if (chemIds == null) { return new Results(0, new ArrayList()); - } - String chemIds = ""; - for (Integer id : projectedChemIds){ - chemIds += ((Integer)id).toString() + ","; - } - if (chemIds != ""){ - chemIds = chemIds.substring(0, chemIds.length()-1); } org.hibernate.Query hql = session.createQuery("from ChemicalAnalysis ca where ca.id in (" + chemIds + @@ -262,12 +210,6 @@ org.hibernate.Query sizeQuery = session.createQuery("select count(*) " + hql.getQueryString()); System.out.println("Search Query Find Chemical Analyses by Id and SubsampleId:" + hql.getQueryString()); - - if (p != null) { - hql.setFirstResult(p.getFirstResult()); - hql.setMaxResults(p.getMaxResults()); - } else { - hql.setFirstResult(0); - hql.setMaxResults(100); - } + + hql = setPagination(p,hql); try { //this is here in order to convert java.util.Collection$EmptyList into @@ -680,3 +622,69 @@ return false; } + + private static org.hibernate.Query setPagination(PaginationParameters p, org.hibernate.Query q){ + if (p != null){ + q.setFirstResult(p.getFirstResult()); + q.setMaxResults(p.getMaxResults()); + } else { + q.setFirstResult(0); + q.setMaxResults(100); + } + return q; + } + + private static FullTextQuery setPagination(PaginationParameters p, FullTextQuery q){ + if (p != null){ + q.setFirstResult(p.getFirstResult()); + q.setMaxResults(p.getMaxResults()); + } else { + q.setFirstResult(0); + q.setMaxResults(100); + } + return q; + } + + private static String getLongIdStringForList(final List projectedIds){ + if (projectedIds.size() == 0) { + return null; + } + String ids = ""; + for (Long o : projectedIds){ + ids += o.toString() + ","; + } + if (ids != ""){ + ids = ids.substring(0, ids.length()-1); + } + return ids; + } + + private static String getIntIdStringForList(final List projectedIds){ + if (projectedIds.size() == 0) { + return null; + } + String ids = ""; + for (Integer o : projectedIds){ + ids += o.toString() + ","; + } + if (ids != ""){ + ids = ids.substring(0, ids.length()-1); + } + return ids; + } + + private static String getLongIdString(final List projectedIds){ + if (projectedIds.size() == 0) { + return null; + } + String ids = ""; + for (Object[] o : projectedIds){ + for (Object id : o){ + ids += ((Long)id).toString() + ","; + } + } + if (ids != ""){ + ids = ids.substring(0, ids.length()-1); + } + return ids; + } }