注解-@Query 事务问题，Executing an update/delete query

问题描述

@Query 事务问题，Executing an update/delete query

新搭建了一个 Spring MVC+Spring Data JPA+Hibernate的一个工程
在DAO的方法之上加了注解@Query、@Modifying，但是在运行过程中一直抛出
javax.persistence.TransactionRequiredException: Executing an update/delete query 的异常，实在无力了，希望有大大来帮助
2015-04-29 23:44:12,534[INFO ]com.mchange.v2.log.MLog:MLog clients using slf4j logging.
2015-04-29 23:44:12,747[INFO ]com.mchange.v2.c3p0.C3P0Registry:Initializing c3p0-0.9.5 [built 02-January-2015 13:25:04 -0500; debug? true; trace: 10]
2015-04-29 23:44:13,142[INFO ]org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean:Building JPA container EntityManagerFactory for persistence unit 'elev'
2015-04-29 23:44:14,041[INFO ]com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource:Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hgeby9991mfjmq1wjzcl2|66c88fce, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceUseNamedDriverClass -> false, identityToken -> 1hgeby9991mfjmq1wjzcl2|66c88fce, idleConnectionTestPeriod -> 0, initialPoolSize -> 20, jdbcUrl -> jdbc:mysql://localhost:3306/elev, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 45, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
2015-04-29 23:44:15,422[DEBUG]org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler:Creating new EntityManager for shared EntityManager invocation
2015-04-29 23:44:15,496[DEBUG]org.springframework.orm.jpa.EntityManagerFactoryUtils:Closing JPA EntityManager
2015-04-29 23:44:15,521[DEBUG]org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler:Creating new EntityManager for shared EntityManager invocation
2015-04-29 23:44:15,521[DEBUG]org.springframework.orm.jpa.EntityManagerFactoryUtils:Closing JPA EntityManager
2015-04-29 23:44:15,763[DEBUG]org.springframework.transaction.annotation.AnnotationTransactionAttributeSource:Adding transactional method 'BusinessUserServiceImpl.editUser' with attribute: PROPAGATION_REQUIRED,ISOLATION_READ_COMMITTED; 'transactionManager'
2015-04-29 23:44:15,982[DEBUG]org.springframework.transaction.annotation.AnnotationTransactionAttributeSource:Adding transactional method 'BusinessUserServiceImpl.editUser' with attribute: PROPAGATION_REQUIRED,ISOLATION_READ_COMMITTED; 'transactionManager'
从日志上看，好像事务已经加上了。。。
以下是工程部分代码：
applicationContext-main.xml

 <?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">
    <context:annotation-config/>
    <context:component-scan base-package="com.feinno">
        <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
    </context:component-scan>
    <import resource="applicationContext-profile.xml"/>
    <import resource="applicationContext-jpa.xml"/>
    <import resource="applicationContext-mvc.xml"/>

</beans>

applicationContext-mvc.xml

 <?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">
    <context:component-scan base-package="com.feinno">
        <context:include-filter type="annotation"
            expression="org.springframework.stereotype.Controller" />
    </context:component-scan>
    <mvc:annotation-driven>
        <mvc:message-converters register-defaults="true">
            <bean
                class="org.springframework.http.converter.json.MappingJackson2HttpMessageConverter">
                <property name="prettyPrint" value="true" />
                <property name="supportedMediaTypes">
                    <list>
                        <value>application/json</value>
                        <value>text/html</value>
                    </list>
                </property>
            </bean>
        </mvc:message-converters>
    </mvc:annotation-driven>
</beans>

applicationContext-profile.xml

 <?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">
    <beans profile="dev">
        <context:property-placeholder
            ignore-resource-not-found="true" location="classpath:/config/dev.properties" />
        <bean id="configProperties"
            class="org.springframework.beans.factory.config.PropertiesFactoryBean">
            <property name="locations">
                <list>
                    <value>classpath:/config/dev.properties</value>
                </list>
            </property>
        </bean>
    </beans>
    <beans profile="test">
        <context:property-placeholder
            ignore-resource-not-found="true" location="classpath:/config/test.properties" />
        <bean id="configProperties"
            class="org.springframework.beans.factory.config.PropertiesFactoryBean">
            <property name="locations">
                <list>
                    <value>classpath:/config/test.properties</value>
                </list>
            </property>
        </bean>
    </beans>
    <beans profile="pro">
        <context:property-placeholder
            ignore-resource-not-found="true" location="classpath:/config/prod.properties" />
        <bean id="configProperties"
            class="org.springframework.beans.factory.config.PropertiesFactoryBean">
            <property name="locations">
                <list>
                    <value>classpath:/config/prod.properties</value>
                </list>
            </property>
        </bean>
    </beans>
    <beans>
        <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
            destroy-method="close">
            <property name="driverClass" value="${jdbc.driver}" />
            <property name="jdbcUrl" value="${jdbc.url}" />
            <property name="user" value="${jdbc.user}" />
            <property name="password" value="${jdbc.password}" />
            <property name="minPoolSize" value="10" />
            <property name="maxPoolSize" value="45" />
            <property name="initialPoolSize" value="20" />
        </bean>
    </beans>
</beans>

applicationContext-jpa.xml

 <?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jpa="http://www.springframework.org/schema/data/jpa" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
    <jpa:repositories base-package="com.feinno.elev.interfaces" transaction-manager-ref="transactionManager" entity-manager-factory-ref="entityManagerFactory" />
    <bean id="entityManagerFactory"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="persistenceUnitName" value="elev" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="generateDdl" value="false" />
                <property name="showSql" value="false" />
                <property name="databasePlatform" value="org.hibernate.dialect.MySQL5Dialect" />
                <property name="database" value="MYSQL" />

            </bean>
        </property>
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.format_sql">true</prop>
            </props>
        </property>
        <property name="packagesToScan">
            <list>
                <value>com.feinno.elev.interfaces.model</value>
            </list>
        </property>
    </bean>
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="dataSource" ref="dataSource" />
        <property name="entityManagerFactory" ref="entityManagerFactory" />
        <property name="jpaDialect">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect"></bean>
        </property>
    </bean>
    <tx:annotation-driven proxy-target-class="true" transaction-manager="transactionManager" />
</beans>

Dao

 package com.feinno.elev.interfaces.business.dao;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.feinno.elev.interfaces.model.BusinessUser;

@Repository
public interface BusinessUserDao extends JpaRepository<BusinessUser, String> {
    @Modifying
    @Query("update BusinessUser set password='123456' where userName=?1")
    public void edit(String userName);

    @Modifying
    @Query(value="delete from sys_accesspro where accessuser = ?1",nativeQuery=true)
    public void delete(String userName);
}

Service

 package com.feinno.elev.interfaces.business.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.feinno.elev.interfaces.business.dao.BusinessUserDao;
import com.feinno.elev.interfaces.business.service.BusinessUserService;
import com.feinno.elev.interfaces.model.BusinessUser;
import com.feinno.elev.util.CommonUtil;
@Service
public class BusinessUserServiceImpl implements BusinessUserService{

    @Autowired
    BusinessUserDao businessUserDao;

    @Override
    public BusinessUser validUser(String userName,String password,String serviceId,String key){
        BusinessUser businessUser = businessUserDao.findOne(userName);
        if(CommonUtil.isNotEmpty(businessUser)){
            if(!businessUser.getPassword().equals(password)||!businessUser.getServiceId().equals(serviceId)||!businessUser.getKey().equals(key)){
                return null;
            }
        }
        return businessUser;
    }
    @Transactional(isolation=Isolation.READ_COMMITTED,readOnly=false,propagation=Propagation.REQUIRED,value="transactionManager")
    @Override
    public void editUser(String userName) {
        BusinessUser bu = new BusinessUser();
        bu.setUserName(userName);
        businessUserDao.edit(userName);
    }

    @Override
    public void deleteUser(String userName) {

    }
}

web.xml

 <?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" metadata-complete="false" version="3.0">
  <listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
  </listener>
  <listener>
    <listener-class>org.springframework.web.util.Log4jConfigListener</listener-class>
  </listener>
  <context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>
            classpath:spring/applicationContext-main.xml
        </param-value>
  </context-param>
  <context-param>
    <param-name>spring.profiles.active</param-name>
    <param-value>dev</param-value>
  </context-param>
  <filter>
    <filter-name>ParseRequestFilter</filter-name>
    <filter-class>com.feinno.elev.interfaces.filter.ParseRequestFilter</filter-class>
  </filter>
  <filter-mapping>
    <filter-name>ParseRequestFilter</filter-name>
    <url-pattern>/dispatcher</url-pattern>
  </filter-mapping>
  <filter>
    <filter-name>CharacterEncodingFilter</filter-name>
    <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
    <init-param>
      <param-name>encoding</param-name>
      <param-value>UTF-8</param-value>
    </init-param>
    <init-param>
      <param-name>forceEncoding</param-name>
      <param-value>true</param-value>
    </init-param>
  </filter>
  <filter-mapping>
    <filter-name>CharacterEncodingFilter</filter-name>
    <url-pattern>/*</url-pattern>
  </filter-mapping>
  <servlet>
    <servlet-name>DispatcherServlet</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <init-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>classpath:spring/applicationContext-mvc.xml</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>DispatcherServlet</servlet-name>
    <url-pattern>/*</url-pattern>
  </servlet-mapping>
</web-app>

解决方案

沉了！！！！！能顶上去不？？？？

解决方案二：

Junit能测试通过吗？

解决方案三：

怎么解决的,我遇到了同样的问题.我是俩个数据库 A完全正常.B系统的save delete 都正常 update就报错 已经加上@Transactional

时间： 2024-09-21 20:26:22

注解-@Query 事务问题，Executing an update/delete query的相关文章

sping hibernate 事务-spring注解方式事务控制没有回滚

问题描述 spring注解方式事务控制没有回滚 项目中使用到了hibernate以及spring事务控制,在service层增加事务控制但是遇到异常没有回滚. 代码如下: 配置文件 <?xml version="1.0" encoding="UTF-8"?> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springfr

spring3 注解式事务不起作用

问题描述 用spring3+hibernate配置注解式事务,测试的时候抛出异常总是不回滚.spring主配置文件hibernate.xml<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-

SQL Server触发器insert update delete示例

·只有inserted表有数据时,当前操作为insert: ·inserted和deleted两张表都有数据时,当前操作为update: ·只有deleted表有数据时,当前操作为delete. 创建触发器用 CREATE TRIGGER  代码如下 复制代码 CREATE TRIGGER 触发器名称 ON 表名 FOR INSERT.UPDATE 或 DELETE AS nserted.deleted 这是两个虚拟表,inserted 保存的是 insert 或 update 之后所影响的记录

在PostgreSQL中实现update | delete limit

背景 使用MySQL的用户可能会比较熟悉这样的用法,更新或删除时可以指定限制更新或删除多少条记录. 达到限定的记录数后不再继续进行下去,而是返回. delete from tbl where xxx limit 100; update tbl set xxx=xxx where xxx limit 100; 目前PostgreSQL没有类似的语法,但是可以通过其他手段来达到同样的效果. with语法实现 创建测试表 postgres=# create table t(id int primary

关于MYSQL DML(UPDATE DELETE)中的子查询问题和ERROR 1093 (HY000)错误

从5.6开始MYSQL的子查询进行了大量的优化,5.5中只有EXISTS strategy,在5.7中包含如下: IN(=ANY) --Semi-join   --table pullout(最快的,子查询条件为唯一键)   --first match   --semi-join materialization   --loosescan   --duplicateweedout --Materialization --EXISTS strategy(最慢的) NOT IN( <>ALL) -

简述SQL SERVER触发器内INSERT,UPDATE,DELETE的三种状态

一个触发器内三种INSERT,UPDATE,DELETE状态 CREATE   TRIGGER   tr_T_A   ON     T_A   for   INSERT,UPDATE,DELETE        如IF   exists   (select   *   from   inserted)   and   not   exists   (select   *   from   deleted)   则为   INSERT  如IF   exists(select   *   from

LINQ体验(9)——LINQ to SQL语句之Insert/Update/Delete操作

我们继续讲解LINQ to SQL语句,这篇我们来讨论Insert/Update/Delete操作 .这个在我们的程序中最为常用了.我们直接看例子. Insert/Update/Delete操作插入(Insert)1.简单形式 说明:new一个 对象,使用InsertOnSubmit方法将其加入到对应的集合中,使用SubmitChanges ()提交到数据库. NorthwindDataContext db = new NorthwindDataContext(); var newCustome

asp access insert update delete 使用

本教程是一篇asp入门篇的初级教程了,主要是讲asp access insert update delete 简单的使用哦,好了下面我们把它们写在一个函数里面然后再举例说明. Function add_del_update(tablename,str,id) Select Case str  Case "insert":    sql="select * from ["&tablename&"] where id=null"   

spring事务注解@Transactional事务不回滚

问题描述 spring事务注解@Transactional事务不回滚 如题: public interface A { public void A(); } public class B implements A{ public void A(){ B();} @Transactional public void B(){ //删除数据 //两者之前发生异常了... //添加数据 } } 如果将@Transactional注解放在某一service实现类的某一非入口方法,发现数据不会回滚,请问大