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

private Update createUpdateStatement(
    String keySpaceName, String tableName, Map<String, Object> record) {
  Update update = QueryBuilder.update(keySpaceName, tableName);
  Assignments assignments = update.with();
  Update.Where where = update.where();
  record
      .entrySet()
      .stream()
      .forEach(
          x -> {
            if (Constants.ID.equals(x.getKey())) {
              where.and(eq(x.getKey(), x.getValue()));
            } else {
              assignments.and(QueryBuilder.set(x.getKey(), x.getValue()));
            }
          });
  return update;
}
 

@Override
public Response deleteRecord(String keyspaceName, String tableName, String identifier) {
  long startTime = System.currentTimeMillis();
  ProjectLogger.log(
      "Cassandra Service deleteRecord method started at ==" + startTime, LoggerEnum.INFO);
  Response response = new Response();
  try {
    Delete.Where delete =
        QueryBuilder.delete()
            .from(keyspaceName, tableName)
            .where(eq(Constants.IDENTIFIER, identifier));
    connectionManager.getSession(keyspaceName).execute(delete);
    response.put(Constants.RESPONSE, Constants.SUCCESS);
  } catch (Exception e) {
    ProjectLogger.log(Constants.EXCEPTION_MSG_DELETE + tableName + " : " + e.getMessage(), e);
    throw new ProjectCommonException(
        ResponseCode.SERVER_ERROR.getErrorCode(),
        ResponseCode.SERVER_ERROR.getErrorMessage(),
        ResponseCode.SERVER_ERROR.getResponseCode());
  }
  logQueryElapseTime("deleteRecord", startTime);
  return response;
}
 

private Response executeSelectQuery(
    String keyspaceName,
    String tableName,
    List<String> ids,
    Builder selectBuilder,
    String primaryKeyColumnName) {
  Response response;
  Select selectQuery = selectBuilder.from(keyspaceName, tableName);
  Where selectWhere = selectQuery.where();
  Clause clause = null;
  if (StringUtils.isBlank(primaryKeyColumnName)) {
    clause = QueryBuilder.in(JsonKey.ID, ids.toArray(new Object[ids.size()]));
  } else {
    clause = QueryBuilder.in(primaryKeyColumnName, ids.toArray(new Object[ids.size()]));
  }

  selectWhere.and(clause);
  ResultSet results = connectionManager.getSession(keyspaceName).execute(selectQuery);
  response = CassandraUtil.createResponse(results);
  return response;
}
 

public static void createQuery(String key, Object value, Where where) {
  if (value instanceof Map) {
    Map<String, Object> map = (Map<String, Object>) value;
    map.entrySet()
        .stream()
        .forEach(
            x -> {
              if (Constants.LTE.equalsIgnoreCase(x.getKey())) {
                where.and(QueryBuilder.lte(key, x.getValue()));
              } else if (Constants.LT.equalsIgnoreCase(x.getKey())) {
                where.and(QueryBuilder.lt(key, x.getValue()));
              } else if (Constants.GTE.equalsIgnoreCase(x.getKey())) {
                where.and(QueryBuilder.gte(key, x.getValue()));
              } else if (Constants.GT.equalsIgnoreCase(x.getKey())) {
                where.and(QueryBuilder.gt(key, x.getValue()));
              }
            });
  } else if (value instanceof List) {
    where.and(QueryBuilder.in(key, (List) value));
  } else {
    where.and(QueryBuilder.eq(key, value));
  }
}
 

/**
 * Method to create the where clause.
 *
 * @param key represents the column name.
 * @param value represents the column value.
 * @param where where clause.
 */
public static void createWhereQuery(String key, Object value, Where where) {
  if (value instanceof Map) {
    Map<String, Object> map = (Map<String, Object>) value;
    map.entrySet()
        .stream()
        .forEach(
            x -> {
              if (Constants.LTE.equalsIgnoreCase(x.getKey())) {
                where.and(QueryBuilder.lte(key, x.getValue()));
              } else if (Constants.LT.equalsIgnoreCase(x.getKey())) {
                where.and(QueryBuilder.lt(key, x.getValue()));
              } else if (Constants.GTE.equalsIgnoreCase(x.getKey())) {
                where.and(QueryBuilder.gte(key, x.getValue()));
              } else if (Constants.GT.equalsIgnoreCase(x.getKey())) {
                where.and(QueryBuilder.gt(key, x.getValue()));
              }
            });
  } else if (value instanceof List) {
    where.and(QueryBuilder.in(key, (List) value));
  } else {
    where.and(QueryBuilder.eq(key, value));
  }
}
 

/**
 * Method to create the cassandra update query.
 *
 * @param primaryKey map representing the composite primary key.
 * @param nonPKRecord map contains the fields that has to update.
 * @param keyspaceName cassandra keyspace name.
 * @param tableName cassandra table name.
 * @return RegularStatement.
 */
public static RegularStatement createUpdateQuery(
    Map<String, Object> primaryKey,
    Map<String, Object> nonPKRecord,
    String keyspaceName,
    String tableName) {

  Update update = QueryBuilder.update(keyspaceName, tableName);
  Assignments assignments = update.with();
  Update.Where where = update.where();
  nonPKRecord
      .entrySet()
      .stream()
      .forEach(
          x -> {
            assignments.and(QueryBuilder.set(x.getKey(), x.getValue()));
          });
  primaryKey
      .entrySet()
      .stream()
      .forEach(
          x -> {
            where.and(QueryBuilder.eq(x.getKey(), x.getValue()));
          });
  return where;
}
 

@Override
public WidgetType findByTenantIdBundleAliasAndAlias(UUID tenantId, String bundleAlias, String alias) {
    log.debug("Try to find widget type by tenantId [{}], bundleAlias [{}] and alias [{}]", tenantId, bundleAlias, alias);
    Where query = select().from(WIDGET_TYPE_BY_TENANT_AND_ALIASES_COLUMN_FAMILY_NAME)
            .where()
            .and(eq(WIDGET_TYPE_TENANT_ID_PROPERTY, tenantId))
            .and(eq(WIDGET_TYPE_BUNDLE_ALIAS_PROPERTY, bundleAlias))
            .and(eq(WIDGET_TYPE_ALIAS_PROPERTY, alias));
    log.trace("Execute query {}", query);
    WidgetTypeEntity widgetTypeEntity = findOneByStatement(query);
    log.trace("Found widget type [{}] by tenantId [{}], bundleAlias [{}] and alias [{}]",
            widgetTypeEntity, tenantId, bundleAlias, alias);
    return DaoUtil.getData(widgetTypeEntity);
}
 

@Override
public UserCredentials findByActivateToken(String activateToken) {
    log.debug("Try to find user credentials by activateToken [{}] ", activateToken);
    Where query = select().from(ModelConstants.USER_CREDENTIALS_BY_ACTIVATE_TOKEN_COLUMN_FAMILY_NAME)
            .where(eq(ModelConstants.USER_CREDENTIALS_ACTIVATE_TOKEN_PROPERTY, activateToken));
    log.trace("Execute query {}", query);
    UserCredentialsEntity userCredentialsEntity = findOneByStatement(query);
    log.trace("Found user credentials [{}] by activateToken [{}]", userCredentialsEntity, activateToken);
    return DaoUtil.getData(userCredentialsEntity);
}
 

@Override
public UserCredentials findByResetToken(String resetToken) {
    log.debug("Try to find user credentials by resetToken [{}] ", resetToken);
    Where query = select().from(ModelConstants.USER_CREDENTIALS_BY_RESET_TOKEN_COLUMN_FAMILY_NAME)
            .where(eq(ModelConstants.USER_CREDENTIALS_RESET_TOKEN_PROPERTY, resetToken));
    log.trace("Execute query {}", query);
    UserCredentialsEntity userCredentialsEntity = findOneByStatement(query);
    log.trace("Found user credentials [{}] by resetToken [{}]", userCredentialsEntity, resetToken);
    return DaoUtil.getData(userCredentialsEntity);
}
 

@Override
public AdminSettings findByKey(String key) {
    log.debug("Try to find admin settings by key [{}] ", key);
    Where query = select().from(ADMIN_SETTINGS_BY_KEY_COLUMN_FAMILY_NAME).where(eq(ADMIN_SETTINGS_KEY_PROPERTY, key));
    log.trace("Execute query {}", query);
    AdminSettingsEntity adminSettingsEntity = findOneByStatement(query);
    log.trace("Found admin settings [{}] by key [{}]", adminSettingsEntity, key);
    return DaoUtil.getData(adminSettingsEntity);
}
 

@Override
public List<WidgetType> findWidgetTypesByTenantIdAndBundleAlias(UUID tenantId, String bundleAlias) {
    log.debug("Try to find widget types by tenantId [{}] and bundleAlias [{}]", tenantId, bundleAlias);
    Where query = select().from(WIDGET_TYPE_BY_TENANT_AND_ALIASES_COLUMN_FAMILY_NAME)
            .where()
            .and(eq(WIDGET_TYPE_TENANT_ID_PROPERTY, tenantId))
            .and(eq(WIDGET_TYPE_BUNDLE_ALIAS_PROPERTY, bundleAlias));
    List<WidgetTypeEntity> widgetTypesEntities = findListByStatement(query);
    log.trace("Found widget types [{}] by tenantId [{}] and bundleAlias [{}]", widgetTypesEntities, tenantId, bundleAlias);
    return DaoUtil.convertDataList(widgetTypesEntities);
}
 

@Override
public DeviceCredentials findByDeviceId(UUID deviceId) {
    log.debug("Try to find device credentials by deviceId [{}] ", deviceId);
    Where query = select().from(ModelConstants.DEVICE_CREDENTIALS_BY_DEVICE_COLUMN_FAMILY_NAME)
            .where(eq(ModelConstants.DEVICE_CREDENTIALS_DEVICE_ID_PROPERTY, deviceId));
    log.trace("Execute query {}", query);
    DeviceCredentialsEntity deviceCredentialsEntity = findOneByStatement(query);
    log.trace("Found device credentials [{}] by deviceId [{}]", deviceCredentialsEntity, deviceId);
    return DaoUtil.getData(deviceCredentialsEntity);
}
 

@Override
public AssetCredentials findByAssetId(UUID assetId) {
    log.debug("Try to find asset credentials by assetId [{}] ", assetId);
    Where query = select().from(ModelConstants.ASSET_CREDENTIALS_BY_ASSET_COLUMN_FAMILY_NAME)
            .where(eq(ModelConstants.ASSET_CREDENTIALS_ASSET_ID_PROPERTY, assetId));
    log.trace("Execute query {}", query);
    AssetCredentialsEntity assetCredentialsEntity = findOneByStatement(query);
    log.trace("Found asset credentials [{}] by assetId [{}]", assetCredentialsEntity, assetId);
    return DaoUtil.getData(assetCredentialsEntity);
}
 

@Override
public AssetCredentials findByCredentialsId(String credentialsId) {
    log.debug("Try to find asset credentials by credentialsId [{}] ", credentialsId);
    Where query = select().from(ModelConstants.ASSET_CREDENTIALS_BY_CREDENTIALS_ID_COLUMN_FAMILY_NAME)
            .where(eq(ModelConstants.ASSET_CREDENTIALS_CREDENTIALS_ID_PROPERTY, credentialsId));
    log.trace("Execute query {}", query);
    AssetCredentialsEntity assetCredentialsEntity = findOneByStatement(query);
    log.trace("Found asset credentials [{}] by credentialsId [{}]", assetCredentialsEntity, credentialsId);
    return DaoUtil.getData(assetCredentialsEntity);
}
 

/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param id
 * @param mark
 * @return <br>
 */
@Override
public CheckInfo getCheckInfo(String id, String mark) {

    Where select = QueryBuilder.select().from("t_tx_check_info").where(QueryBuilder.eq("id", id))
        .and(QueryBuilder.eq("mark", mark));

    TxCheckinfoEntity entity = cassandraOperations.selectOne(select, TxCheckinfoEntity.class);

    if (entity != null) {
        return new CheckInfo(entity.getId(), entity.getMark(),
            entity.getResult() == null ? null : DataUtil.hexStr2Byte(entity.getResult()));
    }
    return null;
}
 

/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param checkInfo <br>
 */
@Override
public void updateCheckInfo(CheckInfo checkInfo) {
    Where select = QueryBuilder.select().from("t_tx_check_info").where(QueryBuilder.eq("id", checkInfo.getId()))
        .and(QueryBuilder.eq("mark", checkInfo.getMark()));

    TxCheckinfoEntity entity = cassandraOperations.selectOne(select, TxCheckinfoEntity.class);
    if (entity != null) {
        entity.setResult(checkInfo.getResult() == null ? null : DataUtil.byte2HexStr(checkInfo.getResult()));
        cassandraOperations.update(entity);
    }

}
 

/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param id
 * @param mark <br>
 */
@Override
public void deleteCheckInfo(String id, String mark) {
    Where select = QueryBuilder.select().from("t_tx_check_info").where(QueryBuilder.eq("id", id))
        .and(QueryBuilder.eq("mark", mark));

    TxCheckinfoEntity entity = cassandraOperations.selectOne(select, TxCheckinfoEntity.class);
    if (entity != null) {
        cassandraOperations.delete(entity);
    }
}
 

@Override
public UserCredentials findByUserId(UUID userId) {
    log.debug("Try to find user credentials by userId [{}] ", userId);
    Where query = select().from(ModelConstants.USER_CREDENTIALS_BY_USER_COLUMN_FAMILY_NAME).where(eq(ModelConstants.USER_CREDENTIALS_USER_ID_PROPERTY, userId));
    log.trace("Execute query {}", query);
    UserCredentialsEntity userCredentialsEntity = findOneByStatement(query);
    log.trace("Found user credentials [{}] by userId [{}]", userCredentialsEntity, userId);
    return DaoUtil.getData(userCredentialsEntity);
}
 

@Override
public User findByEmail(String email) {
    log.debug("Try to find user by email [{}] ", email);
    Where query = select().from(ModelConstants.USER_BY_EMAIL_COLUMN_FAMILY_NAME).where(eq(ModelConstants.USER_EMAIL_PROPERTY, email));
    log.trace("Execute query {}", query);
    UserEntity userEntity = findOneByStatement(query);
    log.trace("Found user [{}] by email [{}]", userEntity, email);
    return DaoUtil.getData(userEntity);
}
 

@Override
public DeviceCredentials findByCredentialsId(String credentialsId) {
    log.debug("Try to find device credentials by credentialsId [{}] ", credentialsId);
    Where query = select().from(ModelConstants.DEVICE_CREDENTIALS_BY_CREDENTIALS_ID_COLUMN_FAMILY_NAME)
            .where(eq(ModelConstants.DEVICE_CREDENTIALS_CREDENTIALS_ID_PROPERTY, credentialsId));
    log.trace("Execute query {}", query);
    DeviceCredentialsEntity deviceCredentialsEntity = findOneByStatement(query);
    log.trace("Found device credentials [{}] by credentialsId [{}]", deviceCredentialsEntity, credentialsId);
    return DaoUtil.getData(deviceCredentialsEntity);
}
 

@Override
public Response updateRecordWithTTL(
    String keyspaceName,
    String tableName,
    Map<String, Object> request,
    Map<String, Object> compositeKey,
    int ttl) {
  long startTime = System.currentTimeMillis();
  Session session = connectionManager.getSession(keyspaceName);
  Update update = QueryBuilder.update(keyspaceName, tableName);
  Assignments assignments = update.with();
  Update.Where where = update.where();
  request
      .entrySet()
      .stream()
      .forEach(
          x -> {
            assignments.and(QueryBuilder.set(x.getKey(), x.getValue()));
          });
  compositeKey
      .entrySet()
      .stream()
      .forEach(
          x -> {
            where.and(eq(x.getKey(), x.getValue()));
          });
  update.using(QueryBuilder.ttl(ttl));
  ProjectLogger.log(
      "CassandraOperationImpl:updateRecordWithTTL: query = " + update.getQueryString(),
      LoggerEnum.INFO.name());
  ResultSet results = session.execute(update);
  Response response = CassandraUtil.createResponse(results);
  logQueryElapseTime("updateRecordWithTTL", startTime);
  return response;
}
 

@Override
public Response getRecordsByCompositeKey(
    String keyspaceName, String tableName, Map<String, Object> compositeKeyMap) {
  long startTime = System.currentTimeMillis();
  ProjectLogger.log(
      "CassandraOperationImpl: getRecordsByCompositeKey called at " + startTime, LoggerEnum.INFO);
  Response response = new Response();
  try {
    Builder selectBuilder = QueryBuilder.select().all();
    Select selectQuery = selectBuilder.from(keyspaceName, tableName);
    Where selectWhere = selectQuery.where();
    for (Entry<String, Object> entry : compositeKeyMap.entrySet()) {
      Clause clause = eq(entry.getKey(), entry.getValue());
      selectWhere.and(clause);
    }
    ResultSet results = connectionManager.getSession(keyspaceName).execute(selectQuery);
    response = CassandraUtil.createResponse(results);
  } catch (Exception e) {
    ProjectLogger.log(
        "CassandraOperationImpl:getRecordsByCompositeKey: "
            + Constants.EXCEPTION_MSG_FETCH
            + tableName
            + " : "
            + e.getMessage());
    throw new ProjectCommonException(
        ResponseCode.SERVER_ERROR.getErrorCode(),
        ResponseCode.SERVER_ERROR.getErrorMessage(),
        ResponseCode.SERVER_ERROR.getResponseCode());
  }
  logQueryElapseTime("getRecordsByCompositeKey", startTime);
  return response;
}
 

@Override
public boolean deleteRecords(String keyspaceName, String tableName, List<String> identifierList) {
  long startTime = System.currentTimeMillis();
  ResultSet resultSet;
  ProjectLogger.log(
      "CassandraOperationImpl: deleteRecords called at " + startTime, LoggerEnum.INFO);
  try {
    Delete delete = QueryBuilder.delete().from(keyspaceName, tableName);
    Delete.Where deleteWhere = delete.where();
    Clause clause = QueryBuilder.in(JsonKey.ID, identifierList);
    deleteWhere.and(clause);
    resultSet = connectionManager.getSession(keyspaceName).execute(delete);
  } catch (Exception e) {
    ProjectLogger.log(
        "CassandraOperationImpl: deleteRecords by list of primary key. "
            + Constants.EXCEPTION_MSG_DELETE
            + tableName
            + " : "
            + e.getMessage(),
        e);
    throw new ProjectCommonException(
        ResponseCode.SERVER_ERROR.getErrorCode(),
        ResponseCode.SERVER_ERROR.getErrorMessage(),
        ResponseCode.SERVER_ERROR.getResponseCode());
  }
  logQueryElapseTime("deleteRecords", startTime);
  return resultSet.wasApplied();
}
 

@Override
public void deleteRecord(
    String keyspaceName, String tableName, Map<String, String> compositeKeyMap) {
  long startTime = System.currentTimeMillis();
  ProjectLogger.log(
      "CassandraOperationImpl: deleteRecord by composite key called at " + startTime,
      LoggerEnum.INFO);
  try {
    Delete delete = QueryBuilder.delete().from(keyspaceName, tableName);
    Delete.Where deleteWhere = delete.where();
    compositeKeyMap
        .entrySet()
        .stream()
        .forEach(
            x -> {
              Clause clause = eq(x.getKey(), x.getValue());
              deleteWhere.and(clause);
            });
    connectionManager.getSession(keyspaceName).execute(delete);
  } catch (Exception e) {
    ProjectLogger.log(
        "CassandraOperationImpl: deleteRecord by composite key. "
            + Constants.EXCEPTION_MSG_DELETE
            + tableName
            + " : "
            + e.getMessage(),
        e);
    throw new ProjectCommonException(
        ResponseCode.SERVER_ERROR.getErrorCode(),
        ResponseCode.SERVER_ERROR.getErrorMessage(),
        ResponseCode.SERVER_ERROR.getResponseCode());
  }
  logQueryElapseTime("deleteRecordByCompositeKey", startTime);
}
 

private Response getRecordByIdentifier(
    String keyspaceName, String tableName, Object key, List<String> fields) {
  long startTime = System.currentTimeMillis();
  ProjectLogger.log(
      "Cassandra Service getRecordBy key method started at ==" + startTime, LoggerEnum.INFO);
  Response response = new Response();
  try {
    Session session = connectionManager.getSession(keyspaceName);
    Builder selectBuilder;
    if (CollectionUtils.isNotEmpty(fields)) {
      selectBuilder = QueryBuilder.select(fields.toArray(new String[fields.size()]));
    } else {
      selectBuilder = QueryBuilder.select().all();
    }
    Select selectQuery = selectBuilder.from(keyspaceName, tableName);
    Where selectWhere = selectQuery.where();
    if (key instanceof String) {
      selectWhere.and(eq(Constants.IDENTIFIER, key));
    } else if (key instanceof Map) {
      Map<String, Object> compositeKey = (Map<String, Object>) key;
      compositeKey
          .entrySet()
          .stream()
          .forEach(
              x -> {
                CassandraUtil.createQuery(x.getKey(), x.getValue(), selectWhere);
              });
    }
    ResultSet results = session.execute(selectWhere);
    response = CassandraUtil.createResponse(results);
  } catch (Exception e) {
    ProjectLogger.log(Constants.EXCEPTION_MSG_FETCH + tableName + " : " + e.getMessage(), e);
    throw new ProjectCommonException(
        ResponseCode.SERVER_ERROR.getErrorCode(),
        ResponseCode.SERVER_ERROR.getErrorMessage(),
        ResponseCode.SERVER_ERROR.getResponseCode());
  }
  logQueryElapseTime("getRecordByIdentifier", startTime);
  return response;
}
 

public static Where buildQuery(String searchView, List<Clause> clauses, Ordering order, TimePageLink pageLink, String idColumn) {
    return buildQuery(searchView, clauses, Collections.singletonList(order), pageLink, idColumn);
}
 

public static Where buildQuery(String searchView, List<Clause> clauses, TimePageLink pageLink, String idColumn) {
    return buildQuery(searchView, clauses, Collections.emptyList(), pageLink, idColumn);
}
 

@Override
public Response updateRecord(
    String keyspaceName,
    String tableName,
    Map<String, Object> request,
    Map<String, Object> compositeKey) {

  long startTime = System.currentTimeMillis();
  ProjectLogger.log(
      "Cassandra Service updateRecord method started at ==" + startTime, LoggerEnum.INFO);
  Response response = new Response();
  try {
    Session session = connectionManager.getSession(keyspaceName);
    Update update = QueryBuilder.update(keyspaceName, tableName);
    Assignments assignments = update.with();
    Update.Where where = update.where();
    request
        .entrySet()
        .stream()
        .forEach(
            x -> {
              assignments.and(QueryBuilder.set(x.getKey(), x.getValue()));
            });
    compositeKey
        .entrySet()
        .stream()
        .forEach(
            x -> {
              where.and(eq(x.getKey(), x.getValue()));
            });
    Statement updateQuery = where;
    session.execute(updateQuery);
  } catch (Exception e) {
    ProjectLogger.log(Constants.EXCEPTION_MSG_UPDATE + tableName + " : " + e.getMessage(), e);
    if (e.getMessage().contains(JsonKey.UNKNOWN_IDENTIFIER)) {
      throw new ProjectCommonException(
          ResponseCode.invalidPropertyError.getErrorCode(),
          CassandraUtil.processExceptionForUnknownIdentifier(e),
          ResponseCode.CLIENT_ERROR.getResponseCode());
    }
    throw new ProjectCommonException(
        ResponseCode.dbUpdateError.getErrorCode(),
        ResponseCode.dbUpdateError.getErrorMessage(),
        ResponseCode.SERVER_ERROR.getResponseCode());
  }
  logQueryElapseTime("updateRecord", startTime);
  return response;
}