@Override public String getCountNonExpiredRowsSql() { if (countRowsSql == null) { countRowsSql = "SELECT COUNT(*) FROM " + getTableName() + " WHERE " + config.timestampColumnName() + " < 0 OR " + config.timestampColumnName() + " > ?"; } return countRowsSql; }
@Override public String getSelectOnlyExpiredRowsSql() { if (deleteExpiredRowsSql == null) { deleteExpiredRowsSql = String.format("%1$s WHERE %2$s < ? AND %2$s > 0", getLoadAllRowsSql(), config.timestampColumnName()); } return deleteExpiredRowsSql; }
@Override public String getSelectExpiredBucketsSql() { if (selectExpiredRowsSql == null) { selectExpiredRowsSql = String.format("%s WHERE %s < ?", getLoadAllRowsSql(), config.timestampColumnName()); } return selectExpiredRowsSql; }
@Override public String getCountNonExpiredRowsSqlForSegments(int numSegments) { StringBuilder stringBuilder = new StringBuilder("SELECT COUNT(*) FROM "); stringBuilder.append(getTableName()); // Note the timestamp or is surrounded with parenthesis stringBuilder.append(" WHERE ("); stringBuilder.append(config.timestampColumnName()); stringBuilder.append(" > ? OR "); stringBuilder.append(config.timestampColumnName()); stringBuilder.append(" < 0) AND "); stringBuilder.append(config.segmentColumnName()); stringBuilder.append(" IN (?"); for (int i = 1; i < numSegments; ++i) { stringBuilder.append(",?"); } stringBuilder.append(")"); return stringBuilder.toString(); }
@Override public String getLoadNonExpiredRowsSqlForSegments(int numSegments) { StringBuilder stringBuilder = new StringBuilder("SELECT "); stringBuilder.append(config.dataColumnName()); stringBuilder.append(", "); stringBuilder.append(config.idColumnName()); stringBuilder.append(" FROM "); stringBuilder.append(getTableName()); // Note the timestamp or is surrounded with parenthesis stringBuilder.append(" WHERE ("); stringBuilder.append(config.timestampColumnName()); stringBuilder.append(" > ? OR "); stringBuilder.append(config.timestampColumnName()); stringBuilder.append(" < 0) AND "); stringBuilder.append(config.segmentColumnName()); stringBuilder.append(" IN (?"); for (int i = 1; i < numSegments; ++i) { stringBuilder.append(",?"); } stringBuilder.append(")"); return stringBuilder.toString(); }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { upsertRowSql = String.format("%1$s ON CONFLICT (%2$s) DO UPDATE SET %3$s = EXCLUDED.%3$s, %4$s = EXCLUDED.%4$s", getInsertRowSql(), config.idColumnName(), config.dataColumnName(), config.timestampColumnName()); } return upsertRowSql; } }
@Override public String getUpdateRowSql() { if (updateRowSql == null) { updateRowSql = String.format("UPDATE %s SET %s = ? , %s = ? WHERE %s = ?", getTableName(), config.timestampColumnName(), config.dataColumnName(), config.idColumnName()); } return updateRowSql; }
@Override public String getUpdateRowSql() { if (updateRowSql == null) { updateRowSql = String.format("UPDATE %s SET %s = ? , %s = ? WHERE %s = ?", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } return updateRowSql; }
@Override public String getLoadNonExpiredAllRowsSql() { if (loadAllNonExpiredRowsSql == null) { loadAllNonExpiredRowsSql = String.format("SELECT %1$s, %2$s, %3$s FROM %4$s WHERE %3$s > ? OR %3$s < 0", config.dataColumnName(), config.idColumnName(), config.timestampColumnName(), getTableName()); } return loadAllNonExpiredRowsSql; }
@Override public String getUpdateRowSql() { if (updateRowSql == null) { updateRowSql = String.format("UPDATE %s SET %s = ? , %s = ? WHERE %s = cast(? as %s)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.idColumnType()); } return updateRowSql; }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { // Assumes that config.idColumnName is the primary key if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("%1$s ON DUPLICATE KEY UPDATE %2$s = VALUES(%2$s), %3$s = VALUES(%3$s)", getInsertRowSql(), config.dataColumnName(), config.timestampColumnName()); } else { upsertRowSql = String.format("%1$s ON DUPLICATE KEY UPDATE %2$s = VALUES(%2$s), %3$s = VALUES(%3$s), %4$s = VALUES(%4$s)", getInsertRowSql(), config.dataColumnName(), config.timestampColumnName(), config.segmentColumnName()); } } return upsertRowSql; } }
@Override public String getInsertRowSql() { if (insertRowSql == null) { if (metaData.isSegmentedDisabled()) { insertRowSql = String.format("INSERT INTO %s (%s,%s,%s) VALUES (?,?,?)", getTableName(), config.idColumnName(), config.timestampColumnName(), config.dataColumnName()); } else { insertRowSql = String.format("INSERT INTO %s (%s,%s,%s,%s) VALUES (?,?,?,?)", getTableName(), config.idColumnName(), config.timestampColumnName(), config.dataColumnName()); } } return insertRowSql; }
@Override public String getUpdateRowSql() { if (updateRowSql == null) { updateRowSql = String.format("UPDATE %s SET %s = ? , %s = ? WHERE %s = convert(%s,?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.idColumnType()); } return updateRowSql; }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("MERGE INTO %1$s (%2$s, %3$s, %4$s) KEY(%4$s) VALUES(?, ?, ?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { upsertRowSql = String.format("MERGE INTO %1$s (%2$s, %3$s, %4$s, %5$s) KEY(%4$s) VALUES(?, ?, ?, ?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.segmentColumnName()); } } return upsertRowSql; }
@Override public String getInsertRowSql() { if (insertRowSql == null) { if (metaData.isSegmentedDisabled()) { insertRowSql = String.format("INSERT INTO %s (%s,%s,%s) VALUES (?,?,?)", getTableName(), config.idColumnName(), config.timestampColumnName(), config.dataColumnName()); } else { insertRowSql = String.format("INSERT INTO %s (%s,%s,%s,%s) VALUES (?,?,?,?)", getTableName(), config.idColumnName(), config.timestampColumnName(), config.dataColumnName(), config.segmentColumnName()); } } return insertRowSql; }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("INSERT OR REPLACE INTO %s (%s, %s, %s) VALUES (?, ?, ?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { upsertRowSql = String.format("INSERT OR REPLACE INTO %s (%s, %s, %s, %s) VALUES (?, ?, ?, ?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.segmentColumnName()); } } return upsertRowSql; } }
@Override public String getInsertRowSql() { if (insertRowSql == null) { if (metaData.isSegmentedDisabled()) { insertRowSql = String.format("INSERT INTO %s (%s,%s,%s) VALUES (?,?,?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { insertRowSql = String.format("INSERT INTO %s (%s,%s,%s,%s) VALUES (?,?,?,?)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.segmentColumnName()); } } return insertRowSql; }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("MERGE INTO %1$s AS t " + "USING (SELECT * FROM TABLE (VALUES (?,?,?))) AS tmp(%4$s, %3$s, %2$s) " + "ON t.%4$s = tmp.%4$s " + "WHEN MATCHED THEN UPDATE SET (t.%2$s, t.%3$s) = (tmp.%2$s, tmp.%3$s) " + "WHEN NOT MATCHED THEN INSERT (t.%4$s, t.%3$s, t.%2$s) VALUES (tmp.%4$s, tmp.%3$s, tmp.%2$s)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { upsertRowSql = String.format("MERGE INTO %1$s AS t " + "USING (SELECT * FROM TABLE (VALUES (?,?,?,?))) AS tmp(%4$s, %3$s, %2$s, %5$s) " + "ON t.%4$s = tmp.%4$s " + "WHEN MATCHED THEN UPDATE SET (t.%2$s, t.%3$s, t.%5$s) = (tmp.%2$s, tmp.%3$s, tmp.%5$s) " + "WHEN NOT MATCHED THEN INSERT (t.%4$s, t.%3$s, t.%2$s, t.%5$s) VALUES (tmp.%4$s, tmp.%3$s, tmp.%2$s, tmp.%5$s)", getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName(), config.segmentColumnName()); } } return upsertRowSql; }
public void createTable(Connection conn) throws PersistenceException { if (cacheName == null || cacheName.trim().length() == 0) throw new PersistenceException("cacheName needed in order to create table"); String ddl; if (metaData.isSegmentedDisabled()) { ddl = String.format("CREATE TABLE %1$s (%2$s %3$s NOT NULL, %4$s %5$s NOT NULL, %6$s %7$s NOT NULL, PRIMARY KEY (%2$s))", getTableName(), config.idColumnName(), config.idColumnType(), config.dataColumnName(), config.dataColumnType(), config.timestampColumnName(), config.timestampColumnType()); } else { ddl = String.format("CREATE TABLE %1$s (%2$s %3$s NOT NULL, %4$s %5$s NOT NULL, %6$s %7$s NOT NULL, %8$s %9$s NOT NULL, PRIMARY KEY (%2$s))", getTableName(), config.idColumnName(), config.idColumnType(), config.dataColumnName(), config.dataColumnType(), config.timestampColumnName(), config.timestampColumnType(), config.segmentColumnName(), config.segmentColumnType()); } if (log.isTraceEnabled()) { log.tracef("Creating table with following DDL: '%s'.", ddl); } executeUpdateSql(conn, ddl); }
@Override public void start() throws PersistenceException { if (config.createOnStart()) { Connection conn = null; try { conn = connectionFactory.getConnection(); if (!tableExists(conn)) { createTable(conn); } createIndex(conn, timestampIndexExt, config.timestampColumnName()); if (!metaData.isSegmentedDisabled()) { createIndex(conn, segmentIndexExt, config.segmentColumnName()); } } finally { connectionFactory.releaseConnection(conn); } } }