0

I'm trying to take backup of the existing table through JPA in Spring boot. For this I'm passing table name date as parameter in the below query but, Its not setting in the query. If table name cannot be modified dynamically, Then can anyone suggest alternate solution for this.

Expected Result: INSURANCE_P_CIF_08/03/2019 Actual Result: INSURANCE_P_CIF_@P0

InsuranceCIFRepository.java

package com.utility.snapp.crud.repository;

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

import com.utility.snapp.entities.InsuranceCIF;

@Repository
public interface InsuranceCIFRepository extends JpaRepository<InsuranceCIF, Long>{

    @Query(value = "select * into INSURANCE_P_CIF_?1 from INSURANCE_P_CIF where 1=1", nativeQuery = true)
    public void takeInsurancePCIFBackup(String currentMonthAndYear);

}

TableBackupService.java

package com.utility.service;

import java.time.LocalDateTime;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.utility.snapp.crud.repository.InsuranceCIFRepository;

@Component
public class TableBackupService {

    @Autowired
    InsuranceCIFRepository insuranceCIFRepository;

    public  void takeBackupOfExistingTables() {
        String currentMonthAndYear=getCurrentMonthAndYear();
        System.out.println(currentMonthAndYear+insuranceCIFRepository);
        try {
        insuranceCIFRepository.takeInsurancePCIFBackup(currentMonthAndYear);
        }
        catch(Exception e) {

        }
    }

    public static String getCurrentMonthAndYear() {
        LocalDateTime currentTime=LocalDateTime.now();
        String month=currentTime.getMonth().toString();
        int year=currentTime.getYear();
        return (month+year+"");
    }

}
Salmaan Ahamed A.M
  • 1
  • 2

1 Answers1

0

Alternate solution:

  1. Create a database procedure as below.

delimiter $$

CREATE OR REPLACE PROCEDURE INSURANCE_P_CIF_BACKUP()

BEGIN

DECLARE createTable varchar(200);

SET createTable = CONCAT("CREATE TABLE INSURANCE_P_CIF",DATE_FORMAT(SYSDATE(), '%d%m%y')," AS SELECT * FROM INSURANCE_P_CIF");

EXECUTE IMMEDIATE createTable;

end$$

  1. Call the procedure in REST API method.
    call INSURANCE_P_CIF_BACKUP();
jugalmadhavan
  • 146
  • 4