Places App with MariaDB Connector/J
This page is part of MariaDB's Documentation.
The parent of this page is: Sample Code
Topics on this page:
Overview
Places is a sample web application to manage location information. The application is compatible with MariaDB Connector/J and Single Node Transactions and Replicated Transactions topologies on MariaDB SkySQL.
Places demonstrates the power and versatility of MariaDB Connectors and Transactions services by managing arbitrary location data in JSON through a Google Maps interface.
Places has three components:
React.js and Google Maps front-end web application
Java-based back-end application service
Single Node Transactions back-end database service, running on MariaDB SkySQL
Prerequisites
Clone the Repository
Places is available from a MariaDB Corporation repository on GitHub. The repository contains the SQL code to initialize the database, the back-end application service code in Java and other languages, and the React.js front-end web application that connects to the back-end.
To download the Places code, clone the repository from GitHub:
$ git clone https://github.com/mariadb-corporation/dev-example-places.git
This creates a dev-example-places
directory on your file system.
Provision Database Services
Places requires a back-end database service using the InnoDB storage engine. The back-end app is compatible with Single Node Transactions and Replicated Transactions topologies. For the purposes of demonstration, this guide uses the Single Node Transactions topology on MariaDB SkySQL.
For additional information, see "Launch".
Create the Database Schema
Places uses the Google Maps API to provide map information and a Single Node Transactions service to store data on locations of interest to the user. You need to create a database and table to store this data for the application.
To create the schema for the Places application:
Create a
places
database:CREATE DATABASE places;
Create a
Locations
table:CREATE TABLE places.locations ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL DEFAULT '', description VARCHAR(500) DEFAULT '', type CHAR(1) NOT NULL DEFAULT '', latitude DECIMAL(9,6) NOT NULL, longitude DECIMAL(9,6) NOT NULL, attr LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL CHECK(JSON_VALID(attr))) ENGINE = InnoDB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8mb4;
Set Google Maps API Key
Places requires an active Google Maps API key. For information on obtaining this key, see Google Cloud.
Install Back-end Application Service
Places front-end web application connects to the database service using a back-end application service. The repository contains different versions of the back-end app, allowing you to test and experiment with different languages and different MariaDB Connectors. These instructions are for Java using MariaDB Connector/J.
Navigate to the correct api
directory:
$ cd /path/to/dev-example-places/api/jdbc/
Configuring the Back-end Application Service
To connect the back-end app to your MariaDB SkySQL database service, configure the back-end app to use the appropriate credentials:
Create properties file at
src/main/resources/application.properties
and populate it with your MariaDB SkySQL credentials:spring.datasource.url=jdbc:mariadb://example.skysql.net:5001/places?autocommit=false&useSsl=true&serverSslCert=classpath:static/skysql_chain.pem spring.datasource.username=db_user spring.datasource.password=db_passwd spring.datasource.driver-class-name=org.mariadb.jdbc.Driver spring.datasource.hikari.connectionTimeout=30000 spring.datasource.hikari.idleTimeout=600000 spring.datasource.hikari.maxLifetime=1800000 spring.datasource.hikari.autoCommit=false spring.datasource.hikari.maximumPoolSize=10 #logging.level.org.mariadb.jdbc=TRACE
Create a directory for static files:
$ mkdir src/main/resources/static
Copy your SkySQL CA file into the
static
directory:$ cp /path/to/skysql_chain.pem src/main/resources/static
Build the Back-end Application Service
To build the Java back-end application service:
$ mvn package
Start the Back-end Application Service
Once you have the back-end API configured and installed, you can start the service:
$ mvn spring-boot:run
The back-end application service is now running. Install and start the React.js web application to use Places.
Install Front-end
Places uses a React.js front-end web application. The front-end connects to the back-end applications service and provides a web interface for the user.
To install and start the web app:
Navigate to the
client
directory:$ cd /path/to/dev-example-places/client/
Edit the
.env
file to add your Google API Key:REACT_APP_GOOGLE_API_KEY=your_api_key
Install the client application:
$ npm install
Start the client:
$ npm start
The React.js web application is now running. The app is accessible through your web browser at http://localhost:3000.
Live Demonstration
Places is a sample web application for MariaDB SkySQL that demonstrates a Single Node Transactions service working with JSON data to record locations in Google Maps.
The web interface opens onto a Google Map. It provides a Map view and a Satellite view. You can zoom in, zoom out, and place the figure to get a Street view. Clicking the "Add Location" button opens a form to add a location to the database.
Places allows you to create an Attraction, Restaurant, or Sports Venue. You can then specify a name, description, position in latitude and longitude, and category. When you click "Save" the application writes the location to the database:
+----+---------------------+-------------+------+------------+--------------+-------------------------+
| id | name | description | type | latitude | longitude | attr |
+----+---------------------+-------------+------+------------+--------------+-------------------------+
| 19 | Washington Monument | NULL | A | 38.8895563 | -77.0352546 | {"category":"Landmark"} |
+----+---------------------+-------------+------+------------+--------------+-------------------------+
Locations can contain arbitrary information in addition to the general fields in the table. These are stored on the attr
column using JSON data.
Attraction:
{ "category":"Landmark", }
Restaurant:
{ "details":{ "foodType":"Pizza", "menu":"www.giodanos.com/menu" }, "favorites":[ { "description":"Classic Chicago", "price":24.99 }, { "description":"Salad", "price":9.99 } ] }
Sports Venue:
{ "details":{ "yearOpened":1994, "capacity":23500 }, "events":[ { "date":"10/18/2019", "description":"Bulls vs Celtics" }, { "date":"10/21/2019", "description":"Bulls vs Lakers" }, { "date":"11/5/2019", "description":"Bulls vs Bucks" }, { "date":"11/5/2019", "description":"Blackhawks vs Blues" } ] }