Abstract
SingleStore has developed a JDBC Driver. This article will show how to configure and use this JDBC driver from SQuirreL SQL, a popular database client.
Introduction
This article used a Virtual Machine running Ubuntu 22.04.2 with a Java 11 JDK, previously installed as follows:
sudo apt install openjdk-11-jdk
Create a SingleStoreDB Cloud account
A previous article showed the steps required to create a free SingleStoreDB Cloud account. We'll use SQuirreL Demo Group as our Workspace Group Name and squirrel-demo as our Workspace Name. We'll make a note of our password and host name.
Create the database and table
We'll use a subset of the inventory system example from a previous article, as it provides a combination of both Relational and JSON data. In the SQL Editor in SingleStoreDB Cloud, we'll create a database and a table:
CREATE DATABASE IF NOT EXISTS e_store;
USE e_store;
DROP TABLE IF EXISTS products;
CREATE TABLE IF NOT EXISTS products (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(250) NOT NULL,
brand_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED NOT NULL,
attributes JSON NOT NULL,
PRIMARY KEY(id),
INDEX CATEGORY_ID(category_id ASC),
INDEX BRAND_ID(brand_id ASC)
);
Populate the database table
Let's now populate the products table:
-- Televisions
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Prime', '1', '1', '{"screen" : "50 inch", "resolution" : "2048 x 1152 pixels", "ports" : {"hdmi" : 1, "usb" : 3}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Octoview', '1', '1', '{"screen" : "40 inch", "resolution" : "1920 x 1080 pixels", "ports" : {"hdmi" : 1, "usb" : 2}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Dreamer', '1', '1', '{"screen" : "30 inch", "resolution" : "1600 x 900 pixels", "ports" : {"hdmi" : 1, "usb" : 1}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Bravia', '1', '1', '{"screen" : "25 inch", "resolution" : "1366 x 768 pixels", "ports" : {"hdmi" : 1, "usb" : 0}, "speakers" : {"left" : "5 watt", "right" : "5 watt"}}'),
('Proton', '1', '1', '{"screen" : "20 inch", "resolution" : "1280 x 720 pixels", "ports" : {"hdmi" : 0, "usb" : 0}, "speakers" : {"left" : "5 watt", "right" : "5 watt"}}');
-- Mobile Phones
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Desire', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["GSM", "CDMA", "HSPA"]', 'EVDO'),
"body",
"5.11 x 2.59 x 0.46 inches",
"weight",
"143 grams",
"sim",
"Micro-SIM",
"display",
"4.5 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android Jellybean v4.3"
)
),
('Passion', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["GSM", "CDMA"]', 'HSPA'),
"body",
"6.11 x 3.59 x 0.46 inches",
"weight",
"145 grams",
"sim",
"Micro-SIM",
"display",
"4.5 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android Jellybean v4.3"
)
),
('Emotion', '2', '2', JSON_BUILD_OBJECT("network" ,
JSON_ARRAY_PUSH_STRING('["GSM", "CDMA"]', 'EVDO'),
"body",
"5.50 x 2.50 x 0.50 inches",
"weight",
"125 grams",
"sim",
"Micro-SIM",
"display",
"5.00 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android KitKat v4.3"
)
),
('Sensation', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["GSM", "HSPA"]', 'EVDO'),
"body",
"4.00 x 2.00 x 0.75 inches",
"weight",
"150 grams",
"sim",
"Micro-SIM",
"display",
"3.5 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android Lollipop v4.3"
)
),
('Joy', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["CDMA", "HSPA"]', 'EVDO'),
"body",
"7.00 x 3.50 x 0.25 inches",
"weight",
"250 grams",
"sim",
"Micro-SIM",
"display",
"6.5 inches",
"resolution",
"1920 x 1080 pixels",
"os",
"Android Marshmallow v4.3"
)
);
-- Cameras
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Explorer', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV III", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}'),
('Runner', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV II", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LED"}'),
('Traveler', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV II", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}'),
('Walker', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV I", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LED"}'),
('Jumper', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV I", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}');
Install SQuirreL SQL
At the time of writing this article, the latest version of SQuirreL SQL is 4.8. We'll download this from SourceForge.
Next, we'll install the software, as follows:
java -jar squirrel-sql-4.8.0-standard.jar
We'll take all the defaults from the installation wizard, including the squirrel-sql-4.8.0
target directory, in our home folder.
Download SingleStore JDBC Driver
We'll download the latest version of the SingleStore JDBC Driver that, at the time of writing this article, was singlestore-jdbc-client-1.2.4.jar
.
We'll move the jar file to the directory squirrel-sql-4.8.0/lib
.
Launch SQuirreL SQL
In a terminal window from the directory squirrel-sql-4.8.0
we'll run the following command:
./squirrel-sql.sh
This will launch SQuirreL SQL, as shown in Figure 1.
Add Driver
From the left-hand navigation pane, we'll select Drivers.
Next, we'll select the ✚ sign to Create a New Driver. We'll enter the following details:
- Name: SingleStoreDB
- Example URL: jdbc:singlestore://<host>:3306/<database>
- Java Class Path: singlestore-jdbc-client-1.2.4.jar
- Class Name: com.singlestore.jdbc.Driver
This is shown in Figure 2.
Next, we'll click OK.
Add Alias
From the left-hand navigation pane, we'll select Aliases.
Next, we'll select the ✚ sign to Create a New Alias. We'll enter the following details:
- Name: e_store
- Driver: SingleStoreDB
- URL: jdbc:singlestore://<host>:3306/e_store
- User Name: admin
- Password: <password>
This is shown in Figure 3.
We'll replace the <host>
and <password>
with the values from our SingleStoreDB Cloud account.
We'll click Test to test the connection. It should be successful, as shown in Figure 4.
We'll click OK and then click Connect followed by Connect again.
Explore the database
From the Objects tab, we can expand e_store > TABLE > products, and explore the data, as shown in Figure 5.
Run SQL queries
We can run SQL queries from the SQL tab, as shown in Figure 6.
Summary
This article has shown how to configure and use the SingleStore JDBC Driver with a popular SQL client.
Top comments (0)