WO2022242635A1

WO2022242635A1 - Management device for sql records for maintenance and application system

Info

Publication number: WO2022242635A1
Application number: PCT/CN2022/093284
Authority: WO
Inventors: 汪永松
Original assignee: 安翰科技(武汉)股份有限公司
Priority date: 2021-05-18
Filing date: 2022-05-17
Publication date: 2022-11-24
Also published as: CN113239014A

Abstract

Disclosed are a management device for SQL records for maintenance and an application system, the device comprising a service database connection module, a template definition module, a record management module and an API interface module. In the device, SQL commands or templates for maintenance are extracted from codes or configuration files and packaged into records that can be maintained by means of a user interface, then all of the records are managed in a unified way, improving management efficiency and reducing the complexity and costs of development and maintenance.

Description

Management device and application system for maintaining SQL records

(This application claims the priority of the Chinese patent application whose application date is May 18, 2021, application number is 202110538142.3, and the title of the invention is "management device and application system for maintaining SQL records", the entire content of which is incorporated by reference in this application.)

technical field

The invention relates to the field of computer technology, in particular to a management device and an application system for maintaining SQL records.

Background technique

For the application system, whether it is C/S (Client/Server, client/server) or B/S (Browser/Server, browser/server) structure, its essence is to provide users with information services. And data is the core of informatization, so informatization services can be narrowly understood as data services. From the level of system implementation, providing data services is a series of processes, usually including: requesting data → obtaining data → processing data → displaying data.

Business data is usually stored in a database system on a database server, and the vast majority of database systems still use relational databases (for example: Oracle, MySQL, etc.). The mode of interaction between the application system and the database system: first register the database driver, then call the API (Application Programming Interface, application programming interface) provided by the database driver to establish a connection with the database system, and then execute SQL (Structured Query Language, Structured query language) statements to perform database operations, usually including: creation and update of data tables, query, addition, update and deletion of records, etc.

For data access APIs, ODBC (Open Database Connectivity, open database connection) in the Windows environment and JDBC (Java Database Connectivity, Java database connection) in the JAVA environment are commonly used in the industry.

Therefore, from the level of system implementation, the management of data services can be simplified as the management of SQL statements. For example: querying student information service corresponds to querying students' SQL statement (SELECT); adding student record service corresponds to adding student's SQL statement (INSERT INTO); updating student information service corresponds to updating student's SQL statement (UPDATE).

The larger the system and the more complex the service, the more SQL statements will be used. From the perspective of system implementation, there are a large number of SQL statements in the system, which must be managed effectively, otherwise it will directly affect the maintainability and development efficiency of the system.

With the development of technical specifications (for example: ORM (Object Relational Mapping, Object Relational Mapping) framework, JPA (Java Persistence API, Java Persistence API) specification), access to data is no longer a relatively low-level JDBC API, replaced by It is a variety of persistence layer frameworks. Hibernate and MyBatis are the most commonly used persistence layer frameworks in the industry. The query statements they support are not native SQL, but SQL-like. Hibernate supports HQL (Hibernate Query Language, Hibernate Query Language) , MyBatis supports custom SQL.

In fact, the underlying mechanism of the persistence framework still executes SQL, the difference is that the syntax and definition of SQL are different. MyBatis is defined in the configuration file (XML), while Hibernate is defined directly in the code.

For such a definition method, once the application system adjusts the business rules, it will inevitably change the SQL. In the Hibernate framework, the code needs to be modified, recompiled and distributed; in the MyBatis framework, the configuration file needs to be modified, and the Web service needs to be restarted to reload the configuration. definition. Restarting services and redistributing codes, these operations will affect the provision of services for information systems, which can be regarded as a major impact.

Not only that, a large amount of SQL is flooded into configuration files and codes, which will directly increase the complexity of code maintenance and also increase the difficulty of testing. Moreover, the execution of SQL statements and business logic processing are mixed together, and it is impossible to accurately distinguish whether it is a problem of database access or a problem of business logic.

Contents of the invention

The object of the present invention is to provide a management device and application system for maintaining SQL records.

In order to achieve one of the above-mentioned purposes of the invention, one embodiment of the present invention provides a management device for maintaining SQL records, the device comprising:

A business database connection module, which is used to define the connection interface of the business database based on the connection information of the business database;

Template definition module, which is used to define the SQL record template for maintenance, and provides a management interface for using the SQL record template for maintenance to define the SQL record for maintenance;

A record management module, which is used to manage and maintain SQL records using the management interface, and perform data interaction with the corresponding business database through the connection interface;

The API interface module is used to provide corresponding API interfaces for application software according to the maintenance SQL records.

As a further improvement of an embodiment of the present invention, the connection interface is a JNDI identifier, and the JNDI identifier is a connection descriptor defining connection information of the service database.

As a further improvement of an embodiment of the present invention, the SQL record templates for maintenance include SQL record templates for new addition, SQL record templates for update, and SQL record templates for deletion;

The SQL records for maintenance include SQL records for new addition, SQL records for update and SQL records for deletion.

As a further improvement of an embodiment of the present invention, the parameters of the newly added SQL record template include the newly added SQL record identifier, the newly added SQL record title, the JNDI identifier and the target data table;

The parameters of the SQL record template for the update include the SQL record mark for the update, the SQL record title for the update, the JNDI mark, the target data table and the update condition;

The parameters of the deletion SQL record template include the deletion SQL record identifier, the deletion SQL record title, the JNDI identifier, the target data table and the deletion condition.

As a further improvement of an embodiment of the present invention, each parameter of the SQL record template for maintenance further includes a block identifier, and the block identifier is used to distinguish the business block to which the SQL record for maintenance belongs.

As a further improvement of an embodiment of the present invention, the parameters of the SQL record template for maintenance also include record status, the record status includes draft, pending review, on-shelf and off-shelf, and the corresponding maintenance user can be controlled by modifying the record status. Enable or disable the SQL record template. Among them, only the maintenance SQL record template whose record status is on the shelf can be used normally.

As a further improvement of an embodiment of the present invention, the device also includes:

A record database, which is used to store the SQL record for maintenance, after the record management module adds, deletes or modifies the SQL record for maintenance, the addition, deletion or modification of the SQL record for maintenance is synchronously stored in the record database.

As a further improvement of an embodiment of the present invention, the device includes a plurality of record management modules, and the API interface module is also configured to, when receiving multiple requests from application software for maintenance SQL records, In the idle state of the management module, the multiple requests are allocated to different record management modules, so as to realize load balancing of multiple record management modules.

In order to achieve one of the objectives of the above invention, an embodiment of the present invention provides an application system, which includes application software, a business database, and a management device for maintaining SQL records as described above.

As a further improvement of an embodiment of the present invention, the application software obtains an SQL command including the connection information of the business database through the management device, and issues the SQL command to the corresponding business database.

Compared with the prior art, the maintenance SQL record management device of the present invention extracts maintenance SQL commands or templates from codes or configuration files and packages them into records that can be maintained through the user interface, and then unifies all records Management, improve management efficiency, reduce the complexity and cost of development and maintenance. Further, since the management device has its own record database, it will be stored in the record database synchronously after the SQL records for maintenance are changed, so after the SQL records for maintenance are changed, there is no need to restart the application software, and the application software will not be affected. The provision of services reduces the operation and maintenance costs of application software.

Description of drawings

FIG. 1 is a schematic structural diagram of a management device for maintaining SQL records in the present invention.

Fig. 2 is a schematic structural diagram of the application system of the present invention.

Detailed ways

The present invention will be described in detail below in conjunction with specific embodiments shown in the accompanying drawings. However, these embodiments do not limit the present invention, and any structural, method, or functional changes made by those skilled in the art according to these embodiments are included in the protection scope of the present invention.

SQL commands for maintenance refer to SQL commands for maintaining the database, including SQL commands for adding, SQL commands for updating, and SQL commands for deleting. The SQL command for adding is used to add records to the target database, the SQL command for updating is used to modify the matching records of the target database according to the matching conditions, and the SQL command for deleting is used to match the records of the target database according to the matching conditions to delete.

The SQL command template for maintenance refers to the SQL command for maintenance without specific parameters, for example: insert into XXX(?1,?2,?3)values.

Since a large number of SQL statements (mainly including SQL commands and SQL command templates) are flooded into configuration files and codes, it will directly increase the complexity of application software code maintenance and the difficulty of application system testing.

Therefore, the present invention provides a management device for maintaining SQL records. The device extracts these SQL statements used for maintenance from codes or configuration files and packages them into SQL records for maintenance, and performs unified maintenance and management on these records. , improve management efficiency, and reduce the complexity and cost of development and maintenance. Wherein, the maintenance SQL record encapsulates the maintenance SQL command template.

As shown in Fig. 1, the management device of the present invention includes a business database connection module, a template definition module, a record management module and an API interface module.

Business database connection module

The business database connection module is used to define the connection interface of the business database based on the connection information of the business database. Specifically, the connection interface is a JNDI (Java Naming and Directory Interface, Java Naming and Directory Interface) identifier, and the JNDI identifier is a connection descriptor defining the connection information of the service database. The following code is an example of defining a JNDI identifier. After the definition is complete, the JNDI description string is java:comp/env/jdbc/FOO:

<Resource name="jdbc/FOO"

scope="Shareable"

auth="Container"

factory="org.apache.naming.factory.BeanFactory"

type="com.mchange.v2.c3p0.ComboPooledDataSource"

driverClass="com.mysql.jdbc.Driver"

jdbcUrl="jdbc:mysql://<ip>:<port>/<db>?..."

password="xxxx"

user="xxxx"

.../>.

It should be noted that the connection information of different business databases is defined as different JNDI identifiers, and subsequently by selecting different JNDI identifiers, the corresponding business databases are connected, so that the device of the present invention can support different data sources and different databases type.

template definition module

The template definition module is used for defining a maintenance SQL record template, and provides a management interface for using the maintenance SQL record template to define a maintenance SQL record.

It should be noted that the SQL record templates for maintenance include SQL record templates for new addition, SQL record templates for update, and SQL record templates for deletion; the SQL records for maintenance include SQL records for new addition, SQL records for update, and SQL record templates for deletion. SQL records.

The SQL record template for maintenance is used to define the SQL record for maintenance. The parameters of each SQL record template for maintenance include the above-mentioned JNDI identifier. The corresponding maintenance SQL command can be connected to the corresponding business database through the JNDI identifier.

The parameters of each SQL record template for maintenance include:

The parameter of the described new SQL record template includes the new SQL record identification, the newly added SQL record title, JNDI identifier and target data table;

The parameters of the SQL record template for the update include the SQL record mark for the update as the primary key PK, the SQL record title for the update, the JNDI mark, the target data table and the update condition;

The parameters of the deletion SQL record template include the deletion SQL record identifier as the primary key PK, the deletion SQL record title, the JNDI identifier, the target data table and the deletion condition.

Furthermore, in the case of a large number of SQL records for maintenance, SQL records can be isolated and managed according to business sectors by adding sector identifiers. therefore. The parameters of the SQL record template for maintenance also include a block ID, and the block ID is used to distinguish the business block to which the SQL record for maintenance belongs. For example: Divide into two sections by order and commodity, the SQL records related to the order are collected under the order section, and the SQL records related to the product are collected under the commodity section. This separation is also convenient for the expansion of permissions. For example, order-related personnel can only manage the SQL records under the order section, and cannot involve the SQL records under the commodity section.

Further, the parameters of the SQL record template for maintenance also include a record status, and the record status includes draft, pending review, on-shelf and off-shelf, and the activation or shutdown of the corresponding maintenance SQL record template is controlled by modifying the record status. Among them, only the SQL record templates for maintenance whose record status is listed can be used normally. After the maintenance SQL record is generated, the default status is draft. After the review is passed, the status can be changed to Shelving, and the application system can use this SQL record to issue SQL commands after being put on the shelf. In addition, when the SQL record is not needed temporarily, its status can be changed to off-shelf.

Table 1 below is an example of a newly added SQL record template:

Table 1: tab_insert_template (newly defined table with SQL record template)

Among them, FK is specifically a Foreign Key (foreign key), indicating that there is a reference relationship between this item and non-self external data. In this example, for the plate identifier ("sub_sys") in Table 1, there is a corresponding plate definition table, and the plate identity will refer to the ID in the plate definition table.

For the target data table ("tbl"), that is, the data table to which new records will be added, the name of the data table is obtained through online synchronization of the data table in the business database connection defined by the JNDI identifier.

The record status ("status") is used to maintain the status control of the SQL record template, and only the SQL record template in the shelf state can be referenced.

According to the SQL records for maintenance, the server will eventually generate a complete new SQL command according to the corresponding SQL command template:

INSERT INTO<tbl>(co11,col2,col3,...) VALUES('col1 value','col2 value','col3 value',...)

Among them, "co11, col2, col3, ..." are column names, "col1 value', 'col2 value', 'col3 value', ..." are column values, and both column names and column values are passed in through context parameters.

Table 2 below is an example of an SQL record template for updating:

Table 2: tab_update_template (update table with SQL record template definition)

For record update, it is generally updated according to filter conditions. Table 2 provides two condition methods: one is to filter according to specified column values (for example: according to the primary key value); the other is to filter according to user-defined conditions (for example: combined conditions). According to the maintenance SQL records, the final server will generate a complete update SQL statement according to the corresponding SQL template:

UPDATE<tbl>SET col1='col1 value', col2='col2 value',...WHERE<filter condition>

Among them, the column name and column value are passed in through the context parameter; the filter condition is generated according to the context parameter.

Table 3 below is an example of a template for deleting SQL records:

Table 3: tab_delete_template (delete table defined with SQL record template)

Table 3: tab_delete_template (delete table defined with SQL record template)

The definition of the SQL template for deletion is the same as that for update. According to the maintenance SQL records, the final server will generate a complete delete SQL statement according to the corresponding SQL template:

DELETE FROM<tbl>WHERE<filter conditions>

Among them, the filter condition is generated according to the context parameter.

Records Management Module

The record management module is used to use the management interface to manage maintenance SQL records, and perform data interaction with the corresponding business database through the connection interface. The record management module manages the records according to the SQL record template for maintenance (the management operations include adding, deleting, modifying and querying the SQL records, etc.), and the record management module can also receive the application software downloads through the API interface module. Send specific parameters, generate specific SQL commands, and return them to the application software.

In a preferred embodiment, the record management module includes a visual user interface for administrators to perform management operations, and the user interface is preferably provided through a web page.

API interface module

The API interface module is used for providing corresponding API interfaces for application software according to the maintenance SQL records. Generally, the corresponding API interface is provided for the application software by maintaining the record identifier of the SQL record. The application software obtains the definition information of the corresponding SQL record according to the record identifier, and then obtains the specific SQL command to be executed from the API interface according to the record identifier and the delivered specific parameters, and sends it to the business database.

In a preferred embodiment, the device further includes a record database.

record database

The record database is used to store the SQL record for maintenance. After the record management module adds, deletes or modifies the SQL record for maintenance, the addition, deletion or modification of the SQL record for maintenance is synchronously stored in the record database. Therefore, after the SQL record for maintenance is changed, there is no need to restart the application software, which does not affect the provision of application software services, and reduces the operation and maintenance cost of the application software.

Further, the device includes a plurality of record management modules, and the API interface module is also configured to, when receiving multiple requests from application software for maintaining SQL records, according to the idle status of the plurality of record management modules, set The multiple requests are allocated to different record management modules, so as to achieve load balancing among multiple record management modules.

As shown in Fig. 2, the present invention also provides a kind of application system, described system comprises application software, business database and the management device of above-mentioned maintenance SQL record, wherein:

The application software obtains the SQL command including the connection information of the business database through the management device, and issues the SQL command to the corresponding business database.

It should be noted that there may be multiple application software, and there may be multiple service databases.

It should be understood that although this description is described according to implementation modes, not each implementation mode only contains an independent technical solution, and this description in the description is only for clarity, and those skilled in the art should take the description as a whole, and each The technical solutions in the embodiments can also be properly combined to form other embodiments that can be understood by those skilled in the art.

The series of detailed descriptions listed above are only specific descriptions for feasible implementations of the present invention, and they are not intended to limit the protection scope of the present invention. Any equivalent implementation or implementation that does not depart from the technical spirit of the present invention All changes should be included within the protection scope of the present invention.

Claims

A management device for maintaining SQL records, characterized in that the device comprises:

A business database connection module, which is used to define the connection interface of the business database based on the connection information of the business database;

Template definition module, which is used to define the SQL record template for maintenance, and provides a management interface for using the SQL record template for maintenance to define the SQL record for maintenance;

A record management module, which is used to manage and maintain SQL records using the management interface, and perform data interaction with the corresponding business database through the connection interface;

The API interface module is used to provide corresponding API interfaces for application software according to the maintenance SQL records.
According to the management device of claim 1 maintenance SQL record, it is characterized in that:

The connection interface is a JNDI identifier, and the JNDI identifier is a connection descriptor defining connection information of the service database.
According to the management device of claim 2 maintenance SQL record, it is characterized in that:

The SQL record template for maintenance includes a new SQL record template, an update SQL record template and a deletion SQL record template;

The SQL records for maintenance include SQL records for new addition, SQL records for update and SQL records for deletion.
According to the management device of the described maintenance SQL record of claim 3, it is characterized in that:

The parameters of the newly added SQL record template include newly added SQL record identifiers, newly added SQL record titles, JNDI identifiers and target data tables;

The parameters of the SQL record template for the update include the SQL record mark for the update, the SQL record title for the update, the JNDI mark, the target data table and the update condition;

The parameters of the deletion SQL record template include the deletion SQL record identifier, the deletion SQL record title, the JNDI identifier, the target data table and the deletion condition.
According to the management device of claim 4 maintenance SQL records, it is characterized in that:

The parameters of each of the SQL record templates for maintenance also include a block ID, and the block ID is used to distinguish the business block to which the SQL record for maintenance belongs.
According to the management device of claim 4 maintenance SQL records, it is characterized in that:

The parameters of the SQL record template for maintenance also include a record status, and the record status includes draft, pending, on-shelf and off-shelf, and the activation or deactivation of the corresponding maintenance SQL record template is controlled by modifying the record status, wherein , only the maintenance SQL record templates whose record status is listed can be used normally.
The management device for maintaining SQL records according to claim 1, wherein said device also includes:

A record database, which is used to store the SQL record for maintenance, after the record management module adds, deletes or modifies the SQL record for maintenance, the addition, deletion or modification of the SQL record for maintenance is synchronously stored in the record database.
According to the management device of the described maintenance SQL record of claim 7, it is characterized in that:

The device includes a plurality of record management modules, and the API interface module is further configured to, when receiving multiple requests from application software for maintaining SQL records, according to the idle state of the plurality of record management modules, Requests are allocated to different record management modules, so as to achieve load balancing of multiple record management modules.
An application system, characterized in that:

The system includes application software, a business database and a management device for maintaining SQL records as claimed in claim 1 .
The application system according to claim 9, characterized in that:

The application software obtains the SQL command including the connection information of the business database through the management device, and issues the SQL command to the corresponding business database.