Examples with SqlBuilder cn.hutool.db.sql.SqlBuilder used on opensource projects

Example 1 with SqlBuilder

use of cn.hutool.db.sql.SqlBuilder in project hutool by looly.

the class AnsiSqlDialect method psForFind.

@Override
public PreparedStatement psForFind(Connection conn, Query query) throws SQLException {
    Assert.notNull(query, "query must not be null !");
    final SqlBuilder find = SqlBuilder.create(wrapper).query(query);
    return DbUtil.prepareStatement(conn, find.build(), find.getParamValues());
}

Example 2 with SqlBuilder

use of cn.hutool.db.sql.SqlBuilder in project hutool by looly.

the class AnsiSqlDialect method psForUpdate.

@Override
public PreparedStatement psForUpdate(Connection conn, Entity entity, Query query) throws SQLException {
    Assert.notNull(query, "query must be not null !");
    final Condition[] where = query.getWhere();
    if (ArrayUtil.isEmpty(where)) {
        // 对于无条件的删除语句直接抛出异常禁止，防止误删除
        throw new SQLException("No 'WHERE' condition, we can't prepare statement for update everything.");
    }
    final SqlBuilder update = SqlBuilder.create(wrapper).update(entity).where(where);
    return StatementUtil.prepareStatement(conn, update);
}

Example 3 with SqlBuilder

use of cn.hutool.db.sql.SqlBuilder in project hutool by looly.

the class MysqlDialect method psForUpsert.

/**
 * 构建用于upsert的{@link PreparedStatement}<br>
 * MySQL通过主键方式实现Upsert，故keys无效，生成SQL语法为：
 * <pre>
 *     INSERT INTO demo(a,b,c) values(?, ?, ?) ON DUPLICATE KEY UPDATE a=values(a), b=values(b), c=values(c);
 * </pre>
 *
 * @param conn   数据库连接对象
 * @param entity 数据实体类（包含表名）
 * @param keys   此参数无效
 * @return PreparedStatement
 * @throws SQLException SQL执行异常
 * @since 5.7.20
 */
@Override
public PreparedStatement psForUpsert(Connection conn, Entity entity, String... keys) throws SQLException {
    SqlBuilder.validateEntity(entity);
    final SqlBuilder builder = SqlBuilder.create(wrapper);
    final StringBuilder fieldsPart = new StringBuilder();
    final StringBuilder placeHolder = new StringBuilder();
    final StringBuilder updateHolder = new StringBuilder();
    // 构建字段部分和参数占位符部分
    entity.forEach((field, value) -> {
        if (StrUtil.isNotBlank(field)) {
            if (fieldsPart.length() > 0) {
                // 非第一个参数，追加逗号
                fieldsPart.append(", ");
                placeHolder.append(", ");
                updateHolder.append(", ");
            }
            field = (null != wrapper) ? wrapper.wrap(field) : field;
            fieldsPart.append(field);
            updateHolder.append(field).append("=values(").append(field).append(")");
            placeHolder.append("?");
            builder.addParams(value);
        }
    });
    String tableName = entity.getTableName();
    if (null != this.wrapper) {
        tableName = this.wrapper.wrap(tableName);
    }
    builder.append("INSERT INTO ").append(tableName).append(" (").append(fieldsPart).append(") VALUES (").append(placeHolder).append(") ON DUPLICATE KEY UPDATE ").append(updateHolder);
    return StatementUtil.prepareStatement(conn, builder);
}

Example 4 with SqlBuilder

use of cn.hutool.db.sql.SqlBuilder in project hutool by looly.

the class OracleTest method oraclePageSqlTest.

@Test
public void oraclePageSqlTest() {
    Page page = new Page(0, 10);
    Entity where = Entity.create("PMCPERFORMANCEINFO").set("yearPI", "2017");
    final Query query = new Query(SqlUtil.buildConditions(where), where.getTableName());
    query.setPage(page);
    SqlBuilder find = SqlBuilder.create(null).query(query).orderBy(page.getOrders());
    final int[] startEnd = page.getStartEnd();
    SqlBuilder builder = // 
    SqlBuilder.create(null).append("SELECT * FROM ( SELECT row_.*, rownum rownum_ from ( ").append(// 
    find).append(" ) row_ where rownum <= ").append(// 
    startEnd[1]).append(// 
    ") table_alias").append(" where table_alias.rownum_ >= ").append(// 
    startEnd[0]);
    String ok = // 
    "SELECT * FROM " + // 
    "( SELECT row_.*, rownum rownum_ from ( SELECT * FROM PMCPERFORMANCEINFO WHERE yearPI = ? ) row_ " + // 
    "where rownum <= 10) table_alias where table_alias.rownum_ >= 0";
    Assert.assertEquals(ok, builder.toString());
}

Example 5 with SqlBuilder

use of cn.hutool.db.sql.SqlBuilder in project hutool by looly.

the class AnsiSqlDialect method psForDelete.

@Override
public PreparedStatement psForDelete(Connection conn, Query query) throws SQLException {
    Assert.notNull(query, "query must be not null !");
    final Condition[] where = query.getWhere();
    if (ArrayUtil.isEmpty(where)) {
        // 对于无条件的删除语句直接抛出异常禁止，防止误删除
        throw new SQLException("No 'WHERE' condition, we can't prepared statement for delete everything.");
    }
    final SqlBuilder delete = SqlBuilder.create(wrapper).delete(query.getFirstTableName()).where(where);
    return StatementUtil.prepareStatement(conn, delete);
}