Import MySQL database with Apache Solr - Full Import

Step 1: Create table structure in MySQL Database

---

MySQL Table Structure:
delimiter $$

CREATE TABLE `item` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '    ',
`NAME` varchar(450) DEFAULT NULL,
`MANU` varchar(450) DEFAULT NULL,
`WEIGHT` float DEFAULT NULL,
`PRICE` float DEFAULT NULL,
`POPULARITY` int(11) DEFAULT NULL,
`INSTOCK` tinyint(4) DEFAULT NULL,
`INCLUDES` varchar(450) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1$$

delimiter $$

CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(450) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1$$

delimiter $$

CREATE TABLE `feature` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(450) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_feature_1` (`item_id`),
CONSTRAINT `fk_feature_1` FOREIGN KEY (`item_id`) REFERENCES `item` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1$$


delimiter $$

CREATE TABLE `item_category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`CATEGORY_ID` int(11) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_item_category_1` (`CATEGORY_ID`),
KEY `fk_item_category_2` (`item_id`),
CONSTRAINT `fk_item_category_1` FOREIGN KEY (`CATEGORY_ID`) REFERENCES `category` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_item_category_2` FOREIGN KEY (`item_id`) REFERENCES `item` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1$$

Sample Data in MySQL:

insert into item values(id,'item 1','item 1 manu','1.2','100.23','1',1,'includes item 1');
insert into feature values(id,'feature item 1',1);
insert into category values(id,'music');
insert into item_category values(id,1,1);

Step 2: Register the Handler in solrconfig.xml

---

Path: "mySolr/solr/example/solr/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>

Step 3: Create data-config.xml and add the following content

---

Path: "mySolr/solr/example/solr/conf/"

<dataConfig>
    <dataSource driver="com.mysql.jdbc.Driver" type="JdbcDataSource"
       url="jdbc:mysql://10.2.5.130:3306/wekho" user="root" password="" />
    <document name="products">
       <entity name="item" query="select * from item">
           <field column="ID" name="id" />
           <field column="NAME" name="name" />
           <field column="MANU" name="manu" />
           <field column="WEIGHT" name="weight" />
           <field column="PRICE" name="price" />
           <field column="POPULARITY" name="popularity" />
           <field column="INSTOCK" name="inStock" />
           <field column="INCLUDES" name="includes" />

           <entity name="feature" query="select description from feature where item_id='${item.ID}'">
               <field name="features" column="description" />
           </entity>
           <entity name="item_category" query="select CATEGORY_ID from item_category where item_id='${item.ID}'">
               <entity name="category" query="select description from category where id = '${item_category.CATEGORY_ID}'">
                   <field column="description" name="cat" />
               </entity>
           </entity>
       </entity>
    </document>
</dataConfig>

Step 4: Add the required JDBC Connector jar file

---

Path: "mySolr/solr/example/lib/"

In our case we used MySQL so we added : mysql-connector-java-5.1.20.jar

Step 5: Run Commands

---

Path: "mySolr/solr/example/"

• Run the Apache Solr server from example directory:

    java -jar start.jar

• Loading data:
• Using URL :

http://localhost:8983/solr/dataimport?command=full-import

OR

• DIH Admin Console :

http://localhost:8983/solr/admin/dataimport.jsp?handler=/dataimport

• To check the data added in Solr:

http://localhost:8983/solr/select/?q=*%3A*&version=2.2&start=0&rows=10&indent=on