Java next splicing execute dynamic SQL statement

In the actual business often need to splicing dynamic SQL to complete the complex data calculation, online various technical forums have been discussed, such as the following questions:

http://bbs.csdn.net/topics/390876591

http://bbs.csdn.net/topics/390981627

Https://www.linkedin.com/groups/SQL-Query-Help-needed-137774.S.5948812806903119877?trk=groups_items_see_more-0-b-ttl

http://bbs.csdn.net/topics/390073758

http://bbs.csdn.net/topics/390611005

The general practice of splicing Dynamic SQL is

1. Using Dynamic statements

Many databases provide syntax for handling dynamic SQL, such as Oracle's execute immediate statement, MSSQL exec and sp_executesql, MySQL preprocessing statements, and so on. These features allow us to handle dynamic queries on the database side with great traversal, but this approach is only appropriate for relatively simple dynamic queries, and complex situations often take the following approach.

2. Using Stored Procedures

For complex situations, dynamic SQL is typically spliced in a stored procedure. The use of stored procedures is relatively flexible, but the coding complexity is too high, sometimes less efficient operation.

3. Use other (such as Java ) Program

It is also a choice to use other high-level languages outside, such as Java, to splice and then go to database execution, but because Java lacks support for collection computing, it is not easy to complete these preparations.

If the hypervisor that needs to execute dynamic SQL is Java, then you can use the collector to assist with dynamic SQL class calculations, which are scripts that dynamically interpret execution and can be easily spelled out in dynamic SQL execution. The collector provides a JDBC interface that can be placed between the Java application and the database, allowing the application to continue to execute the collector script as if it were accessing the database, with little change to the application structure.

Here's an example of how to use a collector to complete a dynamic SQL class calculation and integrate it into a Java program.

Splicing Dynamic SQL

The dynamic SQL stitching is completed in the collector, and the stitched SQL is then transferred to the database to query the target results. The collector does not involve the target calculation when it is finished, only the dynamic SQL is spliced. such as the following requirements:

The parameter source and target represent two tables with the same structure but different data, but the table structure is unknown. Requires a primary key to update the target with source, such as the primary keys for Table1 and table2 are both A and B, with the following data:

When updating table1 with table2, the merge statement should read as follows:

MERGE into table1 as T

USING Table2 as S

On T.A=S.A and t.b=s.b

When matched

Then UPDATE SET T.C=S.C,T.D=S.D

When not matched

Then INSERT VALUES (S.A,S.B,S.C,S.D)

Implementation script:

A1,A2: The primary key of the table source read from the system table is stored in the variable PKS, and the result is set ["A", "B"]. There are different ways to get the primary key in various databases, for example, MSSQL.

A3,A4: read out the full field of source, the columns evaluates to ["A", "B", "C", "D"].

A5: Dynamically generate the merge statement. PKs. (...) is a looping function that computes the members of the collection (including the result set), which can be used in the calculation to refer to the loop variable, with the # Reference loop count.

A6: Executes the merge statement.

Because the table structure is unknown, it is cumbersome to use stored procedures or Java to get a table structure and then dynamically spell SQL. Using the collection class to compute a well-supported set of operators, the code is simple, the script is generic and easy to maintain.

The calculation results of a collection script can be used as a report data source for the report, and it can be read and used by JDBC in a Java program, and the Java read call set script code is as follows:

Class.forName ("Com.esproc.jdbc.InternalDriver");

con= drivermanager.getconnection ("jdbc:esproc:local://");

Call the Collector script (similar to a stored procedure) where P1 is the file name of the collection script

St = (com. esproc.jdbc.InternalCStatement) con.preparecall ("Call P1 ()");

St.setobject (1, "Table1");

St.setobject (2, "table2");

Execute script

St.execute ();

......

The method of invoking the collector script is exactly the same as accessing the database, and programmers familiar with JDBC can quickly master it.

More detailed information on the deployment and invocation of the collector JDBC can be referenced by Java calls to the integrated application of the collector .

Dynamic Inter-table connections

Relative static inter-table connections, dynamic Inter-table connections do not know the table to use beforehand. As the following data query:

Table A

b table

C table

It is now necessary to obtain the NUM value for the ID of Table B or C table according to the TableName of table A.

Target Result:

Implementation script:

A1: Execute SQL from a table fetch number;

A2: First by tablename Group, Loop group splicing dynamic query statements, and finally the query results sorted by ID.

With the set computing power of the collector (grouped members are still saved for later use), the concatenation of dynamic SQL is simplified.

Special Format Data Updates

In addition to Dynamic Data query, and sometimes need to do dynamic update, the updated data is often derived from third-party programs, and its format is various, such as JSON format, XML and so on. In a particular business context, it is sometimes necessary to update the data in these more special formats (relative to the traditional two-dimensional table) into the (relational) database. This requires the use of third-party programs, and high-level languages such as Java, such as the lack of class libraries, hard coding difficulties and so on. At this point you can use the collector to complete, the following is an example of the Collector parsing JSON format file storage, the source data are as follows:

Requirements: Update the service list of the specified node, mainly the IMEI, into database 2 tables groups and services.

The JSON string here is difficult to parse because it contains layers and many layers are dynamic (such as the number and name of nodes under list and services are not fixed), and where the property name section also contains spaces (such as Movistar SPAIN) and dot numbers (such as requires.network) This also greatly increases the difficulty of parsing, using Java is very difficult to write.

Implementation script:

A1: Read in JSON format file, result is a result set with hierarchy;

A2-A3: Create two empty-order tables that store updated content;

A4-D10: Cyclic A1, dynamically parse the content and output the analytic result to the A2, A3 target result sequence table;

A11-A12: Perform an update to update the A2, A3 Order table to groups and services tables.

http://datamachine.iteye.com/blog/2219100

Java next splicing Execute dynamic SQL statement (GO)