public String getDeleteExpiredRowsSql() {
if (deleteExpiredRowsSql == null) {
deleteExpiredRowsSql =
"DELETE FROM "
+ getTableName()
+ " WHERE "
+ config.timestampColumnName()
+ "< ? AND "
+ config.timestampColumnName()
+ "> 0";
}
return deleteExpiredRowsSql;
}
public String getUpdateRowSql() {
if (updateRowSql == null) {
switch (getDialect()) {
case SYBASE:
updateRowSql =
"UPDATE "
+ getTableName()
+ " SET "
+ config.dataColumnName()
+ " = ? , "
+ config.timestampColumnName()
+ "=? WHERE "
+ config.idColumnName()
+ " = convert("
+ config.idColumnType()
+ ","
+ "?)";
break;
case POSTGRES:
updateRowSql =
"UPDATE "
+ getTableName()
+ " SET "
+ config.dataColumnName()
+ " = ? , "
+ config.timestampColumnName()
+ "=? WHERE "
+ config.idColumnName()
+ " = cast(? as "
+ config.idColumnType()
+ ")";
break;
default:
updateRowSql =
"UPDATE "
+ getTableName()
+ " SET "
+ config.dataColumnName()
+ " = ? , "
+ config.timestampColumnName()
+ "=? WHERE "
+ config.idColumnName()
+ " = ?";
break;
}
}
return updateRowSql;
}
public void createTable(Connection conn) throws PersistenceException {
// removed CONSTRAINT clause as this causes problems with some databases, like Informix.
assertMandatoryElementsPresent();
String createTableDdl =
"CREATE TABLE "
+ getTableName()
+ "("
+ config.idColumnName()
+ " "
+ config.idColumnType()
+ " NOT NULL, "
+ config.dataColumnName()
+ " "
+ config.dataColumnType()
+ ", "
+ config.timestampColumnName()
+ " "
+ config.timestampColumnType()
+ ", PRIMARY KEY ("
+ config.idColumnName()
+ "))";
if (trace) {
log.tracef("Creating table with following DDL: '%s'.", createTableDdl);
}
executeUpdateSql(conn, createTableDdl);
}
public String getLoadNonExpiredAllRowsSql() {
if (loadAllNonExpiredRowsSql == null) {
loadAllNonExpiredRowsSql =
"SELECT "
+ config.dataColumnName()
+ ","
+ config.idColumnName()
+ ", "
+ config.timestampColumnName()
+ " FROM "
+ getTableName()
+ " WHERE "
+ config.timestampColumnName()
+ " > ? OR "
+ config.timestampColumnName()
+ " < 0";
}
return loadAllNonExpiredRowsSql;
}
public String getInsertRowSql() {
if (insertRowSql == null) {
insertRowSql =
"INSERT INTO "
+ getTableName()
+ " ("
+ config.dataColumnName()
+ ", "
+ config.timestampColumnName()
+ ", "
+ config.idColumnName()
+ ") VALUES(?,?,?)";
}
return insertRowSql;
}
public String getSelectExpiredRowsSql() {
if (selectExpiredRowsSql == null) {
selectExpiredRowsSql = getLoadAllRowsSql() + " WHERE " + config.timestampColumnName() + "< ?";
}
return selectExpiredRowsSql;
}