Ready to get started?

Learn more about the CData JDBC Driver for QuickBooks or download a free trial:

Download Now

Import QuickBooks data to Apache Solr using Data Import Handler

Use CData JDBC Driver for QuickBooks in Data Impot Handler and set automated import of QuickBooks data to Apache Solr Enterprise Search platform.

Apache Solr https://lucene.apache.org/solr/ is a popular, blazing-fast, open source enterprise search platform build on Apache Lucene.

Apache Solr is equipped with Data Import Handler(DIH) that can import data from DBs, XML, CSV, JSON. With use of CData JDBC Driver for QuickBooks, you can easily import QuickBooks data to Apache Solr. in this article, we will show step-by-step how to use CData JDBC Driver in Apache Solr Data Import Handler and import QuickBooks data to be able to use in Enterprise Search.

Create Apache Solr Core and schema for QuickBooks

  • Run Apache Solr and create a Core.
  • > solr create -c CDataCore
  • Solr in this article funs as stand alone on the local enviroment and you can access the core at this URL:http://localhost:8983/solr/#/CDataCore/core-overview
  • Next step is to create a schema for the QuickBooks data to be imported. LastModifiedDate, if exist in QuickBooks, will be used for incremental update. If it does not exist, you cannot do the deltaquery in the later section.
  •             
                    
                    
                    
                    
                    
                    
                    
                                   
                
            
  • Install CData QuickBooks JDBC Driver. Locate the .jar file(cdata.quickbooks.jar and cdata.jdbc.quickbooks.lic) to the Solr directory.
    • CData JDBC: C:\Program Files\CData\CData JDBC Driver for QuickBooks 2019J\lib
    • Apache Solr: solr-8.5.2\server\lib

Now we are ready to use QuickBooks data in Solr.

Define import of QuickBooks to Apache Solr using Data Import Handler(DIH)

Let's start the setting in DIH.

  • First, modify the Config file of the created Core. You will need to add the .jar file reference and add the DIH RequestHander definition. DIH の設定ファイルは「solr-data-config.xml」としました。
  •                 
                        
    
                        
    
                        
                            
                            solr-data-config.xml
                            
                        
    
                    
                
  • Create solr-data-config.xml at the same level. In this article, we retrieve a table from QuickBooks. Driver Class and sample JDBC Connection strings are in the sample code below: 。
  •                 
                        
                        
                            
                             
                             
                                
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                                                   
                                
                            
                        
                    
                
  • In the query section, set the SQL query that select the data from QuickBooks. deltaQuery and deltaImportquery define the ID and the conditions when using incremental update from the second import of the same entity.
  • After all settings are done, restart Solr.
  • > solr stop -all > solr start

Run DataImport of QuickBooks data.

Execute DataImport from the URL below:
http://localhost:8983/solr/#/CDataCore/dataimport//dataimport

Execute full-import, choose the table from Entity, and Execute.

Check the result of the import from the Query.

Next, try the incremental update using Deltaquery. Modify some data in the original QuickBooks. Now choose Delta-Import this time from DataImport window and Execute.

Check the result of the incremental update.