Connecting Java JDBC driver to CockroachDB using Maven in Eclipse IDE

Introduction

CockroachDB, a Structured Query Language (SQL) database, gives your applications the fastest response times whether they are small, medium, or large scale. When you create a Maven project using the Eclipse IDE UI and incorporate a Cockroach DB database, you harness the benefits of three applications. After that, when you take advantage of the capabilities of the Java JDBC driver to connect to a database, you create an environment for something extraordinary to happen. Let’s see. This tutorial explains the steps to connecting Java JDBC driver to CockroachDB using Maven in Eclipse IDE.

If you’re already familiar with the steps in this tutorial and want to bypass the instructional details, you can see a complete script example by going to Just the Code.

Prerequisites

Download and install the latest versions:

About Maven

Maven is a Java-based project tool you can use to build projects as well as manage them. It streamlines the project development process, enabling you to create and publish fast.

Use the Eclipse IDE UI and Make a Maven Project

Create a new project with the Maven wizard.

1) In the Eclipse platform, select “File”.

2) Next, select “New”.

3) Choose “Project”.

4) Type out “Maven” in the Wizards search bar when the New Project Window displays.

5) Pick “Maven Project”.

6) Press the “Next >” button.

Type maven in the wizard search bar to find Maven Project

7) Check the “Create a simple project (skip archetype selection)” option.

8) Check the “Use default Workspace location” option if you want to save the project there; otherwise, select “Browse” and choose the location.

Select create a simple project by ticking the checkbox

9) Type “com.objectrocket” (without quotes) in the Group Id field.

10) Type “maven-proj” (without quotes) in the Artifact Id field.

11) Press “Finish” to save the changes and create your new Maven project.

Creating the new Maven project by setting up the Artifact details

The role of the POM file in Maven

POM stands for Project Object Model. The pom.xml file stores vital information about a Maven project including configuration and default values. There’s a lot of programming leeway in the POM unit of information. For example, you can specify plugins, dependencies, project version, even mailing lists.

When a specific goal or task is executed, the POM file is read and pertinent information is gathered to follow through on the action to complete the task or goal.

The ZIP format Java ARchive (JAR) file is essential in Maven project development. JAR files help you compress, decompress, and archive files. You also need libraries when you construct projects. In Maven, libraries, JAR files, all of them collectively are called dependencies because you add them to the pom.xml file to create your Maven project.

Use POM. xml for Java JDBC dependency importation

Here are the simple steps to importing Java JDBC dependencies using POM.xml.

1) Find the pom.xml file in the “maven-proj” project.

2) Double-click the pom.xml to open it.

3) Next, click the tab for pom.xml.

Select the "pom.xm" tab to see the xml format of the pom.xml

>NOTE: At first, you’ll see the pom.xml file showing the details of the maven project you just created.

4) Copy the Java JDBC driver code to add it to the pom.xml file.

Copy the xml snippet to be included as Maven dependency

5) Paste the code into the pom.xml file under “< dependencies >”.

Editing the pom.xml file by typing in dependencies and pasting the snippet from the maven repositiory

6) Press “File”.

7) Choose “Save”. Alternatively, press the shortcut Ctrl + S to update the pom.xml file.

8) Allow the JAR files to finish downloading.

Maven downloaded the needed JAR Files as indicated in the pom.xml file

Construct a database sample to test

1) Use the sample code below to make and userdatabase sample database and raizel user access.

-- create user "yeshua"
CREATE USER IF NOT EXISTS yeshua;
-- create database "userdatabase"
CREATE DATABASE userdatabase;

2) Give permissions to access the database. The SELECT DATABASE is userdatabase.

3) The grantee user that will be given permissions to read and write code in this sample is “yeshua”.

GRANT ALL ON DATABASE userdatabase TO yeshua;

4) Type “user q”, “q”, “EXIT”, or “QUIT” to exit the SQL shell.

5) Create a table using the following sample code.

CREATE TABLE tblusers(
id INT PRIMARY KEY,
name STRING,
age INT,
accessLevel STRING,
department STRING,
title STRING
);

6) Add table user records identifying the users.

INSERT INTO tblusers (id, name, age, accessLevel, department, title) VALUES
(1, 'mark', 22, 'user', 'accounting', 'assistant'),
(2, 'gene', 24, 'user', 'research', 'rankfile'),
(3, 'don', 25, 'admin', 'ict', 'supervisor'),
(4, 'isaac', 20, 'user', 'marketing', 'assistant');

Use the Java JDBC to connect a Cockroach DB database

  • The steps below describe how to load the Java JDBC driver and connect it to the database in CockroachDB.
// Connect to the "userdatabase" database.
Properties prp = new Properties();
prp.setProperty("user", "yeshua");
prp.setProperty("sslmode", "disable");

Connection db = DriverManager
.getConnection("jdbc:postgresql://127.0.0.1:26257/userdatabase", prp);



// This prints out the data retrieved from the database
System.out.println("Current Users:");
ResultSet result = db.createStatement()
.executeQuery("SELECT id,age,name,accessLevel,department,title FROM tblusers");
while (result.next()) {
System.out.printf("tuser %s: %s, %s, %s, %s, %sn",
result.getInt("id"),
result.getString("name"),
result.getInt("age"),
result.getString("accessLevel"),
result.getString("department"),
result.getString("title"));
}
  • A successful result looks similar to this one:
Current Users:
user 1: mark, 22, user, accounting, assistant
user 2: gene, 24, user, research, rankfile
user 3: don, 25, admin, ict, supervisor
user 4: isaac, 20, user, marketing, assistant

Conclusion

This tutorial showed you the simplified way of connecting Java JDBC driver in CockroachDB via Maven in Eclipse IDE. The lesson explained the role that Maven’s pom.xml file plays in saving dependency information to execute goals and tasks. It also revealed how to create a new Maven project, design a table, and add data.

You learned the why before the how so that finally, for a clearer understanding, you were shown the step-by-step process on how to connect the Java JDBC driver to a CockroachDB database. Try out these steps today and incorporate them into all your future development projects.

Just the Code

Here’s a sample code for importing the Java JDBC driver and connecting to a database in CockroachDB.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class SampleClass {

public static void main(String[] args) throws ClassNotFoundException, SQLException{



// Connect to the "userdatabase" database.
Properties prp = new Properties();
prp.setProperty("user", "yeshua");
prp.setProperty("sslmode", "disable");

Connection db = DriverManager
.getConnection("jdbc:postgresql://127.0.0.1:26257/userdatabase", prp);


System.out.println("Current Users:");
ResultSet result = db.createStatement()
.executeQuery("SELECT id,age,name,accessLevel,department,title FROM tblusers");
while (result.next()) {
System.out.printf("tuser %s: %s, %s, %s, %s, %sn",
result.getInt("id"),
result.getString("name"),
result.getInt("age"),
result.getString("accessLevel"),
result.getString("department"),
result.getString("title"));
}


}

}

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.