You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by am...@apache.org on 2022/06/06 15:11:01 UTC
[ignite-3] branch ignite-17088 created (now a85805b67)
This is an automated email from the ASF dual-hosted git repository.
amashenkov pushed a change to branch ignite-17088
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
at a85805b67 Add example of using SQL API.
This branch includes the following new commits:
new a85805b67 Add example of using SQL API.
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
[ignite-3] 01/01: Add example of using SQL API.
Posted by am...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
amashenkov pushed a commit to branch ignite-17088
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
commit a85805b67ec816bb02492e82eb5d88e0cad61411
Author: Andrew Mashenkov <an...@gmail.com>
AuthorDate: Mon Jun 6 18:10:53 2022 +0300
Add example of using SQL API.
---
.../example/sql/{jdbc => }/ItSqlExamplesTest.java | 33 +++-
.../apache/ignite/example/sql/SqlApiExample.java | 210 +++++++++++++++++++++
2 files changed, 242 insertions(+), 1 deletion(-)
diff --git a/examples/src/integrationTest/java/org/apache/ignite/example/sql/jdbc/ItSqlExamplesTest.java b/examples/src/integrationTest/java/org/apache/ignite/example/sql/ItSqlExamplesTest.java
similarity index 62%
rename from examples/src/integrationTest/java/org/apache/ignite/example/sql/jdbc/ItSqlExamplesTest.java
rename to examples/src/integrationTest/java/org/apache/ignite/example/sql/ItSqlExamplesTest.java
index 854051357..9d7662596 100644
--- a/examples/src/integrationTest/java/org/apache/ignite/example/sql/jdbc/ItSqlExamplesTest.java
+++ b/examples/src/integrationTest/java/org/apache/ignite/example/sql/ItSqlExamplesTest.java
@@ -15,11 +15,13 @@
* limitations under the License.
*/
-package org.apache.ignite.example.sql.jdbc;
+package org.apache.ignite.example.sql;
import static org.apache.ignite.example.ExampleTestUtils.assertConsoleOutputContains;
import org.apache.ignite.example.AbstractExamplesTest;
+import org.apache.ignite.example.sql.jdbc.SqlJdbcExample;
+import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
/**
@@ -50,4 +52,33 @@ public class ItSqlExamplesTest extends AbstractExamplesTest {
+ " Richard, Miles, St. Petersburg\n"
);
}
+
+ /**
+ * Runs SqlApiExample and checks its output.
+ *
+ * @throws Exception If failed.
+ */
+ @Disabled("https://issues.apache.org/jira/browse/IGNITE-17057")
+ @Test
+ public void testSqlApiExample() throws Exception {
+ assertConsoleOutputContains(SqlApiExample::main, EMPTY_ARGS,
+ "\nAdded cities: 3",
+ "\nAdded accounts: 4",
+
+ "\nAll accounts:\n"
+ + " John, Doe, Forest Hill\n"
+ + " Jane, Roe, Forest Hill\n"
+ + " Mary, Major, Denver\n"
+ + " Richard, Miles, St. Petersburg\n",
+
+ "\nAccounts with balance lower than 1,500:\n"
+ + " John, Doe, 1000.0\n"
+ + " Richard, Miles, 1450.0\n",
+
+ "\nAll accounts:\n"
+ + " Jane, Roe, Forest Hill\n"
+ + " Mary, Major, Denver\n"
+ + " Richard, Miles, St. Petersburg\n"
+ );
+ }
}
diff --git a/examples/src/main/java/org/apache/ignite/example/sql/SqlApiExample.java b/examples/src/main/java/org/apache/ignite/example/sql/SqlApiExample.java
new file mode 100644
index 000000000..1c582932f
--- /dev/null
+++ b/examples/src/main/java/org/apache/ignite/example/sql/SqlApiExample.java
@@ -0,0 +1,210 @@
+package org.apache.ignite.example.sql;
+
+import java.util.Arrays;
+import java.util.concurrent.CompletableFuture;
+import java.util.concurrent.CompletionStage;
+import org.apache.ignite.client.IgniteClient;
+import org.apache.ignite.sql.BatchedArguments;
+import org.apache.ignite.sql.ResultSet;
+import org.apache.ignite.sql.Session;
+import org.apache.ignite.sql.SqlRow;
+import org.apache.ignite.sql.Statement;
+import org.apache.ignite.sql.async.AsyncResultSet;
+
+public class SqlApiExample {
+ /**
+ * Main method of the example.
+ *
+ * @param args The command line arguments.
+ * @throws Exception If failed.
+ */
+ public static void main(String[] args) throws Exception {
+ //--------------------------------------------------------------------------------------
+ //
+ // Creating a client to connect to the cluster.
+ //
+ //--------------------------------------------------------------------------------------
+
+ System.out.println("\nConnecting to server...");
+
+ try (IgniteClient client = IgniteClient.builder()
+ .addresses("127.0.0.1:10800")
+ .build()
+ ) {
+ //--------------------------------------------------------------------------------------
+ //
+ // Creating tables.
+ //
+ //--------------------------------------------------------------------------------------
+
+ try (Session ses = client.sql().createSession()) {
+ ses.execute(
+ null,
+ "CREATE TABLE CITIES ("
+ + "ID INT PRIMARY KEY,"
+ + "NAME VARCHAR)"
+ ).close(); // Ignore result.
+
+ ses.execute(
+ null,
+ "CREATE TABLE ACCOUNTS ("
+ + " ACCOUNT_ID INT PRIMARY KEY,"
+ + " CITY_ID INT,"
+ + " FIRST_NAME VARCHAR,"
+ + " LAST_NAME VARCHAR,"
+ + " BALANCE DOUBLE)"
+ ).close();
+
+ //--------------------------------------------------------------------------------------
+ //
+ // Populating 'CITIES' table.
+ //
+ //--------------------------------------------------------------------------------------
+
+ System.out.println("\nPopulating 'CITIES' table...");
+
+ try (Statement stmt = client.sql().createStatement("INSERT INTO CITIES (ID, NAME) VALUES (?, ?)")) {
+ long rowsAdded = 0;
+
+ try (ResultSet rs = ses.execute(null, stmt, 1, "Forest Hill")) {
+ rowsAdded += rs.affectedRows();
+ }
+ try (ResultSet rs = ses.execute(null, stmt, 2, "Denver")) {
+ rowsAdded += rs.affectedRows();
+ }
+ try (ResultSet rs = ses.execute(null, stmt, 3, "St. Petersburg")) {
+ rowsAdded += rs.affectedRows();
+ }
+
+ System.out.println("\nAdded cities: " + rowsAdded);
+ }
+
+ //--------------------------------------------------------------------------------------
+ //
+ // Populating 'ACCOUNTS' table.
+ //
+ //--------------------------------------------------------------------------------------
+
+ System.out.println("\nPopulating 'ACCOUNTS' table...");
+
+ long rowsAdded = Arrays.stream(ses.executeBatch(null,
+ "INSERT INTO ACCOUNTS (ACCOUNT_ID, CITY_ID, FIRST_NAME, LAST_NAME, BALANCE) values (?, ?, ?, ?, ?)",
+ BatchedArguments.of(1, 1, "John", "Doe", 1000.0d)
+ .add(2, 1, "Jane", "Roe", 2000.0d)
+ .add(3, 1, "Mary", "Major", 1500.0d)
+ .add(4, 1, "Richard", "Miles", 1450.0d)))
+ .asLongStream().sum();
+
+ System.out.println("\nAdded accounts: " + rowsAdded);
+
+ //--------------------------------------------------------------------------------------
+ //
+ // Requesting information about all account owners.
+ //
+ //--------------------------------------------------------------------------------------
+
+ System.out.println("\nAll accounts:");
+
+ try (ResultSet rs = ses.execute(null,
+ "SELECT a.FIRST_NAME, a.LAST_NAME, c.NAME FROM ACCOUNTS a "
+ + "INNER JOIN CITIES c on c.ID = a.CITY_ID ORDER BY a.ACCOUNT_ID")) {
+ while (rs.hasNext()) {
+ SqlRow row = rs.next();
+
+ System.out.println(" "
+ + row.stringValue(1) + ", "
+ + row.stringValue(2) + ", "
+ + row.stringValue(3));
+ }
+ }
+
+ //--------------------------------------------------------------------------------------
+ //
+ // Requesting accounts with balances lower than 1,500.
+ //
+ //--------------------------------------------------------------------------------------
+
+ System.out.println("\nAccounts with balance lower than 1,500:");
+
+ try (ResultSet rs = ses.execute(null,
+ "SELECT a.FIRST_NAME, a.LAST_NAME, a.BALANCE FROM ACCOUNTS a WHERE a.BALANCE < 1500.0 "
+ + "ORDER BY a.ACCOUNT_ID")) {
+ while (rs.hasNext()) {
+ SqlRow row = rs.next();
+
+ System.out.println(" "
+ + row.stringValue(1) + ", "
+ + row.stringValue(2) + ", "
+ + row.stringValue(3));
+ }
+ }
+
+ //--------------------------------------------------------------------------------------
+ //
+ // Deleting one of the accounts.
+ //
+ //--------------------------------------------------------------------------------------
+
+ System.out.println("\nDeleting one of the accounts...");
+
+ try (ResultSet ignored = ses.execute(null, "DELETE FROM ACCOUNTS WHERE ACCOUNT_ID = ?", 1)) {
+ }
+
+ //--------------------------------------------------------------------------------------
+ //
+ // Requesting information about all account owners once again
+ // to verify that the account was actually deleted.
+ //
+ //--------------------------------------------------------------------------------------
+
+ System.out.println("\nAll accounts:");
+
+ // Async way.
+ Statement stmt = client.sql().statementBuilder()
+ .query("SELECT a.FIRST_NAME, a.LAST_NAME, c.NAME FROM ACCOUNTS a INNER JOIN CITIES c on c.ID = a.CITY_ID ORDER BY a.ACCOUNT_ID")
+ .pageSize(1)
+ .build();
+
+ ses.executeAsync(null, stmt)
+ .thenCompose(SqlApiExample::fetchAllRowsInto)
+ .get();
+
+ stmt.close();
+
+ System.out.println("\nDropping the tables...");
+
+ ses.execute(null, "DROP TABLE ACCOUNTS").close();
+ ses.execute(null, "DROP TABLE CITIES").close();
+ }
+ }
+ }
+
+ /**
+ * Fetch full result set asynchronously.
+ * @param resultSet Async result set.
+ * @return Operation future.
+ */
+ private static CompletionStage<Void> fetchAllRowsInto(AsyncResultSet resultSet) {
+ //
+ // Process current page.
+ //
+ for (var row : resultSet.currentPage()) {
+ System.out.println(" "
+ + row.stringValue(1) + ", "
+ + row.stringValue(2) + ", "
+ + row.stringValue(3));
+ }
+
+ //
+ // Finish if no more data.
+ //
+ if (!resultSet.hasMorePages()) {
+ return CompletableFuture.completedFuture(null);
+ }
+
+ //
+ // Request for the next page in async way, then subscribe to the response.
+ //
+ return resultSet.fetchNextPage().thenCompose(SqlApiExample::fetchAllRowsInto);
+ }
+}