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 :
- Derived query to return basic type or
null. - Derived query to return an Optional/entity.
- Derived query for multiple fields.
- 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
Other Spring Data JDBC Tutorials :
- Spring Data JDBC – Embedded Entities
- Spring Data JDBC – Pagination
- Spring Data JDBC – One-to-Many
- Spring Data JDBC – Many-to-Many
- Spring Data JDBC – One-to-One
- Spring Boot + Spring Data JDBC
- Spring Boot – Loading Initial data
