Method and apparatus for dynamically executing database script on basis of nashorn

Abstract

The present invention belongs to the technical field of database scripts, and specifically relates to a method and apparatus for dynamically executing a database script on the basis of Nashorn. In the present invention, by means of introducing an engine Nashorn, a custom code framework is achieved by means of combining a custom-developed engine SQLBuilder, and the framework can support the interaction between a form and a database, and can also be applied to the data interaction of a process node, and data interaction, thereby implementing the function of dynamic interaction with multi-party data; by means of the engine SQLBuilder, a user can autonomously select SQL keywords and required parameters in a visualization mode during custom code editing, so as to form executable SQL codes, such that interaction with the database is realized, the function of the custom code framework is expanded, and the workload of the user is reduced; and by means of sharding technology, the concurrency issues caused by the execution of a large number of SQL scripts in the custom codes are solved.

Description

A method and device for dynamically executing database scripts based on Nashorn Technical Field

[0001] The present invention belongs to the technical field of database scripts, and in particular relates to a method and device for dynamically executing database scripts based on Nashorn. Background Art

[0002] Nashorn is a lightweight, high-performance JavaScript runtime environment based on the JVM. Nashorn fully supports the ECMAScript 5.1 specification and several extensions. It compiles JavaScript into Java bytecode using new language features based on JSR 292, including invokedynamic introduced in JDK 7.

[0003] In the low-code platform based on the Activiti process engine, due to the diversity of user needs, the combination of existing controls and process nodes cannot meet user needs. Therefore, it is often necessary to provide custom code functions so that users can implement custom business logic by writing scripts, interact with the database, and operate existing form data.

[0004] The current custom code function is limited by the scripting language execution engine. Generally speaking, it only supports the execution of a single scripting language and cannot integrate other languages ​​into the custom code writing. This makes the custom code function very limited. Generally, the execution of SQL statements is pre-written in the code and executed through compilation. It cannot be dynamically executed in the custom code written by the user. Summary of the Invention

[0005] The purpose of the present invention is to provide a method for dynamically executing database scripts based on Nashorn, which can support the interaction between forms and databases, and can also be applied to data interaction between process nodes, as well as data interaction between this platform and third-party external systems, realizing the dynamic interaction function with multi-party data.

[0006] The technical solutions adopted by the present invention are as follows:

[0007] A method for dynamically executing database scripts based on Nashorn, comprising:

[0008] Get parameter information in the JavaScript script engine;

[0009] Get basic SQL keywords and combine them with parameter information to form SQL statements;

[0010] Get the shard key, concatenate the shard key with the SQL statement, and generate an executable SQL statement;

[0011] Get the results of the executable SQL statements executed by the sharding middleware, and return the results to the JavaScript script engine for further execution.

[0012] In a preferred solution, the step of obtaining parameter information in the JavaScript script engine includes:

[0013] Get the JavaScript script;

[0014] Get the parameter getter and connect the parameter getter to the database;

[0015] Create SQL queries and connect to the database to execute SQL queries;

[0016] Get the query result set;

[0017] The parameter getter traverses the query result set and extracts parameter information from the JavaScript script.

[0018] In a preferred solution, the parameter information includes a form ID, a control ID, a user ID, and a control value.

[0019] In a preferred solution, the step of obtaining basic SQL keywords and combining the basic SQL keywords with parameter information into an SQL statement includes:

[0020] Get the SQL script generator and generate SQL keywords;

[0021] Select SQL keywords based on parameter information;

[0022] Combine the selected SQL keywords and parameter information into an SQL statement.

[0023] In a preferred embodiment, after the step of combining the selected SQL keywords and parameter information into an SQL statement, the method further includes:

[0024] Get SQL statements;

[0025] Create a JavaScript code block;

[0026] Insert the Sql statement into the JavaScript code block;

[0027] Insert a JavaScript code block containing SQL statements into your JavaScript code.

[0028] In a preferred solution, the SQL keywords include Insert, Update, Delete, Select and Where.

[0029] In a preferred solution, the steps of obtaining a sharding key, concatenating the sharding key with an SQL statement, and generating an executable SQL statement include:

[0030] Get the SQL script parser and parse the corresponding Sql statement before executing the JavaScript script;

[0031] Get the shard key corresponding to the user;

[0032] Concatenate the obtained sharding key with the parsed SQL statement to generate an SQL statement containing the sharding key;

[0033] The sharding key and SQL statement are further combined to generate an executable SQL statement.

[0034] In a preferred solution, the step of obtaining the result of executing the executable SQL statement by the database and table sharding middleware and returning the result to the JavaScript script engine for continued execution includes:

[0035] Receives executable SQL statements.

[0036] Submit the executable SQL statement to the custom sharding middleware to find the shards and execute the SQL.

[0037] Get the result data of shard execution.

[0038] The result data is returned to the JavaScript scripting engine to continue executing the user's custom code.

[0039] The present invention further provides a system for dynamically executing database scripts based on Nashorn, which is applied to the above-mentioned method for dynamically executing database scripts based on Nashorn, comprising:

[0040] Parameter getter, used to obtain parameter information in the JavaScript script engine;

[0041] SQL script generator, used to obtain basic SQL keywords and combine basic SQL keywords with parameter information into SQL statements;

[0042] SQL script parser, used to obtain the corresponding Sql statement before JavaScript script execution, obtain the sharding key, and concatenate the sharding key with the SQL statement to generate an executable SQL statement;

[0043] The SQL script executor is used to obtain the results of the executable SQL statements executed by the sharding middleware and return the results to the JavaScript script engine for further execution.

[0044] And, a device for dynamically executing database scripts based on Nashorn, comprising:

[0045] one or more processors;

[0046] a storage device having one or more programs stored thereon;

[0047] When the one or more programs are executed by the one or more processors, the one or more processors implement the method for dynamically executing database scripts based on Nashorn.

[0048] The technical effects achieved by the present invention are:

[0049] This invention, by introducing the Nashorn engine and combining it with the custom-developed SqlBuilder engine, implements the CustomCode custom code framework. This framework not only supports the interaction between forms and databases, but can also be applied to data interaction at process nodes, as well as data interaction, thus realizing dynamic interaction with multi-party data.

[0050] The present invention uses the SqlBuilder engine to allow users to visually select SQL keywords and required parameters when editing custom code, compose executable SQL code, interact with the database, expand the functionality of the custom code framework, and reduce the user's workload;

[0051] The present invention solves the concurrency problem of executing a large number of SQL scripts in custom codes through sharding technology. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] FIG1 is a flow chart of the method provided by the present invention;

[0053] FIG2 is a block diagram of the system provided by the present invention. DETAILED DESCRIPTION

[0054] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, the specific embodiments of the present invention are described in detail below with reference to the accompanying drawings.

[0055] In the following description, many specific details are set forth to facilitate a full understanding of the present invention. However, the present invention may also be implemented in other ways different from those described herein. Those skilled in the art may make similar generalizations without violating the connotation of the present invention. Therefore, the present invention is not limited to the specific embodiments disclosed below.

[0056] Secondly, the term "one embodiment" or "embodiment" herein refers to a specific feature, structure, or characteristic that may be included in at least one implementation of the present invention. The phrase "in a preferred embodiment" appearing in various places throughout this specification does not necessarily refer to the same embodiment, nor does it constitute a separate or selective embodiment that is mutually exclusive of other embodiments.

[0057] Secondly, the present invention is described in detail with reference to schematic diagrams. When describing the embodiments of the present invention in detail, for the sake of convenience, the schematic diagrams are only examples and should not limit the scope of protection of the present invention.

[0058] Referring to Figures 1 and 2 , a method for dynamically executing database scripts based on Nashorn is provided, including:

[0059] S1. Obtain parameter information in the JavaScript script engine;

[0060] S2. Obtain basic SQL keywords and combine the basic SQL keywords with parameter information into an SQL statement;

[0061] S3. Obtain the sharding key, concatenate the sharding key with the SQL statement, and generate an executable SQL statement.

[0062] S4. Obtain the result of executing the executable SQL statement by the database and table sharding middleware, and return the result to the JavaScript script engine for further execution.

[0063] As in steps S1 to S4 above, the required parameter information is obtained from the JavaScript script engine. This parameter information will be used to build a customized database query. The user selects the SQL operation to be performed (such as SELECT, UPDATE, INSERT, etc.) and combines it with the parameter information to build a complete SQL query, which usually involves combining SQL keywords with parameter information as part of the SQL statement, obtaining key information for sharding, which is usually related to the user or data classification. Then, these sharding keys are spliced ​​with the SQL statement to create a shard-specific SQL query, ensuring that the query will be routed to the correct database shard for execution. The executable SQL statement is passed to the sharding middleware, which is responsible for routing the query to the correct database shard. The database is sharded and queries are executed. The query results are returned to the JavaScript scripting engine to support subsequent application logic and can dynamically adapt to different situations and needs. This can make the scripting engine more versatile and eliminate the need to hard-code parameter information for each query, so that users do not need to have an in-depth understanding of SQL syntax. Queries can be built in a visual or interactive manner, reducing the risk of errors and improving user friendliness. It ensures that in a distributed database environment, each query will be correctly routed to the appropriate shard, thereby improving query performance and management. It can execute complex queries in a distributed environment and ensure that the results are accurately returned to the JavaScript scripting engine to support the user interface or other application logic, which helps to improve query performance, simplify the query building process and enhance user experience.

[0064] The steps for obtaining parameter information in the JavaScript script engine include:

[0065] S101, obtain JavaScript script;

[0066] S102, obtaining a parameter acquirer, and establishing a connection between the parameter acquirer and a database;

[0067] S103, creating an SQL query, and making the database connection execute the SQL query;

[0068] S104, obtaining a query result set;

[0069] S105: The parameter acquirer traverses the query result set and extracts parameter information in the JavaScript script.

[0070] It should be noted that the parameter information includes form ID, control ID, user ID and control value.

[0071] As in the above steps S101 to S105, a JavaScript script is obtained, which is usually a user-defined script that contains the database operations that need to be performed. It can be a JavaScript script for various purposes such as data processing, report generation, and data analysis. The system establishes a connection with the database and uses a parameter getter to retrieve the required parameter information. The system uses the parameter information extracted by the parameter getter to combine it with the SQL query template in the JavaScript script into an actual SQL query. Then, the system sends this query to the database connection for execution. Once the SQL query is executed, the system will obtain a query result set from the database, which includes a set of data rows that meet the query conditions. The parameter getter traverses the query result set and extracts the JavaScript SQL query template from it. cript script, which can cover various data types such as form ID, control ID, user ID and control value, etc. Obtaining JavaScript scripts allows the system to perform user-defined custom operations, making it flexible and versatile. The system can dynamically obtain parameter information in the database for building and customizing SQL queries without hard-coding parameters, and dynamically build and execute SQL queries according to specific circumstances, thereby supporting a variety of different database operations. Obtaining query result sets allows the system to obtain the required data for further processing or presentation to the user. The system can extract specific data from the query results for further processing by JavaScript scripts to implement custom logic, which improves the flexibility and versatility of the system.

[0072] The steps of obtaining basic SQL keywords and combining the basic SQL keywords with parameter information into an SQL statement include:

[0073] S201, obtain an SQL script generator and generate SQL keywords;

[0074] S202. Select SQL keywords based on parameter information;

[0075] S203: Combine the selected SQL keywords and parameter information into an SQL statement.

[0076] It should be noted that SQL keywords include Insert, Update, Delete, Select, and Where.

[0077] As in steps S201 to S205 above, the system obtains an SQL script generator, which includes a set of basic SQL keywords, such as Insert, Update, Delete, Select, and Where. These keywords can be predefined or customized by the user. The system selects appropriate SQL keywords based on the obtained parameter information and user needs. The keyword selection can be based on conditions, user input, or other contexts. The system combines the selected SQL keywords with the obtained parameter information into an SQL query, which usually involves inserting the keywords into an SQL query template and filling in parameter values. Obtaining an SQL script generator allows the system to provide a set of predefined SQL operations, thereby simplifying the process of users building SQL queries and reducing dependence on SQL syntax. The system dynamically selects the SQL operations to be performed according to specific circumstances at runtime, thereby improving the flexibility and versatility of the system. Users do not need to hard-code SQL keywords and can freely select them as needed. Users build SQL queries in an interactive or visual manner without having an in-depth understanding of SQL syntax. At the same time, it also reduces the risk of user errors and improves user friendliness.

[0078] After the step of combining the selected SQL keywords and parameter information into an SQL statement, the following steps are also included:

[0079] S204, obtain SQL statement;

[0080] S205. Create a JavaScript code block;

[0081] S206. Insert the SQL statement into the JavaScript code block;

[0082] S207: Insert the JavaScript code block containing the SQL statement into the JavaScript code.

[0083] As in steps S204 to S207 above, the system has successfully constructed an SQL statement based on the selected SQL keywords and parameter information. This SQL statement is a specific expression of the user's needs and can be an Insert, Update, Delete, Select or a query containing a Where clause. A JavaScript code block is created. This code block will contain the SQL statement for performing database operations. The generated SQL statement is inserted into the created JavaScript code block to form a complete JavaScript code segment. Finally, the JavaScript code block containing the SQL statement is inserted into the user's JavaScript code to perform database operations in the entire JavaScript script. Obtaining the SQL statement is a key step in further processing and executing database operations, which enables the system to accurately perform operations required by the user. Creating a JavaScript code block allows SQL operations to be combined with user-defined JavaScript code. In this way, users can embed SQL queries into their own JavaScript logic as needed, allowing users to perform database operations in JavaScript, interact with other application logic, and embed SQL queries into custom code. This provides greater flexibility, making database operations part of the JavaScript script, allowing users to perform database operations in the script and customize logic as needed, which improves the scalability and customization capabilities of the system.

[0084] The steps to obtain the shard key, concatenate the shard key with the SQL statement, and generate an executable SQL statement include:

[0085] S301. Obtain a SQL script parser and parse the corresponding SQL statement before executing the JavaScript script;

[0086] S302: Obtain the sharding key corresponding to the user;

[0087] S303: Concatenate the obtained sharding key with the parsed SQL statement to generate an SQL statement containing the sharding key.

[0088] S304: Further combine the sharding key and the SQL statement to generate an executable SQL statement.

[0089] As in steps S301 to S304 above, a SQL script parser is obtained, which parses the SQL statement before the JavaScript script is executed. The parser can analyze the SQL statement, perform syntax verification, and perform processing specific to the execution environment (such as tenant or shard), obtain a shard key associated with a specific user or environment, which can be a value associated with a tenant, a specific data identifier, or other identifiable user information, and concatenate the obtained shard key with the parsed SQL statement, which usually involves inserting the shard key into a specific position in the SQL statement, which will further combine the shard key and the SQL statement to generate the final executable SQL statement, which already contains specific shard key information, ensures that the SQL statement is correctly parsed and processed before execution, thereby improving the accuracy and security of execution. It also provides a suitable operating platform for subsequent shard key insertion. Obtaining the shard key can ensure that the query will be routed to the correct database shard for execution, ensuring the accuracy and performance of database operations, ensuring that the SQL statement can dynamically adapt to specific users or environments, thereby improving the accuracy of the query and the effectiveness of execution, and ensuring that the generated SQL statement is executable and can be directly mapped to the correct shard in the database, thereby improving the efficiency and accuracy of database operations.

[0090] The steps to obtain the results of the executable SQL statements executed by the sharding middleware and return the results to the JavaScript script engine for further execution include:

[0091] S401: Receive an executable SQL statement.

[0092] S402: Submit the executable SQL statement to the custom sharding middleware to find the shards and execute the SQL.

[0093] S403: Obtain the result data of the shard execution.

[0094] S404: Return the result data to the JavaScript script engine to continue executing the user's customized code.

[0095] As in the above steps S401 to S404, an executable SQL statement containing specific sharding information is received, and the statement is ready to be submitted to the sharding middleware for execution. The executable SQL statement is submitted to the custom sharding middleware. The task of the middleware is to route the query to the correct database shard according to the sharding information, and execute the SQL operation, obtain the result data after executing the query, including a set of data rows or the output of other queries, and return the query result data to the JavaScript script engine to support subsequent application logic, including further processing in the script or presenting the results to the user, ensuring that the system can obtain and process SQL queries to perform specific database operations. The custom sharding middleware allows the system to execute queries in a distributed database environment, ensures that the queries are correctly routed and executed, improves the performance of database operations and data management, and obtains the result data. Allowing the system to obtain the required data for further processing or presentation to the user, it can continue to execute custom logic in the JavaScript script and integrate the results of the database operation into the application.

[0096] The present invention further provides a system for dynamically executing database scripts based on Nashorn, which is applied to the above-mentioned method for dynamically executing database scripts based on Nashorn, comprising:

[0097] Parameter getter, used to obtain parameter information in the JavaScript script engine;

[0098] SQL script generator, used to obtain basic SQL keywords and combine basic SQL keywords with parameter information into SQL statements;

[0099] SQL script parser, used to obtain the corresponding Sql statement before JavaScript script execution, obtain the sharding key, and concatenate the sharding key with the SQL statement to generate an executable SQL statement;

[0100] The SQL script executor is used to obtain the results of the executable SQL statements executed by the sharding middleware and return the results to the JavaScript script engine for further execution.

[0101] In the above, the parameter getter is responsible for obtaining the required parameter information from the JavaScript script engine. These parameters may include form ID, control ID, user ID and control value, etc. The parameter getter provides this parameter information to other components for use. The SQL script generator is responsible for providing basic SQL keywords such as Insert, Update, Delete, Select and Where. Users can select these keywords and combine them with parameter information to generate a complete SQL query. The SQL script parser parses the corresponding SQL statement before the JavaScript script is executed, and then obtains the sharding key information, combines the sharding key with the SQL statement, and generates an executable SQL query, which may involve analyzing SQL syntax, validating syntax, and processing sharding information. The SQL script executor submits the executable SQL query to the custom sharding middleware, which is responsible for routing the query to the correct database shard and executing it. SQL operations and returns the result data to the JavaScript script engine to continue executing user-defined code. The parameter getter enables the system to dynamically obtain parameter information in the JavaScript script engine, so that the system can adapt to different contexts and needs, improving flexibility and versatility. The SQL script generator simplifies the SQL query construction process, reduces the user's dependence on SQL syntax, reduces the risk of errors, and improves user friendliness. The SQL script parser ensures that SQL queries are correctly parsed and processed before execution, improving the accuracy and security of queries. It also allows dynamic adaptation to sharding requirements in a distributed database environment and improves the performance of database operations. The SQL script executor allows the system to execute complex queries in a distributed environment, ensuring that queries are correctly routed to the appropriate database shards, improving query performance and management. It also supports integrating the results of database operations into applications, improving the flexibility and scalability of the system.

[0102] And, a device for dynamically executing database scripts based on Nashorn, comprising:

[0103] one or more processors;

[0104] a storage device having one or more programs stored thereon;

[0105] When one or more programs are executed by one or more processors, the one or more processors implement a method for dynamically executing database scripts based on Nashorn.

[0106] The foregoing is merely a preferred embodiment of the present invention. It should be noted that those skilled in the art may make various improvements and modifications without departing from the principles of the present invention, and such improvements and modifications are also within the scope of protection of the present invention. Structures, devices, and operating methods not specifically described or explained herein shall, unless otherwise specified or limited, be implemented in accordance with conventional means in the art.

Claims

1. A method for dynamically executing database scripts based on Nashorn, characterized in that: include: Get parameter information in the JavaScript script engine; Get basic SQL keywords and combine them with parameter information into SQL statements; Get the shard key, concatenate the shard key with the SQL statement, and generate an executable SQL statement; Get the results of the executable SQL statements executed by the sharding middleware, and return the results to the JavaScript script engine for further execution.

2. The method for dynamically executing database scripts based on Nashorn according to claim 1, characterized in that: The step of obtaining parameter information in the JavaScript script engine includes: Get the JavaScript script; Get the parameter getter and connect the parameter getter to the database; Create SQL query and make the database connection execute SQL query; Get the query result set; The parameter getter traverses the query result set and extracts parameter information from the JavaScript script.

3. The method for dynamically executing database scripts based on Nashorn according to claim 2, characterized in that: The parameter information includes form ID, control ID, user ID and control value.

4. The method for dynamically executing database scripts based on Nashorn according to claim 2, characterized in that: The step of obtaining basic SQL keywords and combining the basic SQL keywords with parameter information into an SQL statement includes: Get the SQL script generator and generate SQL keywords; Select SQL keywords based on parameter information; Combine the selected SQL keywords and parameter information into an SQL statement.

5. The method for dynamically executing database scripts based on Nashorn according to claim 4, characterized in that: After the step of combining the selected SQL keywords and parameter information into an SQL statement, the method further includes: Get SQL statements; Create a JavaScript code block; Insert the Sql statement into the JavaScript code block; Insert a JavaScript code block containing SQL statements into your JavaScript code.

6. The method for dynamically executing database scripts based on Nashorn according to claim 4, characterized in that: The SQL keywords include Insert, Update, Delete, Select and Where.

7. The method for dynamically executing database scripts based on Nashorn according to claim 5, characterized in that: The step of obtaining a sharding key, concatenating the sharding key with an SQL statement, and generating an executable SQL statement includes: Get the SQL script parser and parse the corresponding Sql statement before executing the JavaScript script; Get the shard key corresponding to the user; Concatenate the obtained sharding key with the parsed SQL statement to generate an SQL statement containing the sharding key; The sharding key and SQL statement are further spliced ​​to generate executable SQL statements.

8. The method for dynamically executing database scripts based on Nashorn according to claim 1, characterized in that: The step of obtaining the result of executing the executable SQL statement by the database and table sharding middleware and returning the result to the JavaScript script engine for further execution includes: Receives executable SQL statements. Submit the executable SQL statement to the custom sharding middleware to find the shards and execute the SQL. Get the result data of the shard execution. The result data is returned to the JavaScript scripting engine to continue executing the user's custom code.

9. A system for dynamically executing database scripts based on Nashorn, applied to the method for dynamically executing database scripts based on Nashorn according to any one of claims 1 to 8, characterized in that: include: Parameter getter, used to obtain parameter information in the JavaScript script engine; SQL script generator, used to obtain basic SQL keywords and combine basic SQL keywords with parameter information into SQL statements; SQL script parser, used to obtain the corresponding Sql statement before the JavaScript script is executed, obtain the sharding key, concatenate the sharding key with the SQL statement, and generate an executable SQL statement; The SQL script executor is used to obtain the results of the executable SQL statements executed by the sharding middleware, and return the results to the JavaScript script engine for further execution.

10. A device for dynamically executing database scripts based on Nashorn, applied to the method for dynamically executing database scripts based on Nashorn according to any one of claims 1 to 8, characterized in that: include: one or more processors; a storage device having one or more programs stored thereon; When the one or more programs are executed by the one or more processors, the one or more processors implement the method for dynamically executing database scripts based on Nashorn.

Images