How to run custom query in the beginning of each transaction?

Question

I'm using Firebird database in my project, and my stored procedures in DB are using custom context variables that are transaction scoped.

I have to set some variables to context in the beginning of each transaction. How can I implement this without repeating code in each @Transactional annotated method?

Example:

Controller

@RestController
@RequestMapping({TBL_EMPLOYEE, TBL_EMP})
public class EmployeeController extends EmployeeCommonController<Employee> {
         
@GetMapping(PATH_LASTLOGIN)
    public List<UserLastLoginWrapper> getUserLastLoginWrapper(Long userid, tring appname) {
        return getService().getUserLastLoginWrapper(userid, appname);
    }
}
    

Service

@Transactional
public class EmployeeService{
  public List<UserLastLoginWrapper> getUserLastLoginWrapper(Long userid, String appname) {
        return ((EmployeeRepository) getRepository()).getUserLastLoginWrapper(null, userid, appname);
    }
}

Repository

@NamedNativeQuery(name = "Employee.getUserLastLoginWrapper", query = "select * from SP_USER_LAST_LOGIN(:userid, :appname)", resultSetMapping = UserLastLoginWrapper.USERLASTLOGINWRAPPER)

Most of the stored procedures are trying to get hotelrefno info from context variables, so I have to call execute procedure SP_CTX_SET_LOGIN_INFO(:hotelrefno, :userid) procedure in the beginning of each transaction.

Answer 1

I call this query after getting connection. See aspect configuration below.

@AfterReturning(pointcut = "execution(* *.getConnection(..))", returning = "connection")
public Connection prepare(Connection connection) throws SQLException {
CallableStatement cs = connection.prepareCall(
                "execute procedure SP_CTX_SET_LOGIN_INFO(?,?,?,?)");
//....
}