Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Use SQL Syntax to Access MongoDB

DZone's Guide to

How to Use SQL Syntax to Access MongoDB

Get a preliminary understanding of the components of tangyuan-mongo, a service that allows you to access Mongo in SQL syntax.

· Database Zone
Free Resource

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

tangyuan-mongo is the Mongo service component in the tangyuan framework. The tangyuan-mongo component encapsulates a series of Mongo operations into Tangyuan's services and provides a unified way to access it. It also provides access to Mongo in SQL syntax.

Image title

The source code can be found here, and the official website is here

Use SQL Syntax to Access MongoDB

If we want to query the gender of women aged between 18 and 28 years old using Mongo original syntax, we need to write:

db.user.find({"gender":"Female", "age":{"$gte":18, "$lte":28}})

Now, we can use the following:

select * from user where gender = 'Female' and age >= 18 and age <= 28

tangyuan-mongo provides access to Mongo in SQL syntax.

Supported SQL Syntax

We'll look at INSERTSELECT, UPDATE, and DELETE.

INSERT

Syntax:

INSERT INTO
    tbl_name (col_name,...)
    VALUES (col_value,...)

Example:

INSERT INTO user_info(id, name, age, amount) VALUES(1, 'xsonorg', 18, 1000);

Parsed Mongo syntax:

db.user_info.insert({ "id" : 1 , "name" : "xsonorg" , "age" : 18 , "amount" : 1000})

SELECT

Syntax:

SELECT
    {col_name | expr}, ...
    [FROM tbl_name
    [WHERE where_definition]
    [ORDER BY col_name [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count}]

Example:

SELECT * from user_info where age >= 18 and age <= 28 order by age limit 10

ParsedMongo syntax

db.user_info.find({ "age" : { "$gte" : 18 , "$lte" : 28}}).sort({"age" : 1}).limit(10)

UPDATE

Syntax:

UPDATE tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

Example:

UPDATE user_info SET name = 'xson.org', age = age + 2 where id = 1

Parsed Mongo syntax:

db.user_info.update({ "id" : 1} , { "$set" : { "name" : "xson.org"} , "$inc" : { "age" : 2}}, false, true );

DELETE

Syntax:

DELETE FROM tbl_name
    [WHERE where_definition]

Example:

delete from user_info where user_id = 1

Parsed Mongo syntax:

db.user_info.remove({ "id" : 1})

Mongo Service

The previous section describes the supported SQL syntax. In this section, we'll show you how to define Mongo services based on SQL statements and the use of Mongo services.

First, what is the Mongo service? The Nongo service is a service that provides MongoDB operations in tangyuan and that contains a series of SQL statements and XML tags. When the user accesses the Mongo service, the framework parses the SQL statement into a MONGO statement, then manipulates MongoDB with the resolved Mongo statement and returns the result.

Let's take a look at a complete example:

Complete Example

Add dependent JAR:

<dependency>
    <groupId>org.xson</groupId>
    <artifactId>tangyuan-mongo</artifactId>
    <version>1.2.0</version>
</dependency>

<dependency>
    <groupId>org.mongodb</groupId>
    <artifactId>mongo-java-driver</artifactId>
    <version>3.3.0</version>
</dependency>

Add the mongo component to the tangyuan configuration file tangyuan.xml:

<?xml version="1.0" encoding="UTF-8"?>
<tangyuan-component xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="http://xson.org/schema/tangyuan/component.xsd">

    <!--Add the mango service component -->
    <component resource="component-mongo.xml" type="mongo" />

</tangyuan-component>

Configure the tangyuan-mongo component in the component-mongo.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<mongo-component xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="http://xson.org/schema/tangyuan/mongo/component.xsd">

    <!-- Mongo dataSource -->
    <dataSource id="mongods">
        <property name="url" value="mongodb://127.0.0.1:27017/mdb" />
    </dataSource>

    <!-- Mongo service plugin -->
    <plugin resource="test-mongo.xml"/>

</mongo-component>

Define the Mongo service in the service plug-in test-mongo.xml:

<?xml version="1.0" encoding="UTF-8"?>
<mongoservices xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="http://xson.org/schema/tangyuan/mongo/service.xsd" ns="demo">

    <!-- Mongo query service -->
     <selectOne id="getUser" dsKey="mongods">
        SELECT * from user_info where id = 1
     </selectOne>

</mongoservices>

Unit test:

@Test
public void testMongo() {
    XCO request = new XCO();
    // set
    Object obj = ServiceActuator.execute("demo/getUser", request);
    System.out.println(obj);
}

The current service getUser returns the result is an XCO object, the XML format is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<X>
    <S K="_id" V="59950f7f2932a11a68285d21"/>
    <I K="id" V="1"/>
    <S K="name" V="xson.org"/>
    <I K="age" V="26"/>
    <I K="amount" V="1000"/>
</X>

Through the previous steps, we achieved a complete process — from the configuration of the component to the definition of the service to the call to the service. In the above example, we demonstrated the Mongo query service, in fact, in the tangyuan-mongo, also supports several other mongo services, the following we will be introduced in turn.

Note: All Mongo services need to be defined in the service plug-in file, such as the test-mongo.xml file in the example, which is no longer specified in the following example.

Mongo Query Service; Query a Result Set

<selectSet id="getUserList" dsKey="mongods"><![CDATA[
    SELECT * from user_info where age >= 18 and age <= 28 order by age limit 10
 ]]></selectSet>

The Mongo query result set service is defined by the selectSet tag. Returns a List<XCO> object.

Mongo Query Service, Query Single Record

 <selectOne id="getUser" dsKey="mongods">
    SELECT * from user_info where id = 1
 </selectOne>

The Mongo query single record service is defined by the selectOne tag. Returns a XCO object.

Mongo Query Service, Query a Field

 <selectVar id="getUserName" dsKey="mongods">
    SELECT name from user_info where id = 1
 </selectVar>

The Mongo query field service is defined by the selectVar tag. Returns the specific type of the field.

Mongo Insert Service

<insert id="addUser" dsKey="mongods">
    INSERT INTO user_info(id, name, age, amount) VALUES(1, 'xsonorg', 18, 1000);
</insert>

The Mongo insert service is defined by the insert tag and is used to insert a document. The return value is the _id value of the document, the String type.

Mongo Update Service

<update id="updateUser" dsKey="mongods"><![CDATA[
    UPDATE user_info SET name = 'xson.org', age = age + 2 where id = 1
 ]]></update>

The Mongo update service is defined by the update tag and is used to update the document. The return value is the number of rows affected, Integer type.

Mongo Delete Service

 <delete id="deleteUser" dsKey="mongods">
    delete from user_info where id = 2
 </delete>

The Mongo update service is defined by the delete tag and is used to delete the document. The return value is the number of rows affected, Integer type.

Mongo Combination Service

<mongo-service id="opUser" dsKey="mongods">
    <insert>
        INSERT INTO user_info(id, name, age, amount) VALUES(2, 'tangyuan-mongo', 18, 1000);
    </insert>    
    <update><![CDATA[
        UPDATE user_info SET name = 'xson.org', age = age + 2 where id = 1
     ]]></update>
    <selectSet resultKey="{users}"><![CDATA[
        SELECT * from user_info where age >= 18 and age <= 28 order by age limit 10
     ]]></selectSet>
     <return>
        <property value="{users}"/>
     </return>
 </mongo-service>

The Mongo combination service is defined by the mongo-service tag, which is used to encapsulate multiple Mongo basic services into a new service for user access. The return object can be defined by the return tag.

Conclusion

Through the contents of this article, we have a preliminary understanding of tangyuan-mongo components. If you're interested, tangyuan-mongo use documentation can be found here!

Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

Topics:
tangyuan ,mongo ,sql ,database ,tutorial

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}