[Writing Practice]: Spring Data JPA Best Practices: Transactions and Manual Queries
This blogs solely for writing practice, so it's primarily a copy blog/article, not intended for content dissemination.
Root blog/article: link-blog
Manual and automatic transaction management
Imagine you need custom logic to work with your database, such as managing transactions, but Spring Data JPA repositories are insufficient for you. So what could you do? Spring Data JPA offers various approaches to achieve this.
Using EntityManager to manage transactions
You can use the JPA EntityManager together with EntityTransaction to mange transactions in Spring manually, as it provides low-level access to the persistence context and database operations, but managing transactions yourself quickly becomes error-prone and required a lot of careful attention.
Threre is one more catch: you cannot use the injected EntityManager to start a transaction directly. In a typical Spring setup, that EntityManager is a container-managed proxy, and Spring expects you to use its own transaction management mechanisms instead.
Creating your own EntityManager from an EntityManagerFactory and using JPA's 'EntityTransaction' API is technically possible, but it bypasses Spring's transaction infrastructure and is generally discouraged except in very special cases.
Let's examine a small code example that utilizes the entity manager to manage transactions.
try (EntityManager em = emf.createEntityManager()) {
EntityTransaction tx = em.getTransaction();
try {
tx.begin();
em.persist(new MyEntity());
tx.commit();
} catch (Exception e) {
if (tx.isActive()) tx.rollback();
throw e;
}
}
Ouch! There is a lot of code. You should always use the entity manager factory to create an instance of the manager. Be careful, as it should be closed at the end of the logic. Additionally, ensure that commits and rollbacks are placed. That looks like a burden with different ways to shoot yourself in the foot.
I couldn't recommend this approach to managing transactions, but real life has plenty of nuances, and for some reason, you may have to use it. Actually, I used it before (spoiler: is was a legacy project), as I had complex low-level logic to manage database transactions.
Using TransactionTemplate
Manual transaction management with an entity manager is a headache, but Spring @Transactional annotation too magical? There is a solution in the middle, it's a TransactionTemplate.
This approach gives you the best of worlds: you get prog rammatic control over exactly where a transaction starts and ends, but you let Spring handle commits, rollbacks, and closings of the entity manager.
The beauty here is that, unlike the manual approach, you can use the injected EntityManager. Because the transaction template interacts directly with Spring's PlatformTransactionManager, it correctly binds the transaction to the current thread, allowing the standard container-managed proxy to function exactly as intended.
Look at this! The explicit try-catch-finally block is gone, along with the closing entity manager.
@Service
@RequiredArgsConstructor
public class CustomTransactionService {
private final PlatformTransactionManager txManager;
private final EntityManager em;
public void saveSafely() {
TransactionTemplate tmplt = new TransactionTemplate(txManager);
tmplt.executeWithoutResult(status -> {
em.persist(new MyEntity());
// No manual commit needed; it happens automatically on exit.
});
}
}
If your code inside the lambda throws a RuntimeException, the template automaticallyu marks the transaction for rollback. If you need to roll it back based on a specific condition without throwing an exception, you can simply call status.setRollbackOnly().
This is the preferred way to write custom transaction logic in the Spring ecosystem. It keeps your code clean, concise, and integrated with the container's lifecycle.
Using @Transaction annotation
Now we arrive at the most common approach. Why write code to manage transactions when you can just declare them with some Spring magic? This is the declarative style, and it is likely what I've seen in many Spring applications.
Instead of injecting managers or creating templates, you simply annotate your method (or class) with @Transactional. Spring's AOP infrastructure takes over, wrapping your bean in a proxy that handles the begin, commit, and rollback logic for you.
It looks so simple, you focus entirely on bussiness logic, nad the container handles the automicity.
@Service
@RequiredArgsConstructor
public class UserService {
private final UserRepository userRepository;
@Transactional
public void createUser(String name) {
userRepository.save(new User(name));
// If an unchecked exception (RuntimeException) is thrown here,
// the transaction rolls back automatically.
}
}
However, there are many nuances. I'll briefly explain some of them. However, the subject actually warrants a dedicated article.
Since it relies on Spring proxies, the interception only happens when the method is called from outside the bean. If you call a transactional method from another method within the same class (self-invocation), the proxy is bypassed, and no transaction is started.
@Service
public class TicketService {
// 1. External caller invokes this method (Proxy intercepts here)
public void bookBatch() {
// 2. Inside the class, "this" refers to the raw Target object, not the Proxy.
// 3. PROBLEM: Calling a @Transactional method from within the same class.
// The Proxy is completely bypassed. No new transaction is started.
reserveSeat();
}
@Transactional
public void reserveSeat() {
// Logic that EXPECTS a transaction, but won't get one
// if called from bookBatch().
repo.save(new Ticket());
}
}
In this scenario, if you call bookBatch() from a Controller, Spring intercepts that first call, but when bookBatch() calls reserveSeat(), it is a direct Java method call inside the instance. Spring's proxy wrapper is sitting outside, unaware that reserveSeat is running, so the spring transaction annotation is ignored.
What else? Spring Proxies cannot intercept private methods. If you do so, Spring will silently ignore your annotation without errors or warnings, which could lead to significant problems and make your app unreliable. The best way to mark this annotation is for public methods only.
One common bug in Spring transaction management is that transactions are always rolled back in the event of any exceptions. But it's not, Spring only rolls back for RuntimeException (unchecked) with its child and Error. It does not rollback for checked exceptions (like IOException or your custom BusinessException if it extends from Exception).
Best practices here? Tell Spring what to rollback or extend your exceptions from RuntimeException and you'll sheep well.
Using spring transactional annotation is a better approach than using an entity manager to manage transactions, as it eliminates the need for boilerplate code. However, you have to spend time learning all the nuances of using this annotation. Your education is a foundation for a better future for your applications.
Manual Querying
Repositories are fantastic, and they could close 90% of your work. But for the last 10%, you've to write some queries in a less abstract way. For example, you need a complex report, a specific join, or a database-specific function that Spring Data's method naming convention just can't handle. You could use mentioned before EntityManager.
One important note: EntityManager is a good way to query, but many tasks can be resolved with JPA repositories, as it provides a way to write native queries, and you don't need to write boilerplate code as above. If your project has a convention for using an entity manager or other reasons, use it. Otherwise, g ive it a try to write queries with Spring Data JPA repositories.
Query vs TypedQuery
When you create a query in JPA, you have two choices. You can use the raw Query interface, or the generic TypedQuery. The raw Query interface returns Object or List<?>. It tells the compiler, "Trust me, I know what I'm doing". This forces you to manually cast the result, which is a code smell and a potential runtime crash waiting to happend.
Just look at this example. There is casting data without verification, which could lead to diferrent runtime problems, it's better to avoid it.
public User getUserViaSimpleQuery(Long id) {
// Returns a raw Query. We have to cast (User) manually.
Query query = entityManager.createQuery(
"SELECT u FROM User u WHERE u.id = :id"
);
query.setParameter("id", id);
return (User) query.getSingleResult();
}
Always prefer TypedQuery. It carries the type information with it, allowing you to delete those unchecked casts and let the compiler help you.
public User getUserViaTypedQuery(Long id) {
// Pass the class type! Now we get TypedQuery<User>
TypedQuery<User> query = entityManager.createQuery(
"SELECT u FROM User u WHERE u.id = :id", User.class
);
query.setParameter("id", id);
return query.getSingleResult();
}
Native Queries
Sometimes JPQL isn't enough. You need to use database-specific features (like CONNECT BY in Oracle or specific window functions). This is a place where createNativeQuery should be used. But there is a problem.
If you are fetching a whole Entity, you can still map it. But if you are fetching a single column (scalar value), things get messy. in legacy code, I saw that developers were assuming that if a database column is a number, Java would return a Long.
public Long getActiveVersionId() {
Query query = entityManager.createNativeQuery(
"select id from utl_rep_version_cfg where rownum = 1"
);
// DANGER: In Oracle, this returns BigDecimal.
// Casting (Long) throws ClassCastException!
return (Long) query.getSingleResult();
}
If you run against Oracle, your app will throw exceptions. The JDBC driver often maps number to BigDecimal or BigInteger, and you cannot cast an object of type BigDecimal directly to Long.
When dealing with native queries and scalars, treat the result as a generic Number. It may seem verbose, but this approach is safer than the previous one. Use it or not, it's only your choice. However, I suggest learning a bit about JDBC data mapping, especialy for your DBMS. For Oracle, you can refer to this documentation.
public Long getActiveVersionId() {
Query query = entityManager.createNativeQuery(
"select id from utl_rep_version_cfg where rownum = 1"
);
Object result = query.getSingleResult();
// Safely convert whatever number the DB driver returns
return result != null ? ((Number) result).longValue() : null;
}
Criteria API
If TypedQuery is safe, the Criteria API adds more type safety. It allows you to build queries programmatically, protecting you from typicall problems. However, I must admit that I dislike the criteria code because it is overly wordy. It's hard to read. It looks like you are writing an abstract syntax tree instead of SQL. However, it is the only safe way to build dynamic queries where filters change at runtime.
Unfortunately, I have seen this a lot. One of the common security bugs is passing parameters with string concatenation. This approach leads tt problems because use input is not sanitized properly and becomes part of the raw SQL query. To solve this, you could use named parameters and pass them safely, or you could also utilize the criteria API for this purpose.
The following example is not complex and highlights only the SQL injection issue. In the real world, developers often use a criteria API to build custom filters depending on use input.
// BAD: Concatenation logic that creates a wide-open security hole
public boolean login(String username, String password) {
// DANGER: We are pasting user input directly into the query string.
String hql = "SELECT count(u) FROM User u " +
"WHERE u.username = '" + username + "'" +
"AND u.password = '" + password + "'";
Long count = (Long) entityManager.createQuery(hql).getSingleResult();
return count > 0;
}
Let's take a look at the alternative criteria: Yes, this is a lot of code, but we've introducecd simple logic. If the password missing, it shouldn't be used in the Where clause. When using simple queries, string concatenation is required, which can lead to security issues. Here, you've avoiding this, bringing more flexibility, but overall, it adds complexity to your codebase.
public boolean loginSafe(String username, String password) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<User> user = cq.from(User.class);
// 1. We want to count users
cq.select(cb.count(user));
// 2. Build predicates dynamically
List<Predicate> predicates = new ArrayList<>();
predicates.add(cb.equal(user.get("username"), username));
// Example of dynamic logic: simple IF statement, no string mess
if (password != null) {
predicates.add(cb.equal(user.get("password"), password));
}
cq.where(predicates.toArray(new Predicate[0]));
// 3. Execute safely
return entityManager.createQuery(cq).getSingleResult() > 0;
}
As I mentioned earlier, I don't like this approach because it's too wordy, contains a lot of code, and adds complexity. However, on other hand, you could see string concatenation. It's not pure evil. With a proper approach, there wouldn't be a problem, but it's a slippery road you've to avoid.
Passing parameters into queries
We have disscussed how to write the queries, and in the example code, I used named parameters to pass parameters safely. This may seem trivial, but in a large codebase, differences in passing parameters ca n cause significant issues. There are generally two ways to pass parameters: positional (?) and named (:id).If you value your snaity and the time of your colleagues, you should have a strong preference for this approach. For me, it's named parameters.
Positional parameter are a relic. They depend entirely on the order of arguments. If a developers comes along six months later and adds a new condtion in the middle of where clause, the parameter indexes shift, and your code breaks - often silently or with confusing data errors.
Look at this fragile code. You need to mentally prepare to update and verify the passed parameters and their indexes. To me, it's not as cools as it seems, as it leads to problems. If you could avoid problems from the start, it's better to avoid them.
TypedQuery<User> q = em.createQuery(
"SELECT u FROM User u WHERE u.id = ?1 AND u.status = ?2",
User.class
);
q.setParameter(1, id);
q.setParameter(2, Status.ACTIVE);
//and with native query
Query q = em.createNativeQuery(
"SELECT * FROM users WHERE id = ? AND status = ?",
User.class
);
q.setParameter(1, id);
q.setParameter(2, "ACTIVE");
After seeing this example, I hope you find named parameters more effective, as they provide a clearer way to keep your code clean and maintainable.Just look at this example. This is kind of self-documented code. The parameter names tell us what they are, and we don't need to concern ourselves with indexes for now.
TypedQuery<User> q = em.createQuery(
"SELECT u FROM User u WHERE u.id = :id AND u.status = :status",
User.class
);
q.setParameter("id", id);
q.setParameter("status", Status.ACTIVE);
Calling stored procedures
You can also call stored procedures via the EntityManager.
The usual pattern is:
- Create a
StoredProcedureQueryby specifying the procedure name. - Register all parameters with
registerStoredProcedureParameter. - Set the parameter values.
In many codebases, these last two steps differ - registering a parameter, setting it, and then registering the next one. This makes the code jump back and forth between two ideas (declaring parameters and providing values). I find it clearer to first register all parameters and only then set their values, but this is mostly a matter of preference.
public void execute(Long userId) {
StoredProcedureQuery spq =
em.createStoredProcedureQuery("pkg_base_computing.recalc");
spq.registerStoredProcedureParameter("p_user_id", Long.class, ParameterMode.IN);
spq.setParameter("p_user_id", userId);
spq.registerStoredProcedureParameter("p_result_id", Long.class, ParameterMode.OUT);
spq.execute();
}
For better readability, first register all parameters and then assign their valuers. It's a small stylistic change, but it slightly improves readability and maintainability, because with a quick glance, you can see which parameters the procedure uses.
public void execute(Long userId) {
StoredProcedureQuery spq =
em.createStoredProcedureQuery("pkg_base_computing.recalc");
spq.registerStoredProcedureParameter("p_user_id", Long.class, ParameterMode.IN);
spq.registerStoredProcedureParameter("p_result_id", Long.class, ParameterMode.OUT);
spq.setParameter("p_user_id", userId);
spq.execute();
}
Projection data with EntityManager
In the previous article about Spring Data JPA repositories we discussed how to project data in DTOs or Interfaces. However, when using EntityManager, you must handle projections manually. You generally have three options: mapping an entity after fetching it, selectiong directly into a DTO, or dealing with raw data.
Mapping entity to DTO with mapping libraries
The easiest way to get a DTO is ti fetch the Entity and map it. Libraries like Mapstruct or ModelMapper make your code cleaner and more readable (probably).
public UserDto getUserDtoViaMap(Long id) {
// 1. Fetches the WHOLE entity (all columns)
User user = entityManager.find(User.class, id);
// 2. Maps it in memory
UserDto userDto = new UserDto();
modelMapper.map(user, userDto);
return userDto;
}
This is functionally correct. Mapping libraries use some magic to generate the mapping logic, and while they might introduce a tiny bit of CPU overhead (especially reflection-based ones like ModelMapper), they generally make your work easier.
Mapping using a constructor
If you use JPQL, you can construct the DTO directly inside the query. This query is faster because the database only sends the columns you asked for. However, there is one major problem: you must specify the full path to the DTO.
Why is this a problem? If you rename your DTO class or move to a different package, the string in your query won't update automatically. The compiler misses this problem, and it will only appear as a RuntimeException when the code is executed. IntelliJ IDEA is smart enough to catch this and highlight it, but only if you analyze your entire code base or open the specific file. If you miss t he IDE warning, you are shipping a bug.
Here is a small example of using constructor expressions:
public UserDto getUserDtoViaQuery(Long id) {
// Note: You must use the fully qualified class name!
TypedQuery<UserDto> query = entityManager.createQuery(
"SELECT new com.app.dto.UserDto(u.username, u.email) " +
"FROM User u WHERE u.id = :id",
UserDto.class
);
query.setParameter("id", id);
return query.getSingleResult();
}
Mapping with Tuple and Object Array
When you are forced to use Native SQL, you can't use the new Class(...) syntax. This leaves you with a choice: the raw Object array or strictly typed Tuples.
Acctually, it's insane, it's scary as hell, but I see this often. Let me introduce - using object arrays for data mapping. You get an array of generic objects, and you have to know which index corresponds to which column and what type it is. If something changes in the database or you modify the indexes, an exception occurs.
// BAD: Object[] projection (Hard to read, error-prone)
public List<UserDto> getActiveUsersBad() {
List<Object[]> results = entityManager.createNativeQuery(
"SELECT id, username, is_active FROM users WHERE is_active = 1"
).getResultList();
List<UserDto> dtos = new ArrayList<>();
for (Object[] row : results) {
// MAGIC NUMBERS! What is row[1]? What is row[2]?
// You have to manually cast everything.
Long id = row[0] != null ? ((Number) row[0]).longValue() : null;
String username = (String) row[1];
Boolean active = (Boolean) row[2]; // Hope this isn't a BigDecimal in Oracle!
dtos.add(new UserDto(id, username, active));
}
return dtos;
}
The better way is to use Tuple interface. It allows you to access results by alias (name) rather than index. It is cleaner, safer, and self-documenting/
// BETTER: Tuple projection with aliases
public List<UserDto> getActiveUsersSafe() {
// 1. Note the aliases (AS id, AS username)
List<Tuple> tuples = entityManager.createNativeQuery(
"SELECT u.id AS id, u.username AS username, u.is_active AS active " +
"FROM users u WHERE u.is_active = 1",
Tuple.class
).getResultList();
List<UserDto> dtos = new ArrayList<>();
for (Tuple t : tuples) {
// 2. Fetch by name and type. No casting needed.
Long id = t.get("id", Long.class);
String username = t.get("username", String.class);
Boolean active = t.get("active", Boolean.class);
dtos.add(new UserDto(id, username, active));
}
return dtos;
}
Using Tuple makes you intent clear. You are telling the next developer exactly what columns you expect, and you don't care what order they are in.
At the end
That was a tough series of articles. I’m pleased to say that I’ve finally adopted my biggest guide and published it in three separate articles. I’ve more ideas for upcoming articles, and if you enjoy my articles, please connect with me on LinkedIn to stay informed about new content.
If you missed the previous articles, here they are: