# mybatis-sql-to-mongodb

**Repository Path**: purefrends/mybatis-sql-to-mongo

## Basic Information

- **Project Name**: mybatis-sql-to-mongodb
- **Description**: 用mybatis 的sql语句操作mongodb的简易方案
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 2
- **Forks**: 0
- **Created**: 2022-09-08
- **Last Updated**: 2022-10-25

## Categories & Tags

**Categories**: Uncategorized

**Tags**: MongoDB, SQL, MyBatis

## README

# mybatis-sql-to-mongodb(用sql查询mongodb)

#### 介绍
结合mongo sql语法转换器vincentrussell
sql-to-mongo-db-query-converter，通过mybatis拦截器实现sql语句操作mongo的方案

## 结合mybatis-plus支持的sql测试如下


### 单个查询

    @Test
    void selectOne(){
        long time = System.currentTimeMillis();
        log.info("开始");
        MyTestCol result = myTestColMapper.selectOne(Wrappers.lambdaQuery(MyTestCol.class).eq(MyTestCol::getAccountId,4)
                .eq(MyTestCol::getCno,"sss"));
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

### 列表查询

    @Test
    void selectList(){
        long time = System.currentTimeMillis();
        log.info("开始");
        PageHelper.startPage(1, 2);
        List<MyTestCol> result = myTestColMapper.selectList(Wrappers.lambdaQuery(MyTestCol.class).eq(MyTestCol::getAccountId,2));
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time,JSONObject.toJSONString(result));
    }

### 查询总数

    @Test
    void selectCount(){
        long time = System.currentTimeMillis();
        log.info("开始");
        Integer result = myTestColMapper.selectCount(Wrappers.lambdaQuery(MyTestCol.class).eq(MyTestCol::getAccountId,1));
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

### 查询多个总数

     select IFNULL(accountId,0) as count1,count(distinct cno) as count2
            from my_test_col t2
            where accountId = 1
            
    @Test
    void selectMulCount(){
        long time = System.currentTimeMillis();
        log.info("开始");
        JSONObject result = myTestColMapper.selectMulCount();
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }
    
#### distinct查询总数

    @Test
    void selectCountDistinct(){
       //暂不支持
    }

#### 查询平均值
    select avg(accountId)
        from my_test_col
        
    @Test
    void selectAvg(){
        long time = System.currentTimeMillis();
        log.info("开始");
        Double result = myTestColMapper.selectAvg();
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### 查询最小值
    select min(accountId)
            from my_test_col
            
    @Test
    void selectMinAccountId(){
        long time = System.currentTimeMillis();
        log.info("开始");
        Long result = myTestColMapper.selectMinAccountId();
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### 查询最大值
     select max(accountId)
            from my_test_col
            
    @Test
    void selectMaxAccountId(){
        long time = System.currentTimeMillis();
        log.info("开始");
        Long result = myTestColMapper.selectMaxAccountId();
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### 查询总和
    select sum(accountId)
        from my_test_col
        
    @Test
    void selectSumAccountId(){
        long time = System.currentTimeMillis();
        log.info("开始");
        Long result = myTestColMapper.selectSumAccountId();
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### 分组统计
      select cno,count(accountId)
             from my_test_col
             where accountId > 1
             GROUP BY cno
             
    @Test
    void selectGroupCountList(){
        long time = System.currentTimeMillis();
        log.info("开始");
        List<JSONObject> result= myTestColMapper.groupCountList();
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### concat函数
    @Test
    void selectConcat(){
        long time = System.currentTimeMillis();
        log.info("开始");
        //支持
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time,1);
    }

#### 单个插入
    @Test
    void insertOne(){
        long time = System.currentTimeMillis();
        MyTestCol myTestCol = new MyTestCol().setAccountId(1123L).setCno("test1").setId("sdsd").setRequestUniqueId("hhh");
        log.info("开始");
        Integer result = myTestColMapper.insert(myTestCol);
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### 批量插入
    @Test
    void insertList(){
        long time = System.currentTimeMillis();
        MyTestCol myTestCol = new MyTestCol().setAccountId(1123L).setCno("test776").setId("111").setRequestUniqueId("hhh");
        MyTestCol myTestCol1 = new MyTestCol().setAccountId(1123L).setCno("test777").setId("222").setRequestUniqueId("hhh");
        log.info("开始");
        List<MyTestCol> myTestColList = new ArrayList<>();
        myTestColList.add(myTestCol);
        myTestColList.add(myTestCol1);
        Integer result = myTestColMapper.insertList(myTestColList);
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### 更新
    @Test
    void update(){
        long time = System.currentTimeMillis();
        MyTestCol myTestCol = new MyTestCol().setAccountId(1123L).setCno("test1").setId("sdsd").setRequestUniqueId("hhh");
        log.info("开始");
        Integer result = myTestColMapper.update(myTestCol,Wrappers.lambdaQuery(MyTestCol.class).eq(MyTestCol::getAccountId,1123));
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### 条件更新
    @Test
    void updateById(){
        long time = System.currentTimeMillis();
        MyTestCol myTestCol = new MyTestCol().setAccountId(1123L).setCno("test1").setId("sdsd").setRequestUniqueId("hhh");
        log.info("开始");
        Integer result = myTestColMapper.updateById(myTestCol);
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### 条件删除
    @Test
    void deleteById(){
        long time = System.currentTimeMillis();
        MyTestCol myTestCol = new MyTestCol().setAccountId(1123L).setCno("test1").setId("sdsd").setRequestUniqueId("hhh");
        log.info("开始");
        Integer result = myTestColMapper.deleteById("sdsd");
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### group having
    select cno,count(accountId)
        from my_test_col
        where accountId > 2
        GROUP BY cnob having count(accountId)>2
        
    @Test
    void groupHaving(){
        long time = System.currentTimeMillis();
        log.info("开始");
        List<JSONObject> result = myTestColMapper.groupHaving();
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### case when
      select concat('s_',cno) as cno,
        accountId,
        CASE
          WHEN accountId = 3 THEN 1
          WHEN accountId > 1 THEN 2
          ELSE 6
          end as cId,
        case when accountId between 1 and 4 THEN accountId
        else cno
        end as aId
        from my_test_col
        where accountId > 1
        
    @Test
    void caseWhen(){
        long time = System.currentTimeMillis();
        log.info("开始");
        List<JSONObject> result = myTestColMapper.caseWhen();
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }

#### where 里 cancat
    select cno,count(accountId)
            from my_test_col
            where cno like concat('%','s','%')
            
    @Test
    void whereConcatLike(){
        long time = System.currentTimeMillis();
        log.info("开始");
        List<JSONObject> result = myTestColMapper.whereConcat();
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }
    
#### filter
    @Test
    void filter(){
       //暂不支持
    }

#### childSelect
    @Test
    void childSelect(){
       //不支持 对mongo不友好
    }

#### childFrom
    select * from
        (select cno,accountId from my_test_col  where accountId > 2) t
    
    @Test
    void childFrom(){
        long time = System.currentTimeMillis();
        log.info("开始");
        List<JSONObject> result = myTestColMapper.childFrom();
        log.info("结束,耗时:{},结果:{}",System.currentTimeMillis()-time, JSONObject.toJSONString(result));
    }