How can I execute a native query to the database from any class in a Java SpringBoot project?

Question

I'm trying to drop a table when deleting a record in the database, but it is giving me the following error:

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

I have read couple of articles and even some questions in Stack overflow, this one Question about the errorbut none of the answers are working, the one I see that might help the most is adding the note @Transactional which I put over the method executeDropTable() but it is giving me the same error., this is my code:

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();
    }

}

Can anyone please help me to sort this out?

Have you tried to use `entityManager.getTransaction().beginTransaction()`? — Roggi, Oct 28 '22 at 21:52
I did, but I didn't know where to put the rest of the code, for example the create query, it is giving me an error saying `void cannot be dereferenced: ` I'm putting it like this: `entityManager.getTransaction().begin() .createNativeQuery(query) .setParameter("tableName", tableName) .executeUpdate();` — Rosh343, Oct 28 '22 at 21:59
It is just starting the SpringBoot application and then verify a user from the database, and then, it prints the system out print I put on the method above and after that I get the error, after adding some lines: `Table name: t_cdr_1666971034226 2022-10-28 18:24:39.164 ERROR 25188 --- [nio-8080-exec-4] c.s.t.c.f.CustomAuthorizationFilter : Error logging in: Request processing failed; nested exception is java.lang.IllegalStateEx` — Rosh343, Oct 28 '22 at 23:28
I guess, that your architecture is not good. You should never drop/create a table in a normal workflow. Consider to delete all rows instead or use foreign keys with cascade delete. — akop, Oct 29 '22 at 11:01
what happens is that we need to delete some tables from the database as there is a process for downloading some .xls files that create a table every time the file is generated, what I'm trying to do when I delete the row of a table is to also DROP the tables that have been created on the database that can be a lot of them. @akop — Rosh343, Oct 29 '22 at 20:14
It also doesn't sounds right, that a download creates a table. This will not scale very well and has the potential to cause more problems, because databases are not designed to change their schema so often. — akop, Oct 30 '22 at 07:18
Oh I see, that's good to know! But in the case that you need to do it, how would you answer the question above? — Rosh343, Oct 30 '22 at 23:41

invzbl3 · Answer 1 · 2022-10-28T22:14:25.930

1

A native query literally means "execute this SQL statement on the database", but you are trying to use JPL or something else with variable expansion.

your SQL string is invalid, try:

String query = "DROP TABLE " + tablename;

entityManager.executeNativeQuery(query);

edited Oct 28 '22 at 22:14

answered Oct 28 '22 at 21:57

invzbl3

5,872
9
36
76

So it means that by using native query, I can perform any action into the database from anywhere just by declaring the statement with the Entity Manager? I'm trying the SQL string in that way and it is giving me the following error: `nested exception is java.lang.IllegalArgumentException: Could not locate named parameter [tableName], expecting one of []` – Rosh343 Oct 28 '22 at 22:43
The ideal way to do so is create a repository for the entity/table and autowire it in the targeted class. Coming to the native query part, you need to use below annotation in the repository above the method. `@Query(isNativeQuery=true, "Normal sql query")` – invzbl3 Oct 28 '22 at 23:12
Yeah, what happens is that we need to delete some tables from the database as there is a process for downloading some .xls files that create a table every time the file is generated, what I'm trying to do when I delete the row of a table is to also DROP the tables that have been created, but I'm not sure if I can delete any table from the Database within the repository of a single table entity, or is there a better way perform this action to drop tables from the database? – Rosh343 Oct 28 '22 at 23:23
If you write :tableName your database expects a parameter called "tableName" but you don't provide it, so just append the tableName to the string without the : or provide the parameter (don't know the API method for this) – invzbl3 Oct 29 '22 at 00:30
Oh, I see! If I'm trying to pass the name of the table I want to drop through an argument, should I append it or pass it as a parameter? the statement would be: DROP TABLE (name of the table), I'm trying to pass the name of the table I want to drop to that part of the sql sentence. – Rosh343 Oct 29 '22 at 00:52

How can I execute a native query to the database from any class in a Java SpringBoot project?

1 Answers1