Discussion on the development of the common database query device

Data | database | experience
At present, the application system based on relational database is everywhere, whether it can provide a powerful data query function is an important consideration index of these systems. Many large database application software companies have their own queries. such as Ufida, SAP and so on. But their queries are used only for their own systems.

I have nothing to do, suddenly born to develop a general dongdong to play the idea of the tunnel has datagetter query the birth. I design for the ordinary users of the application system, not me and other IT technical personnel, it is designed to assume that the user does not understand the base table structure, do not understand any SQL statements.

Fight January, finally have small into, but dare not to stash, so borrow CSDN show to the public, if can enlighten people, then fortunately great!



Basic Concept Definition



















Business object:

has been stored in the definition of the original database. Base tables can be roughly divided into two classes in a database, one for defining entities (objects) and the other for defining relationships between entities (objects). A class of business objects must be able to find the corresponding base table definition. Each record in the base table represents a specific business object definition.

Characteristics:

A feature reflects an aspect of an object's characteristics. Features have the characteristics of passing and converging through object relationships. Features are classified into narrow and generalized features.

Narrow Features:

A narrow feature is the direct nature of an object. Each business field of each base table in the original database is a feature, also known as a basic feature.

Generalized characteristics:

In addition to the narrow features, it also includes all the characteristics and related derivative characteristics of an object through passing rules and aggregation rules.

Delivery Rules:

For objects A and B, if the object relationship between the objects is N:1, the characteristic of B can be passed to a, and the characteristic of B is directly regarded as a.

Convergence rules:

For objects A and B, if the relationship is 1:n, then the characteristic of B can be aggregated into the characteristics of a.

Derivative: A new feature that is derived from the performance of one or more characteristics of a single record in a certain fixed calculation, also known as the derived feature. This type of feature and aggregation characteristics are usually not directly in the original database, but to be implemented in a specialized programming. Therefore, the ability to support these two features is an important condition to judge the power of a counting tool.

















Based on the above concept, we can define the nature of user's fetching behavior as follows: It is the process that the user obtains the attention characteristic value of the attention object. For example, a contract manager needs a certain contract performance data one day, including the contract date, the contractor's name, the total amount of the contract, the cumulative cost, the cumulative amount of the project, the cumulative cost of this year, and the cumulative amount of work this year. In this case, a contract becomes the user's concern, and the date of contract signing becomes the focus of attention. Among these characteristics, the contract date, the contractor's name and the total amount of the contract are the basic characteristics, and the cumulative cost, the cumulative amount of the project, the cumulative cost of the current year, and the cumulative amount of the project this year are derived characteristics.

To sum up, we can know that the user needs characteristics may be in a base table, but also may be scattered in different base tables. This is the key and difficult point of data query development, and the two-layer structure based on business object-feature method is a good way to solve this problem.

The above is the theoretical basis for the development of Datagetter. Datagetter The user hides the structure information of the database and presents the business object and all its business characteristics directly to the user in a two-layer structure. A user who finds a business object can get all of its associated features without having to think about which base table it is scattered in the database. This greatly facilitates the user's fetching number. At the same time, the software also provides the extensible derivation computation method storehouse, the aggregation computation method storehouse, if the user has the new characteristic need, then simply registers in the corresponding table according to the stipulation. If you encounter more specific requirements, you can expand the relevant method library to meet the needs. The following is an introduction to the software to further illustrate the practical working principle of developing data query method based on business object-feature two-layer structure method.

Introduced



















(1) System frame Chart

































(2) Examples of software use



















Demo Scenario: An information system contains a contract management system, which has the function of registering the basic information of the contract and recording the information of each payment order of the contract. The original database base table structure of the system is as follows.























































































































































































































































Some business personnel want to obtain 2003 contract and the implementation of the situation, the specific content is: Contract code, contract name, contract date, contract contractor's name, contract amount, Contract accumulative payment amount. In such an example, we can know from the above base table that this concern for all contracts signed in 2002, while the focus on the characteristics of the contract code, contract name, contract date, the cumulative amount of contract payments for the basic characteristics of the contract contractor name called



















Transfer characteristics, while the contract cumulative payment amount is a convergence feature. The completion process instructions and illustrations for this enquiry are shown below.



















First step: Select the object category of interest, because the contract object is in the contract file cabinet so select the contract file cabinet. Then click Next.

The second step is to select the characteristics of concern. Note that the cumulative amount of payment that the user needs is already present in the feature list to the left, because it is registered in the feature table beforehand, so the user only needs to use it without considering how to complete the calculation of the cumulative value. Click Next when you have finished selecting.

The third step is to define the object filter criteria. You can define a filter condition either by mouse or by typing directly in the condition definition box.

The fourth step is to start the Datagetter software fetch engine. This step is simply to click the Finish key. To complete the start of the counting engine.



The fifth step is to view the results of the fetch and other operations, the results of this case are as follows. Datagetter software provides the ability to view and print results within the system, while saving the results as TXT format files. This feature can be used in other table processing software such as Excel to read the results to facilitate further analysis, processing, printing and so on.

























































(3) Characteristics of Datagetter software



















1. Developed in the Advanced Java language.

2. Multiple databases can be supported.

3. End user's 0 database knowledge requirements.

4.0 Operational training for end users.

5. End user's 0 user programming.

6. The relevant data that exists in multiple tables can be presented.

7. Speed of fetching.

8. The system provides an extensible multi-line algorithm library (e.g. cumulative, average, etc.). The user can obtain the result data of the multiline operation as simple as obtaining other basic data without using the operation method of the arithmetic function.

9. The system provides an extensible export Method library (generating new meaningful data from existing data, such as date data that can be exported for year data).

10. The system provides an extensible relational Computing method Library.

11. The system provides an extensible feature library.

12. The system can store data extraction results as text format. In order to meet the user's data extraction results into Excel and other special form processing software to complete the further processing, typesetting, printing and so on.

13. The results can be browsed in the system in Excel grid format.

14. The system provides the function of quick print extraction result.