CODEWITHSUNDEEP
javaspring-bootspring-data-jpafirebirdspring-transactions
Erce Tilav
Erce Tilav

Reputation: 1138

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

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.

Upvotes: 2

Views: 1267

Answers (2)

Erce Tilav
Erce Tilav

Reputation: 1138

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(?,?,?,?)");
//....
}

Upvotes: 1

Simon Martinelli
Simon Martinelli

Reputation: 36123

You could create a Before Aspect

@Aspect
public class ProdcedureAspect {

    @Before("execution(* **.*Service.*(..))")
    public void doBefore(JoinPoint joinPoint) { 
       // execute procedure SP_CTX_SET_LOGIN_INFO(:hotelrefno, :userid)
    }
}

Read more about Spring AOP here:

https://docs.spring.io/spring/docs/5.1.7.RELEASE/spring-framework-reference/core.html#aop-api

Upvotes: 2

Related Questions