Project

General

Profile

Revision 6602

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

View differences:

test/edu/ucsb/nceas/metacattest/QuerySpecificationTest.java
27 27
import java.io.FileNotFoundException;
28 28
import java.io.FileReader;
29 29
import java.io.IOException;
30
import java.sql.PreparedStatement;
31
import java.util.ArrayList;
32
import java.util.List;
30 33

  
31 34
import junit.framework.Test;
32 35
import junit.framework.TestSuite;
33 36

  
34 37
import edu.ucsb.nceas.MCTestCase;
38
import edu.ucsb.nceas.metacat.DBQuery;
35 39
import edu.ucsb.nceas.metacat.QuerySpecification;
40
import edu.ucsb.nceas.metacat.database.DBConnectionPool;
36 41
import edu.ucsb.nceas.metacat.properties.PropertyService;
37 42
import edu.ucsb.nceas.metacat.util.MetacatUtil;
38 43
import edu.ucsb.nceas.utilities.PropertyNotFoundException;
......
49 54
    
50 55
    /** The utilities object for accessing property values */
51 56
    
52
    private static String selectionQuery = "SELECT docid,docname,doctype,date_created, date_updated, rev " +
53
    		"FROM xml_documents WHERE docid IN (((((SELECT DISTINCT docid FROM xml_nodes " +
54
    		"WHERE UPPER(nodedata) LIKE '%JONES%' ) ))))";
57
    private static String selectionQuery = 
58
    	"SELECT docid,docname,doctype,date_created, date_updated, rev " +
59
    	"FROM xml_documents WHERE docid IN (((((SELECT DISTINCT docid FROM xml_nodes " +
60
    	"WHERE UPPER(nodedata) LIKE %JONES% ) )))) ";
55 61
    /*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 " +
56 62
    		"('obfs.45337', 'obfs.45338', 'obfs.45346') AND xml_nodes.nodetype = 'TEXT' AND " +
57 63
    		"xml_nodes.rootnodeid = xml_documents.rootnodeid UNION select xml_nodes.docid, 'originator/individualName/surName' as " +
......
84 90
    		"IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'eml' AND parentnodeid = rootnodeid ) ) ) )  " +
85 91
    		"AND xml_nodes.docid in ('obfs.45337', 'obfs.45338', 'obfs.45346') AND xml_nodes.nodetype = 'TEXT' " +
86 92
    		"AND xml_nodes.rootnodeid = xml_documents.rootnodeid";*/
87
    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"+
88
                                    " ( (xml_index.nodeid=xml_nodes.parentnodeid AND xml_index.path IN ('dataset/title', 'originator/individualName/surName' , 'keyword' ) AND xml_nodes.nodetype = 'TEXT') "+
89
                                    "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'))) "+
90
                                    "AND xml_nodes.docid in ('obfs.45337', 'obfs.45338', 'obfs.45346')";
93
    private static String extendedQuery =
94
    		"select xml_nodes.docid, xml_index.path, xml_nodes.nodedata,  xml_nodes.parentnodeid, xml_nodes.nodetype " +
95
    		"FROM xml_index, xml_nodes " +
96
    		"WHERE ( (xml_index.nodeid=xml_nodes.parentnodeid AND xml_index.path IN ( dataset/title, originator/individualName/surName , keyword ) " +
97
    		"AND xml_nodes.nodetype = 'TEXT') "+
98
            "OR  (xml_index.nodeid=xml_nodes.nodeid AND ( xml_index.path IN ( /eml/@packageId, /eml/dataset/access/@authSystem , /eml/dataset/access/@order ) " +
99
            "AND xml_nodes.nodetype = 'ATTRIBUTE'))) "+
100
            "AND xml_nodes.docid in ('obfs.45337', 'obfs.45338', 'obfs.45346')";
91 101
    
92 102
    /* Initialize properties*/
93 103
    static
......
98 108
 	  }
99 109
  	  catch(Exception e)
100 110
  	  {
101
  		  System.err.println("Exception in initialize option in MetacatServletNetTest "+e.getMessage());
111
  		  System.err.println("Exception in initialize option in MetacatServletNetTest " + e.getMessage());
102 112
  	  }
103 113
    }
104 114
    
......
149 159
            QuerySpecification qspec = new QuerySpecification(xml, 
150 160
                    PropertyService.getProperty("xml.saxparser"), 
151 161
                    PropertyService.getProperty("document.accNumSeparator"));
152
            String query = qspec.printSQL(false);
153
           assertTrue(selectionQuery.equals(query.trim()));
154
        } catch (IOException e) {
155
            fail(e.getMessage());
156
        } catch (PropertyNotFoundException pnfe) {
157
            fail(pnfe.getMessage());
158
        }
162
            // keep track of parameter values
163
            List<Object> parameterValues = new ArrayList<Object>();
164
            String query = qspec.printSQL(false, parameterValues);
165
    		// fill in the values to really check the query string matches original/expected
166
    		PreparedStatement pstmt = DBConnectionPool.getDBConnection("queryGroupTest").prepareStatement(query);
167
    		pstmt = DBQuery.setPreparedStatementValues(parameterValues, pstmt);
168
    		String preparedQueryString = pstmt.toString();
169

  
170
    		System.out.println("Prepared query: " + preparedQueryString);
171
            System.out.println("Original query: " + selectionQuery);
172
            
173
            assertEquals(selectionQuery, preparedQueryString);
174
        	 
175
        } catch (Exception e) {
176
    		e.printStackTrace();
177
			fail(e.getMessage());
178
		}
159 179
    }
160 180

  
161 181
    /**
......
164 184
    public void testPrintExtendedSQL()
165 185
    {
166 186
        try {
167
            System.out.println("---- orginal EXT SQL  ------------------\n"+extendedQuery);
187
            System.out.println("---- orginal EXT SQL  ------------------");
188
            System.out.println(extendedQuery);
168 189
            QuerySpecification qspec = new QuerySpecification(xml, 
169 190
                    PropertyService.getProperty("xml.saxparser"), 
170 191
                    PropertyService.getProperty("document.accNumSeparator"));
192
            // keep track of parameter values
193
            List<Object> parameterValues = new ArrayList<Object>();
171 194
            String query = 
172 195
                    qspec.printExtendedSQL(
173
                            "'obfs.45337', 'obfs.45338', 'obfs.45346'", true);
174
            System.out.println("---- builded EXT SQL ------------------ \n"+query);
175
            assertTrue(extendedQuery.equals(query));
176
        } catch (IOException e) {
196
                            "'obfs.45337', 'obfs.45338', 'obfs.45346'", true, parameterValues);
197
            
198
            System.out.println("---- built EXT SQL ------------------");
199
            System.out.println(query);
200
            
201
            // fill in the values to really check the query string matches original/expected
202
    		PreparedStatement pstmt = DBConnectionPool.getDBConnection("queryGroupTest").prepareStatement(query);
203
    		pstmt = DBQuery.setPreparedStatementValues(parameterValues, pstmt);
204
    		String preparedQueryString = pstmt.toString();
205

  
206
    		System.out.println("Prepared query: " + preparedQueryString);
207
            System.out.println("Original query: " + extendedQuery);
208
            
209
            assertEquals(extendedQuery, preparedQueryString);
210
            
211
        } catch (Exception e) {
212
        	e.printStackTrace();
177 213
            fail(e.getMessage());
178
        } catch (PropertyNotFoundException pnfe) {
179
            fail(pnfe.getMessage());
180 214
        }
181 215
    }
182 216

  
test/edu/ucsb/nceas/metacattest/QueryGroupTest.java
23 23
 */
24 24
package edu.ucsb.nceas.metacattest;
25 25

  
26
import java.sql.PreparedStatement;
27
import java.util.ArrayList;
28
import java.util.List;
29

  
26 30
import junit.framework.Test;
27 31
import junit.framework.TestSuite;
28 32

  
29 33
import edu.ucsb.nceas.MCTestCase;
34
import edu.ucsb.nceas.metacat.DBQuery;
30 35
import edu.ucsb.nceas.metacat.QueryGroup;
31 36
import edu.ucsb.nceas.metacat.QueryTerm;
37
import edu.ucsb.nceas.metacat.database.DBConnectionPool;
32 38
import edu.ucsb.nceas.metacat.properties.PropertyService;
33 39
import edu.ucsb.nceas.metacat.util.MetacatUtil;
34 40

  
......
52 58
  		  System.err.println("Exception in initialize option in MetacatServletNetTest "+e.getMessage());
53 59
  	  }
54 60
    }
55
    private String query = "(SELECT DISTINCT docid FROM xml_path_index WHERE  (UPPER(nodedata) "+
56
                                            "LIKE '%LAND%' AND path IN ('dataset/title','geographicCoverage/boundingCoordinates/southBoundingCoordinate')) " +
57
                                            "OR ((UPPER(nodedata) LIKE '%JONES%' AND path LIKE 'organizationName') ) OR ((UPPER(nodedata) LIKE '%LAND %' AND path LIKE 'keyword') ) " +
58
                                            "OR ((UPPER(nodedata) LIKE '%DATOS%' AND path LIKE 'entityName') ) UNION ((SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) " +
59
                                            "LIKE '%VALUE1%' AND parentnodeid IN (SELECT nodeid FROM xml_index WHERE path LIKE 'path1') )  UNION " +
60
                                            "(SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) LIKE '%VALUE2%' AND parentnodeid IN " +
61
                                            "(SELECT nodeid FROM xml_index WHERE path LIKE 'path2') ) ))";
61
    /**
62
     * NOTE: there are no quotes around the values because we are comparing it to a PreparedStatement.toString()
63
     * String after binding the parameter values. Please trust that the PreparedStatement is correct.
64
     */
65
    private String query = 
66
    	"(SELECT DISTINCT docid FROM xml_path_index WHERE  (UPPER(nodedata) "+
67
        "LIKE %LAND% AND path IN ( dataset/title, geographicCoverage/boundingCoordinates/southBoundingCoordinate )) " +
68
        "OR ((UPPER(nodedata) LIKE %JONES% AND path LIKE organizationName) ) OR ((UPPER(nodedata) LIKE %LAND % AND path LIKE keyword) ) " +
69
        "OR ((UPPER(nodedata) LIKE %DATOS% AND path LIKE entityName) ) UNION ((SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) " +
70
        "LIKE %VALUE1% AND parentnodeid IN (SELECT nodeid FROM xml_index WHERE path LIKE path1) )  UNION " +
71
        "(SELECT DISTINCT docid FROM xml_nodes WHERE UPPER(nodedata) LIKE %VALUE2% AND parentnodeid IN " +
72
        "(SELECT nodeid FROM xml_index WHERE path LIKE path2) ) ))";
62 73
    
63 74
    /**
64 75
     * Constructor to build the test
......
120 131
    	group.addChild(term4);
121 132
    	group.addChild(term5);
122 133
    	group.addChild(child);
123
    	String queryString = group.printSQL(true);
124
    	assertTrue(queryString.equals(query)); 
125
    	
134
    	// keep track of parameter values
135
        List<Object> parameterValues = new ArrayList<Object>();
136
    	String queryString = group.printSQL(true, parameterValues);
137
    	try {
138
    		// fill in the values to really check the query string matches original/expected
139
    		PreparedStatement pstmt = DBConnectionPool.getDBConnection("queryGroupTest").prepareStatement(queryString);
140
    		pstmt = DBQuery.setPreparedStatementValues(parameterValues, pstmt);
141
    		String preparedQueryString = pstmt.toString();
142
    		System.out.println("Prepared query: " + preparedQueryString);
143
    		System.out.println("Original query: " + query);
144

  
145
    		assertEquals(query, preparedQueryString);
146
    	} catch (Exception e) {
147
    		e.printStackTrace();
148
			fail(e.getMessage());
149
		}
126 150
    }
127 151
    
128 152
    
src/edu/ucsb/nceas/metacat/QuerySpecification.java
32 32
import java.io.IOException;
33 33
import java.io.Reader;
34 34
import java.io.StringReader;
35
import java.util.ArrayList;
35 36
import java.util.Enumeration;
37
import java.util.List;
36 38
import java.util.Stack;
37 39
import java.util.Vector;
38 40

  
......
739 741
    /**
740 742
     * create a SQL serialization of the query that this instance represents
741 743
     */
742
    public String printSQL(boolean useXMLIndex)
744
    public String printSQL(boolean useXMLIndex, List<Object> parameterValues)
743 745
    {
744 746

  
745 747
        StringBuffer self = new StringBuffer();
......
752 754
        // Get the query from the QueryGroup and check
753 755
        // if no query has been returned
754 756
        String queryFromQueryGroup;
757
        // keep track of the values we add as prepared statement question marks (?)
758
        List<Object> groupValues = new ArrayList<Object>();
755 759
        if (query != null) {
756
        	queryFromQueryGroup = query.printSQL(useXMLIndex);
760
        	queryFromQueryGroup = query.printSQL(useXMLIndex, groupValues);
757 761
        } else {
758 762
        	queryFromQueryGroup = "";
759 763
        }
......
763 767
            self.append(" docid IN (");
764 768
            self.append(queryFromQueryGroup);
765 769
            self.append(") ");
770
            // add the parameter values
771
            parameterValues.addAll(groupValues);
766 772
        }
767 773

  
768 774
        // Add SQL to filter for doctypes requested in the query
......
853 859
     * @param useXMLIndex a boolean flag indicating whether to search using
854 860
     *            xml_index
855 861
     */
856
    public String printExtendedSQL(String doclist, boolean useXMLIndex)
862
    public String printExtendedSQL(String doclist, boolean useXMLIndex, List<Object> allValues)
857 863
    {
858
        if (useXMLIndex && !containsPredicates)
859
        {
860
            return printExtendedSQL(doclist);
864
    	
865
    	// keep track of the values we add as prepared statement question marks (?)
866
    	//List<Object> allValues = new ArrayList<Object>();
867
    	
868
        if (useXMLIndex && !containsPredicates) {
869
        	// keep track of the values we add as prepared statement question marks (?)
870
        	List<Object> parameterValues = new ArrayList<Object>();
871
        	String query = printExtendedSQL(doclist, parameterValues);
872
        	// add parameter values to our running list
873
        	allValues.addAll(parameterValues);
874
        	return query;
861 875
        }
862 876
        else
863 877
        {
864 878
            StringBuffer self = new StringBuffer();
865 879
            boolean firstfield = true;
880
            // keep track of the values we add as prepared statement question marks (?)
881
        	List<Object> parameterValues = new ArrayList<Object>();
866 882
            // first part comes from fields without  predicates 
867
            String queryFromWithoutPrecidates = printExtendedSQL(doclist);
868
             if (queryFromWithoutPrecidates != null)
869
             {
883
            String queryFromWithoutPrecidates = printExtendedSQL(doclist, parameterValues);
884
            // add parameter values to our running list
885
        	allValues.addAll(parameterValues);
886
        	if (queryFromWithoutPrecidates != null) {
870 887
            	 // it has return fields without predicate
871 888
            	 self.append(queryFromWithoutPrecidates);
872 889
            	 firstfield = false;
873
             }
890
        	}
874 891
            //put the returnfields into the query
875 892
            //the for loop allows for multiple fields
876 893
            for (int i = 0; i <   returnFieldListWithPredicates.size(); i++)
......
884 901
                    self.append(" UNION ");
885 902
                }
886 903
                String path  = (String)  returnFieldListWithPredicates.elementAt(i);
904
                path = path.replaceAll("'", "''");
905
                // TODO: can we use prepared statements for this?
906
                allValues.add(path);
887 907
                self.append("select xml_nodes.docid, ");
888
                self.append("'"+ path.replaceAll("'", "''") + "' as path, ");
908
                self.append("? as path, ");
889 909
                self.append("xml_nodes.nodedata, ");
890 910
                self.append("xml_nodes.parentnodeid, ");
891 911
                self.append("xml_nodes.nodetype ");
892 912
                //self.append("from xml_nodes, xml_documents ");
893 913
                self.append("from xml_nodes ");
894 914
                self.append("where ");
895
                self.append(QueryTerm.useNestedStatements(path));
915
                // keep track of the values we add as prepared statement question marks (?)
916
            	List<Object> nestedParameterValues = new ArrayList<Object>();
917
                String nestedQuery = QueryTerm.useNestedStatements(path, nestedParameterValues);
918
                self.append(nestedQuery);
919
                // add to the running total
920
                allValues.addAll(nestedParameterValues);
896 921

  
897 922
                self.append(" AND xml_nodes.docid in (");
898 923
                self.append(doclist);
......
951 976
     * @param unaccessableNodePair the node pairs (start id and end id)
952 977
     *            which this user should not access
953 978
     */
954
    private String printExtendedSQL(String doclist) {
979
    private String printExtendedSQL(String doclist, List<Object> values) {
980
    	
981
    	// keep track of the values we add as prepared statement question marks (?)
982
    	//List<Object> values = new ArrayList<Object>();
983
    	
955 984
        logMetacat.debug("QuerySpecification.printExtendedSQL - in printExtendedSQL");
956 985
        StringBuffer self = new StringBuffer();
957
        Vector elementVector = new Vector();
958
        Vector attributeVector = new Vector();
986
        Vector<String> elementVector = new Vector<String>();
987
        Vector<String> attributeVector = new Vector<String>();
959 988

  
960 989
        boolean usePathIndex = true;
961 990

  
......
994 1023
        	return null;
995 1024
        }
996 1025

  
997
        if(usePathIndex){
1026
        if (usePathIndex){
998 1027
            self.append("select docid, path, nodedata, parentnodeid, null as nodetype ");
999
            self.append("from xml_path_index where path in( '");
1028
            self.append("from xml_path_index where path in ( ");
1000 1029

  
1001 1030
            boolean firstfield = true;
1002 1031
            //put the returnfields into the query
......
1007 1036
            	returnField = returnField.replaceAll("'", "''");
1008 1037
                if (firstfield) {
1009 1038
                    firstfield = false;
1010
                    self.append(returnField);
1011
                    self.append("' ");
1039
                    self.append("? ");
1040
                	values.add(returnField);
1012 1041
                }
1013 1042
                else {
1014
                    self.append(", '");
1015
                    self.append(returnField);
1016
                    self.append("' ");
1043
                    self.append(", ? ");
1044
                    values.add(returnField);
1017 1045
                }
1018 1046
            }
1019 1047
            self.append(") AND docid in (");
......
1026 1054
            self.append("xml_nodes.nodetype ");
1027 1055
            self.append("FROM xml_index, xml_nodes WHERE (");
1028 1056
           
1029

  
1030 1057
            boolean firstElement = true;
1031 1058
            boolean firstAttribute = true;
1032 1059
            //put the returnfields into the query
......
1037 1064
	            	String path = (String) elementVector.elementAt(i);
1038 1065
	                if (firstElement) {
1039 1066
	                	firstElement = false;
1040
	                	self.append(" (xml_index.nodeid=xml_nodes.parentnodeid AND xml_index.path IN ('");
1041
	                    self.append(path);
1042
	                    self.append("'");
1067
	                	self.append(" (xml_index.nodeid=xml_nodes.parentnodeid AND xml_index.path IN ( ");
1068
	                    self.append("?");
1069
	                    values.add(path);
1043 1070
	                 }
1044 1071
	                else 
1045 1072
	                {
1046
	                    self.append(", '");
1047
	                    self.append(path);
1048
	                    self.append("' ");
1073
	                    self.append(", ? ");
1074
	                    values.add(path);
1049 1075
	                }
1050 1076
	            }
1051 1077
	            self.append(") AND xml_nodes.nodetype = 'TEXT')");
......
1063 1089
                		{
1064 1090
                			self.append(" OR ");
1065 1091
                		}
1066
            			self.append(" (xml_index.nodeid=xml_nodes.nodeid AND ( xml_index.path IN ( '");
1067
	                    self.append(path);
1068
	                    self.append("'");
1092
            			self.append(" (xml_index.nodeid=xml_nodes.nodeid AND ( xml_index.path IN ( ");
1093
	                    self.append("?");
1094
	                    values.add(path);
1069 1095
            		}
1070 1096
            		else 
1071 1097
	                {
1072
	                    self.append(", '");
1073
	                    self.append(path);
1074
	                    self.append("' ");
1098
	                    self.append(", ? ");
1099
	                    values.add(path);
1075 1100
	                }
1076 1101
            	}
1077 1102
            	self.append(") AND xml_nodes.nodetype = 'ATTRIBUTE'))");
src/edu/ucsb/nceas/metacat/DBQuery.java
45 45
import java.sql.PreparedStatement;
46 46
import java.sql.ResultSet;
47 47
import java.sql.SQLException;
48
import java.sql.Timestamp;
49
import java.util.ArrayList;
50
import java.util.Date;
48 51
import java.util.Enumeration;
49 52
import java.util.Hashtable;
50 53
import java.util.Iterator;
54
import java.util.List;
51 55
import java.util.StringTokenizer;
52 56
import java.util.Vector;
53 57
import java.util.zip.ZipEntry;
......
582 586
                                      Vector givenDocids, String qformat)
583 587
                                      throws Exception
584 588
    {
589
    	// keep track of the values we add as prepared statement question marks (?)
590
  	  List<Object> parameterValues = new ArrayList<Object>();
591
  	  
585 592
      StringBuffer resultsetBuffer = new StringBuffer();
586 593
      String query = null;
587 594
      int count = 0;
......
623 630
       * and contruct a simpler query based on a 
624 631
       * list of docids rather than a bunch of subselects
625 632
       */
633
      // keep track of the values we add as prepared statement question marks (?)
634
	  List<Object> docidValues = new ArrayList<Object>();
626 635
      if ( givenDocids == null || givenDocids.size() == 0 ) {
627
          query = qspec.printSQL(useXMLIndex);
636
          query = qspec.printSQL(useXMLIndex, docidValues);
637
          parameterValues.addAll(docidValues);
628 638
      } else {
629 639
    	  // condition for the docids
630 640
    	  StringBuffer docidCondition = new StringBuffer();
......
645 655
              query = query + docidCondition.toString();
646 656
    	  } else {
647 657
    		  // start with the keyword query, but add conditions
648
              query = qspec.printSQL(useXMLIndex);
658
              query = qspec.printSQL(useXMLIndex, docidValues);
659
              parameterValues.addAll(docidValues);
649 660
              String myOperator = "";
650 661
              if (!query.endsWith("WHERE")) {
651 662
	              if (operator.equalsIgnoreCase(QueryGroup.UNION)) {
......
702 713
      
703 714
      startTime = System.currentTimeMillis() / 1000;
704 715
      pstmt = dbconn.prepareStatement(query);
716
      
717
      // set all the values we have collected 
718
      pstmt = setPreparedStatementValues(parameterValues, pstmt);
719
      
720
      logMetacat.debug("Prepared statement after setting parameter values: " + pstmt.toString());
705 721
      rs = pstmt.executeQuery();
706 722

  
707 723
      double queryExecuteTime = System.currentTimeMillis() / 1000;
......
1236 1252
          boolean tableHasRows = false;
1237 1253
        
1238 1254

  
1255
          // keep track of parameter values
1256
          List<Object> parameterValues = new ArrayList<Object>();
1239 1257
           String extendedQuery =
1240
               qspec.printExtendedSQL(doclist.toString(), useXMLIndex);
1258
               qspec.printExtendedSQL(doclist.toString(), useXMLIndex, parameterValues);
1241 1259
           logMetacat.info("DBQuery.addReturnfield - Extended query: " + extendedQuery);
1242 1260

  
1243 1261
           if(extendedQuery != null){
1244 1262
//        	   long extendedQueryStart = System.currentTimeMillis();
1245 1263
               pstmt = dbconn.prepareStatement(extendedQuery);
1264
               // set the parameter values
1265
               pstmt = DBQuery.setPreparedStatementValues(parameterValues, pstmt);
1246 1266
               //increase dbconnection usage count
1247 1267
               dbconn.increaseUsageCount(1);
1248 1268
               pstmt.execute();
......
1430 1450
		   queryResultCache.clear();
1431 1451
	   }
1432 1452
   }
1453
   
1454
   /**
1455
    * Set the parameter values in the prepared statement using instrospection
1456
    * of the given value objects
1457
    * @param parameterValues
1458
    * @param pstmt
1459
    * @return
1460
    * @throws SQLException
1461
    */
1462
   public static PreparedStatement setPreparedStatementValues(List<Object> parameterValues, PreparedStatement pstmt) throws SQLException {
1463
	   // set all the values we have collected 
1464
      int parameterIndex = 1;
1465
      for (Object parameterValue: parameterValues) {
1466
    	  if (parameterValue instanceof String) {
1467
    		  pstmt.setString(parameterIndex, (String) parameterValue);
1468
    	  }
1469
    	  else if (parameterValue instanceof Integer) {
1470
    		  pstmt.setInt(parameterIndex, (Integer) parameterValue);
1471
    	  }
1472
    	  else if (parameterValue instanceof Float) {
1473
    		  pstmt.setFloat(parameterIndex, (Float) parameterValue);
1474
    	  }
1475
    	  else if (parameterValue instanceof Double) {
1476
    		  pstmt.setDouble(parameterIndex, (Double) parameterValue);
1477
    	  }
1478
    	  else if (parameterValue instanceof Date) {
1479
    		  pstmt.setTimestamp(parameterIndex, new Timestamp(((Date) parameterValue).getTime()));
1480
    	  }
1481
    	  else {
1482
    		  pstmt.setObject(parameterIndex, parameterValue);
1483
    	  }
1484
    	  parameterIndex++;
1485
      }
1486
      return pstmt;
1487
   }
1433 1488

  
1434 1489

  
1435 1490
    /*
src/edu/ucsb/nceas/metacat/QueryGroup.java
35 35
import edu.ucsb.nceas.metacat.util.SystemUtil;
36 36

  
37 37
import java.io.*;
38
import java.util.ArrayList;
38 39
import java.util.Hashtable;
40
import java.util.List;
39 41
import java.util.Stack;
40 42
import java.util.Vector;
41 43
import java.util.Enumeration;
......
51 53
                                                  //will hold query terms which has same search value.
52 54
    private Vector queryTermsInPathIndex = null; //this vector holds query terms without same value
53 55
                                                                                 // and search path is in path index.
54
    private Vector queryTerms = null;//this vector only holds query terms without same search value.
56
    private Vector<QueryTerm> queryTerms = null;//this vector only holds query terms without same search value.
55 57
                                                             // and search path is NOT in path index.
56 58
    private Vector queryGroupsChildren = null;
57 59
    private static Logger logMetacat = Logger.getLogger(QueryGroup.class);
......
70 72
      children = new Vector();
71 73
      queryTermsWithSameValue = new Vector();
72 74
      queryTermsInPathIndex = new Vector(); 
73
      queryTerms = new Vector();
75
      queryTerms = new Vector<QueryTerm>();
74 76
      queryGroupsChildren = new Vector();
75 77
    }
76 78

  
......
110 112
    /**
111 113
     * create a SQL serialization of the query that this instance represents
112 114
     */
113
    public String printSQL(boolean useXMLIndex) {
115
    public String printSQL(boolean useXMLIndex, List<Object> parameterValues) {
116
    	
114 117
      StringBuffer self = new StringBuffer();
115 118
      StringBuffer queryString = new StringBuffer();
116 119

  
......
118 121
      
119 122
      if (!queryTermsWithSameValue.isEmpty() || !queryTermsInPathIndex.isEmpty())
120 123
      {
121
    	  String pathIndexQueryString = printSQLStringInPathIndex();
124
    	  // keep track of the values we add as prepared statement question marks (?)
125
    	  List<Object> groupValues = new ArrayList<Object>();
126
    	  String pathIndexQueryString = printSQLStringInPathIndex(groupValues);
127
    	  parameterValues.addAll(groupValues);
122 128
    	  queryString.append(pathIndexQueryString);
123 129
    	  if (queryString != null)
124 130
    	  {
......
129 135
      for (int i=0; i<queryGroupsChildren.size(); i++)
130 136
      {
131 137
      
132
        
133
            QueryGroup qg = (QueryGroup)queryGroupsChildren.elementAt(i);
134
        	String queryGroupSQL = qg.printSQL(useXMLIndex);
135
        	logMetacat.info("In QueryGroup.printSQL.. found a QueryGroup: " 
136
        			+ queryGroupSQL);       	
138
    	  // keep track of the values we add as prepared statement question marks (?)
139
    	  List<Object> childrenValues = new ArrayList<Object>();
140
    	  // get the group
141
    	  QueryGroup qg = (QueryGroup) queryGroupsChildren.elementAt(i);
142
    	  String queryGroupSQL = qg.printSQL(useXMLIndex, childrenValues);
143
    	  logMetacat.info("In QueryGroup.printSQL.. found a QueryGroup: " + queryGroupSQL);       	
137 144
        	if (first) {
138 145
        		first = false;
139 146
        	} else {
......
141 148
                    queryString.append(" " + operator + " ");
142 149
        		}
143 150
        	}
151
        	// add the sql
144 152
   		  	queryString.append(queryGroupSQL);
153
   		  	// add the parameter values
154
   		  	parameterValues.addAll(childrenValues);
145 155
   		  	
146
   		  	// count percerntage number
156
   		  	// count percentage number
147 157
   		  	int count = qg.getPercentageSymbolCount();
148 158
   		  	countPercentageSearchItem = countPercentageSearchItem + count;
149 159
      }
150 160
      
151 161
      for (int i=0; i<queryTerms.size(); i++)
152 162
      {
153
           QueryTerm qt = (QueryTerm)queryTerms.elementAt(i);
154
           String termQueryString = qt.printSQL(useXMLIndex);
155
       	   logMetacat.info("In QueryGroup.printSQL.. found a QueryGroup: " 
156
        			+ termQueryString);
157
           if(!(qt.getSearchMode().equals("contains") && qt.getValue().equals("%"))){
163
    	  // keep track of the values we add as prepared statement question marks (?)
164
    	  List<Object> termValues = new ArrayList<Object>();
165
    	  // get the term
166
    	  QueryTerm qt = (QueryTerm)queryTerms.elementAt(i);
167
    	  String termQueryString = qt.printSQL(useXMLIndex, termValues);
168
    	  logMetacat.info("In QueryGroup.printSQL.. found a QueryGroup: " + termQueryString);
169
           if (!(qt.getSearchMode().equals("contains") && qt.getValue().equals("%"))){
158 170
        	   if (first) {
159 171
                   first = false;
160 172
               } else {
......
162 174
                       queryString.append(" " + operator + " ");
163 175
                   }
164 176
               }
177
        	   // include the sql
165 178
               queryString.append(termQueryString);
166
           
179
               // include the parameter values
180
               parameterValues.addAll(termValues);
181
               
167 182
           // count percerntage number
168 183
           int count = qt.getPercentageSymbolCount();
169 184
           countPercentageSearchItem = countPercentageSearchItem + count;
......
191 206
     * we still use "OR" to replace UNION action (we only handle union operator in the query group).
192 207
     * 
193 208
     */
194
    private String printSQLStringInPathIndex()
209
    private String printSQLStringInPathIndex(List<Object> parameterValues)
195 210
    {
196 211
    	String sql ="";
197 212
    	String value ="";
......
221 236
		        	
222 237
					sqlBuff.append(" (");
223 238
		        	
239
					// keep track of parameter values
240
			        List<Object> searchValues = new ArrayList<Object>();
241
			        
224 242
		        	// get the general search criteria (no path info)
225
		        	String searchTermSQL = term1.printSearchExprSQL();
243
		        	String searchTermSQL = term1.printSearchExprSQL(searchValues);
244
		        	
245
		        	// add the SQL
226 246
					sqlBuff.append(searchTermSQL);
227 247
					
228
					sqlBuff.append("AND path IN (");
248
					// add parameter values
249
					parameterValues.addAll(searchValues);
250
					
251
					sqlBuff.append("AND path IN ( ");
229 252

  
230 253
		    		//gets every path in query term object
231 254
		    		for (int i=0; i<queryTermVector.size(); i++)
......
238 261
		    				if (first)
239 262
		    				{
240 263
		    					first = false;
241
		    					sqlBuff.append("'");
242
		    					sqlBuff.append(path);
243
		    					sqlBuff.append("'");
244
		    					
264
		    					sqlBuff.append("?");
265
		    					parameterValues.add(path);
245 266
		    				}
246 267
		    				else
247 268
		    				{
248
		    					sqlBuff.append(",'");
249
		    					sqlBuff.append(path);
250
		    					sqlBuff.append("'");
269
		    					sqlBuff.append(", ?");
270
		    					parameterValues.add(path);
251 271
		    				}
252 272
		    				index++;
253 273
		     				if (value != null && (value.equals("%") || value.equals("%%%")))
......
256 276
		                    }
257 277
	    			     }
258 278
	    		    }
259
	    		    sqlBuff.append("))");
279
	    		    sqlBuff.append(" ))");
260 280
	    	
261 281
	    	    }
262 282
	    	}
......
273 293
		    				 sqlBuff.append(" "+"OR"+" ");
274 294
		    			 }
275 295
		    			 sqlBuff.append("(");
276
	    				 sqlBuff.append(term.printSQL(true));
296
		    			 // keep track of the parameter values for this sql
297
		    			 List<Object> termParameterValues = new ArrayList<Object>();
298
		    			 String termSQL = term.printSQL(true, termParameterValues);
299
	    				 sqlBuff.append(termSQL);
277 300
	    				 sqlBuff.append(")");
301
	    				 // add the param values
302
	    				 parameterValues.addAll(termParameterValues);
278 303
	    				 index++;
279 304
	    			}
280 305
    			}
......
338 363
							// new query term into search value in handle new
339 364
							// query term");
340 365
							// find a target which has same search value
341
							Vector newSameValueVector = new Vector();
366
							Vector<QueryTerm> newSameValueVector = new Vector<QueryTerm>();
342 367
							newSameValueVector.add(term);
343 368
							newSameValueVector.addElement(newTerm);
344 369
							queryTermsWithSameValue.add(newSameValueVector);
......
349 374
					// no same search value was found in queryTerms.
350 375
					// then we need search queryTermsWithSameValue
351 376
					for (int i = 0; i < queryTermsWithSameValue.size(); i++) {
352
						Vector sameValueVec = (Vector) queryTermsWithSameValue
353
								.elementAt(i);
377
						Vector sameValueVec = (Vector) queryTermsWithSameValue.elementAt(i);
354 378
						// we only compare the first query term
355 379
						QueryTerm term = (QueryTerm) sameValueVec.elementAt(0);
356 380
						if (term != null && term.hasSameSearchValue(newTerm)) {
src/edu/ucsb/nceas/metacat/QueryTerm.java
29 29

  
30 30
package edu.ucsb.nceas.metacat;
31 31

  
32
import java.util.ArrayList;
32 33
import java.util.Calendar;
34
import java.util.List;
33 35
import java.util.Vector;
34 36

  
35 37
import javax.xml.bind.DatatypeConverter;
......
151 153
    }
152 154

  
153 155
    
154
    public String printSearchExprSQL() {
156
    public String printSearchExprSQL(List<Object> parameterValues) {
155 157

  
156 158
        // Uppercase the search string if case match is not important
157 159
        String casevalue = null;
......
168 170
        // Add appropriate wildcards to search string
169 171
        String searchexpr = null;
170 172
        if (searchmode.equals("starts-with")) {
171
            searchexpr = nodedataterm + " LIKE '" + casevalue + "%' ";
173
            searchexpr = nodedataterm + " LIKE ? ";
174
            parameterValues.add(casevalue + "%");
172 175
        } else if (searchmode.equals("ends-with")) {
173
            searchexpr = nodedataterm + " LIKE '%" + casevalue + "' ";
176
            searchexpr = nodedataterm + " LIKE ? ";
177
            parameterValues.add("%" + casevalue + "%");
174 178
        } else if (searchmode.equals("contains")) {
175 179
            if (!casevalue.equals("%")) {
176
                searchexpr = nodedataterm + " LIKE '%" + casevalue + "%' ";
180
                searchexpr = nodedataterm + " LIKE ? ";
181
                parameterValues.add("%" + casevalue + "%");
177 182
            } else {
178
                searchexpr = nodedataterm + " LIKE '" + casevalue + "' ";
183
                searchexpr = nodedataterm + " LIKE ? ";
184
                parameterValues.add(casevalue);
179 185
                // find percentage symbol
180 186
                percentageSymbol = true;
181 187
            }
182 188
        } else if (searchmode.equals("not-contains")) {
183 189
        	notEqual = true;
184
            searchexpr = nodedataterm + " LIKE '%" + casevalue + "%' ";
190
            searchexpr = nodedataterm + " LIKE ? ";
191
            parameterValues.add("%" + casevalue + "%");
185 192
        } else if (searchmode.equals("equals")) {
186
            searchexpr = nodedataterm + " = '" + casevalue + "' ";
193
            searchexpr = nodedataterm + " = ? ";
194
            parameterValues.add(casevalue);
187 195
        } else if (searchmode.equals("isnot-equal")) {
188 196
        	notEqual = true;
189
            searchexpr = nodedataterm + " = '" + casevalue + "' ";
197
            searchexpr = nodedataterm + " = ? ";
198
            parameterValues.add(casevalue);
190 199
        } else {
191 200
            String oper = null;
192 201
            if (searchmode.equals("greater-than")) {
......
211 220
            
212 221
            try {
213 222
                // it is number; numeric comparison
214
                searchexpr = nodedataterm + " " + oper + " " + new Double(casevalue) + " ";
223
            	Double doubleVal = new Double(casevalue);
224
                searchexpr = nodedataterm + " " + oper + " ? ";
225
                parameterValues.add(doubleVal);
215 226
            } catch (NumberFormatException nfe) {
216 227
            	// is it a date?
217 228
            	try {
218 229
                	// try ISO 8601 formats
219 230
                	Calendar dataDateValue = DatatypeConverter.parseDateTime(casevalue);
220
                	String lexicalString = DatatypeConverter.printDateTime(dataDateValue);
231
                	//String lexicalString = DatatypeConverter.printDateTime(dataDateValue);
221 232
                    nodedataterm = "nodedatadate";
222 233
            		searchexpr = 
223
            			nodedataterm + " " + oper + " '" + lexicalString + "' ";
234
            			nodedataterm + " " + oper + " ? ";
235
            		parameterValues.add(dataDateValue.getTime());
224 236
            	} catch (Exception pe) {
225 237
            		// these are characters; character comparison
226
                    searchexpr = nodedataterm + " " + oper + " '" + casevalue + "' ";
238
                    searchexpr = nodedataterm + " " + oper + " ? ";
239
                    parameterValues.add(casevalue);
227 240
            	}
228 241
            }
229 242
        }
......
245 258
    /**
246 259
     * create a SQL serialization of the query that this instance represents
247 260
     */
248
    public String printSQL(boolean useXMLIndex)
261
    public String printSQL(boolean useXMLIndex, List<Object> parameterValues)
249 262
    {
263
    	
250 264
        StringBuffer self = new StringBuffer();
251 265
        
252 266
        // does it contain a not equals?
253 267
        boolean notEqual = isNotEqualTerm();
254 268

  
269
        // keep track of parameter values
270
        List<Object> searchValues = new ArrayList<Object>();
271
        
255 272
        // get the general search expression
256
        String searchexpr = printSearchExprSQL();
273
        String searchexpr = printSearchExprSQL(searchValues);
274
        
275
        // add our parameter values
276
        parameterValues.addAll(searchValues);
257 277
       
258 278
        // to check xml_path_index can be used
259 279
        boolean usePathIndex = false;
......
277 297
        			self.append("SELECT DISTINCT docid from xml_path_index WHERE");
278 298
        			self.append(" docid NOT IN (Select docid FROM xml_path_index WHERE ");
279 299
        			self.append(searchexpr);
280
        			self.append("AND path LIKE '" + pathexpr + "') ");
300
        			self.append("AND path LIKE ? ) ");
301
        			parameterValues.add(pathexpr);
281 302
        		}
282 303
        		else
283 304
        		{
284 305
        			//if this is in union group we need to use "OR" to modify query
285 306
        			self.append("("+searchexpr);
286
        			self.append("AND path LIKE '" + pathexpr + "') ");
307
        			self.append("AND path LIKE ?) ");
308
        			parameterValues.add(pathexpr);
287 309
        		}
288 310
        	} else {
289 311
        		if (!inUnionGroup)
290 312
        		{
291 313
        			self.append("SELECT DISTINCT docid FROM xml_path_index WHERE ");
292 314
        			self.append(searchexpr);
293
        			self.append("AND path LIKE '" + pathexpr + "' ");
315
        			self.append("AND path LIKE ? ");
316
        			parameterValues.add(pathexpr);
294 317
        		}
295 318
        		else
296 319
        		{
297 320
        			//if this is in union group we need to use "OR" to modify query
298 321
        			self.append("("+searchexpr);
299
        			self.append("AND path LIKE '" + pathexpr + "') ");
322
        			self.append("AND path LIKE ?) ");
323
        			parameterValues.add(pathexpr);
300 324
        		}
301 325
        	}
302 326

  
......
319 343
                        // without attributes in path
320 344
                        self.append("AND parentnodeid IN ");
321 345
                        self.append(
322
                            "(SELECT nodeid FROM xml_index WHERE path LIKE "
323
                            + "'" + path + "') ");
346
                            "(SELECT nodeid FROM xml_index WHERE path LIKE ?) ");
347
                        parameterValues.add(path);
324 348
                    } else {
325 349
                        // has a attribute in path
326 350
                        String attributeName = QuerySpecification
327 351
                            .getAttributeName(pathexpr);
328 352
                        self.append(
329
                            "AND nodetype LIKE 'ATTRIBUTE' AND nodename LIKE '"
330
                            + attributeName + "' ");
353
                            "AND nodetype LIKE 'ATTRIBUTE' AND nodename LIKE ? ");
354
                        parameterValues.add(attributeName);
331 355
                        // and the path expression includes element content other than
332 356
                        // just './' or '../'
333 357
                        if ( (!pathexpr.startsWith(QuerySpecification.
......
341 365
                            path = QuerySpecification
342 366
                                .newPathExpressionWithOutAttribute(pathexpr);
343 367
                            self.append(
344
                                "(SELECT nodeid FROM xml_index WHERE path LIKE "
345
                                + "'" + path + "') ");
368
                                "(SELECT nodeid FROM xml_index WHERE path LIKE ?) ");
369
                            parameterValues.add(path);
346 370
                        }
347 371
                    }
348 372
                }
349 373
                else {
350 374
                    // without using XML Index; using nested statements instead
375
                	// keep track of the values we add as prepared statement question marks (?)
376
                	List<Object> nestedParameterValues = new ArrayList<Object>();
351 377
                    //self.append("AND parentnodeid IN ");
352 378
                	self.append("AND ");
353
                    self.append(useNestedStatements(pathexpr));
379
                	String nested = useNestedStatements(pathexpr, nestedParameterValues);		
380
                    self.append(nested);
381
                	// include them
382
                    parameterValues.addAll(nestedParameterValues);
354 383
                }
355 384
            }
356 385
            else if ( (value.trim()).equals("%")) {
......
377 406
    }
378 407

  
379 408
   
380
    public static String useNestedStatements(String pathexpr)
409
    public static String useNestedStatements(String pathexpr, List<Object> parameterValues)
381 410
    {
411
    	
382 412
        log.info("useNestedStatements()");
383 413
        log.info("pathexpr: " + pathexpr);
384 414
        String elementPrefix = " parentnodeid IN ";
......
400 430
            int predicateStart = -1;
401 431
            int predicateEnd;
402 432
            String node;
403
            Vector predicates = new Vector();
433
            Vector<String> predicates = new Vector<String>();
404 434

  
405 435
            // extract predicates
406 436
            predicateStart = path.indexOf(QuerySpecification.PREDICATE_START, predicateStart + 1);
......
458 488
                }
459 489
            }
460 490

  
461
            nestedStmts.insert(0, "' ").insert(0, node).insert(0,
462
                    "(SELECT nodeid FROM xml_nodes WHERE nodename LIKE '");
463

  
491
            nestedStmts.insert(0, "(SELECT nodeid FROM xml_nodes WHERE nodename LIKE ? ");
492
            parameterValues.add(node);
493
            
464 494
            // for the last statement: it is without " AND parentnodeid IN "
465
            if (!path.equals(""))
495
            if (!path.equals("")) {
466 496
                nestedStmts.insert(0, "AND parentnodeid IN ");
467

  
497
            }
498
            
468 499
            if (predicates.size() > 0)
469 500
            {
470 501
                for (int n = 0; n < predicates.size(); n++)
471 502
                {
472
                    String predSQL = predicate2SQL((String) predicates.get(n));
503
                	// keep track of the values we add as prepared statement question marks (?)
504
                	List<Object> predicateParameterValues = new ArrayList<Object>();
505
                    String predSQL = predicate2SQL(predicates.get(n), predicateParameterValues);
473 506

  
474
                    if (predSQL.equals(""))
507
                    if (predSQL.equals("")) {
475 508
                        return "";
476

  
509
                    }
510
                    
511
                    // include the values
512
                    parameterValues.addAll(predicateParameterValues);
513
                    // include the sql
477 514
                    nestedStmts.append(predSQL).append(' ');
478 515
                }
479 516
            }
......
536 573
    /**
537 574
     * 
538 575
     */
539
    public static String predicate2SQL(String predicate)
576
    public static String predicate2SQL(String predicate, List<Object> predicateParameterValues)
540 577
    {
541 578
        String path = predicate.trim();
542 579
        int equals = path.indexOf('=');
......
582 619
            if (literal != null)
583 620
            {
584 621
                sql.append("AND nodeid IN (SELECT parentnodeid FROM xml_nodes WHERE nodetype = 'TEXT' AND nodedata LIKE ")
585
                    .append(literal).append(")");
622
                    .append("?").append(")");
623
                // remove single or double quotes
624
                literal = literal.substring(1, literal.length() - 1);
625
                // add to vlaues list
626
                predicateParameterValues.add(literal);
586 627
            }
587 628
        }
588 629
        else
589 630
        {
590 631
            sql.append(
591
                    "AND nodeid IN (SELECT parentnodeid FROM xml_nodes WHERE nodetype = 'ATTRIBUTE' AND nodename LIKE '")
592
                    .append(path.substring(attribute + 1).trim()).append("' ");
632
                    "AND nodeid IN (SELECT parentnodeid FROM xml_nodes WHERE nodetype = 'ATTRIBUTE' AND nodename LIKE ? ");
633
            // add to values
634
            predicateParameterValues.add(path.substring(attribute + 1).trim());
593 635

  
594 636
            if (literal != null)
595 637
            {
596
                sql.append("AND nodedata LIKE ").append(literal);
638
                sql.append("AND nodedata LIKE ? ");
639
                // remove single or double quotes
640
                literal = literal.substring(1, literal.length() - 1);
641
                // add to values list
642
                predicateParameterValues.add(literal);
597 643
            }
598 644

  
599 645
            sql.append(")");
......
630 676
                path = "";
631 677
            }
632 678

  
633
            if (!node.equals(""))
634
                sql.insert(0, "' ").insert(0, node)
635
                    .insert(0, "(SELECT parentnodeid FROM xml_nodes WHERE nodename LIKE '").append(") ");
679
            if (!node.equals("")) {
680
                sql.insert(0, "(SELECT parentnodeid FROM xml_nodes WHERE nodename LIKE ? ) ");
681
                predicateParameterValues.add(node);
682
            }
636 683
            else if (!path.equals(""))
637 684
            {
638 685
                log.warn("predicate2SQL(): ");
......
655 702
    }
656 703

  
657 704
    /**
658
     * create a String description of the query that this instance represents.
659
     * This should become a way to get the XML serialization of the query.
705
     * Show a string representation of the query
706
     * @deprecated This should not be used for performing the query
707
     * because the parameter values are not bound in the raw SQL
660 708
     */
661 709
    public String toString()
662 710
    {
663 711

  
664
        return this.printSQL(true);
712
    	// keep track of the values we add as prepared statement question marks (?)
713
    	List<Object> parameterValues = new ArrayList<Object>();
714
        return this.printSQL(true, parameterValues);
665 715
    }
666 716
    
667 717
    /**

Also available in: Unified diff