分析型数据库如何进行业务系统连接并进行查询?(2)

带重试的JDBC样例程序片段

1. [backcolor=transparent]public[backcolor=transparent] [backcolor=transparent]static[backcolor=transparent] [backcolor=transparent]final[backcolor=transparent] [backcolor=transparent]int[backcolor=transparent] MAX_QUERY_RETRY_TIMES [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]3[backcolor=transparent];
3. [backcolor=transparent]public[backcolor=transparent] [backcolor=transparent]static[backcolor=transparent] [backcolor=transparent]Connection[backcolor=transparent] conn [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent];
4. [backcolor=transparent]public[backcolor=transparent] [backcolor=transparent]static[backcolor=transparent] [backcolor=transparent]Statement[backcolor=transparent] statement [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent];
5. [backcolor=transparent]public[backcolor=transparent] [backcolor=transparent]static[backcolor=transparent] [backcolor=transparent]ResultSet[backcolor=transparent] rs [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent];
7. [backcolor=transparent]public[backcolor=transparent] [backcolor=transparent]static[backcolor=transparent] [backcolor=transparent]void[backcolor=transparent] main[backcolor=transparent]([backcolor=transparent]String[backcolor=transparent][][backcolor=transparent] args[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]throws[backcolor=transparent] [backcolor=transparent]ClassNotFoundException[backcolor=transparent] [backcolor=transparent]{
8. [backcolor=transparent]    [backcolor=transparent]String[backcolor=transparent] yourDB [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]"user_db"[backcolor=transparent];
9. [backcolor=transparent]    [backcolor=transparent]String[backcolor=transparent] username [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]"my_access_key_id"[backcolor=transparent];
10. [backcolor=transparent]    [backcolor=transparent]String[backcolor=transparent] password [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]"my_access_key_secret"[backcolor=transparent];
12. [backcolor=transparent]    [backcolor=transparent]Class[backcolor=transparent].[backcolor=transparent]forName[backcolor=transparent]([backcolor=transparent]"com.mysql.jdbc.Driver"[backcolor=transparent]);
13. [backcolor=transparent]    [backcolor=transparent]String[backcolor=transparent] url [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]"jdbc:mysql://mydbname.ads-hz.aliyuncs.com:5544/"[backcolor=transparent] [backcolor=transparent]+[backcolor=transparent] yourDB [backcolor=transparent]+[backcolor=transparent] [backcolor=transparent]"?useUnicode=true&characterEncoding=UTF-8"[backcolor=transparent];
15. [backcolor=transparent]    [backcolor=transparent]Properties[backcolor=transparent] connectionProps [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]new[backcolor=transparent] [backcolor=transparent]Properties[backcolor=transparent]();
16. [backcolor=transparent]    connectionProps[backcolor=transparent].[backcolor=transparent]put[backcolor=transparent]([backcolor=transparent]"user"[backcolor=transparent],[backcolor=transparent] username[backcolor=transparent]);
17. [backcolor=transparent]    connectionProps[backcolor=transparent].[backcolor=transparent]put[backcolor=transparent]([backcolor=transparent]"password"[backcolor=transparent],[backcolor=transparent] password[backcolor=transparent]);
19. [backcolor=transparent]    [backcolor=transparent]String[backcolor=transparent] query [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]"select id from test4dmp.test limit 10"[backcolor=transparent];
21. [backcolor=transparent]    [backcolor=transparent]int[backcolor=transparent] retryTimes [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]0[backcolor=transparent];
22. [backcolor=transparent]    [backcolor=transparent]while[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]retryTimes [backcolor=transparent]<[backcolor=transparent] MAX_QUERY_RETRY_TIMES[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
23. [backcolor=transparent]        [backcolor=transparent]try[backcolor=transparent] [backcolor=transparent]{
24. [backcolor=transparent]            getConn[backcolor=transparent]([backcolor=transparent]url[backcolor=transparent],[backcolor=transparent] connectionProps[backcolor=transparent]);
25. [backcolor=transparent]            execQuery[backcolor=transparent]([backcolor=transparent]query[backcolor=transparent]);
26. [backcolor=transparent]            [backcolor=transparent]break[backcolor=transparent];[backcolor=transparent] [backcolor=transparent]// Query execution successfully, break out.
28. [backcolor=transparent]        [backcolor=transparent]}[backcolor=transparent] [backcolor=transparent]catch[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]SQLException[backcolor=transparent] e[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
29. [backcolor=transparent]            [backcolor=transparent]System[backcolor=transparent].[backcolor=transparent]out[backcolor=transparent].[backcolor=transparent]println[backcolor=transparent]([backcolor=transparent]"Met SQL exception: "[backcolor=transparent] [backcolor=transparent]+[backcolor=transparent] e[backcolor=transparent].[backcolor=transparent]getMessage[backcolor=transparent]()[backcolor=transparent] [backcolor=transparent]+[backcolor=transparent] [backcolor=transparent]", then go to retry task ..."[backcolor=transparent]);
30. [backcolor=transparent]            [backcolor=transparent]try[backcolor=transparent] [backcolor=transparent]{
31. [backcolor=transparent]                [backcolor=transparent]if[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]conn [backcolor=transparent]==[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent] [backcolor=transparent]||[backcolor=transparent] conn[backcolor=transparent].[backcolor=transparent]isClosed[backcolor=transparent]())[backcolor=transparent] [backcolor=transparent]{
32. [backcolor=transparent]                    retryTimes[backcolor=transparent]++;
33. [backcolor=transparent]                [backcolor=transparent]}
34. [backcolor=transparent]            [backcolor=transparent]}[backcolor=transparent] [backcolor=transparent]catch[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]SQLException[backcolor=transparent] e1[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
35. [backcolor=transparent]                [backcolor=transparent]if[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]conn [backcolor=transparent]!=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
36. [backcolor=transparent]                    [backcolor=transparent]try[backcolor=transparent] [backcolor=transparent]{
37. [backcolor=transparent]                        conn[backcolor=transparent].[backcolor=transparent]close[backcolor=transparent]();
38. [backcolor=transparent]                    [backcolor=transparent]}[backcolor=transparent] [backcolor=transparent]catch[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]SQLException[backcolor=transparent] e2[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
39. [backcolor=transparent]                        e[backcolor=transparent].[backcolor=transparent]printStackTrace[backcolor=transparent]();
40. [backcolor=transparent]                    [backcolor=transparent]}
41. [backcolor=transparent]                [backcolor=transparent]}
42. [backcolor=transparent]            [backcolor=transparent]}
43. [backcolor=transparent]        [backcolor=transparent]}
44. [backcolor=transparent]    [backcolor=transparent]}
46. [backcolor=transparent]    [backcolor=transparent]// Clear connection resource.
47. [backcolor=transparent]    closeResource[backcolor=transparent]();
48. [backcolor=transparent]}
50. [backcolor=transparent]/**
51. [backcolor=transparent] * Get connection.
52. [backcolor=transparent] *
53. [backcolor=transparent] * @param url
54. [backcolor=transparent] * @param connectionProps
55. [backcolor=transparent] * @throws SQLException
56. [backcolor=transparent] */
57. [backcolor=transparent]public[backcolor=transparent] [backcolor=transparent]static[backcolor=transparent] [backcolor=transparent]void[backcolor=transparent] getConn[backcolor=transparent]([backcolor=transparent]String[backcolor=transparent] url[backcolor=transparent],[backcolor=transparent] [backcolor=transparent]Properties[backcolor=transparent] connectionProps[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]throws[backcolor=transparent] [backcolor=transparent]SQLException[backcolor=transparent] [backcolor=transparent]{
58. [backcolor=transparent]    conn [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]DriverManager[backcolor=transparent].[backcolor=transparent]getConnection[backcolor=transparent]([backcolor=transparent]url[backcolor=transparent],[backcolor=transparent] connectionProps[backcolor=transparent]);
59. [backcolor=transparent]}
61. [backcolor=transparent]/**
62. [backcolor=transparent] * Query task execution logic.
63. [backcolor=transparent] *
64. [backcolor=transparent] * @param sql
65. [backcolor=transparent] * @throws SQLException
66. [backcolor=transparent] */
67. [backcolor=transparent]public[backcolor=transparent] [backcolor=transparent]static[backcolor=transparent] [backcolor=transparent]void[backcolor=transparent] execQuery[backcolor=transparent]([backcolor=transparent]String[backcolor=transparent] sql[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]throws[backcolor=transparent] [backcolor=transparent]SQLException[backcolor=transparent] [backcolor=transparent]{
68. [backcolor=transparent]    [backcolor=transparent]Statement[backcolor=transparent] statement [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent];
69. [backcolor=transparent]    [backcolor=transparent]ResultSet[backcolor=transparent] rs [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent];
70. [backcolor=transparent]    statement [backcolor=transparent]=[backcolor=transparent] conn[backcolor=transparent].[backcolor=transparent]createStatement[backcolor=transparent]();
72. [backcolor=transparent]    [backcolor=transparent]for[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]int[backcolor=transparent] i [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]0[backcolor=transparent];[backcolor=transparent] i [backcolor=transparent]<[backcolor=transparent] [backcolor=transparent]10[backcolor=transparent];[backcolor=transparent] i[backcolor=transparent]++)[backcolor=transparent] [backcolor=transparent]{
73. [backcolor=transparent]        [backcolor=transparent]long[backcolor=transparent] startTs [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]System[backcolor=transparent].[backcolor=transparent]currentTimeMillis[backcolor=transparent]();
74. [backcolor=transparent]        rs [backcolor=transparent]=[backcolor=transparent] statement[backcolor=transparent].[backcolor=transparent]executeQuery[backcolor=transparent]([backcolor=transparent]sql[backcolor=transparent]);
75. [backcolor=transparent]        [backcolor=transparent]int[backcolor=transparent] cnt [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]0[backcolor=transparent];
76. [backcolor=transparent]        [backcolor=transparent]while[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]rs[backcolor=transparent].[backcolor=transparent]next[backcolor=transparent]())[backcolor=transparent] [backcolor=transparent]{
77. [backcolor=transparent]            cnt[backcolor=transparent]++;
78. [backcolor=transparent]            [backcolor=transparent]System[backcolor=transparent].[backcolor=transparent]out[backcolor=transparent].[backcolor=transparent]println[backcolor=transparent]([backcolor=transparent]rs[backcolor=transparent].[backcolor=transparent]getObject[backcolor=transparent]([backcolor=transparent]1[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]+[backcolor=transparent] [backcolor=transparent]" "[backcolor=transparent]);
79. [backcolor=transparent]        [backcolor=transparent]}
80. [backcolor=transparent]        [backcolor=transparent]long[backcolor=transparent] endTs [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]System[backcolor=transparent].[backcolor=transparent]currentTimeMillis[backcolor=transparent]();
81. [backcolor=transparent]        [backcolor=transparent]System[backcolor=transparent].[backcolor=transparent]out[backcolor=transparent].[backcolor=transparent]println[backcolor=transparent]([backcolor=transparent]"Elapse Time: "[backcolor=transparent] [backcolor=transparent]+[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]endTs [backcolor=transparent]-[backcolor=transparent] startTs[backcolor=transparent]));
82. [backcolor=transparent]        [backcolor=transparent]System[backcolor=transparent].[backcolor=transparent]out[backcolor=transparent].[backcolor=transparent]println[backcolor=transparent]([backcolor=transparent]"Row count: "[backcolor=transparent] [backcolor=transparent]+[backcolor=transparent] cnt[backcolor=transparent]);
83. [backcolor=transparent]        [backcolor=transparent]try[backcolor=transparent] [backcolor=transparent]{
84. [backcolor=transparent]            [backcolor=transparent]Thread[backcolor=transparent].[backcolor=transparent]sleep[backcolor=transparent]([backcolor=transparent]160000[backcolor=transparent]);
85. [backcolor=transparent]        [backcolor=transparent]}[backcolor=transparent] [backcolor=transparent]catch[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]InterruptedException[backcolor=transparent] e[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
86. [backcolor=transparent]            e[backcolor=transparent].[backcolor=transparent]printStackTrace[backcolor=transparent]();
87. [backcolor=transparent]        [backcolor=transparent]}
88. [backcolor=transparent]    [backcolor=transparent]}
89. [backcolor=transparent]}
91. [backcolor=transparent]/**
92. [backcolor=transparent] * Close connection resource.
93. [backcolor=transparent] */
94. [backcolor=transparent]public[backcolor=transparent] [backcolor=transparent]static[backcolor=transparent] [backcolor=transparent]void[backcolor=transparent] closeResource[backcolor=transparent]()[backcolor=transparent] [backcolor=transparent]{
95. [backcolor=transparent]    [backcolor=transparent]if[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]rs [backcolor=transparent]!=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
96. [backcolor=transparent]        [backcolor=transparent]try[backcolor=transparent] [backcolor=transparent]{
97. [backcolor=transparent]            rs[backcolor=transparent].[backcolor=transparent]close[backcolor=transparent]();
98. [backcolor=transparent]        [backcolor=transparent]}[backcolor=transparent] [backcolor=transparent]catch[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]SQLException[backcolor=transparent] e[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
99. [backcolor=transparent]            e[backcolor=transparent].[backcolor=transparent]printStackTrace[backcolor=transparent]();
100. [backcolor=transparent]        [backcolor=transparent]}
101. [backcolor=transparent]    [backcolor=transparent]}
102. [backcolor=transparent]    [backcolor=transparent]if[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]statement [backcolor=transparent]!=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
103. [backcolor=transparent]        [backcolor=transparent]try[backcolor=transparent] [backcolor=transparent]{
104. [backcolor=transparent]            statement[backcolor=transparent].[backcolor=transparent]close[backcolor=transparent]();
105. [backcolor=transparent]        [backcolor=transparent]}[backcolor=transparent] [backcolor=transparent]catch[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]SQLException[backcolor=transparent] e[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
106. [backcolor=transparent]            e[backcolor=transparent].[backcolor=transparent]printStackTrace[backcolor=transparent]();
107. [backcolor=transparent]        [backcolor=transparent]}
108. [backcolor=transparent]    [backcolor=transparent]}
109. [backcolor=transparent]    [backcolor=transparent]if[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]conn [backcolor=transparent]!=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
110. [backcolor=transparent]        [backcolor=transparent]try[backcolor=transparent] [backcolor=transparent]{
111. [backcolor=transparent]            conn[backcolor=transparent].[backcolor=transparent]close[backcolor=transparent]();
112. [backcolor=transparent]        [backcolor=transparent]}[backcolor=transparent] [backcolor=transparent]catch[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]SQLException[backcolor=transparent] e[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
113. [backcolor=transparent]            e[backcolor=transparent].[backcolor=transparent]printStackTrace[backcolor=transparent]();
114. [backcolor=transparent]        [backcolor=transparent]}
115. [backcolor=transparent]    [backcolor=transparent]}
116. [backcolor=transparent]}

通过JDBC连接池连接分析型数据库


通过JDBC连接池来连接分析型数据库是不错的选择，在1.5章节中，已经演示了通过连接池Druid连接分析型数据库的方式，请参考。

通过PHP连接分析型数据库


在1.5章节中，已经演示了在PHP应用中连接分析型数据库的方式，请参考。

关于负载均衡


用户在创建数据库（章节3.1）时，分析型数据库会根据用户指定的服务参数，为用户在分析型数据库集群中分配特定数量的前端服务机，这些机器对用户透明，用户得到的是访问该数据库的URL（例如：mydbname-xxxx.ads-hz.aliyuncs.com:5544，在控制台中的连接信息中获取），分析型数据库集群自己使用阿里云负载均衡产品SLB（ http://www.aliyun.com/product/slb ）来对访问请求进行负载均衡，用户无需关心访问分析型数据库时的负载均衡策略。若用户的业务应用自己想采用一定的负载均衡方案，可以参考 http://www.aliyun.com/product/slb 。

关于重试以及异常处理


网络环境下连接和使用分析型数据库，从业务应用的角度来看，使用适当的重试和异常处理机制是保证业务应用高可用性的手段之一。无论是通过JDBC直连、JDBC连接池、PHP，还是Python等方式连接分析型数据库，出现连接连接异常时，应用均可采用适当的重试机制来保证连接可用性。在海量数据并发处理任务量大的情况下，偶尔在查询过程中出现异常时，也可采用适当的重试机制来重发查询