小例子
我们在表格Ticket和TicketComment中加入了fulltext key。小例子在Ticket的Subject或Body,以及在TicketComment的Body检索内容,按分页方式显示出来,同时显示关联分数,并按关联分数降序排列。
-- Ticket中队Subject和Body这两列进行全文检索
FULLTEXT KEY `Ticket_Search` (`Subject`,`Body`),
-- TicketComment中对Body列进行全文检索。
FULLTEXT KEY `TicketComment_Search` (`Body`),
增加一个搜索结果,涵盖TicketEntity和关联分数
根据需求,我们将检索两个表格的内容,获取分数,以检索hello为例子,SQL语句如下-- 显示Ticket表的内容
SELECT DISTINCT t.*,
-- 显示关联分数(将两个表格的关联分数加起来),列名为 ft_scoreColumn
(MATCH(t.Subject, t.Body) AGAINST("hello") + MATCH(c.Body) AGAINST("hello")) AS _ft_scoreColumn
-- 将表格Ticket和TicketComment 根据ticket.id join在一起
FROM Ticket t LEFT OUTER JOIN TicketComment c ON c.TicketId = t.TicketId
-- where存在关联性
WHERE MATCH(t.Subject, t.Body) AGAINST("hello") OR MATCH(c.Body) AGAINST("hello")
-- 设置排序
ORDER BY _ft_scoreColumn DESC, TicketId DESC;
在关联表格的返回中,通常会包含多个内容,并不是只与某个Entity相对应,在本例中就含有分值,我们将学习如何映射这样的结果。
创建存放结果的类SearchResult
public class SearchResult<T> {
private final T entity;
private final double relevance;
public SearchResult(T entity, double relevance) {
this.entity = entity;
this.relevance = relevance;
}
public T getEntity() {
return entity;
}
public double getRelevance() {
return relevance;
}
}
@SqlResultSetMapping提供返回结果的映射关系
我们将这个映射关系命名为"searchResultMapping.ticket",放在TicketEntity中,当然也可以放在其他的Class,只要标记@SqlResultSetMapping即可。
@Entity
@Table(name = "Ticket")
@SqlResultSetMapping(
name = "searchResultMapping.ticket",
entities = { @EntityResult(entityClass = TicketEntity.class) },
columns = { @ColumnResult(name = "_ft_scoreColumn", type = Double.class)}
)
public class TicketEntity implements Serializable
这种方式等同与xml的配置。
<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm
http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd"
version="2.1">
<sql-result-set-mapping name="searchResultMapping.ticket">
<entity-result entity-class="com.wrox.site.entities.TicketEntity" />
<column-result name="_ft_scoreColumn" class="java.lang.Double" />
</sql-result-set-mapping>
</entity-mappings>
这个配置一般位于/META-INF/orm.xml。也可以在persistence.xml中通过<mapping-file>来执行位置,或者通过下面的代码来执行位置。
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(){
...
factory.setJpaPropertyMap(properties);
factory.setMappingResources("com/example/config/mappings.xml");
return factory;
}
在createNativeQuery中将结果进行映射
我们使用了原生的SQL语句,映射方式如下,例子的具体实现在后面介绍
Query query = entityManager.createNativeQuery(sql, "searchResultMapping.ticket");
List<Object[]> results = query.getResultList();
for(Object[] result : results){
//result[0]对应searchResultMapping.ticket的第一项entites里面的TicketEntity.class
//result[1]对应searchResultMapping.ticket的第二项,因为entities只有一项,因此为columns中的name = "_ft_scoreColumn", type = Double.class
}
在仓库中增加查询接口
public interface SearchableRepository<T>{
Page<SearchResult<T>> search(String query, boolean useBooleanMode, Pageable pageable);
}
public interface TicketRepository extends CrudRepository<TicketEntity, Long>,SearchableRepository<TicketEntity>{
}
接口实现
public class TicketRepositoryImpl implements SearchableRepository<TicketEntity>{
@PersistenceContext EntityManager entityManager;
@Override
public Page<SearchResult<TicketEntity>> search(String query, boolean useBooleanMode, Pageable pageable) {
String mode = useBooleanMode ? "IN BOOLEAN MODE" : "IN NATURAL LANGUAGE MODE";
String matchTicket = "MATCH(t.Subject, t.Body) AGAINST(?1 " + mode + ")";
String matchComment = "MATCH(c.Body) AGAINST(?1 " + mode + ")";
//1】分页需要获得总数以及该页的数据,显示获取总数。请参考前面对sql的说明。
String sql = "SELECT COUNT(DISTINCT t.TicketId) FROM Ticket t " +
"LEFT OUTER JOIN TicketComment c ON c.TicketId = " +
"t.TicketId WHERE " + matchTicket + " OR " + matchComment;
//对于原生SQL的方式,返回结果是BigInteger不能直接转换为Long。采用了Number来进行。
long total = ((Number) this.entityManager.createNativeQuery(sql).setParameter(1, query).getSingleResult())
.longValue();
//2】获取该页的信息,
sql = "SELECT DISTINCT t.*, (" + matchTicket + " + " + matchComment +") AS _ft_scoreColumn " +
"FROM Ticket t LEFT OUTER JOIN TicketComment c ON c.TicketId = t.TicketId " +
"WHERE " + matchTicket + " OR " + matchComment + " " +
"ORDER BY _ft_scoreColumn DESC, TicketId DESC";
@SuppressWarnings("unchecked")
List<Object[]> results = this.entityManager.createNativeQuery(sql, "searchResultMapping.ticket")
.setParameter(1, query)
.setFirstResult(pageable.getOffset())
.setMaxResults(pageable.getPageSize())
.getResultList();
//3】将结果转为我们定义SearchResult。
List<SearchResult<TicketEntity>> list = new ArrayList<>();
results.forEach(o -> list.add(
new SearchResult<TicketEntity>((TicketEntity)o[0], (Double)o[1])));
return new PageImpl<>(list,pageable,total);
}
}
使用createNativeQuery而不是criteria JPA接口意味着实现和底层和数据库相关,如果更换为其他数据库,需要重新编写代码,而有些数据库支持fulltext key有些不支持。