private ArrayList theEntityArrayList = new ArrayList();
private String whereClause, orderClause;
private StorageObject storage;
- private int count, offset, limit;
+ private int offset, limit;
private int nextOffset = -1;
private int previousOffset = -1;
}
/**
- * Sets the number of rows that match the WHERE clause
- *
- * @param i The number of rows that match the WHERE clause
- */
- public void setCount(int i) {
- count = i;
- }
-
- /**
- * Returns the number of rows that match the WHERE clause
- *
- * @return The number of rows ...
- */
- public int getCount() {
- return count;
- }
-
- /**
* Sets the offset
*
* @param i The offset
}
/**
- * Returns the offset of the previous batch.
- *
- * @return offset of the previous batch
- */
- public int getPrevBatch() {
- return previousOffset;
- }
-
- /**
- * Returns whether there is a previous batch.
- *
- * @return true if yes, false if no
- */
- public boolean hasPrevBatch() {
- return (previousOffset >= 0);
- }
-
- /**
- * Returns the start index of the batch.
- *
- * @return
- */
- public int getFrom() {
- return offset+1;
- }
-
- /**
- * Returns the end index of the batch.
- *
- * @return
- */
- public int getTo() {
- if (hasNextBatch())
- return nextOffset;
- else
- return count;
- }
-
- /**
* Inserts an Entity into the EntityList.
*
* @param anEntity The entity to be inserted.
/**
* Implements database access.
*
- * @version $Id: Database.java,v 1.44.2.24 2005/01/09 20:37:09 zapata Exp $
+ * @version $Id: Database.java,v 1.44.2.25 2005/01/09 22:07:45 zapata Exp $
* @author rk
*
*/
// cause StringUtil.splitString puts in emptyString
if (anExtraTables!=null && ((String) anExtraTables.get(0)).trim().equals("")){
- logger.debug("+++ made anExtraTables to null!");
anExtraTables=null;
}
Statement statement = null;
ResultSet resultSet;
- int offsetCount = 0;
- int count = 0;
-
// build sql-statement
if ((aWhereClause != null) && (aWhereClause.trim().length() == 0)) {
aWhereClause = null;
}
- StringBuffer countSql =
- new StringBuffer("select count(*) from ").append(useTable);
StringBuffer selectSql =
new StringBuffer("select "+selection+" from ").append(useTable);
if (anExtraTables!=null) {
for (int i=0;i < anExtraTables.size();i++) {
if (!anExtraTables.get(i).equals("")) {
- countSql.append( ", " + anExtraTables.get(i));
selectSql.append( ", " + anExtraTables.get(i));
}
}
if (aWhereClause != null) {
selectSql.append(" where ").append(aWhereClause);
- countSql.append(" where ").append(aWhereClause);
}
if ((anOrderByClause != null) && !(anOrderByClause.trim().length() == 0)) {
}
if ((aLimit > -1) && (anOffset > -1)) {
- selectSql.append(" LIMIT ").append(aLimit).append(" OFFSET ").append(anOffset);
+ selectSql.append(" LIMIT ").append(aLimit+1).append(" OFFSET ").append(anOffset);
}
// execute sql
try {
connection = obtainConnection();
statement = connection.createStatement();
+ boolean hasMore = false;
// selecting...
resultSet = executeSql(statement, selectSql.toString());
if (resultSet != null) {
theReturnList = new EntityList();
Entity theResultEntity;
- while (resultSet.next()) {
+ int position = 0;
+ while (((aLimit == -1) || (position<aLimit)) && resultSet.next()) {
theResultEntity = makeEntityFromResultSet(resultSet);
theReturnList.add(theResultEntity);
- offsetCount++;
+ position++;
}
+ hasMore = resultSet.next();
resultSet.close();
}
- // making entitylist infos
- count = offsetCount;
-
if (theReturnList != null) {
// now we decide if we have to know an overall count...
- count = offsetCount;
-
- if ((aLimit > -1) && (anOffset > -1)) {
- if (offsetCount == aLimit) {
- resultSet = executeSql(statement, countSql.toString());
-
- if (resultSet != null) {
- if (resultSet.next()) {
- count = resultSet.getInt(1);
- }
-
- resultSet.close();
- }
- else {
- logger.error("Could not count: " + countSql);
- }
- }
- }
-
- theReturnList.setCount(count);
theReturnList.setOffset(anOffset);
theReturnList.setWhere(aWhereClause);
theReturnList.setOrder(anOrderByClause);
theReturnList.setPrevBatch(anOffset - aLimit);
}
- if ((anOffset + offsetCount) < count) {
+ if (hasMore) {
theReturnList.setNextBatch(anOffset + aLimit);
}
.append(") values (").append(v).append(")");
String sql = sqlBuf.toString();
- logger.info("INSERT: " + sql);
+ logQueryBefore(sql);
con = obtainConnection();
con.setAutoCommit(false);
pstmt = con.prepareStatement(sql);
}
sql.append(" where id=").append(id);
- logger.info("UPDATE: " + sql);
+ logQueryBefore(sql.toString());
try {
con = obtainConnection();
String sql =
"delete from " + mainTable + " where " + primaryKeyField + "='" + id + "'";
- logger.debug("DELETE " + sql);
+ logQueryBefore(sql);
try {
con = obtainConnection();
stmt = con.createStatement();
public ResultSet executeSql(Statement stmt, String sql)
throws StorageObjectFailure, SQLException {
ResultSet rs;
+ logQueryBefore(sql);
long startTime = System.currentTimeMillis();
-
try {
rs = stmt.executeQuery(sql);
- logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
+ logQueryAfter(sql, (System.currentTimeMillis() - startTime));
}
catch (SQLException e) {
- logger.error(e.getMessage() +"\n" + (System.currentTimeMillis() - startTime) + "ms. for: " + sql);
+ logQueryError(sql, (System.currentTimeMillis() - startTime), e);
throw e;
}
*/
public int getSize(String mainTablePrefix, List extraTables, String where) throws SQLException, StorageObjectFailure {
- long startTime = System.currentTimeMillis();
-
String useTable = mainTable;
if (mainTablePrefix!=null && mainTablePrefix.trim().length()>0) {
useTable+=" "+mainTablePrefix;
Connection con = null;
Statement stmt = null;
int result = 0;
+ logQueryBefore(countSql.toString());
+ long startTime = System.currentTimeMillis();
try {
con = obtainConnection();
finally {
freeConnection(con, stmt);
}
- logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + countSql);
+ logQueryAfter(countSql.toString(), (System.currentTimeMillis() - startTime));
return result;
}
public int executeUpdate(Statement stmt, String sql)
throws StorageObjectFailure, SQLException {
int rs;
+
+ logQueryBefore(sql);
long startTime = System.currentTimeMillis();
try {
rs = stmt.executeUpdate(sql);
- logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
+ logQueryAfter(sql, (System.currentTimeMillis() - startTime));
}
catch (SQLException e) {
- logger.error("Failed: " + (System.currentTimeMillis() - startTime) + "ms. for: " + sql);
+ logQueryError(sql, (System.currentTimeMillis() - startTime), e);
throw e;
}
public int executeUpdate(String sql)
throws StorageObjectFailure, SQLException {
int result = -1;
- long startTime = System.currentTimeMillis();
Connection con = null;
PreparedStatement pstmt = null;
+ logQueryBefore(sql);
+ long startTime = System.currentTimeMillis();
try {
con = obtainConnection();
pstmt = con.prepareStatement(sql);
result = pstmt.executeUpdate();
+ logQueryAfter(sql, System.currentTimeMillis() - startTime);
}
catch (Throwable e) {
- logger.error("Database.executeUpdate(" + sql + "): " + e.getMessage());
+ logQueryError(sql, System.currentTimeMillis() - startTime, e);
throw new StorageObjectFailure("Database.executeUpdate(" + sql + "): " + e.getMessage(), e);
}
finally {
freeConnection(con, pstmt);
}
-
- logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
return result;
}
}
}
+ private void logQueryBefore(String aQuery) {
+ logger.debug("about to perform QUERY " + aQuery);
+// (new Throwable()).printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
+ }
+
+ private void logQueryAfter(String aQuery, long aTime) {
+ logger.info("QUERY " + aQuery + " took " + aTime + "ms.");
+ }
+
+ private void logQueryError(String aQuery, long aTime, Throwable anException) {
+ logger.error("QUERY " + aQuery + " took " + aTime + "ms, but threw exception " + anException.toString());
+ }
+
/**
* a small wrapper class that allows us to store the DB connection resources
* that the BlobInputStream is using and free them upon closing of the stream