🔧 Using JDBC to Retrieve Cursor Data in GBase 8c
Nachrichtenbereich: 🔧 Programmierung
🔗 Quelle: dev.to
GBase 8c is a multi-modal, distributed database that supports various storage modes such as row storage, column storage, and memory storage, as well as multiple deployment forms including standalone, master-slave, and distributed. GBase 8c boasts high performance, high availability, elastic scalability, and high security, and can be deployed on physical machines, virtual machines, containers, private clouds, and public clouds. It provides secure, stable, and reliable data storage and management services for key industry core systems, internet business systems, and government and enterprise business systems.
How can you retrieve cursor data from a GBase 8c database using JDBC? This process is explained through the following example (Note: Steps 1 and 2 below are executed on the remote client of GBase 8c V5 3.0.0 version, while steps 3 and 4 are executed in the Java environment).
Prerequisites: Correctly install GBase 8c and configure remote connections; the JDBC driver can be downloaded from the official website or obtained by consulting GBase technical staff. Official website: GBase Download
Steps:
1. Create a Custom Function
First, create a custom function to achieve a specific effect, for example, to get the username where the cursor is located:
CREATE OR REPLACE FUNCTION get_users()
RETURNS refcursor AS $$
DECLARE
result_cursor refcursor;
BEGIN
OPEN result_cursor FOR
SELECT * FROM users;
RETURN result_cursor;
END;
$$ LANGUAGE plpgsql;
2. Create a Test Table
create table users(id int, name varchar(10));
insert into users values(1, 'John Doe'), (2, 'Jane Smith'), (3, 'Alex Brown');
3. Call the Cursor Data in Java
public class jdbccallproc {
public static void main(String[] args) {
String jdbcURL = "jdbc:gbase8c://172.16.5.102:5432/postgres?loggerLevel=warning";
String username = "regress";
String password = "gbase;234";
try {
// 1. Register GBase8c JDBC Driver
Class.forName("com.gbase8c.Driver");
// 2. Establish Connection
Connection connection = DriverManager.getConnection(jdbcURL, username, password);
// 3. Call Stored Procedure
String call = "{ ? = call get_users() }";
connection.setAutoCommit(false);
CallableStatement stmt = connection.prepareCall(call);
// 4. Register Output Parameter Type
//stmt.registerOutParameter(1, OracleTypes.CURSOR);
//stmt.registerOutParameter(1, 1111);
stmt.registerOutParameter(1, Types.REF_CURSOR);
// 5. Execute Query
stmt.execute();
// 6. Retrieve Result Set
ResultSet rs = (ResultSet) stmt.getObject(1);
// 7. Process Result Set
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
// Process other fields as needed
System.out.println("ID: " + id + ", Name: " + name);
}
// 8. Close Connection
rs.close();
stmt.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
Note:
-
setAutoCommit(false)is required to prevent exceptions. - The
registerOutParametertype supports Oracle'sOracleTypes.CURSOR,1111, andTypes.REF_CURSOR. The example comments out the first two types; feel free to experiment with them.
4. Expected Output
ID: 1, Name: John Doe
ID: 2, Name: Jane Smith
ID: 3, Name: Alex Brown
This example serves as an introduction, and we welcome technical exchanges~
...
🔧 Using JDBC to Retrieve Cursor Data in GBase 8c
📈 78.51 Punkte
🔧 Programmierung
🔧 Guide to Using GBase 8s JDBC Driver URL
📈 40.84 Punkte
🔧 Programmierung
🔧 Introduction to GBase 8s Static Cursor
📈 33.07 Punkte
🔧 Programmierung
🔧 using slack api to retrieve data
📈 26.83 Punkte
🔧 Programmierung
🐧 Connect to Oracle DB via JDBC Driver Using Java
📈 23.23 Punkte
🐧 Linux Tipps
🐧 Connect to Oracle DB via JDBC Driver Using Python
📈 23.23 Punkte
🐧 Linux Tipps
🔧 Using SQL-Tracing in GBase 8s
📈 22.23 Punkte
🔧 Programmierung