My Profile
Sign Out
Tickets
Knowledge Base
Login
Articles in this section
Category / Section
  2. React
  3. Query Builder

How to Import and Export SQL Queries with React Query Builder?

14 mins read

The React Query Builder is a user-friendly UI for filtering large data sets, enabling condition creation and grouping using AND/OR logic, and supports data visualization controls.

This article outlines the steps to import and export SQL queries with React Query Builder

Setting Up the Query Builder

Begin by integrating the Syncfusion React Query Builder into your application. Ensure that you have the necessary packages installed and your component is properly configured.

Importing SQL Queries

Importing from SQL improves workflow efficiency and data accuracy by integrating predefined conditions or data from a SQL database into the Query Builder. It supports various types, including Inline SQL, Parameter SQL, and Named Parameter SQL.

To import SQL queries into the React Query Builder, follow the below steps:

  1. Prepare the SQL Query: Ensure that the SQL query you want to import is in a valid format. The query should be a string that represents a valid SQL statement.

  2. Render the Query Builder: After importing the SQL query, render the Query Builder component in your application. The imported query will be displayed in the builder interface.

  3. Handle Errors: Implement error handling to manage any issues that may arise during the import process, such as invalid SQL syntax.

Importing from Inline SQL Query

Importing from Inline SQL integrates SQL queries into the Query Builder, allowing users to input statements for analysis and manipulation, and setting conditions using the setRulesFromSL method.

Here’s a code snippet to import an inline SQL query:

import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { QueryBuilderComponent } from '@syncfusion/ej2-react-querybuilder';
import * as React from 'react';
import * as ReactDom from 'react-dom';
// @ts-ignore
import { hardwareData } from '../datasource.ts';
function App() {
   let qryBldrObj;
   let columnData = [
       { field: 'TaskID', label: 'Task ID', type: 'number' },
       { field: 'Name', label: 'Name', type: 'string' },
       { field: 'Category', label: 'Category', type: 'string' },
       { field: 'SerialNo', label: 'Serial No', type: 'string' },
       { field: 'InvoiceNo', label: 'Invoice No', type: 'string' },
       { field: 'Status', label: 'Status', type: 'string' }
   ];
   function importRule() {
       qryBldrObj.setRulesFromSql("TaskID = 1 and Status LIKE ('Assigned%')");
   }
   return (<div>
           <QueryBuilderComponent width='100%' dataSource={hardwareData} ref={(scope) => { qryBldrObj = scope; }} columns={columnData}/>
           <div className="e-qb-button">
               <ButtonComponent id="importrules" cssClass='e-primary' content='set Rules' onClick={importRule}/>
           </div>
       </div>);
}
export default App;
ReactDom.render(<App />, document.getElementById('querybuilder'));

Importing from Parameter SQL Query

Importing from Parameter SQL integrates SQL queries with parameters into Query Builder, allowing dynamic input and customizable querying. Conditions can be set using setParameterizedSql method.

Here’s a code snippet to importing from parameter SQL Query

import { QueryBuilderComponent } from '@syncfusion/ej2-react-querybuilder';
import * as React from 'react';
import * as ReactDom from 'react-dom';
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
QueryBuilderComponent.Inject(QueryLibrary);
// @ts-ignore
function App() {
   let qryBldrObj;
   let columnData = [
       { field: 'TaskID', label: 'Task ID', type: 'number' },
       { field: 'Name', label: 'Name', type: 'string' },
       { field: 'Category', label: 'Category', type: 'string' },
       { field: 'SerialNo', label: 'Serial No', type: 'string' },
       { field: 'InvoiceNo', label: 'Invoice No', type: 'string' },
       { field: 'Status', label: 'Status', type: 'string' }
   ];
   let hardwareData  = [{
       'TaskID': 1,
       'Name': 'Lenovo Yoga',
       'Category': 'Laptop',
       'SerialNo': 'CB27932009',
       'InvoiceNo': 'INV-2878',
       'Status': 'Assigned'
   },
   {
       'TaskID': 2,
       'Name': 'Acer Aspire',
       'Category': 'Others',
       'SerialNo': 'CB35728290',
       'InvoiceNo': 'INV-3456',
       'Status': 'In-repair'
   },
   {
       'TaskID': 3,
       'Name': 'Apple MacBook',
       'Category': 'Laptop',
       'SerialNo': 'CB35628728',
       'InvoiceNo': 'INV-2763',
       'Status': 'In-repair'
   }];
   
   function importSql() {
       qryBldrObj.setParameterizedSql({ sql: '(Category IN (?,?) OR TaskID IN (?,?))', params:  ['Laptop', 'Others', 1, 2] });
   }
   return (<div>
       <QueryBuilderComponent width='100%' dataSource={hardwareData} columns={columnData} ref={(scope) => { qryBldrObj = scope; }}/>
       <div className="e-qb-button">
           <ButtonComponent id="importSql" cssClass='e-primary' content='Set Parameter SQL Rules' onClick = {importSql}/>
       </div>
   </div>);
}
export default App;
ReactDom.render(<App />, document.getElementById('querybuilder'));

Importing from Named Parameter SQL Query

Importing from Named Parameter SQL integrates SQL queries with named parameters into Query Builder, allowing dynamic parameter assignment and enabling condition setting within the application’s query environment. Conditions can be set using setParameterizedNamedSql method.

Here’s a code snippet to importing from named parameter SQL Query

import { QueryBuilderComponent, QueryLibrary } from '@syncfusion/ej2-react-querybuilder';
import * as React from 'react';
import * as ReactDom from 'react-dom';
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
QueryBuilderComponent.Inject(QueryLibrary);
// @ts-ignore
function App() {
   let qryBldrObj;
   let columnData = [
       { field: 'TaskID', label: 'Task ID', type: 'number' },
       { field: 'Name', label: 'Name', type: 'string' },
       { field: 'Category', label: 'Category', type: 'string' },
       { field: 'SerialNo', label: 'Serial No', type: 'string' },
       { field: 'InvoiceNo', label: 'Invoice No', type: 'string' },
       { field: 'Status', label: 'Status', type: 'string' }
   ];
   let hardwareData  = [{
       'TaskID': 1,
       'Name': 'Lenovo Yoga',
       'Category': 'Laptop',
       'SerialNo': 'CB27932009',
       'InvoiceNo': 'INV-2878',
       'Status': 'Assigned'
   },
   {
       'TaskID': 2,
       'Name': 'Acer Aspire',
       'Category': 'Others',
       'SerialNo': 'CB35728290',
       'InvoiceNo': 'INV-3456',
       'Status': 'In-repair'
   },
   {
       'TaskID': 3,
       'Name': 'Apple MacBook',
       'Category': 'Laptop',
       'SerialNo': 'CB35628728',
       'InvoiceNo': 'INV-2763',
       'Status': 'In-repair'
   }];
   
   function importSql() {
       qryBldrObj.setParameterizedNamedSql({ sql: '(Category IN (:Category_1,:Category_2) OR TaskID IN (:TaskID_1,:TaskID_2))', params: {"Category_1": "Laptop", "Category_2": "Others", "TaskID_1": 1, "TaskID_2": 2} });
   }

   return (<div>
       <QueryBuilderComponent width='100%' dataSource={hardwareData} columns={columnData} ref={(scope) => { qryBldrObj = scope; }}/>
       <div className="e-qb-button">
           <ButtonComponent id="importSql" cssClass='e-primary' content='Set Named Parameter SQL Rules' onClick = {importSql}/>
       </div>
   </div>);
}
export default App;
ReactDom.render(<App />, document.getElementById('querybuilder'));

Exporting SQL Queries

Exporting to SQL converts Query Builder conditions into SQL queries, enabling users to generate SQL code for execution on a database or further analysis. It supports various types, including Inline SQL, Parameter SQL, and Named Parameter SQL.

Exporting SQL queries from the React Query Builder is straightforward. Here’s how to do it:

  1. Build Your Query: Use the Query Builder to create your SQL query through the user interface. This can involve adding conditions, selecting fields, and defining relationships.

  2. Display or Save the Query: You can display the exported SQL query in your application or save it to a file or database as needed.

  3. Consider Formatting: Depending on your requirements, you may want to format the exported SQL query for better readability or compatibility with other systems.

Exporting to Inline SQL Query

Exporting to Inline SQL Query embeds defined conditions into SQL statements, ensuring seamless integration into SQL syntax, facilitated by the getSqlFromRules method.

Here’s a code snippet to exporting to inline SQL Query

import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { DialogComponent } from '@syncfusion/ej2-react-popups';
import { QueryBuilderComponent } from '@syncfusion/ej2-react-querybuilder';
import * as React from 'react';
import * as ReactDom from 'react-dom';
// @ts-ignore
import { hardwareData } from '../datasource.ts';
function App() {
   let qryBldrObj;
   let dialogInstance;
   let animationSettings = { effect: 'Zoom', duration: 400, delay: 0 };
   let columnData = [
       { field: 'TaskID', label: 'Task ID', type: 'number' },
       { field: 'Name', label: 'Name', type: 'string' },
       { field: 'Category', label: 'Category', type: 'string' },
       { field: 'SerialNo', label: 'Serial No', type: 'string' },
       { field: 'InvoiceNo', label: 'Invoice No', type: 'string' },
       { field: 'Status', label: 'Status', type: 'string' }
   ];
   let importRules = {
       'condition': 'or',
       'rules': [{
               'field': 'Category',
               'label': 'Category',
               'operator': 'equal',
               'type': 'string',
               'value': 'Laptop'
           }]
   };
   function getSql() {
       dialogInstance.content = qryBldrObj.getSqlFromRules(qryBldrObj.getValidRules(qryBldrObj.rule));
       dialogInstance.show();
   }
   function getRule() {
       const validRule = qryBldrObj.getValidRules(qryBldrObj.rule);
       dialogInstance.content = '<pre>' + JSON.stringify(validRule, null, 4) + '</pre>';
       dialogInstance.show();
   }
   return (<div>
           <QueryBuilderComponent width='100%' dataSource={hardwareData} ref={(scope) => { qryBldrObj = scope; }} columns={columnData} rule={importRules}/>
           <div className="e-qb-button">
               <ButtonComponent cssClass='e-primary' content='Get Sql' onClick={getSql}/>
               <ButtonComponent cssClass='e-primary' content='Get Rule' onClick={getRule}/>
           </div>
           <DialogComponent id="defaultdialog" showCloseIcon={true} animationSettings={animationSettings} ref={dialog => dialogInstance = dialog} height='auto' header='Querybuilder' visible={false} width='50%'/>
       </div>);
}
export default App;
ReactDom.render(<App />, document.getElementById('querybuilder'));

Exporting to Parameter SQL Query

Exporting to Parameter SQL allows dynamic value assignment in SQL queries, enhancing flexibility and adaptability. This can be achieved using the getParameterizedSql method.

Here’s a code snippet to exporting to parameter SQL Query

import { QueryBuilderComponent } from '@syncfusion/ej2-react-querybuilder';
import * as React from 'react';
import * as ReactDom from 'react-dom';
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { AnimationSettingsModel, DialogComponent } from '@syncfusion/ej2-react-popups';
QueryBuilderComponent.Inject(QueryLibrary);
// @ts-ignore
function App() {
   let qryBldrObj;
   let dialog;
   let columnData = [
       { field: 'TaskID', label: 'Task ID', type: 'number' },
       { field: 'Name', label: 'Name', type: 'string' },
       { field: 'Category', label: 'Category', type: 'string' },
       { field: 'SerialNo', label: 'Serial No', type: 'string' },
       { field: 'InvoiceNo', label: 'Invoice No', type: 'string' },
       { field: 'Status', label: 'Status', type: 'string' }
   ];
   let hardwareData  = [{
       'TaskID': 1,
       'Name': 'Lenovo Yoga',
       'Category': 'Laptop',
       'SerialNo': 'CB27932009',
       'InvoiceNo': 'INV-2878',
       'Status': 'Assigned'
   },
   {
       'TaskID': 2,
       'Name': 'Acer Aspire',
       'Category': 'Others',
       'SerialNo': 'CB35728290',
       'InvoiceNo': 'INV-3456',
       'Status': 'In-repair'
   },
   {
       'TaskID': 3,
       'Name': 'Apple MacBook',
       'Category': 'Laptop',
       'SerialNo': 'CB35628728',
       'InvoiceNo': 'INV-2763',
       'Status': 'In-repair'
   }];

   let importRules = {
       'condition': 'or',
       'rules': [{
           'label': 'Category',
           'field': 'Category',
           'type': 'string',
           'operator': 'equal',
           'value': 'Laptop'
       }]
   };
   
   function getSql() {
       dialog.content = JSON.stringify(qryBldrObj.getParameterizedSql(qryBldrObj.getValidRules(qryBldrObj.rule)), null, 2);
       dialog.show();
   }

   function getRule() {
       let validRule = qryBldrObj.getValidRules(qryBldrObj.rule);
        dialog.content = '<pre>' + JSON.stringify(validRule, null, 4) + '</pre>';
        dialog.show();
   }
   return (<div>
       <QueryBuilderComponent width='100%' dataSource={hardwareData} columns={columnData} rule={importRules} ref={(scope) => { qryBldrObj = scope; }}/>
       <div className="e-qb-button">
           <ButtonComponent id="exportsql" cssClass='e-primary' content='Get Parameter sql' onClick = {getSql}/>
           <ButtonComponent id="exportrule" cssClass='e-primary' content='Get Rule' onClick = {getRule}/>
       </div>
       <DialogComponent id='dialog' width='50%' animationSettings={animationSettings} header={"Query Builder"} visible={false} closeOnEscape={false} showCloseIcon={true} ref={(scope) => { dialog = scope; }}></DialogComponent>
   </div>);
}
export default App;
ReactDom.render(<App />, document.getElementById('querybuilder'));

Exporting to Named Parameter SQL Query

Exporting to Named Parameter SQL integrates Query Builder conditions into SQL queries with named parameters, improving readability and flexibility. It simplifies maintenance, modification, and dynamic parameter assignment within the database. This can be accomplished using the method ggetParameterizedNamedSql for exporting to Named Parameter SQL query.

Here’s a code snippet to exporting to named parameter SQL Query

import { QueryBuilderComponent, QueryLibrary } from '@syncfusion/ej2-react-querybuilder';
import * as React from 'react';
import * as ReactDom from 'react-dom';
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
QueryBuilderComponent.Inject(QueryLibrary);
// @ts-ignore
function App() {
   let qryBldrObj;
   let dialog;
   let columnData = [
       { field: 'TaskID', label: 'Task ID', type: 'number' },
       { field: 'Name', label: 'Name', type: 'string' },
       { field: 'Category', label: 'Category', type: 'string' },
       { field: 'SerialNo', label: 'Serial No', type: 'string' },
       { field: 'InvoiceNo', label: 'Invoice No', type: 'string' },
       { field: 'Status', label: 'Status', type: 'string' }
   ];
   let hardwareData  = [{
       'TaskID': 1,
       'Name': 'Lenovo Yoga',
       'Category': 'Laptop',
       'SerialNo': 'CB27932009',
       'InvoiceNo': 'INV-2878',
       'Status': 'Assigned'
   },
   {
       'TaskID': 2,
       'Name': 'Acer Aspire',
       'Category': 'Others',
       'SerialNo': 'CB35728290',
       'InvoiceNo': 'INV-3456',
       'Status': 'In-repair'
   },
   {
       'TaskID': 3,
       'Name': 'Apple MacBook',
       'Category': 'Laptop',
       'SerialNo': 'CB35628728',
       'InvoiceNo': 'INV-2763',
       'Status': 'In-repair'
   }];

   let importRules = {
       'condition': 'or',
       'rules': [{
           'label': 'Category',
           'field': 'Category',
           'type': 'string',
           'operator': 'equal',
           'value': 'Laptop'
       }]
   };
   
   function getSql() {
       dialog.content = JSON.stringify(qryBldrObj.getParameterizedNamedSql(qryBldrObj.getValidRules(qryBldrObj.rule)), null, 2);
       dialog.show();
   }

   function getRule() {
       let validRule = qryBldrObj.getValidRules(qryBldrObj.rule);
       dialog.content = '<pre>' + JSON.stringify(validRule, null, 4) + '</pre>';
       dialog.show();
   }

   return (<div>
       <QueryBuilderComponent width='100%' dataSource={hardwareData} columns={columnData} rule={importRules} ref={(scope) => { qryBldrObj = scope; }}/>
       <div className="e-qb-button">
           <ButtonComponent id="exportmongo" cssClass='e-primary' content='Get Parameter Named sql' onClick = {getSql}/>
           <ButtonComponent id="exportrule" cssClass='e-primary' content='Get Rule' onClick = {getRule}/>
       </div>
       <DialogComponent id='dialog' width='50%' animationSettings={animationSettings} header={"Query Builder"} visible={false} closeOnEscape={false} showCloseIcon={true} ref={(scope) => { dialog = scope; }}></DialogComponent>
   </div>);
}
export default App;
ReactDom.render(<App />, document.getElementById('querybuilder'));

Conclusion

I hope you found it helpful to learn how to import and export SQL query with React Query Builder.

You can refer to our React Query Builder feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our React Query Builder example to understand how to create and manipulate data.

For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.

If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please  to leave a comment
Access denied
Access denied