Connecting to MongoDB using JDBC

The JDBC API defines a set of interfaces and classes that all major database providers adhere to in order allow Java developers to seamlessly connect to many Relational Database Management Systems (RDBMS).   All major vendors provide their own JDBC drivers which contain a set of java classes that enables you to connect to that particular database.

What JDBC does is standardize how to do many of the operations like: connect to the database, how to query the database, how to update the database, and how to call stored procedures.

Installation and Preparation of Database Drivers

Before you begin you will need to download and install the appropriate JDBC drivers from the vendor of choice for your database. Below you will find the links to each of the most popular databases available.

Oracle 11g Database Drivers

• Download 11g drivers here

Sybase Database Drivers

• Download Sybase JConnect drivers here

Microsoft SQL Server Database Drivers

• Download Microsoft JDBC drivers here

DB2 Database Drivers

• Download DB2 Universal JDBC drivers here

MySQL Database Drivers

• Download MySQL Connector/J drivers here

PostgreSQL Database Drivers

• Download PostgreSQL JDBC drivers here

MongoDB Database Drivers

• Download MongoDB JDBC drivers from Unity here

Common Database URL Formats

JDBC Basics

In order to use your database and connect via JDBC you will need to do the following five steps:

• Add the JDBC Driver jar files to build path
• Import java.sql.* packages
• Register the Database Driver
• Create the Database Connections
• Close Connections

1. Add the JDBC Driver JAR files in Eclipse

In your development environment, in my case, Eclipse, you will need to ensure that the JDBC Driver is added to the build path. For my example, I am using the MongoDB JDBC drivers. In order to add the new jar file into your project build path, click on the package name in Package Explorer and hit Alt + Enter, then click on Java Build Path. Next click on the button that says “Add External JARs…” and locate your newly downloaded JDBC Driver jar files. In my case, since I am using MongoDB, I will choose unityjdbc.jar in addition to the mongo-java-driver-2.12.2.jar.

2. Import Java.SQL Package

Now that we have added the appropriate JDBC driver to our Java build path, you will need to import the java.sql.* classes. The import statements declares Java class(es) to use in the code below the import statement. Once a Java class is declared, then the class name can be used in the code without specifying the package the class belongs to.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

or use one import statement;

import java.sql.*;

3. Register the Database Driver

Before you can begin using JDBC with your database provider, you must register the driver. You do this using the Class.forName method.

try {
  Class.forName("mongodb.jdbc.MongoDriver");
} catch (ClassNotFoundException e) {
  System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
  e.printStackTrace();
  return;
}

4. Create the database connection

Once we have registered our JDBC driver, we need to establish a connection to the database. This is done using the DriverManager.getConnection method.

try {
       connection = DriverManager.getConnection(database_url, username, password);
    } catch (SQLException e) {
      System.out.println("ERROR:  Unable to establish a connection with the database!");
      e.printStackTrace();
      return;
    }

5. Create the JDBC statement

Once we have a connection we can begin to interact with the database. The JDBC createStatement(), prepareCall(), and prepareStatement() methods enable you to send your SQL commands and receive data from our database.

try{
  statement = connection.createStatement();
  result = statement.executeQuery("select employee_id, first_name, last_name from employee");

  while (result.next()) {
    String employee_id = result.getString("employee_id");
    String first_name  = result.getString("first_name");
    String last_name  = result.getString("last_name");
    System.out.printf("Employee ID: [%s], %s %s n", employee_id, first_name, last_name);
  }
} catch (SQLException e) {
   System.out.println(e.getMessage());
}

6. Iterate through the ResultSet

A ResultSet contains data records being returned from a previously executed database query. Looking at the Javadocs, a ResultSet maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The ‘next’ method moves the cursor to the next row.

As you can see below, we are looping through all of the records in the ResultSet using the while loop. We are stepping through each record in the ResultSet using result.next().

try{
  statement = connection.createStatement();
  result = statement.executeQuery("select employee_id, first_name, last_name from employee");

  while (result.next()) {
    String employee_id = result.getString("employee_id");
    String first_name  = result.getString("first_name");
    String last_name  = result.getString("last_name");
    System.out.printf("Employee ID: [%s], %s %s n", employee_id, first_name, last_name);
  }
} catch (SQLException e) {
   System.out.println(e.getMessage());
}

7. Closing the Connection

As a rule, you must ensure you close database connections and other resources at the end of your program or when they are no longer necessary. Forgetting to do so is generally considered to be poor programming practice and can lead to hard to track problems like memory leaks.

} finally {
  if (connection != null) connection.close();
}

Sample JDBC Program

package com.avaldes.tutorials;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MongoJDBCExample {

public static void main(String[] args) throws SQLException {
String database_url = "jdbc:mongo://localhost:27017/tutorial";
String username = "webuser";
String password = "webuser123";
Connection connection = null;
Statement statement = null;
ResultSet result = null;

try {
Class.forName("mongodb.jdbc.MongoDriver");
} catch (ClassNotFoundException e) {
System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
e.printStackTrace();
return;
}
System.out.println("MongoDB JDBC Driver has been registered...");

System.out.println("Trying to get a connection to the database...");
try {
connection = DriverManager.getConnection(database_url, username, password);
} catch (SQLException e) {
System.out.println("ERROR: Unable to establish a connection with the database!");
e.printStackTrace();
return;
}

if (connection != null) {
DatabaseMetaData metadata = connection.getMetaData();
System.out.println("Connection to the database has been established...");
System.out.println("JDBC Driver Name : " + metadata.getDriverName());
System.out.println("JDBC Driver Version : " + metadata.getDriverVersion());
} else {
System.out.println("ERROR: Unable to make a database connection!");
}

System.out.println("Trying to get a list of all employees in employee collection...");
try {
statement = connection.createStatement();

String sql = "select employee_id, first_name, last_name from employee";
result = statement.executeQuery(sql);

while (result.next()) {
int employee_id = result.getInt("employee_id");
String first_name = result.getString("first_name");
String last_name = result.getString("last_name");

System.out.printf("EMPLOYEE_ID: [%d], %s %s n", employee_id, first_name, last_name);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
System.out.println("Closing all open resources...");
if (result != null) result.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
}
}
}

Output

That’s It!

I hope you enjoyed this tutorial. It was certainly a lot of fun putting it together and testing it out. Please continue to share the love and like us so that we can continue bringing you quality tutorials. Happy Coding!!!

Related Posts

• MongoDB Tutorials – Installation, Basics, Core, JAX-RS and Spring Data Examples
  This is an index post containing a consolidated list of all of the Mongo related tutorials as well as those using other Frameworks like Spring MVC/Spring Data, and JAX-RS.
• MongoDB Setup – Installation, Configure and Set up Windows Service
  This MongoDB Setup -tutorial will guide you through installation, configuration setup and show you how to configure MongoDB as a Windows Service.
• MongoDB Shell Basics – Insert, Update, Find, Delete and Indexing
  In this, MongoDB Shell Basics tutorial we will take you through the basics of using the mongo shell to create, update, delete and find documents in collections. In addition we will show you howto create indexes in collections.
• MongoDB Basics – Finding Distinct Values, Using Sort and Finding the Number of Documents in a Collection
  In this tutorial we learn about how to use Distinct to find unique fields within a document. In addition, we learn about using Sort and Count methods in MongoDB.
• MongoDB Basics – Aggregation and Group Examples Tutorial
  In this tutorial we learn about how to use aggregation operations to process data matching a certain criteria and perform some operation to return computed results.
• Connecting to MongoDB using JDBC
  The JDBC API defines a set of interfaces and classes that all major database providers adhere to in order allow Java developers to seamlessly connect to many Relational Database Management Systems (RDBMS). All major vendors provide their own JDBC drivers which contain a set of java classes that enables you to connect to that particular database.
• Java Connecting to MongoDB 3.2 Examples
  In this tutorial, Java Connecting to MongoDB 3.2 Examples we will show you different ways to connect to the latest version of MongoDB using Java and their mongo java driver (mongo-java-driver-3.2.0.jar).
• MongoDB Java CRUD Operations Example Tutorial
  In this tutorial we will focus on using CRUD Operations (Create, Read, Update and Delete) with the latest version of MongoDB using Java and MongoDB Java Driver (mongo-java-driver-3.2.0.jar). We will focus our efforts on insertOne, updateOne, replaceOne, findOneAndUpdate and findOneAndDelete.
• MongoDB Java using Find and Query Operations Example Tutorial
  In this tutorial we will focus on using Find and Query Operations to retrieve data from a MongoDB collection. We will concentrate on using the following MongoDB operators: ($gt, $lt, $gte, $lte, $in, $nin, $and and $or) with the latest version of MongoDB using Java and MongoDB Java Driver (mongo-java-driver-3.2.0.jar).

Related Posts

• Connecting to SQL Server using JDBC
• Connecting to Oracle using JDBC
• Connecting to MySQL Server using JDBC
• Connecting to MongoDB Server using JDBC

Please Share Us on Social Media