Re-execute application SQL (openGauss)

When the primary database node fails and does not recover within 10 seconds, openGauss will upgrade the corresponding standby database node to the primary, so that openGauss can run normally. Jobs that were running during backup to master will fail; jobs started after backup to master will no longer be affected. If the upper-layer business is not aware of the database node active-standby switchover process, you can refer to this example to build the SQL retry mechanism at the business layer.

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 {
//Create a database connection.
public static Connection GetConnection(String username, String passwd) {
String driver = "org.opengauss.Driver";
String sourceURL = "jdbc:opengauss://10.131.72.136:8000/postgres";
Connection conn = null;
try {
/ /Load the database driver.
Class.forName(driver).newInstance();
} catch (Exception e) {
e.printStackTrace();
return null;
}

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

return conn;
}

//execution Common SQL statement to create jdbc_test1 table.
public static void CreateTable(Connection conn) {
Statement stmt = null;
try {
stmt = conn.createStatement();


Runtime.getRuntime().addShutdownHook(new ExitHandler(stmt));

//Execute common SQL statements.
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();
}
}

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

try {
//Generate prepared statements.
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();
}
//Execute batch processing.
pst.executeBatch();
pst.close();
} catch (SQLException e) {
if (pst != null) {
try {
pst.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
}
}

//Execute precompiled statements and 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;
}

//Query statement, retry execution failure, the number of retries is configurable.
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);

}

/**
* The main program calls each static method step by step.
* @param args
* @throws InterruptedException
*/
public static void main(String[] args) throws InterruptedException {
// Create a database connection.
Connection conn = GetConnection("testuser", "test@123");

//Create a table.
CreateTable(conn);

//Batch insert data.
BatchInsertData(conn);

//Execute precompiled statements to update data.
ExecPreparedSQL(conn);

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

}

}import java.sql.Connection;