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