Home Spring Boot Spring 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 {

	@Id
	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

	@org.springframework.data.annotation.Transient // 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.
@Repository
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.

@Repository
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.

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

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

3. Named Queries in Spring Data JDBC

Named queries has to be defined in jdbc-named-queries.properties 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)
User.sampleNamedQuery=SELECT LENGTH(PASSWORD) FROM USER WHERE ID=:id
@Repository
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))
BEGIN
    SELECT *
    FROM USER
    WHERE user_name LIKE CONCAT('%', name , '%');
END$


/*  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
)
BEGIN
  SELECT COUNT(id)
  INTO total
  FROM USER
  WHERE user_name LIKE CONCAT('%', name , '%');
  SELECT total AS users_found;
END

Calling Stored Procedures in Spring Data Jdbc :

@Repository
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 = …​.)

@Repository
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>{

	@Override
	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
		
		User user = new User();
		user.setId(rs.getLong("ID"));
		user.setUserName(rs.getString("USER_NAME"));
		user.setPassword(rs.getString("PASSWORD"));
		user.setEmail(rs.getString("EMAIL"));
		user.setCreatedTime(rs.getDate("CREATED_TIME"));
		user.setUpdatedTime(rs.getDate("UPDATED_TIME"));
		user.setUserType(UserType.valueOf(rs.getString("USER_TYPE")));
		user.setDateofBirth(rs.getDate("DOB"));

		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:

@SpringBootApplication
public class MySpringBootApplication {

	// ...

	@Bean
	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:

@Repository
public interface UserRepository extends CrudRepository<User, Long>{
	
       // ....
	
	@Modifying
	@Query("UPDATE USER SET USER_NAME=:name WHERE ID=:id")
	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 Boot + Spring Data JDBC 2.0

Satish Varma
Satish Varmahttps://javabydeveloper.com
Satish is post graduated in master of computer applications and experienced software engineer with focus on Spring, JPA, REST, TDD and web development. Follow him on LinkedIn or Twitter or Facebook

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay in Touch

Categories