/src/edu/ucsb/nceas/metacat/QueryGroup.java - Diff - Metacat - Ecoinformatics Redmine

« Previous | Next »

Revision 6602

Added by ben leinfelder over 12 years ago

uses prepared statement parameter binding for queries
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5527

     import edu.ucsb.nceas.metacat.util.SystemUtil;
     import java.io.*;
     import java.util.ArrayList;
     import java.util.Hashtable;
     import java.util.List;
     import java.util.Stack;
     import java.util.Vector;
     import java.util.Enumeration;
-...
                                                       //will hold query terms which has same search value.
         private Vector queryTermsInPathIndex = null; //this vector holds query terms without same value
                                                                                      // and search path is in path index.
         private Vector queryTerms = null;//this vector only holds query terms without same search value.
         private Vector<QueryTerm> queryTerms = null;//this vector only holds query terms without same search value.
                                                                  // and search path is NOT in path index.
         private Vector queryGroupsChildren = null;
         private static Logger logMetacat = Logger.getLogger(QueryGroup.class);
-...
           children = new Vector();
           queryTermsWithSameValue = new Vector();
           queryTermsInPathIndex = new Vector();
           queryTerms = new Vector();
           queryTerms = new Vector<QueryTerm>();
           queryGroupsChildren = new Vector();
+        }
-...
         /**
          * create a SQL serialization of the query that this instance represents
          */
         public String printSQL(boolean useXMLIndex) {
         public String printSQL(boolean useXMLIndex, List<Object> parameterValues) {
           StringBuffer self = new StringBuffer();
           StringBuffer queryString = new StringBuffer();
-...
           if (!queryTermsWithSameValue.isEmpty() || !queryTermsInPathIndex.isEmpty())
+          {
         	  String pathIndexQueryString = printSQLStringInPathIndex();
         	  // keep track of the values we add as prepared statement question marks (?)
         	  List<Object> groupValues = new ArrayList<Object>();
         	  String pathIndexQueryString = printSQLStringInPathIndex(groupValues);
         	  parameterValues.addAll(groupValues);
         	  queryString.append(pathIndexQueryString);
         	  if (queryString != null)
+        	  {
-...
           for (int i=0; i<queryGroupsChildren.size(); i++)
+          {
                 QueryGroup qg = (QueryGroup)queryGroupsChildren.elementAt(i);
             	String queryGroupSQL = qg.printSQL(useXMLIndex);
             	logMetacat.info("In QueryGroup.printSQL.. found a QueryGroup: "
             			+ queryGroupSQL);
         	  // keep track of the values we add as prepared statement question marks (?)
         	  List<Object> childrenValues = new ArrayList<Object>();
         	  // get the group
         	  QueryGroup qg = (QueryGroup) queryGroupsChildren.elementAt(i);
         	  String queryGroupSQL = qg.printSQL(useXMLIndex, childrenValues);
         	  logMetacat.info("In QueryGroup.printSQL.. found a QueryGroup: " + queryGroupSQL);
             	if (first) {
             		first = false;
             	} else {
-...
                         queryString.append(" " + operator + " ");
+            		}
+            	}
             	// add the sql
        		  	queryString.append(queryGroupSQL);
        		  	// add the parameter values
        		  	parameterValues.addAll(childrenValues);
        		  	// count percerntage number
        		  	// count percentage number
        		  	int count = qg.getPercentageSymbolCount();
        		  	countPercentageSearchItem = countPercentageSearchItem + count;
+          }
           for (int i=0; i<queryTerms.size(); i++)
+          {
                QueryTerm qt = (QueryTerm)queryTerms.elementAt(i);
                String termQueryString = qt.printSQL(useXMLIndex);
            	   logMetacat.info("In QueryGroup.printSQL.. found a QueryGroup: "
             			+ termQueryString);
                if(!(qt.getSearchMode().equals("contains") && qt.getValue().equals("%"))){
         	  // keep track of the values we add as prepared statement question marks (?)
         	  List<Object> termValues = new ArrayList<Object>();
         	  // get the term
         	  QueryTerm qt = (QueryTerm)queryTerms.elementAt(i);
         	  String termQueryString = qt.printSQL(useXMLIndex, termValues);
         	  logMetacat.info("In QueryGroup.printSQL.. found a QueryGroup: " + termQueryString);
                if (!(qt.getSearchMode().equals("contains") && qt.getValue().equals("%"))){
             	   if (first) {
                        first = false;
                    } else {
-...
                            queryString.append(" " + operator + " ");
+                       }
+                   }
             	   // include the sql
                    queryString.append(termQueryString);
                    // include the parameter values
                    parameterValues.addAll(termValues);
                // count percerntage number
                int count = qt.getPercentageSymbolCount();
                countPercentageSearchItem = countPercentageSearchItem + count;
-...
          * we still use "OR" to replace UNION action (we only handle union operator in the query group).
+         *
          */
         private String printSQLStringInPathIndex()
         private String printSQLStringInPathIndex(List<Object> parameterValues)
+        {
         	String sql ="";
         	String value ="";
-...
     					sqlBuff.append(" (");
     					// keep track of parameter values
     			        List<Object> searchValues = new ArrayList<Object>();
     		        	// get the general search criteria (no path info)
     		        	String searchTermSQL = term1.printSearchExprSQL();
     		        	String searchTermSQL = term1.printSearchExprSQL(searchValues);
     		        	// add the SQL
     					sqlBuff.append(searchTermSQL);
     					sqlBuff.append("AND path IN (");
     					// add parameter values
     					parameterValues.addAll(searchValues);
     					sqlBuff.append("AND path IN ( ");
     		    		//gets every path in query term object
     		    		for (int i=0; i<queryTermVector.size(); i++)
-...
     		    				if (first)
+    		    				{
     		    					first = false;
     		    					sqlBuff.append("'");
     		    					sqlBuff.append(path);
     		    					sqlBuff.append("'");
     		    					sqlBuff.append("?");
     		    					parameterValues.add(path);
+    		    				}
     		    				else
+    		    				{
     		    					sqlBuff.append(",'");
     		    					sqlBuff.append(path);
     		    					sqlBuff.append("'");
     		    					sqlBuff.append(", ?");
     		    					parameterValues.add(path);
+    		    				}
     		    				index++;
     		     				if (value != null && (value.equals("%") || value.equals("%%%")))
-...
+    		                    }
+    	    			     }
+    	    		    }
     	    		    sqlBuff.append("))");
     	    		    sqlBuff.append(" ))");
+    	    	    }
+    	    	}
-...
     		    				 sqlBuff.append(" "+"OR"+" ");
+    		    			 }
     		    			 sqlBuff.append("(");
     	    				 sqlBuff.append(term.printSQL(true));
     		    			 // keep track of the parameter values for this sql
     		    			 List<Object> termParameterValues = new ArrayList<Object>();
     		    			 String termSQL = term.printSQL(true, termParameterValues);
     	    				 sqlBuff.append(termSQL);
     	    				 sqlBuff.append(")");
     	    				 // add the param values
     	    				 parameterValues.addAll(termParameterValues);
     	    				 index++;
+    	    			}
+        			}
-...
     							// new query term into search value in handle new
     							// query term");
     							// find a target which has same search value
     							Vector newSameValueVector = new Vector();
     							Vector<QueryTerm> newSameValueVector = new Vector<QueryTerm>();
     							newSameValueVector.add(term);
     							newSameValueVector.addElement(newTerm);
     							queryTermsWithSameValue.add(newSameValueVector);
-...
     					// no same search value was found in queryTerms.
     					// then we need search queryTermsWithSameValue
     					for (int i = 0; i < queryTermsWithSameValue.size(); i++) {
     						Vector sameValueVec = (Vector) queryTermsWithSameValue
     								.elementAt(i);
     						Vector sameValueVec = (Vector) queryTermsWithSameValue.elementAt(i);
     						// we only compare the first query term
     						QueryTerm term = (QueryTerm) sameValueVec.elementAt(0);
     						if (term != null && term.hasSameSearchValue(newTerm)) {

Also available in: Unified diff

Project

General

Profile

Metacat

Revision 6602

Added by ben leinfelder over 12 years ago