Examples with Sql org.nutz.dao.sql.Sql used on opensource projects

Example 1 with Sql

use of org.nutz.dao.sql.Sql in project nutz by nutzam.

the class ExtDaoInvocationHandler method migration.

/**
     * 为数据表自动增减字段
     *
     * @param dao
     *            Dao实例
     * @param klass
     *            映射Pojo
     * @param add
     *            是否允许添加字段
     * @param del
     *            是否允许删除字段
     * @param checkIndex
     *            是否检查索引
     * @param tableName
     *            动态表名上下文
     */
public static void migration(Dao dao, final Class<?> klass, final boolean add, final boolean del, final boolean checkIndex, final Object tableName) {
    final JdbcExpert expert = dao.getJdbcExpert();
    if (tableName != null && Strings.isNotBlank(tableName.toString())) {
        dao = ext(dao, tableName);
    }
    final Entity<?> en = dao.getEntity(klass);
    if (!dao.exists(klass))
        return;
    final List<Sql> sqls = new ArrayList<Sql>();
    final Set<String> _indexs = new HashSet<String>();
    dao.run(new ConnCallback() {

        public void invoke(Connection conn) throws Exception {
            expert.setupEntityField(conn, en);
            Statement stat = null;
            ResultSet rs = null;
            ResultSetMetaData meta = null;
            try {
                // 获取数据库元信息
                stat = conn.createStatement();
                rs = stat.executeQuery("select * from " + en.getTableName() + " where 1 != 1");
                meta = rs.getMetaData();
                Set<String> columnNames = new HashSet<String>();
                int columnCount = meta.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    columnNames.add(meta.getColumnName(i).toLowerCase());
                }
                for (MappingField mf : en.getMappingFields()) {
                    if (mf.isReadonly())
                        continue;
                    String colName = mf.getColumnName();
                    if (columnNames.contains(colName.toLowerCase())) {
                        columnNames.remove(colName.toLowerCase());
                        continue;
                    }
                    if (add) {
                        log.infof("add column[%s] to table[%s]", mf.getColumnName(), en.getTableName());
                        sqls.add(expert.createAddColumnSql(en, mf));
                    }
                }
                if (del) {
                    for (String colName : columnNames) {
                        log.infof("del column[%s] from table[%s]", colName, en.getTableName());
                        Sql sql = Sqls.create("ALTER table $table DROP column $name");
                        sql.vars().set("table", en.getTableName());
                        sql.vars().set("name", colName);
                        sqls.add(sql);
                    }
                }
                // show index from mytable;
                if (checkIndex)
                    _indexs.addAll(expert.getIndexNames(en, conn));
            } catch (SQLException e) {
                if (log.isDebugEnabled())
                    log.debugf("migration Table '%s' fail!", en.getTableName(), e);
            } finally // Close ResultSet and Statement
            {
                Daos.safeClose(stat, rs);
            }
        }
    });
    // 创建索引
    UpdateIndexSql indexSqls = createIndexs(dao, en, _indexs, tableName);
    if (checkIndex) {
        // 因为已删除的字段的索引是没办法删除的 所以要先处理索引 再处理字段
        Sql[] delIndexSqls = indexSqls.getSqlsDel();
        if (!Lang.isEmptyArray(delIndexSqls)) {
            dao.execute(delIndexSqls);
        }
    }
    for (Sql sql : sqls) {
        dao.execute(sql);
    }
    if (checkIndex) {
        Sql[] addIndexSqls = indexSqls.getSqlsAdd();
        if (!Lang.isEmptyArray(addIndexSqls)) {
            dao.execute(addIndexSqls);
        }
    }
    // 创建关联表
    dao.getJdbcExpert().createRelation(dao, en);
}

Example 2 with Sql

use of org.nutz.dao.sql.Sql in project nutz by nutzam.

the class Sqlserver2005JdbcExpert method createEntity.

public boolean createEntity(Dao dao, Entity<?> en) {
    StringBuilder sb = new StringBuilder("CREATE TABLE " + en.getTableName() + "(");
    // 创建字段
    for (MappingField mf : en.getMappingFields()) {
        if (mf.isReadonly())
            continue;
        sb.append('\n').append(mf.getColumnNameInSql());
        sb.append(' ').append(evalFieldType(mf));
        // 非主键的 @Name，应该加入唯一性约束
        if (mf.isName() && en.getPkType() != PkType.NAME) {
            sb.append(" UNIQUE NOT NULL");
        } else // 普通字段
        {
            if (mf.isUnsigned())
                sb.append(" UNSIGNED");
            if (mf.isNotNull())
                sb.append(" NOT NULL");
            if (mf.isAutoIncreasement())
                sb.append(" IDENTITY");
            if (mf.hasDefaultValue())
                addDefaultValue(sb, mf);
        }
        sb.append(',');
    }
    // 创建主键
    List<MappingField> pks = en.getPks();
    if (!pks.isEmpty()) {
        sb.append('\n');
        sb.append("PRIMARY KEY (");
        for (MappingField pk : pks) {
            sb.append(pk.getColumnNameInSql()).append(',');
        }
        sb.setCharAt(sb.length() - 1, ')');
        sb.append("\n ");
    }
    // 结束表字段设置
    sb.setCharAt(sb.length() - 1, ')');
    // 执行创建语句
    dao.execute(Sqls.create(sb.toString()));
    // 创建索引
    dao.execute(createIndexs(en).toArray(new Sql[0]));
    // 创建关联表
    createRelation(dao, en);
    // 添加注释(表注释与字段注释)
    addComment(dao, en, COMMENT_COLUMN);
    return true;
}

Example 3 with Sql

use of org.nutz.dao.sql.Sql in project nutz by nutzam.

the class SqlTemplate method queryForList.

/**
 * 执行一个SQL查询操作，结果为一组对象。
 *
 * @param sql
 *            包含变量占位符的SQL
 * @param vars
 *            变量map，无参数时，可为null
 * @param params
 *            参数map，无参数时，可为null
 * @param classOfT
 *            对象类型
 *
 * @return 对象列表，无查询结果时返回长度为0的List对象
 */
public <T> List<T> queryForList(String sql, Map<String, Object> vars, Map<String, Object> params, final Class<T> classOfT) {
    Sql sqlObj = createSqlObj(sql, params);
    sqlObj.setCallback(new SqlCallback() {

        public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
            List<T> list = new ArrayList<T>();
            while (rs.next()) {
                T result = Castors.me().castTo(rs.getObject(1), classOfT);
                list.add(result);
            }
            return list;
        }
    });
    execute(sqlObj, vars, params);
    return sqlObj.getList(classOfT);
}

Example 4 with Sql

use of org.nutz.dao.sql.Sql in project nutz by nutzam.

the class OracleJdbcExpert method dropEntity.

@Override
public boolean dropEntity(Dao dao, Entity<?> en) {
    if (super.dropEntity(dao, en)) {
        if (en.getPks().isEmpty())
            return true;
        List<Sql> sqls = new ArrayList<Sql>();
        for (MappingField pk : en.getPks()) {
            if (pk.isAutoIncreasement()) {
                String sql = gSQL(DSEQ, en.getTableName(), pk.getColumnName());
                sqls.add(Sqls.create(sql));
            }
        }
        try {
            dao.execute(sqls.toArray(new Sql[sqls.size()]));
            return true;
        } catch (Exception e) {
        }
    }
    return false;
}

Example 5 with Sql

use of org.nutz.dao.sql.Sql in project nutz by nutzam.

the class OracleJdbcExpert method createEntity.

public boolean createEntity(Dao dao, Entity<?> en) {
    StringBuilder sb = new StringBuilder("CREATE TABLE " + en.getTableName() + "(");
    // 创建字段
    for (MappingField mf : en.getMappingFields()) {
        if (mf.isReadonly())
            continue;
        sb.append('\n').append(mf.getColumnNameInSql());
        sb.append(' ').append(evalFieldType(mf));
        // 非主键的 @Name，应该加入唯一性约束
        if (mf.isName() && en.getPkType() != PkType.NAME) {
            sb.append(" NOT NULL UNIQUE");
        } else // 普通字段
        {
            if (mf.isPk() && en.getPks().size() == 1)
                sb.append(" primary key ");
            if (mf.isNotNull())
                sb.append(" NOT NULL");
            if (mf.hasDefaultValue() && mf.getColumnType() != ColType.BOOLEAN)
                addDefaultValue(sb, mf);
            if (// 有点暴力
            mf.isUnsigned() && mf.getColumnType() != ColType.BOOLEAN)
                sb.append(" Check ( ").append(mf.getColumnNameInSql()).append(" >= 0)");
        }
        sb.append(',');
    }
    // 结束表字段设置
    sb.setCharAt(sb.length() - 1, ')');
    // 指定表空间
    if (en.hasMeta(META_TABLESPACE)) {
        sb.append(String.format(CTS, en.getMeta(META_TABLESPACE)));
    }
    List<Sql> sqls = new ArrayList<Sql>();
    sqls.add(Sqls.create(sb.toString()));
    // 创建复合主键
    List<MappingField> pks = en.getPks();
    if (pks.size() > 1) {
        StringBuilder pkNames = new StringBuilder();
        for (MappingField pk : pks) {
            pkNames.append(pk.getColumnName()).append(',');
        }
        pkNames.setLength(pkNames.length() - 1);
        String pkNames2 = makePksName(en);
        String sql = String.format("alter table %s add constraint primary_key_%s primary key (%s)", en.getTableName(), pkNames2, pkNames);
        sqls.add(Sqls.create(sql));
    }
    // 处理AutoIncreasement
    for (MappingField mf : en.getMappingFields()) {
        if (!mf.isAutoIncreasement())
            continue;
        // 序列
        sqls.add(Sqls.create(gSQL(CSEQ, en.getTableName(), mf.getColumnName())));
        // 触发器
        sqls.add(Sqls.create(gSQL(CTRI, en.getTableName(), mf.getColumnName())));
    }
    // 创建索引
    sqls.addAll(createIndexs(en));
    // TODO 详细处理Clob
    // TODO 详细处理Blob
    // 执行创建语句
    dao.execute(sqls.toArray(new Sql[sqls.size()]));
    // 创建关联表
    createRelation(dao, en);
    // 添加注释(表注释与字段注释)
    addComment(dao, en);
    return true;
}