Home

1. Project Introduction

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.

2. Use SQL syntax to access MongoDB

If we want to query the gender of women, aged between 18 and 28 years old, if you use 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

Is it like the SQL query? Yes, tangyuan-mongo is to provide access to Mongo in SQL syntax.

3. Supported SQL syntax

3.1 SELECT

Syntax

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

Query field

select * from table ...
select a, b, c from table ...
select count(*) from table ...

WHERE expression

Operator description example
= Equal … WHERE age = 18
<> Not equal … WHERE age = 18
> Greater than … WHERE age > 18
< Less than … WHERE age < 18
>= greater than or equal … WHERE age >= 18
<= less than or equal … WHERE age <= 18
LIKE Based on the comparison of regular expressions … WHERE name like ‘^name$’
IN … WHERE type IN (1, 2, 3)
NOT IN … WHERE type NOT IN (1, 2, 3)
AND … WHERE name = ‘xsonorg’ AND age = 18
OR … WHERE age = 20 OR age = 18

Sorting

select * from table order by update_time
select * from table order by update_time DESC
select * from table order by name ASC, age DESC

Paging

select * from table limit 10
select * from table limit 0, 20

3.2 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);

3.3 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

WHERE expression

Reference the WHERE expression for SELECT

3.4 DELETE

Syntax

DELETE FROM tbl_name
    [WHERE where_definition]

Example

delete from user_info where user_id = 1

WHERE expression

Reference the WHERE expression for SELECT

4. Version and reference

Current latest version：1.2.0

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

5. Source code

https://github.com/xsonorg/tangyuan2