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