Shulou(Shulou.com)06/01 Report--

What this article shares to you is about how to query the statistics of the sub-database data. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

The calculation after the sub-library can not directly use the SQL; heterogeneous library SQL functions are different; JAVA hard coding is difficult to implement; even if the transparent gateway is used to access the remote database, the performance optimization of the sub-library is also a headache.

General general approach:

Method 1:java hard coding

For a simple cross-library count operation, part of the Java code should be written as follows:

...

Public Connection getConnection (String drivername,String url, String username, String password) {

Try {

Class.forName (drivername)

Conn = DriverManager.getConnection (url, username, password)

} catch (Exception e) {

System.out.println ("DBUtils.getConnection_FAILED!")

E.printStackTrace ()

}

Return conn

}

...

...

Connection conn1 = DBUtils.getConnection (URL1, USER1, PASSWORD1)

Connection conn2 = DBUtils.getConnection (URL2, USER2, PASSWORD2)

...

String sql = "select count (*) c from sales"

PreparedStatement ptmt1 = conn1.prepareStatement (sql)

PreparedStatement ptmt2 = conn1.prepareStatement (sql)

...

ResultSet rs1 = ptmt1.executeQuery ()

ResultSet rs2 = ptmt2.executeQuery ()

...

Return rs1.getInt ("c") + rs2.getInt ("c")

...

Method 2: transparent gateway

For example, dblink of oracle

SQL with DBLINK is complex to optimize. These SQL with DBLINK can be optimized by analyzing the execution plan and using collocated inline view,driving_site hint and other means. But even for an experienced DBA, it's a headache and a lot of effort.

In this case, it's much easier if you have an aggregator. Take a look at the following example:

Count operation of n libraries. The operation needs to be done by count in each sub-library, and the results of each sub-library should be summed up with sum in the summary stage, and the SPL can be done with only 4 lines.

AB1=n. (connect ("mysql" + string (~) / / get the connection of n sub-libraries. SQL3=A1. (~ .cursor (SQL)) / / execute SQL4=A3.conjx (). Total (sum (c)) / / summarize the count values calculated by each sub-library.

Even if the sub-database is heterogeneous, the aggregator can easily convert SQL through the SQL translation function, providing executable SQL for different databases.

According to the characteristics of the data, the aggregator can easily write targeted high-performance code, and the efficient algorithm can be realized without a large amount of hard coding. The aggregator is also easily embedded in Java applications, and there are ways to use and get it how Java invokes the SPL script.

The above is the sub-database data how to query statistics, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.