Application example based on the JDBC interface provided by openGauss

//DBtest.java
//Demonstrate the main steps of JDBC-based development, which will involve creating a database, creating a table, inserting data, etc.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.Types;

public class DBTest {

// Create a database connection.
public static Connection GetConnection(String username, String passwd) {
String driver = "org.opengauss.Driver";
String sourceURL = "jdbc:opengauss://localhost:8000/postgres";
Connection conn = null;
try {
//load Database driven.
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;
};

// Execute common SQL statements to create the customer_t1 table.
public static void CreateTable(Connection conn) {
Statement stmt = null;
try {
stmt = conn.createStatement();

//Execute common SQL statements.
int rc =
stmt.executeUpdate("CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));");

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

//Execute prepared statements and insert data in batches.
public static void BatchInsertData(Connection conn) {
PreparedStatement pst = null;

try {
//Generate prepared statements.
pst = conn.prepareStatement("INSERT INTO customer_t1 VALUES (?,?)");
for (int i = 0; i < 3; 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.
public static void ExecPreparedSQL(Connection conn) {
PreparedStatement pstmt = null;
try {
pstmt = conn
.prepareStatement("UPDATE customer_t1 SET c_customer_name = ? WHERE c_customer_sk = 1");
pstmt.setString(1, "new Data");
int rowcount = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e1) {
e1.printStackTrace() ;
}
}
e.printStackTrace();
}
}


//Execute the stored procedure.
public static void ExecCallableSQL(Connection conn) {
CallableStatement cstmt = null;
try {
// The stored procedure TESTPROC needs to be created in advance.
cstmt=conn.prepareCall("{? = CALL TESTPROC(?,?,?)}");
cstmt.setInt(2, 50);
cstmt.setInt(1, 20);
cstmt.setInt(3, 90);
cstmt.registerOutParameter(4, Types.INTEGER); //Register the parameter of out type, the type is integer.
cstmt.execute();
int out = cstmt.getInt(4); //Get out parameters
System.out.println("The CallableStatment TESTPROC returns:"+out);
cstmt.close();
} catch (SQLException e) {
if (cstmt != null) {
try {
cstmt.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
}
}


/**
* The main program calls each static method.
* @param args
*/
public static void main(String[] args) {
// Create a database connection.
Connection conn = GetConnection("tester", "Password1234");

// Create a table.
CreateTable(conn);

//Batch insert data.
BatchInsertData(conn);

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

//Execute the stored procedure.
ExecCallableSQL(conn);

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

}

}