Solr服务器配置Sqlserver的dataimporthandler以及如何新增一个core

上篇文章已经把solr服务器搭建起来了。这边文章记录下如何配置SQL Server数据库的导入。

使用的软件：

　　jtds-1.2.4.jar 第三方mssqlserver的jdbc驱动程序

　　solr-dataimporthandler-4.8.0.jar和solr-dataimporthandler-extras-4.8.0.jar导入模块

第一步：

　　复制粘贴core根目录（D:\Working\solr\tomcat-solr）目录的collection1，并重命名为TBAgent

第二步：

打开TBAgent目录，可以看到core.properties，修改name属性为TBAgent。

第三步：拷贝文件

　　将jtds-1.2.4.jar和solr-dataimporthandler-4.8.0.jar和solr-dataimporthandler-extras-4.8.0.jar拷贝到tomcat\webapps\solr\WEB-INF\lib。我的是（D:\Working\solr\tomcat\webapps\solr\WEB-INF\lib）

第四步：配置solrconfig.xml文件配置

　　在solr对应的core的配置文件中。我以我自建的TBAgent为例（D:\Working\solr\tomcat-solr\TBAgent\conf目录），打开solrconfig.xml文件。新增如下节点：

<requestHandler name="/dataimport" class="org.apache.solr.handler.dataimport.DataImportHandler">         
    <lst name="defaults">              
        <str name="config">data-config.xml</str>        
    </lst>  
</requestHandler>

第五步：同目录下建立data-config.xml文件用于导入语句。

以我的为例，语句为：

<?xml version="1.0" encoding="UTF-8" ?>   
    <dataConfig>     
        <dataSource type="JdbcDataSource" driver="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://localhost/HouseDRV320130611;useLOBs=false" user="usrfordb" password="111111" />        <document>    
            <entity name="TBAgent"  query="select 
    upper(AgentID) as AgentID,
    TE.AgentNo as AgentNo,
    TE.AgentOrder as AgentOrder,
    TE.AgentName as AgentName,
    (select TitleName from TBTitle where TBTitle.TitleID=TE.TitleID) as TitleName,
    (select Dept5 from TBDept where TBDept.DeptID=TE.DeptID) as DeptID,
    (select DeptName from TBDept where TBDept.DeptID=(select Dept5 from TBDept where TBDept.DeptID=TE.DeptID)) as DeptName,
    (select RoleName from TBRole as TP where Tp.RoleID=TE.RoleID) as RoleName,
    TE.TradeType as TradeType,
    TE.TelephoneA as TelephoneA,
    TE.Sex as Sex,
    TE.AgentPhoto as AgentPhoto,
    case when TE.IsLock=1 then 1 else 0 end as IsLock,
    TE.EntryDate as EntryDate,
    TEA.QQ as QQ,
    TEA.Email as Email,
    TEA.MSN as MSN,
    TE.LastModifyDate as LastModifyDate,
    (select AreaID from TBArea where TBArea.AreaID=(select DistrictID from TBDept where TBDept.DeptID=(select Dept5 from TBDept as CC where CC.DeptID=TE.DeptID))) as DistrictID,
    (select AreaName from TBArea where TBArea.AreaID=(select DistrictID from TBDept where TBDept.DeptID=(select Dept5 from TBDept as CC where CC.DeptID=TE.DeptID)))  as DistrictName,
    (select AreaID from TBArea where TBArea.AreaID=(select AreaID from TBDept where TBDept.DeptID=(select Dept5 from TBDept as CC where CC.DeptID=TE.DeptID)))  as AreaID,
    (select AreaName from TBArea where TBArea.AreaID=(select AreaID from TBDept where TBDept.DeptID=(select Dept5 from TBDept as CC where CC.DeptID=TE.DeptID)))  as AreaName,
    (select COUNT(*) from TBLookCustomer as TLC where TLC.AgentID=TE.AgentID and TLC.LookDate>=DATEADD(DAY,-30,GETDATE())) as LookCount,
    TE.PersonalIntro as PersonalIntro
from TBAgent as TE
left join TBAgentAdd as TEA on TEA.AgentAddID=TE.AgentAddID
where TE.AgentStatus=0 and TE.ShowInAddress=1"> 
                <field column="AgentID" name="AgentID" /> 
                <field column="AgentNo" name="AgentNo" />  
                <field column="AgentOrder" name="AgentOrder" /> 
                 <field column="AgentName" name="AgentName" />  
                <field column="TitleName" name="TitleName" />   
                <field column="DeptName" name="DeptName" /> 
                <field column="DeptID" name="DeptID" />   
                <field column="RoleName" name="RoleName" />   
                <field column="TradeType" name="TradeType" />   
                <field column="TelephoneA" name="TelephoneA" />   
                <field column="Sex" name="Sex" />   
                <field column="AgentPhoto" name="AgentPhoto" />   
                <field column="IsLock" name="IsLock" />   
                <field column="EntryDate" name="EntryDate" />   
                <field column="QQ" name="QQ" />   
                <field column="Email" name="Email" />   
                <field column="MSN" name="MSN" /> 
                <field column="LastModifyDate" name="LastModifyDate" />
                <field column="DistrictID" name="DistrictID" /> 
                <field column="DistrictName" name="DistrictName" /> 
                <field column="AreaID" name="AreaID" /> 
                <field column="AreaName" name="AreaName" /> 
                <field column="LookCount" name="LookCount" /> 
                <field column="PersonalIntro" name="PersonalIntro" />
            </entity>    
        </document>    
    </dataConfig>

我只写了query语句。引用风过无痕521的文章：

<!--query:查询数据库表符合记录数据-->
<!--deltaQuery:查询出需要增量索引的数据，所有经过修改的记录的Id,可能是修改操作、添加操作、删除操作产生的(此查询只对增量导入起作用，而且只能返回Id值) -->
<!--deletedPkQuery:查询出需要删除的数据记录主键Id，solr通过它来删除索引里面对应的数据(此查询只对增量导入起作用，而且只能返回ID值)-->
<!--deltaImportQuery:次查询是获取以上两步的Id，然后把其全部数据获取，根据获取的数据,对索引库进行更新操作，可能是删除，添加，修改(此查询只对增量导入起作用，可以返回多个字段的值,一般情况下，都是返回所有字段的列)-->
<!--transformer:格式转化-->

以及引用风过无痕521的例子。

<?xml version="1.0" encoding="UTF-8" ?>
<dataConfig>
  <dataSource driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://localhost:1433;DatabaseName=SolrDemoDB;username=sa;password=123456" user="sa" password="123456"  batchSize="100"/>
  <document>
    <entity
      name="Article"
      pk="ArticleId"
      query="select * from Article where IsDelete=0"
      deltaQuery="select ArticleId from Article where CreateDate > '${dataimporter.last_index_time}'"
      deletedPkQuery="select ArticleId from Article where IsDelete=1"
      deltaImportQuery="select * from Article where ArticleId='${dataimporter.delta.ArticleId}'"
      transformer="ClobTransformer,HTMLStripTransformer,DateFormatTransformer" >
      <field column="ArticleId" name="ArticleId" />
      <field column="Title" name="Title" />
      <field column="CreateDate" name="CreateDate" dateTimeFormat="yyyy-MM-dd" />
      <field column="IsDelete" name="IsDelete" />
      <field column="Content" name="Content" />
      <field column="TypeId" name="TypeId" />
      <entity name="ArticleType" query="select * from ArticleType where TypeId=${Article.TypeId}">
        <field column="ArticleTypeName" name="ArticleTypeName" />
      </entity>
    </entity>
  </document>
</dataConfig>

上面是的一对一的例子，还有一种一对多的方法。也记录下来。

<?xml version="1.0" encoding="UTF-8" ?>   
    <dataConfig>     
        <dataSource type="JdbcDataSource" driver="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://localhost/HouseDRV320130611;useLOBs=false" user="usrfordb" password="111111" />        <document>    
            <entity name="TBCommunity"  query="select 
    TE.CommID as CommID,
    TD.AreaID as DistrictID,
    TD.AreaName as DistrictName,
    TA.AreaID as AreaID,
    TA.AreaName,
    TE.CommCode as CommCode,
    TE.CommName as CommName,
    TE.CommAlias as CommAlias,
    TE.CommQuanPin as CommQuanPin,
    TE.Address as Address,
    TE.ZipCode as ZipCode,
    TE.QueryCode as QueryCode,
    TE.BaiduLongitude as BaiduLongitude,
    TE.BaiduLatitude as BaiduLatitude,
    TE.SosoLongitude as SosoLongitude,
    TE.SosoLatitude as SosoLatitude,
    TE.SosoPano as SosoPano,
    TR1.ItemValue as RefPropertyTypeName,
    TR2.ItemValue as RefBuildingStructureName,
    TR3.ItemValue as RefBuildingTypeName,
    TEA.MgtCompany as MgtCompany,
    TEA.MgtTelephone as MgtTelephone,
    TEA.MgtPrice as MgtPrice,
    TEA.MgtAddress as MgtAddress,
    TEA.MgtInfo as MgtInfo,
    TEA.MgtSecurity as MgtSecurity,
    TEA.BuildSquare as BuildSquare,
    TEA.TotalSquare as TotalSquare,
    TEA.DevCompany as DevCompany,
    TEA.FinishYear as FinishYear,
    TEA.TotalRoom as TotalRoom,
    TEA.PlotRatio as PlotRatio,
    TEA.AffoRatio as AffoRatio,
    TEA.CommIntro as CommIntro,
    TEA.ParkSpace as ParkSpace,
    TEA.ParkRentPrice as ParkRentPrice,
    TEA.ParkSalePrice as ParkSalePrice,
    TEA.CommEntrance as CommEntrance,
    TEA.SupplyWater as SupplyWater,
    TEA.SupplyHeating as SupplyHeating,
    TEA.SupplyElectricity as SupplyElectricity,
    TEA.SupplyGas as SupplyGas,
    TEA.SupplyCommunication as SupplyCommunication,
    TEA.SupplyElevator as SupplyElevator,
    TEA.SupplyClean as SupplyClean,
    TE.LastModifyDate as LastModifyDate,
    (select SUM(LookCount) from TBHouse where TBHouse.CommID=TE.CommID) as LookCount
from TBCommunity as TE
left join TBCommunityAdd as TEA ON TEA.CommAddID=TE.CommAddID
left join TBArea as TA on TE.AreaID=TA.AreaID
left join TBArea as TD ON TD.AreaID=TA.ParentID
left join TBReference as TR1 ON TR1.RefID=TE.RefPropertyType
left join TBReference as TR2 ON TR2.RefID=TE.RefBuildingStructure
left join TBReference as TR3 ON TR3.RefID=TE.RefBuildingType
where TE.IsDeleted=0"> 
                <field column="CommID" name="CommID" /> 
                <field column="DistrictID" name="DistrictID" />
                <field column="DistrictName" name="DistrictName" />  
                <field column="AreaID" name="AreaID" />
                <field column="AreaName" name="AreaName" /> 
                 <field column="CommCode" name="CommCode" />  
                <field column="CommName" name="CommName" />   
                <field column="CommAlias" name="CommAlias" />   
                <field column="CommQuanPin" name="CommQuanPin" />   
                <field column="Address" name="Address" />   
                <field column="ZipCode" name="ZipCode" />   
                <field column="QueryCode" name="QueryCode" />   
                <field column="BaiduLongitude" name="BaiduLongitude" />   
                <field column="BaiduLatitude" name="BaiduLatitude" />   
                <field column="SosoLongitude" name="SosoLongitude" />   
                <field column="SosoLatitude" name="SosoLatitude" />   
                <field column="SosoPano" name="SosoPano" />   
                <field column="RefPropertyTypeName" name="RefPropertyTypeName" /> 
                <field column="RefBuildingStructureName" name="RefBuildingStructureName" />  
                <field column="RefBuildingTypeName" name="RefBuildingTypeName" /> 
                <field column="MgtCompany" name="MgtCompany" /> 
                <field column="MgtTelephone" name="MgtTelephone" /> 
                <field column="MgtAddress" name="MgtAddress" /> 
                <field column="MgtPrice" name="MgtPrice" /> 
                <field column="MgtInfo" name="MgtInfo" /> 
                <field column="MgtSecurity" name="MgtSecurity" /> 
                <field column="BuildSquare" name="BuildSquare" /> 
                <field column="TotalSquare" name="TotalSquare" /> 
                <field column="DevCompany" name="DevCompany" /> 
                <field column="FinishYear" name="FinishYear" /> 
                <field column="TotalRoom" name="TotalRoom" /> 
                <field column="PlotRatio" name="PlotRatio" /> 
                <field column="AffoRatio" name="AffoRatio" /> 
                <field column="CommIntro" name="CommIntro" /> 
                <field column="ParkSpace" name="ParkSpace" /> 
                <field column="ParkRentPrice" name="ParkRentPrice" /> 
                <field column="ParkSalePrice" name="ParkSalePrice" /> 
                <field column="CommEntrance" name="CommEntrance" /> 
                <field column="SupplyWater" name="SupplyWater" /> 
                <field column="SupplyHeating" name="SupplyHeating" /> 
                <field column="SupplyElectricity" name="SupplyElectricity" /> 
                <field column="SupplyGas" name="SupplyGas" /> 
                <field column="SupplyCommunication" name="SupplyCommunication" /> 
                <field column="SupplyElevator" name="SupplyElevator" /> 
                <field column="SupplyClean" name="SupplyClean" /> 
                <field column="LastModifyDate" name="LastModifyDate" />
                <field column="LookCount" name="LookCount" />
                <entity name="TBCommunityPhoto" 
                   query="select
    TP.PhotoType as HPPhotoType,
    TP.PhotoPathBig as HPPhotoPathBig,
    TP.PhotoPathList as HPPhotoPathList,
    TP.PhotoPathSmall as HPPhotoPathSmall
from TBCommunityPhoto as TP where TP.CommID='${TBCommunity.CommID}'">
                        <field column="HPPhotoType" name="HPPhotoType" /> 
                        <field column="HPPhotoPathBig" name="HPPhotoPathBig" /> 
                        <field column="HPPhotoPathList" name="HPPhotoPathList" /> 
                        <field column="HPPhotoPathSmall" name="HPPhotoPathSmall" /> 
                </entity>
                <entity name="TBHouseSellCount" 
                   query="select count(*) as SellCount
from TBHouse as HP
where HP.Status=0 and HP.HouseType=1 and HP.CommID='${TBCommunity.CommID}' and HP.IsDeleted=0 and HP.TotalPrice>0">
                        <field column="SellCount" name="SellCount" /> 
                </entity>
                <entity name="TBHouseRentCount" 
                   query="select count(*) as RentCount
from TBHouse as HP
where HP.Status=0 and HP.HouseType=2 and HP.CommID='${TBCommunity.CommID}' and HP.IsDeleted=0 and HP.TotalPrice>0">
                        <field column="RentCount" name="RentCount" />
                </entity>
            </entity>    
        </document>    
    </dataConfig>

第六步：配置字段属性文件schema.xml文件。

以我的为例。一对一的字段属性文件。

<field name="AgentID" type="int" indexed="true" stored="true" required="true" multiValued="false" /> 
   <field name="AgentNo" type="string" indexed="true" stored="true"/>
   <field name="AgentOrder" type="int" indexed="false" stored="true"/>
   <field name="AgentName" type="string" indexed="true" stored="true"/>
   <field name="TitleName" type="string" indexed="true" stored="true"/>
   <field name="DeptID" type="int" indexed="true" stored="true"/>
   <field name="DeptName" type="string" indexed="true" stored="true"/>
   <field name="RoleName" type="string" indexed="true" stored="true"/>
   <field name="TradeType" type="int" indexed="true" stored="true"/>
   <field name="TelephoneA"  type="string" indexed="true" stored="true"/>
   <field name="Sex" type="string" indexed="true" stored="true" />
   <field name="AgentPhoto" type="string" indexed="true" stored="true" />
   <field name="IsLock" type="int" indexed="true" stored="true"/>
   <field name="EntryDate" type="date" indexed="true" stored="true"/>
   <field name="QQ" type="string" indexed="true" stored="true"/>
   <field name="Email" type="string" indexed="false" stored="true"/>
   <field name="MSN" type="string" indexed="true" stored="true"/>
   <field name="LastModifyDate" type="date" indexed="true" stored="true"/>
   <field name="DistrictID" type="int" indexed="true" stored="true"/>
   <field name="DistrictName"  type="string" indexed="true" stored="true"/>
   <field name="AreaID" type="int" indexed="true" stored="true"/>
   <field name="AreaName"  type="string" indexed="true" stored="true"/>
   <field name="LookCount" type="int" indexed="true" stored="true"/>
   <field name="PersonalIntro"  type="string" indexed="true" stored="true"/>
   <field name="text" type="text_general" stored="false" indexed="true"  multiValued="true"/>
   <defaultSearchField>text</defaultSearchField>
   
   <uniqueKey>AgentID</uniqueKey>

一对多的文件属性。

<field name="CommID" type="int" indexed="true" stored="true" required="true" multiValued="false" /> 
   <field name="DistrictID" type="int" indexed="true" stored="true"/>
   <field name="DistrictName" type="string" indexed="true" stored="true"/>
   <field name="AreaID" type="int" indexed="true" stored="true"/>
   <field name="AreaName" type="string" indexed="true" stored="true"/>
   <field name="CommCode" type="string" indexed="true" stored="true"/>
   <field name="CommName" type="string" indexed="true" stored="true"/>
   <field name="CommAlias" type="string" indexed="true" stored="true"/>
   <field name="CommQuanPin" type="string" indexed="true" stored="true"/>
   <field name="Address" type="string" indexed="true" stored="true"/>
   <field name="ZipCode"  type="string" indexed="true" stored="true"/>
   <field name="QueryCode" type="string" indexed="true" stored="true" />
   <field name="BaiduLongitude" type="string" indexed="true" stored="true" />
   <field name="BaiduLatitude" type="string" indexed="true" stored="true"/>
   <field name="SosoLongitude" type="string" indexed="true" stored="true"/>
   <field name="SosoLatitude" type="string" indexed="false" stored="true"/>
   <field name="SosoPano" type="string" indexed="false" stored="true"/>
   <field name="RefPropertyTypeName" type="string" indexed="true" stored="true"/>
   <field name="RefBuildingStructureName" type="string" indexed="false" stored="true"/>
   <field name="RefBuildingTypeName" type="string" indexed="true" stored="true"/>
   <field name="MgtCompany" type="string" indexed="true" stored="true"/>
   <field name="MgtTelephone" type="string" indexed="true" stored="true"/>
   <field name="MgtAddress" type="string" indexed="true" stored="true"/>
   <field name="MgtPrice" type="string" indexed="true" stored="true"/>
   <field name="MgtInfo" type="string" indexed="true" stored="true"/>
   <field name="MgtSecurity" type="string" indexed="true" stored="true"/>
   <field name="BuildSquare" type="string" indexed="true" stored="true"/>
   <field name="TotalSquare" type="string" indexed="true" stored="true"/>
   <field name="DevCompany" type="string" indexed="true" stored="true"/>
   <field name="FinishYear" type="string" indexed="true" stored="true"/>
   <field name="TotalRoom" type="string" indexed="true" stored="true"/>
   <field name="PlotRatio" type="string" indexed="true" stored="true"/>
   <field name="AffoRatio" type="string" indexed="true" stored="true"/>
   <field name="CommIntro" type="string" indexed="true" stored="true"/>
   <field name="ParkSpace" type="string" indexed="true" stored="true"/>
   <field name="ParkRentPrice" type="string" indexed="true" stored="true"/>
   <field name="ParkSalePrice" type="string" indexed="true" stored="true"/>
   <field name="CommEntrance" type="string" indexed="true" stored="true"/>
   <field name="SupplyWater" type="string" indexed="true" stored="true"/>
   <field name="SupplyHeating" type="string" indexed="true" stored="true"/>
   <field name="SupplyElectricity" type="string" indexed="true" stored="true"/>
   <field name="SupplyGas" type="string" indexed="true" stored="true"/>
   <field name="SupplyCommunication" type="string" indexed="true" stored="true"/>
   <field name="SupplyElevator" type="string" indexed="true" stored="true"/>
   <field name="SupplyClean" type="string" indexed="true" stored="true"/>
   <field name="LastModifyDate" type="date" indexed="true" stored="true" />
   <field name="LookCount" type="int" indexed="true" stored="true" />
   <field name="text" type="text_general" stored="false" indexed="true"  multiValued="true"/>
   <defaultSearchField>text</defaultSearchField>
   
   <field name="HPPhotoType" type="string" indexed="true" stored="true" multiValued="true"/>
   <field name="HPPhotoPathBig" type="string" indexed="true" stored="true" multiValued="true"/>
   <field name="HPPhotoPathList" type="string" indexed="true" stored="true" multiValued="true"/>
   <field name="HPPhotoPathSmall" type="string" indexed="true" stored="true" multiValued="true"/>
   <field name="SellCount" type="int" indexed="true" stored="true"/>
   <field name="RentCount" type="int" indexed="true" stored="true"/>
   
   <uniqueKey>CommID</uniqueKey>

至此配置完成。打开http://localhost:8080/solr/就可以看见solr的管理界面了。并且可以看见我们新建的TBAgent和TBCommunity了。