全部产品

Java访问

更新时间:2019-04-28 15:45:53

JDBC是连接分析型数据库MySQL版最简单和直接的方式,分析型数据库MySQL版支持MySQL自带的客户端以及大部分版本的MySQL-JDBC驱动。

MySQL JDBC驱动版本

分析型数据库MySQL版支持的MySQL JDBC驱动版本号:

  • 5.0系列:5.0.2,5.0.3,5.0.4,5.0.5,5.0.7,5.0.8

  • 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

在Java程序中,将MySQL-JDBC驱动包(mysql-connector-java-x.x.x.jar)加入到CLASSPATH中,通过以下示例程序即可连接并访问数据库。

不带重试的JDBC连接示例

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

带重试的JDBC连接示例

JDBC程序重试也可以通过在JDBC中的参数控制实现。

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

MySQL命令行参数

通过MySQL命令行连接分析型数据库MySQL版进行查询时,为了识别查询中的HINT,命令行参数需要指定-c参数

  1. mysql -h<host> -P<port> -u<user_name> -p<password> <db_name> -c -A

示例

  1. mysql -h127.0.0.1 -P9999 -uTestUser -pTestPassword test_db -c -A