com.datastax.driver.core.querybuilder.Select Java Examples

/**
 * Create all prepared statements use in this class for metrics_locator
 */
private void createPreparedStatements()  {

    // create a generic select statement for retrieving from metrics_locator
    Select.Where select = QueryBuilder
            .select()
            .all()
            .from( CassandraModel.CF_METRICS_LOCATOR_NAME )
            .where( eq ( KEY, bindMarker() ));
    getValue = DatastaxIO.getSession().prepare( select );

    // create a generic insert statement for inserting into metrics_locator
    Insert insert = QueryBuilder.insertInto( CassandraModel.CF_METRICS_LOCATOR_NAME)
            .using(ttl(TenantTtlProvider.LOCATOR_TTL))
            .value(KEY, bindMarker())
            .value(COLUMN1, bindMarker())
            .value(VALUE, bindMarker());
    putValue = DatastaxIO.getSession()
            .prepare(insert)
            .setConsistencyLevel( ConsistencyLevel.LOCAL_ONE );
}
 

private Iterable<Row> queryPartitionKeysLegacyWithMultipleQueries(CassandraTable table, List<Set<Object>> filterPrefixes)
{
    CassandraTableHandle tableHandle = table.getTableHandle();
    List<CassandraColumnHandle> partitionKeyColumns = table.getPartitionKeyColumns();

    Set<List<Object>> filterCombinations = Sets.cartesianProduct(filterPrefixes);

    ImmutableList.Builder<Row> rowList = ImmutableList.builder();
    for (List<Object> combination : filterCombinations) {
        Select partitionKeys = selectDistinctFrom(tableHandle, partitionKeyColumns);
        addWhereClause(partitionKeys.where(), partitionKeyColumns, combination);

        List<Row> resultRows = execute(partitionKeys).all();
        if (resultRows != null && !resultRows.isEmpty()) {
            rowList.addAll(resultRows);
        }
    }

    return rowList.build();
}
 

@Inject
public CassandraSearcher(CassandraSession session, @Named("newtsMetricRegistry") MetricRegistry registry, ContextConfigurations contextConfigurations) {
    m_session = checkNotNull(session, "session argument");
    m_searchTimer = registry.timer(name("search", "search"));
    m_contextConfigurations = checkNotNull(contextConfigurations, "contextConfigurations argument");

    Select select = QueryBuilder.select(Schema.C_TERMS_RESOURCE).from(Schema.T_TERMS);
    select.where(eq(Schema.C_TERMS_CONTEXT, bindMarker(Schema.C_TERMS_CONTEXT)))
          .and(  eq(Schema.C_TERMS_FIELD, bindMarker(Schema.C_TERMS_FIELD)))
          .and(  eq(Schema.C_TERMS_VALUE, bindMarker(Schema.C_TERMS_VALUE)));
    m_searchStatement = m_session.prepare(select.toString());

    select = QueryBuilder.select(Schema.C_ATTRS_ATTR, Schema.C_ATTRS_VALUE).from(Schema.T_ATTRS);
    select.where(eq(Schema.C_ATTRS_CONTEXT, bindMarker(Schema.C_ATTRS_CONTEXT)))
          .and(  eq(Schema.C_ATTRS_RESOURCE, bindMarker(Schema.C_ATTRS_RESOURCE)));
    m_selectAttributesStatement = m_session.prepare(select.toString());

    select = QueryBuilder.select(Schema.C_METRICS_NAME).from(Schema.T_METRICS);
    select.where(eq(Schema.C_METRICS_CONTEXT, bindMarker(Schema.C_METRICS_CONTEXT)))
          .and(  eq(Schema.C_METRICS_RESOURCE, bindMarker(Schema.C_METRICS_RESOURCE)));
    m_selectMetricNamesStatement = m_session.prepare(select.toString());
}
 

@Test
public void testInsert() throws InterruptedException {
	Book book = new Book();
	book.setIsbn(UUIDs.timeBased());
	book.setTitle("Spring Integration Cassandra");
	book.setAuthor("Cassandra Guru");
	book.setPages(521);
	book.setSaleDate(new Date());
	book.setInStock(true);

	this.sink.input().send(new GenericMessage<>(book));

	final Select select = QueryBuilder.select().all().from("book");

	assertEqualsEventually(1, new Supplier<Integer>() {

		@Override
		public Integer get() {
			return cassandraTemplate.select(select, Book.class).size();
		}

	});

	this.cassandraTemplate.delete(book);
}
 

@Inject
public CassandraCachePrimer(CassandraSession session) {
    m_session = checkNotNull(session);
    Select select = QueryBuilder.select(Constants.Schema.C_METRICS_CONTEXT,
            Constants.Schema.C_METRICS_RESOURCE,
            Constants.Schema.C_METRICS_NAME)
            .from(Constants.Schema.T_METRICS);
    m_selectAllMetricsStatement = session.prepare(select.toString());

    select = QueryBuilder.select(Constants.Schema.C_ATTRS_CONTEXT,
            Constants.Schema.C_ATTRS_RESOURCE,
            Constants.Schema.C_ATTRS_ATTR,
            Constants.Schema.C_ATTRS_VALUE)
            .from(Constants.Schema.T_ATTRS);
    m_selectAllAttributesStatement = session.prepare(select.toString());
}
 

public DDelayedLocatorIO() {

        // create a generic select statement for retrieving from metrics_delayed_locator
        Select.Where select = QueryBuilder
                .select()
                .all()
                .from( CassandraModel.CF_METRICS_DELAYED_LOCATOR_NAME )
                .where( eq ( KEY, bindMarker() ));
        getValue = DatastaxIO.getSession().prepare( select );

        // create a generic insert statement for inserting into metrics_delayed_locator
        Insert insert = QueryBuilder.insertInto( CassandraModel.CF_METRICS_DELAYED_LOCATOR_NAME)
                .using(ttl(TenantTtlProvider.DELAYED_LOCATOR_TTL))
                .value(KEY, bindMarker())
                .value(COLUMN1, bindMarker())
                .value(VALUE, bindMarker());
        putValue = DatastaxIO.getSession()
                .prepare(insert)
                .setConsistencyLevel( ConsistencyLevel.LOCAL_ONE );
    }
 

@Override
public List<E> queryByOpId(String opId, Function<String, E> findOne) throws EventStoreException {
    Select select = QueryBuilder.select(CassandraEventRecorder.ENTITY_ID, CassandraEventRecorder.VERSION).from(tableNameByOps);
    select.where(QueryBuilder.eq(CassandraEventRecorder.OP_ID, opId));
    List<Row> entityEventDatas = cassandraSession.execute(select, PagingIterable::all);
    Map<String, E> resultList = new HashMap<>();
    for (Row entityEvent : entityEventDatas) {
        String entityId = entityEvent.getString(CassandraEventRecorder.ENTITY_ID);
        int version = entityEvent.getInt(CassandraEventRecorder.VERSION);
        E snapshot = findOne.apply(entityId);
        E newEntity = null;
        if (snapshot == null || snapshot.getVersion() > version) {
            newEntity = queryEntity(entityId);
        } else if (snapshot.getVersion() < version) {
            newEntity = queryEntity(entityId, version, snapshot);
        } else {
            log.debug("Up-to-date Snapshot:" + snapshot);
        }
        if (!resultList.containsKey(entityId)) {
            if (newEntity != null)
                resultList.put(entityId, newEntity);
        }

    }
    return new ArrayList<>(resultList.values());
}
 

@Override
public List<E> queryByOpId(String opId) throws EventStoreException {
    Select select = QueryBuilder.select(CassandraEventRecorder.ENTITY_ID).from(tableNameByOps);
    select.where(QueryBuilder.eq(CassandraEventRecorder.OP_ID, opId));
    List<Row> entityEventDatas = cassandraSession.execute(select, PagingIterable::all);

    Map<String, E> resultList = new HashMap<>();
    for (Row entityEvent : entityEventDatas) {
        String entityId = entityEvent.getString(CassandraEventRecorder.ENTITY_ID);
        if (!resultList.containsKey(entityId)) {
            E value = queryEntity(entityId);
            if (value != null)
                resultList.put(entityId, value);
        }
    }
    return new ArrayList<>(resultList.values());
}
 

private void createPreparedStatements() {

        Select.Where select = select()
                .all()
                .from( CassandraModel.CF_METRICS_METADATA_NAME )
                .where( eq( KEY, bindMarker() ));

        getValue = DatastaxIO.getSession().prepare( select );

        Insert insert = insertInto( CassandraModel.CF_METRICS_METADATA_NAME )
                .value( KEY, bindMarker() )
                .value( COLUMN1, bindMarker() )
                .value( VALUE, bindMarker() );

        putValue = DatastaxIO.getSession().prepare( insert );
        putValue.setConsistencyLevel( ConsistencyLevel.LOCAL_ONE );
    }
 

@Test
public void whenSavingBook_thenAvailableOnRetrieval_usingPreparedStatements() throws InterruptedException {
    final UUID uuid = UUIDs.timeBased();
    final String insertPreparedCql = "insert into book (id, title, publisher, tags) values (?, ?, ?, ?)";
    final List<Object> singleBookArgsList = new ArrayList<>();
    final List<List<?>> bookList = new ArrayList<>();
    singleBookArgsList.add(uuid);
    singleBookArgsList.add("Head First Java");
    singleBookArgsList.add("OReilly Media");
    singleBookArgsList.add(ImmutableSet.of("Software"));
    bookList.add(singleBookArgsList);
    cassandraTemplate.ingest(insertPreparedCql, bookList);
    // This may not be required, just added to avoid any transient issues
    Thread.sleep(5000);
    final Select select = QueryBuilder.select().from("book");
    final Book retrievedBook = cassandraTemplate.selectOne(select, Book.class);
    assertEquals(uuid, retrievedBook.getId());
}
 

/**
 * Build the SELECT query statement for execution. For every non-primary-key column, the TTL, WRITETIME, and execution
 * time are also queried.
 *
 * For example, a table t with columns a, b, and c, where A is the partition key, B is the clustering key, and C is a
 * regular column, looks like the following:
 * <pre>
 *     {@code SELECT now() as execution_time, a, b, c, TTL(c) as c_ttl, WRITETIME(c) as c_writetime FROM t;}
 * </pre>
 */
private static BuiltStatement generateSnapshotStatement(TableMetadata tableMetadata) {
    List<String> allCols = tableMetadata.getColumns().stream().map(ColumnMetadata::getName).collect(Collectors.toList());
    Set<String> primaryCols = tableMetadata.getPrimaryKey().stream().map(ColumnMetadata::getName).collect(Collectors.toSet());
    List<String> collectionCols = tableMetadata.getColumns().stream().filter(cm -> collectionTypes.contains(cm.getType().getName()))
            .map(ColumnMetadata::getName).collect(Collectors.toList());

    Select.Selection selection = QueryBuilder.select().raw(CASSANDRA_NOW_UNIXTIMESTAMP).as(EXECUTION_TIME_ALIAS);
    for (String col : allCols) {
        selection.column(withQuotes(col));

        if (!primaryCols.contains(col) && !collectionCols.contains(col)) {
            selection.ttl(withQuotes(col)).as(ttlAlias(col));
        }
    }
    return selection.from(tableMetadata.getKeyspace().getName(), tableMetadata.getName());
}
 

private void setEnd(Select.Where statement, Scan scan) {
  if (!scan.getEndClusteringKey().isPresent()) {
    return;
  }

  scan.getEndClusteringKey()
      .ifPresent(
          k -> {
            List<Value> end = k.get();
            IntStream.range(0, end.size())
                .forEach(
                    i -> {
                      if (i == (end.size() - 1)) {
                        if (scan.getEndInclusive()) {
                          statement.and(lte(end.get(i).getName(), bindMarker()));
                        } else {
                          statement.and(lt(end.get(i).getName(), bindMarker()));
                        }
                      } else {
                        statement.and(eq(end.get(i).getName(), bindMarker()));
                      }
                    });
          });
}
 

@Override
public ListenableFuture<List<DeviceType>> findDeviceTypesByTenantIdCustomerIdAndIdsAsync(UUID tenantId, UUID customerId, List<UUID> deviceIds) {
    log.debug("Try to find devices by tenantId [{}], customerId [{}] and device Ids [{}]", tenantId, customerId, deviceIds);
    Select select = select().from(getColumnFamilyName());
    Select.Where query = select.where();
    query.and(eq(DEVICE_TYPE_TENANT_ID_PROPERTY, tenantId));
    query.and(eq(DEVICE_TYPE_CUSTOMER_ID_PROPERTY, customerId));
    query.and(in(ID_PROPERTY, deviceIds));
    return findListByStatementAsync(query);
}
 

@Override
public Optional<DeviceType> findDeviceTypeByTenantIdAndName(UUID tenantId, String deviceName) {
    Select select = select().from(DEVICE_TYPE_BY_TENANT_AND_NAME_VIEW_NAME);
    Select.Where query = select.where();
    query.and(eq(DEVICE_TYPE_TENANT_ID_PROPERTY, tenantId));
    query.and(eq(DEVICE_TYPE_NAME_PROPERTY, deviceName));
    return Optional.ofNullable(DaoUtil.getData(findOneByStatement(query)));
}
 

public Response getRecords(
    String keySpace, String table, Map<String, Object> filters, List<String> fields) {
  Response response = new Response();
  Session session = connectionManager.getSession(keySpace);
  try {
    Select select;
    if (CollectionUtils.isNotEmpty(fields)) {
      select = QueryBuilder.select((String[]) fields.toArray()).from(keySpace, table);
    } else {
      select = QueryBuilder.select().all().from(keySpace, table);
    }

    if (MapUtils.isNotEmpty(filters)) {
      Select.Where where = select.where();
      for (Map.Entry<String, Object> filter : filters.entrySet()) {
        Object value = filter.getValue();
        if (value instanceof List) {
          where = where.and(QueryBuilder.in(filter.getKey(), ((List) filter.getValue())));
        } else {
          where = where.and(QueryBuilder.eq(filter.getKey(), filter.getValue()));
        }
      }
    }

    ResultSet results = null;
    results = session.execute(select);
    response = CassandraUtil.createResponse(results);
  } catch (Exception e) {
    ProjectLogger.log(Constants.EXCEPTION_MSG_FETCH + table + " : " + e.getMessage(), e);
    throw new ProjectCommonException(
        ResponseCode.SERVER_ERROR.getErrorCode(),
        ResponseCode.SERVER_ERROR.getErrorMessage(),
        ResponseCode.SERVER_ERROR.getResponseCode());
  }
  return response;
}
 

@Override
public ListenableFuture<List<AttributeKvEntry>> findAll(EntityId entityId, String attributeType) {
    Select.Where select = select().from(ATTRIBUTES_KV_CF)
            .where(eq(ENTITY_TYPE_COLUMN, entityId.getEntityType()))
            .and(eq(ENTITY_ID_COLUMN, entityId.getId()))
            .and(eq(ATTRIBUTE_TYPE_COLUMN, attributeType));
    log.trace("Generated query [{}] for entityId {} and attributeType {}", select, entityId, attributeType);
    return Futures.transform(executeAsyncRead(select), (Function<? super ResultSet, ? extends List<AttributeKvEntry>>) input ->
                    convertResultToAttributesKvEntryList(input)
            , readResultsProcessingExecutor);
}
 

@Override
public Optional<Customer> findCustomersByTenantIdAndTitle(UUID tenantId, String title) {
    Select select = select().from(CUSTOMER_BY_TENANT_AND_TITLE_VIEW_NAME);
    Select.Where query = select.where();
    query.and(eq(CUSTOMER_TENANT_ID_PROPERTY, tenantId));
    query.and(eq(CUSTOMER_TITLE_PROPERTY, title));
    CustomerEntity customerEntity = findOneByStatement(query);
    Customer customer = DaoUtil.getData(customerEntity);
    return Optional.ofNullable(customer);
}
 

@Override
public D findById(UUID key) {
    log.debug("Get entity by key {}", key);
    Select.Where query = select().from(getColumnFamilyName()).where(eq(ModelConstants.ID_PROPERTY, key));
    log.trace("Execute query {}", query);
    E entity = findOneByStatement(query);
    return DaoUtil.getData(entity);
}
 

@Override
public ListenableFuture<List<DeviceType>> findDeviceTypesByTenantIdAndIdsAsync(UUID tenantId, List<UUID> deviceIds) {
    log.debug("Try to find devices by tenantId [{}] and device Ids [{}]", tenantId, deviceIds);
    Select select = select().from(getColumnFamilyName());
    Select.Where query = select.where();
    query.and(eq(DEVICE_TYPE_TENANT_ID_PROPERTY, tenantId));
    query.and(in(ID_PROPERTY, deviceIds));
    return findListByStatementAsync(query);
}
 

@Override
public ListenableFuture<Optional<AttributeKvEntry>> find(EntityId entityId, String attributeType, String attributeKey) {
    Select.Where select = select().from(ATTRIBUTES_KV_CF)
            .where(eq(ENTITY_TYPE_COLUMN, entityId.getEntityType()))
            .and(eq(ENTITY_ID_COLUMN, entityId.getId()))
            .and(eq(ATTRIBUTE_TYPE_COLUMN, attributeType))
            .and(eq(ATTRIBUTE_KEY_COLUMN, attributeKey));
    log.trace("Generated query [{}] for entityId {} and key {}", select, entityId, attributeKey);
    return Futures.transform(executeAsyncRead(select), (Function<? super ResultSet, ? extends Optional<AttributeKvEntry>>) input ->
                    Optional.ofNullable(convertResultToAttributesKvEntry(attributeKey, input.one()))
            , readResultsProcessingExecutor);
}
 

protected Collection<Select> queryCondition2Select(Query query,
                                                   Select select) {
    // Query by conditions
    Set<Condition> conditions = query.conditions();
    for (Condition condition : conditions) {
        Clause clause = condition2Cql(condition);
        select.where(clause);
        if (Clauses.needAllowFiltering(clause)) {
            select.allowFiltering();
        }
    }
    return ImmutableList.of(select);
}
 

public void assertValue(String k, Integer expectedValue) {
    Select selectStatement = select().column("v").from(KEYSPACE_NAME, TABLE_NAME);
    selectStatement.where(eq(KEY_NAME, k));
    ResultSet results = getSession().execute(selectStatement);
    LOG.debug("EXECUTING [{}]", selectStatement.toString());
    Integer actualValue = results.one().getInt(VALUE_NAME);
    assertEquals(expectedValue, actualValue);
}
 

@Override
public List<EntityEvent> queryHistory(String entityId) throws EventStoreException {
    Select select = QueryBuilder.select().from(tableName);
    select.where(QueryBuilder.eq(CassandraEventRecorder.ENTITY_ID, entityId));
    return cassandraSession.execute(select, PagingIterable::all)
            .stream().map(BaseCassandraViewQuery::convertToEntityEvent).collect(Collectors.toList());
}
 

@Test
public void whenSavingBook_thenAvailableOnRetrieval_usingQueryBuilder() {
    final UUID uuid = UUIDs.timeBased();
    final Insert insert = QueryBuilder.insertInto(DATA_TABLE_NAME).value("id", uuid).value("title", "Head First Java").value("publisher", "OReilly Media").value("tags", ImmutableSet.of("Software"));
    cassandraTemplate.execute(insert);
    final Select select = QueryBuilder.select().from("book").limit(10);
    final Book retrievedBook = cassandraTemplate.selectOne(select, Book.class);
    assertEquals(uuid, retrievedBook.getId());
}
 

@Override
public ListenableFuture<Alarm> findAlarmByIdAsync(UUID key) {
    log.debug("Get alarm by id {}", key);
    Select.Where query = select().from(ALARM_BY_ID_VIEW_NAME).where(eq(ModelConstants.ID_PROPERTY, key));
    query.limit(1);
    log.trace("Execute query {}", query);
    return findOneByStatementAsync(query);
}
 

@Override
public E queryEntity(String entityId, int version) throws EventStoreException {
    Select select = QueryBuilder.select().from(tableName);
    select.where(QueryBuilder.eq(CassandraEventRecorder.ENTITY_ID, entityId));
    select.where(QueryBuilder.lte(CassandraEventRecorder.VERSION, version));
    return queryEntityInternal(entityId, select);
}
 

@Override
public Optional<Device> findDeviceByTenantIdAndName(UUID tenantId, String deviceName) {
    Select select = select().from(DEVICE_BY_TENANT_AND_NAME_VIEW_NAME);
    Select.Where query = select.where();
    query.and(eq(DEVICE_TENANT_ID_PROPERTY, tenantId));
    query.and(eq(DEVICE_NAME_PROPERTY, deviceName));
    return Optional.ofNullable(DaoUtil.getData(findOneByStatement(query)));
}
 

@Override
public Statement retrieve(List<String> keys) {
    // Retrieve all the columns associated with the keys
    Select statement = QueryBuilder.select().column(SOURCE_KEY_NAME)
            .column(WORD_KEY_NAME).column(VALUE_NAME)
            .from(KEYSPACE_NAME, TABLE_NAME);
    statement.where(QueryBuilder.eq(SOURCE_KEY_NAME, keys.get(0)));
    statement.where(QueryBuilder.eq(WORD_KEY_NAME, keys.get(1)));
    return statement;
}
 

@Override
public ListenableFuture<List<Device>> findDevicesByTenantIdCustomerIdAndIdsAsync(UUID tenantId, UUID customerId, List<UUID> deviceIds) {
    log.debug("Try to find devices by tenantId [{}], customerId [{}] and device Ids [{}]", tenantId, customerId, deviceIds);
    Select select = select().from(getColumnFamilyName());
    Select.Where query = select.where();
    query.and(eq(DEVICE_TENANT_ID_PROPERTY, tenantId));
    query.and(eq(DEVICE_CUSTOMER_ID_PROPERTY, customerId));
    query.and(in(ID_PROPERTY, deviceIds));
    return findListByStatementAsync(query);
}
 

@Override
public EntityEvent queryEvent(String entityId, int version) throws EventStoreException {
    Select select = QueryBuilder.select().from(tableName);
    select.where(QueryBuilder.eq(CassandraEventRecorder.ENTITY_ID, entityId));
    select.where(QueryBuilder.eq(CassandraEventRecorder.VERSION, version));
    Row one = cassandraSession.execute(select, PagingIterable::one);
    return one == null ? null : convertToEntityEvent(one);
}