Mongo DB query examples

Greater than:

db.getCollection('StorageContent').find({ $and:[
    {"shardKey" : "7507501"},
    {"cd": {$gte: ISODate("2017-07-02T00:00:00.731Z")}}
]})

Less than:

db.getCollection('NotificationProfile').find({ $and:[
    {createdBy: "hban"}, 
    {deliveryType: "F"}, 
    { 
    "createdDate": {
        $lte: ISODate("2018-11-30")
    }}
]})

Between:

db.getCollection('LogEntry').find({ $and:[
    {
    "time": {
        $gte: ISODate("2017-11-10")
    }}, 
    { 
    "time": {
        $lte: ISODate("2017-11-17")
    }}
]})

Oerder By date:

db.getCollection('notifyEmailSent').find({ $and:[
    {
    "created": {
        $gte: ISODate("2018-12-01 16:00:00:000Z")
    }}
]}).sort({"created": -1})

Exist:

db.getCollection('NotificationProfile').find({ $and:[
    {
        "terminal":{$exists:true}
    },
    {
        "company": '100'
    }
]})

Group By:

db.getCollection('DocumentAudit').aggregate([  
   {  
      "$match":{  
         "tp":"STR",
         "stp":"SSC",
      }
   },
   {  
      "$group":{  
         _id:{  
            "grp":"$grp",
            "com":"$com",
            "tp":"$tp",
            "stp":"$stp"
         },
         count:{  
            $sum:1
         }
      }
   },
   {  
      "$sort":{  
         "_id.grp":1
      }
   },
   {  
      "$sort":{  
         "_id.com":1
      }
   },
   {  
      "$sort":{  
         "_id.tp":1
      }
   },
   {  
      "$project":{  
         "count":1,
         "group":"$_id.grp",
         "company":"$_id.com",
         "type":"$_id.tp",
         "subType":"$_id.stp"
      }
   },
   {  
      "$limit":2000
   }
])

NoSQL vs RDBMS

nosql-img3
Overview:
NoSQL (Not only SQL) have been growing beside RDB. The reason for growing is it’s good for Big Data.

Compare to traditional RDB, NoSQL is FAST for a large amount of data. RDB (Relational Database) support Transaction (Commit/Rollback) sometimes know as ACID (Atomicity, Consistency, Isolation, Durability). This is one of reasons that RDB is slow compared to NoSQL.

For example, NoSQL updates a data based on its last request; It’s like LIFO (Last In First Out). It has time stamp to determined the latest record.

MongoDB + Spring MVC

mongodb-img3
NoSQL (Not Only SQL) is growing beside a RDB (Relational database). For example, DynamoDB for Amazon, BigTable for Google, Apollo for Facebook, and so forth. MongoDB is the one of most popular NoSQL DB.


Overview

  • It is said to be “Documente Oriented”, and they call table as “Collection”.
  • Unlike RDB, it does not support JOIN.
  • It Supports an ARRAY in a single record (document).
  • The format looks like JSON object. Therefore, it is good to use with Web Service.
  • Mongo DB is written in C++

This example is Spring MVC with CRUD operation by Mongo DB to demonstrate Web Service. And here is the configuration:
mongo-config

<!-- Mongo DB -->
<dependency>
	<groupId>org.springframework.data</groupId>
	<artifactId>spring-data-mongodb</artifactId>
	<version>1.5.2.RELEASE</version>
</dependency>
<dependency>
	<groupId>org.mongodb</groupId>
	<artifactId>mongo-java-driver</artifactId>
	<version>2.12.4</version>
</dependency>

<dependency>
	<groupId>com.drewnoakes</groupId>
	<artifactId>metadata-extractor</artifactId>
	<version>2.4.0-beta-1</version>
</dependency>

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" version="3.0">
  <display-name>ns2015mvcmongo001</display-name>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>

    <servlet>
        <servlet-name>ns2015</servlet-name>
        <servlet-class>
            org.springframework.web.servlet.DispatcherServlet
        </servlet-class>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>ns2015</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
</web-app>

NOTE: At line 16 of web.xml, servlet-name is specified as ns2015. Therefore, servlet context file will be “ns2015-servlet.xml“.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:mongo="http://www.springframework.org/schema/data/mongo"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
        http://www.springframework.org/schema/data/mongo http://www.springframework.org/schema/data/mongo/spring-mongo-1.5.xsd">

	<context:component-scan base-package="com.ns.spring" />
	 
	<!-- Configuration defining views files -->	 
	<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
	    <property name="prefix">
	        <value>/WEB-INF/jsp/</value>
	    </property>
	    <property name="suffix">
	        <value>.jsp</value>
	    </property>
	</bean>
	 
	<!-- Factory bean that creates the Mongo instance -->
	<bean id="mongo" class="org.springframework.data.mongodb.core.MongoFactoryBean">
	    <property name="host" value="localhost" />
	</bean>
	 
	<!-- MongoTemplate for connecting and quering the documents in the database -->
	<bean id="mongoTemplate" class="org.springframework.data.mongodb.core.MongoTemplate">
	     <constructor-arg name="mongo" ref="mongo" />
	     <constructor-arg name="databaseName" value="nsdb" />
	</bean>

	<!-- To scan sub interface for MongoRepository -->
	<mongo:repositories base-package="com.ns.spring" />
</beans>

Mongo Repository

mongodb-img3
For CRUD operation in Mongo DB, you have basically two choices:

  • MongoTemplate
  • MongoRepository

And MongoRepository is said to be more advanced compared to MongoTemplate. MongoTemplate provide more pre-defined method than MongoRepository, but MongoRepository is very much like Generic DAO. There are some pre-defined method but you can define your custom methods.

2015-06-05_08h32_07

package com.ns.spring.dao;

import java.util.List;

import org.springframework.data.mongodb.repository.MongoRepository;
import org.springframework.stereotype.Repository;
import org.springframework.data.mongodb.repository.Query;
import com.ns.spring.model.RMA_HDR;


@Repository
public interface RmaHdrRepository extends MongoRepository<RMA_HDR, String>{
	
	@Query("{rma_num : ?0}")
	List<RMA_HDR> findByRma_num(String rma_num);

	@Query("{rma_hdr_sts_cd : ?0}")
	List<RMA_HDR> findByRma_hdr_sts_cd(String findByRma_hdr_sts_cd);
	
	@Query("{'rma_hdr_sts_cd' : {$ne : ?0}}")
	List<RMA_HDR> findByRma_exclude(String findByRma_hdr_sts_cd);
	
}

The syntax for MongoDB statements

In MongoDB, the table (RDB) is called “Collection”.
For inquiry: db.”collection name”.find()


Following are some customized generic method defined in RmaHdrRepository.java (line 14 and 20). These will be executed in Mongo DB.

@Query("{rma_num : ?0}")
List<RMA_HDR> findByRma_num(String rma_num);
    /* select * from  rma_hdr where rma_num  = 'RMA00026' */
    db.rma_hdr.find({rma_num : 'RMA00026'})
    

    momgo-select-by-id-img1

@Query("{'rma_hdr_sts_cd' : {$ne : ?0}}")
List<RMA_HDR> findByRma_exclude(String findByRma_hdr_sts_cd);
    /* select * from  rma_hdr where rma_hdr_sts_cd <> 'auth' */
    db.rma_hdr.find({'rma_hdr_sts_cd' : {$ne : 'auth'}})
    

    momgo-select-by-not-eq-img1

NOTE: Equivalent SQL are showing as comments.

List<RMA_HDR> list = rmaRep.findByRma_num(rma_num);
List<RMA_HDR> rmaList = rmaRep.findByRma_exclude("auth");

Called by MVCController.java at line 49, 86 and 96.


Following are non-customized method; Already provided by Mongo Repository

    findAll()
    save(object)
    delete(object)
List<RMA_HDR> rmaList = rmaRep.findAll();
rmaRep.save(rma);
rmaRep.delete(rma);

Called by MVCController.java at line 35, 61, 81, 101, 107.


package com.ns.spring;

import java.util.ArrayList;
import java.util.List;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.ns.spring.dao.RmaHdrRepository;
import com.ns.spring.model.RMA_HDR;
import com.sun.jersey.api.client.Client;
import com.sun.jersey.api.client.ClientResponse;
import com.sun.jersey.api.client.WebResource;

@Controller
public class MVCController {

	private String url = "http://localhost:8080/NS2015V07/ns-home/json";

	@Autowired
	private RmaHdrRepository rmaRep;

	@RequestMapping(value = "/welcome_01")
	public ModelAndView init01(ModelMap model) {

		ModelAndView modelAndView = new ModelAndView("welcome_01");
		List<RMA_HDR> rmaList = rmaRep.findAll();
		modelAndView.addObject("rmaList", rmaList);

		// mast be match with jsp name to be displayed
		return modelAndView;
	}

	@RequestMapping(value = "/welcome_01", params = "webService", method = RequestMethod.POST)
	public ModelAndView refreshByWebService() {

		ModelAndView modelAndView = new ModelAndView("welcome_01");
		List<JSONObject> jsonList = getRmaHdrList();
		saveRma(jsonList);

		List<RMA_HDR> rmaList = rmaRep.findByRma_exclude("auth");
		modelAndView.addObject("rmaList", rmaList);

		return modelAndView;
	}

	@RequestMapping("/authorize/{rmaNum}")
	public ModelAndView authorizeRma(@PathVariable("rmaNum") String rmaNum) {

		ModelAndView modelAndView = new ModelAndView("welcome_01");
		saveByRmaNum(rmaNum, "auth");

		List<RMA_HDR> rmaList = rmaRep.findAll();
		modelAndView.addObject("rmaList", rmaList);

		return modelAndView;
	}

	private void saveRma(List<JSONObject> jsonList) {

		for (int i = 0; i < jsonList.size(); i++) {
			JSONObject rmaJson = jsonList.get(i);
			Long id = (Long) rmaJson.get("id");
			String rmaNum = (String) rmaJson.get("rmaNum");
			String stsCd = (String) rmaJson.get("rmaHdrStsCd");

			deleteByRmaNum(rmaNum);

			RMA_HDR rma = new RMA_HDR();
			rma.setRma_num(rmaNum);
			rma.setRma_hdr_sts_cd(stsCd);

			rmaRep.save(rma);
		}
	}

	private void deleteByRmaNum(String rma_num) {
		List<RMA_HDR> list = rmaRep.findByRma_num(rma_num);
		if (list != null && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				RMA_HDR temp = list.get(i);
				delete(temp);
			}
		}
	}

	private void saveByRmaNum(String rma_num, String stsNm) {
		List<RMA_HDR> list = rmaRep.findByRma_num(rma_num);
		if (list != null && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				RMA_HDR temp = list.get(i);
				temp.setRma_hdr_sts_cd(stsNm);
				rmaRep.save(temp);
			}
		}
	}

	private void delete(RMA_HDR obj) {
		rmaRep.delete(obj);
	}

	private List<JSONObject> getRmaHdrList() {
		String output = getJsonStrByURL(this.url);
		List<JSONObject> list = new ArrayList<JSONObject>();
		try {
			JSONParser parser = new JSONParser();
			Object obj = parser.parse(output);
			JSONArray array = (JSONArray) obj;

			for (int i = 0; i < array.size(); i++) {
				JSONObject json = (JSONObject) array.get(i);
				list.add(json);

				Long id = (Long) json.get("id");
				String rmaNum = (String) json.get("rmaNum");
				String rmaHdrStsCd = (String) json.get("rmaHdrStsCd");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	private String getJsonStrByURL(String url) {
		Client client = Client.create();
		WebResource response = client.resource("http://localhost:8080/NS2015V07/ns-home/json");
		ClientResponse clientRes = response.accept("application/json").get(ClientResponse.class);
		if (clientRes.getStatus() != 200) {
			throw new RuntimeException("Failed : HTTP error code : " + clientRes.getStatus());
		}
		return clientRes.getEntity(String.class);
	}
}