Examples with SqlBoxContext com.github.drinkjava2.jsqlbox.SqlBoxContext used on opensource projects

Example 6 with SqlBoxContext

use of com.github.drinkjava2.jsqlbox.SqlBoxContext in project jSqlBox by drinkjava2.

the class UsuageAndSpeedTest method dbUtilsWithConnMethod.

@Test
public void dbUtilsWithConnMethod() {
    SqlBoxContext ctx = new SqlBoxContext(dataSource);
    for (int i = 0; i < REPEAT_TIMES; i++) {
        Connection conn = null;
        try {
            conn = ctx.prepareConnection();
            ctx.execute(conn, "insert into users (name,address) values(?,?)", "Sam", "Canada");
            ctx.execute(conn, "update users set name=?, address=?", "Tom", "China");
            Assert.assertEquals(1L, ctx.queryForObject(conn, "select count(*) from users where name=? and address=?", "Tom", "China"));
            ctx.execute(conn, "delete from users where name=? or address=?", "Tom", "China");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                ctx.close(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Example 7 with SqlBoxContext

use of com.github.drinkjava2.jsqlbox.SqlBoxContext 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 8 with SqlBoxContext

use of com.github.drinkjava2.jsqlbox.SqlBoxContext in project jSqlBox by drinkjava2.

the class TestBase method init.

@Before
public void init() {
    dataSource = BeanBox.getBean(DataSourceBox.class);
    // dataSource = new HikariDataSource();
    // dataSource.setJdbcUrl("jdbc:h2:mem:DBName;MODE=MYSQL;DB_CLOSE_DELAY=-1;TRACE_LEVEL_SYSTEM_OUT=0");
    // dataSource.setDriverClassName("org.h2.Driver");
    // dataSource.setUsername("sa");
    // dataSource.setPassword("");
    // dataSource.setMaximumPoolSize(8);
    // dataSource.setConnectionTimeout(2000);
    dialect = Dialect.guessDialect(dataSource);
    // SqlBoxContext.setGlobalAllowShowSql(true);
    ctx = new SqlBoxContext(dataSource);
    SqlBoxContext.setGlobalSqlBoxContext(ctx);
}

Example 9 with SqlBoxContext

use of com.github.drinkjava2.jsqlbox.SqlBoxContext in project jSqlBox by drinkjava2.

the class SqlBoxContextUtils method delete.

/**
 * Delete entityBean in database according primary key value
 */
public static void delete(SqlBoxContext ctx, Object entityBean) {
    SqlBox box = SqlBoxUtils.findAndBindSqlBox(ctx, entityBean);
    checkBeanAndBoxExist(entityBean, box);
    TableModel tableModel = box.getTableModel();
    List<Object> pkeyParameters = new ArrayList<Object>();
    StringBuilder sb = new StringBuilder();
    sb.append("delete from ").append(tableModel.getTableName()).append(" where ");
    Map<String, Method> readMethods = ClassCacheUtils.getClassReadMethods(entityBean.getClass());
    for (String fieldName : readMethods.keySet()) {
        ColumnModel col = findMatchColumnForJavaField(fieldName, box);
        if (!col.getTransientable() && col.getPkey()) {
            Object value = ClassCacheUtils.readValueFromBeanField(entityBean, fieldName);
            sb.append(col.getColumnName()).append("=?, ");
            pkeyParameters.add(value);
        }
    }
    // delete the last "," character
    sb.setLength(sb.length() - 2);
    if (pkeyParameters.isEmpty())
        throw new SqlBoxException("No primary key set for entityBean");
    int rowAffected = box.context.nExecute(sb.toString(), pkeyParameters.toArray(new Object[pkeyParameters.size()]));
    if (ctx.isBatchEnabled())
        return;
    if (rowAffected <= 0)
        throw new SqlBoxException("No row be deleted for entityBean");
    if (rowAffected > 1)
        throw new SqlBoxException("Multiple rows affected when delete entityBean");
}

Example 10 with SqlBoxContext

use of com.github.drinkjava2.jsqlbox.SqlBoxContext in project jSqlBox by drinkjava2.

the class SqlBoxContextUtils method update.

/**
 * Update entityBean according primary key
 */
public static int update(SqlBoxContext ctx, Object entityBean) {
    SqlBox box = SqlBoxUtils.findAndBindSqlBox(ctx, entityBean);
    checkBeanAndBoxExist(entityBean, box);
    TableModel tableModel = box.getTableModel();
    StringBuilder sb = new StringBuilder();
    sb.append("update ").append(tableModel.getTableName()).append(" set ");
    List<Object> normalParams = new ArrayList<Object>();
    List<Object> pkeyParams = new ArrayList<Object>();
    List<ColumnModel> pkeyColumns = new ArrayList<ColumnModel>();
    Map<String, Method> readMethods = ClassCacheUtils.getClassReadMethods(entityBean.getClass());
    for (String fieldName : readMethods.keySet()) {
        ColumnModel col = findMatchColumnForJavaField(fieldName, box);
        if (!col.getTransientable() && col.getUpdatable()) {
            Object value = ClassCacheUtils.readValueFromBeanField(entityBean, fieldName);
            if (!col.getPkey()) {
                normalParams.add(value);
                sb.append(col.getColumnName()).append("=?, ");
            } else {
                pkeyParams.add(value);
                pkeyColumns.add(col);
            }
        }
    }
    if (!normalParams.isEmpty())
        // delete the last ", " characters
        sb.setLength(sb.length() - 2);
    if (pkeyColumns.isEmpty())
        throw new SqlBoxException("No primary column setting found for entityBean");
    sb.append(" where ");
    for (ColumnModel col : pkeyColumns) sb.append(col.getColumnName()).append("=? and ");
    // delete the last " and " characters
    sb.setLength(sb.length() - 5);
    for (Object pkeyParam : pkeyParams) // join PKey values
    normalParams.add(pkeyParam);
    return box.context.nUpdate(sb.toString(), normalParams.toArray(new Object[normalParams.size()]));
}