Implementation Method and System for ES-JDBC to Connect SQL-DML

Abstract

The present invention relates to a method and system for implementing ES-JDBC to connect SQL-DML, which is characterized in that it includes: establishing files related to lexical analysis and syntax analysis, and generating corresponding lexical analysis classes and syntax analysis classes; parsing SQL statements according to the generated lexical analysis classes and syntax analysis classes to obtain a syntax tree; parsing the syntax tree and constructing entity classes required for the client class of the ES server; in the constructed entity classes, according to the SQL parsing result object queried by the ES server, calling different return results in the SQL operation method and different methods of the client class to complete the operation of ES data. The present invention can be widely applied to the field of application development technology.

Description

Technical Field

[0001] The present invention relates to a method and system for implementing ES-JDBC connection to SQL-DML, belonging to the technical field of application development. Background Art

[0002] ElasticSearch (abbreviated as ES, a search server based on Lucene) is a distributed, RESTful-style (a software architecture style) search and data analysis engine. Its built-in Restful-API interface is complex to use and has a relatively high learning cost. SQL (Structured Query Language), on the other hand, is a structured query language well-known to almost all programmers and is very convenient for operating and managing databases.

[0003] However, currently ElasticSearch can only implement Jdbc (Java Database Connectivity) to connect DQL (Data Query Language) for querying data, and has not implemented DML (Data Manipulation Language) for operating data. To insert, modify, or delete ElasticSearch data, methods such as Restful-API and official JAVA-API can be used, but they require a certain learning cost, increasing the difficulty of getting started and development for users familiar with SQL operations. Summary of the Invention

[0004] Aiming at the above problems, the purpose of the present invention is to provide a method and system for implementing ES-JDBC connection to SQL-DML that can reduce the difficulty of getting started and development.

[0005] To achieve the above purpose, the present invention adopts the following technical solutions: In the first aspect, a method for implementing ES-JDBC connection to SQL-DML is provided, including:

[0006] Establish files related to lexical analysis and syntax analysis, and generate corresponding lexical analysis classes and syntax analysis classes;

[0007] Parse the SQL statement according to the generated lexical analysis classes and syntax analysis classes to obtain a syntax tree;

[0008] Parse the syntax tree and construct entity classes required for the client class of the ES server;

[0009] In the constructed entity classes, according to the SQL parsing result object queried by the ES server, call different return results in the SQL operation method and different methods of the client class to complete the operation of ES data.

[0010] Further, the writing of files related to lexical analysis and syntax analysis and the generation of corresponding lexical analysis classes and syntax analysis classes include:

[0011] Write the.g4 files related to lexical analysis and syntax analysis based on the grammar rules of antlr4;

[0012] Generate the corresponding lexical analysis class and syntax analysis class according to the.g4 files related to lexical analysis and syntax analysis.

[0013] Furthermore, parse the SQL statement according to the generated lexical analysis class and syntax analysis class to obtain a syntax tree, including:

[0014] The driver management class loads the driver class;

[0015] Set the configuration information, and call the getConnection method in the driver management class, passing in the ES-URL and configuration information to obtain the connection class;

[0016] Construct the client class according to the called URL and configuration information;

[0017] Call the createStatement method in the connection class to obtain the first type for executing static SQL statements and returning the result object;

[0018] Concatenate the SQL statement in the first type and call the executeUpdate method to obtain the second type for SQL statement parsing;

[0019] Call the parsing method in the second type to obtain the third type for constructing the syntax tree, and based on the third type, parse the SQL statement to obtain the syntax tree.

[0020] Furthermore, parse the syntax tree and construct the entity classes required for the client class of the ES server, including:

[0021] Construct the lexical analysis class in the form of a byte stream for the SQL statement;

[0022] Construct the syntax analysis class according to the lexical analysis class;

[0023] Call the sql method in the syntax analysis class to parse the SQL statement according to the constructed lexical analysis class and syntax analysis class to obtain the SQL context;

[0024] Construct the entity classes required for the client class of the ES server according to the obtained SQL context.

[0025] Furthermore, the entity classes required for the client class of the ES server include the insert subclass, update subclass, and delete subclass.

[0026] Further, in the constructed entity class, according to the SQL parsing result object queried by the ES server, different return results in the SQL operation method are called and different methods of the client class are called to complete the operation of ES data, which further includes:

[0027] The method of the client class called has a return value, and the return value is returned to the first category.

[0028] Further, the different methods of the client class include insertion, update, and deletion.

[0029] In a second aspect, an implementation system for ES-JDBC connecting SQL-DML is provided, including:

[0030] A file establishment module for establishing files related to lexical analysis and syntax analysis and generating corresponding lexical analysis classes and syntax analysis classes;

[0031] An SQL statement parsing module for parsing an SQL statement according to the generated lexical analysis class and syntax analysis class to obtain a syntax tree;

[0032] An entity class construction module for parsing the syntax tree and constructing an entity class required for the client class of the ES server;

[0033] An ES data operation module for, in the constructed entity class, according to the SQL parsing result object queried by the ES server, calling different return results in the SQL operation method and different methods of the client class to complete the operation of ES data.

[0034] In a third aspect, a processing device is provided, including computer program instructions, where the computer program instructions, when executed by the processing device, are used to implement the steps corresponding to the above-mentioned implementation method of ES-JDBC connecting SQL-DML.

[0035] In a fourth aspect, a computer-readable storage medium is provided, characterized in that computer program instructions are stored on the computer-readable storage medium, where the computer program instructions, when executed by a processor, are used to implement the steps corresponding to the above-mentioned implementation method of ES-JDBC connecting SQL-DML.

[0036] Due to the above technical solutions adopted by the present invention, it has the following advantages: In the prior art, when it is necessary to insert, modify, and delete ES data, methods such as Restful-API and JAVA-API can be used, but there is a certain learning cost. However, by using the present invention, the driver program can be directly imported, and the data of the ES server can be inserted, modified, and deleted in the ordinary jdbc writing manner, which can improve the development efficiency and save time, and can be widely applied in the field of application development technology. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Figure 1 It is a schematic flowchart of a method provided by an embodiment of the present invention. Specific Embodiments

[0038] Hereinafter, exemplary embodiments of the present invention will be described in more detail with reference to the accompanying drawings. Although the exemplary embodiments of the present invention are shown in the drawings, it should be understood that the present invention can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided so that the present invention can be more thoroughly understood and the scope of the present invention can be fully conveyed to those skilled in the art.

[0039] It should be understood that the terms used herein are for the purpose of describing specific example embodiments only and are not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms "a", "an", and "the" as used herein may also include the plural forms. The terms "comprising", "including", "containing", and "having" are inclusive and thus specify the presence of the stated features, steps, operations, elements, and / or components, but do not preclude the presence or addition of one or more other features, steps, operations, elements, components, and / or combinations thereof. The method steps, processes, and operations described herein are not to be construed as necessarily requiring them to be performed in the particular order described or illustrated, unless the order of performance is explicitly stated. It should also be understood that additional or alternative steps may be used.

[0040] Term Explanation:

[0041] 1. ElasticSearch: Abbreviated as ES, it is a search server based on Lucene;

[0042] 2. JDBC: Java Database Connectivity, Java database connection;

[0043] 3. SQL: Structured Query Language, structured query language;

[0044] 4. DML: Data Manipulation Language, data manipulation language;

[0045] 5. DQL: Data Query Language, data query language;

[0046] 6. Restful: It is a design style and development method for web applications;

[0047] 7. API: Application Programming Interface, application programming interface;

[0048] 8. ANTLR4: A cross - language syntax parser that can be used to read, process, execute, or translate structured text or binary files and is widely used in building languages, tools, and frameworks;

[0049] 9. IDEA: An integrated environment for Java programming language development;

[0050] 10. URL: Uniform Resource Locator system.

[0051] 11..g4: The suffix of the file that the ANTLR4 parser needs to parse is.g4.

[0052] 12. Class.forName(): In the Java language, it initializes a class according to the class name.

[0053] 13. DriverManager: A Java driver management class that manages the basic services of a group of JDBC drivers. After a driver is registered, it will be saved in the registered list in DriverManager.

[0054] 14. getConnection: A method to establish a connection to a given database URL.

[0055] 15. ES - URL: The URL address for JDBC to connect to ElasticSearch.

[0056] 16. Connection class: Through this Java class, it can provide information such as the database's description of its tables, the SQL syntax it supports, its stored procedures, and connection capabilities.

[0057] 17. EsDmlDriver: A custom - loaded driver class that can implement the java.sql.Driver interface class; all JDBC drivers need to implement the java.sql.Driver interface.

[0058] 18. REST: A set of architectural constraints and principles that better utilize some of the guidelines and constraints in existing Web standards.

[0059] 19. RestHighLevelClient: A high - level REST client class provided by ElasticSearch officially, which encapsulates an instance of the low - level RestClient.

[0060] 20. executeUpdate: The executeUpdate method in the custom Java class ElasticStatement.

[0061] 21. ElasticsearchLexer: A Java class (mainly for parsing words in SQL). By using the ANTLR tool in IDEA to execute the generation of an ANTLR recognizer for the ElasticsearchLexer.g4 file, the ElasticsearchLexer class is obtained. The content in the g4 file is written using ANTLR4 grammar (mainly for parsing SQL lexical rules).

[0062] 22. ElasticsearchParser: A Java class (mainly for parsing SQL syntax). It is generated in the same way as ElasticsearchLexer, and the file it operates on is ElasticsearchParser.g4 (written using ANTLR4 grammar for parsing SQL syntax rules).

[0063] 23. QueryParser interface class: An interface class in Java that defines a parsing method (parse), which is implemented by other Java classes (such as parsing classes for insert, update, delete, etc.).

[0064] 24. ElasticStatement class, a custom Java class that implements the methods of the Statement interface class (an important interface for Java to execute database operations).

[0065] 25. Maven: A project management tool.

[0066] 26. es-sql-core: A module in IDEA. Below the project in IDEA are modules.

[0067] 27. es-sql-jdbc-dml: A module in IDEA. Below the project in IDEA are modules.

[0068] 28. pom.xml: POM is short for Project Object Model. It is a file in a Maven project, represented in XML, and named pom.xml.

[0069] 29. Properties: Properties inherits from Hashtable and represents a persistent set of properties. Each key and its corresponding value in the property list is a string.

[0070] The implementation method and system for ES-JDBC to connect SQL-DML provided by the embodiments of the present invention first write relevant.g4 files for lexical analysis and syntax analysis based on the antlr4 grammar, and generate corresponding lexical analysis classes and syntax analysis classes in Java. Secondly, write a JDBC driver program in Java, and then obtain the SQL string during execution, call the lexical analysis class and syntax analysis class to parse the SQL statement, and obtain the syntax analysis tree. Thirdly, use Java programming to parse the content of the syntax tree to construct the entity classes required by the ElasticSearchRestHighLevelClient. Finally, call the relevant API interfaces of the ElasticSearch client class to implement the insertion, modification, and deletion of ElasticSearch data. The embodiments of the present invention enhance and optimize the functions during the use of the search engine component, and can implement the SQL-DML data operation of ElasticSearch.

[0071] Embodiment 1

[0072] As Figure 1 shown, this embodiment provides an implementation method for ES-JDBC to connect SQL-DML, including the following steps:

[0073] 1) Based on the grammar rules of antlr4, write relevant.g4 files for lexical analysis and syntax analysis, and generate corresponding lexical analysis classes and syntax analysis classes. Specifically:

[0074] 1.1) Based on the grammar rules of antlr4, write relevant.g4 files for lexical analysis and syntax analysis.

[0075] 1.2) Use the idea programming language development tool to generate corresponding lexical analysis classes (ElasticsearchLexer) and syntax analysis classes (ElasticsearchParser) according to the relevant.g4 files for lexical analysis and syntax analysis.

[0076] 2) According to the generated lexical analysis classes and syntax analysis classes, parse the SQL statement to obtain the syntax tree. Specifically:

[0077] 2.1) Load the driver class (EsDmlDriver). The driver class can implement the java.sql.Driver interface class, that is, the interface that all JDBC driver programs need to implement.

[0078] 2.2) Set the configuration information (including the user name and password), and call the getConnection method in the DriverManager class, pass in the ES-URL and configuration information, and obtain the Connection class.

[0079] 2.3) Construct the client (RestHighLevelClient) class based on the called URL and configuration information.

[0080] Specifically, EsDmlDriver overrides the connect method in the driver class (Driver), creates a new connection class (ElasticConnection), which requires the ES connection URL, configuration information, and a client class object, to obtain the client class object for subsequent operation of ES data.

[0081] 2.4) Call the createStatement method in the connection class to obtain the Statement class (an object that executes static SQL statements and returns the results generated by them).

[0082] 2.5) Concatenate the SQL statement in the Statement class and call the executeUpdate method to obtain the ElasticSql2DslParser class (a custom Java class mainly for parsing SQL statements).

[0083] 2.6) Call the parse parsing method in the ElasticSql2DslParser class to construct the Walker class (a custom Java class mainly for constructing the Antlr syntax tree), and call the buildAntlrTree method in the Walker class to parse the SQL statement and obtain the syntax tree.

[0084] 3) Parse the syntax tree and construct the entity classes required for the client class of the ES server, specifically:

[0085] 3.1) Construct the lexical analysis class (ElasticsearchLexer) in the form of a byte stream for the SQL statement.

[0086] 3.2) Construct the syntax analysis class (ElasticsearchParser) based on the lexical analysis class (ElasticsearchLexer).

[0087] 3.3) Call the sql method in the syntax analysis class to parse the SQL statement according to the constructed lexical analysis class and syntax analysis class, and obtain the SQL context (SqlContext).

[0088] 3.4) Call the parse parsing method of the QueryParser interface class to construct the entity classes required for the client class of the ES server based on the obtained SQL context, including the insert subclass (IndexRequest), update subclass (UpdateRequest), and delete subclass (DeleteRequest).

[0089] 4) In the ElasticStatement class, according to the SQL parsing result object (ElasticSqlParseResult) queried from the ES server, different return results in the getSqlOperation method for obtaining SQL operations and different methods of the client class are called to complete the operation of ES data.

[0090] Specifically, the method of the client class called has a return value, and the return value is returned to the Statement class.

[0091] Specifically, different methods of the client class include index, update, and delete.

[0092] The following details the implementation method of ES-JDBC connecting SQL-DML of the present invention through specific embodiments:

[0093] 1. When a developer adopts the method of the present invention, the core jar packages (es-sql-core-1.0.0.jar, es-sql-jdbc-dml-1.0.0.jar) need to be imported into the maven project or imported into the maven repository. Note: When importing into the maven repository, the corresponding pom files (es-sql-core-1.0.0.xml, es-sql-jdbc-dml-1.0.0.xml) need to be imported, that is:

[0094] Prepare the development environment:

[0095] JDK 1.8.0_261, maven 3.5.4, and the idea programming development tool are required.

[0096] Build the project:

[0097] Using the idea programming development tool, create a new project as a Maven project, and add two modules, [es-sql-core] and [es-sql-jdbc-dml], to the project.

[0098] Write the pom.xml file under the [es-sql-core] module and introduce the dependencies of JUnit version 4.13.1, Spatial4j version 0.7, JTS Core version 1.16.1, Elasticsearch version 7.12.1, Jackson Databind version 2.10.1, Jackson Annotations version 2.10.1, Jackson Core version 2.10.1, Guava version 28.1-jre, Commons Collections4 version 4.3, Commons Lang3 version 3.8.1, Antlr4 Runtime version 4.7.2, maven-jar-plugin, maven-dependency-plugin, and maven-resources-plugin.

[0099] Write the pom.xml file under the [es-sql-core] module and introduce the es-sql-core module.

[0100] II. Start:

[0101] Core call logic:

[0102] Use the method Class.forName(com.seaboxdata.jdbc.EsDmlDriver) to load the EsDmlDriver driver class.

[0103] Create a new Properties object, set the username and password, and obtain the connection class through DriverManager.getConnection(ES URL address, Properties object).

[0104] The Connection class calls the createStatement() method to obtain the Statement class.

[0105] Concatenate the SQL statement.

[0106] The Statement class calls the executeUpdate() method and passes in the SQL statement.

[0107] Close the Statement and Connection resources to complete the DML operation of ES.

[0108] Example 2

[0109] This example provides an implementation system for ES-JDBC to connect SQL-DML, including:

[0110] A file creation module, which is used to create files related to lexical analysis and syntax analysis, and generate corresponding lexical analysis classes and syntax analysis classes.

[0111] An SQL statement parsing module, which is used to parse an SQL statement according to the generated lexical analysis class and syntax analysis class to obtain a syntax tree.

[0112] An entity class construction module, which is used to parse the syntax tree and construct entity classes required for the client class of the ES server.

[0113] An ES data operation module, which is used to query an SQL parsing result object according to the ES server in the constructed entity class, call different return results in the SQL operation method and different methods of the client class to complete the operation of ES data.

[0114] Embodiment 3

[0115] This embodiment provides a processing device corresponding to the implementation method of ES-JDBC connecting SQL-DML provided in Embodiment 1. The processing device can be a processing device for a client, such as a mobile phone, a laptop computer, a tablet computer, a desktop computer, etc., to execute the method of Embodiment 1.

[0116] The processing device includes a processor, a memory, a communication interface and a bus. The processor, the memory and the communication interface are connected through the bus to complete communication with each other. A computer program that can run on the processing device is stored in the memory. When the processing device runs the computer program, it executes the implementation method of ES-JDBC connecting SQL-DML provided in Embodiment 1.

[0117] In some implementations, the memory can be a high-speed random access memory (RAM: Random Access Memory), and may also include a non-volatile memory, such as at least one disk memory.

[0118] In other implementations, the processor can be various types of general-purpose processors such as a central processing unit (CPU) and a digital signal processor (DSP), which are not limited here.

[0119] Embodiment 4

[0120] This embodiment provides a computer program product corresponding to the implementation method of ES-JDBC connecting SQL-DML provided in Embodiment 1. The computer program product can include a computer-readable storage medium, on which computer-readable program instructions for executing the implementation method of ES-JDBC connecting SQL-DML described in Embodiment 1 are uploaded.

[0121] A computer-readable storage medium can be a tangible device that holds and stores instructions for use by an instruction execution device. A computer-readable storage medium can be, for example, but is not limited to, an electrical storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any combination of the foregoing.

[0122] The above embodiments are only used to illustrate the present invention. The structures, connection manners, manufacturing processes, etc. of the components can all be changed. Any equivalent transformation and improvement made on the basis of the technical solution of the present invention should not be excluded from the protection scope of the present invention.

Claims

1. An implementation method for ES-JDBC to connect SQL-DML, characterized in that, Including: Establish files related to lexical analysis and syntax analysis, and generate corresponding lexical analysis classes and syntax analysis classes; Parse the SQL statement according to the generated lexical analysis class and syntax analysis class to obtain a syntax tree; Parse the syntax tree and construct entity classes required for the client class of the ES server; In the constructed entity classes, query the SQL parsing result object according to the ES server, call different return results in the SQL operation method and different methods of the client class to complete the operation of ES data; The establishment of files related to lexical analysis and syntax analysis, and the generation of corresponding lexical analysis classes and syntax analysis classes, including: Based on the grammar rules of antlr4, write.g4 files related to lexical analysis and syntax analysis; Generate corresponding lexical analysis classes and syntax analysis classes according to the.g4 files related to lexical analysis and syntax analysis; The parsing of the SQL statement according to the generated lexical analysis class and syntax analysis class to obtain a syntax tree, including: The driver management class loads the driver class; Set the configuration information, and call the getConnection method in the driver management class, passing in the ES-URL and configuration information to obtain the connection class; Construct the client class according to the called URL and configuration information; Call the createStatement method in the connection class to obtain the first class for executing static SQL statements and returning the generated result object; Concatenate the SQL statement in the first class and call the executeUpdate method to obtain the second class for SQL statement parsing; Call the parsing method in the second class to obtain the third class for constructing the syntax tree, and based on the third class, parse the SQL statement to obtain the syntax tree; The parsing of the syntax tree and the construction of entity classes required for the client class of the ES server, including: Construct the lexical analysis class in the form of a byte stream for the SQL statement; Construct the syntax analysis class according to the lexical analysis class; According to the constructed lexical analysis class and syntax analysis class, call the sql method in the syntax analysis class to parse the SQL statement to obtain the SQL context; According to the obtained SQL context, construct entity classes required for the client class of the ES server, and the entity classes required for the client class of the ES server include insert subclasses, update subclasses and delete subclasses.

2. The implementation method for ES-JDBC to connect SQL-DML according to claim 1, characterized in that, The operation of ES data is completed by querying the SQL parsing result object according to the ES server in the constructed entity classes, calling different return results in the SQL operation method and different methods of the client class, and further including: The method of the called client class has a return value, and the return value is returned to the first class.

3. The implementation method for ES-JDBC to connect SQL-DML according to claim 1, characterized in that, The different methods of the client class include insert, update and delete.

4. An implementation system for ES-JDBC to connect SQL-DML, characterized in that, Including: A file establishment module for establishing files related to lexical analysis and syntax analysis, and generating corresponding lexical analysis classes and syntax analysis classes; An SQL statement parsing module for parsing the SQL statement according to the generated lexical analysis class and syntax analysis class to obtain a syntax tree; An entity class construction module for parsing the syntax tree and constructing entity classes required for the client class of the ES server; The ES data operation module is used to query the SQL parsing result object according to the ES server in the constructed entity class, call different return results in the obtained SQL operation method and different methods of the client class to complete the operation of ES data; Establish files related to lexical analysis and syntax analysis, and generate corresponding lexical analysis classes and syntax analysis classes, including: Write.g4 files related to lexical analysis and syntax analysis based on the grammar rules of antlr4; Generate corresponding lexical analysis classes and syntax analysis classes according to the.g4 files related to lexical analysis and syntax analysis; Parse the SQL statement according to the generated lexical analysis class and syntax analysis class to obtain a syntax tree, including: The driver management class loads the driver class; Set the configuration information, and call the getConnection method in the driver management class, passing in the ES-URL and configuration information to obtain the connection class; Construct a client class according to the called URL and configuration information; Call the createStatement method in the connection class to obtain a first class for executing static SQL statements and returning a result object; Concatenate the SQL statement in the first class and call the executeUpdate method to obtain a second class for SQL statement parsing; Call the parsing method in the second class to obtain a third class for constructing a syntax tree, and based on the third class, parse the SQL statement to obtain a syntax tree; Parse the syntax tree and construct the entity class required for the client class of the ES server, including: Construct a lexical analysis class in the form of a byte stream for the SQL statement; Construct a syntax analysis class according to the lexical analysis class; According to the constructed lexical analysis class and syntax analysis class, call the sql method in the syntax analysis class to parse the SQL statement to obtain the SQL context; Construct the entity class required for the client class of the ES server according to the obtained SQL context. The entity class required for the client class of the ES server includes an insert subclass, an update subclass, and a delete subclass.

5. A processing device, characterized in that, It includes computer program instructions. Among them, when the computer program instructions are executed by a processing device, they are used to implement the steps corresponding to the method for implementing ES-JDBC connection SQL-DML described in any one of claims 1-3.

6. A computer-readable storage medium, characterized in that, Computer program instructions are stored on the computer-readable storage medium. Among them, when the computer program instructions are executed by a processor, they are used to implement the steps corresponding to the method for implementing ES-JDBC connection SQL-DML described in any one of claims 1-3.

Images