Java通过mongo-java-driver-3.0+查询mongodb数据库

妖狐艹你老母 2022-06-07 12:10 365阅读 0赞

本文以mongo-java-driver-3.5.0.jar为例

**1 基本查询关键字（对应的sql语句）：**

<table> 
 <thead> 
  <tr> 
   <th>MongoDB Aggregation Operators</th> 
   <th>SQL Terms, Functions, and Concepts</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>$match</td> 
   <td>WHERE/HAVING</td> 
  </tr> 
  <tr> 
   <td>$group</td> 
   <td>GROUP BY</td> 
  </tr> 
  <tr> 
   <td>$project</td> 
   <td>SELECT</td> 
  </tr> 
  <tr> 
   <td>$limit</td> 
   <td>LIMIT</td> 
  </tr> 
  <tr> 
   <td>$sort (降序:-1 升序:1)</td> 
   <td>ORDER BY (降序:DESC 升序:ASC默认)</td> 
  </tr> 
  <tr> 
   <td>$skip 跳过n个</td> 
   <td>持久化框架有接口</td> 
  </tr> 
  <tr> 
   <td>$sum</td> 
   <td>SUM()/COUNT()</td> 
  </tr> 
  <tr> 
   <td>$in</td> 
   <td>IN()</td> 
  </tr> 
  <tr> 
   <td>$nin</td> 
   <td>NOT IN()</td> 
  </tr> 
  <tr> 
   <td>$or</td> 
   <td>OR</td> 
  </tr> 
  <tr> 
   <td>$and</td> 
   <td>AND</td> 
  </tr> 
  <tr> 
   <td>$regex 模糊查询</td> 
   <td>LIKE</td> 
  </tr> 
  <tr> 
   <td>$gt</td> 
   <td>&gt;</td> 
  </tr> 
  <tr> 
   <td>$gte</td> 
   <td>&gt;=</td> 
  </tr> 
  <tr> 
   <td>$lt</td> 
   <td>&lt;</td> 
  </tr> 
  <tr> 
   <td>$lte</td> 
   <td>&lt;=</td> 
  </tr> 
  <tr> 
   <td>$ne</td> 
   <td>!=</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>=</td> 
  </tr> 
 </tbody> 
</table>

**2 构造复杂查询条件的3种方法：**

2.1 测试数据

{ "_id" : { "$oid" : "59c8cbea6157d6c78dab4ff0" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 2 }
    { "_id" : { "$oid" : "59c9c1393471f5330c65ccd2" }, "name" : "nie", "password" : "nie", "nickname" : "nn", "iid" : 8 }
    { "_id" : { "$oid" : "59c9ec75a4e55c541a000003" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 8.0, "isRead" : false, "createTime" : { "$date" : 1506405705952 }, "money" : 88.88 }
    { "_id" : { "$oid" : "59b34277beca1aaeaf1d1fbe" }, "name" : "yan", "password" : "ygirlyan", "nickname" : "ygirl2", "iid" : 3.0 }
    { "_id" : { "$oid" : "59b34282beca1aaeaf1d1fbf" }, "name" : "yan", "password" : "ygirlyan", "nickname" : "ygirl2", "iid" : 4.0 }

2.3 实现构造复杂查询条件的3种方法：

public static MongoCollection<Document> connect() {  
        @SuppressWarnings("resource")  
        MongoClient client = new MongoClient("127.0.0.1", 27017);  
        MongoDatabase db = client.getDatabase("test");  
        MongoCollection<Document> collection = db.getCollection("user");  
        return collection;
    } 
    
    public static List<Document> findBy(Bson filter) {  
        List<Document> results = new ArrayList<Document>();  
        FindIterable<Document> iterables = connect().find(filter);  
        MongoCursor<Document> cursor = iterables.iterator();  
        while (cursor.hasNext()) {  
            results.add(cursor.next());  
        }  
    
        return results;  
    }  
    public static List<Document> findBy1(DBObject filter) {  
        List<Document> results = new ArrayList<Document>();  
        FindIterable<Document> iterables = connect().find((Bson) filter);  
        MongoCursor<Document> cursor = iterables.iterator();  
        while (cursor.hasNext()) {  
            results.add(cursor.next());  
        }  
    
        return results;  
    }  
    
    // 方法1：运用Filters.and添加复合条件；
    @Test  
    public void testFindByLickQuery2(){  
        int iid = 2;
        String name = "yy";
        String nickname = "ygirl2";
        Bson filter = null;
        if (iid >= 0) {
            if (name != "") {
                if (nickname != "") {
                    filter = Filters.and(Filters.gte("iid", iid), Filters.eq("nickname", nickname), Filters.regex("name", name));
                } else {
                    filter = Filters.and(Filters.gte("iid", iid), Filters.regex("name", name));
                }
            } else {
                if (nickname != "") {
                    filter = Filters.and(Filters.gte("iid", iid), Filters.eq("nickname", nickname));
                } else {
                    filter = Filters.and(Filters.gte("iid", iid));
                }
            }
        } else {
            if (name != "") {
                if (nickname != "") {
                    filter = Filters.and(Filters.eq("nickname", nickname), Filters.regex("name", name));
                } else {
                    filter = Filters.and(Filters.regex("name", name));
                }
            } else {
                if (nickname != "") {
                    filter = Filters.and(Filters.eq("nickname", nickname));
                } else {
                    filter = Filters.and();
                }
            }
        }
        List<Document> results = findBy(filter);
        for(Document doc : results){  
            System.out.println(doc.toJson());  
        }  
        // { "_id" : { "$oid" : "59c8cbea6157d6c78dab4ff0" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 2 }
        // { "_id" : { "$oid" : "59c9ec75a4e55c541a000003" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 8.0, "isRead" : false, "createTime" : { "$date" : 1506405705952 }, "money" : 88.88 }
    }  
    
    // 方法2：运用Document filter.append添加复合条件；
    @Test  
    public void testFindByLickQuery1(){  
        int iid = 3, iid2 = 4;
        String name = "";
        String nickname = "";
        Document filter = new Document(); 
        if (iid >= 0) {
            filter.append("iid", new Document("$gte", iid));
        }
        // 得到iid<=4结果；
        // iid>=3条件被覆盖，Document.append不适合在同一个结果添加多个条件
        if (iid2 >= 0) {
            filter.append("iid", new Document("$lte", iid2));
    
        }
        if (name != "") {
            filter.append("name",new Document("$regex", name));
        }
        if (nickname != "") {
            filter.append("nickname",nickname);  
        }
    
        List<Document> results = findBy(filter); 
        for(Document doc : results){  
            System.out.println(doc.toJson());  
        }  
        // { "_id" : { "$oid" : "59c8cbea6157d6c78dab4ff0" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 2 }
        // { "_id" : { "$oid" : "59b34277beca1aaeaf1d1fbe" }, "name" : "yan", "password" : "ygirlyan", "nickname" : "ygirl2", "iid" : 3.0 }
        // { "_id" : { "$oid" : "59b34282beca1aaeaf1d1fbf" }, "name" : "yan", "password" : "ygirlyan", "nickname" : "ygirl2", "iid" : 4.0 }
    } 
    
    // 方法3：运用DBObject.put添加复合条件；
    @Test  
    public void testFindByLickQuery1Copy(){  
        int iid = 3, iid2 = 4;
        String name = "";
        String nickname = "";
        DBObject filter = new BasicDBObject();  
        if (iid >= 0) {
            filter.put("iid", new BasicDBObject("$gte", iid));          
        }
        // 得到iid<=4结果；
        // iid>=3条件被覆盖，Document.append不适合在同一个结果添加多个条件
        if (iid2 >= 0) {
            if (iid >= 0) {
                BasicDBObject[] queryObjectAnd =  new BasicDBObject[] { new BasicDBObject("iid", new BasicDBObject("$gte", iid)),  
                        new BasicDBObject("iid", new BasicDBObject("$lte", iid2)) };
                filter.put("$and", queryObjectAnd);
            } else {
            filter.put("iid",  new BasicDBObject("$lte", iid2)); 
            }
    
        }
        if (name != "") {
            filter.put("name",new BasicDBObject("$regex", name));
        }
        if (nickname != "") {
            filter.put("nickname",nickname);  
        }
    
        List<Document> results = findBy1(filter); 
        for(Document doc : results){  
            System.out.println(doc.toJson());  
        } 
        // { "_id" : { "$oid" : "59b34277beca1aaeaf1d1fbe" }, "name" : "yan", "password" : "ygirlyan", "nickname" : "ygirl2", "iid" : 3.0 }
        // { "_id" : { "$oid" : "59b34282beca1aaeaf1d1fbf" }, "name" : "yan", "password" : "ygirlyan", "nickname" : "ygirl2", "iid" : 4.0 }
    }

2.3 分析构造复杂查询条件的3种方法异同：

<table> 
 <thead> 
  <tr> 
   <th>方法</th> 
   <th>实现</th> 
   <th>难易程度</th> 
   <th>能否给同一个字段加大于1个约束条件</th> 
   <th>适用于</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>方法1</td> 
   <td>Bson filter=Filters.and(Filters 1,…)</td> 
   <td>条件越多实现越困难，if-else越复杂</td> 
   <td>能</td> 
   <td>查询条件少且固定永远不变</td> 
  </tr> 
  <tr> 
   <td>方法2</td> 
   <td>Document f=append(key, v)<br>Document f=append(key, newBasicDBObject(“$gte”,v))</td> 
   <td>条件越多实现不会太困难</td> 
   <td>不能，后面的append条件会覆盖前面的</td> 
   <td>查询条件多且每个属性查询条件&lt;=1</td> 
  </tr> 
  <tr> 
   <td>方法3</td> 
   <td>DBObject filter=put(“iid”,new BasicDBObject(“$gte”,v));</td> 
   <td>条件越多实现不会太困难</td> 
   <td>能</td> 
   <td>适用于一切查询，特别适用于复杂条件查询应用</td> 
  </tr> 
 </tbody> 
</table>

**3 其他查询关键字应用：**

3.1 String类型按条件聚合

@Test
    public void testGroup() {
         /* Group操作*/  
        //DBObject groupFields = new BasicDBObject("_id", new BasicDBObject("name", "$name").append("nickname", "$nickname")); 
        // 1 加入标志项_id，即聚合字段值;
        DBObject groupFields = new BasicDBObject("_id", "$name");  
        // 2 加入统计数count，即聚合个数；
        groupFields.put("count", new BasicDBObject("$sum", 1));  
        DBObject group = new BasicDBObject("$group", groupFields); 
    
        // 排序操作 ，按count降序
        DBObject sortchBasicDBObjet = new BasicDBObject("$sort", new BasicDBObject("count",-1));  
    
        // 分页操作.skip(startRow)，首先取2个记录，再跳过1个记录取出第二个记录显示出来；
        DBObject limitBasicDBObjet = new BasicDBObject("$limit",2); 
        DBObject limitBasicDBObjet2 = new BasicDBObject("$skip",2);
    
        // project 操作 : 过滤查询显示字段个数,db.books.aggregate( [ { $project : { _id: 0, name : 1 , password : 1 }]);只显示name和password
        // DBObject projectBasicDBObjet = new BasicDBObject("$project", new BasicDBObject("_id",0).append("times", "$_id.times").append("user_id", "$_id.user_id").append("user_account", "$_id.user_account").append("count", 1));
        List<Bson> list = new ArrayList<Bson>();  
        list.add((Bson) group); 
        list.add((Bson) sortchBasicDBObjet);  
        // $limit和$skip add到list顺序不一样，结果不一样
        list.add((Bson) limitBasicDBObjet2);
        list.add((Bson) limitBasicDBObjet);  
        // list.add((Bson) limitBasicDBObjet2);
        List<Document> results = MongoDb.group(list);; 
        for(Document doc : results){  
            System.out.println(doc.toJson());  
        }  
    }
    
    
    /* * * isRead "0"表示未读，"1"表示已读 */
    public List<Object> findIfreadAlarmLog(String isRead, String CommunityName, String EQAddress) {
        MongoCollection<Document> collection = getMongodbCollection();
        // 添加统计统计
        //DBObject matchBasicDBObjet = new BasicDBObject("$match",new BasicDBObject("IsRead", "0"));
        DBObject queryCondition = new BasicDBObject();  
        queryCondition.put("IsRead", isRead);
        if (CommunityName != null && !"".equals(CommunityName)) {
            // 模糊匹配
            queryCondition.put("CommunityName", new BasicDBObject("$regex", CommunityName));
        }
        if (EQAddress != null && !"".equals(EQAddress)) {
            // 模糊匹配
            queryCondition.put("EQAddress", new BasicDBObject("$regex", EQAddress));
        }
        DBObject matchBasicDBObjet = new BasicDBObject("$match", queryCondition);
    
        // 添加统计条件
        DBObject groupFields = new BasicDBObject("_id", new BasicDBObject("CommunityID", "$CommunityID").append("CommunityName", "$CommunityName").append("EQName", "$EQName")
                .append("EQAccount", "$EQAccount").append("EQAddress", "$EQAddress").append("Coordinate", "$Coordinate").append("SingleCoordinate", "$SingleCoordinate"));  
        // 加入统计数count，即聚合个数；
           groupFields.put("count", new BasicDBObject("$sum", 1));  
           DBObject group = new BasicDBObject("$group", groupFields); 
    
           // 添加显示条件
           DBObject projectBasicDBObjet = new BasicDBObject("$project", new BasicDBObject("_id",0).append("CommunityID", "$_id.CommunityID").append("CommunityName", "$_id.CommunityName").append("EQName", "$_id.EQName")
                .append("EQAccount", "$_id.EQAccount").append("EQAddress", "$_id.EQAddress").append("Coordinate", "$_id.Coordinate").append("SingleCoordinate", "$_id.SingleCoordinate").append("count", 1));
           List<Bson> list = new ArrayList<Bson>(); 
           list.add((Bson) matchBasicDBObjet); 
           list.add((Bson) group); 
           list.add((Bson) projectBasicDBObjet); 
           AggregateIterable<Document> iterables = collection.aggregate(list);       
           List<Object> results = new ArrayList<Object>();  
           MongoCursor<Document> cursor = iterables.iterator();  
           while (cursor.hasNext()) {  
               results.add(cursor.next());  
           }  
           return results;  
    }

3.2 json格式的查询

/* * 先转换为Object再查询 */
    public List<String> getHouseHouseAccounts(List<Object> singleCoords , String isRead) {
        MongoCollection<Document> collection = getMongodbCollection();
        DBObject queryCondition = new BasicDBObject();  
        List<String> resultArray = new ArrayList<String>();
        // 报错：org.bson.codecs.configuration.CodecConfigurationException: Can't find a codec for class cn.com.vo.Point.,Point包含lng，lat属性
        // queryCondition.put("SingleCoordinate", JsonUtils.jsonToPojo(singleCoord, Point.class));
    
        // 返回空
        //queryCondition.put("SingleCoordinate", singleCoord);
    
        // 成功返回，(DBObject) JSON.parse(singleCoord)=JSON.parse(singleCoord)={ "lng" : 102.731653 , "lat" : 25.052474}，88:H20170926000002 88:H20170926000003,H20170926000004
        //queryCondition.put("SingleCoordinate", JSON.parse(singleCoord));
    
        // 成功返回，JsonUtils.jsonToPojo(singleCoord, Object.class)={lng=102.731653, lat=25.052474}，88:H20170926000002 88:H20170926000003,H20170926000004
        for (Object object : singleCoords) {
            //queryCondition.put("SingleCoordinate", JsonUtils.jsonToPojo(singleCoord, Object.class));
            queryCondition.put("SingleCoordinate", object);
            queryCondition.put("IsRead", isRead);
            List<Object> list = new ArrayList<Object>();
            FindIterable<Document> findIterable =collection.find((Bson) queryCondition);
            MongoCursor<Document> mongoCursor = findIterable.iterator();
            while(mongoCursor.hasNext()){  
               Document document=mongoCursor.next();
               list.add(document);         
            }  
            List<AlarmLog> alarmLogLists = JsonUtils.jsonToList(JsonUtils.objectToJson(list), AlarmLog.class);
            for (AlarmLog alarmLog : alarmLogLists) {
                resultArray.add("'" + alarmLog.getHouseAccount() + "'");
            }
        }
    
        //return StringUtils.collectionToDelimitedString(resultArray, ",");
        return resultArray;
    }

3.3 $or和$and关键字查询

/** * Document OR AND 查询:db.user.find({"likes": {$gt:50}, $or: [{"by": "菜鸟教程"},{"title": "MongoDB 教程"}]}).pretty()； * 适合少条件且条件不变的查询，适合单字段多条件查询 */
    @Test  
    public void testFindByOrAnd(){   
        // (查询iid等于2或者iid等于8的数据)  失败   只能查到8
        Document filter = new Document(); 
        filter.append("iid", 2);
        filter.append("iid", 8);
        List<Document> results = MongoDb.findBy(filter);
    
        // $or (查询iid等于2或者iid等于8的数据)  
        BasicDBObject queryObjectOr = new BasicDBObject().append(  
                "$or",  
                new BasicDBObject[] { new BasicDBObject("iid", 2),  
                        new BasicDBObject("iid", 8) }); 
    
        // (查询iid等于2并且name等于nie的数据)   成功查出相应数据
        Document filter2 = new Document(); 
        filter2.append("iid", 2);
        filter2.append("name", "yy");
        List<Document> results2 = MongoDb.findBy(filter2);
    
        // $and(查询iid等于2并且name等于nie的数据)  
        BasicDBObject queryObjectAnd = new BasicDBObject().append("$and",  
                new BasicDBObject[] { new BasicDBObject("iid", 2),  
                        new BasicDBObject("name", "yy") }); 
        List<Document> results3 = MongoDb.findBy(queryObjectOr); 
        List<Document> results4 = MongoDb.findBy(queryObjectAnd);
        for(Document doc : results){  
            System.out.println(doc.toJson()); 
        }  
        System.out.println("=========="); 
        for(Document doc : results3){  
            System.out.println(doc.toJson()); 
        }  
        System.out.println("=========="); 
        for(Document doc : results4){  
            System.out.println(doc.toJson()); 
        } 
        System.out.println("=========="); 
        for(Document doc : results2){  
            System.out.println(doc.toJson()); 
        } 
        // { "_id" : { "$oid" : "59c9c1393471f5330c65ccd2" }, "name" : "nie", "password" : "nie", "nickname" : "nn", "iid" : 8 }
        // { "_id" : { "$oid" : "59c9ec75a4e55c541a000003" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 8.0, "isRead" : false, "createTime" : { "$date" : 1506405705952 }, "money" : 88.88 }
        // ==========
        // { "_id" : { "$oid" : "59c8cbea6157d6c78dab4ff0" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 2 }
        // { "_id" : { "$oid" : "59c9c1393471f5330c65ccd2" }, "name" : "nie", "password" : "nie", "nickname" : "nn", "iid" : 8 }
        // { "_id" : { "$oid" : "59c9ec75a4e55c541a000003" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 8.0, "isRead" : false, "createTime" : { "$date" : 1506405705952 }, "money" : 88.88 }
        // ==========
        // { "_id" : { "$oid" : "59c8cbea6157d6c78dab4ff0" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 2 }
        // ==========
        // { "_id" : { "$oid" : "59c8cbea6157d6c78dab4ff0" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 2 }
    
    }

3.4$in和$nin关键字查询

/**
     * Document  in  nin 查询
     */
    @Test  
    public void testFindByInNin(){   
    
        // $in  （查询iid为1和2的数据）
        BasicDBObject queryObjectIn = new BasicDBObject().append("iid", new BasicDBObject(  
                "$in", new int[] { 2, 8 })); 
        // $nin  （查询iid不为1,2,3,4,5,6,7,8,9的数据）
        BasicDBObject queryObjectNin = new BasicDBObject().append("iid", new BasicDBObject(
                "$nin", new int[] { 2, 9 })); 
        List<Document> results3 = MongoDb.findBy(queryObjectIn); 
        List<Document> results4 = MongoDb.findBy(queryObjectNin);
        for(Document doc : results3){  
            System.out.println(doc.toJson());  
        }  
        // { "_id" : { "$oid" : "59c8cbea6157d6c78dab4ff0" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 2 }
        // { "_id" : { "$oid" : "59c9c1393471f5330c65ccd2" }, "name" : "nie", "password" : "nie", "nickname" : "nn", "iid" : 8 }
        // { "_id" : { "$oid" : "59c9ec75a4e55c541a000003" }, "name" : "yy", "password" : "123", "nickname" : "ygirl2", "iid" : 8.0, "isRead" : false, "createTime" : { "$date" : 1506405705952 }, "money" : 88.88 }
    }