A kind of data access system and method thereof based on view
Technical field
The present invention relates to computer data access technique field, especially relate to a kind of data access system and method thereof based on view that is applied to the computerized information field.
Background technology
Current, in the data access based on relational database, mainly contain several lower several methods:
(1) embedded SQL code
Method employed in figure 1 is that SQL (Structured Query Language, Structured Query Language (SQL)) code directly is embedded in service class, and this strategy is can fast Development for mini-system.But its shortcoming is also obviously, and the SQL code is to write in source code, and this just means any one simple change, for example changes a row name of showing in database, must revise source code.This modification still can be accepted for a mini-system, if for big-and-middle-sized system, this defective is very serious.
(2) service class is set up independently data class
Method employed in figure 2 is that the SQL code of service class writes on independently in one or more data class or in storing process.This method makes great progress with respect to the SQL code is embedded in service class, but this method equally also is only applicable to service class less than the mini-system of 40 to 50 left and right.Simultaneously, when database simply changed, corresponding data class will recompilate.This method has just been accomplished the pressure source code, and treatment S QL has been placed on data class.Adopt this method, supporting to lack dirigibility aspect the corresponding same mapping definition of a plurality of object models, the developer must manual each object model of mapping.Worse, in enterprise-level application, this method is can not be well in the isomery component environment independent, and because method is static, that is: corresponding mapping model of class is so can not expand.
(3) service class is set up the data class of sharing
Method employed in figure 3 is to set up a shared data class for all service class to realize data access, is actually for set up the improvement of independent data class methods for each service class.Compare with above-mentioned independent data class methods, shared data class has been realized the reusability of code.Shared data class figure shown in Figure 4, wherein method parameter tableName is table name, two static array Name and Value deposit respectively row name and train value.Concrete grammar comprises inquiry, increase, deletion and revises, according to table name parameter and two static arrays, utilizes the dynamic sql technology to realize the automatic generation of SQL code.Share the data class method and broken away from heavy SQL statement, realized the automatic generation of SQL statement.From this angle, share the data class method and be greatly improved, only be adapted to single table handling but share data class, inapplicable multilist operation, when for example needing in query manipulation to obtain data from many tables of database, above-mentioned shared data class can't be realized.
In the middle of prior art, a kind of is to be applied on 05 10th, 2007 by LANG CHAO GENERASOFT Co Ltd with the immediate technology of the present invention, on November 07th, 2007 is open, and publication number is that the Chinese invention patent application " mapping conversion method between data access layer XML formatted data and relation data " of CN101067814A is disclosed.This invention is mainly binding mode, the method for explanation data access layer.It provides the mapping conversion method between a kind of data access layer XML formatted data and relation data, two stages when the method mainly comprises when design and operation, wherein: the stage when design, carry out modeling for the data structure of relation data and mapping and the conversion of object and relation data, be described as two kinds of metadata of data object and data model, data object is that relation data carries out modeling, the data structure of the table in database and view is out abstract, its title, field attribute etc. are described.Stage when operation, by data accessing engine, the metadata information of definition during according to design, data message in conjunction with input, SQL (Structured Query Language (SQL)) statement of dynamic constructs database access visit, accessing database is completed the function that additions and deletions change the data access layer of looking into automatically, and the result of dynamic construction process is carried out appropriate buffer memory.
(1) in embedded SQL code method, the SQL code writes in source code, and any one simple change must be revised source code;
(2) in service class is set up independently the data class method, the SQL code writes on independently in one or more data class or in storing process.Adopt this method, supporting to lack dirigibility aspect the corresponding same mapping definition of a plurality of object models, the developer must manual each object model of mapping.Worse, in enterprise-level application, this method is can not be well in the isomery component environment independent, and because method is static, that is: corresponding mapping model of class is so can not expand;
(3) set up in the data class method of sharing in service class, set up a shared data class for all service class and realize data access, utilize the dynamic sql technology to realize the automatic generation of SQL code.Share the data class method and broken away from heavy SQL statement, realized the automatic generation of SQL statement.From this angle, share the data class method and be greatly improved, be only applicable to single table handling but share data class, be not suitable for the multilist operation, when for example needing in query manipulation to obtain data from many tables of database, above-mentioned shared data class can't be realized.
(4) when the user uses the method for CN101067814A application for a patent for invention to carry out the list data operation, if the data source of form is following situation:
(a) use the view of DISTINCT operational character in the SELECT statement;
(b) use the view of the aggregate functions such as similar SUN or MAX;
(c) comprise GROUP BY, ORDER BY clause's view;
(d) use the view of subquery in the SELECT list;
(e) view of a plurality of tables of associating.
Several class views of above-mentioned this can not upgrade by SQL statement in the ordinary course of things, for example can't operate the viewdata that a plurality of physics tables form with a stsndard SQL (for example Update);
When data source has view and physics table, the developer not only wants the table definition in acquaint with data storehouse, also will be familiar with a large amount of view definitions, has increased development difficulty.
Summary of the invention
The invention provides a kind of data access system based on view and method thereof, the SQL code does not appear in this system and method thereof in source code file, realize that the MDAC object to the mapping of relational database, has reduced developer's quantities effectively, can shorten the whole construction cycle.Whole system is easy to study and operation simultaneously.
The invention provides a kind of technic relization scheme of the data access system based on view, a kind of data access system based on view comprises: interface assembly, data access layer, database interface, relational database link block, relational database;
Interface assembly is connected with data access layer, and sends user message to data access layer;
Data access layer comprises Object Creation assembly and MDAC, the Object Creation building component MDAC of data access layer, and MDAC comprises complex view assembly and simple view assembly;
MDAC produces the SQL action statement, and MDAC is connected with database interface, obtains database information by the XML configuration file, obtains simultaneously database connection object;
Database interface is connected with the relational database link block, by the relational database link block, the SQL action statement is passed to relational database, relational database is carried out the SQL action statement, and by the relational database link block, the defined query statement of data attribute that obtains in the MDAC object being passed to relational database, relational database is carried out query statement;
The relational database link block according to query statement with the data transmission returned to MDAC;
MDAC transfers to interface assembly with the data source of obtaining and shows, the interface assembly refresh data, and notify the user.
As the further improvement of a kind of data access system technical scheme based on view of the present invention, the complex view assembly comprises the first central control unit, a SQL operation maker, the first view generation device, INSTEAD OF trigger maker, the first dynamic sql assembly unit maker, the first connector, the first public attribute set;
The first central control unit receives user message, return to user data or information, the analyzing XML mapped file also writes the first public attribute set, to a SQL operation maker, the first view generation device, INSTEAD OF trigger maker sending action message, and receive a SQL operation maker, the first view generation device, the message of INSTEAD OF trigger maker and returning results of relational database;
The one SQL operation maker generates SQL, and is sent to relational database by the first connector and database interface;
The first view generation device creates according to different relational databases the dynamic sql that assembly relies on view;
INSTEAD OF trigger maker creates a dynamic sql that relies on the INSTEAD OF trigger that operates on view at assembly according to different relational databases;
Dynamic sql assembly unit maker carries out the assembly unit of SQL according to the public attribute value of the first public attribute set output, realizes dynamic sql;
The first connector is connected with database interface;
The first public attribute set storage is from the relevant information of the first central control unit analyzing XML mapped file.
As the further improvement of a kind of data access system technical scheme based on view of the present invention, the simple view assembly comprises the second central control unit, the 2nd SQL operation maker, the second view generation device, the second dynamic sql assembly unit maker, the second connector, the second public attribute set;
The second central control unit receives user message, return to user data or information, the analyzing XML mapped file also writes the public attribute set, and to the 2nd SQL operation maker and the second view generation device sending action message, receive the 2nd SQL operation maker and the message of the second view generation device and returning results of database;
The 2nd SQL operation maker generates SQL, and is sent to relational database by the second connector and database interface;
The second view generation device creates according to different relational databases the dynamic sql that assembly relies on view;
The second dynamic sql assembly unit maker carries out the assembly unit of SQL according to the public attribute value of the second public attribute set output, realizes dynamic sql;
The second connector is connected with database interface;
The second public attribute set storage is from the relevant information of the second central control unit analyzing XML mapped file.
The present invention also provides a kind of above-mentioned technic relization scheme that carries out the method for data access based on the data access system of view that utilizes, and the method comprises the following steps:
S101: interface assembly sends to data access layer and comprises that path, MDAC object identity, the MDAC of concrete operation information, view component type identification, XML mapped file and XML configuration file obtain the user message of Data Identification;
S102: the Object Creation assembly of data access layer creates a data access component object according to the view component sign, the MDAC object comprises complex view component object or simple view component object, and completes the initial work of MDAC object;
S103: the MDAC object is completed initial work according to the type of complex view assembly or simple view assembly, and according to the operation information attribute, if data acquisition, executing data acquisition process; If data deletion, executing data delete procedure; If data increase or revise, executing data increases or modification process, produces a SQL action statement by MDAC at last;
S104: MDAC object connection data bank interface, obtain database information by the XML configuration file, obtain simultaneously a database connection object;
S105: the relational database link block passes to relational database by database connection object with the SQL action statement, and relational database is carried out the SQL action statement;
S106: the relational database link block passes to relational database by database connection object with the defined query statement of data attribute that obtains in the MDAC object, and relational database is carried out query statement;
S107: database connection object is passed to the MDAC object according to query statement with the data of returning, and after complete operation, this database connection object is closed;
S108: the MDAC object offers the interface assembly object with the data source of obtaining and shows, this MDAC object of system-kill;
S109: interface assembly object refresh data, and notify the user.
As the further improvement of a kind of data access method technical scheme based on view of the present invention, comprise complex view component object initialization procedure based on the data access method of view, this process comprises the following steps:
S201: the first central control unit writes user message respectively in the public attribute set of component object, comprises concrete operations message attributes, XML mapped file path attribute, XML Profile Path attribute, object identity attribute, obtains the Data Identification attribute or obtains the query condition value of data;
S202: the first central control unit finds the XML mapped file according to the XML mapped file path attribute of component object, realize the location in the XML mapped file according to the object identity attribute, find corresponding data block, the first central control unit carries out XML to data block and resolves work, and the public attribute that physics table name, Table Properties etc. is write object is concentrated;
S203: the first central control unit sends to a SQL operation maker and obtains the Data Identification attribute message, call the first dynamic sql assembly unit maker and select query statement of the first public attribute set Dynamic Generation by a SQL operation maker, the one SQL operation maker returns to the first central control unit with select query statement message, and the first central control unit writes the select query statement obtaining in data attribute of object again;
S204: the first central control unit sends Query Database view message to a SQL operation maker, call by a SQL operation maker SQL whether view that the first dynamic sql assembly unit maker and the first public attribute set Dynamic Generation component object rely on exists, and send to the first connector;
S205: after the first connector connection data bank interface, create a database connection object, relational database is carried out the SQL whether the inquiry view exists, and result is returned to the first connector, and the first connector is passed to the first central control unit with message; If otherwise the first connector is overtime, the first central control unit sends an error message, and flow process finishes;
S206: the first central control unit is received the message of the first connector, if view existence, direct execution in step S211; Otherwise the first connector cuts out the database connection object of new establishment by database interface, the first central control unit sends to the first view generation device and creates view message simultaneously, the interface of the first view generation device obtains the relational database type according to the XML configuration file, select corresponding relational database view generation assembly, SQL who creates view of this component call first dynamic sql assembly unit maker and the first public attribute set Dynamic Generation, and this SQL is sent to the first connector;
S207: the first connector is received the SQL that creates view, and the connection data bank interface creates a database connection object, and relational database is carried out and created view SQL, and result is returned to the first connector, and the first connector is passed to the first central control unit with message; If otherwise the first connector is overtime, the first central control unit sends an error message, and flow process finishes;
S208: the first central control unit is received the message that view creates, closed the database connection object of new establishment by database interface by the first connector, send to INSTEAD OF trigger maker simultaneously and create INSTEAD OF trigger message, the interface of INSTEAD OF trigger maker obtains the relational database type according to the XML configuration file, select corresponding relational database INSTEAD OF trigger formation component, SQL who generates INSTEAD OF trigger of this component call first dynamic sql assembly unit maker and the first public attribute set Dynamic Generation, and this SQL is sent to the first connector,
S209: the first connector is received the SQL that creates INSTEAD OF trigger, the connection data bank interface, create a database connection object, relational database is carried out and is created INSTEAD OF trigger SQL, result is returned to the first connector, and the first connector is passed to the first central control unit with message; If otherwise the first connector is overtime, the first central control unit sends an error message, and flow process finishes;
S210: the first central control unit is received the message that INSTEAD OF trigger creates;
S211: the first central control unit sends closing information to the first connector, closes new creation database connecting object by the first connector by database interface, sends simultaneously the completed information of initialization, and this flow process finishes.
As the further improvement of a kind of data access method technical scheme based on view of the present invention, comprise complex view component object data acquisition based on the data access method of view, this process comprises the following steps:
S301: if the view component object does not carry out initialization, first carry out the initialization flow process;
S302: the first central control unit sends data acquisition information to a SQL operation maker;
S303: a SQL operation maker is received data acquisition information, and the data acquisition attribute of object is sent to the first connector;
S304: after the first connector connection data bank interface, create a database connection object, relational database is carried out a select and is obtained data on the view that component object relies on, and result is returned to the first connector, and the first connector is passed to the first central control unit with data set; If otherwise the first connector is overtime, the first central control unit sends an error message, and flow process finishes;
S305: after the first central control unit is received data set, send message to the first connector, closed the database connection object of new establishment by the first connector by database interface;
S306: the first central control unit is to interface assembly object transfer data, interface assembly object refresh data, and after notifying the user, this flow process finishes.
As the further improvement of a kind of data access method technical scheme based on view of the present invention, comprise complex view component object data delete procedure based on the data access method of view, this process comprises the following steps:
S401: the initial work flow process of first carrying out object;
S402: the first central control unit is deletion work from the concrete operations determined property, sends deletion information to a SQL operation maker;
S403: a SQL operation maker receives deletion information, at first obtains the parent table attribute according to the public attribute collection, and secondly the physics table of determination component object is multilist or single table, if single table is directly carried out S406; If multilist, a SQL operation maker generates a SQL who obtains parent table master external key, sends to the first connector;
S404: after the first connector connection data bank interface, create a database connection object, relational database is carried out the SQL that obtains parent table master external key, and result is returned to the first connector, and the first connector is passed to the first central control unit with message; If otherwise the first connector is overtime, the first central control unit sends an error message, and flow process finishes;
S405: the first central control unit is received the main foreign key information of parent table, close the database connection object of new establishment by the first connector notification data bank interface, simultaneously main foreign key information is write the first public attribute set, write complete after, the first central control unit sends main external key success obtaining information to a SQL operation maker;
S406: a SQL operation maker calls the first dynamic sql assembly unit maker and public attribute set, generates the deletion SQL of an operation parent table, realizes the information deletion of single table or multilist;
S407: after the first connector connection data bank interface, create a database connection object, relational database is carried out the deletion SQL of an operation parent table, and result is returned to the first connector, and the first connector is passed to the first central control unit with message; If otherwise the first connector is overtime, the first central control unit sends an error message, and flow process finishes;
S408: the first central control unit is received deletion action message, closes the database connection object of new establishment by the first connector notification data bank interface, sends obtaining information to a SQL operation maker simultaneously;
S409: after the data acquisition flow process of executive module object, flow process finishes.
As the further improvement of a kind of data access method technical scheme based on view of the present invention, comprise that based on the data access method of view complex view component object data increase and modification process, this process comprises the following steps:
S501: the initial work flow process of first executive module object;
S502: the first central control unit is to increase datamation or Update Table work from the concrete operations determined property, sends increase information to a SQL operation maker;
S503: a SQL operation maker receives increase information, produces one by the first dynamic sql assembly unit maker and is relied on the insertion statement that operates on view at component object, realizes increasing data and Update Table work, realizes the renewal of view;
S504: after the first connector connection data bank interface, create a database connection object, relational database is carried out and is inserted statement, and result is returned to the first connector, and the first connector is passed to the first central control unit with message; If otherwise the first connector is overtime, the first central control unit sends an error message, and flow process finishes;
S505: the first central control unit is received update message, closes the database connection object of new establishment by the first connector notification data bank interface, sends obtaining information to a SQL operation maker simultaneously;
S506: after the data acquisition flow process of executive module object, flow process finishes.
As the further improvement of a kind of data access method technical scheme based on view of the present invention, comprise simple view component object initialization procedure based on the data access method of view, this process comprises the following steps:
S601: the user message that the second central control unit will comprise concrete operations message attributes, XML mapped file path attribute, XML Profile Path attribute, object identity attribute, obtain the Data Identification attribute, obtain the query condition value of data writes respectively the public attribute of component object and concentrates;
S602: the second central control unit finds the XML mapped file according to the XML mapped file path attribute of component object, secondly realize the location in the XML mapped file according to the object identity attribute, find corresponding data block, the second central control unit begins that data block is carried out XML and resolves work, and the public attribute that physics table name, Table Properties etc. is write object is concentrated;
S603: the second central control unit sends to the 2nd SQL operation maker and obtains the Data Identification attribute message, call the second dynamic sql assembly unit maker and select query statement of the second public attribute set Dynamic Generation by the 2nd SQL operation maker, the 2nd SQL operation maker returns to the second central control unit with select query statement message, and the second central control unit writes the select query statement obtaining in data attribute of object again;
S604: the second central control unit sends Query Database view message to the 2nd SQL operation maker, call by the 2nd SQL operation maker the SQL whether view that the second dynamic sql assembly unit maker and the second public attribute set Dynamic Generation component object rely on exists, and send to the second connector;
S605: after the second connector connection data bank interface, create a database connection object, relational database is carried out the SQL whether the inquiry view exists, and result is returned to the second connector, and the second connector is passed to the second central control unit with message; If otherwise the second connector is overtime, the second central control unit sends an error message, and flow process finishes;
S606: the second central control unit is received the message of the second connector, if view existence, direct execution in step S609; Otherwise the second connector cuts out new creation database connecting object by database interface, the second central control unit sends to the second view generation device and creates view message simultaneously, the interface of the second view generation device obtains the relational database type according to the XML configuration file, select corresponding relational database view generation assembly, SQL who creates view of this component call second dynamic sql assembly unit maker and the second public attribute set Dynamic Generation, and this SQL is sent to the second connector;
S607: the second connector is received the SQL that creates view, and the connection data bank interface creates a database connection object, and relational database is carried out and created view SQL, and result is returned to the second connector, and the second connector is passed to the second central control unit with message; If otherwise the second connector is overtime, the second central control unit sends an error message, and flow process finishes;
S608: the second central control unit is received the message that view creates;
S609: the second central control unit sends closing information to the second connector, closes new creation database connecting object by the second connector by database interface, sends simultaneously the completed information of initialization, and this flow process finishes.
As the further improvement of a kind of data access method technical scheme based on view of the present invention, comprise simple view component object data delete procedure based on the data access method of view, this process comprises the following steps:
S701: the initial work flow process of first executive module object;
S702: the second central control unit is deletion work from the concrete operations determined property, sends deletion information to the 2nd SQL operation maker;
S703: the 2nd SQL operation maker receives deletion information, calls the second dynamic sql assembly unit maker and the second public attribute set, generates one at the deletion SQL that relies on the enterprising line operate of view, realizes information deletion;
S704: after the second connector connection data bank interface, create a database connection object, relational database is carried out the deletion SQL that an operation relies on view, and result is returned to the second connector, and the second connector is passed to the second central control unit with message; If otherwise connector is overtime, the second central control unit sends an error message, and flow process finishes;
S705: the second central control unit is received deletion action message, closes the database connection object of new establishment by the second connector notification data bank interface, sends obtaining information to the 2nd SQL operation maker simultaneously;
S706: after the data acquisition flow process of executive module object, flow process finishes.
Described a kind of based on the data access system of view and the technical scheme of method thereof by using the invention described above embodiment, reached following technique effect:
(1) utilize XML configuration file and XML mapped file to generate dynamic sql, the SQL code do not occur in source code file, realize that the MDAC object is to the mapping of relational database; (2) be simple and easy to use concerning the operator, the quantities that provides simultaneously share service effectively to reduce the developer can shorten the whole construction cycle; (3) developer needs only the table definition in acquaint with data storehouse, and view definition is by the MDAC Object Operations.View in database is transparent to the developer; (4) developer is when configuration XML mapped file, according to treaty rule, as long as the physics table in configuration database, need not be concerned about the data source of actual generation.
Description of drawings
In order to be illustrated more clearly in the embodiment of the present invention or technical scheme of the prior art, the below will do to introduce simply to the accompanying drawing of required use in embodiment or description of the Prior Art, apparently, accompanying drawing in the following describes is only some embodiments of the present invention, for those of ordinary skills, under the prerequisite of not paying creative work, can also obtain according to these accompanying drawings other accompanying drawing.
Fig. 1 is that the described SQL code of 1 one kinds of embodiments of prior art is embedded in the data access system structural representation in service class;
Fig. 2 is the data access system structural representation that 2 one kinds of described service class of embodiment of prior art are set up the independent data class;
Fig. 3 is that the described service class of 3 one kinds of embodiments of prior art is set up the data access system structural representation of sharing data class;
Fig. 4 is the described data access system fundamental diagram based on view of a kind of embodiment of the present invention;
Fig. 5 is the functional structure chart of the described data access system complex view assembly based on view of a kind of embodiment of the present invention;
Fig. 6 is the functional structure chart of the described data access system simple view assembly based on view of a kind of embodiment of the present invention;
wherein, the 1-interface assembly, the 2-data access layer, the 3-database interface, 4-relational database link block, the 5-relational database, 6-Object Creation assembly, the 7-MDAC, 8-complex view assembly, 9-simple view assembly, 10-the first central control unit, 11-the one SQL operation maker, 12-the first view generation device, 13-INSTEAD OF trigger maker, 14-the first dynamic sql assembly unit maker, 15-the first connector, 16-the first public attribute set, 17-the second central control unit, 18-the 2nd SQL operation maker, 19-the second view generation device, 20-the second dynamic sql assembly unit maker, 21-the second connector, 22-the second public attribute set.
Embodiment
Below in conjunction with the accompanying drawing in the embodiment of the present invention, the technical scheme in the embodiment of the present invention is clearly and completely described, obviously, described embodiment is only a part of embodiment of the present invention, rather than whole embodiment.Based on the embodiment in the present invention, those of ordinary skills belong to the scope of protection of the invention not making the every other embodiment that obtains under the creative work prerequisite.
As shown in accompanying drawing 4,5,6, provided a kind of specific embodiment that is applied to the access of computer information technology field database based on data access system and the method thereof of view of the present invention, the invention will be further described below in conjunction with the drawings and specific embodiments.
The present invention is based on the data access system of view take view as the basis, is complex view with following view definition:
(1) use the view of DISTINCT operational character in the SELECT statement;
(2) use the view of the aggregate functions such as similar SUN or MAX;
(3) comprise GROUP BY, ORDER BY clause's view;
(4) use the view of subquery in the SELECT list;
(5) view of a plurality of tables of associating.
View except complex view is defined as simple view.
MDAC is divided into complex view assembly and simple view assembly by above-mentioned view classification.
The data access system based on view as shown in Figure 4 comprises: interface assembly 1, data access layer 2, database interface 3, relational database link block 4, relational database 5;
Interface assembly 1 is connected with data access layer 2, and sends user message to data access layer 2;
Data access layer 2 comprises Object Creation assembly 6 and MDAC 7, and the Object Creation assembly 6 of data access layer 2 creates MDAC 7, and MDAC 7 comprises complex view assembly 8 and simple view assembly 9;
MDAC 7 produces the SQL action statement, and MDAC 7 is connected with database interface 3, obtains database information by the XML configuration file, obtains simultaneously database connection object;
Database interface 3 is connected with relational database link block 4, by relational database link block 4, the SQL action statement is passed to relational database 5, relational database 5 is carried out the SQL action statement, and by relational database link block 4, the defined query statement of data attribute that obtains in the MDAC object is passed to relational database 5, relational database 5) the execution query statement;
Relational database link block 4 according to query statement with the data transmission returned to MDAC 7;
MDAC 7 transfers to interface assembly 1 with the data source of obtaining and shows, interface assembly 1 refresh data, and notify the user.
The fundamental diagram of Fig. 4 has illustrated a kind of work structuring of the data access method based on view techniques, and its course of work is as follows:
S101: interface assembly 1 sends user message to data access layer 2, this message comprises concrete operation information (operations such as insertion, deletion), path, MDAC object identity, the MDAC of view component type identification (complex view assembly or simple view assembly), XML (Extensible Markup Language, XML (extensible Markup Language)) mapped file and XML configuration file obtain Data Identification etc.;
S102: the Object Creation assembly of data access layer 2 creates a data access component object (complex view component object or simple view component object) according to the view component sign, and complete the initial work of MDAC object, this part work is mainly to complete the work such as analyzing XML mapped file, establishment view, and internal mechanism sees the initial work flow process of following complex view assembly and simple view assembly for details;
S103: the MDAC object is completed initial work according to the type of complex view assembly 8 or simple view assembly 9, and according to the operation information attribute, if data acquisition, executing data acquisition process; If data deletion, executing data delete procedure; If data increase or revise, executing data increases or modification process, produces a SQL action statement (for example delete statement) by MDAC 7 at last;
S104: MDAC object connection data bank interface 3, obtain related data library information (as certain type of relationship database, database name, IP address etc.) by the XML configuration file, obtain a database connection object by static factory mode;
S105: by database connection object, the SQL action statement is passed to relational database 5, relational database 5 is carried out the SQL action statement;
S106: by database connection object, the defined query statement of data attribute that obtains in the MDAC object is passed to relational database 5, relational database 5 is carried out query statement;
S107: database connection object is passed to the MDAC object according to query statement with the data of returning, and after complete operation, this database connection object is closed;
S108: the MDAC object offers the interface assembly object with the data source of obtaining and shows, this MDAC object of system-kill;
S109: interface assembly object refresh data, and notify the user.
Wherein, as shown in Figure 5 complex view assembly further comprises the first central control unit 10, a SQL operation maker 11, the first view generation device 12, INSTEAD OF trigger maker 13, the first dynamic sql assembly unit maker 14, the first connector 15, the first public attribute set 16.In the complex view assembly, the functional structure of each parts is described as follows shown in table 1.
The first central control unit 10 receives user message, return to user data or information, the analyzing XML mapped file also writes the first public attribute set 16, to a SQL operation maker 11, the first view generation device 12, INSTEAD OF trigger maker 13 sending action message, and receive a SQL operation maker 11, the first view generation device 12, the message of INSTEAD OF trigger maker 13 and returning results of relational database 5;
The one SQL operation maker 11 generates SQL, and is sent to relational database 5 by the first connector 15 and database interface 3;
The first view generation device 12 creates according to different relational database 5 dynamic sql that assembly relies on view (view that component object creates according to mapped file, module data operates on this view and carries out);
INSTEAD OF trigger maker 13 creates a dynamic sql that relies on the INSTEAD OF trigger that operates on view at assembly according to different relational database 5;
The first dynamic sql assembly unit maker 14 carries out the assembly unit of SQL according to the public attribute value of the first public attribute set 16 outputs, realizes dynamic sql;
The first connector 15 is connected with database interface 3;
The first public attribute set 16 storages are from the relevant information of the analyzing XML mapped file of the first central control unit 10.
The functional structure explanation of table 1 complex view assembly
The complex view component object is carried out initial work process, deletion data, is increased data, Update Table and obtain the datamation process, below is introduced respectively.
1, the initial work process of complex view component object is as follows:
S201: the first central control unit 10 writes user message respectively in the public attribute set of component object, comprises concrete operations message attributes (operations such as insertion, deletion), XML mapped file path attribute, XML Profile Path attribute, object identity attribute, obtains the Data Identification attribute.If user message also comprises the query condition value of obtaining data, also write public attribute and concentrate;
S202: the first central control unit 10 finds the XML mapped file according to the XML mapped file path attribute of component object, secondly realizes in the XML mapped file according to the object identity attribute finding corresponding data block in the location.The first central control unit 10 begins that data block is carried out XML and resolves work, and the public attribute that physics table name, Table Properties etc. is write object is concentrated;
S203: the first central control unit 10 sends to a SQL operation maker 11 and obtains the Data Identification attribute message, calls the first dynamic sql assembly unit maker 14 and select query statement of the first public attribute set 16 Dynamic Generation by a SQL operation maker 11.The one SQL operation maker 11 returns to the first central control unit 10, the first central control units 10 with select query statement message and the select query statement is write obtaining in data attribute of object again;
S204: the first central control unit 10 sends Query Database view message to a SQL operation maker 11, call by a SQL operation maker 11 SQL whether view that the first dynamic sql assembly unit maker 14 and the first public attribute set 16 (according to the physics table name) Dynamic Generation component object rely on exists, and send to the first connector 15;
S205: after the first connector 15 connection data bank interfaces 3, create a database connection object, relational database 5 is carried out the SQL whether the inquiry view exists, and result is returned to the first connector 15, the first connectors 15 message is passed to the first central control unit 10; If otherwise the first connector 15 is overtime, the first central control unit 10 sends an error message, and flow process finishes;
S206: the first central control unit 10 is received the message of the first connector 15, if view existence, direct execution in step S211; Otherwise the first connector 15 cuts out new creation database connecting object by database interface 3, and the first central control unit 10 sends to the first view generation device 12 and creates view message simultaneously.The interface of the first view generation device 12 obtains relational database type (as oracle, sqlserver etc.) according to the XML configuration file, selects certain relational database view generation assembly.SQL who creates view of this component call first dynamic sql assembly unit maker 14 and the first public attribute set 16 Dynamic Generation, and this SQL is sent to the first connector 15;
S207: the first connector 15 is received the SQL that creates view, connection data bank interface 3 creates a database connection object, and relational database 5 is carried out and created view SQL, result is returned to the first connector 15, the first connectors 15 message is passed to the first central control unit 10; If otherwise the first connector 15 is overtime, the first central control unit 10 sends an error message, and flow process finishes;
S208: the first central control unit 10 is received the message that view creates, and closes the database connection object of new establishment by database interface 3 by the first connector 15, sends to INSTEAD OF trigger maker 13 simultaneously and creates INSTEAD OF trigger message.The interface of INSTEAD OF trigger maker 13 obtains relational database type (as oracle, sqlserver etc.) according to the XML configuration file, selects certain relational database INSTEAD OF trigger formation component.SQL who generates INSTEAD OF trigger of this component call first dynamic sql assembly unit maker 14 and the first public attribute set 16 Dynamic Generation, and this SQL is sent to the first connector 15;
S209: the first connector 15 is received the SQL that creates INSTEAD OF trigger, connection data bank interface 3, create a database connection object, relational database 5 is carried out and is created INSTEAD OF trigger SQL, result is returned to the first connector 15, the first connectors 15 message is passed to the first central control unit 10; If otherwise the first connector 15 is overtime, the first central control unit 10 sends an error message, and flow process finishes;
S210: the first central control unit 10 is received the message that INSTEAD OF trigger creates;
S211: the first central control unit 10 sends closing information to the first connector 15, closes new creation database connecting object by the first connector 15 by database interface 3, sends simultaneously the completed information of initialization, and this flow process finishes.
2, the complex view component object to obtain the datamation flow process as follows:
S301: if the view component object does not carry out initialization, first carry out the initialization flow process;
S302: the first central control unit 10 sends data acquisition information to a SQL operation maker 11;
S303: a SQL operation maker 11 is received data acquisition information, and the data acquisition attribute of object is sent to the first connector 15;
S304: after the first connector 15 connection data bank interfaces 3, create a database connection object, relational database 5 is carried out a select and is obtained data on the view that component object relies on, and result is returned to the first connector 15, the first connectors 15 data set is passed to the first central control unit 10; If otherwise the first connector 15 is overtime, the first central control unit 10 sends an error message, and flow process finishes;
S305: after the first central control unit 10 is received data set, send message to the first connector 15, close the database connection object of new establishment by database interface 3 by the first connector 15;
S306: the first central control unit 10 is to interface assembly object transfer data, interface assembly object refresh data, and after notifying the user, this flow process finishes.
3, deletion datamation, the flow process of complex view component object are as follows:
S401: the initial work flow process of first carrying out object;
S402: the first central control unit 10 is deletion work from the concrete operations determined property, sends deletion information to a SQL operation maker 11;
S403: a SQL operation maker 11 receives deletion information, at first obtains the parent table attribute according to the public attribute collection, and secondly the physics table of determination component object is multilist or single table, if single table is directly carried out S406; If (multilist adopts main foreign key reference to multilist, main external key is set during the database initialize table, in definition, on delete cascade option is set during external key and realizes the data deletion of being correlated with), a SQL operation maker 11 generates a SQL who obtains parent table master external key, sends to the first connector 15;
S404: after the first connector 15 connection data bank interfaces 3, create a database connection object, relational database 5 is carried out the SQL that obtains parent table master external key, result is returned to the first connector 15, the first connectors 15 message is passed to the first central control unit 10; If otherwise the first connector 15 is overtime, the first central control unit 10 sends an error message, and flow process finishes;
S405: the first central control unit 10 is received the main foreign key information of parent table, closes the database connection object of new establishment by the first connector 15 notification data bank interfaces, after simultaneously main foreign key information being write the public attribute collection.Write complete after, the first central control unit 10 sends main external key success obtaining information to SQL operation maker;
S406: a SQL operation maker 11 calls the first dynamic sql assembly unit maker 14 and the first public attribute set 16, generates the deletion SQL of an operation parent table, realizes the information deletion of single table or multilist;
S407: after the first connector 15 connection data bank interfaces 3, create a database connection object, relational database 5 is carried out the deletion SQL of an operation parent table, result is returned to the first connector 15, the first connectors 15 message is passed to the first central control unit 10; If otherwise the first connector 15 is overtime, the first central control unit 10 sends an error message, and flow process finishes;
S408: the first central control unit 10 is received deletion action message, closes the database connection object of new establishment by the first connector 15 notification data bank interfaces 3, sends obtaining information to a SQL operation maker 11 simultaneously;
S409: after the data acquisition flow process of executive module object, flow process finishes.
4, the increase data of complex view component object and Update Table workflow are as follows:
S501: the initial work flow process of first carrying out object;
S502: the first central control unit 10 is to increase datamation or Update Table work from the concrete operations determined property, sends increase information to a SQL operation maker 11;
S503: a SQL operation maker 11 receives increase information, produces one by the first dynamic sql assembly unit maker 14 and is relied on insertion (insert) statement that operates on view at component object.Due in the component object initialization, created an INSTEAD OF trigger on the dependence view, insert (insert) statement and can realize simultaneously increasing data and Update Table work, realize view update;
S504: after the first connector 15 connection data bank interfaces 3, create a database connection object, relational database 5 is carried out and is inserted (insert) statement, result is returned to the first connector 15, the first connectors 15 message is passed to the first central control unit 10; If otherwise the first connector 15 is overtime, the first central control unit 10 sends an error message, and flow process finishes;
S505: the first central control unit 10 is received update message, closes the database connection object of new establishment by the first connector 15 notification data bank interfaces 3, sends obtaining information to a SQL operation maker 11 simultaneously;
S506: after the data acquisition flow process of executive module object, flow process finishes.
Simple view assembly as shown in Figure 6 further comprises the second central control unit 17, the 2nd SQL operation maker 18, the second view generation device 19, the second dynamic sql assembly unit maker 20, the second connector 21, the second public attribute set 22;
The second central control unit 17 receives user message, return to user data or information, the analyzing XML mapped file also writes the public attribute set, and to the 2nd SQL operation maker 18 and the second view generation device 19 sending action message, receive the 2nd SQL operation maker 18 and the message of the second view generation device 19 and returning results of database;
The 2nd SQL operation maker 18 generates SQL, and is sent to relational database 5 by the second connector 21 and database interface 3;
The second view generation device 19 creates according to different relational database 5 dynamic sql that assembly relies on view;
The second dynamic sql assembly unit maker 20 carries out the assembly unit of SQL according to the public attribute value of the second public attribute set 22 outputs, realizes dynamic sql;
The second connector 21 is connected with database interface 3;
The second public attribute set 22 storages are from the relevant information of the analyzing XML mapped file of the second central control unit 17.
The simple view component object is carried out initial work process, deletion data, is increased data, Update Table and obtain the datamation process, below is introduced respectively.
5, the initial work flow process of simple view component object is as follows:
S601: the second central control unit 17 is concentrated the public attribute that user message writes respectively component object, comprises concrete operations message attributes (operations such as insertion, deletion), XML mapped file path attribute, XML Profile Path attribute, object identity attribute, obtains the Data Identification attribute.If user message also comprises the query condition value of obtaining data, also write public attribute and concentrate;
S602: the second central control unit 17 finds the XML mapped file according to the XML mapped file path attribute of component object, secondly realizes in the XML mapped file according to the object identity attribute finding corresponding data block in the location.The second central control unit 17 begins that data block is carried out XML and resolves work, and the public attribute that physics table name, Table Properties etc. is write object is concentrated;
S603: the second central control unit 17 sends to the 2nd SQL operation maker 18 and obtains the Data Identification attribute message, calls the second dynamic sql assembly unit maker 20 and select query statement of the second public attribute set 22 Dynamic Generation by the 2nd SQL operation maker 18.The 2nd SQL operation maker 18 returns to the second central control unit 17, the second central control units 17 with select query statement message and the select query statement is write obtaining in data attribute of object again;
S604: the second central control unit 17 sends Query Database view message to the 2nd SQL operation maker 18, call by the 2nd SQL operation maker 18 SQL whether view that the second dynamic sql assembly unit maker 20 and the second public attribute set 22 (according to the physics table name) Dynamic Generation component object rely on exists, and send to the second connector 21.
S605: after the second connector 21 connection data bank interfaces 3, create a database connection object, relational database 5 is carried out the SQL whether the inquiry view exists, and result is returned to the second connector 21, the second connectors 21 message is passed to the second central control unit 17; If otherwise the second connector 21 is overtime, the second central control unit 17 sends an error message, and flow process finishes;
S606: the second central control unit 17 is received the message of the second connector 21, if view existence, direct execution in step S609; Otherwise the second connector 21 cuts out new creation database connecting object by database interface 3, and the second central control unit 17 sends to the second view generation device 19 and creates view message simultaneously.The interface of the second view generation device 19 obtains relational database type (as oracle, sqlserver etc.) according to the XML configuration file, selects certain relational database view generation assembly.SQL who creates view of this component call second dynamic sql assembly unit maker 20 and the second public attribute set 22 Dynamic Generation, and this SQL is sent to the second connector 21;
S607: the second connector 21 is received the SQL that creates view, connection data bank interface 3 creates a database connection object, and relational database 5 is carried out and created view SQL, result is returned to the second connector 21, the second connectors 21 message is passed to the second central control unit 17; If otherwise the second connector 21 is overtime, the second central control unit 17 sends an error message, and flow process finishes;
S608: the second central control unit 17 is received the message that view creates;
S609: the second central control unit 17 sends closing information to the second connector 21, closes new creation database connecting object by the second connector 21 by database interface 3, sends simultaneously the completed information of initialization, and this flow process finishes.
6, the datamation flow process of obtaining of simple view component object is obtained the datamation flow process with the complex view object.
7, the deletion datamation flow process of simple view component object is as follows:
S701: the initial work flow process of first carrying out object;
S702: the second central control unit 17 is deletion work from the concrete operations determined property, sends deletion information to the 2nd SQL operation maker 18;
S703: the 2nd SQL operation maker 18 receives deletion information, calls the second dynamic sql assembly unit maker 20 and the second public attribute set 22, generates one at the deletion SQL that relies on the enterprising line operate of view, realizes information deletion;
S704: after the second connector 21 connection data bank interfaces 3, create a database connection object, relational database 5 is carried out the deletion SQL that an operation relies on view, result is returned to the second connector 21, the second connectors 21 message is passed to the second central control unit 17; If otherwise the second connector 21 is overtime, the second central control unit 17 sends an error message, and flow process finishes;
S705: the second central control unit 17 is received deletion action message, closes the database connection object of new establishment by the second connector 21 notification data bank interfaces 3, sends obtaining information to the 2nd SQL operation maker 18 simultaneously;
S706: after the data acquisition flow process of executive module object, flow process finishes.
8, the increase data of simple view component object and Update Table workflow are with increase datamation and the Update Table workflow of complex view object.
Wherein, in the structural design part of XML configuration file, adopt the XML formatted file to realize configuration, the attribute of configuration file is as follows:
(1) dbType: type of database (as Oracle);
(2) connect-server: server name;
(3) connect-dataBase: database name;
(4) connect-user: log database user name;
(5) connect-password: log database password;
Specific as follows:
<?xml?version=’1.0’encoding=’UTF-8’?>
<configuration>
<property name=" dbType " value (as sqlserver)</property 〉
<property name=" connection-server " value (as 192.168.0.1)</property 〉
<property name=" connection-dataBase " value (as EA)</property 〉
<property name=" connection-user " value (as sa)</property 〉
<property name=" connection-password " value (as sa)</property 〉
</configuration>
The structure of the XML mapped file of complex view assembly is as follows:
Aspect the structural design of XML mapped file, the XML mapped file of complex view assembly be defined as follows table 2.
The structural design instruction card of table 2 complex view assembly
It is below an instantiation of the XML mapped file of complex view assembly.
The structure of the XML mapped file of simple view assembly is as follows:
The XML mapped file structural design of simple view assembly is as shown in table 3 below:
The structural design instruction card of table 3 simple view assembly
It is below an example of the XML mapped file of simple view assembly.
By using embodiment of the present invention described a kind of data access system and method thereof based on view, reached following technique effect:
(1) utilize XML configuration file and XML mapped file to generate dynamic sql, the SQL code do not occur in source code file, realize that the MDAC object is to the mapping of relational database;
(2) be simple and easy to use, as long as the developer adopts this method to realize that data access completes following work:
(a) XML configuration file and XML mapped file are set;
(b) MDAC attribute (as the path of XML mapped file, MDAC object name etc.) is set
(3) provide share service to reduce developer's quantities;
(4) developer needs only the table definition in acquaint with data storehouse, and view definition is by the MDAC Object Operations.View in database is transparent to the developer;
(5) developer is when configuration XML mapped file, according to treaty rule, as long as the physics table in configuration database, need not be concerned about the data source of actual generation.Comprise following any one situation:
(a) use the view of DISTINCT operational character in the SELECT statement;
(b) use the view of the aggregate functions such as similar SUN or MAX;
(c) comprise GROUP BY, ORDER BY clause's view;
(d) use the view of subquery in the SELECT list;
(e) view of a plurality of tables of associating.
(6) support multiple relational database, and provide interface to expand.
The above is only preferred embodiment of the present invention, is not the present invention is done any pro forma restriction.Although the present invention discloses as above with preferred embodiment, yet is not to limit the present invention.Any those of ordinary skill in the art, do not breaking away from technical solution of the present invention scope situation, all can utilize method and the technology contents of above-mentioned announcement to make many possible changes and modification to technical solution of the present invention, or be revised as the equivalent embodiment of equivalent variations.Therefore, every content that does not break away from technical solution of the present invention, according to technical spirit of the present invention to any simple modification made for any of the above embodiments, be equal to replacements, equivalence changes and modify, all still belong in the scope that technical solution of the present invention protects.