Civil aviation data-oriented intelligent query method and device, and electronic device

Abstract

The application discloses a kind of civil aviation data-oriented intelligent query method and device, electronic equipment, it is related to shipping service technical field, the method includes: based on directory folder name and / or table name fuzzy information, in database metadata dictionary matching target data table, and the name of all columns in the target data table and data type definition are acquired, column information mapping table is generated;Each node in the query condition tree structure is traversed, according to column information mapping table determine the data type of each node corresponding column, based on data type execution adaptive syntax conversion and logic operator splicing, generate query condition string;Combined with the sorting condition and paging parameter input by user end, build executable SQL query statement;SQL query statement is executed, obtains original data result set, traverses original data result set, according to the data type definition in column information mapping table, data value is converted into standardized string format, and output query response result to user end.

Description

Technical Field

[0001] This invention relates to the field of shipping service technology or other related fields, and more specifically, to an intelligent query method and device for civil aviation data, and an electronic device. Background Technology

[0002] In today's increasingly globalized aviation market, fares play a crucial bridging role between airlines and passengers. They are not only a core element influencing an airline's market competitiveness but also directly related to its profitability. With the rapid development of the air transport industry and the continuous improvement of informatization, the complexity and scale of data processing faced by fare management have reached unprecedented levels. The civil aviation fare sector actually constitutes an extremely sophisticated, dynamic, and multi-dimensional business system, encompassing massive amounts of basic data such as base fares, fuel surcharges, various taxes and fees, discount rules, fare usage conditions, refund and change policies, as well as sales channel information and city mileage. These data come from diverse sources and have complex dimensions, collectively forming a vast and intricate data network.

[0003] In actual operation and maintenance work, professionals engaged in fare data maintenance must have a deep understanding and memorization of the structural design of hundreds or even thousands of data tables. They need to know clearly which table stores basic fare information, where the relevant applicable rules are linked, and at which data level the historical fare change trajectory is recorded. They also need to be familiar with the names, data types, and actual business meanings of each field in each table. Because many data table structures are inherently very complex, field definitions often differ significantly between different tables. Especially when the system is still under development, the complexity and cost of overall data maintenance are further increased due to continuous document updates and differences in data definition habits among different designers and developers. Therefore, the daily maintenance of fare data and the troubleshooting of production environment issues not only require practitioners to have solid knowledge of airfare business but also to be experienced database experts capable of skillfully writing and optimizing SQL queries. This dual skill requirement creates a high professional threshold and brings significant human resource costs.

[0004] In related technologies, common freight rate query patterns heavily rely on individuals' memory of data table structures and their ability to manually write SQL statements. This approach exposes a series of efficiency bottlenecks in practice: when troubleshooting production issues or tracing historical freight rate changes, staff must first consult numerous technical documents, repeatedly understand the relevant table structures, and then organize complex SQL queries based on business needs. This entire process is time-consuming and error-prone, especially during new employee training or cross-team collaboration, where knowledge transfer and experience reuse become more difficult, hindering rapid problem identification and resolution. Furthermore, because non-technical personnel (such as business analysts and managers) lack SQL writing skills, simple data query needs rely on the IT department, leading to high communication costs, long response times, and severely restricting the efficiency of business decision-making.

[0005] There is currently no effective solution to the above problems. Summary of the Invention

[0006] This invention provides an intelligent query method, device, and electronic device for civil aviation data, which at least solves the technical problems in related technologies where airfare query modes rely on an individual's ability to memorize data table structures and manually write SQL query statements, resulting in low query efficiency and a high risk of errors.

[0007] According to one aspect of the present invention, an intelligent query method for civil aviation data is provided, comprising: responding to a fare query request from a user terminal; matching a target data table in a database metadata dictionary based on fuzzy information of a directory folder name and / or table name input by the user terminal, and obtaining the names of all columns in the target data table and their corresponding data type definitions, thereby generating a column information mapping table; traversing each node in a query condition tree structure, determining the data type of the corresponding column of each node according to the column information mapping table, performing adaptive syntax conversion and logical operator concatenation based on the data type, and generating a query condition string; constructing an executable SQL query statement based on the fuzzy information of the directory folder name and / or table name, the query condition string, the sorting conditions and pagination parameters input by the user terminal; executing the SQL query statement to obtain an original data result set, traversing the original data result set, converting the data values ​​into a standardized string format according to the data type definitions in the column information mapping table, and outputting the query response result to the user terminal.

[0008] Optionally, the step of matching the target data table in the database metadata dictionary based on the fuzzy information of the directory folder name and / or table name input by the user terminal includes: receiving the directory folder name input by the user terminal; if the user terminal has not input the fuzzy information of the table name, returning a list of all table names under the directory folder name to the user terminal; receiving the first table name selection result from the user terminal; based on the table name information in the first table name selection result and the directory folder name, obtaining a list of names of all columns of the target data table and the data type definitions of the columns in the database metadata dictionary; if the user terminal has input the fuzzy information of the table name, searching for table names containing the fuzzy information of the table name in the folder space indicated by the directory folder name, generating a list of candidate table names for the user terminal to choose from; receiving the second table name selection result from the user terminal; based on the table name information in the second table name selection result and the directory folder name, obtaining a list of names of all columns of the target data table and the data type definitions of the columns in the database metadata dictionary.

[0009] Optionally, the steps of traversing each node in the query condition tree structure, determining the data type of the corresponding column of each node according to the column information mapping table, performing adaptive syntax conversion and logical operator concatenation based on the data type, and generating a query condition string include: receiving a multi-level condition tree structure configured by the user and calling a data type conversion rule library, wherein the multi-level condition tree structure includes: column name, operator, and operation condition; determining the data type of the corresponding column of each node in the multi-level condition tree structure according to the column information mapping table; performing independent verification and concatenation processing on each leaf node in the multi-level condition tree structure based on the data type of the corresponding column of each node to obtain a first processing result; recursively assembling the non-leaf nodes in the multi-level condition tree structure based on the data type of the corresponding column of each node to obtain a second processing result; and outputting the query condition string based on the first processing result and the second processing result.

[0010] Optionally, the step of independently validating and concatenating each leaf node in the multi-level condition tree structure based on the data type of the column corresponding to each node to obtain the first processing result includes: if the data type of the column corresponding to the current node is a character type, adjusting the operation condition to be enclosed in single quotes; if the data type of the column corresponding to the current node is a numeric type, removing the single quotes from the operation condition; if the data type of the column corresponding to the current node is a time type, determining the format based on the number of digits in the input number, and calling the string to date / time function according to the input number format; if the data type of the column corresponding to the current node is a pattern matching operator and the user does not specify wildcards before or after, automatically supplementing the specified wildcard symbols.

[0011] Optionally, the step of recursively assembling the non-leaf nodes in the multi-level condition tree structure based on the data type of the corresponding column of each node includes: if the multi-level condition tree structure contains a condition list child node, concatenating the condition list child node with the condition list and connecting it with the ampersand symbol; if the multi-level condition tree structure contains an or condition list child node, concatenating the condition list child node with the condition list and connecting it with the or condition list and enclosing the connection result in parentheses.

[0012] Optionally, the step of constructing an executable SQL query statement based on the fuzzy information of the directory folder name and / or table name, the query condition string, and the sorting conditions and pagination parameters input by the user includes: constructing a query statement and specifying a preset query field as an asterisk in the query statement, or specifying a field according to requirements, specifying the table name of the directory folder name and the fuzzy information of the table name in the data source clause; concatenating the query condition string after the query statement, and detecting whether the user has provided a list of sorting conditions and pagination parameters; if the user has input the list of sorting conditions, constructing a sorting clause for the query result set according to the list of sorting conditions, and concatenating the column names and column sorting identifiers; if the user has not specified a list of sorting conditions, adding a descending identifier by default; if the user has input the pagination parameters, constructing pagination logic according to the database dialect, converting the page size and page number into corresponding row number limits or offset parameters, and outputting an executable SQL query statement.

[0013] Optionally, the step of converting data values ​​into a standardized string format according to the data type definition in the column information mapping table includes: if the column data type in the column information mapping table is a numeric type, converting the data value into a string format; if the column data type in the column information mapping table is a date / time type, converting the time object into a standard string format; if the column data type in the column information mapping table is a character type, keeping it as a string format; if the column data content in the column information mapping table is empty, replacing it with a specific string; if the column data type in the column information mapping table is a file pointer type, extracting the directory name and file name, and concatenating them into a specified directory name-file name format; if the column data type in the column information mapping table is a target capacity data type, only returning a string as the column type, where the target capacity data type refers to a data type whose capacity data is greater than a preset capacity threshold.

[0014] According to another aspect of the present invention, an intelligent query device for civil aviation data is also provided, comprising: a table name matching unit, configured to respond to a fare query request from a user terminal, match a target data table in a database metadata dictionary based on the fuzzy information of the directory folder name and / or table name input by the user terminal, and obtain the names of all columns in the target data table and their corresponding data type definitions, thereby generating a column information mapping table; a data type determination unit, configured to traverse each node in the query condition tree structure, determine the data type of the corresponding column of each node according to the column information mapping table, perform adaptive syntax conversion and logical operator concatenation based on the data type, and generate a query condition string; a query statement construction unit, configured to construct an executable SQL query statement based on the fuzzy information of the directory folder name and / or table name, the query condition string, the sorting conditions and pagination parameters input by the user terminal; and a query unit, configured to execute the SQL query statement, obtain an original data result set, traverse the original data result set, convert the data values ​​into a standardized string format according to the data type definitions in the column information mapping table, and output the query response result to the user terminal.

[0015] Optionally, the table name matching unit includes: a folder name receiving module, used to receive a directory folder name input by the user; a list returning module, used to return a list of all table names under the directory folder name to the user if the user has not input the fuzzy information of the table name; a table name selection receiving module, used to receive the first table name selection result from the user; a first name list acquisition module, used to obtain a list of names of all columns of the target data table and the data type definition of the columns in the database metadata dictionary based on the table name information in the first table name selection result and the directory folder name; a table name retrieval module, used to retrieve table names containing the fuzzy information of the table name in the folder space indicated by the directory folder name if the user has input the fuzzy information of the table name, and generate a candidate table name list for the user to choose from; receive the second table name selection result from the user; and a second name list acquisition module, used to obtain a list of names of all columns of the target data table and the data type definition of the columns in the database metadata dictionary based on the table name information in the second table name selection result and the directory folder name.

[0016] According to another aspect of the present invention, a computer-readable storage medium is also provided, the computer-readable storage medium including a stored computer program, wherein, when the computer program is executed, it controls the device where the computer-readable storage medium is located to execute the intelligent query method for civil aviation data described above.

[0017] According to another aspect of the present invention, an electronic device is also provided, including one or more processors and a memory, the memory being used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors cause the one or more processors to implement the intelligent query method for civil aviation data described above.

[0018] According to another aspect of the present invention, a computer program product is also provided, including a computer program that, when executed by a processor, implements the steps of the intelligent query method for civil aviation data described in any one of the above embodiments.

[0019] In this disclosure, in response to a user's fare query request, based on the fuzzy information of the directory folder name and / or table name input by the user, the system matches the target data table in the database metadata dictionary and obtains the names of all columns and their corresponding data type definitions in the target data table, generating a column information mapping table. It then traverses each node in the query condition tree structure, determines the data type of the corresponding column for each node according to the column information mapping table, performs adaptive syntax conversion and logical operator concatenation based on the data type, and generates a query condition string. Based on the fuzzy information of the directory folder name and / or table name, the query condition string, the sorting conditions and pagination parameters input by the user, it constructs an executable SQL query statement. Finally, it executes the SQL query statement, obtains the original data result set, traverses the original data result set, converts the data values ​​into a standardized string format according to the data type definitions in the column information mapping table, and outputs the query response result to the user.

[0020] In this disclosure, a metadata-driven dynamic table structure discovery mechanism eliminates the reliance on manually memorizing table structures (i.e., by receiving fuzzy information such as directory folder names and table names input by the user, it automatically queries the database metadata dictionary, matches and returns a list of target data tables that meet the conditions). Subsequently, it automatically obtains the names, data types, and attributes of all columns in the target data tables and generates a column information mapping table for the user to select. It also replaces the process of manually writing SQL statements through an intelligent SQL syntax automatic construction engine. After receiving the query conditions configured by the user through the interface, it automatically concatenates the SQL query statement by traversing the condition tree through a recursive algorithm. The adaptive syntax conversion capability based on data types ensures that the generated SQL query statement conforms to the database specification, avoiding syntax errors caused by human negligence. This solves the technical problem in related technologies where the airfare query mode relies on the individual's ability to memorize the data table structure and manually write SQL query statements, resulting in low query efficiency and a high risk of errors. Attached Figure Description

[0021] The accompanying drawings, which are included to provide a further understanding of the invention and form part of this application, illustrate exemplary embodiments of the invention and, together with their description, serve to explain the invention and do not constitute an undue limitation thereof. In the drawings:

[0022] Figure 1 This is a flowchart of an optional intelligent query method for civil aviation data according to an embodiment of the present invention;

[0023] Figure 2 This is a flowchart of an optional intelligent query method for civil aviation data according to an embodiment of the present invention;

[0024] Figure 3 This is a schematic diagram of an optional data structure according to an embodiment of the present invention;

[0025] Figure 4 This is a schematic diagram of an optional intelligent query device for civil aviation data according to an embodiment of the present invention;

[0026] Figure 5 This is a hardware structure block diagram of an electronic device (or mobile device) for an intelligent query method for civil aviation data according to an embodiment of the present invention. Detailed Implementation

[0027] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0028] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0029] To facilitate understanding of the present invention by those skilled in the art, some terms or nouns involved in the various embodiments of the present invention are explained below:

[0030] The Additional Service Fare Publishing System (ASFF) is a software system used in the civil aviation industry to publish and manage fare information related to additional services.

[0031] A schema is a namespace in a relational database used to logically isolate database objects (such as tables and views).

[0032] The data query object, dataQuery, encapsulates the core data object of the entire query request, including table information, conditions, sorting, and pagination information.

[0033] The table information object, tableInfo, encapsulates basic information about the target table for the query, including the schema name and table name.

[0034] Schema name: Specifies the schema name where the queried data is located, used to limit the scope of the table search.

[0035] It should be noted that the intelligent query method and device for civil aviation data in this disclosure can be used in the field of intelligent query and visualization analysis of civil aviation data in the shipping service technology field, and can also be used in any field other than the shipping service technology field in the field of intelligent query and visualization analysis of civil aviation data. This disclosure does not limit the application field of the intelligent query method and device for civil aviation data.

[0036] It should be noted that the information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, and displayed data, etc.) collected in this public disclosure are information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, storage, use, processing, transmission, provision, disclosure, and application of related data are handled with corresponding access points, allowing users to choose to authorize or refuse. For example, this system has interfaces with relevant users or organizations. Before obtaining relevant information, a request to obtain the information needs to be sent to the aforementioned user or organization through the interface, and the relevant information is obtained only after receiving consent from the aforementioned user or organization.

[0037] It should be noted that in this disclosure, customer information is collected and analyzed, and users are provided with corresponding operation entry points to choose whether to agree to or reject the automated decision results; if the user chooses to reject, the process will proceed to the expert decision-making process.

[0038] The following embodiments of the present invention can be applied to various intelligent query systems / applications / devices for civil aviation data. The present invention is applicable to airfare query and shipping service scenarios, especially data operation and maintenance scenarios such as civil aviation fare data management and the Additional Service Fare Release System (ASFF).

[0039] This invention lowers the technical threshold, enabling non-database professionals to perform complex data queries through a visual interface without needing to memorize table structures or write SQL statements; it improves query efficiency and accuracy by automatically identifying data types, automatically concatenating SQL syntax, and handling nested logic, thus avoiding human error. Simultaneously, it enhances system versatility and adaptability, enabling it to adapt to different database table structures, supporting flexible combinations of multi-level AND and OR conditions, and improving the level of intelligent data operation and maintenance.

[0040] The present invention will now be described in detail with reference to various embodiments.

[0041] Example 1

[0042] According to an embodiment of the present invention, an embodiment of an intelligent query method for civil aviation data is provided. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.

[0043] Figure 1 This is a flowchart of an optional intelligent query method for civil aviation data according to an embodiment of the present invention, such as... Figure 1 As shown, the method includes the following steps:

[0044] Step S101: In response to the user's fare query request, based on the fuzzy information of the directory folder name and / or table name input by the user, match the target data table in the database metadata dictionary, and obtain the names of all columns in the target data table and their corresponding data type definitions, and generate a column information mapping table.

[0045] It should be noted that the civil aviation fare data system is vast, containing hundreds of complex data tables. Maintenance personnel often struggle to accurately remember the specific table names. Therefore, this invention allows users to input only the schema name (i.e., the directory folder name) and some vague information about the table name, such as key fields in the table name. A search is then performed in the database's system view or metadata dictionary to find database objects belonging to the specified schema whose table names contain the aforementioned vague information. This helps users quickly filter out potentially relevant data tables based on key features, even when unfamiliar with the complete table name, thereby reducing the risk of query failures due to memory bias. Furthermore, after determining the target data table, this embodiment performs a deep extraction operation on the table structure information. This can be understood as the system calling the database's system tables to obtain detailed information for each column in the target data table, including column name, data type, column length, and precision. This embodiment ultimately generates a column information mapping table, which can be considered an intermediate data structure encapsulating all metadata information of the target data table. At the user interface level, this list can serve as the data source for drop-down menus, allowing users to select specific columns when constructing query conditions. By pre-acquiring and parsing column information, this invention hides complex database structure knowledge in the backend, allowing frontend users to only face a list of business-specific column names.

[0046] Optionally, the step of matching the target data table in the database metadata dictionary based on the fuzzy information of the directory folder name and / or table name input by the user includes: receiving the directory folder name input by the user; if the user did not input fuzzy information of the table name, returning a list of all table names under the directory folder name to the user; receiving the first table name selection result from the user; based on the table name information in the first table name selection result and the directory folder name, obtaining a list of names of all columns of the target data table and the data type definitions of the columns in the database metadata dictionary; if the user has inputted fuzzy information of the table name, searching for table names containing the fuzzy information of the table name in the folder space indicated by the directory folder name, generating a list of candidate table names for the user to choose from; receiving the second table name selection result from the user; based on the table name information in the second table name selection result and the directory folder name, obtaining a list of names of all columns of the target data table and the data type definitions of the columns in the database metadata dictionary.

[0047] It's important to note that the directory folder name is fundamental for limiting the query scope. When the user doesn't input any vague table name information, a list of all table names under that directory folder name can be returned to the user. This full list display helps users discover the required data table by browsing the list even if they are completely unfamiliar with the specific business table names. This is particularly suitable for exploratory queries or scenarios where new users are familiarizing themselves with the system structure. Furthermore, after the user makes a selection from the full list, the system receives the user's first table name selection result. Based on the table name information in the first table name selection result and the directory folder name, the system retrieves a list of all column names and data type definitions of the target data table from the database metadata dictionary, establishing a mapping relationship between the user interface and the underlying database structure.

[0048] To improve query efficiency, this embodiment also supports precise positioning based on fuzzy information. If the user has entered fuzzy information about the table name, the system can search for table names containing that fuzzy information within the folder space indicated by the directory folder name, generating a list of candidate table names for the user to choose from. This fuzzy matching mechanism helps users quickly narrow down their selection range by memorizing some table name keywords, filtering out potentially relevant target tables from hundreds or thousands of tables, thereby reducing the time cost of browsing and searching. Furthermore, after the user selects a specific table from the candidate list, the system receives the second table name selection result from the user's end. Based on the table name information in the second table name selection result and the directory folder name, the system retrieves a list of all column names and data type definitions of the target data table from the database metadata dictionary. By providing both full list and fuzzy search modes, the system helps meet the needs of users in different scenarios, supporting both memory-free browsing and quick positioning based on keywords. Simultaneously, automatically retrieving the column name list and data type definitions helps to present complex database structure information to the user in a structured way, enabling subsequent data query operations to be performed based on accurate field information, thereby reducing the technical risks caused by human selection errors or structural misunderstandings.

[0049] Step S102: Traverse each node in the query condition tree structure, determine the data type of the corresponding column of each node according to the column information mapping table, perform adaptive syntax conversion and logical operator concatenation based on the data type, and generate the query condition string.

[0050] Optionally, the steps of traversing each node in the query condition tree structure, determining the data type of the corresponding column of each node according to the column information mapping table, performing adaptive syntax conversion and logical operator concatenation based on the data type, and generating a query condition string include: receiving the multi-level condition tree structure configured by the user and calling the data type conversion rule library, wherein the multi-level condition tree structure includes: column name, operator, and operation condition; determining the data type of the corresponding column of each node in the multi-level condition tree structure according to the column information mapping table; performing independent validation and concatenation processing on each leaf node in the multi-level condition tree structure based on the data type of the corresponding column of each node to obtain a first processing result; recursively assembling the non-leaf nodes in the multi-level condition tree structure based on the data type of the corresponding column of each node to obtain a second processing result; and outputting the query condition string based on the first processing result and the second processing result.

[0051] Among them, the multi-level condition tree structure is represented at the data level as a nested collection of objects or nodes. Each node can contain column names, operators (such as equal to, greater than, contain, etc.) and operation conditions (i.e., the specific values ​​entered by the user). The tree structure helps to flexibly express complex business logic, such as nesting "or" relationships in "AND" relationships, or combining different filtering conditions in multiple levels, thereby supporting richer data filtering needs than simple linear queries.

[0052] Furthermore, upon receiving the query condition tree structure, the system invokes the data type conversion rule base and determines the data type of the corresponding column in each node of the tree structure based on the column information mapping table. The column information mapping table stores the correspondence between column names and underlying database data types (such as VARCHAR2, NUMBER, DATE, etc.). By looking up the mapping table, the system can accurately identify the field type operated on by each query condition node, helping to convert the user-input business field names into system-recognizable technical types and avoiding SQL concatenation anomalies caused by type identification errors.

[0053] It should be noted that this embodiment adopts a phased processing strategy. First, the leaf nodes in the multi-level condition tree structure are independently validated and concatenated to obtain the first processing result. Here, leaf nodes refer to condition nodes that do not contain child nodes or whose child nodes are empty, representing the lowest-level atomic query conditions. Based on the determined data type, the operation conditions of the leaf nodes are formatted, such as removing quotes from numeric types, adding quotes to character types, and converting date types to TO_DATE function format. Simultaneously, the validity of column names is validated, and the validated column names, operators, and processed values ​​are concatenated into a standard SQL fragment. This independent processing helps isolate logical errors in various basic conditions, facilitates debugging and maintenance, and also improves the modularity of condition construction.

[0054] Furthermore, based on the data type of the corresponding columns of each node, the non-leaf nodes in the multi-level condition tree structure are recursively assembled to obtain the second processing result. The non-leaf nodes contain lists of "AND" and "OR" conditions, representing logical combinations. These nodes are traversed recursively, and the processing results of the child nodes (whether SQL fragments from leaf nodes or assembled results from child non-leaf nodes) are concatenated according to logical operators (AND / OR). For example, if an "OR" node contains multiple "AND" child nodes, the system will enclose the concatenation results of these "AND" child nodes in parentheses and add the "OR" keyword. This recursive assembly method helps to correctly handle complex nested logic and operator precedence, automatically generating WHERE clauses that conform to SQL syntax without requiring the user to manually manage parentheses and logical hierarchy.

[0055] Based on the first and second processing results, the final output is a complete query condition string. The first processing result provides standardized SQL fragments of all atomic conditions, while the second processing result provides the logical structure of these fragments combined according to the user's intent. Combining the two generates a complete, valid SQLWHERE clause string that matches the user's query intent. This automates the conversion process from graphical interface configuration to underlying SQL generation, helping to eliminate common syntax errors and logical inconsistencies in manually written SQL. It ensures that complex query conditions are accurately passed to the database execution engine. By introducing a data type conversion rule base and a recursive assembly algorithm, the system can automatically handle the specific SQL syntax differences required for different data types, as well as complex multi-level logical nesting.

[0056] This embodiment further provides an adaptive syntax conversion mechanism based on column data types to achieve automatic adaptation between user input values ​​and database SQL syntax. Optionally, the step of independently validating and concatenating each leaf node in the multi-level condition tree structure based on the data type of the column corresponding to each node to obtain the first processing result includes: if the data type of the column corresponding to the current node is a character type, adjusting the operation condition to be enclosed in single quotes; if the data type of the column corresponding to the current node is a numeric type, removing the single quotes from the operation condition; if the data type of the column corresponding to the current node is a time type, determining the format based on the number of digits in the input number, and calling the string to date / time function according to the input number format; if the data type of the column corresponding to the current node is a pattern matching operator and the user has not specified wildcards before and after, automatically supplementing the specified wildcard symbols.

[0057] It's important to note that during the generation of query condition strings, the system first identifies the data type of the column corresponding to the current leaf node and performs differentiated string processing logic for different column types. This helps eliminate SQL syntax errors caused by data type differences; for example, character data requires quotation marks while numeric data does not, thus reducing the difficulty for users to manually handle formatting. Furthermore, when the data type of the column corresponding to the current node is determined to be character, the system adjusts the operation conditions to be enclosed in single quotes. For example, if the user enters "ABC", the system automatically converts it to "'ABC'", which helps conform to the SQL standard's writing conventions for string text data and avoids syntax parsing errors caused by missing quotes. It also supports string queries containing special characters or spaces. It's also important to note that if the data type of the column corresponding to the current node is numeric, the system removes single quotes from the operation conditions. For example, if the user habitually enters "'100'", it will be automatically cleaned up to "100", helping to ensure the accuracy of numeric comparison operations and preventing the database from misinterpreting numeric strings as text for comparison, thereby avoiding query result deviations or performance degradation due to type mismatches. Furthermore, for cases where the data type of the column corresponding to the current node is time, the specific date or time format is determined based on the number of digits in the user-input number. For example, if the input is an 8-digit number (such as 20260101), the system can recognize it as a date format; if the input contains more digits or separators, it can be recognized as a time format with hours, minutes, and seconds. Subsequently, based on the recognized format, the corresponding string-to-date-time function is called to convert the user-input raw string into a time type object that the database can recognize. This helps to unify the storage and comparison format of time data, solve the problem of inconsistent time string formats from different sources, and enable range queries (such as BETWEEN) to perform accurate matching based on the standard time type.

[0058] Additionally, it's worth noting that if the current node's column uses a pattern matching operator (such as LIKE), and the system detects that the user hasn't specified wildcards before or after the condition (i.e., no % or _ symbols are entered), the system will automatically add the specified wildcard symbol (such as %) before and after the condition. For example, if the user enters "value" using the LIKE operator, the system will automatically convert it to "%value%", which helps simplify the user's query operations, provides fuzzy search functionality by default, and avoids empty or unexpected query results due to the user omitting wildcards, thereby improving query error tolerance and user experience. By automatically performing operations such as adding or removing quotation marks, converting date formats, and adding wildcards based on data type, the system helps transform non-standardized user input into SQL fragments that conform to database syntax specifications.

[0059] Furthermore, this embodiment provides a recursive assembly algorithm for logical operators based on a tree structure, converting user-configured graphical query conditions into standard SQL logical expressions. Optionally, the step of recursively assembling non-leaf nodes in a multi-level condition tree structure based on the data type of the corresponding columns of each node includes: if the multi-level condition tree structure contains a condition list child node, concatenating the result of the condition list child node with the condition list and connecting it with the ampersand symbol; if the multi-level condition tree structure contains an or condition list child node, concatenating the result of the condition list child node with the condition list and connecting it with the or condition list child node with the or symbol, and enclosing the connection result in parentheses.

[0060] In this system, non-leaf nodes represent logical combination layers, containing "AND condition lists" and "OR condition lists." By recursively traversing this tree structure and merging the query results of child nodes from bottom to top, it helps handle nested logic of arbitrary depth, enabling complex business rules to be accurately mapped into SQL WHERE clauses that the database can understand. Furthermore, when an AND condition list child node is detected in a multi-level condition tree structure, the concatenation results of each child node in the AND condition list are connected using the "AND" symbol. For example, if the AND condition list contains two child nodes, the system will connect the SQL fragments generated by these two child nodes using "AND" to form a new logical unit. This helps to express business scenarios that simultaneously satisfy multiple conditions, conforms to the standard syntax of logical "AND" operations in SQL, and thus constructs precise multi-filtering logic.

[0061] It should be noted that if the multi-level condition tree structure contains child nodes of the OR condition list, the system further processes the OR conditions based on the concatenation result with the condition list. Specifically, the concatenation results of each child node in the OR condition list can be connected using the "OR" symbol. More importantly, this group of child node results connected by "OR" is enclosed in parentheses. For example, if the "OR" condition list contains three child nodes A, B, and C, it generates the form "(AORBORC)". The enclosed parentheses help clarify the priority of logical operations and prevent ambiguity when mixed with other logical operators (such as the outer AND), thus ensuring that the generated SQL logical structure is completely consistent with the tree structure configured by the user.

[0062] Furthermore, this recursive assembly process is hierarchically transitive; for child nodes (i.e., non-leaf child nodes) nested within an OR condition list, the same assembly logic can be invoked again. If a branch in the OR condition is itself a complex AND-OR hybrid structure, the parenthesis-wrapped structure within that branch is first recursively calculated, and then treated as a whole in the outer OR join. This recursive mechanism helps handle deeply nested complex query logic, ensuring that syntactically correct and logically clear SQL statements are generated regardless of how the user combines the conditions.

[0063] Step S103: Construct an executable SQL query statement based on the fuzzy information of the directory folder name and / or table name, the query condition string, the sorting conditions and pagination parameters input by the user.

[0064] Optionally, the steps for constructing an executable SQL query statement based on the directory folder name and / or fuzzy information of the table name, the query condition string, the sorting conditions and pagination parameters input by the user include: constructing the query statement and specifying the preset query fields as asterisks in the query statement, or specifying fields according to requirements, specifying the directory folder name and the table name of the fuzzy information of the table name in the data source clause; concatenating the query condition string after the query statement, and checking whether the user has provided a list of sorting conditions and pagination parameters; if the user has entered a list of sorting conditions, constructing a sorting clause for the query result set according to the list of sorting conditions, and concatenating the column names and column sorting identifiers; if the user has not specified a list of sorting conditions, adding a descending identifier by default; if the user has entered pagination parameters, constructing pagination logic according to the database dialect, converting the page size and page number into the corresponding row number limit or offset parameters, and outputting the executable SQL query statement.

[0065] Based on the directory folder name, table name, generated query condition string, sorting conditions, and pagination parameters, the basic skeleton of the query statement is constructed. In the data source clause, combining the directory folder name and the target table name forms the standard "Schema.TableName" format to accurately locate the data source. Furthermore, this embodiment supports two modes for selecting query fields: one is to default to specifying a preset query field as an asterisk (…). The first option is to return all columns in the table; the second option is to specify specific fields according to user needs. This flexibility helps to meet the data display needs in different scenarios. For example, when only some key fields need to be viewed, the amount of data transmitted over the network can be reduced by specifying fields; while when full data is needed, the asterisk query provides convenience.

[0066] It's important to note that after condition concatenation and status detection, if the user has already entered a list of sorting criteria, the ORDER BY clause will be constructed based on this list. Specifically, the sorting criteria list can be iterated through, concatenating each column name with its corresponding sorting identifier (ASC for ascending order or DESC for descending order). For example, if the user specifies "column A descending" and "column B ascending," the result will be "ORDER BY column A DESC, column B ASC," helping users to view the query results in an orderly manner and quickly locate or analyze data trends. Furthermore, if the user does not specify a sorting criteria list, a descending identifier is added by default, generating statements like "ORDER BY primary key DESC" or by default descending order based on the time field. This default behavior helps ensure the consistency of the query results, allowing users to obtain a data list that conforms to conventional reading habits (such as newest records first) without having to explicitly specify sorting rules each time.

[0067] It's worth noting that for pagination requirements, if the user has already input pagination parameters (including page size and page number), the corresponding pagination logic is constructed based on the underlying database's dialect (Dialect). The page size and page number are calculated as specific offsets or row number ranges and embedded into the SQL statement. This helps achieve on-demand loading for large datasets, avoiding performance bottlenecks or memory overflows caused by returning massive amounts of data at once. By automatically combining table identifiers, condition strings, sorting clauses, and pagination logic, it helps generate complete, executable SQL statements that conform to specific database syntax. This assembly process not only simplifies user operations but also enhances adaptability across different database environments by supporting pagination conversion for different database dialects, thereby improving the versatility and execution efficiency of data queries.

[0068] After executing an SQL query in the database, a ResultSet containing raw binary data or data of a specific type can be obtained. Since front-end displays or API interfaces typically require a uniform data format, iterating through each row and column of this result set and combining it with a pre-obtained column information mapping table to perform type conversion on each data value helps to shield the heterogeneity of the data returned by the underlying database driver. This ensures that all data is uniformly processed into an easily parsed string format before being transmitted to the user, thereby improving the compatibility and stability of front-end and back-end data interaction.

[0069] Step S104: Execute the SQL query statement to obtain the original data result set, traverse the original data result set, convert the data values ​​into a standardized string format according to the data type definition in the column information mapping table, and output the query response result to the user terminal.

[0070] Optionally, the step of converting data values ​​into a standardized string format according to the data type definition in the column information mapping table includes: if the column data type in the column information mapping table is a numeric type, converting the data value into a string format; if the column data type in the column information mapping table is a date / time type, converting the time object into a standard string format; if the column data type in the column information mapping table is a character type, keeping it as a string format; if the column data content in the column information mapping table is null, replacing it with a specific string; if the column data type in the column information mapping table is a file pointer type, extracting the directory name and file name, and concatenating them into a specified directory name-file name format; if the column data type in the column information mapping table is a target capacity data type, only returning a string as the column type, where the target capacity data type refers to a data type whose capacity data is greater than a preset capacity threshold.

[0071] For columns defined as numeric types in the column information mapping table, the original data values ​​are converted to a standard numeric string format. For example, internal numeric types are converted to strings without trailing zeros to ensure precision. This helps eliminate differences in how different database drivers handle numeric precision, ensuring consistency in the displayed values ​​and avoiding display errors caused by floating-point precision issues. If the column data type in the column information mapping table is date / time, the system converts the time object returned by the database into a standard string format. For example, Date or Timestamp objects are converted into strings according to a preset format such as "YYYY-MM-DD HH:MM:SS" or similar. This helps provide a readable time display, eliminating confusion caused by inconsistencies in time zones or internal storage formats, allowing users to intuitively identify the time attributes of the data. Furthermore, for columns defined as character types in the column information mapping table, they are kept in string format, but trailing spaces may need to be removed or encoding conversions may be performed to meet front-end display requirements. This helps ensure the integrity of the text data and avoids garbled characters caused by inconsistencies between the database character set and the front-end encoding, maintaining the original semantics of the text content. It's worth noting that if the column data in the column information mapping table contains null values ​​(NULL), replacing it with a specific string identifier (such as "[null]" or an empty string) helps prevent the front-end interface from throwing parsing exceptions or displaying blank pages due to receiving null objects. This provides a more user-friendly visual feedback, clearly indicating that the field has no data, thereby improving the robustness of the data display. Furthermore, if the column data type in the column information mapping table is a file pointer type (such as BFILE), instead of directly returning a binary stream, the directory name and file name of the file are extracted from the database metadata and concatenated into a specified "directory name:file name" format. This helps provide users with file location information or access clues without needing to download large files, which is particularly suitable for scenarios pointing to external storage paths. This saves network bandwidth while providing necessary file location information. It should be noted that if the column data type in the column information mapping table is a target capacity data type (such as BLOB, CLOB, NCLOB, etc., large capacity objects), and it is determined that its data content is greater than the preset capacity threshold, only a string containing the column type identifier (such as "[BLOB]") will be returned, instead of the actual large data content. This helps to prevent network congestion, memory overflow, or response timeouts caused by transmitting extremely large data (such as images, document binary streams), and significantly improves the speed of query response and the utilization of system resources.

[0072] By performing differentiated format conversions based on column type, it helps ensure that the front end can correctly parse and display various types of data, including handling null values, large objects, and complex types. In particular, the truncation return strategy for large volumes of data helps optimize data transmission efficiency and avoid unnecessary resource consumption.

[0073] Through the above steps, the system can respond to fare query requests from users. Based on the fuzzy information of the directory / folder name and / or table name input by the user, it matches the target data table in the database metadata dictionary and obtains the names of all columns and their corresponding data type definitions in the target data table, generating a column information mapping table. It then traverses each node in the query condition tree structure, determines the data type of the corresponding column based on the column information mapping table, performs adaptive syntax conversion and logical operator concatenation based on the data type, and generates a query condition string. Based on the fuzzy information of the directory / folder name and / or table name, the query condition string, the sorting conditions and pagination parameters input by the user, it constructs an executable SQL query statement. Finally, it executes the SQL query statement, obtains the original data result set, traverses the original data result set, converts the data values ​​into a standardized string format according to the data type definitions in the column information mapping table, and outputs the query response result to the user. In this embodiment, a metadata-driven dynamic table structure discovery mechanism can eliminate the reliance on manually memorizing table structures (i.e., by receiving fuzzy information such as directory folder names and table names input by the user, automatically querying the database metadata dictionary, matching and returning a list of target data tables that meet the conditions). Subsequently, it automatically obtains the names, data types, and attributes of all columns in the target data tables and generates a column information mapping table for the user to select. An intelligent SQL syntax auto-construction engine replaces the process of manually writing SQL statements. After receiving the query conditions configured by the user through the interface, it traverses the condition tree using a recursive algorithm and automatically concatenates the SQL query statement. The adaptive syntax conversion capability based on data types ensures that the generated SQL query statement conforms to database specifications, avoiding syntax errors caused by human negligence. This solves the technical problem in related technologies where airfare query modes rely on the individual's ability to memorize data table structures and manually write SQL query statements, resulting in low query efficiency and a high risk of errors.

[0074] The following describes in detail another optional implementation method.

[0075] To address the issues of time-consuming and error-prone fare queries in civil aviation fare data management, particularly during new employee training or cross-team collaboration where knowledge transfer and experience reuse are more difficult and hinder rapid data location, this invention proposes a simple tool to facilitate data queries for developers and related business personnel. The aim is to build a universal, visual, and interactive query methodology. The core objective of this system is to enable fare professionals to move beyond the traditional process of tedious, mechanical data table queries in their daily data maintenance and analysis work, eliminating the need for rote memorization of numerous table structures, field definitions, and data types. This tool allows users to quickly and automatically generate the necessary SQL queries based on business intent, significantly simplifying the data query process, lowering the technical barrier, and enabling professionals to focus more on business logic analysis and decision-making, thereby improving overall work efficiency and the intelligence level of data operation and maintenance.

[0076] Because civil aviation fare rates involve numerous tables, and considering that data maintenance personnel often find it difficult to accurately remember table names and the column names and attributes of related data within those tables, the overall design of the tool includes the following usage scenarios:

[0077] 1. Query table names based on schema name (select from list, required) and fuzzy information about table names (optional).

[0078] 2. Based on the schema name (required) and table name (required), you can obtain a list of column names (used for query conditions, a list of sorting options, and subsequent construction of query conditions).

[0079] 3. Based on the combined query conditions, return the corresponding data. The returned content includes: a list of column names, a list of data contents, the SQL concatenation result (used by the user to verify whether it matches the intended query), and the total number of data points involved in the SQL.

[0080] Figure 2 This is a flowchart of an optional intelligent query method for civil aviation data according to an embodiment of the present invention, such as... Figure 2 As shown, the method includes the following main steps:

[0081] Step 1: Basic information about the table to be queried, including the schema name and table name.

[0082] Step 2: Query combination condition restrictions, supporting multi-level AND / OR relationships. Starting with condition 1, other conditions can be optionally listed side-by-side. Any condition node can have corresponding AND / OR conditions listed vertically or horizontally. Each condition input includes three parts: column name (columnA), operator (supported operations include: =, !=, >, <, >=, <=, between, like, in), and operation condition (e.g., ...). Figure 2 Example of a query condition (columnA='AAA').

[0083] Step 3, query sorting restrictions: Supports multiple columns to be sorted in ascending or descending order in list form, such as 'columnA', 'desc'.

[0084] Step 4: Pagination conditions, including page size and page number.

[0085] Figure 3 This is a schematic diagram of an optional data structure according to an embodiment of the present invention, such as... Figure 3 As shown in the embodiments of the present invention, in order to support parallel and hierarchical AND / OR conditions, a combination pattern is adopted for the design of query conditions. A consistent method is used to process AND / OR logic at different levels. Each SelectCondition represents a parallel AND / OR relationship at the same level. In the OR structure of a SelectCondition, the next level of AND / OR relationship can be extended. In this way, whether it is a single filtering condition or a relatively complex nested multi-level condition, it can be recursively processed and traversed through a unified interface, effectively reducing the complexity of business logic.

[0086] The input example is as follows:

[0087] {

[0088] "dataQuery": {

[0089] "tableInfo": {

[0090] "schemaName": "someSchemaName",

[0091] "tableName": "SomeTableName"

[0092] },

[0093] "selectCondition": {

[0094] "andConditionList": [

[0095] {

[0096] "columnName": "column1",

[0097] "operationTag": "IN",

[0098] "operationCondition": "value1, value2, value3"

[0099] },

[0100] {

[0101] "columnName": "column2",

[0102] "operationTag": ">=",

[0103] "operationCondition": "1"

[0104] }

[0105] ],

[0106] "orConditionList": [

[0107] {

[0108] "andConditionList": [

[0109] {

[0110] "columnName": "column3",

[0111] "operationTag": " BETWEEN ",

[0112] "operationCondition": "(date1, date2)"

[0113] },

[0114] {

[0115] "columnName": "column4",

[0116] "operationTag": ">",

[0117] "operationCondition": "value4"

[0118] }

[0119] ],

[0120] "orConditionList": [

[0121] {

[0122] "andConditionList": [

[0123] {

[0124] "columnName": "column5",

[0125] "operationTag": "like",

[0126] "operationCondition": "value5"

[0127] }

[0128] ],

[0129] "orConditionList": []

[0130] } ]

[0132] } ]

[0134] },

[0135] "orderConditionBoList": [

[0136] {

[0137] "columnName": "column6",

[0138] "descORAsc": null

[0139] },

[0140] {

[0141] "columnName": "column7",

[0142] "descORAsc": "asc

[0143] } ]

[0145] },

[0146] "pageInfo": {

[0147] "pageSize": 10,

[0148] "currentPageNum": 5

[0149] }

[0150] }

[0151] The multi-level AND-OR processing logic is as follows:

[0152] 1. For any SelectCondition node, if the condition list is not empty, concatenate the conditions using AND;

[0153] 2. If the OR condition list is not empty, add the [OR()] condition to the above OR condition. Repeat the above operation for each SelectCondition element in the OR condition list and add the concatenation result to the parentheses.

[0154] The simplified code is as follows:

[0155] / / The above logic is achieved by recursively calling the concatenateCondition method.

[0156] String concatenateCondition(){

[0157] Sting sql="";

[0158] / / item refers to the result of concatenating a certain condition node, such as

columnA='aaa'

[0159] / / Concatenation and Conditional Lists

[0160] if (andConditionList not null) {

[0161] for (item : andConditionList) {

[0162] sql = sql + item + "AND";

[0163] }

[0164] sql = sql.length()-5 / / Remove the final AND sign

[0165] }

[0166] / / Concatenation or conditional list

[0167] if (orConditionList not null) {

[0168] sql = sql + "OR ( ");

[0169] For (item : orConditionList) {

[0170] sql = sql + concatenateCondition();

[0171] }

[0172] sql = sql + ")";

[0173] }

[0174] return sql;

[0175] }

[0176] When designing TableColumn, it's generally difficult for users to remember the structure of each piece of data in the development table. Normal SQL queries rely heavily on specific data types. Therefore, to facilitate user queries, the query conditions need to be adjusted based on the data structure. This includes automatically removing single quotes from input value types (e.g., if the user entered single quotes), automatically adding single quotes to character types (e.g., if the user didn't enter single quotes), and automatically converting time types to the TO_DATE format based on the number of significant digits.

[0177] Other user-friendly design features include: 1. Automatic removal of leading and trailing spaces from query information; 2. Automatic conversion of SQL-related keywords to uppercase, except for query conditions; 3. For multi-value query conditions using "between" and "in", the front end is marked with "[multi-value conditions separated by English commas]", and the system judges the user's input and automatically adds "and" and parentheses (if the user has not entered them).

[0178] Furthermore, the returned content in this embodiment includes column names, data content converted into strings according to each column type, SQL concatenation results, and the total number of data involved in the SQL.

[0179] The following is a detailed process of an intelligent query method for civil aviation data, which mainly includes the following parts.

[0180] Part 1: Table Name Lookup.

[0181] Professionals in this field often only remember a few commonly used table field names. When problems arise, they typically need to consult manuals or documentation to determine the exact table name containing the relevant data. To alleviate the burden of memorization and searching for information, this embodiment of the invention allows users to input the schema name and some fields of the table name they wish to query. The system will then search for table names containing the relevant fields within the relevant schema space based on the input information, providing the user with a selection option. If the user enters an empty table name, the system returns a list of all table names within the relevant schema space for the user to choose from.

[0182] When a user selects query conditions, after specifying the schema name and table name on the user's end, the system queries the names of all columns contained in that table, returns them to the front end, and provides drop-down menus for the user to construct query conditions and sorting conditions.

[0183] Then, data is returned based on the query conditions. Users need to specify the schema name and table name, and other information can be entered selectively.

[0184] Each individual query condition node consists of three parts: column name, operator (supported operations include: =, !=, >, <, >=, <=, between, like, in), and operation condition (such as 'AAA'). The client can then expand the corresponding sibling AND or OR conditions horizontally or vertically.

[0185] Each individual sorting condition consists of two parts: column name and sorting method (ascending or descending, or descending if not specified). Multiple sorting conditions can be specified at once.

[0186] Pagination information: Users can enter the page size (i.e. how much data is displayed on a page) and the page number.

[0187] Step 1. Field Information Validation. Upon receiving the request, the validity of each field is first validated. For example, column names should be a combination of uppercase and lowercase letters, numbers, and hash symbols. If all fields pass validation, the query logic is initiated.

[0188] Step 2. Each query condition node is independently validated based on the structure and column types of the table being queried, and the SQL part of the query statement for each node is concatenated according to the queried columns. This step 2 includes the following parts:

[0189] Step 2.1 Obtain column information from the query table, including the correspondence between column names and column types.

[0190] Step 2.2 determines whether it is a special condition type. Special condition types include [IN, LIKE, BETWEEN] conditions. These three conditions not only require input validation of the column type, but also involve transformation in the SQL concatenation process. For example, for the [condition1] restriction, the concatenation logic of the [IN] condition is first entered. That is, according to the column type, the final concatenation is in the form of [column1in()]. If the column type is a character type, such as varchar2, varchar, char, etc., the final concatenation result is [column1 in('value1','value2','value3')]. If the column type is a numeric type, the final concatenation result is [column1 in(value1,value2,value3)]. For example, regarding the restriction

condition3

BETWEEN

column3 BETWEEN()AND()

column3 BETWEEN TO_DATE(time1,'YYYYMMDD')AND(time2,'YYYYMMDD')

condition5

LIKE

column5 LIKE '%somevalue%'

[0191] Step 2.3 Based on the column type, including time, number, and character, convert the input into the corresponding SQL form, as described previously.

[0192] The following explains the assembly of multi-level query conditions. Here, the query condition SQL can be recursively assembled based on the AND / OR list relationship of each node (as in the previous step 2.2 query condition scheme design). For example, the logical relationship of the corresponding SQL assembly result should be

(condition1||(condition2||condition3&&(condition4||condition7))&&(condition5||condition6||condition8))&&condition9

【(column1in('value1','value2','value3')OR(column2>1ORcolumn3 BETWEEN TO_DATE(time1,'YYYYMMDD') AND(time2,'YYYYMMDD')AND(column4 > value4 'VALUE4'OR column7>'value7')) AND(column5 LIKE '%somevalue%'OR column6!=6 OR column8<=TO_DATE('20251122090000', 'YYYYMMDDHH24MISS')))AND column9='value9'].

[0193] The following explains how to concatenate sorting conditions. If no sorting condition is specified, the default is to sort in descending order. For example, the concatenated sorting condition is

ORDER BY columnA desc, columnB asc

[0194] The following explains how to concatenate the complete SQL query the user intends to retrieve. The concatenated result should be: [SELECT ...] FROM someSchemaName.someTableName WHERE (condition1||(condition2|| condition3&& ( condition4 || condition7)) && ( condition5 || conditon6 || condition8 )) && conditon9】, that is, [(column1 in ('value1', 'value2', 'value3') OR ( column2 >1 OR column3 BETWEEN TO_DATE(time1, 'YYYYMMDD') AND (time2, 'YYYYMMDD') AND( column4 > value4 'VALUE4' OR column7 > 'value7' ) ) AND (column5 LIKE '%somevalue%' OR column6 != 6 OR column8 <= TO_DATE('20251122090000','YYYYMMDDHH24MISS')) ) AND column9 = 'value9'ORDER BY columnA desc, [columnBasc] (Note: In practice, all information, including schema name, table name, and column name, will be converted to uppercase).

[0195] The following explains how to retrieve and return data based on pagination criteria for display. The front-end can then display data according to the returned column names and data content, thus supporting different table structures. Specifically, this can include:

[0196] 6.1 If the relevant field is empty, return the string: [null].

[0197] 6.2 Display the contents of number (double, number, decimal, bitint, binary_float, binary_double), date (date, interval, intervals, timestamp), and character (varchar, varchar2, char, nvarchar2, nchar) type columns as string information.

[0198] 6.3 Returns the contents of the file pointer bfile type column in the following format: directory name: file name.

[0199] 6.4 Returns only the content of large data types (blob, nclob, clob, long, long Row) as: [column type].

[0200] The above implementation method replaces the writing of complex SQL statements with a visual interactive interface. Users do not need to memorize the structures, field names, and data types of hundreds of data tables, nor do they need to master SQL syntax. Business or operations personnel can construct queries simply through intuitive operations such as drop-down selections and condition configurations, enabling non-technical personnel to independently complete complex data retrieval, significantly reducing human resource costs and training difficulty.

[0201] This invention utilizes a composite pattern to design a query condition tree structure, supporting flexible nesting of multi-level AND / OR relationships. It automates the handling of logical operator precedence and parenthesis matching, avoiding common syntax errors encountered when manually writing SQL (such as mismatched parentheses and misuse of logical symbols). Simultaneously, automatic table name matching and fuzzy search based on a metadata dictionary shorten the time to locate the target data table, significantly improving the efficiency of converting intent into query statements. Furthermore, it features an adaptive syntax conversion mechanism based on data types, automatically handling details such as dequoting numeric types, adding quotes to character types, converting date formats, and automatically supplementing wildcards. This effectively masks the differences in SQL syntax between different database data types, reducing type mismatch errors caused by human error and improving the accuracy and robustness of generated query statements.

[0202] The following detailed description is based on another embodiment.

[0203] Example 2

[0204] The intelligent query device for civil aviation data provided in this embodiment includes multiple implementation units, each of which corresponds to a specific implementation step in Embodiment 1 above.

[0205] Figure 4 This is a schematic diagram of an optional intelligent query device for civil aviation data according to an embodiment of the present invention, such as... Figure 4 As shown, the intelligent query device for civil aviation data may include: a table name matching unit 41, a data type determination unit 42, a query statement construction unit 43, and a query unit 44.

[0206] The table name matching unit 41 is used to respond to the user's fare query request. Based on the directory folder name and / or fuzzy information of the table name input by the user, it matches the target data table in the database metadata dictionary, obtains the names of all columns in the target data table and their corresponding data type definitions, and generates a column information mapping table.

[0207] The data type determination unit 42 is used to traverse each node in the query condition tree structure, determine the data type of the corresponding column of each node according to the column information mapping table, perform adaptive syntax conversion and logical operator concatenation based on the data type, and generate the query condition string.

[0208] The query statement construction unit 43 is used to construct an executable SQL query statement based on fuzzy information such as directory folder name and / or table name, query condition string, sorting conditions and pagination parameters input by the user.

[0209] The query unit 44 is used to execute SQL query statements, obtain the original data result set, traverse the original data result set, convert the data values ​​into a standardized string format according to the data type definition in the column information mapping table, and output the query response results to the user terminal.

[0210] The aforementioned intelligent query device for civil aviation data can respond to fare query requests from users through a table name matching unit 41. Based on the fuzzy information of the directory folder name and / or table name input by the user, it matches the target data table in the database metadata dictionary and obtains the names of all columns and their corresponding data type definitions in the target data table, generating a column information mapping table. Through a data type determination unit 42, it traverses each node in the query condition tree structure, determines the data type of the corresponding column of each node according to the column information mapping table, performs adaptive syntax conversion and logical operator concatenation based on the data type, and generates a query condition string. Through a query statement construction unit 43, it constructs an executable SQL query statement based on the fuzzy information of the directory folder name and / or table name, the query condition string, the sorting conditions and pagination parameters input by the user, and executes the SQL query statement through a query unit 44 to obtain the original data result set. It then traverses the original data result set, converts the data values ​​into a standardized string format according to the data type definitions in the column information mapping table, and outputs the query response result to the user. In this embodiment, a metadata-driven dynamic table structure discovery mechanism can eliminate the reliance on manually memorizing table structures (i.e., by receiving fuzzy information such as directory folder names and table names input by the user, automatically querying the database metadata dictionary, matching and returning a list of target data tables that meet the conditions). Subsequently, it automatically obtains the names, data types, and attributes of all columns in the target data tables and generates a column information mapping table for the user to select. An intelligent SQL syntax auto-construction engine replaces the process of manually writing SQL statements. After receiving the query conditions configured by the user through the interface, it traverses the condition tree using a recursive algorithm and automatically concatenates the SQL query statement. The adaptive syntax conversion capability based on data types ensures that the generated SQL query statement conforms to database specifications, avoiding syntax errors caused by human negligence. This solves the technical problem in related technologies where airfare query modes rely on the individual's ability to memorize data table structures and manually write SQL query statements, resulting in low query efficiency and a high risk of errors.

[0211] Optionally, the table name matching unit includes: a folder name receiving module for receiving the directory folder name input by the user; a list return module for returning a list of all table names under the directory folder name to the user if the user has not input any fuzzy table name information; a table name selection receiving module for receiving the first table name selection result from the user; a first name list acquisition module for obtaining a list of names of all columns of the target data table and the data type definitions of the columns from the database metadata dictionary based on the table name information in the first table name selection result and the directory folder name; a table name retrieval module for retrieving table names containing the fuzzy table name information in the folder space indicated by the directory folder name if the user has input fuzzy table name information, generating a candidate table name list for the user to choose from; receiving the second table name selection result from the user; and a second name list acquisition module for obtaining a list of names of all columns of the target data table and the data type definitions of the columns from the database metadata dictionary based on the table name information in the second table name selection result and the directory folder name.

[0212] Optionally, the data type determination unit includes: a condition tree structure receiving module, used to receive a multi-level condition tree structure configured by the user and call a data type conversion rule library, wherein the multi-level condition tree structure includes: column names, operators, and operation conditions; a data type determination module, used to determine the data type of the corresponding column of each node in the multi-level condition tree structure according to the column information mapping table; a first verification module, used to independently verify and concatenate each leaf node in the multi-level condition tree structure based on the data type of the corresponding column of each node to obtain a first processing result; a recursive assembly module, used to recursively assemble the non-leaf nodes in the multi-level condition tree structure based on the data type of the corresponding column of each node to obtain a second processing result; and a condition string output module, used to output a query condition string based on the first processing result and the second processing result.

[0213] Optionally, the first validation module includes: a single quote adjustment module, used to adjust the operation conditions to be enclosed in single quotes if the data type of the column corresponding to the current node is a character type; a single quote removal module, used to remove the single quotes in the operation conditions if the data type of the column corresponding to the current node is a numeric type; a format judgment module, used to judge the format based on the number of digits of the input number if the data type of the column corresponding to the current node is a time type, and call the string to date and time function according to the format of the input number; and a wildcard supplementation module, used to automatically supplement the specified wildcard symbols if the data type of the column corresponding to the current node is a pattern matching operator and the user does not specify the wildcards before and after.

[0214] Optionally, the recursive assembly module includes: an AND symbol connection module, used to connect the AND condition list child nodes to the AND symbol if the multi-level condition tree structure contains AND condition list child nodes; and an OR symbol connection module, used to connect the OR condition list child nodes to the OR symbol based on the AND condition list concatenation result in the multi-level condition tree structure, and to enclose the connection result in parentheses if the OR condition list child nodes are in the multi-level condition tree structure.

[0215] Optionally, the query statement construction unit includes: a query statement initialization module, used to construct the query statement and specify the preset query fields as asterisks in the query statement, or specify fields according to requirements, and specify the directory folder name and table name with fuzzy information in the data source clause; a string concatenation module, used to concatenate the query condition string after the query statement and detect whether the user has provided a sorting condition list and pagination parameters; a column name concatenation module, used to construct a sorting clause for the query result set according to the sorting condition list if the user has entered a sorting condition list, and concatenate the column names and column sorting identifiers; if the user has not specified a sorting condition list, a descending identifier is added by default; and a query statement output module, used to construct pagination logic according to the database dialect if the user has entered pagination parameters, convert the page size and page number into the corresponding row number limit or offset parameters, and output an executable SQL query statement.

[0216] Optionally, the query unit includes: a data value conversion module, used to convert data values ​​to string format if the column data type in the column information mapping table is numeric; a time object conversion module, used to convert time objects to standard string format if the column data type in the column information mapping table is date and time; a string format preservation module, used to preserve the column data type in the column information mapping table as a string if the column data type is character; a specific string replacement module, used to replace null values ​​in the column data mapping table with specific strings; a directory file concatenation module, used to extract directory names and file names and concatenate them into a specified directory name-file name format if the column data type in the column information mapping table is file pointer; and a column type return module, which returns only strings if the column data type in the column information mapping table is a target capacity data type, where the target capacity data type refers to a data type whose capacity data is greater than a preset capacity threshold.

[0217] The aforementioned intelligent query device for civil aviation data may also include a processor and a memory. The table name matching unit 41, data type determination unit 42, query statement construction unit 43, query unit 44, etc., are all stored in the memory as program units, and the processor executes the aforementioned program units stored in the memory to realize the corresponding functions.

[0218] The aforementioned processor contains a kernel, which retrieves the corresponding program units from memory. One or more kernels can be configured, and by adjusting kernel parameters, intelligent querying and visualization analysis of civil aviation data can be achieved.

[0219] The aforementioned memory may include non-permanent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM, and the memory includes at least one memory chip.

[0220] According to another aspect of the present invention, a computer-readable storage medium is also provided, the computer-readable storage medium including a stored computer program, wherein, when the computer program is running, it controls the device where the computer-readable storage medium is located to execute the intelligent query method for civil aviation data according to any one of the above embodiments.

[0221] According to another aspect of the present invention, an electronic device is also provided, including one or more processors and a memory, wherein the memory is used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors cause the one or more processors to implement the intelligent query method for civil aviation data according to any one of the above embodiments.

[0222] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the intelligent query method for civil aviation data described in various embodiments of this application.

[0223] This application also provides a computer program product, including a non-volatile computer-readable storage medium storing a computer program, which, when executed by a processor, implements the steps of the intelligent query method for civil aviation data described in various embodiments of this application.

[0224] Figure 5 This is a hardware structure block diagram of an electronic device (or mobile device) for an intelligent query method for civil aviation data according to an embodiment of the present invention. Figure 5 As shown, an electronic device may include one or more ( Figure 5 The processor 502 (which may include, but is not limited to, a microprocessor MCU or a programmable logic device FPGA, etc.) and a memory 504 for storing data may also be included. In addition, it may include: a display, an input / output interface (I / O interface), a universal serial bus (USB) port (which may be included as one of the ports of the I / O interface), a network interface, a keyboard, a power supply, and / or a camera. Those skilled in the art will understand that... Figure 5 The structure shown is for illustrative purposes only and does not limit the structure of the electronic device described above. For example, the electronic device may also include... Figure 5 The more or fewer components shown, or having the same Figure 5 The different configurations shown.

[0225] The sequence numbers of the above embodiments of the present invention are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0226] In the above embodiments of the present invention, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0227] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. The device embodiments described above are merely illustrative; for example, the division of units can be a logical functional division, and in actual implementation, there may be other division methods. For instance, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the displayed or discussed mutual coupling, direct coupling, or communication connection may be through some interfaces; the indirect coupling or communication connection between units or modules may be electrical or other forms.

[0228] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0229] Furthermore, the functional units in the various embodiments of the present invention can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0230] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0231] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. An intelligent query method for civil aviation data, characterized in that, include: In response to a user's fare query request, based on the fuzzy information of the directory folder name and / or table name input by the user, the target data table is matched in the database metadata dictionary, and the names and corresponding data type definitions of all columns in the target data table are obtained to generate a column information mapping table; Traverse each node in the query condition tree structure, determine the data type of the corresponding column of each node according to the column information mapping table, perform adaptive syntax conversion and logical operator concatenation based on the data type, and generate the query condition string; Based on the fuzzy information of the directory folder name and / or table name, the query condition string, the sorting conditions and pagination parameters input by the user, an executable SQL query statement is constructed; The SQL query statement is executed to obtain the original data result set. The original data result set is traversed, and the data values ​​are converted into a standardized string format according to the data type definition in the column information mapping table. The query response result is then output to the user terminal.

2. The intelligent query method for civil aviation data according to claim 1, characterized in that, The step of matching the target data table in the database metadata dictionary based on the fuzzy information of the directory folder name and / or table name input by the user includes: Receive the directory / folder name input by the user; If the user does not input the fuzzy information of the table name, then return a list of all table names under the directory folder name to the user; receive the first table name selection result from the user; based on the table name information in the first table name selection result and the directory folder name, obtain a list of names of all columns of the target data table and the data type definition of the columns from the database metadata dictionary; If the user has already entered the fuzzy information for the table name, then the system searches for table names containing the fuzzy information within the folder space indicated by the directory folder name, generates a list of candidate table names for the user to choose from, receives the second table name selection result from the user, and, based on the table name information in the second table name selection result and the directory folder name, obtains a list of names of all columns of the target data table and the data type definitions of the columns from the database metadata dictionary.

3. The intelligent query method for civil aviation data according to claim 1, characterized in that, The steps of traversing each node in the query condition tree structure, determining the data type of the corresponding column for each node based on the column information mapping table, performing adaptive syntax conversion and logical operator concatenation based on the data type, and generating the query condition string include: Receive the multi-level condition tree structure configured by the user and call the data type conversion rule library. The multi-level condition tree structure includes: column name, operator and operation condition. The data type of the corresponding column in the multi-level condition tree structure is determined based on the column information mapping table; Based on the data type of the corresponding column of each node, each leaf node in the multi-level condition tree structure is independently validated and spliced ​​to obtain the first processing result; Based on the data type of the corresponding column of each node, the non-leaf nodes in the multi-level condition tree structure are recursively assembled to obtain the second processing result. Based on the first processing result and the second processing result, the query condition string is output.

4. The intelligent query method for civil aviation data according to claim 3, characterized in that, Based on the data type of the corresponding column of each node, the steps of independently validating and concatenating each leaf node in the multi-level condition tree structure to obtain the first processing result include: If the data type of the column corresponding to the current node is character, adjust the operation condition to be enclosed in single quotes; If the data type of the column corresponding to the current node is numeric, remove the single quotes in the calculation conditions; If the data type of the column corresponding to the current node is time, determine the format based on the number of digits in the input number, and call the string to date and time function according to the input number format; If the data type of the column corresponding to the current node is a pattern matching operator and the user does not specify wildcards before or after it, the wildcard symbols will be automatically added.

5. The intelligent query method for civil aviation data according to claim 3, characterized in that, Based on the data type of the corresponding column of each node, the step of recursively assembling the non-leaf nodes in the multi-level condition tree structure includes: If the multi-level condition tree structure contains condition list child nodes, the result of concatenating the condition list child nodes will be connected with the symbol. If a sub-node of the condition list in the multi-level condition tree structure is connected with the concatenation result of the condition list by an OR symbol, and the connection result is enclosed in parentheses.

6. The intelligent query method for civil aviation data according to claim 1, characterized in that, The steps of constructing an executable SQL query statement based on the fuzzy information of the directory folder name and / or table name, the query condition string, the sorting conditions and pagination parameters input by the user, include: Construct a query statement and specify the preset query field as an asterisk in the query statement, or specify the field as required, and specify the table name of the directory folder name and the table name fuzzy information in the data source clause; The query condition string is appended to the query statement, and it is checked whether the user has provided a list of sorting conditions and pagination parameters. If the user has already entered the sorting criteria list, a sorting clause for the query result set is constructed based on the sorting criteria list, and the column names and column sorting identifiers are concatenated; if the user has not specified a sorting criteria list, a descending order identifier is added by default. If the user has already input the pagination parameters, the pagination logic is constructed according to the database dialect, the page size and page number are converted into the corresponding line number limit or offset parameters, and an executable SQL query statement is output.

7. The intelligent query method for civil aviation data according to claim 1, characterized in that, The step of converting data values ​​into a standardized string format according to the data type definition in the column information mapping table includes: If the data type of a column in the column information mapping table is numeric, convert the data value to string format. If the data type of a column in the column information mapping table is date and time, convert the time object to a standard string format; If the column data type in the column information mapping table is character type, it shall be kept in string format; If the column data content in the column information mapping table is empty, it will be replaced with a specific string; If the column data type in the column information mapping table is file pointer type, extract the directory name and file name, and concatenate them into the specified directory name-file name format; If the column data type in the column information mapping table is the target capacity data type, only the string is returned as the column type. The target capacity data type refers to a data type whose capacity data is greater than the preset capacity threshold.

8. An intelligent query device for civil aviation data, characterized in that, include: The table name matching unit is used to respond to the fare query request from the user terminal. Based on the directory folder name and / or fuzzy information of the table name input by the user terminal, it matches the target data table in the database metadata dictionary, obtains the names of all columns in the target data table and their corresponding data type definitions, and generates a column information mapping table. The data type determination unit is used to traverse each node in the query condition tree structure, determine the data type of the corresponding column of each node according to the column information mapping table, perform adaptive syntax conversion and logical operator concatenation based on the data type, and generate a query condition string. The query statement construction unit is used to construct an executable SQL query statement based on the fuzzy information of the directory folder name and / or table name, the query condition string, the sorting conditions and pagination parameters input by the user terminal; The query unit is used to execute the SQL query statement, obtain the original data result set, traverse the original data result set, convert the data values ​​into a standardized string format according to the data type definition in the column information mapping table, and output the query response result to the user terminal.

9. An electronic device, characterized in that, It includes one or more processors and a memory, the memory being used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors cause the one or more processors to implement the intelligent query method for civil aviation data as described in any one of claims 1 to 7.

10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by the processor, it implements the steps of the intelligent query method for civil aviation data as described in any one of claims 1 to 7.

Images