mamacmm commented 10 years ago

环境

1.jdk6,tomcat7,eclipse3.7 2.sql server 2008 r2数据库 3.使用prototype项目

问题

1.把数据源切换到sqlserver(修改相应的配置文件，包括context-profiles.xml、application.properties和quartz-cluster.properties) 2.启动项目，发现报错：找不到QRTZ_TRIGGERS application.properties的jdbc.initialize.database.enable=true context-profiles.xml的jdbc:initialize-database已经修改： jdbc:script location="classpath*:sql/ddl/quartz/tables_sqlServer.sql" encoding="UTF-8"

但是，仍然报错，好像tables_sqlServer.sql没有执行。

把tables_sqlServer.sql放到数据库，手动执行下，发现数据库里面多了一些表，然后启动项目，发现还是报错，这个时候再看数据库，刚刚增加的那些表都不见了！

xautlx commented 10 years ago

请贴出context-profiles.xml文件内容

mamacmm commented 10 years ago

怎么把xml内容贴出来？直接粘贴格式就乱了。。。

xautlx commented 10 years ago

<description>Spring公共配置 </description>

<!-- 使用annotation 自动注册bean, 并保证@Required、@Autowired的属性被注入 -->
<context:component-scan base-package="lab.s2jh"
    scoped-proxy="targetClass">
</context:component-scan>

<!-- JPA Entity Manager 配置 -->
<bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
    </property>
    <property name="persistenceUnitPostProcessors" ref="persistenceUnitPostProcessors" />
    <property name="packagesToScan" value="lab.s2jh" />
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.connection.autocommit">false</prop>
            <prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>
            <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory
            </prop>
            <prop key="net.sf.ehcache.configurationResourceName">ehcache-config.xml</prop>
            <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>

            <prop key="hibernate.cache.use_query_cache">true</prop>
            <prop key="hibernate.cache.use_second_level_cache">true</prop>
            <prop key="hibernate.generate_statistics">true</prop>

            <!-- <prop key="javax.persistence.validation.mode">none</prop> <prop 
                key="hibernate.validator.apply_to_ddl">false</prop> -->

            <prop key="hibernate.connection.SetBigStringTryClob">true</prop>

            <prop key="org.hibernate.envers.do_not_audit_optimistic_locking_field">false</prop>
            <prop key="org.hibernate.envers.global_with_modified_flag">true</prop>
        </props>
    </property>
</bean>

<bean id="persistenceUnitPostProcessors" class="lab.s2jh.core.dao.jpa.ExtPersistenceUnitPostProcessor" />

<!-- Spring Data JPA配置 -->
<jpa:repositories base-package="lab.s2jh"
    transaction-manager-ref="transactionManager"
    entity-manager-factory-ref="entityManagerFactory" />

<!-- JPA 事务配置 -->
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
    <property name="dataSource" ref="dataSource" />
</bean>

<!-- 使用annotation定义事务 -->
<tx:annotation-driven transaction-manager="transactionManager"
    proxy-target-class="true" />

<!-- Spring Cache配置 -->
<cache:annotation-driven />
<bean id="cacheManager" class="org.springframework.cache.ehcache.EhCacheCacheManager">
    <property name="cacheManager" ref="ehcache" />
</bean>
<bean id="ehcache"
    class="org.springframework.cache.ehcache.EhCacheManagerFactoryBean">
    <property name="configLocation" value="classpath:ehcache-config.xml" />
    <property name="shared" value="true" />
</bean>

<task:scheduler id="springScheduler" pool-size="10" />
<!-- 用于增强Spring @Async 注解在异步模式能捕捉到异常日志 -->
<bean id="handlingAsyncTaskExecutor" class="lab.s2jh.core.context.async.HandlingAsyncTaskExecutor" />
<!-- 建议只定义@Async注解使用，尽量不用@Scheduled而改用后面的Quartz方式便于文本定义调整定时策略 -->
<task:annotation-driven executor="handlingAsyncTaskExecutor" scheduler="springScheduler" />

xautlx commented 10 years ago

试了一下，格式还好吧

mamacmm commented 10 years ago

<beans profile="default">
    <!-- 定制扩展PropertyPlaceholder，实现从数据库加载覆盖属性配置 -->
    <bean class="lab.s2jh.cfg.DynamicPropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:/application.properties</value>
            </list>
        </property>
        <property name="ignoreResourceNotFound" value="true" />
        <property name="dataSource" ref="dataSource" />
        <property name="tableName" value="T_SYS_CFG_PROP" />
        <property name="nameColumn" value="prop_key" />
        <property name="valueColumn" value="simple_value" />
    </bean>

    <!-- H2文件模式数据库，实际开发替换成其他MySQL、Oracle等数据库配置即可，注意别忘了在pom.xml中添加对应数据库驱动的依赖 -->
    <bean id="dataSourceSpied" class="org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactoryBean">
        <property name="databaseName" value="I:\\h2\\prototype" />
        <property name="databaseConfigurer">
            <bean class="org.springframework.jdbc.datasource.embedded.H2EmbeddedFileDatabaseConfigurer"
                factory-method="getInstance">
            </bean>
        </property>
    </bean>

    <bean id="extSlf4jSpyLogDelegator" class="net.sf.log4jdbc.ExtSlf4jSpyLogDelegator" />

    <!--  
    <bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource" depends-on="extSlf4jSpyLogDelegator">
        <constructor-arg>
            <ref bean="dataSourceSpied" />
        </constructor-arg>
    </bean>
    -->

    <!--  
    <bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close" depends-on="extSlf4jSpyLogDelegator">
        <property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver" />
        <property name="url" value="jdbc:jtds:sqlserver://localhost:1433/prototype" />
        <property name="username" value="sa" />
        <property name="password" value="***" />
    </bean>
    -->

    <!--  -->
    <bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close" depends-on="extSlf4jSpyLogDelegator">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
        <property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=prototype" />
        <property name="username" value="sa" />
        <property name="password" value="***" />
    </bean>

    <jdbc:initialize-database data-source="dataSource" ignore-failures="ALL" enabled="${jdbc.initialize.database.enable}">
        <!-- 
        <jdbc:script location="classpath*:sql/ddl/quartz/tables_h2.sql" encoding="UTF-8" />
        -->
        <jdbc:script location="classpath*:sql/ddl/quartz/tables_sqlServer.sql" encoding="UTF-8" />
        <jdbc:script location="classpath:sql/data/t_sys_data_dict.sql" encoding="UTF-8" />          
        <jdbc:script location="classpath:sql/data/t_sys_menu.sql" encoding="UTF-8" />
        <jdbc:script location="classpath:sql/data/t_auth_user.sql" encoding="UTF-8" />
        <jdbc:script location="classpath:sql/data/t_auth_privilege.sql" encoding="UTF-8" />
        <jdbc:script location="classpath:sql/data/t_auth_role.sql" encoding="UTF-8" />
        <jdbc:script location="classpath:sql/data/t_auth_user_r2_role.sql" encoding="UTF-8" />
    </jdbc:initialize-database>
</beans>

<!-- 用于Unit Test配置 -->
<beans profile="test">
    <!-- 缺省PropertyPlaceholder定义 -->
    <bean
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:/application.properties</value>
            </list>
        </property>
        <property name="ignoreResourceNotFound" value="true" />
    </bean>

    <!-- 嵌入式内存中数据库 -->
    <jdbc:embedded-database id="dataSourceSpied" type="H2" />

    <bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource">
        <constructor-arg>
            <ref bean="dataSourceSpied" />
        </constructor-arg>
    </bean>
</beans>

<!-- Oracle配置演示 -->
<beans profile="oracle">
    <!-- 缺省PropertyPlaceholder定义 -->
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:/application.properties</value>
            </list>
        </property>
        <property name="ignoreResourceNotFound" value="true" />
    </bean>

    <bean id="extSlf4jSpyLogDelegator" class="net.sf.log4jdbc.ExtSlf4jSpyLogDelegator" />
    <!-- Oracle 定义DBCP连接池 -->
    <bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close" depends-on="extSlf4jSpyLogDelegator">
        <property name="driverClassName" value="net.sf.log4jdbc.DriverSpy" />
        <property name="url" value="jdbc:log4jdbc:oracle:thin:@localhost:1521:XE" />
        <property name="username" value="showcase" />
        <property name="password" value="showcase" />
    </bean>
</beans>

xautlx commented 10 years ago

Quartz的sql脚本有先删除逻辑，prototype做演示没有考虑到这些细节。可以这样处理：去掉，然后手工到数据库执行脚本，应该就不会出现表消失的问题了。

USE [enter_db_name_here] GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1) ALTER TABLE [dbo].[QRTZ_TRIGGERS] DROP CONSTRAINT FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1) ALTER TABLE [dbo].[QRTZ_CRON_TRIGGERS] DROP CONSTRAINT FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1) ALTER TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS] DROP CONSTRAINT FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1) ALTER TABLE [dbo].[QRTZ_SIMPROP_TRIGGERS] DROP CONSTRAINT FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_CALENDARS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1) DROP TABLE [dbo].[QRTZ_CALENDARS] GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_CRON_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1) DROP TABLE [dbo].[QRTZ_CRON_TRIGGERS] GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_BLOB_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1) DROP TABLE [dbo].[QRTZ_BLOB_TRIGGERS] GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_FIRED_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1) DROP TABLE [dbo].[QRTZ_FIRED_TRIGGERS] GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_PAUSED_TRIGGER_GRPS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1) DROP TABLE [dbo].[QRTZ_PAUSED_TRIGGER_GRPS] GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_SCHEDULER_STATE]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1) DROP TABLE [dbo].[QRTZ_SCHEDULER_STATE] GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_LOCKS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1) DROP TABLE [dbo].[QRTZ_LOCKS] GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_JOB_DETAILS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1) DROP TABLE [dbo].[QRTZ_JOB_DETAILS] GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_SIMPLE_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1) DROP TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS] GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_SIMPROP_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1) DROP TABLE [dbo].[QRTZ_SIMPROP_TRIGGERS] GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1) DROP TABLE [dbo].[QRTZ_TRIGGERS] GO

mamacmm commented 10 years ago

嗯，我就是这样处理的，但很奇怪，删除了，为啥不再创建呢？

xautlx / s2jh

更改sqlserver数据源后初始化数据失败 #34

环境

问题