Using the Streaming Read Function
Streaming read function: All data is read at a time by the server and sent to the socket buffer of a client until the buffer is full. If there is free space, the data continues to be sent to the buffer. At the same time, a JVM reads data from the buffer row by row.
The advantage is that the result is processed fast and no JVM memory overflow occurs. The disadvantage is that streaming read can only traverse backwards. Before data processing is complete or the statement is closed, no other operations can be performed in the current connection.
Prerequisites for code running: Add the gaussdbjdbc.jar package as required. For example, if you use an IDE to run code, you need to add the gaussdbjdbc.jar package to the local IDE.
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 |
// There will be security risks if the username and password used for authentication are directly written into code. It is recommended that the username and password be stored in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security. // In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (set the environment variable names based on the actual situation). // You need to change the values of $ip, $port, and database. import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Stream { // Establish a database connection. public static Connection getConnection(String username, String passwd) { String driver = "com.huawei.gaussdb.jdbc.Driver"; String sourceURL = "jdbc:gaussdb://$ip:$port/database?enableStreamingQuery=true"; Connection conn = null; try { // Load the driver. Class.forName(driver); } catch (Exception e) { e.printStackTrace(); return null; } try { // Connect to the database in non-encrypted mode. conn = DriverManager.getConnection(sourceURL, username, passwd); System.out.println("Connection succeed!"); } catch (Exception e) { e.printStackTrace(); return null; } return conn; } // Execute basic SQL statements to create table t_user. public static void CreateTable(Connection conn) { Statement stmt = null; try { stmt = conn.createStatement(); // Execute basic SQL statements. stmt.executeUpdate("DROP TABLE IF EXISTS t_user"); stmt.executeUpdate("CREATE TABLE t_user(id int, name VARCHAR(20));"); stmt.close(); } catch (SQLException e) { if (stmt != null) { try { stmt.close(); } catch (SQLException e1) { e1.printStackTrace(); } } e.printStackTrace(); } } // Execute a prepared 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 t_user VALUES (?,?)"); for (int i = 0; i < 20; i++) { // Add parameters. pst.setInt(1, i + 1); pst.setString(2, "name " + (i + 1)); 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(); } } // Enable streaming read and query the content in the t_user table. public static void StreamingQuery(Connection conn) { PreparedStatement pst = null; ResultSet resultSet = null; try { // Query all values in the t_user table. pst = conn.prepareStatement("SELECT * FROM t_user"); pst.setFetchSize(Integer.MIN_VALUE); resultSet = pst.executeQuery(); while (resultSet.next()) { System.out.println(resultSet.getInt(1)); } } catch (SQLException e) { throw new RuntimeException(e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if (pst != null) { try { pst.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } } public static void main(String[] args) throws Exception { String userName = System.getenv("EXAMPLE_USERNAME_ENV"); String password = System.getenv("EXAMPLE_PASSWORD_ENV"); Connection conn = getConnection(userName, password); CreateTable(conn); BatchInsertData(conn); StreamingQuery(conn); // Close the connection to the database. try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } |
The execution result of the preceding example is as follows:
Connection succeed! 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

When the streaming read function is used, you need to perform the resultSet.close() or statement.close() operation after the result set is used. Otherwise, the current connection is unavailable.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot