Akmal Chaudhri for SingleStore

Posted on Nov 4, 2022 • Updated on Nov 1

Configure the SQuirreL SQL Client to use the SingleStore JDBC Driver

#singlestoredb #squirrelsql #jdbc

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.

Figure 1. Home Screen.

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.

Figure 2. Add Driver.

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.

Figure 3. Add Alias.

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.

Figure 4. Test Connection.

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.

Figure 5. Objects tab.

Run SQL queries

We can run SQL queries from the SQL tab, as shown in Figure 6.

Figure 6. SQL tab.

Summary

This article has shown how to configure and use the SingleStore JDBC Driver with a popular SQL client.

DEV Community