上篇文章已经把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了。