I have small SQL in annotations, for example:

@SqlQuery("SELECT salary FROM Employees WHERE id = :employeId")
int getSalary(@Bind("employeId") int employeId);

and if SQL is too big I move it to separate file. It's readable and very handy. But our developers think that we should have all our code in one place, so if we have separate files - we should have all SQL in separate file, like this:

//java file
@UseStringTemplateSqlLocator
int getSalary(@Bind("employeId") int employeId);

--sql file:
group EmployeeGroup;
getSalary() ::= <<
 SELECT salary FROM Employees WHERE id = :employeId
>>

So there is two opinions:

1. Store small SQL in annotation, and big in separate files.
2. Store all SQL in separate files.

What is best way for us and why?

CodePudding user response：

My context is Spring Boot with Spring Data JPA.

In my experience, I have observed and have followed the practice that annotating the Entity with the @NameQueries({}) is a reasonable approach as opposed to annotating the repository.

@NamedQueries({
    @NamedQuery(name = "foo", query = "..."), 
    @NamedQuery(name = "bar", query = "..."), 
})
@Entity
public class Something {

}

It make sense because it will group all the related queries together for that entity and provide a good starting point to follow the related entities.

These named queries can then be referenced on the @JPARepository interface method declarations using @Query(name = "queryname").

CodePudding user response：

This can start an endless discussion, but in my opinion and experience it depends. I prefer to maintain all raw SQL in separated files and in plain sql format so I can copy and test it directly from the database console. But for JPQL I try to use criteria queries as much as possible, but if textual JPQL is required and the query is not complex, Annotations are ok. In many cases I prefer to put complex raw sql queries in stored procedures so I don't have to deal with them in java, just call the procedure. That have the advantage that you can use/test the queries directly from the database too.