@Override public URI getEntityURI(ProjectEntity entity) { return URI.create( format( "urn:test:entity:%s:%d", entity.getProjectEntityType().name(), entity.id() ) ); }
@Override @Cacheable(cacheNames = "properties", key = "#typeName + #entityType.name() + #entityId.value") public TProperty loadProperty(String typeName, ProjectEntityType entityType, ID entityId) { return getFirstItem( String.format( "SELECT * FROM PROPERTIES WHERE TYPE = :type AND %s = :entityId", entityType.name() ), params("type", typeName).addValue("entityId", entityId.getValue()), (rs, rowNum) -> toProperty(rs) ); }
@Override public URI getEntityPage(ProjectEntity entity) { return page("entity:%s:%d", entity.getProjectEntityType().name(), entity.id()); }
@Override public Optional<Event> getLastEvent(ProjectEntityType entityType, ID entityId, EventType eventType, BiFunction<ProjectEntityType, ID, ProjectEntity> entityLoader, Function<String, EventType> eventTypeLoader) { return getOptional( format("SELECT * FROM EVENTS WHERE %s = :entityId AND EVENT_TYPE = :eventType ORDER BY ID DESC LIMIT 1", entityType.name()), params("entityId", entityId.get()).addValue("eventType", eventType.getId()), (ResultSet rs, int num) -> toEvent(rs, entityLoader, eventTypeLoader) ); }
@Override public Optional<Signature> getLastEventSignature(ProjectEntityType entityType, ID entityId, EventType eventType) { return getOptional( format("SELECT * FROM EVENTS WHERE %s = :entityId AND EVENT_TYPE = :eventType ORDER BY ID DESC LIMIT 1", entityType.name()), params("entityId", entityId.get()).addValue("eventType", eventType.getId()), (ResultSet rs, int num) -> readSignature(rs, "event_time", "event_user") ); }
@Override public Optional<EntityDataStoreRecord> getById(ProjectEntity entity, int id) { return getOptional( String.format( "SELECT * FROM ENTITY_DATA_STORE " + "WHERE %s = :entityId " + "AND ID = :id", entity.getProjectEntityType().name() ), params("id", id).addValue("entityId", entity.id()), (rs, rowNum) -> toEntityDataStoreRecord(entity, rs) ); }
@Override public int getCountByCategory(ProjectEntity entity, String category) { return getNamedParameterJdbcTemplate().queryForObject( String.format( "SELECT COUNT(*) FROM ENTITY_DATA_STORE " + "WHERE %s = :entityId " + "AND CATEGORY = :category ", entity.getProjectEntityType().name() ), params("entityId", entity.id()) .addValue("category", category), Integer.class ); }
@Override public void post(Event event) { StringBuilder sql = new StringBuilder("INSERT INTO EVENTS(EVENT_VALUES, EVENT_TIME, EVENT_USER, EVENT_TYPE, REF"); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("eventValues", writeJson(event.getValues())); params.addValue("eventTime", dateTimeForDB(event.getSignature().getTime())); params.addValue("eventUser", event.getSignature().getUser().getName()); params.addValue("eventType", event.getEventType().getId()); params.addValue("ref", event.getRef() != null ? event.getRef().name() : null); for (ProjectEntityType type : event.getEntities().keySet()) { sql.append(", ").append(type.name()); } sql.append(") VALUES (:eventValues, :eventTime, :eventUser, :eventType, :ref"); for (Map.Entry<ProjectEntityType, ProjectEntity> entry : event.getEntities().entrySet()) { ProjectEntityType type = entry.getKey(); ProjectEntity entity = entry.getValue(); String typeEntry = type.name().toLowerCase(); sql.append(", :").append(typeEntry); params.addValue(typeEntry, entity.id()); } sql.append(")"); getNamedParameterJdbcTemplate().update( sql.toString(), params ); }
@Override @CacheEvict(cacheNames = "properties", key = "#typeName + #entityType.name() + #entityId.value") public Ack deleteProperty(String typeName, ProjectEntityType entityType, ID entityId) { return Ack.one( getNamedParameterJdbcTemplate().update( String.format( "DELETE FROM PROPERTIES WHERE TYPE = :type AND %s = :entityId", entityType.name() ), params("type", typeName).addValue("entityId", entityId.getValue()) ) ); }
@Override public int getCountByCategoryAndName(ProjectEntity entity, String category, String name) { return getNamedParameterJdbcTemplate().queryForObject( String.format( "SELECT COUNT(*) FROM ENTITY_DATA_STORE " + "WHERE %s = :entityId " + "AND CATEGORY = :category " + "AND NAME = :name ", entity.getProjectEntityType().name() ), params("entityId", entity.id()) .addValue("category", category) .addValue("name", name), Integer.class ); }
@Override public List<Event> query(ProjectEntityType entityType, ID entityId, int offset, int count, BiFunction<ProjectEntityType, ID, ProjectEntity> entityLoader, Function<String, EventType> eventTypeLoader) { return getNamedParameterJdbcTemplate().query( format("SELECT * FROM EVENTS WHERE %s = :entityId", entityType.name()) + " ORDER BY ID DESC" + " LIMIT :count OFFSET :offset", params("entityId", entityId.get()) .addValue("count", count) .addValue("offset", offset), (rs, num) -> toEvent(rs, entityLoader, eventTypeLoader) ); }
@Override public void deleteByName(ProjectEntity entity, String category, String name) { getNamedParameterJdbcTemplate().update( String.format( "DELETE FROM ENTITY_DATA_STORE " + "WHERE %s = :entityId " + "AND CATEGORY = :category " + "AND NAME = :name", entity.getProjectEntityType().name() ), params("entityId", entity.id()) .addValue("category", category) .addValue("name", name) ); }
@Override public void deleteByGroup(ProjectEntity entity, String category, String groupName) { getNamedParameterJdbcTemplate().update( String.format( "DELETE FROM ENTITY_DATA_STORE " + "WHERE %s = :entityId " + "AND CATEGORY = :category " + "AND GROUPID = :groupId", entity.getProjectEntityType().name() ), params("entityId", entity.id()) .addValue("category", category) .addValue("groupId", groupName) ); }
private void buildCriteria(EntityDataStoreFilter filter, StringBuilder criteria, MapSqlParameterSource params) { // Entity if (filter.getEntity() != null) { criteria.append(String.format(" AND %s = :entityId", filter.getEntity().getProjectEntityType().name())); params.addValue("entityId", filter.getEntity().id()); } // Category if (StringUtils.isNotBlank(filter.getCategory())) { criteria.append(" AND CATEGORY = :category"); params.addValue("category", filter.getCategory()); } // Name if (StringUtils.isNotBlank(filter.getName())) { criteria.append(" AND NAME = :name"); params.addValue("name", filter.getName()); } // Group if (StringUtils.isNotBlank(filter.getGroup())) { criteria.append(" AND GROUPID = :group"); params.addValue("group", filter.getGroup()); } // Before time if (filter.getBeforeTime() != null) { criteria.append(" AND CREATION <= :beforeTime"); params.addValue("beforeTime", dateTimeForDB(filter.getBeforeTime())); } }
@Override public Optional<EntityDataStoreRecord> findLastByCategoryAndGroupAndName(ProjectEntity entity, String category, String groupName, String name) { return getLastByName( getNamedParameterJdbcTemplate().query( String.format( "SELECT * FROM ENTITY_DATA_STORE " + "WHERE %s = :entityId " + "AND CATEGORY = :category " + "AND GROUPID = :groupId " + "AND NAME = :name", entity.getProjectEntityType().name() ), params("entityId", entity.id()) .addValue("category", category) .addValue("groupId", groupName) .addValue("name", name), (rs, rowNum) -> toEntityDataStoreRecord(entity, rs) ) ).stream().findFirst(); }
@Override public List<EntityDataStoreRecord> findLastRecordsByNameInCategory(ProjectEntity entity, String category) { return getLastByName( getNamedParameterJdbcTemplate().query( String.format( "SELECT * FROM ENTITY_DATA_STORE " + "WHERE %s = :entityId " + "AND CATEGORY = :category " + "ORDER BY CREATION DESC, ID DESC", entity.getProjectEntityType().name() ), params("entityId", entity.id()) .addValue("category", category), (rs, rowNum) -> toEntityDataStoreRecord(entity, rs) ) ); }
@Override public List<EntityDataStoreRecord> getByCategory(ProjectEntity entity, String category, int offset, int page) { return getNamedParameterJdbcTemplate().query( String.format( "SELECT * FROM ENTITY_DATA_STORE " + "WHERE %s = :entityId " + "AND CATEGORY = :category " + "ORDER BY CREATION DESC, ID DESC " + "LIMIT :page OFFSET :offset", entity.getProjectEntityType().name() ), params("entityId", entity.id()) .addValue("category", category) .addValue("offset", offset) .addValue("page", page), (rs, rowNum) -> toEntityDataStoreRecord(entity, rs) ); }
@Override public List<Event> query(EventType eventType, ProjectEntityType entityType, ID entityId, int offset, int count, BiFunction<ProjectEntityType, ID, ProjectEntity> entityLoader, Function<String, EventType> eventTypeLoader) { return getNamedParameterJdbcTemplate().query( format("SELECT * FROM EVENTS WHERE %s = :entityId", entityType.name()) + " AND EVENT_TYPE = :eventType" + " ORDER BY ID DESC" + " LIMIT :count OFFSET :offset", params("entityId", entityId.get()) .addValue("eventType", eventType.getId()) .addValue("count", count) .addValue("offset", offset), (rs, num) -> toEvent(rs, entityLoader, eventTypeLoader) ); }
@Override public Optional<EntityDataStoreRecord> findLastByCategoryAndName(ProjectEntity entity, String category, String name, LocalDateTime beforeTime) { // SQL & parameters String sql = String.format( "SELECT * FROM ENTITY_DATA_STORE " + "WHERE %s = :entityId AND CATEGORY = :category AND NAME = :name ", entity.getProjectEntityType().name() ); MapSqlParameterSource params = params("entityId", entity.id()) .addValue("category", category) .addValue("name", name); // Time criteria if (beforeTime != null) { sql += "AND CREATION <= :beforeTime "; params = params.addValue("beforeTime", dateTimeForDB(beforeTime)); } // Ordering sql += "ORDER BY CREATION DESC, ID DESC LIMIT 1"; // Performs the query return getOptional( sql, params, (rs, rowNum) -> toEntityDataStoreRecord(entity, rs) ); }
@Override public List<EntityDataStoreRecord> getByCategoryAndName(ProjectEntity entity, String category, String name, int offset, int page) { return getNamedParameterJdbcTemplate().query( String.format( "SELECT * FROM ENTITY_DATA_STORE " + "WHERE %s = :entityId " + "AND CATEGORY = :category " + "AND NAME = :name " + "ORDER BY CREATION DESC, ID DESC " + "LIMIT :page OFFSET :offset", entity.getProjectEntityType().name() ), params("entityId", entity.id()) .addValue("category", category) .addValue("name", name) .addValue("offset", offset) .addValue("page", page), (rs, rowNum) -> toEntityDataStoreRecord(entity, rs) ); }