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

业务系统连接分析型数据库并进行查询




使用jdbc odbc php python R-Mysql等连接分析型数据库的例子和注意事项介绍，以及流控、retry链接、异常处理等方法。
分析型数据库集群系统部署在阿里云环境中，用户在部署业务应用系统时，尽可能保证业务系统与阿里云环境的网络联通性，如购买阿里云ECS主机（ https://www.aliyun.com/product/ecs/ ）作为业务系统的服务器。



JDBC直接连接分析型数据库




最简单直接的连接并访问分析型数据库的方式是通过JDBC，分析型数据库支持MySQL自带的客户端以及大部分版本的mysql-jdbc驱动。



支持的mysql jdbc驱动版本号

• [backcolor=transparent]5.0系列： 5.0.2，5.0.3，5.0.4，5.0.5，5.0.7，5.0.8
• [backcolor=transparent]5.1系列： 5.1.1，5.1.2，5.1.3，5.1.4，5.1.5，5.1.6，5.1.7，5.1.8，5.1.11，5.1.12，5.1.13，5.1.14，5.1.15，5.1.16，5.1.17，5.1.18，5.1.19，5.1.20，5.1.21，5.1.22，5.1.23，5.1.24，5.1.25，5.1.26，5.1.27，5.1.28，5.1.29，5.1.31，5.1.32，5.1.33，5.1.34
• [backcolor=transparent]5.4系列
• [backcolor=transparent]5.5系列

1. [backcolor=transparent]Connection[backcolor=transparent] connection [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent];
2. [backcolor=transparent]Statement[backcolor=transparent] statement [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent];
3. [backcolor=transparent]ResultSet[backcolor=transparent] rs [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent];
4. [backcolor=transparent]try[backcolor=transparent] [backcolor=transparent]{
5. [backcolor=transparent]    [backcolor=transparent]Class[backcolor=transparent].[backcolor=transparent]forName[backcolor=transparent]([backcolor=transparent]"com.mysql.jdbc.Driver"[backcolor=transparent]);
6. [backcolor=transparent]    [backcolor=transparent]String[backcolor=transparent] url [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]"jdbc:mysql://mydbname.ads-hz.aliyuncs.com:5544/my_ads_db?useUnicode=true&characterEncoding=UTF-8"[backcolor=transparent];
8. [backcolor=transparent]    [backcolor=transparent]Properties[backcolor=transparent] connectionProps [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]new[backcolor=transparent] [backcolor=transparent]Properties[backcolor=transparent]();
9. [backcolor=transparent]    connectionProps[backcolor=transparent].[backcolor=transparent]put[backcolor=transparent]([backcolor=transparent]"user"[backcolor=transparent],[backcolor=transparent] [backcolor=transparent]"my_access_key_id"[backcolor=transparent]);
10. [backcolor=transparent]    connectionProps[backcolor=transparent].[backcolor=transparent]put[backcolor=transparent]([backcolor=transparent]"password"[backcolor=transparent],[backcolor=transparent] [backcolor=transparent]"my_access_key_secret"[backcolor=transparent]);
12. [backcolor=transparent]    connection [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]DriverManager[backcolor=transparent].[backcolor=transparent]getConnection[backcolor=transparent]([backcolor=transparent]url[backcolor=transparent],[backcolor=transparent] connectionProps[backcolor=transparent]);
13. [backcolor=transparent]    statement [backcolor=transparent]=[backcolor=transparent] connection[backcolor=transparent].[backcolor=transparent]createStatement[backcolor=transparent]();
15. [backcolor=transparent]    [backcolor=transparent]String[backcolor=transparent] query [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]"select count(*) from information_schema.tables"[backcolor=transparent];
16. [backcolor=transparent]    rs [backcolor=transparent]=[backcolor=transparent] statement[backcolor=transparent].[backcolor=transparent]executeQuery[backcolor=transparent]([backcolor=transparent]query[backcolor=transparent]);
18. [backcolor=transparent]    [backcolor=transparent]while[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]rs[backcolor=transparent].[backcolor=transparent]next[backcolor=transparent]())[backcolor=transparent] [backcolor=transparent]{
19. [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]));
20. [backcolor=transparent]    [backcolor=transparent]}
22. [backcolor=transparent]}[backcolor=transparent] [backcolor=transparent]catch[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]ClassNotFoundException[backcolor=transparent] e[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
23. [backcolor=transparent]    e[backcolor=transparent].[backcolor=transparent]printStackTrace[backcolor=transparent]();
24. [backcolor=transparent]}[backcolor=transparent] [backcolor=transparent]catch[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]SQLException[backcolor=transparent] e[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
25. [backcolor=transparent]    e[backcolor=transparent].[backcolor=transparent]printStackTrace[backcolor=transparent]();
26. [backcolor=transparent]}[backcolor=transparent] [backcolor=transparent]catch[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]Exception[backcolor=transparent] e[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
27. [backcolor=transparent]    e[backcolor=transparent].[backcolor=transparent]printStackTrace[backcolor=transparent]();
28. [backcolor=transparent]}[backcolor=transparent] [backcolor=transparent]finally[backcolor=transparent] [backcolor=transparent]{
29. [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]{
30. [backcolor=transparent]        [backcolor=transparent]try[backcolor=transparent] [backcolor=transparent]{
31. [backcolor=transparent]            rs[backcolor=transparent].[backcolor=transparent]close[backcolor=transparent]();
32. [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]{
33. [backcolor=transparent]            e[backcolor=transparent].[backcolor=transparent]printStackTrace[backcolor=transparent]();
34. [backcolor=transparent]        [backcolor=transparent]}
35. [backcolor=transparent]    [backcolor=transparent]}
36. [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]{
37. [backcolor=transparent]        [backcolor=transparent]try[backcolor=transparent] [backcolor=transparent]{
38. [backcolor=transparent]            statement[backcolor=transparent].[backcolor=transparent]close[backcolor=transparent]();
39. [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]{
40. [backcolor=transparent]            e[backcolor=transparent].[backcolor=transparent]printStackTrace[backcolor=transparent]();
41. [backcolor=transparent]        [backcolor=transparent]}
42. [backcolor=transparent]    [backcolor=transparent]}
43. [backcolor=transparent]    [backcolor=transparent]if[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]connection [backcolor=transparent]!=[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]{
44. [backcolor=transparent]        [backcolor=transparent]try[backcolor=transparent] [backcolor=transparent]{
45. [backcolor=transparent]            connection[backcolor=transparent].[backcolor=transparent]close[backcolor=transparent]();
46. [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]{
47. [backcolor=transparent]            e[backcolor=transparent].[backcolor=transparent]printStackTrace[backcolor=transparent]();
48. [backcolor=transparent]        [backcolor=transparent]}
49. [backcolor=transparent]    [backcolor=transparent]}
50. [backcolor=transparent]}