package com.ai.ipu.example.mongodb;

import com.ai.ipu.basic.log.ILogger;
import com.ai.ipu.basic.log.IpuLoggerFactory;
import com.ai.ipu.nosql.INoSql;
import com.ai.ipu.nosql.mongodb.MongoCacheFactory;
import com.alibaba.fastjson.JSONObject;
import junit.framework.TestCase;

import java.util.HashMap;
import java.util.Map;

/**
 * @author miaozy@asiainfo.com
 * @desc 使用标准SQL操作mongoDB
 * @date 2020/9/27 7:36 下午
 */
public class MongodbExampleWithSql extends TestCase {

  transient protected ILogger log = IpuLoggerFactory.createLogger(MongodbExampleWithSql.class);

  private String connName;
  private String dbName;
  private String tableName;
  private INoSql noSql;

  @Override
  protected void setUp() throws Exception {
    super.setUp();
    connName = "data";
    dbName = "test";
    tableName = "mycol1";
    noSql = MongoCacheFactory.getMongoDao(connName, dbName, tableName);
  }

  @Override
  protected void tearDown() throws Exception {
    //如果开启了事务开关,就要打开这里的提交代码
    //如果不需要支持事务，请注释这里的提交代码
    if (noSql != null)
      noSql.commitTransaction();
  }

  /**
   * 使用标准sql插入一条数据
   *@throws Exception
   */
  public void testExecuteInsert() throws Exception {
    JSONObject param = new JSONObject();
    param.put("name", "test");
    log.debug("before insert, num is :" + noSql.takeRecordNum(param.toJSONString()));
    noSql.executeInsert("insert into mycol1 (name, nickName, remarks, age) values ('test','this is a test','2',20)");
    log.debug("after insert, num is :" + noSql.takeRecordNum(param.toJSONString()));
  }

  /**
   * 使用标准sql删除mongodb数据
   * @throws Exception
   */
  public void testExecuteDelete() throws Exception {
    noSql.executeDelete("delete from mycol1 where name = 'test'");
  }

  /**
   * 使用标准sql修改mongodb数据
   * @throws Exception
   */
  public void testExecuteUpdate() throws Exception {
    noSql.executeUpdate("update mycol1 set remarks = '2' where name = 'test'");
  }


  /**
   * 使用标准SQL条件查询mongodb(分页)
   * @throws Exception
   */
  public void testExecuteSelectWithPage() throws Exception {
    log.debug("查询结果为:" + noSql.executeSelect("select * from mycol1 limit 0,5"));
  }

  /**
   * 使用标准SQL,where条件查询mongodb
   * 简单的汇总计数
   * @throws Exception
   */
  public void testExecuteSelectWithCount() throws Exception {
    log.debug("查询结果为:" + noSql.executeSelect("select count(1) totalNum from mycol1 where name != 'mike' "));
  }

  /**
   * 使用标准SQL查询mongodb
   * 排序符:asc,desc
   * @throws Exception
   */
  public void testExecuteSelectWithSort() throws Exception {
    log.debug("升序查询结果为:" + noSql.executeSelect("select * from mycol1 where name != 'mike' order by age asc"));
    //log.debug("降序查询结果为:" + noSql.executeSelect("select * from mycol1 where name != 'mike' order by age desc"));
  }

  /**
   * 使用标准SQL查询mongodb
   * 比较符: >,>=,=,<=,<,!=
   * @throws Exception
   */
  public void testExecuteSelectWithCompare() throws Exception {
    //log.debug("查询结果为:" + noSql.executeSelect("select * from mycol1 where age > 20"));
    //log.debug("查询结果为:" + noSql.executeSelect("select * from mycol1 where age < 20"));
    //log.debug("查询结果为:" + noSql.executeSelect("select * from mycol1 where age >= 20"));
    //log.debug("查询结果为:" + noSql.executeSelect("select * from mycol1 where age <= 20"));
    //如果collection中的age列插入的数据类型如果是字符型,则等于,不等于条件需分别修改为:where age = '20';where age != '20';
    log.debug("查询结果为:" + noSql.executeSelect("select * from mycol1 where age = 20"));
    //log.debug("查询结果为:" + noSql.executeSelect("select * from mycol1 where age != 20"));
  }

  /**
   * 使用标准SQL查询mongodb
   * 操作符号:in,not in,and,or
   * @desc 当前age列插入的是数值型,如果是字符型,以IN操作符为例: in('10','20');
   * @throws Exception
   */
  public void testExecuteSelectWithScope() throws Exception {
    log.debug("查询结果为:" + noSql.executeSelect("select * from mycol1 where age in (10,20)"));
    //log.debug("查询结果为:" + noSql.executeSelect("select * from mycol1 where age not in (20)"));
    //log.debug("查询结果为:" + noSql.executeSelect("select * from mycol1 where age >= 20 and age < 21"));
    //log.debug("查询结果为:" + noSql.executeSelect("select * from mycol1 where age >= 20 or age < 21"));
  }

  /**
   * 使用标准SQL多条件查询mongodb
   * 条件:group by,having,order by
   * 分类汇总
   * @throws Exception
   */
  public void testExecuteSelectWithManyCondition() throws Exception {
    log.debug("查询结果为:" + noSql.executeSelect("select name,nickName,sum(age) sumAge,count(1) totalNum from mycol1 where name != 'mike' group by name,nickName  having sumAge>=10 order by sumAge desc"));
  }
}