JPA/Hibernate/Spring MVC/JNDI-插入查询不适用于第二个DB

Question

我使用带有JNDI的JPA/Hibernate/Spring MVC。我连接到了两个oracle数据库，一个数据库的一切都如预期一样工作，但当我试图插入记录时，其他数据库的一切正常，这给了我以下错误，

<b>root cause</b></p><pre>org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: ORA-00900: invalid SQL statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: ORA-00900: invalid SQL statement
org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:321)
org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:105)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
com.sun.proxy.$Proxy155.addCaseDetailsNative(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
com.sun.proxy.$Proxy195.addCaseDetailsNative(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868)
javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

即使记录已成功输入数据库-我对多个数据源使用JNDI，下面是我试图插入的数据库的配置-

@Configuration
@Profile("in-memory")
@EnableJpaRepositories(basePackages = "abc.xxx.jpa", entityManagerFactoryRef = "bpmEntityManagerFactory", transactionManagerRef = "bpmTransactionManager")
@PropertySource("classpath:application.properties")
@EnableTransactionManagement
public class BPMOracleLocalDataSourceConfig extends
        AbstractLocalDataSourceConfig {
    private static final String PROPERTY_NAME_DATABASE_DRIVER = "db.bpm.driver";
    private static final String PROPERTY_NAME_DATABASE_PASSWORD = "db.bpm.password";
    private static final String PROPERTY_NAME_DATABASE_URL = "db.bpm.url";
    private static final String PROPERTY_NAME_DATABASE_USERNAME = "db.bpm.username";
    @Resource
    private Environment env;
    @Bean
    public DataSource bpmDataSource() throws NamingException {
         JndiTemplate jndiTemplate = new JndiTemplate();
            DataSource dataSource
                    = (DataSource) jndiTemplate.lookup("java:comp/env/jdbc/bpmDataSource");
        return dataSource;
            }
    @Bean
    public LocalContainerEntityManagerFactoryBean bpmEntityManagerFactory() throws NamingException {
        return createEntityManagerFactoryBean(bpmDataSource(), getHibernateDialect());
    }
    @Bean
    public JpaTransactionManager bpmTransactionManager(
            EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
    protected String getHibernateDialect() {
        return Oracle10gDialect.class.getName();
    }
    protected LocalContainerEntityManagerFactoryBean createEntityManagerFactoryBean(
            DataSource dataSource, String dialectClassName) {
        Map<String, String> properties = new HashMap<String, String>();
        properties.put(org.hibernate.cfg.Environment.HBM2DDL_AUTO, "update");
        properties.put(org.hibernate.cfg.Environment.DIALECT, dialectClassName);
        properties.put(org.hibernate.cfg.Environment.SHOW_SQL, "true");
        properties.put(org.hibernate.cfg.Environment.FORMAT_SQL, "true");
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan(BestCustIntactnTyp.class.getPackage().getName());
        em.setPersistenceProvider(new HibernatePersistence());
        em.setJpaPropertyMap(properties);
        return em;
    }
}

这是域-

@Entity
@Table(name="CBR_CSE_DTL")
@NamedQuery(name="CbrCseDtl.findAll", query="SELECT c FROM CbrCseDtl c")
public class CbrCseDtl implements Serializable {
    private static final long serialVersionUID = 1L;
@Id
@Column(name="CBR_CSE_DTL_NBR")
private String cbrCseDtlNbr;
@Column(name="ACCT_TYP_CDE")
private String acctTypCde;
@Column(name="BPM_INSTN_ID")
private String bpmInstnId;
@Column(name="CBR_ACCT_ID")
private BigDecimal cbrAcctId;
@Temporal(TemporalType.DATE)
@Column(name="CBR_FLWUP_DT")
private Date cbrFlwupDt;
@Column(name="CBR_LIST_AMT")
private BigDecimal cbrListAmt;
@Column(name="CBR_SHLD_BE_AMT")
private BigDecimal cbrShldBeAmt;
@Column(name="CBR_UNBL_SEND_CDE")
private String cbrUnblSendCde;
@Column(name="CBR_UPD_ACTN_CDE")
private String cbrUpdActnCde;
@Column(name="CHAN_NM")
private String chanNm;
@Column(name="CHK_PROC_CDE")
private String chkProcCde;
@Temporal(TemporalType.DATE)
@Column(name="CLSE_DT")
private Date clseDt;
@Column(name="CNTR_ID")
private String cntrId;
@Temporal(TemporalType.DATE)
@Column(name="CRTE_DT")
private Date crteDt;
@Column(name="CSE_DTL_STAT_ID")
private String cseDtlStatId;
@Column(name="CSE_SRC_NM")
private String cseSrcNm;
@Column(name="CSE_UID")
private String cseUid;
@Temporal(TemporalType.DATE)
@Column(name="CYCL_DT")
private Date cyclDt;
@Column(name="DSL_TYP_NM")
private String dslTypNm;
@Column(name="FST_NM")
private String fstNm;
@Column(name="INTACTN_TYP_CDE")
private String intactnTypCde;
@Column(name="LOAN_ACCT_NBR")
private String loanAcctNbr;
@Column(name="LST_NM")
private String lstNm;
@Column(name="REOPEN_CSE_STAT_DSC")
private String reopenCseStatDsc;
@Column(name="UPD_CNTR_ID")
private String updCntrId;
@Temporal(TemporalType.DATE)
@Column(name="UPD_DT")
private Date updDt;
@Column(name="UPD_UID")
private String updUid;
and then get and set methods

这是查询-

@Query(value= "INSERT INTO BEST.CBR_CSE_DTL"
        + "  (CBR_CSE_DTL_NBR, CHAN_NM , INTACTN_TYP_CDE  , FST_NM , LST_NM, CBR_ACCT_ID , CSE_UID , CNTR_ID , CRTE_DT , CSE_DTL_STAT_ID"
        + ", CLSE_DT, UPD_UID , UPD_CNTR_ID , UPD_DT , BPM_INSTN_ID , ACCT_TYP_CDE  , LOAN_ACCT_NBR , CHK_PROC_CDE , CBR_UNBL_SEND_CDE , CBR_LIST_AMT , "
        + " CBR_SHLD_BE_AMT , CBR_FLWUP_DT , CBR_UPD_ACTN_CDE , DSL_TYP_NM , CYCL_DT , CSE_SRC_NM , REOPEN_CSE_STAT_DSC) "
        + "VALUES"
        + " ('testing' ,  'test' , 'test'  ,  'test' ,  'test' ,  100 ,  'test' ,  'test' , TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss') ,  'test' , "
        + " TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss') , 'test' , 'test' , TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss') , 'test'  , 'test' , 'test' , 'test'  , 'test'  , 100,"
        + " 100  , TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss')  , 'test'  , 'test'  , TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss' ) , 'test' ,'test')", nativeQuery=true)
public void addCaseDetailsNative();

我尝试过.save，但它不会插入记录或出现任何错误。感谢您的帮助。如果需要其他信息，请告诉我。

Answer 1

我不确定，但我认为@Query只接受select语句（至少我从未见过您定义的方式）。

@Query文档似乎支持：Annotation直接在存储库方法上声明finder查询(http://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/Query.html)。我会按照@Tiny所说的去做。

无论如何，由于我不确定，你至少可以尝试以下其中一种：

1. 试着直接对数据库运行这个查询，看看是否有任何语法错误（不过，乍一看，我还没有看到任何语法错误）
2. 请尝试@Query(sql="INSERT INTO BEST.CBR_CSE_DTL...)，而不是声明@Query(value= "INSERT INTO BEST.CBR_CSE_DTL...)