public String getDeleteRowSql() {
if (deleteRowSql == null) {
switch (getDialect()) {
case SYBASE:
deleteRowSql =
"DELETE FROM "
+ getTableName()
+ " WHERE "
+ config.idColumnName()
+ " = convert("
+ config.idColumnType()
+ ","
+ "?)";
break;
case POSTGRES:
deleteRowSql =
"DELETE FROM "
+ getTableName()
+ " WHERE "
+ config.idColumnName()
+ " = cast(? as "
+ config.idColumnType()
+ ")";
break;
default:
deleteRowSql =
"DELETE FROM " + getTableName() + " WHERE " + config.idColumnName() + " = ?";
break;
}
}
return deleteRowSql;
}
public String getLoadAllRowsSql() {
if (loadAllRowsSql == null) {
loadAllRowsSql =
"SELECT "
+ config.dataColumnName()
+ ","
+ config.idColumnName()
+ " FROM "
+ getTableName();
}
return loadAllRowsSql;
}
public String getDeleteExpiredRowsSql() {
if (deleteExpiredRowsSql == null) {
deleteExpiredRowsSql =
"DELETE FROM "
+ getTableName()
+ " WHERE "
+ config.timestampColumnName()
+ "< ? AND "
+ config.timestampColumnName()
+ "> 0";
}
return deleteExpiredRowsSql;
}
public String getInsertRowSql() {
if (insertRowSql == null) {
insertRowSql =
"INSERT INTO "
+ getTableName()
+ " ("
+ config.dataColumnName()
+ ", "
+ config.timestampColumnName()
+ ", "
+ config.idColumnName()
+ ") VALUES(?,?,?)";
}
return insertRowSql;
}
public String getLoadAllKeysStringSql() {
if (loadAllKeysStringSql == null) {
loadAllKeysStringSql =
String.format("SELECT %s FROM %s", config.idColumnName(), getTableName());
}
return loadAllKeysStringSql;
}
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 void stop() throws PersistenceException {
if (config.dropOnExit()) {
Connection conn = null;
try {
conn = connectionFactory.getConnection();
dropTable(conn);
} finally {
connectionFactory.releaseConnection(conn);
}
}
}
public void start(ConnectionFactory connectionFactory) throws PersistenceException {
this.connectionFactory = connectionFactory;
if (config.createOnStart()) {
Connection conn = null;
try {
conn = this.connectionFactory.getConnection();
if (!tableExists(conn, getTableName())) {
createTable(conn);
}
} finally {
this.connectionFactory.releaseConnection(conn);
}
}
}
public String getSelectIdRowSql() {
if (selectIdRowSql == null) {
switch (getDialect()) {
case SYBASE:
selectIdRowSql =
"SELECT "
+ config.idColumnName()
+ " FROM "
+ getTableName()
+ " WHERE "
+ config.idColumnName()
+ " = convert("
+ config.idColumnType()
+ ","
+ "?)";
break;
case POSTGRES:
selectIdRowSql =
"SELECT "
+ config.idColumnName()
+ " FROM "
+ getTableName()
+ " WHERE "
+ config.idColumnName()
+ " = cast(? as "
+ config.idColumnType()
+ ")";
break;
default:
selectIdRowSql =
"SELECT "
+ config.idColumnName()
+ " FROM "
+ getTableName()
+ " WHERE "
+ config.idColumnName()
+ " = ?";
break;
}
}
return selectIdRowSql;
}
/**
* For DB queries the fetch size will be set on {@link java.sql.ResultSet#setFetchSize(int)}. This
* is optional parameter, if not specified will be defaulted to {@link #DEFAULT_FETCH_SIZE}.
*/
public int getFetchSize() {
return config.fetchSize();
}
/**
* When doing repetitive DB inserts this will be batched according to this parameter. This is an
* optional parameter, and if it is not specified it will be defaulted to {@link
* #DEFAULT_BATCH_SIZE}. Guaranteed to be a power of two.
*/
public int getBatchSize() {
return config.batchSize();
}
/**
* For DB queries the fetch size will be set on {@link java.sql.ResultSet#setFetchSize(int)}. This
* is optional parameter, if not specified will be defaulted to {@link #DEFAULT_FETCH_SIZE}.
*/
public int getFetchSize() {
return getDialect() == DatabaseType.MYSQL ? Integer.MIN_VALUE : config.fetchSize();
}
public String getLoadSomeRowsSql() {
if (loadSomeRowsSql == null) {
// this stuff is going to be database specific!!
// see
// http://stackoverflow.com/questions/595123/is-there-an-ansi-sql-alternative-to-the-mysql-limit-keyword
switch (getDialect()) {
case ORACLE:
loadSomeRowsSql =
String.format(
"SELECT %s, %s FROM (SELECT %s, %s FROM %s) WHERE ROWNUM <= ?",
config.dataColumnName(),
config.idColumnName(),
config.dataColumnName(),
config.idColumnName(),
getTableName());
break;
case DB2:
case DB2_390:
case DERBY:
loadSomeRowsSql =
String.format(
"SELECT %s, %s FROM %s FETCH FIRST ? ROWS ONLY",
config.dataColumnName(), config.idColumnName(), getTableName());
break;
case INFORMIX:
case INTERBASE:
case FIREBIRD:
loadSomeRowsSql =
String.format(
"SELECT FIRST ? %s, %s FROM %s",
config.dataColumnName(), config.idColumnName(), getTableName());
break;
case SQL_SERVER:
loadSomeRowsSql =
String.format(
"SELECT TOP (?) %s, %s FROM %s",
config.dataColumnName(), config.idColumnName(), getTableName());
break;
case ACCESS:
case HSQL:
case SYBASE:
loadSomeRowsSql =
String.format(
"SELECT TOP ? %s, %s FROM %s",
config.dataColumnName(), config.idColumnName(), getTableName());
break;
default:
// the MySQL-style LIMIT clause (works for PostgreSQL too)
loadSomeRowsSql =
String.format(
"SELECT %s, %s FROM %s LIMIT ?",
config.dataColumnName(), config.idColumnName(), getTableName());
break;
}
}
return loadSomeRowsSql;
}
public TableName getTableName() {
if (tableName == null) {
tableName = new TableName(getIdentifierQuoteString(), config.tableNamePrefix(), cacheName);
}
return tableName;
}
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 String getSelectExpiredRowsSql() {
if (selectExpiredRowsSql == null) {
selectExpiredRowsSql = getLoadAllRowsSql() + " WHERE " + config.timestampColumnName() + "< ?";
}
return selectExpiredRowsSql;
}
