spring mongo data api learn

1 索引

1.1 单列索引

@Indexed
@Field(value = "delete_flag")
private Boolean deleteFlag = false;

@Indexed属性：name定义索引名称、unique是否为唯一索引，默认false

1.2 组合索引

@Document(collection = "#{T(com.nd.social.common.handler.TenantHandler).getTablePrefix().concat('block_content')}")
@CompoundIndexes(
        @CompoundIndex(name = "idx_bc_t_sc_s", def = "{'tenant':1,'scope.code':1,'sourceId':1}", unique = true)
)
@TypeAlias("BlockContent")

@CompoundIndexes(
        @CompoundIndex(name = "_ui_s_df_idx_", def = "{'userId':1, 'status':1, 'deleteFlag':1}", unique = true)
)

 2 注意

在自定义接口实现中使用数据库中的字段名作为查询条件，而不是实体类的属性名

如果进行+1操作 尽量使用inc 避免并发问题

3 排序

    private static final Sort SORT_BY_CREATE_TIME_DESC =
            new Sort(Sort.Direction.DESC, "createAt");
    List<Idea> finByUserIdAndDeleteFlagFalse(String userId, Sort sort);

 命名查询：OrderBy...Desc/ASC
    List<Idea> findByUserIdAndStatusInAndViewAtLessThanAndDeleteFlagFalseOrderByCreateAtDesc(String userId, List<IdeaStatus> status, long viewAt);

4 分页

1 offset/limit

    private Pageable getPageable(SearchVo condition) {
        int limit = condition.getLimit();
        int offset = condition.getOffset();
        return new PageRequest(offset / limit, limit, SORT_BY_CREATE_TIME_DESC);
    }

    private int offset = 0;
    private int limit = 15;

    public int getOffset() {
        return offset;
    }

    public void setOffset(int offset) {
        this.offset = offset;
    }

    public int getLimit() {
        return limit <= 0 ? 15 : limit;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

2 page/size

page 页码，请求第几页数据(默认值：1) 可选
size 每页数量(默认值：30) 可选
new PageRequest(page - 1, size)

3 计算总页数

pageVo.setTotalPage(size == 0 ? 1 : (int) Math.ceil((double) total / (double) size));  

4 结果集示例

public class PageVo<T> {
    // 总数
    private long totalCount;
    // 总页数
    private int totalPage;
    // 页码
    private int page;
    // 单页数量
    private int size;
    // 结果列表
    private List<T> items;

    public List<T> getItems() {
        return items;
    }

    public void setItems(List<T> items) {
        this.items = items;
    }

    public long getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(long totalCount) {
        this.totalCount = totalCount;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getSize() {
        return size;
    }

    public void setSize(int size) {
        this.size = size;
    }
}

public class Items<T> {

    // 结果列表 可以是Set/List
    private Collection<T> items;

    // 如果不需要 可以设置为items的size值
    private long totalCount;

    public static <T> Items<T> of(Collection<T> list) {
        Items<T> items = new Items<>();
        items.setItems(list);
        items.setTotalCount(list.size());
        return items;
    }

    public static <T> Items<T> of(Collection<T> list, long totalCount) {
        Items<T> items = new Items<>();
        items.setItems(list);
        items.setTotalCount(totalCount);
        return items;
    }

    public Collection<T> getItems() {
        return items;
    }

    public void setItems(Collection<T> items) {
        this.items = items;
    }

    public long getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(long totalCount) {
        this.totalCount = totalCount;
    }

}

5 打印mongo NoSql语句

显示操作mongo的语句，log4j.properties里面加入：

log4j.logger.org.springframework.data.mongodb.core=DEBUG, mongodb

log4j.appender.mongodb=org.apache.log4j.ConsoleAppender
log4j.appender.mongodb.Target=System.out
log4j.appender.mongodb.Threshold=DEBUG
log4j.appender.mongodb.ImmediateFlush=true
log4j.appender.mongodb.layout=org.apache.log4j.PatternLayout
log4j.appender.mongodb.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} %5p %X{RequestId} - %m%n

原因：
在mongo的底层实现中，如MongoTemplate中，判断了是否日志级别为debug，是的时候会打印语句出来，例如

private static final Logger LOGGER = LoggerFactory.getLogger(MongoTemplate.class);

protected void executeQuery(Query query, String collectionName, DocumentCallbackHandler dch,
            CursorPreparer preparer) {

        Assert.notNull(query);

        DBObject queryObject = queryMapper.getMappedObject(query.getQueryObject(), null);
        DBObject sortObject = query.getSortObject();
        DBObject fieldsObject = query.getFieldsObject();

        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Executing query: {} sort: {} fields: {} in collection: {}", serializeToJsonSafely(queryObject),
                    sortObject, fieldsObject, collectionName);
        }

        this.executeQueryInternal(new FindCallback(queryObject, fieldsObject), preparer, dch, collectionName);
}

6 注解查询

1 一个方法命名查询中同一个属性不能出现2次 可以使用@Query注解查询
2 @Query：
  value 查询语句
  count 作为统计的查询 返回int值
  delete 作为删除语句并返回删除后的文档集合
  fields 限定需要返回哪些字段

示例：

@Query(count = true, value = "{'$and':[{'tenant':?3},{'reportStatus':?0}," +
            " {'dealTime':{'$gte':?1}}, {'dealTime':{'$lte':?2}}]}")
int countByStatusAndDealTimeBetween(ReportStatus status, Date begin, Date end, long tenant);

@Query("{'$and':[{'userId':?0},{'deleteFlag':false}," +
        "{'$or':[{'content':{'$regex':?1}},{'location':{'$regex':?1}},{'createAtStr':{'$regex':?1}}]}]}")
List<Idea> findByKeyWord(String userId, String key, Pageable pageable);

{
    '$and': [
        {
            'userId': ?0
        },
        {
            'deleteFlag': false
        },
        {
            '$or': [
                {
                    'content': {
                        '$regex': ?1
                    }
                },
                {
                    'location': {
                        '$regex': ?1
                    }
                },
                {
                    'createAtStr': {
                        '$regex': ?1
                    }
                }
            ]
        }
    ]
}

7 MongoOptions/MongoTemplate

public <T> T findOne(Query query, Class<T> entityClass) 
public boolean exists(Query query, Class<?> entityClass)
public <T> List<T> find(Query query, Class<T> entityClass)
public <T> T findById(Object id, Class<T> entityClass)

public <T> T findAndModify(Query query, Update update, Class<T> entityClass)
public <T> T findAndModify(Query query, Update update, FindAndModifyOptions options, Class<T> entityClass)
public class FindAndModifyOptions {
    boolean returnNew;   // 是否返回更新后的值
    boolean upsert;      // 没有找到是否插入 
    boolean remove;      // 找到是否删除
}

public <T> T findAndRemove(Query query, Class<T> entityClass)
public long count(Query query, Class<?> entityClass) 

public void insert(Object objectToSave) 
public void insert(Collection<? extends Object> batchToSave, Class<?> entityClass)
public void insertAll(Collection<? extends Object> objectsToSave)

public void save(Object objectToSave) 保存/修改

public WriteResult upsert(Query query, Update update, Class<?> entityClass) 
public WriteResult updateFirst(Query query, Update update, Class<?> entityClass)
public WriteResult updateMulti(Query query, Update update, Class<?> entityClass)

public WriteResult remove(Object object)
public WriteResult remove(Query query, String collectionName)


public <T> List<T> findAll(Class<T> entityClass)
public <T> List<T> findAllAndRemove(Query query, Class<T> entityClass)

  public DB getDb()
  DBCollection getCollection(String collectionName); DBCollection 中包含各种CRUD操作以及对集合本身的定义操作（索引、命名）
  public String getCollectionName(Class<?> entityClass)

public <T> MapReduceResults<T> mapReduce(String inputCollectionName, String mapFunction, String reduceFunction, Class<T> entityClass)
public <T> MapReduceResults<T> mapReduce(String inputCollectionName, String mapFunction, String reduceFunction,MapReduceOptions mapReduceOptions, Class<T> entityClass) 

public <T> GroupByResults<T> group(String inputCollectionName, GroupBy groupBy, Class<T> entityClass)


public <O> AggregationResults<O> aggregate(TypedAggregation<?> aggregation, Class<O> outputType)
public <O> AggregationResults<O> aggregate(Aggregation aggregation, Class<?> inputType, Class<O> outputType)

distinct方法：

 public List<String> distinctUserId() {
     return mongoTemplate.getCollection("ideas").distinct("user_id");
 }

 public List<String> distinctLocation(String userId) {
     DBObject query = Query.query(Criteria.where("user_id").is(userId)).getQueryObject();
     return mongoTemplate.getCollection("ideas").distinct("location", query);
 }

Sort

private final List<Order> orders;

public Sort and(Sort sort) {
  if (sort == null) {
    return this;
  }
  ArrayList<Order> these = new ArrayList<Order>(this.orders);
  for (Order order : sort) {
     these.add(order);
  }
  return new Sort(these);
}

Query

    private Sort sort;
    private int skip;
    private int limit;

    public Query skip(int skip) {
        this.skip = skip;
        return this;
    }

    public Query limit(int limit) {
        this.limit = limit;
        return this;
    }

        public Query with(Pageable pageable) {

        if (pageable == null) {
            return this;
        }

        this.limit = pageable.getPageSize();
        this.skip = pageable.getOffset();

        return with(pageable.getSort());
    }

    public Query with(Sort sort) {

        if (sort == null) {
            return this;
        }

        for (Order order : sort) {
            if (order.isIgnoreCase()) {
                throw new IllegalArgumentException(String.format("Gven sort contained an Order for %s with ignore case! "
                        + "MongoDB does not support sorting ignoreing case currently!", order.getProperty()));
            }
        }

        if (this.sort == null) {
            this.sort = sort;
        } else {
            this.sort = this.sort.and(sort);
        }

        return this;
    }



private final Map<String, CriteriaDefinition> criteria = new LinkedHashMap<String, CriteriaDefinition>();

    public static Query query(CriteriaDefinition criteriaDefinition) {
        return new Query(criteriaDefinition);
    }

    public Query() {}


    public Query(CriteriaDefinition criteriaDefinition) {
        addCriteria(criteriaDefinition);
    }


    public Query addCriteria(CriteriaDefinition criteriaDefinition) {

        CriteriaDefinition existing = this.criteria.get(criteriaDefinition.getKey());
        String key = criteriaDefinition.getKey();

        if (existing == null) {
            this.criteria.put(key, criteriaDefinition);
        } else {
            throw new InvalidMongoDbApiUsageException("Due to limitations of the com.mongodb.BasicDBObject, "
                    + "you can't add a second '" + key + "' criteria. " + "Query already contains '"
                    + existing.getCriteriaObject() + "'.");
        }

        return this;
    }

Criteria

    private String key;
    private List<Criteria> criteriaChain;
    private LinkedHashMap<String, Object> criteria = new LinkedHashMap<String, Object>();
    private Object isValue = NOT_SET;

    public static Criteria where(String key) {
        return new Criteria(key);
    }

    public Criteria and(String key) {
        return new Criteria(this.criteriaChain, key);
    }

is ne lt lte gt gte in/all nin mod size exits type not regex
in: 包含其中一个即可
all:全部包含才可以       查询时要明确这多个值主键的关系是什么样的

public Criteria orOperator(Criteria... criteria)
public Criteria andOperator(Criteria... criteria)
public Criteria norOperator(Criteria... criteria)

8 案例

1 按照创建时间查找上一条下一条记录

 public Idea findIdeaNearTo(String userId, long createAt, boolean isNext) {
        Criteria criteria = Criteria.where("user_id").is(userId).and("delete_flag").is(false);
        Query query;
        if (isNext) {
            query = new Query(criteria).with(new Sort(Sort.Direction.ASC, "create_at"));
            criteria.and("create_at").gt(createAt);
        } else {
            query = new Query(criteria).with(new Sort(Sort.Direction.DESC, "create_at"));
            criteria.and("create_at").lt(createAt);
        }
        return mongoTemplate.findOne(query, Idea.class);
 }

next:
{ "user_id" : "2107164232" , "delete_flag" : false , "create_at" : { "$gt" : 1474600921000}}
pre：
{ "user_id" : "2107164232" , "delete_flag" : false , "create_at" : { "$lt" : 1474600921000}}

2 orOperator / andOperator

    public List<Idea> find(String userId, IdeaStatus status, OriginalityType type, long createAtFrom, long createAtTo) {
        Criteria criteria = Criteria.where("user_id").is(userId)
                .and("delete_flag").is(false)
                .and("status").in(status);
        if (type == null) {
            criteria.orOperator(
                    Criteria.where("originality_type").exists(false), // 字段是否存在 exists
                    Criteria.where("originality_type").size(0));      // 字段是数组，大小 size
        } else {
            criteria.and("originality_type").in(type);
        }
        criteria.andOperator(
                Criteria.where("create_at").gte(createAtFrom),
                Criteria.where("create_at").lt(createAtTo)
        );
        return mongoTemplate.find(new Query(criteria), Idea.class);
    } 

{
    "user_id": "290536",
    "delete_flag": false,
    "status": {
        "$in": [
            "originality"
        ]
    },
    "$or": [
        {
            "originality_type": {
                "$exists": false
            }
        },
        {
            "originality_type": {
                "$size": 0
            }
        }
    ],
    "$and": [
        {
            "create_at": {
                "$gte": 1445788800000
            }
        },
        {
            "create_at": {
                "$lt": 1446393600000
            }
        }
    ]
}

 注意：一个字段有多个条件限制，需要使用多个Criteria实现。各个Criteria之间使用orOperator或andOperator链接。

 案例2

 public Items<Idea> listOriginalities(String userId, SearchVo condition, Pageable pageable) {
        List<Criteria> orCriteriaList = new ArrayList<>();
        if (condition.getStatus() == null || condition.getStatus().size() == 0) {
            orCriteriaList.add(Criteria.where("status").in(Arrays.asList(IdeaStatus.originality)));
        } else {
            for (IdeaStatus status : condition.getStatus()) {
                orCriteriaList.add(Criteria.where("status").all(Arrays.asList(status, IdeaStatus.originality)));
            }
        }
        Criteria criteria = Criteria.where("userId").is(userId).and("deleteFlag").is(false)
                .orOperator(orCriteriaList.toArray(new Criteria[0]));

        if (!CollectionUtils.isEmpty(condition.getTag())) {
            criteria.and("tags").in(condition.getTag());
        }
        Query query = query(criteria).with(pageable);
        Query countQuery = query(criteria);
        return Items.of(mongoTemplate.find(query, Idea.class), mongoTemplate.count(countQuery, Idea.class));
    }

{
    "user_id": "2107164232",
    "delete_flag": false,
    "$or": [
        {
            "status": {
                "$all": [
                    "discussing",
                    "originality"
                ]
            }
        },
        {
            "status": {
                "$all": [
                    "considering",
                    "originality"
                ]
            }
        }
    ]
}

localhost:9088/v0.1/ideas/originality?tag=系统默认&status=discussing,considering

要求status必须是originality，若条件中包含status，必须是其中之一或全是。

public Criteria orOperator(Criteria... criteria) 

把条件中每一个状态拆分与originality组成一个查询条件，这样就得到一组查询条件，每个条件是or的关系。

3 更新或保存  更新返回旧值

 public BlockContent addIfNotExists(BlockContent blockContent, long tenant) {
        Query query = Query.query(where("tenant").is(tenant)
                .and("sourceId").is(blockContent.getSourceId())
                .and("scope.code").is(blockContent.getScope().getCode()));

        Update update = getUpdate(blockContent);

        return operations.findAndModify(
                query, update,
                options().upsert(true).returnNew(false),
                BlockContent.class
        );
    }
返回null 保存
返回旧值 更新 使用他的id

3 更新语句避免 n+1操作

    public void setBlockStatusAndRecoverTime(Set<String> ids, long tenant, Date recoverTime) {
        Query query = Query.query(
                where("_id").in(ids)
                        .and("tenant").is(tenant)
                        .and("blockStatus").is(BlockStatus.BLOCKED));

        operations.updateMulti(
                query,
                Update.update("blockStatus", BlockStatus.RECOVER)
                        .set("recoverTime", recoverTime),
                BlockContent.class
        );
    }

对于一样的修改动作，尤其更新少量字段时候(deleteFlag，dealTime，status)，使用一条更新语句更新多个字段（updateMulti），而不是先查询，修改后保存