What is the better way than using customized query in Java Spring Boot?

Question

This is written in Play framework in my Service package:

public static BigInteger validUsername(JsonNode user) {     

    Query query = JPA.em().createNativeQuery("SELECT COUNT(u.userId) FROM "+USER_TABLE+" u where username = ? and status != 'deleted'");
    query.setParameter(1, user.findPath("userName").asText());
    BigInteger count =  (BigInteger) query.getSingleResult();

return count;
}

Is there a better way to implement the same in a Java Spring Boot application?

Possible duplicate of [Is it possible to use raw SQL within a Spring Repository](https://stackoverflow.com/questions/15948795/is-it-possible-to-use-raw-sql-within-a-spring-repository) — jonrsharpe, May 28 '18 at 09:22

score 2 · Answer 1 · answered May 28 '18 at 12:49

To do this, you can use JPA and Spring Data JPA. This means you should map your tables to entities, for example:

@Entity
@Table(name = "USER_TABLE")
public class User {
    @Id
    private Long userId;
    private String username;
    private String status;

    // Getters, setters, ...
}

After that, you could use Spring Data JPA to write a repository:

public interface UserRepository extends JPARepository<User, Long> {

}

This also allows you to write custom queries in several ways, like using the @Query annotation, query methods, ... .

public interface UserRepository extends JPARepository<User, Long> {

    @Query("select count(u.userId) from User u where u.username = :username AND u.status != 'deleted'")
    int countNonDeletedUsersByUsername(@Param String username);
}

This query uses JPQL (a query language for JPA), but if you prefer to write native queries, you could always enable the nativeQuery flag, as mentioned by the documentation:

The @Query annotation allows for running native queries by setting the nativeQuery flag to true.

score 1 · Answer 2 · answered May 28 '18 at 09:24

I may advice you to use JPA Repository. HERE

you may find a lot of examples how to implement it.

In short the method from repository shoul looks like:

public interface CustomerRepository extends CrudRepository<Customer, Long> {

List<Customer> findByLastName(String lastName);
}

If you want to use native query ass in example, you may use @Query annotation and type your query as parameter.

 @Query("SELECT t.title FROM Todo t where t.id = :id") 
String findTitleById(@Param("id") Long id);

What is the better way than using customized query in Java Spring Boot?

2 Answers2