DriverManager vs. DataSource

At a high level, there are two ways to connect to a SQL database in Java.

• DriverManager
• DataSource

When using DriverManager, you place the database driver JAR (such as mysql.jar) on the system running Eclipse and then you configure Eclipse to use the JAR. When using DataSource, you place the database driver JAR on the system running the application server (JBoss, Tomcat, WebSphere). 

DataSource has numerous advantages over DriverManager. With DataSource you only need to place the JAR on one system (the application server). Another advantage is that you can leverage additional capabilities, such as connection pooling. This article describes how to connect to a SQL database via DataSource. Refer to this article to connect to a database via DriverManager.

JDBC / JNDI

First, you will need to configure your application server with a JNDI such as "jdbc/mySQL" or "jdbc/Oracle" or "jdbc/DB2" to handle JDBC.

• IBM Liberty
• IBM WebSphere (create JDBC Provider, create Data Source)

In this example, WebSphere has been setup with a JNDI of jdbc/mySQL to handle the JDBC.

J2C alias

You will need to decide if you want to bind the WebSphere data source to a J2C alias or if you want to code your app(s) to get the J2C alias from WebSphere.

JSP

You can now write the code needed to make a connection to the database. Let's say you configured your application server with a JNDI of jdbc/mySQL. Add the following to WEB-INF/web.xml.

<resource-ref>
    <res-ref-name>jdbc/mySQL</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

Here is an example of a JSP page that makes a connection to the database associated with jdbc/mySQL.

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
     
<sql:query var="myvar" dataSource="jdbc/mySQL">
    select * from foo;
</sql:query>

Servlet

Connecting to the database via a servlet takes a bit more markup.

package com.main;

import java.io.*;
import java.sql.*;
import javax.annotation.Resource;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.WebServlet;
import javax.sql.DataSource;

@WebServlet("/foo")
public class foo extends HttpServlet {
  
  @Resource(name = "jdbc/mySQL")
  private DataSource ds1;
  private Connection conn = null;
  private static final long serialVersionUID = 1L;
  
  public foo() {
    super();
  }
  public void doGet(HttpServletRequest request, HttpServletResponse response) 
                                       throws ServletException, IOException {
    PrintWriter out = response.getWriter();
    Statement stmt = conn.createStatement();
    try {
      conn = ds1.getConnection();
      stmt.executeUpdate( "INSERT INTO employee VALUES (?,?,?)" );
    } 
    catch (SQLException e) { e.printStackTrace(); } 
    finally {
      if (conn == null) {
        out.println("Database connection failed");
      } 
      else {
        out.println("Database connection success");
        stmt.close();
        conn.close();
      }
    }
  }
}