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

Sybase Database Drivers

Microsoft SQL Server Database Drivers

DB2 Database Drivers

MySQL Database Drivers

PostgreSQL Database Drivers

MongoDB Database Drivers

 

Common Database URL Formats

DatabaseJDBC Driver NameDatabase URL format
SQLServercom.microsoft.sqlserver.jdbc.SQLServerDriverjdbc:sqlserver://{hostname}:{port};
databaseName={database_name}
Sybasecom.sybase.jdbc2.jdbc.SybDriverdbc:jtds:sybase://{hostname}:{port}/
{database_name}
MySQLcom.mysql.jdbc.Driverjdbc:mysql://{hostname}:{port}/
{database_name}
Oracleoracle.jdbc.driver.OracleDriverjdbc:oracle:thin:@{hostname}:{port}/{database_name}
DB2com.ibm.db2.jcc.DB2Driverjdbc:db2://{hostname}:{port}/
{database_name}
PostreSQLorg.postgresql.Driverjdbc:postgresql://{hostname}:{port}/
{database_name}
MongoDBmongodb.jdbc.MongoDriverjdbc:mongo://{hostname}:{port}/
{database_name}

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.

mongo jdbc properties

 

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;
}

Please Note

As of Java 6, the process of registering JDBC Drivers is no longer necessary and may be omitted completely. However, doing so will not harm anything and will ensure backwards compatibility with older JDKs.

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

License Issued date: Sat Nov 22 11:03:37 EST 2014
Trial End date: Tue Dec 23 03:00:00 EST 2014
MongoDB JDBC Driver has been registered…
Trying to get a connection to the database…
Connection to the database has been established…
JDBC Driver Name : Mongo JDBC
JDBC Driver Version : 1.0
Trying to get a list of all employees in employee collection…
EMPLOYEE_ID: [1], Alex Smith
EMPLOYEE_ID: [2], David Harvey
EMPLOYEE_ID: [3], Lisa Bank
EMPLOYEE_ID: [4], James Young
EMPLOYEE_ID: [5], Danielle Gray
EMPLOYEE_ID: [6], Jeff Wang
EMPLOYEE_ID: [7], Rishi Patel
EMPLOYEE_ID: [8], Karen Ly
EMPLOYEE_ID: [9], Chris Canning
Closing all open resources…

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!!!

mongodb_basics_tutorial

Related Posts

Related Posts

Please Share Us on Social Media

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *