You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@geode.apache.org by jc...@apache.org on 2019/05/03 22:02:56 UTC
[geode-examples] 01/01: Add JDBC Connector example.
This is an automated email from the ASF dual-hosted git repository.
jchen21 pushed a commit to branch jdbc
in repository https://gitbox.apache.org/repos/asf/geode-examples.git
commit 0b847efbcd7ec3d34cd6860367125fd09013b8b4
Author: Jianxia Chen <jc...@apache.org>
AuthorDate: Fri May 3 15:02:27 2019 -0700
Add JDBC Connector example.
Authored-by: Jianxia Chen <jc...@apache.org>
---
jdbc/README.md | 72 +++++++++++++++++++++
jdbc/scripts/start.gfsh | 34 ++++++++++
jdbc/scripts/stop.gfsh | 20 ++++++
.../org/apache/geode_examples/jdbc/Example.java | 75 ++++++++++++++++++++++
.../org/apache/geode_examples/jdbc/Parent.java | 58 +++++++++++++++++
settings.gradle | 1 +
6 files changed, 260 insertions(+)
diff --git a/jdbc/README.md b/jdbc/README.md
new file mode 100644
index 0000000..4a6f369
--- /dev/null
+++ b/jdbc/README.md
@@ -0,0 +1,72 @@
+<!--
+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.
+-->
+
+#Apache Geode JDBC Connector Example
+
+The JDBC Connector allows Apache Geode to connect to external data sources with JDBC.
+
+##Steps:
+
+1. Install MySQL: https://dev.mysql.com/downloads/
+
+If your MySQL installation does not include JDBC driver, download it from https://dev.mysql.com/downloads/connector/j/
+
+2. Start MySQL server with `mysql.server start`. Use `mysql` CLI to create database, table and populate the table:
+
+```
+create database geode_db;
+
+use geode_db;
+
+create table parent(id bigint, name varchar(100), income double);
+
+insert into parent values (2, 'Parent_2', 987654321.0);
+```
+
+3. Build this example's jar file `jdbc.jar` by running `../gradlew clean build`
+
+The jar file `jdbc.jar` will be generated in `build/libs` directory.
+
+4. Add MySQL JDBC driver jar and `jdbc.jar` to `CLASSPATH`
+
+e.g. `export CLASSPATH=/path/to/mysql-connector-java-8.0.15.jar:/path/to/geode-examples/jdbc/build/libs/jdbc.jar`
+
+5. Prepare the script `start.gfsh` for creating data source and mapping the Apache Geode region and MySQL table
+
+This can be done by uncommenting the `create data-source` and `create jdbc-mapping` commands in `start.gfsh` in `scripts` directory.
+
+6. Start the Geode cluster with `gfsh run --file=scripts/start.gfsh`
+
+This will start the locator and two servers. And create `Parent` region, data source and JDBC mapping.
+
+7. Run the example with `../gradlew run`
+
+This will first `put` an entry with key 1 in `Parent` region. The entry will be propagated to MySQL's `parent` table in database `geode_db`.
+Then it will invoke a `get` with key 2. Since `Parent` region does not have an entry with key equals 2, it will trigger JDBC Connector to
+load the entry from `parent` table in database `geode_db` from MySQL.
+
+You can also use `gfsh` to connect to cluster and run the following commands:
+`list data-source`
+`describe data-source`
+`list jdbc-mapping`
+`describe jdbc-mapping`
+`destroy jdbc-mapping`
+`destroy data-source`
+
+And use `mysql` to query the `parent` table.
+
+8. Shutdown the cluster with `gfsh run --file=scripts/stop.gfsh`
\ No newline at end of file
diff --git a/jdbc/scripts/start.gfsh b/jdbc/scripts/start.gfsh
new file mode 100644
index 0000000..5dab959
--- /dev/null
+++ b/jdbc/scripts/start.gfsh
@@ -0,0 +1,34 @@
+#
+# 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.
+#
+
+start locator --name=locator --bind-address=127.0.0.1 --include-system-classpath
+
+configure pdx --auto-serializable-classes=org.apache.geode_examples.jdbc.Parent
+
+start server --name=server1 --locators=127.0.0.1[10334] --server-port=0 --include-system-classpath
+
+start server --name=server2 --locators=127.0.0.1[10334] --server-port=0 --include-system-classpath
+
+create region --name=Parent --type=REPLICATE
+
+#create data-source --name=mysql_data_source --url="jdbc:mysql://localhost/geode_db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC" --username=root --password="changeme"
+
+#create jdbc-mapping --data-source=mysql_data_source --region=Parent --table=parent --pdx-name=org.apache.geode_examples.jdbc.Parent --catalog=geode_db --id=id
+
+list members
+
+describe region --name=Parent
\ No newline at end of file
diff --git a/jdbc/scripts/stop.gfsh b/jdbc/scripts/stop.gfsh
new file mode 100644
index 0000000..4ef6005
--- /dev/null
+++ b/jdbc/scripts/stop.gfsh
@@ -0,0 +1,20 @@
+#
+# 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.
+#
+
+connect --locator=127.0.0.1[10334]
+
+shutdown --include-locators=true
\ No newline at end of file
diff --git a/jdbc/src/main/java/org/apache/geode_examples/jdbc/Example.java b/jdbc/src/main/java/org/apache/geode_examples/jdbc/Example.java
new file mode 100644
index 0000000..a149dbb
--- /dev/null
+++ b/jdbc/src/main/java/org/apache/geode_examples/jdbc/Example.java
@@ -0,0 +1,75 @@
+/*
+ * 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.geode_examples.jdbc;
+
+import java.util.HashSet;
+import java.util.Set;
+
+import org.apache.geode.cache.Region;
+import org.apache.geode.cache.client.ClientCache;
+import org.apache.geode.cache.client.ClientCacheFactory;
+import org.apache.geode.cache.client.ClientRegionShortcut;
+import org.apache.geode.pdx.ReflectionBasedAutoSerializer;
+
+public class Example {
+ private final Region<Long, Parent> region;
+
+ public Example(Region<Long, Parent> region) {
+ this.region = region;
+ }
+
+ public static void main(String[] args) {
+ // connect to the locator using default port 10334
+ ClientCache cache =
+ new ClientCacheFactory().addPoolLocator("127.0.0.1", 10334)
+ .setPdxSerializer(
+ new ReflectionBasedAutoSerializer("org.apache.geode_examples.jdbc.Parent"))
+ .create();
+
+ // create a local region that connects to the server region
+ Region<Long, Parent> region =
+ cache.<Long, Parent>createClientRegionFactory(ClientRegionShortcut.PROXY).create("Parent");
+ System.out.println("Region=" + region.getFullPath());
+
+ Example example = new Example(region);
+
+ // Put entry in Parent region to verify it propagates to the external RDBMS table
+ System.out.println("Adding an entry into Parent region");
+ Long key = Long.valueOf(1);
+ region.put(key, new Parent(key, "Parent_1", Double.valueOf(123456789.0)));
+
+ // Get an entry from Parent region that will trigger the cache loader to
+ // retrieve the entry from the external table
+ System.out.println(
+ "Getting key=2, if JDBC Connector is configured, it will retrieve data from external data source");
+ key = Long.valueOf(2);
+ region.get(key);
+
+ // Print the current entries in the region
+ System.out.println("All entries currently in Parent region");
+ example.printValues(example.getKeys());
+
+ cache.close();
+ }
+
+ Set<Long> getKeys() {
+ return new HashSet<>(region.keySetOnServer());
+ }
+
+ void printValues(Set<Long> values) {
+ values.forEach(key -> System.out.println(String.format("%d:%s", key, region.get(key))));
+ }
+
+}
diff --git a/jdbc/src/main/java/org/apache/geode_examples/jdbc/Parent.java b/jdbc/src/main/java/org/apache/geode_examples/jdbc/Parent.java
new file mode 100644
index 0000000..46744e0
--- /dev/null
+++ b/jdbc/src/main/java/org/apache/geode_examples/jdbc/Parent.java
@@ -0,0 +1,58 @@
+/*
+ * 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.geode_examples.jdbc;
+
+public class Parent {
+ private Long id;
+ private String name;
+ private Double income;
+
+ public Parent() {}
+
+ public Parent(Long id, String name, Double income) {
+ this.id = id;
+ this.name = name;
+ this.income = income;
+ }
+
+ public Long getId() {
+ return this.id;
+ }
+
+ public String getName() {
+ return this.name;
+ }
+
+ public Double getIncome() {
+ return this.income;
+ }
+
+ public void setId(Long id) {
+ this.id = id;
+ }
+
+ public void setName(String name) {
+ this.name = name;
+ }
+
+ public void setIncome(Double income) {
+ this.income = income;
+ }
+
+ @Override
+ public String toString() {
+ return "Parent [Id=" + id + ", name=" + name + ", income=" + income + "]";
+ }
+}
diff --git a/settings.gradle b/settings.gradle
index 18a22a4..09d16a9 100644
--- a/settings.gradle
+++ b/settings.gradle
@@ -37,3 +37,4 @@ include 'expiration'
include 'indexes'
include 'transaction'
include 'wan'
+include 'jdbc'