package com.baomidou.mybatisplus.test;
import org.junit.Assert;
import org.junit.Test;
import com.baomidou.mybatisplus.entity.CountOptimize;
import com.baomidou.mybatisplus.toolkit.SqlUtils;
/**
* <p>
* 测试SqlUtils工具类
* </p>
*
* @author Caratacus
* @Date 2016-11-3
*/
public class SqlUtilsTest {
/**
* 测试jsqlparser方式
*/
@Test
public void sqlCountOptimize1() {
CountOptimize countOptimize = SqlUtils
.getCountOptimize(
"select * from user a left join (select uuid from user2) b on b.id = a.aid where a=1 order by (select 1 from dual)",
"jsqlparser", "mysql", true);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertFalse(orderBy);
Assert.assertEquals("SELECT COUNT(1) FROM user a LEFT JOIN (SELECT uuid FROM user2) b ON b.id = a.aid WHERE a = 1",
countsql);
}
/**
* 测试jsqlparser方式
*/
@Test
public void sqlCountOptimize2() {
CountOptimize countOptimize = SqlUtils
.getCountOptimize(
"select distinct * from user a left join (select uuid from user2) b on b.id = a.aid where a=1 order by (select 1 from dual)",
"jsqlparser", "mysql", true);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertFalse(orderBy);
Assert.assertEquals(
"SELECT COUNT(1) FROM ( SELECT DISTINCT * FROM user a LEFT JOIN (SELECT uuid FROM user2) b ON b.id = a.aid WHERE a = 1 ) TOTAL",
countsql);
}
/**
* 测试jsqlparser方式
*/
@Test
public void sqlCountOptimize3() {
CountOptimize countOptimize = SqlUtils
.getCountOptimize(
"select * from user a left join (select uuid from user2) b on b.id = a.aid where a=1 group by a.id order by (select 1 from dual)",
"jsqlparser", "mysql", true);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertFalse(orderBy);
Assert.assertEquals(
"SELECT COUNT(1) FROM ( SELECT * FROM user a LEFT JOIN (SELECT uuid FROM user2) b ON b.id = a.aid WHERE a = 1 GROUP BY a.id ORDER BY (SELECT 1 FROM dual) ) TOTAL",
countsql);
}
/**
* 测试default方式
*/
@Test
public void sqlCountOptimize4() {
CountOptimize countOptimize = SqlUtils
.getCountOptimize(
"select * from user a left join (select uuid from user2) b on b.id = a.aid where a=1 group by a.id order by (select 1 from dual)",
"default", "mysql", false);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertFalse(orderBy);
Assert.assertEquals(
"SELECT COUNT(1) FROM ( select * from user a left join (select uuid from user2) b on b.id = a.aid where a=1 group by a.id order by (select 1 from dual) ) TOTAL",
countsql);
}
/**
* 测试default方式
*/
@Test
public void sqlCountOptimize5() {
CountOptimize countOptimize = SqlUtils.getCountOptimize("select * from test where 1= 1 order by id ", "default", "mysql",
true);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertFalse(orderBy);
Assert.assertEquals("SELECT COUNT(1) from test where 1= 1 ", countsql);
}
/**
* 测试default方式
*/
@Test
public void sqlCountOptimize6() {
CountOptimize countOptimize = SqlUtils.getCountOptimize("select * from test where 1= 1 order by id ", "default", "mysql",
false);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertFalse(orderBy);
Assert.assertEquals("SELECT COUNT(1) FROM ( select * from test where 1= 1 order by id ) TOTAL", countsql);
}
/**
* 测试default方式
*/
@Test
public void sqlCountOptimize7() {
CountOptimize countOptimize = SqlUtils.getCountOptimize("select * from test where 1= 1 ", "default", "mysql", false);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertTrue(orderBy);
Assert.assertEquals("SELECT COUNT(1) FROM ( select * from test where 1= 1 ) TOTAL", countsql);
}
/**
* 测试aliDruid方式
*/
@Test
public void sqlCountOptimize8() {
CountOptimize countOptimize = SqlUtils.getCountOptimize("select * from test where 1= 1 order by id ", "aliDruid",
"mysql", false);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertFalse(orderBy);
Assert.assertEquals("SELECT COUNT(*)\n" + "FROM test\n" + "WHERE 1 = 1", countsql);
}
/**
* 测试aliDruid方式
*/
@Test
public void sqlCountOptimize9() {
CountOptimize countOptimize = SqlUtils.getCountOptimize("select * from test where 1= 1 ", "aliDruid", "mysql", false);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertTrue(orderBy);
Assert.assertEquals("SELECT COUNT(*)\n" + "FROM test\n" + "WHERE 1 = 1", countsql);
}
/**
* 测试aliDruid方式
*/
@Test
public void sqlCountOptimize10() {
CountOptimize countOptimize = SqlUtils
.getCountOptimize(
"select * from user a left join (select uuid from user2) b on b.id = a.aid where a=1 order by (select 1 from dual)",
"aliDruid", "mysql", true);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertFalse(orderBy);
Assert.assertEquals("SELECT COUNT(*)\n" + "FROM user a\n" + "\tLEFT JOIN (SELECT uuid\n" + "\t\tFROM user2\n"
+ "\t\t) b ON b.id = a.aid\n" + "WHERE a = 1", countsql);
}
/**
* 测试aliDruid方式
*/
@Test
public void sqlCountOptimize11() {
CountOptimize countOptimize = SqlUtils
.getCountOptimize(
"select distinct * from user a left join (select uuid from user2) b on b.id = a.aid where a=1 order by (select 1 from dual)",
"aliDruid", "mysql", true);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertFalse(orderBy);
Assert.assertEquals("SELECT COUNT(DISTINCT *)\n" + "FROM user a\n" + "\tLEFT JOIN (SELECT uuid\n" + "\t\tFROM user2\n"
+ "\t\t) b ON b.id = a.aid\n" + "WHERE a = 1", countsql);
}
/**
* 测试aliDruid方式
*/
@Test
public void sqlCountOptimize12() {
CountOptimize countOptimize = SqlUtils
.getCountOptimize(
"select * from user a left join (select uuid from user2) b on b.id = a.aid where a=1 group by a.id order by (select 1 from dual)",
"aliDruid", "mysql", true);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertFalse(orderBy);
Assert.assertEquals("SELECT COUNT(*)\n" + "FROM (SELECT *\n" + "\tFROM user a\n" + "\t\tLEFT JOIN (SELECT uuid\n"
+ "\t\t\tFROM user2\n" + "\t\t\t) b ON b.id = a.aid\n" + "\tWHERE a = 1\n" + "\tGROUP BY a.id\n"
+ "\t) ALIAS_COUNT", countsql);
}
/**
* 测试aliDruid方式
*/
@Test
public void sqlCountOptimize13() {
CountOptimize countOptimize = SqlUtils
.getCountOptimize(
"select * from user a left join (select uuid from user2) b on b.id = a.aid where a=1 group by a.id order by (select 1 from dual)",
"aliDruid", "mysql", false);
String countsql = countOptimize.getCountSQL();
boolean orderBy = countOptimize.isOrderBy();
System.out.println(countsql);
System.out.println(orderBy);
Assert.assertFalse(orderBy);
Assert.assertEquals("SELECT COUNT(*)\n" + "FROM (SELECT *\n" + "\tFROM user a\n" + "\t\tLEFT JOIN (SELECT uuid\n"
+ "\t\t\tFROM user2\n" + "\t\t\t) b ON b.id = a.aid\n" + "\tWHERE a = 1\n" + "\tGROUP BY a.id\n"
+ "\t) ALIAS_COUNT", countsql);
}
}