FreeKB - Connect to a SQL database in Java using Eclipse via DataSource
Connect to a SQL database in Java using Eclipse via DataSource

Home > Search


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.

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

 

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

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter 01cc0 in the box below so that we can be sure you are a human.




Comments