Full-Text indexing----creating multiple-table SOLR indexes

when we use SOLR as an index server, we typically combine multiple fields of multiple tables as a federated index, and fast data queries on multiple tables are also the efficiencies of the SOLR server. This article describes how to create an index based on multiple data tables.
Create an index without associated multiple tables
1.1 Database Preparation

We use any of the two tables as the data source, the two tables can belong to a database, or can belong to a different database, if you use two databases, you need two data source connection string, we use the same library of two tables as an example.

the two table structures are as follows:

Table I:

Table II:

1.2 Configuring Data-config.xml
We have previously configured the SOLR server, so we only need to modify the data source configuration file and the index configuration file. The data source file is configured as follows:

<datasource name= "Jfinal_demo" type= "Jdbcdatasource" driver= "Com.mysql.jdbc.Driver"

Url= "Jdbc:mysql://192.168.21.20:3306/jfinal_demo" user= "root" password= "123456" batchsize= "-1"/> <document Name= "Testdoc" ><entity name= "user" datasource= "Jfinal_demo" pk= "id" query= "SELECT * from User" ><field column= "id" name= "userId"/>  <span style= "White-space:pre" ></span><field column= "UserName" Name= "UserName"/><field column= "Userage" name= "userage"/><field column= "useraddress" Name= "userAddress "/></entity><entity name=" role "pk=" id "query=" SELECT * from Role "><field column=" id "name=" roleid "/ ><field column= "name" Name= "RoleName"/></entity></document>

Description: The datasource tag is a database connection string, the Name property as the connection string identifier, type is the data source types, we choose the JDBC data source jdbcdatasource,drive is data driven, select MySQL data driven, The URL is a database connection string.
The document tag adds the data we need to index, the entity represents a body, the name attribute is used to distinguish between different entities, the PK attribute is the primary key of the data table, this property must be consistent with the primary key of the data table and cannot be modified.
Field tags are fields that need to be indexed, column is the data columns, and name is an alias.
Note: To set the primary key is to pay special attention, if the two tables of the primary key data consistent, then the index will overwrite the front of the index, but in many cases, we use self-growing data as the primary key, so the inevitable will produce a primary key consistency, Baidu a bit, there are roughly two ways to solve:
    1 primary keys use the UUID format to fundamentally avoid gradual consistency and are more secure.
    2 Remove the UniqueKey property in Schema.xml, or create a new field in the table as the UniqueKey property.
    1.3 Configuring Schema.xml
    Add all the fields in the table that need to be indexed to the Field tab in the file, and note that the same fields do not want to be added, such as IDs. The configuration is as follows:


    1.4 Restart SOLR Service, test.

    Two related tables create an index
    The steps for creating an index on a table are consistent with the unrelated table, except that the Data-config.xml configuration is different, as described below.

2.1 The database structure diagram is as follows:

The 2.2 Data-config.xml is configured as follows:

<datasource type= "Jdbcdatasource" driver= "Com.mysql.jdbc.Driver" url= "jdbc:mysql://192.168.21.20:3306/jrkj_ Java "user=" root "password=" 123456 "batchsize="-1 "/><document name=" Testdoc "><entity name=" Tj_student " pk= "id" query= "select Id,nickname,isdelete from tj_student where isdelete = 0 and applyteacherstate = 1 and Isteacher = 1" ><field column= "id" name= "id"/><field column= "nickname" Name= "nickname"/><field column= "IsDelete" Name= "Isdelete"/><entity name= "Tj_course" pk= "id" query= "select Id,coursename from Tj_course where isdelete=0 and Courseauditstate=1 and studentid= ' ${tj_student.id} ' "><field column=" id "name=" CourseID "/><field column= "Coursename" name= "Coursename"/> </entity><entity name= "Tj_userfield" pk= "id" query= "select FieldId, StudentID from Tj_userfield where isdelete = 0 and Userfieldtype = 1 and StudentID = ' ${tj_student.id} ' ><field Colu Mn= "FieldID" name= "FieldID"/><field column= "StudentID" Name= "StudentID "/><entity name=" Tj_field "pk=" id "query=" select Fieldname,pointword from Tj_field where isdelete = 0 and I D= ' ${tj_userfield.fieldid} ' "><field column=" FieldName "name=" FieldName "/><field column=" Pointword " Name= "Pointword"/></entity></entity><entity name= "Tj_userindustry" pk= "id" query= "select Industryid from tj_userindustry where isdelete = 0 and Userindustrytype = 1 and StudentID = ' ${tj_student.id} ' ><fie LD column= "Industryid"/><entity name= "Tj_industry" pk= "id" query= "select Industryname from Tj_industry where Isdelete = 0 and id = ' ${tj_userindustry.industryid} ' "><field column=" Industryname "name=" Industryname "/>< /entity></entity></entity> </document>

In addition to this form above, the official website gives the second way, the structure is as follows:

<datasource type= "Jdbcdatasource" driver= "Com.mysql.jdbc.Driver" url= "jdbc:mysql://192.168.21.20:3306/jrkj_ Java "user=" root "password=" 123456 "batchsize="-1 "/><document name=" Testdoc "><entity name=" Tj_student " pk= "id" query= "select Id,nickname,isdelete from tj_student where isdelete = 0 and applyteacherstate = 1 and Isteacher = 1" ><entity name= "Tj_course" pk= "id" query= "select Id,coursename from Tj_course where isdelete=0 and Courseauditstate =1 and studentid= ' ${tj_student.id} ' "> </entity><entity name=" Tj_userfield "pk=" id "query=" select FieldId, StudentID from Tj_userfield where isdelete = 0 and Userfieldtype = 1 and StudentID = ' ${tj_student.id} ' ><entity nam E= "Tj_field" pk= "id" query= "select Fieldname,pointword from Tj_field where isdelete = 0 and id= ' ${tj_userfield.fieldid} '" ></entity></entity><entity name= "Tj_userindustry" pk= "id" query= "select Industryid from Tj_ userindustry where Isdelete = 0 and Userindustrytype = 1 and StUdentid = ' ${tj_student.id} ' "><entity name=" Tj_industry "pk=" id "query=" select Industryname from Tj_industry where Isdelete = 0 and id = ' ${tj_userindustry.industryid} ' ></entity></entity></entity> </ Document>

2.3 Configuring Schema.xml

The configuration is as follows:

2.4 Restart SOLR service, test.

three summary

Multi-table indexes are the most common index for SOLR servers because index keywords are easy to duplicate, especially when primary keys are error prone, so you need to be very careful when creating indexes.

Full-Text indexing----creating multiple-table SOLR indexes