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