@Override public String getLoadAllRowsSql() { if (loadAllRowsSql == null) { loadAllRowsSql = String.format("SELECT %s, %s FROM %s", config.dataColumnName(), config.idColumnName(), getTableName()); } return loadAllRowsSql; }
@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 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 getSelectRowSql() { if (selectRowSql == null) { selectRowSql = String.format("SELECT %s, %s FROM %s WHERE %s = ?", config.idColumnName(), config.dataColumnName(), getTableName(), config.idColumnName()); } return selectRowSql; }
@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 getSelectRowSql() { if (selectRowSql == null) { selectRowSql = String.format("SELECT %s, %s FROM %s WHERE %s = cast(? as %s)", config.idColumnName(), config.dataColumnName(), getTableName(), config.idColumnName(), config.idColumnType()); } return selectRowSql; }
@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 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 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 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 getSelectRowSql() { if (selectRowSql == null) { selectRowSql = String.format("SELECT %s, %s FROM %s WHERE %s = convert(%s,?)", config.idColumnName(), config.dataColumnName(), getTableName(), config.idColumnName(), config.idColumnType()); } return selectRowSql; }
@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("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 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; }
@Override public String getUpsertRowSql() { if (upsertRowSql == null) { if (metaData.isSegmentedDisabled()) { upsertRowSql = String.format("MERGE INTO %1$s AS t " + "USING (SELECT ? %2$s, ? %3$s, ? %4$s) AS tmp " + "ON (t.%4$s = tmp.%4$s) " + "WHEN MATCHED THEN UPDATE SET t.%2$s = tmp.%2$s, t.%3$s = tmp.%3$s " + "WHEN NOT MATCHED THEN INSERT VALUES (tmp.%4$s, tmp.%2$s, tmp.%3$s)", this.getTableName(), config.dataColumnName(), config.timestampColumnName(), config.idColumnName()); } else { upsertRowSql = String.format("MERGE INTO %1$s AS t " + "USING (SELECT ? %2$s, ? %3$s, ? %4$s, ? %5$s) AS tmp " + "ON (t.%4$s = tmp.%4$s) " + "WHEN MATCHED THEN UPDATE SET t.%2$s = tmp.%2$s, t.%3$s = tmp.%3$s " + "WHEN NOT MATCHED THEN INSERT VALUES (tmp.%4$s, tmp.%2$s, tmp.%3$s, tmp.%5$s)", this.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); }