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 53d8e05b6a0b3aaed46045736194c6c9cceb062d Author: Jianxia Chen <[email protected]> AuthorDate: Fri May 3 15:02:27 2019 -0700 Add JDBC Connector example. Authored-by: Jianxia Chen <[email protected]> --- 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..313c1f3 --- /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'
