How to implement "MATCH AGAINST" NativeQuery on SpringBoot, JPA, and MySQL
I have to implement simple fulltext search function with MySQL fulltext search. (it is not enough to use any search engine like Solr)but JPA(hibernate) can't understand MATCH AGAINST statement, so I've implemented it by using native query.
The hard things of Native Query
- it cannot use named parameter (:condition) for query declaration.
only can use sequential placeholder like ?1, ?2 ..... - it cannot return the data as Page type. to return the data as Page type,
we have to create PageImpl instance manually like following. - we have to create limit statement manually.
Environment
- Spring Boot 1.3.1
- JPA(hibernate)
- MySQL5.7.10
- Design: Domain-Driven
implementation
SearchServiceImpl: call findByKeyword() via repository for execution fulltext search
/**
* execute fulltext search
*/
@Service
public class SearchServiceImpl implements SearchService {
@Autowired
SampleRepository sampleRepository;
/**
* full text search by user-inputted keyword
*
* @param keyword Search keyword
* @return Matched result
*/
public Page<MethodEntity> searchByKeyword(String keyword, Pageable pageable) {
// parse keyword and get search condition for match against(…) statement
String searchCondition = getFulltextSearchCondition(keyword);
List<SampleEntity> sampleEntities = sampleRepository.findByKeyword(searchCondition,
pageable.getOffset(),
pageable.getPageSize());
// avoid entityManager.createNativeQuery(sql).getSingleResult();
// getSingleResult returns Object type.
// map result to DTO(Entity)
CountDto countDto = sampleRepository.countByKeyword(searchCondition);
// we have to instantiate Page object from List of entity manually
Page<SampleEntity> page = new PageImpl<>(sampleEntities, pageable, countDto.getCount());
return page;
}
SampleEntity: declare native queries
/**
* Sample Entity
*/
@Entity
@Table(name = "sample")
@Getter // lombok
@Setter // lombok
@NamedNativeQueries({
// for search
// impotant: should conform name to method name in SampleRepository
@NamedNativeQuery(name = "SampleEntity.findByKeyword",
query = "SELECT s.sample_id, s.body FROM sample AS s WHERE MATCH(s.body) AGAINST(?1 IN BOOLEAN MODE) limit ?2, ?3",
resultClass = MethodEntity.class),
// for count
@NamedNativeQuery(name = "SampleEntity.countByKeyword",
query = "SELECT count(*) as count FROM sample AS s WHERE MATCH(s.body) AGAINST(?1 IN BOOLEAN MODE)",
resultClass = CountDto.class)
})
public class SampleEntity {
@Id
@GeneratedValue
@Column(name = "sample_id", nullable = false, unique = true, updatable = false)
private int id;
@Column(nullable = false)
private String title;
@Column(nullable = false)
private String body;
:
SampleRepository: declare methods which are the same name as query name in SampleEntity
/**
* Sample Repository
*/
@Repository
public interface SampleRepository extends JpaRepository<SampleEntity, Integer> {
List<SampleEntity> findByKeyword(String keyword, Integer offset, Integer pageSize);
CountDto countByKeyword(String keyword);
}
CountDto(optional): for specify resultClass to bind result
/**
* Dto for binding from "select count(*) as count from ..." query
*/
@Entity
@Getter
@Setter
public class CountDto {
@Id
private Integer count;
}