Revision 6602
Added by ben leinfelder over 12 years ago
| 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
uses prepared statement parameter binding for queries
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5527