Examples with Sql com.github.drinkjava2.jsqlbox.annotation.Sql used on opensource projects

Example 6 with Sql

use of com.github.drinkjava2.jsqlbox.annotation.Sql in project jSqlBox by drinkjava2.

the class EntitySqlMapListHandler method explainNetQuery.

/**
 * Replace .** to all fields, replace .## to all PKey and FKey fields only,
 * for example:
 *
 * <pre>
 * u.**  ==> u.id as u_id, u.userName as u_userName, u.address as u_address...
 * u.##  ==> u.id as u_id
 * </pre>
 */
public String explainNetQuery(SqlBoxContext ctx, String sqlString) {
    // NOSONAR
    SqlBoxException.assureNotEmpty(sqlString, "Sql can not be empty");
    String sql = SqlBoxStrUtils.formatSQL(sqlString);
    TableModel[] configModels = EntityNetUtils.objectConfigsToModels(ctx, netConfigObjects);
    int pos = sql.indexOf(".**");
    if (pos < 0)
        pos = sql.indexOf(".##");
    while (pos >= 0) {
        StringBuilder aliasSB = new StringBuilder();
        for (int i = pos - 1; i >= 0; i--) {
            if (SqlBoxStrUtils.isNormalLetters(sql.charAt(i)))
                aliasSB.insert(0, sql.charAt(i));
            else
                break;
        }
        if (aliasSB.length() == 0)
            throw new SqlBoxException(".** can not put at front");
        String alias = aliasSB.toString();
        // NOSONAR
        sql += " ";
        // alias found, not find the table name
        int posAlias = StrUtils.indexOfIgnoreCase(sql, " as " + alias + " ");
        if (posAlias == -1)
            posAlias = StrUtils.indexOfIgnoreCase(sql, " as " + alias + ",");
        if (posAlias == -1)
            posAlias = StrUtils.indexOfIgnoreCase(sql, " as " + alias + ")");
        if (posAlias == -1)
            posAlias = StrUtils.indexOfIgnoreCase(sql, " " + alias + " ");
        if (posAlias == -1)
            posAlias = StrUtils.indexOfIgnoreCase(sql, " " + alias + ",");
        if (posAlias == -1)
            posAlias = StrUtils.indexOfIgnoreCase(sql, " " + alias + ")");
        if (posAlias == -1)
            throw new SqlBoxException("Alias '" + alias + "' not found");
        StringBuilder tableNameSb = new StringBuilder();
        for (int i = posAlias - 1; i >= 0; i--) {
            char c = sql.charAt(i);
            if (SqlBoxStrUtils.isNormalLetters(c))
                tableNameSb.insert(0, c);
            else if (tableNameSb.length() > 0)
                break;
        }
        if (tableNameSb.length() == 0)
            throw new SqlBoxException("Alias '" + alias + "' not found tablename in SQL");
        String tbStr = tableNameSb.toString();
        sql = replaceStarStarToColumn(sql, alias, tbStr, configModels);
        pos = sql.indexOf(".**");
        if (pos < 0)
            pos = sql.indexOf(".##");
    }
    generatedTableModels = configModels;
    return sql;
}

Example 7 with Sql

use of com.github.drinkjava2.jsqlbox.annotation.Sql in project jSqlBox by drinkjava2.

the class ActiveRecordUtils method doGuess.

/**
 * Execute operation to access database, based on current method @Sql annotated
 * String or Text String and parameters, guess a best fit
 * query/update/delete/execute method to run
 *
 * @param ac
 * @param params
 * @return <T> T
 */
@SuppressWarnings("all")
protected static <T> T doGuess(ActiveRecord ac, Object... params) {
    // NOSONAR
    int callerPos = 0;
    StackTraceElement[] stacks = Thread.currentThread().getStackTrace();
    for (StackTraceElement stack : stacks) {
        callerPos++;
        if ("com.github.drinkjava2.jsqlbox.ActiveRecord".equals(stack.getClassName()) && "guess".equals(stack.getMethodName()))
            break;
    }
    String callerClassName = stacks[callerPos].getClassName();
    String callerMethodName = stacks[callerPos].getMethodName();
    Class<?> callerClass = ClassCacheUtils.checkClassExist(callerClassName);
    if (callerClass == null)
        throw new SqlBoxException("Can not find class '" + callerClassName + "'");
    Method callerMethod = ClassCacheUtils.checkMethodExist(callerClass, callerMethodName);
    if (callerMethod == null)
        throw new SqlBoxException("Can not find method '" + callerMethodName + "' in '" + callerClassName + "'");
    PreparedSQL sp = getPreparedSQLNoParamsFromSrcCode(callerClassName, callerMethodName, callerMethod);
    String sql = sp.getSql().trim();
    Class<?>[] handlerClass = sp.getHandlerClasses();
    char dotype;
    if (StrUtils.startsWithIgnoreCase(sql, "select"))
        dotype = 's';
    else if (StrUtils.startsWithIgnoreCase(sql, "delete"))
        dotype = 'u';
    else if (StrUtils.startsWithIgnoreCase(sql, "update"))
        dotype = 'u';
    else if (StrUtils.startsWithIgnoreCase(sql, "insert"))
        dotype = 'u';
    else
        // execute
        dotype = 'e';
    boolean hasQuestionMark = sql.indexOf('?') > -1;
    boolean useTemplate = sql.indexOf(':') > -1 || sql.indexOf("#{") > -1;
    if (useTemplate && hasQuestionMark)
        throw new SqlBoxException("guess() method can not determine use template or normal style for SQL '" + sql + "'");
    boolean isEntityQuery = sql.indexOf(".**") > -1;
    if (isEntityQuery && 's' != dotype)
        throw new SqlBoxException("'.**' entity query style SQL can only start with 'select'");
    Map<String, Object> map = null;
    if (useTemplate)
        map = buildParamMap(callerClassName, callerMethodName, params);
    Object o = null;
    switch(dotype) {
        case 's':
            {
                ResultSetHandler<T> resultSetHandler = buildResultHandler(handlerClass);
                if (isEntityQuery) {
                    if (useTemplate)
                        return (T) ac.ctx().tQuery(new EntityListHandler(ac.getClass()), sql, map);
                    else
                        return (T) ac.ctx().nQuery(new EntityListHandler(ac.getClass()), sql, params);
                } else {
                    if (useTemplate)
                        return ac.ctx().tQuery(resultSetHandler, sql, map);
                    else
                        return ac.ctx().nQuery(resultSetHandler, sql, params);
                }
            }
        case 'u':
            {
                if (useTemplate)
                    o = ac.ctx().tUpdate(sql, map);
                else
                    o = ac.ctx().nUpdate(sql, params);
                return (T) o;
            }
        case 'e':
            {
                if (handlerClass == null) {
                    if (useTemplate)
                        o = ac.ctx().tExecute(sql, map);
                    else
                        o = ac.ctx().nExecute(sql, params);
                    return (T) o;
                }
                ResultSetHandler<T> resultSetHandler = buildResultHandler(handlerClass);
                if (useTemplate)
                    o = ac.ctx().tExecute(resultSetHandler, sql, map);
                else
                    o = ac.ctx().nExecute(resultSetHandler, sql, params);
                return (T) o;
            }
        default:
            return null;
    }
}

Example 8 with Sql

use of com.github.drinkjava2.jsqlbox.annotation.Sql in project jSqlBox by drinkjava2.

the class BeetlSqlTemplateDemo method doTest.

@Test
public void doTest() {
    HikariDataSource ds = new HikariDataSource();
    // H2 Memory database
    ds.setDriverClassName("org.h2.Driver");
    ds.setJdbcUrl("jdbc:h2:mem:DBName;MODE=MYSQL;DB_CLOSE_DELAY=-1;TRACE_LEVEL_SYSTEM_OUT=0");
    ds.setUsername("sa");
    ds.setPassword("");
    ConnectionSource source = ConnectionSourceHelper.getSingle(ds);
    DBStyle dbstyle = new H2Style();
    SQLLoader loader = new ClasspathLoader("/sql");
    UnderlinedNameConversion nc = new UnderlinedNameConversion();
    SQLManager sqlManager = new SQLManager(dbstyle, loader, source, nc, new Interceptor[] {});
    // Done BeetlSQL engine
    SqlBoxContext.setGlobalTemplateEngine(new BeetlSqlTempalte(sqlManager));
    // Log output
    SqlBoxContext.setGlobalAllowShowSql(true);
    SqlBoxContext ctx = new SqlBoxContext(ds);
    SqlBoxContext.setGlobalSqlBoxContext(ctx);
    String[] ddlArray = ctx.toDropAndCreateDDL(User.class);
    for (String ddl : ddlArray) ctx.quiteExecute(ddl);
    for (int i = 1; i <= 100; i++) {
        User u = new User();
        u.setName("Foo" + i);
        u.setAge(i);
        u.insert();
    }
    Assert.assertEquals(100, ctx.nQueryForLongValue("select count(*) from users"));
    Map<String, Object> params = new HashMap<>();
    params.put("age", 50);
    List<Map<String, Object>> result = ctx.tQueryForMapList("user.select", params);
    Assert.assertEquals(50, result.size());
    ds.close();
}

Example 9 with Sql

use of com.github.drinkjava2.jsqlbox.annotation.Sql in project jSqlBox by drinkjava2.

the class ImprovedQueryRunner method readCache.

/**
 * Explain SQL to cached result object, if have
 */
private Object[] readCache(ResultSetHandler<?> rsh, String sql, Object... params) {
    Object[] result = new Object[2];
    String key = null;
    if (handlers != null)
        for (ResultSetHandler handler : handlers) if (handler instanceof CacheSqlHandler) {
            if (key == null)
                key = createKey(sql, params);
            result[0] = key;
            Object value = ((CacheSqlHandler) handler).readFromCache(key);
            if (value != null) {
                result[1] = value;
                return result;
            }
        }
    for (ResultSetHandler handler : getThreadedHandlers()) if (handler instanceof CacheSqlHandler) {
        if (key == null)
            key = createKey(sql, params);
        result[0] = key;
        Object value = ((CacheSqlHandler) handler).readFromCache(key);
        if (value != null) {
            result[1] = value;
            return result;
        }
    }
    if (rsh != null && rsh instanceof CacheSqlHandler) {
        if (key == null)
            key = createKey(sql, params);
        result[0] = key;
        Object value = ((CacheSqlHandler) rsh).readFromCache(key);
        if (value != null)
            result[1] = value;
    }
    return result;
}

Example 10 with Sql

use of com.github.drinkjava2.jsqlbox.annotation.Sql in project jSqlBox by drinkjava2.

the class BasicSqlTemplate method doRender.

/**
 * Render a template with
 *
 * @param template
 *            A SQL Template String
 * @param paramMap
 *            A Map stored SQL parameters
 * @param startDelimiter
 *            Start Delimiter of SQL Template
 * @param endDelimiter
 *            End Delimiter of SQL Template
 * @return A PreparedSQL instance
 */
protected static PreparedSQL doRender(String template, Map<String, Object> paramMap, String startDelimiter, String endDelimiter, Set<String> directReplaceNamesSet) {
    if (template == null)
        throw new NullPointerException("Template can not be null");
    StringBuilder sql = new StringBuilder();
    StringBuilder keyNameSB = new StringBuilder();
    List<Object> paramList = new ArrayList<Object>();
    char[] chars = ("   " + template + "   ").toCharArray();
    int lg = startDelimiter.length();
    char start1 = startDelimiter.charAt(0);
    char start2 = '\u0000';
    if (lg == 2)
        start2 = startDelimiter.charAt(1);
    char e = endDelimiter.charAt(0);
    int drlg = DIRECT_REPLACE_START_DELIMITER.length();
    char drst1 = DIRECT_REPLACE_START_DELIMITER.charAt(0);
    char drst2 = '\u0000';
    if (drlg == 2)
        drst2 = DIRECT_REPLACE_START_DELIMITER.charAt(1);
    char eDirect = DIRECT_REPLACE_END_DELIMITER.charAt(0);
    // - - # { - - - } - - - $ { - - - } - -
    // 0 0 1 1 2 2 2 3 0 0 0 1 1 2 2 2 3 0 0
    // - - - - - - - - - - - D D D D D - - -
    // 0:normal 1:start-delimiter 2:inside 3: end-delimiter
    int status = 0;
    // direct replace tag
    boolean directRep = false;
    for (int i = 3; i < chars.length - 2; i++) {
        char c = chars[i];
        char c1 = chars[i + 1];
        char cm1 = chars[i - 1];
        char cm2 = chars[i - 2];
        if (status == 0 && ((lg == 1 && c == start1) || (lg == 2 && c == start1 && c1 == start2))) {
            status = 1;
            keyNameSB.setLength(0);
            directRep = false;
        } else if (status == 0 && ((drlg == 1 && c == drst1) || (drlg == 2 && c == drst1 && c1 == drst2))) {
            status = 1;
            keyNameSB.setLength(0);
            directRep = true;
        } else if (status == 1 && ((lg == 1 && cm1 == start1) || (lg == 2 && (cm2 == start1 && cm1 == start2)))) {
            status = 2;
        } else if (status == 1 && ((drlg == 1 && cm1 == drst1) || (drlg == 2 && (cm2 == drst1 && cm1 == drst2)))) {
            status = 2;
        } else if (status == 2 && (((c == e) && !directRep) || ((c == eDirect) && directRep))) {
            status = 3;
            if (keyNameSB.length() == 0)
                throwEX("Empty parameter name '" + startDelimiter + endDelimiter + "' found in template: " + template);
            String key = keyNameSB.toString();
            if (key.indexOf('.') >= 0) {
                // JavaBean
                String beanName = substringBefore(key, ".");
                String propertyName = substringAfter(key, ".");
                if (isEmpty(beanName) || isEmpty(propertyName))
                    throwEX("illegal parameter name '" + key + "' found in template: " + template);
                boolean directReplaceType = isDirectReplaceTypeParameter(template, paramMap, directReplaceNamesSet, directRep, beanName);
                boolean hasValue = paramMap.containsKey(beanName);
                if (!hasValue)
                    throwEX("Not found bean '" + beanName + "' when render template: " + template);
                Object bean = paramMap.get(beanName);
                PropertyDescriptor pd = null;
                try {
                    pd = new PropertyDescriptor(propertyName, bean.getClass());
                } catch (IntrospectionException e1) {
                    throwEX("IntrospectionException happen when get bean property '" + key + "' in template: " + template, e1);
                }
                Method method = pd.getReadMethod();
                Object beanProperty = null;
                try {
                    beanProperty = method.invoke(bean);
                } catch (Exception e1) {
                    throwEX("Exception happen when read bean property '" + key + "' in template: " + template, e1);
                }
                if (directReplaceType) {
                    sql.append(beanProperty);
                } else {
                    sql.append("?");
                    paramList.add(beanProperty);
                }
            } else {
                boolean directReplaceType = isDirectReplaceTypeParameter(template, paramMap, directReplaceNamesSet, directRep, key);
                boolean hasValue = paramMap.containsKey(key);
                if (!hasValue)
                    throwEX("No parameter found for '" + key + "' in template: " + template + ". Current params=" + paramMap);
                if (directReplaceType) {
                    sql.append(paramMap.get(key));
                } else {
                    sql.append("?");
                    paramList.add(paramMap.get(key));
                }
            }
            keyNameSB.setLength(0);
        } else if (status == 3 && cm1 == e) {
            status = 0;
        }
        if (status == 0)
            sql.append(c);
        else if (status == 2)
            keyNameSB.append(c);
    }
    if (status != 0)
        throwEX("Missing end delimiter '" + endDelimiter + "' in template: " + template);
    PreparedSQL sp = new PreparedSQL();
    sql.setLength(sql.length() - 1);
    sp.setSql(sql.toString());
    sp.setParams(paramList.toArray(new Object[paramList.size()]));
    return sp;
}