Updated on 2023-12-29 GMT+08:00

Example: Retrying SQL Queries for Applications

If the primary DN is faulty and cannot be restored within 40s, its standby is automatically promoted to primary to ensure the normal running of the cluster. Jobs running during the failover will fail and those started after the failover will not be affected. To protect upper-layer services from being affected by the failover, refer to the following example to construct a SQL retry mechanism at the service layer.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
//gsjdbc4.jar is used as an example. If gsjdbc200.jar is used, replace the driver class name org.postgresql with com.huawei.gauss200.jdbc and replace the URL prefix jdbc:postgresql with jdbc:gaussdb.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 
 *
 */

class ExitHandler extends Thread {
    private Statement cancel_stmt = null;

    public ExitHandler(Statement stmt) {
        super("Exit Handler");
        this.cancel_stmt = stmt;
    }
    public void run() {
        System.out.println("exit handle");
        try {
            this.cancel_stmt.cancel();
        } catch (SQLException e) {
            System.out.println("cancel query failed.");
            e.printStackTrace();
        }
    }
}

public class SQLRetry {
  //Establish a connection to the database.
   public static Connection GetConnection(String username, String passwd) {
     String driver = "org.postgresql.Driver";
     String sourceURL = "jdbc:postgresql://10.131.72.136:8000/gaussdb";
     Connection conn = null;
     try {
      //Load the database driver.
       Class.forName(driver).newInstance();
     } catch (Exception e) {
       e.printStackTrace();
       return null;
     }

     try {
      //Establish a connection to the database.
       conn = DriverManager.getConnection(sourceURL, username, passwd);
       System.out.println("Connection succeed!");
     } catch (Exception e) {
       e.printStackTrace();
       return null;
     }

     return conn;
}
 
  //Run an ordinary SQL statement. Create a jdbc_test1 table.
   public static void CreateTable(Connection conn) {
     Statement stmt = null;
     try {
       stmt = conn.createStatement();

       // add ctrl+c handler
       Runtime.getRuntime().addShutdownHook(new ExitHandler(stmt));

      // Run an ordinary SQL statement.
       int rc2 = stmt
          .executeUpdate("DROP TABLE if exists jdbc_test1;");

       int rc1 = stmt
          .executeUpdate("CREATE TABLE jdbc_test1(col1 INTEGER, col2 VARCHAR(10));");

       stmt.close();
     } catch (SQLException e) {
       if (stmt != null) {
         try {
           stmt.close();
         } catch (SQLException e1) {
           e1.printStackTrace();
         }
       }
       e.printStackTrace();
     }
   }

  //Run the preprocessing statement to insert data in batches.
   public static void BatchInsertData(Connection conn) {
     PreparedStatement pst = null;

     try {
      //Generate a prepared statement.
       pst = conn.prepareStatement("INSERT INTO jdbc_test1 VALUES (?,?)");
       for (int i = 0; i < 100; i++) {
        //Add parameters.
         pst.setInt(1, i);
         pst.setString(2, "data " + i);
         pst.addBatch();
       }
      //Perform batch processing.
       pst.executeBatch();
       pst.close();
     } catch (SQLException e) {
       if (pst != null) {
         try {
           pst.close();
         } catch (SQLException e1) {
         e1.printStackTrace();
         }
       }
       e.printStackTrace();
     }
   }
 
  //Run the precompilation statement to update data.
   private static boolean QueryRedo(Connection conn){
     PreparedStatement pstmt = null;
     boolean retValue = false;
     try {
       pstmt = conn
           .prepareStatement("SELECT col1 FROM jdbc_test1 WHERE col2 = ?");
 
           pstmt.setString(1, "data 10");
           ResultSet rs = pstmt.executeQuery();

           while (rs.next()) {
               System.out.println("col1 = " + rs.getString("col1"));
           }
           rs.close();
 
       pstmt.close();
        retValue = true;
      } catch (SQLException e) {
       System.out.println("catch...... retValue " + retValue);
       if (pstmt != null) {
         try {
          pstmt.close();
        } catch (SQLException e1) {
          e1.printStackTrace();
         }
       }
       e.printStackTrace();
     }
 
      System.out.println("finesh......"); 
     return retValue;
   }

//Run a query statement and retry upon a failure. The number of retry times can be configured.
   public static void ExecPreparedSQL(Connection conn) throws InterruptedException {
         int maxRetryTime = 50;
         int time = 0;
         String result = null;
         do {
             time++;
             try {
  System.out.println("time:" + time);
  boolean ret = QueryRedo(conn);
  if(ret == false){
   System.out.println("retry, time:" + time);
   Thread.sleep(10000); 
   QueryRedo(conn);
  }
             } catch (Exception e) {
                 e.printStackTrace();
             }
         } while (null == result && time < maxRetryTime); 
 
   }

   /**
   * Main process. Call static methods one by one.
    * @param args
  * @throws InterruptedException 
   */
   public static void main(String[] args) throws InterruptedException {
    //Establish a connection to the database.
     Connection conn = GetConnection("testuser", "test@123");

    //Create a table.
     CreateTable(conn);

    //Insert data in batches.
     BatchInsertData(conn);

    //Run the precompilation statement to update data.
     ExecPreparedSQL(conn);

    //Disconnect from the database.
     try {
       conn.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }

   }

 }