Java for Web学习笔记（一二三）：搜索（5）MySQL全文索引（下）

小例子

我们在表格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有些不支持。

相关链接：我的Professional Java for Web Applications相关文章