Project

General

Profile

« Previous | Next » 

Revision 7452

use dual query for query slicing - one for count, another for the actual records when requested.
https://redmine.dataone.org/issues/3065

View differences:

src/edu/ucsb/nceas/metacat/IdentifierManager.java
1424 1424
        int start, int count) 
1425 1425
        throws SQLException, PropertyNotFoundException, ServiceException {
1426 1426
        ObjectList ol = new ObjectList();
1427
        int total = 0;
1428 1427
        DBConnection dbConn = null;
1429 1428
        int serialNumber = -1;
1430 1429

  
1431 1430
        try {
1432
            String sql = "select guid, date_uploaded, rights_holder, checksum, "
1431
            String fieldSql = "select guid, date_uploaded, rights_holder, checksum, "
1433 1432
                    + "checksum_algorithm, origin_member_node, authoritive_member_node, "
1434 1433
                    + "date_modified, submitter, object_format, size from systemmetadata";
1435 1434
            
1436 1435
            // handle special case quickly
1437 1436
            String countSql = "select count(guid) from systemmetadata";
1438
            if (count == 0) {
1439
            	sql = countSql;
1440
            }
1437
            
1438
            // the clause
1439
            String whereClauseSql = "";
1441 1440

  
1442 1441
            boolean f1 = false;
1443 1442
            boolean f2 = false;
1444 1443
            boolean f3 = false;
1445 1444

  
1446 1445
            if (startTime != null) {
1447
                sql += " where systemmetadata.date_modified >= ?";
1446
                whereClauseSql += " where systemmetadata.date_modified >= ?";
1448 1447
                f1 = true;
1449 1448
            }
1450 1449

  
1451 1450
            if (endTime != null) {
1452 1451
                if (!f1) {
1453
                    sql += " where systemmetadata.date_modified < ?";
1452
                    whereClauseSql += " where systemmetadata.date_modified < ?";
1454 1453
                } else {
1455
                    sql += " and systemmetadata.date_modified < ?";
1454
                    whereClauseSql += " and systemmetadata.date_modified < ?";
1456 1455
                }
1457 1456
                f2 = true;
1458 1457
            }
1459 1458

  
1460 1459
            if (objectFormatId != null) {
1461 1460
                if (!f1 && !f2) {
1462
                    sql += " where object_format = ?";
1461
                    whereClauseSql += " where object_format = ?";
1463 1462
                } else {
1464
                    sql += " and object_format = ?";
1463
                    whereClauseSql += " and object_format = ?";
1465 1464
                }
1466 1465
                f3 = true;
1467 1466
            }
......
1469 1468
            if (!replicaStatus) {
1470 1469
                String currentNodeId = PropertyService.getInstance().getProperty("dataone.nodeId");
1471 1470
                if (!f1 && !f2 && !f3) {
1472
                    sql += " where authoritive_member_node != '" +
1471
                    whereClauseSql += " where authoritive_member_node != '" +
1473 1472
                        currentNodeId.trim() + "'";
1474 1473
                } else {
1475
                    sql += " and authoritive_member_node != '" +
1474
                    whereClauseSql += " and authoritive_member_node != '" +
1476 1475
                        currentNodeId.trim() + "'";
1477 1476
                }
1478 1477
            }
1479

  
1480
            // order the results for slicing ops
1481
            String finalQuery = null;
1482
            if (count != 0) {
1483
            	sql += " order by guid ";
1484
            	finalQuery = DatabaseService.getInstance().getDBAdapter().getPagedQuery(sql, start, count);
1485
            } else {
1486
            	finalQuery = sql;
1487
            }
1488 1478
            
1479
            // connection
1489 1480
            dbConn = DBConnectionPool.getDBConnection("IdentifierManager.querySystemMetadata");
1490 1481
            serialNumber = dbConn.getCheckOutSerialNumber();
1491
            PreparedStatement stmt = dbConn.prepareStatement(finalQuery);
1492 1482

  
1483
            // the field query
1484
            String orderBySql = " order by guid ";
1485
            String fieldQuery = fieldSql + whereClauseSql + orderBySql;
1486
            String finalQuery = DatabaseService.getInstance().getDBAdapter().getPagedQuery(fieldQuery, start, count);
1487
            PreparedStatement fieldStmt = dbConn.prepareStatement(finalQuery);
1488
            
1489
            // construct the count query and statment
1490
            String countQuery = countSql + whereClauseSql;
1491
            PreparedStatement countStmt = dbConn.prepareStatement(countQuery);
1492

  
1493 1493
            if (f1 && f2 && f3) {
1494
                stmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1495
                stmt.setTimestamp(2, new Timestamp(endTime.getTime()));
1496
                stmt.setString(3, objectFormatId.getValue());
1494
                fieldStmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1495
                fieldStmt.setTimestamp(2, new Timestamp(endTime.getTime()));
1496
                fieldStmt.setString(3, objectFormatId.getValue());
1497
                // count
1498
                countStmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1499
                countStmt.setTimestamp(2, new Timestamp(endTime.getTime()));
1500
                countStmt.setString(3, objectFormatId.getValue());
1497 1501
            } else if (f1 && f2 && !f3) {
1498
                stmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1499
                stmt.setTimestamp(2, new Timestamp(endTime.getTime()));
1502
                fieldStmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1503
                fieldStmt.setTimestamp(2, new Timestamp(endTime.getTime()));
1504
                // count
1505
                countStmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1506
                countStmt.setTimestamp(2, new Timestamp(endTime.getTime()));
1500 1507
            } else if (f1 && !f2 && f3) {
1501
                stmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1502
                stmt.setString(2, objectFormatId.getValue());
1508
                fieldStmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1509
                fieldStmt.setString(2, objectFormatId.getValue());
1510
                // count
1511
                countStmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1512
                countStmt.setString(2, objectFormatId.getValue());
1503 1513
            } else if (f1 && !f2 && !f3) {
1504
                stmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1514
                fieldStmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1515
                // count
1516
                countStmt.setTimestamp(1, new Timestamp(startTime.getTime()));
1505 1517
            } else if (!f1 && f2 && f3) {
1506
                stmt.setTimestamp(1, new Timestamp(endTime.getTime()));
1507
                stmt.setString(2, objectFormatId.getValue());
1518
                fieldStmt.setTimestamp(1, new Timestamp(endTime.getTime()));
1519
                fieldStmt.setString(2, objectFormatId.getValue());
1520
                // count
1521
                countStmt.setTimestamp(1, new Timestamp(endTime.getTime()));
1522
                countStmt.setString(2, objectFormatId.getValue());
1508 1523
            } else if (!f1 && !f2 && f3) {
1509
                stmt.setString(1, objectFormatId.getValue());
1524
                fieldStmt.setString(1, objectFormatId.getValue());
1525
                // count
1526
                countStmt.setString(1, objectFormatId.getValue());
1510 1527
            } else if (!f1 && f2 && !f3) {
1511
                stmt.setTimestamp(1, new Timestamp(endTime.getTime()));
1528
                fieldStmt.setTimestamp(1, new Timestamp(endTime.getTime()));
1529
                // count
1530
                countStmt.setTimestamp(1, new Timestamp(endTime.getTime()));
1512 1531
            }
1513 1532

  
1514
            // logMetacat.debug("LISTOBJECTS QUERY: " + stmt.toString());
1533
            // logMetacat.debug("LISTOBJECTS QUERY: " + fieldStmt.toString());
1515 1534

  
1516
            ResultSet rs = stmt.executeQuery();
1517
        	
1518
            // get the total object count
1519
            String totalQuery = "select count(*) from systemmetadata";
1520
            PreparedStatement statement = dbConn.prepareStatement(totalQuery);
1521
            ResultSet totalResult = statement.executeQuery();
1522
        	total = totalResult.getInt(1);
1523

  
1524
            // handle special count = 0 query here
1525
            if (count == 0) {
1526
            	ol.setStart(start);
1527
                ol.setCount(count);
1528
                ol.setTotal(total);
1535
            // get the total object count no matter what
1536
            int total = 0;
1537
            ResultSet totalResult = countStmt.executeQuery();
1538
            if (totalResult.next()) {
1539
            	total = totalResult.getInt(1);
1529 1540
            }
1530
            else {
1531 1541
            
1542
        	// set the totals
1543
        	ol.setStart(start);
1544
            ol.setCount(count);
1545
            ol.setTotal(total);
1546
            
1547
            // retrieve the actual records if requested
1548
            if (count != 0) {
1549
            	
1550
                ResultSet rs = fieldStmt.executeQuery();
1532 1551
	            while (rs.next()) {                
1533 1552
	                
1534 1553
	                String guid = rs.getString(1);
......
1580 1599
	                ol.addObjectInfo(oi);                    
1581 1600

  
1582 1601
	            }
1602
	            
1603
	            // set the actual count retrieved
1604
	            ol.setCount(ol.sizeObjectInfoList());
1583 1605
	
1584 1606
	        }
1585 1607
            
1586
            // set the objectList
1587
            ol.setStart(start);
1588
            ol.setCount(count);
1589
            ol.setTotal(ol.sizeObjectInfoList());
1590 1608
        }
1591 1609

  
1592 1610
        finally {

Also available in: Unified diff