Revision 6602
Added by ben leinfelder over 12 years ago
| 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