Examples with MySqlDialect org.sagacity.sqltoy.dialect.impl.MySqlDialect used on opensource projects

Example 1 with MySqlDialect

use of org.sagacity.sqltoy.dialect.impl.MySqlDialect in project sagacity-sqltoy by chenrenfei.

the class DialectFactory method getDialectSqlWrapper.

/**
 * @todo 根据数据库类型获取处理sql的handler
 * @param dbType
 * @return
 * @throws Exception
 */
private Dialect getDialectSqlWrapper(Integer dbType) throws Exception {
    // 从map中直接获取实例，避免重复创建和判断
    if (dialects.containsKey(dbType)) {
        return dialects.get(dbType);
    }
    // 按照市场排名作为优先顺序
    Dialect dialectSqlWrapper = null;
    switch(dbType) {
        // oracle12c(分页方式有了改变,支持identity主键策略(内部其实还是sequence模式))
        case DBType.ORACLE:
            {
                dialectSqlWrapper = new OracleDialect();
                break;
            }
        // MariaDB 在检测的时候归并到mysql,采用跟mysql一样的语法
        case DBType.MYSQL:
        case DBType.MYSQL57:
            {
                dialectSqlWrapper = new MySqlDialect();
                break;
            }
        // sqlserver2012 以后分页方式更简单
        case DBType.SQLSERVER:
            {
                dialectSqlWrapper = new SqlServerDialect();
                break;
            }
        // postgresql/greenplum
        case DBType.POSTGRESQL:
            {
                dialectSqlWrapper = new PostgreSqlDialect();
                break;
            }
        // oceanbase 数据库支持
        case DBType.OCEANBASE:
            {
                dialectSqlWrapper = new OceanBaseDialect();
                break;
            }
        // db2 10.x版本分页支持offset模式
        case DBType.DB2:
            {
                dialectSqlWrapper = new DB2Dialect();
                break;
            }
        // clickhouse 19.x 版本开始支持
        case DBType.CLICKHOUSE:
            {
                dialectSqlWrapper = new ClickHouseDialect();
                break;
            }
        // Tidb方言支持
        case DBType.TIDB:
            {
                dialectSqlWrapper = new TidbDialect();
                break;
            }
        // 华为guassdb(postgresql 为蓝本的)
        case DBType.GAUSSDB:
            {
                dialectSqlWrapper = new GuassDBDialect();
                break;
            }
        case DBType.IMPALA:
            {
                dialectSqlWrapper = new ImpalaDialect();
                break;
            }
        // dm数据库支持(以oracle为蓝本)
        case DBType.DM:
            {
                dialectSqlWrapper = new DMDialect();
                break;
            }
        // 基本支持(sqlite 本身功能就相对简单)
        case DBType.SQLITE:
            {
                dialectSqlWrapper = new SqliteDialect();
                break;
            }
        // 10g,11g
        case DBType.ORACLE11:
            {
                dialectSqlWrapper = new Oracle11gDialect();
                break;
            }
        // 北大金仓
        case DBType.KINGBASE:
            {
                dialectSqlWrapper = new KingbaseDialect();
                break;
            }
        // 如果匹配不上使用默认dialect
        default:
            dialectSqlWrapper = new DefaultDialect();
    }
    dialects.put(dbType, dialectSqlWrapper);
    return dialectSqlWrapper;
}

Example 2 with MySqlDialect

use of org.sagacity.sqltoy.dialect.impl.MySqlDialect in project sagacity-sqltoy by chenrenfei.

the class MySqlDialectTest method testCTESearch.

@Test
public void testCTESearch() {
    StringBuilder query = new StringBuilder();
    query.append("with sag_dict_post as ");
    query.append("(");
    query.append("	select t0.DICT_TYPE_CODE,t0.DICT_KEY,t0.DICT_NAME,t0.ENABLED ");
    query.append("	from sag_dict_detail t0 where t0.enabled=? ");
    query.append("    and t0.DICT_TYPE_CODE=? ");
    query.append("),sag_tech_level as ");
    query.append("( ");
    query.append("	select t.DICT_TYPE_CODE,t.DICT_KEY,t.DICT_NAME,t.ENABLED  ");
    query.append("	from sag_dict_detail t where t.enabled=? ");
    query.append("    and t.DICT_TYPE_CODE=? ");
    query.append(") ");
    query.append("select t1.STAFF_ID,t1.STAFF_NAME,t2.DICT_NAME POST_NAME,t3.DICT_NAME TECH_NAME ");
    query.append("from sys_staff_info t1 left join sag_dict_post t2 on t1.POST=t2.DICT_KEY left join sag_tech_level t3 ");
    query.append("on t1.TECH_LEVEL=t3.DICT_KEY ");
    query.append("where t1.STAFF_NAME like ?");
    query.append(" order by t1.STAFF_NAME");
    Object[] paramValues = { 1, "POST_TYPE", 1, "SEAT_SKILL_LEVEL", "王%" };
    Connection conn = DBUtilsTest.getConnection(DBUtilsTest.DRIVER_MYSQL, "jdbc:mysql://192.168.56.109:3306/vxiplatform?useUnicode=true&characterEncoding=utf-8", "root", "root");
    MySqlDialect dialect = new MySqlDialect();
    try {
        Long count = dialect.getCountBySql(null, null, query.toString(), paramValues, false, conn, DBType.MYSQL, "mysql");
        System.err.println(count);
    } catch (Exception e) {
        e.printStackTrace();
    }
}