Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 Daos.migration(dao, "PACKAGE_NAME", true, true, true)检查索引出错
发布于 2745天前 作者 tanqimin 1946 次浏览 复制 上一个帖子 下一个帖子
标签: 无

数据库：SQL SERVER 2014
问题：调用Daos.migration(dao, "PACKAGE_NAME", true, true, true)时，报错：
请问不会自动跳过已存在的索引吗？

17-05-12 09:45:34.481 [restartedMain] ERROR org.springframework.boot.SpringApplication - Application startup failed
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'organizationTypeController': Unsatisfied dependency expressed through field 'organizationTypeService'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'organizationTypeServiceImpl': Unsatisfied dependency expressed through field 'organizationTypeDao'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'organizationTypeDaoImpl': Unsatisfied dependency expressed through field 'dao'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'primaryNutDao' defined in class path resource [com/iotlead/ent/config/datasource/PrimaryDataSourceConfig.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [com.iotlead.ent.util.nutz.SpringNutDao]: Factory method 'primaryNutDao' threw exception; nested exception is org.nutz.dao.DaoException: !Nutz SQL Error: 'Create UNIQUE Index IDX_ORG_TYPE_CODE ON ERP_ORGANIZATION_TYPE(code)'
PreparedStatement: 
'Create UNIQUE Index IDX_ORG_TYPE_CODE ON ERP_ORGANIZATION_TYPE(code)'
CaseMessage=操作失败，因为在 表 'ERP_ORGANIZATION_TYPE' 上已存在名称为 'IDX_ORG_TYPE_CODE' 的索引或统计信息。
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:588)
	at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:366)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1264)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:866)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542)
	at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:737)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:370)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:314)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1162)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1151)
	at com.iotlead.ent.ApiApplication.main(ApiApplication.java:12)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)

请问不会自动跳过已存在的索引吗？

33 回复

把最后一个ture改成false

我需要检查新增索引，如果设置成false就不可以了吧？

原有的表也是nutz建的？

是的，我是这样处理，先建表，再migration

        Daos.createTablesInPackage(nutDao, ENTITY_PACKAGE, false);
        Daos.migration(nutDao, ENTITY_PACKAGE, true, true, true);

nutz版本是多少，ERP_ORGANIZATION_TYPE对应的pojo代码也贴一下

版本：1.r.61.r2

POJO用了继承，代码如下：
EntityBase 

public class EntityBase implements Serializable {
    @Name
    @ColDefine(width = 36, type = ColType.CHAR, notNull = true)
    private String  id;
    @Column
    private Integer version;
    @Column
    private Date    created;
    @Column
    private Date    modified;
    @Column
    private Date    deleted;
    @Column("create_by")
    @ColDefine(width = 36, type = ColType.CHAR)
    private String  createdBy;
    @Column("modified_by")
    @ColDefine(width = 36, type = ColType.CHAR)
    private String  modifiedBy;
    @Column("deleted_by")
    @ColDefine(width = 36, type = ColType.CHAR)
    private String  deletedBy;
    //省略getter、setter
}

OrganizationType：

@Table("ERP_ORGANIZATION_TYPE")
@TableIndexes(
        @Index(name = "IDX_ORG_TYPE_CODE", fields = {"code"}, unique = true)
)
public class OrganizationType extends EntityBase {
    @Column
    @ColDefine(width = 20, type = ColType.VARCHAR, notNull = true)
    private String  code;
    @Column
    @ColDefine(width = 50, type = ColType.VARCHAR, notNull = true)
    private String  name;
    @Column
    @ColDefine(type = ColType.BOOLEAN, notNull = true)
    private Boolean disable;
    @Column
    @ColDefine(width = 1000, type = ColType.TEXT)
    private String  description;
    @Column("is_system")
    private Boolean isSystem;
    //省略getter、setter
}

恩,我测试一下

谢谢，麻烦您了

找到原因了：
是Daos中有一段代码：

                    // show index from mytable;
                    if (checkIndex)
                        _indexs.addAll(expert.getIndexNames(en, conn));

这里expert.getIndexNames(en, conn)中，SQL Server不能够使用“show index from TABLE”获取索引，必须有单独的实现方法。

因为出错了，Exception被catch吃掉了

哦, SQL Server是没实现 https://github.com/nutzam/nutz/issues/1149

SQL Server获取索引的方法

SELECT idx.name
FROM    sysindexes idx
JOIN    sysindexkeys idk ON idx.id = idk.id AND idx.indid = idk.indid 
JOIN    sysobjects o ON idk.id = o.id AND o.xtype = 'U'
WHERE   o.name = '数据表名称'

麻烦下一版本加一个实现，谢谢

好, 提交了 https://github.com/nutzam/nutz/commit/e14eb6fea6ebafaf67ed5d50af950ad95325abbb

大概10分钟后有快照版

非常感激，新手，以后请多指教

String showIndexs = "SELECT idx.name" +
 +                            "FROM    sysindexes idx" +
 +                            "JOIN    sysindexkeys idk ON idx.id = idk.id AND idx.indid = idk.indid " +
 +                            "JOIN    sysobjects o ON idk.id = o.id AND o.xtype = 'U'" +
 +                            "WHERE   o.name = '" + en.getTableName() + "'";

貌似没有留空格。。@wendal

哦，好坑

提交了

谢谢

@wendal 😄😄
删除索引的语句也不一样，ALTER TABLE 数据表名称 DROP INDEX 索引名称 是错误的，正确是这样的：

DROP INDEX 数据表名称.索引名称

好,提交了

还有一些问题，刚才我给你的SQL中是包含主键的。如果drop index，会导致出错。

@wendal 抱歉，是我的问题，麻烦再更新下查找索引的语句，谢谢

SELECT i.name FROM sys.indexes AS i INNER JOIN sys.tables AS o ON i.[object_id] = o.[object_id]
WHERE  o.name = '数据表名称' AND i.is_primary_key = 0

哦，我改改

改好了

@wendal SqlServer 删除索引与删除唯一索引的语法，竟然不相同：

SELECT i.name, i.is_unique FROM sys.indexes AS i INNER JOIN sys.tables AS o ON i.[object_id] = o.[object_id] 
WHERE o.name = '数据表名称' AND i.is_primary_key = 0

如果is_unique = 1时，删除唯一索引的语句是：

ALTER TABLE 数据表 DROP CONSTRAINT 唯一索引名字

如果is_unique = 0时，删除索引的语句是：

DROP INDEX 数据表名称.索引名称

额，怎么才知道是不是唯一性索引？

通过查询索引的语句：

SELECT i.name, i.is_unique FROM sys.indexes AS i INNER JOIN sys.tables AS o ON i.[object_id] = o.[object_id] 
WHERE o.name = '数据表名称' AND i.is_primary_key = 0

这里多加了一个字段，is_unique，需要通过这个字段判断

额, 我想想怎么改

话说,为啥要删索引呢???

我调用Daos.migration(nutDao, ENTITY_PACKAGE, true, true, true);的时候，如果把checkIndex设为true，它就会执行删除索引的语句。

是不是里面是先删除再创建呢？
我担心如果正式环境可能会慢，看来只能用在开发环境上。本来是想能够简单实现像hibernate更新数据表结构的功能。

索引还在自己建比较好

好的，看来Flyway得继续用才行^_^
打扰你一上午，谢谢

添加回复
请先登陆
回到顶部