SpringBoot执行原生删表查询遇TransactionRequiredException求助

报错信息 #

Error logging in: Request processing failed; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query

我在删除数据库记录时执行DROP TABLE原生查询，触发了上述错误。查了相关资料但没解决，已经在executeDropTable()方法上加了@Transactional注解，问题依然存在，附上代码求帮助：

package com.ssc.test.cb3.service;

import com.ssc.test.cb3.dto.ReportRequestDTO;
import com.ssc.test.cb3.dto.mapper.ReportRequestMapper;
import com.ssc.test.cb3.repository.ReportRequestRepository;
import java.util.List;
import org.springframework.stereotype.Service;
import com.ssc.test.cb3.model.ReportRequest;
import com.ssc.test.cb3.repository.ReportTableRepository;
import java.util.Map;
import javax.persistence.EntityManager;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.transaction.annotation.Transactional;


/**
 * Class to prepare the services to be dispatched to the database upon request.
 *
 * @author ssc
 */
@Service
@RequiredArgsConstructor
@Slf4j
public class ReportRequestService {

    private final ReportRequestRepository reportRequestRepository;
    private final EntityManager entityManager;
    private final ReportTableRepository reportTableRepository;
    private static String SERVER_LOCATION = "D:\\JavaProjectsNetBeans\\sscb3Test\\src\\main\\resources\\";

    
    /**
     * Function to delete a report from the database
     *
     * @param id from the report request objet to identify what is the specific
     * report
     */
    public void delete(int id) {

        ReportRequest reportRequest = reportRequestRepository.findById(id).orElse(null);
        // 原代码此处存在未完成赋值的语法错误，保留原样
        ReportTable reportTable = 
        String fileName = reportRequest.getFileName();
        if (reportRequest == null || reportRequest.getStatus() == 1) {
            log.error("It was not possible to delete the selected report as it hasn't been processed yet or it was not found");
        } else {
            reportRequestRepository.deleteById(id);
            log.info("The report request {} was successfully deleted", id);
            new File(SERVER_LOCATION + reportRequest.getFileName()).delete(); // Delete file
            log.info("The file {} was successfully deleted from the server", fileName);
            // DROP created tables with file name without extention
            executeDropTable(fileName);
            log.info("The table {} was successfully deleted from the data base", fileName);
            

        }
    }

    /**
     * Service to Drop report request tables created on the database when a
     * report request is generated and serviced to be downloaded This method
     * will be called when a user deletes in the fron-end a report request in
     * finished status.
     *
     * @param tableName will be the name of the table that was created on the
     * database
     */
    @Transactional
    public void executeDropTable(String tableName) {
        int substract = 4;
        tableName = tableName.substring(0, tableName.length() - substract);
        System.out.println("Table name: " + tableName);

        String query = "DROP TABLE :tableName"; // IF EXISTS
        entityManager.createNativeQuery(query)
                .setParameter("tableName", tableName)
                .executeUpdate();
    }

}

解决步骤 #

1. 修复事务不生效问题 #

executeDropTable()的@Transactional没生效，核心原因是Spring事务代理仅对外部调用生效，delete()方法是无事务的内部调用，不会触发事务代理逻辑。解决方法二选一：

• 直接给delete()方法添加@Transactional注解：

  @Transactional
  public void delete(int id) {
      // 原有逻辑保持不变
  }
  

• 将executeDropTable()的逻辑抽离到另一个Spring管理的Bean中，通过依赖注入调用，确保是外部调用触发事务代理。

2. 修复原生SQL表名绑定错误 #

JPA的参数绑定仅支持值类型，不能用于表名、列名这类数据库对象。需要直接拼接表名（注意：确保tableName是内部生成的安全值，避免SQL注入风险）：

@Transactional
public void executeDropTable(String tableName) {
    int substract = 4;
    tableName = tableName.substring(0, tableName.length() - substract);
    System.out.println("Table name: " + tableName);

    // MySQL可加IF EXISTS避免表不存在报错，其他数据库可调整语法
    String query = "DROP TABLE IF EXISTS " + tableName;
    entityManager.createNativeQuery(query).executeUpdate();
}

3. 额外检查 #

确保项目已开启Spring事务管理：在配置类上添加@EnableTransactionManagement注解，同时确认数据源和事务管理器的配置正常。

内容的提问来源于stack exchange，提问作者Rosh343