Connecting to SQL Server using JDBC

Java_jdbcThe 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 SQLServer 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 SQLServer, I will choose sqljdbc4.jar.

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 for JDK-compliant JVMs and DriverManager.registerDriver for Microsoft JVMs.

Approach #1


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

Approach #2


try {
  DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
} 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 dbo.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 dbo.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.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MyJDBCExample {

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

    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException e) {
      System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
      e.printStackTrace();
      return;
    }
    System.out.println("SQLServer 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) {
      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 table...");
    try {
        statement = connection.createStatement();

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

          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());
    } finally {
      System.out.println("Closing all open resources...");
        if (result != null) result.close();
        if (statement != null) statement.close();
        if (connection != null) connection.close();
    }
  }
}

Output

SQLServer JDBC Driver has been registered…
Trying to get a connection to the database…
Connection to the database has been established…
JDBC Driver Name : Microsoft JDBC Driver 4.0 for SQL Server
JDBC Driver Version : 4.0.2206.100
Trying to get a list of all employees in employee table…
Employee ID: [1], John 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
Employee ID: [100], Babe Ruth
Employee ID: [101], Derek Jeter
Closing all open resources…

Related Posts

Please Share Us on Social Media

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Leave a Reply

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