IGNITE-5679: Example for thin JDBC driver. This closes #2232.
authortledkov-gridgain <tledkov@gridgain.com>
Mon, 10 Jul 2017 12:19:58 +0000 (15:19 +0300)
committerdevozerov <vozerov@gridgain.com>
Mon, 10 Jul 2017 12:19:58 +0000 (15:19 +0300)
examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryDdlExample.java
examples/src/main/java/org/apache/ignite/examples/datagrid/JdbcExample.java [new file with mode: 0644]
examples/src/test/java/org/apache/ignite/examples/CacheExamplesMultiNodeSelfTest.java
examples/src/test/java/org/apache/ignite/examples/CacheExamplesSelfTest.java

index 84a67cd..201dda1 100644 (file)
@@ -56,33 +56,33 @@ public class CacheQueryDdlExample {
             try (
                 IgniteCache<?, ?> cache = ignite.getOrCreateCache(cacheCfg)
             ) {
-                // Create table based on PARTITIONED template with one backup.
-                cache.query(new SqlFieldsQuery(
-                    "CREATE TABLE person (id LONG PRIMARY KEY, name VARCHAR, city_id LONG) " +
-                    "WITH \"backups=1\"")).getAll();
-
                 // Create reference City table based on REPLICATED template.
                 cache.query(new SqlFieldsQuery(
                     "CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) WITH \"template=replicated\"")).getAll();
 
+                // Create table based on PARTITIONED template with one backup.
+                cache.query(new SqlFieldsQuery(
+                    "CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id)) " +
+                    "WITH \"backups=1, affinityKey=city_id\"")).getAll();
+
                 // Create an index.
                 cache.query(new SqlFieldsQuery("CREATE INDEX on Person (city_id)")).getAll();
 
                 print("Created database objects.");
 
-                SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO person (id, name, city_id) values (?, ?, ?)");
+                SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO city (id, name) VALUES (?, ?)");
+
+                cache.query(qry.setArgs(1L, "Forest Hill")).getAll();
+                cache.query(qry.setArgs(2L, "Denver")).getAll();
+                cache.query(qry.setArgs(3L, "St. Petersburg")).getAll();
+
+                qry = new SqlFieldsQuery("INSERT INTO person (id, name, city_id) values (?, ?, ?)");
 
                 cache.query(qry.setArgs(1L, "John Doe", 3L)).getAll();
                 cache.query(qry.setArgs(2L, "Jane Roe", 2L)).getAll();
                 cache.query(qry.setArgs(3L, "Mary Major", 1L)).getAll();
                 cache.query(qry.setArgs(4L, "Richard Miles", 2L)).getAll();
 
-                qry = new SqlFieldsQuery("INSERT INTO city (id, name) VALUES (?, ?)");
-
-                cache.query(qry.setArgs(1L, "Forest Hill")).getAll();
-                cache.query(qry.setArgs(2L, "Denver")).getAll();
-                cache.query(qry.setArgs(3L, "St. Petersburg")).getAll();
-
                 print("Populated data.");
 
                 List<List<?>> res = cache.query(new SqlFieldsQuery(
@@ -91,7 +91,7 @@ public class CacheQueryDdlExample {
                 print("Query results:");
 
                 for (Object next : res)
-                    System.out.println(">>>     " + next);
+                    System.out.println(">>>    " + next);
 
                 cache.query(new SqlFieldsQuery("drop table Person")).getAll();
                 cache.query(new SqlFieldsQuery("drop table City")).getAll();
diff --git a/examples/src/main/java/org/apache/ignite/examples/datagrid/JdbcExample.java b/examples/src/main/java/org/apache/ignite/examples/datagrid/JdbcExample.java
new file mode 100644 (file)
index 0000000..bc96e42
--- /dev/null
@@ -0,0 +1,135 @@
+/*
+ * 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.examples.datagrid;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.Statement;
+import org.apache.ignite.examples.ExampleNodeStartup;
+
+/**
+ * This example demonstrates usage of Ignite JDBC driver.
+ * <p>
+ * Ignite nodes must be started in separate process using {@link ExampleNodeStartup} before running this example.
+ */
+public class JdbcExample {
+    /**
+     * Executes example.
+     *
+     * @param args Command line arguments, none required.
+     * @throws Exception If example execution failed.
+     */
+    public static void main(String[] args) throws Exception {
+        print("JDBC example started.");
+
+        // Open JDBC connection
+        try (Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1/")) {
+            print("Connected to server.");
+
+            // Create database objects.
+            try (Statement stmt = conn.createStatement()) {
+                // Create reference City table based on REPLICATED template.
+                stmt.executeUpdate("CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) " +
+                    "WITH \"template=replicated\"");
+
+                // Create table based on PARTITIONED template with one backup.
+                stmt.executeUpdate("CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, " +
+                    "PRIMARY KEY (id, city_id)) WITH \"backups=1, affinityKey=city_id\"");
+
+                // Create an index.
+                stmt.executeUpdate("CREATE INDEX on Person (city_id)");
+            }
+
+            print("Created database objects.");
+
+            // Populate City table with PreparedStatement.
+            try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO city (id, name) VALUES (?, ?)")) {
+                stmt.setLong(1, 1L);
+                stmt.setString(2, "Forest Hill");
+                stmt.executeUpdate();
+
+                stmt.setLong(1, 2L);
+                stmt.setString(2, "Denver");
+                stmt.executeUpdate();
+
+                stmt.setLong(1, 3L);
+                stmt.setString(2, "St. Petersburg");
+                stmt.executeUpdate();
+            }
+
+            // Populate Person table with PreparedStatement.
+            try (PreparedStatement stmt =
+                conn.prepareStatement("INSERT INTO person (id, name, city_id) values (?, ?, ?)")) {
+                stmt.setLong(1, 1L);
+                stmt.setString(2, "John Doe");
+                stmt.setLong(3, 3L);
+                stmt.executeUpdate();
+
+                stmt.setLong(1, 2L);
+                stmt.setString(2, "Jane Roe");
+                stmt.setLong(3, 2L);
+                stmt.executeUpdate();
+
+                stmt.setLong(1, 3L);
+                stmt.setString(2, "Mary Major");
+                stmt.setLong(3, 1L);
+                stmt.executeUpdate();
+
+                stmt.setLong(1, 4L);
+                stmt.setString(2, "Richard Miles");
+                stmt.setLong(3, 2L);
+                stmt.executeUpdate();
+            }
+
+            print("Populated data.");
+
+            // Get data.
+            try (Statement stmt = conn.createStatement()) {
+                try (ResultSet rs =
+                    stmt.executeQuery("SELECT p.name, c.name FROM Person p INNER JOIN City c on c.id = p.city_id")) {
+                    print("Query results:");
+
+                    while (rs.next())
+                        System.out.println(">>>    " + rs.getString(1) + ", " + rs.getString(2));
+                }
+            }
+
+            // Drop database objects.
+            try (Statement stmt = conn.createStatement()) {
+                stmt.executeUpdate("DROP TABLE Person");
+                stmt.executeUpdate("DROP TABLE City");
+            }
+
+            print("Dropped database objects.");
+        }
+
+        print("JDBC example finished.");
+    }
+
+    /**
+     * Prints message.
+     *
+     * @param msg Message to print before all objects are printed.
+     */
+    private static void print(String msg) {
+        System.out.println();
+        System.out.println(">>> " + msg);
+    }
+}
\ No newline at end of file
index 6de0273..f940ff7 100644 (file)
@@ -17,6 +17,8 @@
 
 package org.apache.ignite.examples;
 
+import org.apache.ignite.examples.datagrid.JdbcExample;
+
 /**
  * Cache examples multi-node self test.
  */
@@ -42,4 +44,11 @@ public class CacheExamplesMultiNodeSelfTest extends CacheExamplesSelfTest {
 
         super.testCacheLockExample();
     }
+
+    /**
+     * @throws Exception If failed.
+     */
+    public void testJdbcThinExample() throws Exception {
+        JdbcExample.main(EMPTY_ARGS);
+    }
 }
\ No newline at end of file
index f65d97c..30f0763 100644 (file)
@@ -139,7 +139,7 @@ public class CacheExamplesSelfTest extends GridAbstractExamplesTest {
     /**
      * @throws Exception If failed.
      */
-    public void testCacheQUeryDdlExample() throws Exception {
+    public void testCacheQueryDdlExample() throws Exception {
         CacheQueryDdlExample.main(EMPTY_ARGS);
     }