在GreenDao开源代码中添加FTS4全文检索功能

最新推荐文章于 2021-05-27 18:50:23 发布

kexinmm

最新推荐文章于 2021-05-27 18:50:23 发布

阅读量352

点赞数 2

分类专栏： GreenDao 文章标签：数据库安卓

本文链接：https://blog.csdn.net/kexinmm/article/details/114640076

版权

GreenDao 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

GreenDao并不支持全文检索，导致在大量数据中的查询效率低下。本文介绍了如何在GreenDao中添加支持全文检索的功能，希望对GreenDao粉有所帮助。
本文基于GreenDao V3.2.3 + rk3288 android7.1.2实测有效，读者有任何问题可留言沟通。

1.修改DaoGeneratorV3

1)Entity.java增加一个属性useFts，用来标识是否使用全文检索。

private boolean useFts;//lihuili add 20210310 for FTS 

    public boolean isUseFts() {
		return useFts;
	}

	public void setUseFts(boolean useFts) {
		this.useFts = useFts;
	}

	Entity(Schema schema, String className) {
        this.schema = schema;
        this.className = className;
        properties = new ArrayList<Property>();
        propertiesPk = new ArrayList<Property>();
        propertiesNonPk = new ArrayList<Property>();
        propertyNames = new HashSet<String>();
        indexes = new ArrayList<Index>();
        multiIndexes = new ArrayList<Index>();
        toOneRelations = new ArrayList<ToOne>();
        toManyRelations = new ArrayList<ToManyBase>();
        incomingToManyRelations = new ArrayList<ToManyBase>();
        additionalImportsEntity = new TreeSet<String>();
        additionalImportsDao = new TreeSet<String>();
        interfacesToImplement = new ArrayList<String>();
        contentProviders = new ArrayList<ContentProvider>();
        constructors = true;
        useFts = false;
    }

2)模板dao.ftl增加：如果entity.useFts为true，就创建使用FTS4的虚表

<!-- lihuili add 20210310 for FTS -->
	<#if entity.useFts>
	/** Creates the underlying database table. */
	    public static void createTable(Database db, boolean ifNotExists) {
	        String constraint = ifNotExists? "IF NOT EXISTS ": "";
	        db.execSQL("CREATE VIRTUAL TABLE " + constraint + "\"${entity.dbName}\" USING FTS4 (" + //
		<#list entity.propertiesColumns as property>
	    "\"${property.dbName}\" ${property.dbType}<#if property.constraints??> ${property.constraints} </#if><#if property_has_next>," +<#else>);");</#if> // ${property_index}: ${property.propertyName}
		</#list>
		<#if entity.indexes?has_content >
		   // Add Indexes
		<#list entity.indexes as index>
		  db.execSQL("CREATE <#if index.unique>UNIQUE </#if>INDEX " + constraint + "${index.name} ON \"${entity.dbName}\"" +
		    " (<#list index.properties 
		as property>\"${property.dbName}\"<#if (index.propertiesOrder[property_index])??> ${index.propertiesOrder[property_index]}</#if><#sep>,</#list>);");
		</#list>
		</#if>
		    }
	<#else>
		    /** Creates the underlying database table. */
		    public static void createTable(Database db, boolean ifNotExists) {
		        String constraint = ifNotExists? "IF NOT EXISTS ": "";
		        db.execSQL("CREATE TABLE " + constraint + "\"${entity.dbName}\" (" + //
		<#list entity.propertiesColumns as property>
		                "\"${property.dbName}\" ${property.dbType}<#if property.constraints??> ${property.constraints} </#if><#if property_has_next>," +<#else>);");</#if> // ${property_index}: ${property.propertyName}
		</#list>
		<#if entity.indexes?has_content >
		        // Add Indexes
		<#list entity.indexes as index>
		        db.execSQL("CREATE <#if index.unique>UNIQUE </#if>INDEX " + constraint + "${index.name} ON \"${entity.dbName}\"" +
		                " (<#list index.properties 
		as property>\"${property.dbName}\"<#if (index.propertiesOrder[property_index])??> ${index.propertiesOrder[property_index]}</#if><#sep>,</#list>);");
		</#list>
		</#if>         
		    }
	</#if>

2.修改自动生成代码的java文件

以DaoGeneratorFaceCollectSTD.java为例：
1)创建一个15列的普通表check_list


```java
private static Entity addCheck_list(Schema schema){
    	Entity check_list = schema.addEntity("check_list");
    	check_list.addLongProperty("item_id").primaryKey().autoincrement();
    	check_list.addLongProperty("file_id");
    	check_list.addStringProperty("name");
    	check_list.addStringProperty("identy_no");
    	check_list.addStringProperty("col3");
    	check_list.addStringProperty("col4");
    	check_list.addStringProperty("col5");
    	check_list.addStringProperty("col6");
    	check_list.addStringProperty("col7");
    	check_list.addStringProperty("col8");
    	check_list.addStringProperty("col9");
    	check_list.addStringProperty("col10");
    	check_list.addStringProperty("col11");
    	check_list.addStringProperty("col12");
    	check_list.addStringProperty("col13");
    	check_list.addStringProperty("col14");
    	check_list.addStringProperty("col15");    	
    	return check_list;
    }


2)创建一个3列的虚表，这3列与普通表完全一样
关键点：需要调用setUseFts(true)，明确告诉程序创建虚表。

```java
 private static Entity addCheck_list_fts(Schema schema){
    	Entity check_list_fts = schema.addEntity("check_list_fts");
    	check_list_fts.setUseFts(true);//使用FTS4
    	check_list_fts.addLongProperty("item_id").primaryKey().autoincrement();    	
    	check_list_fts.addStringProperty("name");
    	check_list_fts.addStringProperty("identy_no");    	  	
    	return check_list_fts;
}

3)在main函数中调用上面封装的函数

public static void main(String[] args) throws Exception {
        Schema schema = new Schema(SCHEMA_VERSION_INFO_DB, "com.routon.idr.facecollect.std.dao.info");
        schema.enableActiveEntitiesByDefault();
        schema.enableKeepSectionsByDefault();
        
        addCommonParam(schema);
        addFaceInfo(schema);
        addCheckfile_info(schema);
        addCheck_list(schema);
        addCheck_list_fts(schema);
        new DaoGenerator().generateAll(schema, "../iDRFaceCollectSTD/src");
    }

4)Run As Java Application自动生成代码

3.修改DaoCoreV3

1)修改Property.java，增加支持match
注意：一定要带问号！

public class Property {
	...
	public WhereCondition match(Object value) {
		return new PropertyCondition(this, " match?", value);
	}
}

4.封装FTS查询函数

以FaceInfoCtrl.java为例：
1)函数QueryCheckListFtsByIdentyNo()调用match()构造where条件

public List<Check_list_fts> QueryCheckListFtsByIdentyNo(String identy_no){
		if(!TextUtils.isEmpty(identy_no)){
			Check_list_ftsEntityDao entityDao = new Check_list_ftsEntityDao(getDBManager());
			if(entityDao!=null){
				Check_list_ftsDao dao = entityDao.getEntityDao();
				QueryBuilder<Check_list_fts> builder = dao.queryBuilder();
				builder = builder.where(Check_list_ftsDao.Properties.Identy_no.match(identy_no));
				long beginTick = SystemClock.uptimeMillis();
				List<Check_list_fts> checkListFtss = builder.build().list();
				long endTick = SystemClock.uptimeMillis();
				Log.d(TAG, "match " + identy_no + " use " + (endTick-beginTick) + "ms");
				if(checkListFtss!=null && checkListFtss.size()>0){
					Log.d(TAG, checkListFtss.get(0).getName());
				}
				return checkListFtss;
			}
		}
		return null;
	}

5.验证测试FTS4全文检索

1)先单独运行一下添加了FTS支持的test.apk，生成一个数据库文件face_info.db, 该文件中已经自动创建了空表check_list_fts
2)创建一个插入65535条记录的sql脚本文件CHECK_LIST_FTS.sql，例：

INSERT INTO "CHECK_LIST_FTS" VALUES ('1', '夏紫寒', '642401194107180628');
INSERT INTO "CHECK_LIST_FTS" VALUES ('2', '范梦松', '220904201505271831');
INSERT INTO "CHECK_LIST_FTS" VALUES ('3', '魏孤岚', '612232196501110123');

…
3)从终端pull face_info.db到pc
4)在pc使用在navicat premium把CHECK_LIST_FTS.sql导入到CHECK_LIST_FTS表
5)从pc push face_info.db到终端
6)在test.apk中添加调用FTS查询的函数QueryCheckListFtsByIdentyNo()
7)对比测试普通查询和FTS4全文检索的效率
在65535条记录中，普通查询平均耗时190ms，FTS4全文检索平均耗时5ms！