HomeSpring BootSpring Data JDBC - Query Derivation Examples

Spring Data JDBC – Query Derivation Examples

This is a quick guide that provides several examples to understand how to use Query Derivation/Query Methods in Spring Data JDBC.

1. Spring Data JDBC Query Derivation Support

Since 2.0 query derivation is supported in Spring Data JDBC. Earlier versions supported query methods using only @Query, there is no support for Query Derivation in Spring Data JDBC.

1.1. Entity class

To demonstrate query methods examples created an entity class.

@Data // lomok
public class User {

	private Long id;
	private String userName;
	private String password;
	private String email;
	private Date createdTime;
	private Date updatedTime;
	@Column("DOB") // to map db column if property not same as column name
	private Date dateofBirth;
	private UserType userType; // Enum Type // to not persist into DB (just to expose to view/client)
	private String dateOfBirthString;
	// to display on view
	public String getDateOfBirthString() {
		return this.dateofBirth.toString();

1.2. UserRepository with Derived Queries

Following Example explains :

  1. Derived query to return basic type or null.
  2. Derived query to return an Optional/entity.
  3. Derived query for multiple fields.
  4. Derived query method to get Collection OR Java 8 Streams.
public interface UserRepository extends CrudRepository<User, Long>{
	Optional<User> findByUserName(String userName);
	List<User> findByUserNameAndUserType(String userName, String userType);
	Stream<User> findByUserNameStartsWith(String userName);
	User findByEmail(String email);

       // ....

Derived query methods to get paginated and sorted results.

public interface UserPaginationAndSortRepository extends PagingAndSortingRepository<User, Long>{
        // ...
	/* query method using pageable */
	List<User> findByUserType(String userType, Pageable pageable);
	/* query method using Sort */
	List<User> findByUserType(String userType, Sort sort);
        // ...

Refer to get all Supported keywords by Spring Data Jdbc.

2. Using @Query annotation

@Query annotation supports defining a native query manually as String.

public interface UserRepository extends CrudRepository<User, Long>{
        // ...

	@Query("SELECT * FROM USER u where = :email")
	User findByEmailAddress(@Param("email") String email);
       // ...

3. Named Queries in Spring Data JDBC

Named queries has to be defined in under META-INF folder in class path. The location of that file may be changed by setting a value to @EnableJdbcRepositories.namedQueriesLocation.

Query lookup for named queries is the default is to take the domain class simple name and append the name of the method separated by a .

Defining Named Query :

User.hasStrongPassword=SELECT IF((SELECT LENGTH(password) FROM USER where USER_NAME=:name) > 6, true, false)
public interface UserPaginationAndSortRepository extends PagingAndSortingRepository<User, Long>{
	// ...

	boolean hasStrongPassword(String name);
	int sampleNamedQuery(Long id);

      // ...

4. Calling Stored procedures

In Spring Data Jdbc, you can call stored procedures using @Query annotation with IN and OUT parameters. To get results of OUT parameter, we need to declare OUT variable in query to store returned value.

Example Mysql Stored Procedures :

/* Simple Stored procedure with only IN parameter */
DROP procedure IF EXISTS `search_users_proc`$

CREATE PROCEDURE `search_users_proc` (IN name VARCHAR(50))
    SELECT *
    WHERE user_name LIKE CONCAT('%', name , '%');

/*  Simple Stored procedure with IN and OUT parameters */
DROP procedure IF EXISTS `count_search_users_proc`$

CREATE PROCEDURE `count_search_users_proc` (
   IN  name VARCHAR(50),
   OUT total INT
  INTO total
  WHERE user_name LIKE CONCAT('%', name , '%');
  SELECT total AS users_found;

Calling Stored Procedures in Spring Data Jdbc :

public interface UserRepository extends CrudRepository<User, Long>{

        // ...
	@Query(value="call search_users_proc(:name);")
	List<User> searchUsersByName(@Param("name") String name);
	@Query(value="call count_search_users_proc(:name, @total);")
	int countSearchUsersByName(@Param("name") String name);

        // ...

5. Using Custom RowMapper

You can configure a custom RowMapper either @Query(rowMapperClass = …​.)

public interface UserRepository extends CrudRepository<User, Long>{
	// ...

	@Query(value="call search_users_proc(:name);", rowMapperClass = UserRowMapper.class)
	List<User> searchUsersByName(@Param("name") String name);
	// ...
public class UserRowMapper implements RowMapper<User>{

	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
		User user = new User();

		return user;

In other way by registering a RowMapperMap bean and registering a RowMapper per method return type. The following example shows how to register DefaultQueryMappingConfiguration:

public class MySpringBootApplication {

	// ...

	QueryMappingConfiguration rowMappers() {
		return new DefaultQueryMappingConfiguration()
				.registerRowMapper(User.class, new UserRowMapper());


6. Modifying Query

You can mark a query as being a modifying query by using the @Modifying on query method, as the following example shows:

public interface UserRepository extends CrudRepository<User, Long>{
       // ....
	boolean updateUserName(@Param("name") String name, @Param("id") Long id);

7. Conclusion

In this quick guide we have covered the support for Query Derivation and Query methods in Spring Data JDBC with simple examples.

8. References

  1. Spring Document
  2. Spring Data JDBC Example

Other Spring Data JDBC Tutorials :

  1. Spring Data JDBC – Embedded Entities
  2. Spring Data JDBC – Pagination
  3. Spring Data JDBC – One-to-Many
  4. Spring Data JDBC – Many-to-Many
  5. Spring Data JDBC – One-to-One
  6. Spring Boot + Spring Data JDBC
  7. Spring Boot – Loading Initial data


Please enter your comment!
Please enter your name here