/ - Diff - Metacat - Ecoinformatics Redmine

« Previous | Next »

Revision 6602

Added by ben leinfelder about 13 years ago

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

     import java.io.FileNotFoundException;
     import java.io.FileReader;
     import java.io.IOException;
     import java.sql.PreparedStatement;
     import java.util.ArrayList;
     import java.util.List;
     import junit.framework.Test;
     import junit.framework.TestSuite;
     import edu.ucsb.nceas.MCTestCase;
     import edu.ucsb.nceas.metacat.DBQuery;
     import edu.ucsb.nceas.metacat.QuerySpecification;
     import edu.ucsb.nceas.metacat.database.DBConnectionPool;
     import edu.ucsb.nceas.metacat.properties.PropertyService;
     import edu.ucsb.nceas.metacat.util.MetacatUtil;
     import edu.ucsb.nceas.utilities.PropertyNotFoundException;
-...
         /** The utilities object for accessing property values */
         private static String selectionQuery = "SELECT docid,docname,doctype,date_created, date_updated, rev " +
         		"FROM xml_documents WHERE docid IN (((((SELECT DISTINCT docid FROM xml_nodes " +
         		"WHERE UPPER(nodedata) LIKE '%JONES%' ) ))))";
         private static String selectionQuery =
         	"SELECT docid,docname,doctype,date_created, date_updated, rev " +
         	"FROM xml_documents WHERE docid IN (((((SELECT DISTINCT docid FROM xml_nodes " +
         	"WHERE UPPER(nodedata) LIKE %JONES% ) )))) ";
         /*private static String extendedQuery = "select xml_nodes.docid, 'dataset/title' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'title' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'dataset' ) )  AND xml_nodes.docid in " +
         		"('obfs.45337', 'obfs.45338', 'obfs.45346') AND xml_nodes.nodetype = 'TEXT' AND " +
         		"xml_nodes.rootnodeid = xml_documents.rootnodeid UNION select xml_nodes.docid, 'originator/individualName/surName' as " +
-...
         		"IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'eml' AND parentnodeid = rootnodeid ) ) ) )  " +
         		"AND xml_nodes.docid in ('obfs.45337', 'obfs.45338', 'obfs.45346') AND xml_nodes.nodetype = 'TEXT' " +
         		"AND xml_nodes.rootnodeid = xml_documents.rootnodeid";*/
         private static String extendedQuery ="select xml_nodes.docid, xml_index.path, xml_nodes.nodedata,  xml_nodes.parentnodeid, xml_nodes.nodetype FROM xml_index, xml_nodes WHERE"+
                                         " ( (xml_index.nodeid=xml_nodes.parentnodeid AND xml_index.path IN ('dataset/title', 'originator/individualName/surName' , 'keyword' ) AND xml_nodes.nodetype = 'TEXT') "+
                                         "OR  (xml_index.nodeid=xml_nodes.nodeid AND ( xml_index.path IN ( '/eml/@packageId', '/eml/dataset/access/@authSystem' , '/eml/dataset/access/@order' ) AND xml_nodes.nodetype = 'ATTRIBUTE'))) "+
                                         "AND xml_nodes.docid in ('obfs.45337', 'obfs.45338', 'obfs.45346')";
         private static String extendedQuery =
         		"select xml_nodes.docid, xml_index.path, xml_nodes.nodedata,  xml_nodes.parentnodeid, xml_nodes.nodetype " +
         		"FROM xml_index, xml_nodes " +
         		"WHERE ( (xml_index.nodeid=xml_nodes.parentnodeid AND xml_index.path IN ( dataset/title, originator/individualName/surName , keyword ) " +
         		"AND xml_nodes.nodetype = 'TEXT') "+
                 "OR  (xml_index.nodeid=xml_nodes.nodeid AND ( xml_index.path IN ( /eml/@packageId, /eml/dataset/access/@authSystem , /eml/dataset/access/@order ) " +
                 "AND xml_nodes.nodetype = 'ATTRIBUTE'))) "+
                 "AND xml_nodes.docid in ('obfs.45337', 'obfs.45338', 'obfs.45346')";
         /* Initialize properties*/
         static
-...
+     	  }
       	  catch(Exception e)
+      	  {
       		  System.err.println("Exception in initialize option in MetacatServletNetTest "+e.getMessage());
       		  System.err.println("Exception in initialize option in MetacatServletNetTest " + e.getMessage());
+      	  }
+        }
-...
                 QuerySpecification qspec = new QuerySpecification(xml,
                         PropertyService.getProperty("xml.saxparser"),
                         PropertyService.getProperty("document.accNumSeparator"));
                 String query = qspec.printSQL(false);
                assertTrue(selectionQuery.equals(query.trim()));
             } catch (IOException e) {
                 fail(e.getMessage());
             } catch (PropertyNotFoundException pnfe) {
                 fail(pnfe.getMessage());
+            }
                 // keep track of parameter values
                 List<Object> parameterValues = new ArrayList<Object>();
                 String query = qspec.printSQL(false, parameterValues);
         		// fill in the values to really check the query string matches original/expected
         		PreparedStatement pstmt = DBConnectionPool.getDBConnection("queryGroupTest").prepareStatement(query);
         		pstmt = DBQuery.setPreparedStatementValues(parameterValues, pstmt);
         		String preparedQueryString = pstmt.toString();
         		System.out.println("Prepared query: " + preparedQueryString);
                 System.out.println("Original query: " + selectionQuery);
                 assertEquals(selectionQuery, preparedQueryString);
             } catch (Exception e) {
         		e.printStackTrace();
     			fail(e.getMessage());
+    		}
+        }
         /**
-...
         public void testPrintExtendedSQL()
+        {
             try {
                 System.out.println("---- orginal EXT SQL  ------------------\n"+extendedQuery);
                 System.out.println("---- orginal EXT SQL  ------------------");
                 System.out.println(extendedQuery);
                 QuerySpecification qspec = new QuerySpecification(xml,
                         PropertyService.getProperty("xml.saxparser"),
                         PropertyService.getProperty("document.accNumSeparator"));
                 // keep track of parameter values
                 List<Object> parameterValues = new ArrayList<Object>();
                 String query =
                         qspec.printExtendedSQL(
                                 "'obfs.45337', 'obfs.45338', 'obfs.45346'", true);
                 System.out.println("---- builded EXT SQL ------------------ \n"+query);
                 assertTrue(extendedQuery.equals(query));
             } catch (IOException e) {
                                 "'obfs.45337', 'obfs.45338', 'obfs.45346'", true, parameterValues);
                 System.out.println("---- built EXT SQL ------------------");
                 System.out.println(query);
                 // fill in the values to really check the query string matches original/expected
         		PreparedStatement pstmt = DBConnectionPool.getDBConnection("queryGroupTest").prepareStatement(query);
         		pstmt = DBQuery.setPreparedStatementValues(parameterValues, pstmt);
         		String preparedQueryString = pstmt.toString();
         		System.out.println("Prepared query: " + preparedQueryString);
                 System.out.println("Original query: " + extendedQuery);
                 assertEquals(extendedQuery, preparedQueryString);
             } catch (Exception e) {
             	e.printStackTrace();
                 fail(e.getMessage());
             } catch (PropertyNotFoundException pnfe) {
                 fail(pnfe.getMessage());
+            }
+        }

      */
     package edu.ucsb.nceas.metacattest;
     import java.sql.PreparedStatement;
     import java.util.ArrayList;
     import java.util.List;
     import junit.framework.Test;
     import junit.framework.TestSuite;
     import edu.ucsb.nceas.MCTestCase;
     import edu.ucsb.nceas.metacat.DBQuery;
     import edu.ucsb.nceas.metacat.QueryGroup;
     import edu.ucsb.nceas.metacat.QueryTerm;
     import edu.ucsb.nceas.metacat.database.DBConnectionPool;
     import edu.ucsb.nceas.metacat.properties.PropertyService;
     import edu.ucsb.nceas.metacat.util.MetacatUtil;
-...
       		  System.err.println("Exception in initialize option in MetacatServletNetTest "+e.getMessage());
+      	  }
+        }
         private String query = "(SELECT DISTINCT docid FROM xml_path_index WHERE  (UPPER(nodedata) "+
                                                 "LIKE '%LAND%' AND path IN ('dataset/title','geographicCoverage/boundingCoordinates/southBoundingCoordinate')) " +
                                                 "OR ((UPPER(nodedata) LIKE '%JONES%' AND path LIKE 'organizationName') ) OR ((UPPER(nodedata) LIKE '%LAND %' AND path LIKE 'keyword') ) " +
                                                 "OR ((UPPER(nodedata) LIKE '%DATOS%' AND path LIKE 'entityName') ) UNION ((SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) " +
                                                 "LIKE '%VALUE1%' AND parentnodeid IN (SELECT nodeid FROM xml_index WHERE path LIKE 'path1') )  UNION " +
                                                 "(SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) LIKE '%VALUE2%' AND parentnodeid IN " +
                                                 "(SELECT nodeid FROM xml_index WHERE path LIKE 'path2') ) ))";
         /**
          * NOTE: there are no quotes around the values because we are comparing it to a PreparedStatement.toString()
          * String after binding the parameter values. Please trust that the PreparedStatement is correct.
          */
         private String query =
         	"(SELECT DISTINCT docid FROM xml_path_index WHERE  (UPPER(nodedata) "+
             "LIKE %LAND% AND path IN ( dataset/title, geographicCoverage/boundingCoordinates/southBoundingCoordinate )) " +
             "OR ((UPPER(nodedata) LIKE %JONES% AND path LIKE organizationName) ) OR ((UPPER(nodedata) LIKE %LAND % AND path LIKE keyword) ) " +
             "OR ((UPPER(nodedata) LIKE %DATOS% AND path LIKE entityName) ) UNION ((SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) " +
             "LIKE %VALUE1% AND parentnodeid IN (SELECT nodeid FROM xml_index WHERE path LIKE path1) )  UNION " +
             "(SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) LIKE %VALUE2% AND parentnodeid IN " +
             "(SELECT nodeid FROM xml_index WHERE path LIKE path2) ) ))";
         /**
          * Constructor to build the test
-...
         	group.addChild(term4);
         	group.addChild(term5);
         	group.addChild(child);
         	String queryString = group.printSQL(true);
         	assertTrue(queryString.equals(query));
         	// keep track of parameter values
             List<Object> parameterValues = new ArrayList<Object>();
         	String queryString = group.printSQL(true, parameterValues);
         	try {
         		// fill in the values to really check the query string matches original/expected
         		PreparedStatement pstmt = DBConnectionPool.getDBConnection("queryGroupTest").prepareStatement(queryString);
         		pstmt = DBQuery.setPreparedStatementValues(parameterValues, pstmt);
         		String preparedQueryString = pstmt.toString();
         		System.out.println("Prepared query: " + preparedQueryString);
         		System.out.println("Original query: " + query);
         		assertEquals(query, preparedQueryString);
         	} catch (Exception e) {
         		e.printStackTrace();
     			fail(e.getMessage());
+    		}
+        }

     import java.io.IOException;
     import java.io.Reader;
     import java.io.StringReader;
     import java.util.ArrayList;
     import java.util.Enumeration;
     import java.util.List;
     import java.util.Stack;
     import java.util.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();
-...
             // Get the query from the QueryGroup and check
             // if no query has been returned
             String queryFromQueryGroup;
             // keep track of the values we add as prepared statement question marks (?)
             List<Object> groupValues = new ArrayList<Object>();
             if (query != null) {
             	queryFromQueryGroup = query.printSQL(useXMLIndex);
             	queryFromQueryGroup = query.printSQL(useXMLIndex, groupValues);
             } else {
             	queryFromQueryGroup = "";
+            }
-...
                 self.append(" docid IN (");
                 self.append(queryFromQueryGroup);
                 self.append(") ");
                 // add the parameter values
                 parameterValues.addAll(groupValues);
+            }
             // Add SQL to filter for doctypes requested in the query
-...
          * @param useXMLIndex a boolean flag indicating whether to search using
          *            xml_index
          */
         public String printExtendedSQL(String doclist, boolean useXMLIndex)
         public String printExtendedSQL(String doclist, boolean useXMLIndex, List<Object> allValues)
+        {
             if (useXMLIndex && !containsPredicates)
+            {
                 return printExtendedSQL(doclist);
         	// keep track of the values we add as prepared statement question marks (?)
         	//List<Object> allValues = new ArrayList<Object>();
             if (useXMLIndex && !containsPredicates) {
             	// keep track of the values we add as prepared statement question marks (?)
             	List<Object> parameterValues = new ArrayList<Object>();
             	String query = printExtendedSQL(doclist, parameterValues);
             	// add parameter values to our running list
             	allValues.addAll(parameterValues);
             	return query;
+            }
             else
+            {
                 StringBuffer self = new StringBuffer();
                 boolean firstfield = true;
                 // keep track of the values we add as prepared statement question marks (?)
             	List<Object> parameterValues = new ArrayList<Object>();
                 // first part comes from fields without  predicates
                 String queryFromWithoutPrecidates = printExtendedSQL(doclist);
                  if (queryFromWithoutPrecidates != null)
+                 {
                 String queryFromWithoutPrecidates = printExtendedSQL(doclist, parameterValues);
                 // add parameter values to our running list
             	allValues.addAll(parameterValues);
             	if (queryFromWithoutPrecidates != null) {
                 	 // it has return fields without predicate
                 	 self.append(queryFromWithoutPrecidates);
                 	 firstfield = false;
+                 }
+            	}
                 //put the returnfields into the query
                 //the for loop allows for multiple fields
                 for (int i = 0; i <   returnFieldListWithPredicates.size(); i++)
-...
                         self.append(" UNION ");
+                    }
                     String path  = (String)  returnFieldListWithPredicates.elementAt(i);
                     path = path.replaceAll("'", "''");
                     // TODO: can we use prepared statements for this?
                     allValues.add(path);
                     self.append("select xml_nodes.docid, ");
                     self.append("'"+ path.replaceAll("'", "''") + "' as path, ");
                     self.append("? as path, ");
                     self.append("xml_nodes.nodedata, ");
                     self.append("xml_nodes.parentnodeid, ");
                     self.append("xml_nodes.nodetype ");
                     //self.append("from xml_nodes, xml_documents ");
                     self.append("from xml_nodes ");
                     self.append("where ");
                     self.append(QueryTerm.useNestedStatements(path));
                     // keep track of the values we add as prepared statement question marks (?)
                 	List<Object> nestedParameterValues = new ArrayList<Object>();
                     String nestedQuery = QueryTerm.useNestedStatements(path, nestedParameterValues);
                     self.append(nestedQuery);
                     // add to the running total
                     allValues.addAll(nestedParameterValues);
                     self.append(" AND xml_nodes.docid in (");
                     self.append(doclist);
-...
          * @param unaccessableNodePair the node pairs (start id and end id)
          *            which this user should not access
          */
         private String printExtendedSQL(String doclist) {
         private String printExtendedSQL(String doclist, List<Object> values) {
         	// keep track of the values we add as prepared statement question marks (?)
         	//List<Object> values = new ArrayList<Object>();
             logMetacat.debug("QuerySpecification.printExtendedSQL - in printExtendedSQL");
             StringBuffer self = new StringBuffer();
             Vector elementVector = new Vector();
             Vector attributeVector = new Vector();
             Vector<String> elementVector = new Vector<String>();
             Vector<String> attributeVector = new Vector<String>();
             boolean usePathIndex = true;
-...
             	return null;
+            }
             if(usePathIndex){
             if (usePathIndex){
                 self.append("select docid, path, nodedata, parentnodeid, null as nodetype ");
                 self.append("from xml_path_index where path in( '");
                 self.append("from xml_path_index where path in ( ");
                 boolean firstfield = true;
                 //put the returnfields into the query
-...
                 	returnField = returnField.replaceAll("'", "''");
                     if (firstfield) {
                         firstfield = false;
                         self.append(returnField);
                         self.append("' ");
                         self.append("? ");
                     	values.add(returnField);
+                    }
                     else {
                         self.append(", '");
                         self.append(returnField);
                         self.append("' ");
                         self.append(", ? ");
                         values.add(returnField);
+                    }
+                }
                 self.append(") AND docid in (");
-...
                 self.append("xml_nodes.nodetype ");
                 self.append("FROM xml_index, xml_nodes WHERE (");
                 boolean firstElement = true;
                 boolean firstAttribute = true;
                 //put the returnfields into the query
-...
     	            	String path = (String) elementVector.elementAt(i);
     	                if (firstElement) {
     	                	firstElement = false;
     	                	self.append(" (xml_index.nodeid=xml_nodes.parentnodeid AND xml_index.path IN ('");
     	                    self.append(path);
     	                    self.append("'");
     	                	self.append(" (xml_index.nodeid=xml_nodes.parentnodeid AND xml_index.path IN ( ");
     	                    self.append("?");
     	                    values.add(path);
+    	                 }
     	                else
+    	                {
     	                    self.append(", '");
     	                    self.append(path);
     	                    self.append("' ");
     	                    self.append(", ? ");
     	                    values.add(path);
+    	                }
+    	            }
     	            self.append(") AND xml_nodes.nodetype = 'TEXT')");
-...
+                    		{
                     			self.append(" OR ");
+                    		}
                 			self.append(" (xml_index.nodeid=xml_nodes.nodeid AND ( xml_index.path IN ( '");
     	                    self.append(path);
     	                    self.append("'");
                 			self.append(" (xml_index.nodeid=xml_nodes.nodeid AND ( xml_index.path IN ( ");
     	                    self.append("?");
     	                    values.add(path);
+                		}
                 		else
+    	                {
     	                    self.append(", '");
     	                    self.append(path);
     	                    self.append("' ");
     	                    self.append(", ? ");
     	                    values.add(path);
+    	                }
+                	}
                 	self.append(") AND xml_nodes.nodetype = 'ATTRIBUTE'))");

     import java.sql.PreparedStatement;
     import java.sql.ResultSet;
     import java.sql.SQLException;
     import java.sql.Timestamp;
     import java.util.ArrayList;
     import java.util.Date;
     import java.util.Enumeration;
     import java.util.Hashtable;
     import java.util.Iterator;
     import java.util.List;
     import java.util.StringTokenizer;
     import java.util.Vector;
     import java.util.zip.ZipEntry;
-...
                                           Vector givenDocids, String qformat)
                                           throws Exception
+        {
         	// keep track of the values we add as prepared statement question marks (?)
       	  List<Object> parameterValues = new ArrayList<Object>();
           StringBuffer resultsetBuffer = new StringBuffer();
           String query = null;
           int count = 0;
-...
            * and contruct a simpler query based on a
            * list of docids rather than a bunch of subselects
            */
           // keep track of the values we add as prepared statement question marks (?)
     	  List<Object> docidValues = new ArrayList<Object>();
           if ( givenDocids == null || givenDocids.size() == 0 ) {
               query = qspec.printSQL(useXMLIndex);
               query = qspec.printSQL(useXMLIndex, docidValues);
               parameterValues.addAll(docidValues);
           } else {
         	  // condition for the docids
         	  StringBuffer docidCondition = new StringBuffer();
-...
                   query = query + docidCondition.toString();
         	  } else {
         		  // start with the keyword query, but add conditions
                   query = qspec.printSQL(useXMLIndex);
                   query = qspec.printSQL(useXMLIndex, docidValues);
                   parameterValues.addAll(docidValues);
                   String myOperator = "";
                   if (!query.endsWith("WHERE")) {
     	              if (operator.equalsIgnoreCase(QueryGroup.UNION)) {
-...
           startTime = System.currentTimeMillis() / 1000;
           pstmt = dbconn.prepareStatement(query);
           // set all the values we have collected
           pstmt = setPreparedStatementValues(parameterValues, pstmt);
           logMetacat.debug("Prepared statement after setting parameter values: " + pstmt.toString());
           rs = pstmt.executeQuery();
           double queryExecuteTime = System.currentTimeMillis() / 1000;
-...
               boolean tableHasRows = false;
               // keep track of parameter values
               List<Object> parameterValues = new ArrayList<Object>();
                String extendedQuery =
                    qspec.printExtendedSQL(doclist.toString(), useXMLIndex);
                    qspec.printExtendedSQL(doclist.toString(), useXMLIndex, parameterValues);
                logMetacat.info("DBQuery.addReturnfield - Extended query: " + extendedQuery);
                if(extendedQuery != null){
     //        	   long extendedQueryStart = System.currentTimeMillis();
                    pstmt = dbconn.prepareStatement(extendedQuery);
                    // set the parameter values
                    pstmt = DBQuery.setPreparedStatementValues(parameterValues, pstmt);
                    //increase dbconnection usage count
                    dbconn.increaseUsageCount(1);
                    pstmt.execute();
-...
     		   queryResultCache.clear();
+    	   }
+       }
        /**
         * Set the parameter values in the prepared statement using instrospection
         * of the given value objects
         * @param parameterValues
         * @param pstmt
         * @return
         * @throws SQLException
         */
        public static PreparedStatement setPreparedStatementValues(List<Object> parameterValues, PreparedStatement pstmt) throws SQLException {
     	   // set all the values we have collected
           int parameterIndex = 1;
           for (Object parameterValue: parameterValues) {
         	  if (parameterValue instanceof String) {
         		  pstmt.setString(parameterIndex, (String) parameterValue);
+        	  }
         	  else if (parameterValue instanceof Integer) {
         		  pstmt.setInt(parameterIndex, (Integer) parameterValue);
+        	  }
         	  else if (parameterValue instanceof Float) {
         		  pstmt.setFloat(parameterIndex, (Float) parameterValue);
+        	  }
         	  else if (parameterValue instanceof Double) {
         		  pstmt.setDouble(parameterIndex, (Double) parameterValue);
+        	  }
         	  else if (parameterValue instanceof Date) {
         		  pstmt.setTimestamp(parameterIndex, new Timestamp(((Date) parameterValue).getTime()));
+        	  }
         	  else {
         		  pstmt.setObject(parameterIndex, parameterValue);
+        	  }
         	  parameterIndex++;
+          }
           return pstmt;
+       }
         /*

     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)) {

     package edu.ucsb.nceas.metacat;
     import java.util.ArrayList;
     import java.util.Calendar;
     import java.util.List;
     import java.util.Vector;
     import javax.xml.bind.DatatypeConverter;
-...
+        }
         public String printSearchExprSQL() {
         public String printSearchExprSQL(List<Object> parameterValues) {
             // Uppercase the search string if case match is not important
             String casevalue = null;
-...
             // Add appropriate wildcards to search string
             String searchexpr = null;
             if (searchmode.equals("starts-with")) {
                 searchexpr = nodedataterm + " LIKE '" + casevalue + "%' ";
                 searchexpr = nodedataterm + " LIKE ? ";
                 parameterValues.add(casevalue + "%");
             } else if (searchmode.equals("ends-with")) {
                 searchexpr = nodedataterm + " LIKE '%" + casevalue + "' ";
                 searchexpr = nodedataterm + " LIKE ? ";
                 parameterValues.add("%" + casevalue + "%");
             } else if (searchmode.equals("contains")) {
                 if (!casevalue.equals("%")) {
                     searchexpr = nodedataterm + " LIKE '%" + casevalue + "%' ";
                     searchexpr = nodedataterm + " LIKE ? ";
                     parameterValues.add("%" + casevalue + "%");
                 } else {
                     searchexpr = nodedataterm + " LIKE '" + casevalue + "' ";
                     searchexpr = nodedataterm + " LIKE ? ";
                     parameterValues.add(casevalue);
                     // find percentage symbol
                     percentageSymbol = true;
+                }
             } else if (searchmode.equals("not-contains")) {
             	notEqual = true;
                 searchexpr = nodedataterm + " LIKE '%" + casevalue + "%' ";
                 searchexpr = nodedataterm + " LIKE ? ";
                 parameterValues.add("%" + casevalue + "%");
             } else if (searchmode.equals("equals")) {
                 searchexpr = nodedataterm + " = '" + casevalue + "' ";
                 searchexpr = nodedataterm + " = ? ";
                 parameterValues.add(casevalue);
             } else if (searchmode.equals("isnot-equal")) {
             	notEqual = true;
                 searchexpr = nodedataterm + " = '" + casevalue + "' ";
                 searchexpr = nodedataterm + " = ? ";
                 parameterValues.add(casevalue);
             } else {
                 String oper = null;
                 if (searchmode.equals("greater-than")) {
-...
                 try {
                     // it is number; numeric comparison
                     searchexpr = nodedataterm + " " + oper + " " + new Double(casevalue) + " ";
                 	Double doubleVal = new Double(casevalue);
                     searchexpr = nodedataterm + " " + oper + " ? ";
                     parameterValues.add(doubleVal);
                 } catch (NumberFormatException nfe) {
                 	// is it a date?
                 	try {
                     	// try ISO 8601 formats
                     	Calendar dataDateValue = DatatypeConverter.parseDateTime(casevalue);
                     	String lexicalString = DatatypeConverter.printDateTime(dataDateValue);
                     	//String lexicalString = DatatypeConverter.printDateTime(dataDateValue);
                         nodedataterm = "nodedatadate";
                 		searchexpr =
                 			nodedataterm + " " + oper + " '" + lexicalString + "' ";
                 			nodedataterm + " " + oper + " ? ";
                 		parameterValues.add(dataDateValue.getTime());
                 	} catch (Exception pe) {
                 		// these are characters; character comparison
                         searchexpr = nodedataterm + " " + oper + " '" + casevalue + "' ";
                         searchexpr = nodedataterm + " " + oper + " ? ";
                         parameterValues.add(casevalue);
+                	}
+                }
+            }
-...
         /**
          * 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();
             // does it contain a not equals?
             boolean notEqual = isNotEqualTerm();
             // keep track of parameter values
             List<Object> searchValues = new ArrayList<Object>();
             // get the general search expression
             String searchexpr = printSearchExprSQL();
             String searchexpr = printSearchExprSQL(searchValues);
             // add our parameter values
             parameterValues.addAll(searchValues);
             // to check xml_path_index can be used
             boolean usePathIndex = false;
-...
             			self.append("SELECT DISTINCT docid from xml_path_index WHERE");
             			self.append(" docid NOT IN (Select docid FROM xml_path_index WHERE ");
             			self.append(searchexpr);
             			self.append("AND path LIKE '" + pathexpr + "') ");
             			self.append("AND path LIKE ? ) ");
             			parameterValues.add(pathexpr);
+            		}
             		else
+            		{
             			//if this is in union group we need to use "OR" to modify query
             			self.append("("+searchexpr);
             			self.append("AND path LIKE '" + pathexpr + "') ");
             			self.append("AND path LIKE ?) ");
             			parameterValues.add(pathexpr);
+            		}
             	} else {
             		if (!inUnionGroup)
+            		{
             			self.append("SELECT DISTINCT docid FROM xml_path_index WHERE ");
             			self.append(searchexpr);
             			self.append("AND path LIKE '" + pathexpr + "' ");
             			self.append("AND path LIKE ? ");
             			parameterValues.add(pathexpr);
+            		}
             		else
+            		{
             			//if this is in union group we need to use "OR" to modify query
             			self.append("("+searchexpr);
             			self.append("AND path LIKE '" + pathexpr + "') ");
             			self.append("AND path LIKE ?) ");
             			parameterValues.add(pathexpr);
+            		}
+            	}
-...
                             // without attributes in path
                             self.append("AND parentnodeid IN ");
                             self.append(
                                 "(SELECT nodeid FROM xml_index WHERE path LIKE "
                                 + "'" + path + "') ");
                                 "(SELECT nodeid FROM xml_index WHERE path LIKE ?) ");
                             parameterValues.add(path);
                         } else {
                             // has a attribute in path
                             String attributeName = QuerySpecification
                                 .getAttributeName(pathexpr);
                             self.append(
                                 "AND nodetype LIKE 'ATTRIBUTE' AND nodename LIKE '"
                                 + attributeName + "' ");
                                 "AND nodetype LIKE 'ATTRIBUTE' AND nodename LIKE ? ");
                             parameterValues.add(attributeName);
                             // and the path expression includes element content other than
                             // just './' or '../'
                             if ( (!pathexpr.startsWith(QuerySpecification.
-...
                                 path = QuerySpecification
                                     .newPathExpressionWithOutAttribute(pathexpr);
                                 self.append(
                                     "(SELECT nodeid FROM xml_index WHERE path LIKE "
                                     + "'" + path + "') ");
                                     "(SELECT nodeid FROM xml_index WHERE path LIKE ?) ");
                                 parameterValues.add(path);
+                            }
+                        }
+                    }
                     else {
                         // without using XML Index; using nested statements instead
                     	// keep track of the values we add as prepared statement question marks (?)
                     	List<Object> nestedParameterValues = new ArrayList<Object>();
                         //self.append("AND parentnodeid IN ");
                     	self.append("AND ");
                         self.append(useNestedStatements(pathexpr));
                     	String nested = useNestedStatements(pathexpr, nestedParameterValues);
                         self.append(nested);
                     	// include them
                         parameterValues.addAll(nestedParameterValues);
+                    }
+                }
                 else if ( (value.trim()).equals("%")) {
-...
+        }
         public static String useNestedStatements(String pathexpr)
         public static String useNestedStatements(String pathexpr, List<Object> parameterValues)
+        {
             log.info("useNestedStatements()");
             log.info("pathexpr: " + pathexpr);
             String elementPrefix = " parentnodeid IN ";
-...
                 int predicateStart = -1;
                 int predicateEnd;
                 String node;
                 Vector predicates = new Vector();
                 Vector<String> predicates = new Vector<String>();
                 // extract predicates
                 predicateStart = path.indexOf(QuerySpecification.PREDICATE_START, predicateStart + 1);
-...
+                    }
+                }
                 nestedStmts.insert(0, "' ").insert(0, node).insert(0,
                         "(SELECT nodeid FROM xml_nodes WHERE nodename LIKE '");
                 nestedStmts.insert(0, "(SELECT nodeid FROM xml_nodes WHERE nodename LIKE ? ");
                 parameterValues.add(node);
                 // for the last statement: it is without " AND parentnodeid IN "
                 if (!path.equals(""))
                 if (!path.equals("")) {
                     nestedStmts.insert(0, "AND parentnodeid IN ");
+                }
                 if (predicates.size() > 0)
+                {
                     for (int n = 0; n < predicates.size(); n++)
+                    {
                         String predSQL = predicate2SQL((String) predicates.get(n));
                     	// keep track of the values we add as prepared statement question marks (?)
                     	List<Object> predicateParameterValues = new ArrayList<Object>();
                         String predSQL = predicate2SQL(predicates.get(n), predicateParameterValues);
                         if (predSQL.equals(""))
                         if (predSQL.equals("")) {
                             return "";
+                        }
                         // include the values
                         parameterValues.addAll(predicateParameterValues);
                         // include the sql
                         nestedStmts.append(predSQL).append(' ');
+                    }
+                }
-...
         /**
+         *
          */
         public static String predicate2SQL(String predicate)
         public static String predicate2SQL(String predicate, List<Object> predicateParameterValues)
+        {
             String path = predicate.trim();
             int equals = path.indexOf('=');
-...
                 if (literal != null)
+                {
                     sql.append("AND nodeid IN (SELECT parentnodeid FROM xml_nodes WHERE nodetype = 'TEXT' AND nodedata LIKE ")
                         .append(literal).append(")");
                         .append("?").append(")");
                     // remove single or double quotes
                     literal = literal.substring(1, literal.length() - 1);
                     // add to vlaues list
                     predicateParameterValues.add(literal);
+                }
+            }
             else
+            {
                 sql.append(
                         "AND nodeid IN (SELECT parentnodeid FROM xml_nodes WHERE nodetype = 'ATTRIBUTE' AND nodename LIKE '")
                         .append(path.substring(attribute + 1).trim()).append("' ");
                         "AND nodeid IN (SELECT parentnodeid FROM xml_nodes WHERE nodetype = 'ATTRIBUTE' AND nodename LIKE ? ");
                 // add to values
                 predicateParameterValues.add(path.substring(attribute + 1).trim());
                 if (literal != null)
+                {
                     sql.append("AND nodedata LIKE ").append(literal);
                     sql.append("AND nodedata LIKE ? ");
                     // remove single or double quotes
                     literal = literal.substring(1, literal.length() - 1);
                     // add to values list
                     predicateParameterValues.add(literal);
+                }
                 sql.append(")");
-...
                     path = "";
+                }
                 if (!node.equals(""))
                     sql.insert(0, "' ").insert(0, node)
                         .insert(0, "(SELECT parentnodeid FROM xml_nodes WHERE nodename LIKE '").append(") ");
                 if (!node.equals("")) {
                     sql.insert(0, "(SELECT parentnodeid FROM xml_nodes WHERE nodename LIKE ? ) ");
                     predicateParameterValues.add(node);
+                }
                 else if (!path.equals(""))
+                {
                     log.warn("predicate2SQL(): ");
-...
+        }
         /**
          * create a String description of the query that this instance represents.
          * This should become a way to get the XML serialization of the query.
          * Show a string representation of the query
          * @deprecated This should not be used for performing the query
          * because the parameter values are not bound in the raw SQL
          */
         public String toString()
+        {
             return this.printSQL(true);
         	// keep track of the values we add as prepared statement question marks (?)
         	List<Object> parameterValues = new ArrayList<Object>();
             return this.printSQL(true, parameterValues);
+        }
         /**

Also available in: Unified diff

Project

General

Profile

Metacat

Revision 6602

Added by ben leinfelder about 13 years ago