You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by se...@apache.org on 2021/10/01 14:36:41 UTC
[ignite-3] branch main updated: IGNITE-15545 JDBC Sql queries
example. Fixes #358
This is an automated email from the ASF dual-hosted git repository.
sergeychugunov pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new f6af397 IGNITE-15545 JDBC Sql queries example. Fixes #358
f6af397 is described below
commit f6af397356cceaa898820b1ddaf3d20da2017e57
Author: Vladimir Ermakov <85...@users.noreply.github.com>
AuthorDate: Fri Oct 1 17:33:08 2021 +0300
IGNITE-15545 JDBC Sql queries example. Fixes #358
Signed-off-by: Sergey Chugunov <se...@gmail.com>
---
examples/pom.xml | 5 +
.../org/apache/ignite/sql/jdbc/SqlJdbcExample.java | 253 +++++++++++++++++++++
2 files changed, 258 insertions(+)
diff --git a/examples/pom.xml b/examples/pom.xml
index 0bb9f56..fe42fbe 100644
--- a/examples/pom.xml
+++ b/examples/pom.xml
@@ -37,5 +37,10 @@
<groupId>org.apache.ignite</groupId>
<artifactId>ignite-runner</artifactId>
</dependency>
+
+ <dependency>
+ <groupId>org.apache.ignite</groupId>
+ <artifactId>ignite-client</artifactId>
+ </dependency>
</dependencies>
</project>
diff --git a/examples/src/main/java/org/apache/ignite/sql/jdbc/SqlJdbcExample.java b/examples/src/main/java/org/apache/ignite/sql/jdbc/SqlJdbcExample.java
new file mode 100644
index 0000000..21f7b45
--- /dev/null
+++ b/examples/src/main/java/org/apache/ignite/sql/jdbc/SqlJdbcExample.java
@@ -0,0 +1,253 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.ignite.sql.jdbc;
+
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.Statement;
+import org.apache.ignite.app.Ignite;
+import org.apache.ignite.app.IgnitionManager;
+import org.apache.ignite.internal.schema.configuration.SchemaConfigurationConverter;
+import org.apache.ignite.schema.SchemaBuilders;
+import org.apache.ignite.schema.definition.ColumnType;
+import org.apache.ignite.schema.definition.TableDefinition;
+import org.apache.ignite.table.Table;
+
+/**
+ * This example demonstrates usage of Ignite JDBC driver.
+ * <p>
+ * To run the example, do the following:
+ * <ol>
+ * <li>Import the examples project into you IDE.</li>
+ * <li>Run the example in the IDE.</li>
+ * </ol>
+ */
+public class SqlJdbcExample {
+ /**
+ * Executes example.
+ *
+ * @param args Command line arguments, none required.
+ * @throws Exception If example execution failed.
+ */
+ public static void main(String[] args) throws Exception {
+ Ignite ignite = IgnitionManager.start(
+ "node-0",
+ Files.readString(Path.of( "examples/config/ignite-config.json").toAbsolutePath()),
+ Path.of("work")
+ );
+
+ print("JDBC example started.");
+
+ // Open JDBC connection
+ try (Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.1.1:10800/")) {
+ print("Connected to server.");
+
+ //---------------------------------------------------------------------------------
+ //
+ // Creating City table.
+ //
+ // CREATE TABLE city (
+ // id INT PRIMARY KEY,
+ // name VARCHAR
+ // )
+ //
+ //---------------------------------------------------------------------------------
+ TableDefinition cityTbl = SchemaBuilders.tableBuilder("PUBLIC", "CITY").columns(
+ SchemaBuilders.column("ID", ColumnType.INT32).asNonNull().build(),
+ SchemaBuilders.column("NAME", ColumnType.string()).asNullable().build()
+ ).withPrimaryKey("ID").build();
+
+ ignite.tables().createTable(cityTbl.canonicalName(), tblCh ->
+ SchemaConfigurationConverter.convert(cityTbl, tblCh)
+ .changeReplicas(1)
+ .changePartitions(3)
+ );
+ //---------------------------------------------------------------------------------
+ //
+ // Creating accounts table.
+ //
+ // CREATE TABLE accounts (
+ // accountId INT PRIMARY KEY,
+ // cityId INT,
+ // firstName VARCHAR,
+ // lastName VARCHAR,
+ // balance DOUBLE
+ // )
+ //
+ //---------------------------------------------------------------------------------
+
+ TableDefinition accountsTbl = SchemaBuilders.tableBuilder("PUBLIC", "ACCOUNTS").columns(
+ SchemaBuilders.column("ACCOUNTID", ColumnType.INT32).asNonNull().build(),
+ SchemaBuilders.column("CITYID", ColumnType.INT32).asNonNull().build(),
+ SchemaBuilders.column("FIRSTNAME", ColumnType.string()).asNullable().build(),
+ SchemaBuilders.column("LASTNAME", ColumnType.string()).asNullable().build(),
+ SchemaBuilders.column("BALANCE", ColumnType.DOUBLE).asNullable().build()
+ ).withPrimaryKey("ACCOUNTID").build();
+
+ ignite.tables().createTable(accountsTbl.canonicalName(), tblCh ->
+ SchemaConfigurationConverter.convert(accountsTbl, tblCh)
+ .changeReplicas(1)
+ .changePartitions(3)
+ );
+
+ print("Created database objects.");
+
+ // Populate City table with PreparedStatement.
+ try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO city (id, name) VALUES (?, ?)")) {
+ stmt.setInt(1, 1);
+ stmt.setString(2, "Forest Hill");
+ stmt.executeUpdate();
+
+ stmt.setInt(1, 2);
+ stmt.setString(2, "Denver");
+ stmt.executeUpdate();
+
+ stmt.setInt(1, 3);
+ stmt.setString(2, "St. Petersburg");
+ stmt.executeUpdate();
+ }
+
+ // Populate Person table with PreparedStatement.
+ try (PreparedStatement stmt =
+ conn.prepareStatement("INSERT INTO accounts (accountId, cityId, firstName, lastName, balance)" +
+ " values (?, ?, ?, ?, ?)")) {
+ stmt.setInt(1, 1);
+ stmt.setInt(2, 1);
+ stmt.setString(3, "John");
+ stmt.setString(4, "Doe");
+ stmt.setDouble(5, 1000.0d);
+ stmt.executeUpdate();
+
+ stmt.setInt(1, 2);
+ stmt.setInt(2, 1);
+ stmt.setString(3, "Jane");
+ stmt.setString(4, "Roe");
+ stmt.setDouble(5, 2000.0d);
+ stmt.executeUpdate();
+
+ stmt.setInt(1, 3);
+ stmt.setInt(2, 2);
+ stmt.setString(3, "Mary");
+ stmt.setString(4, "Major");
+ stmt.setDouble(5, 1500.0d);
+ stmt.executeUpdate();
+
+ stmt.setInt(1, 4);
+ stmt.setInt(2, 3);
+ stmt.setString(3, "Richard");
+ stmt.setString(4, "Miles");
+ stmt.setDouble(5, 1450.0d);
+ stmt.executeUpdate();
+ }
+
+ print("Accounts with city.");
+
+ //---------------------------------------------------------------------------------
+ //
+ // Gets accounts joined with cities.
+ //
+ //---------------------------------------------------------------------------------
+
+ try (Statement stmt = conn.createStatement()) {
+ try (ResultSet rs =
+ stmt.executeQuery("SELECT a.firstName, a.lastName, c.name FROM accounts a " +
+ "INNER JOIN city c on c.id = a.cityId")) {
+ print("Query results:");
+
+ while (rs.next()) {
+ System.out.println(">>> " + rs.getString(1) + ", " + rs.getString(2)
+ + ", " + rs.getString(3));
+ }
+ }
+ }
+
+ //---------------------------------------------------------------------------------
+ //
+ // Gets accounts with balance lower than 1500.
+ //
+ //---------------------------------------------------------------------------------
+
+ print("Accounts with low balance.");
+
+ try (Statement stmt = conn.createStatement()) {
+ try (ResultSet rs =
+ stmt.executeQuery("SELECT a.firstName, a.lastName, a.balance FROM accounts a " +
+ "WHERE a.balance < 1500.0")) {
+ print("Query results:");
+
+ while (rs.next()) {
+ System.out.println(">>> " + rs.getString(1) + ", " + rs.getString(2)
+ + ", " + rs.getDouble(3));
+ }
+ }
+ }
+
+ //---------------------------------------------------------------------------------
+ //
+ // Delete one of accounts.
+ //
+ //---------------------------------------------------------------------------------
+
+ print("Delete an account with id '1'.");
+
+ try (PreparedStatement stmt = conn.prepareStatement("DELETE FROM accounts WHERE accountId = ?")) {
+ stmt.setInt(1, 1);
+ stmt.executeUpdate();
+ }
+
+ print("Existing accounts.");
+
+ try (Statement stmt = conn.createStatement()) {
+ try (ResultSet rs =
+ stmt.executeQuery("SELECT a.firstName, a.lastName, c.name FROM accounts a " +
+ "INNER JOIN city c on c.id = a.cityId")) {
+ print("Query results:");
+
+ while (rs.next()) {
+ System.out.println(">>> " + rs.getString(1) + ", " + rs.getString(2)
+ + ", " + rs.getString(3));
+ }
+ }
+ }
+
+ // Drop database objects.
+ for (Table table : ignite.tables().tables()) {
+ print("Drop table: " + table.tableName());
+
+ ignite.tables().dropTable(table.tableName());
+ }
+ }
+
+ print("JDBC example finished.");
+
+ ignite.close();
+ }
+
+ /**
+ * Prints message.
+ *
+ * @param msg Message to print before all objects are printed.
+ */
+ private static void print(String msg) {
+ System.out.println("\n>>> " + msg);
+ }
+}