JDBC Datasource Connection

JDBC Connection to a database

As mentioned earlier, there are two main ways to go in creating a connection to a database:
  1. You can download and register a JDBC database vendor driver and then use a method with the url, username and password arguments in a DriverManager class to create a connection to a database.
  2. Or you can get a connection via a DataSource, that is already registered as a resource in a Connection pool. Connection pools are pre-registered in a web server with the necessary arguments (vendor driver, url, user, password etc.) for a database connection.
    You get a DataSources by doing a JNDI lookup from a web server java context.

This is about using DataSource connection.

  • If you deploy an application to a web server, you should use a DataSource that you pre-register in the web server to obtain a connection to the database.
  • You must register a JDBC resource in the web server before you can access the resource through a JNDI (Java Naming and Directory Interface) lookup. How this is done depends on your Web server.
  • In some web servers, you have to create a JNDI name for a JDBC resource that maps to a Connection pool. Prior to that you must register a Connection pool resource and this should contain all the required parameters (user, password, url, driver etc.) for a connection to the database.
  • In other web servers, you simply register a JDBC resource that contains the necessary parameters (username, password, url, driver, etc.) for connection to a database and do a mapping of the resource to a JNDI name.

Example with a Tomcat web-server:

You can download this example here (needed tools can be found in the right menu on this page).

If you like to participate in the review of this example you must first create a Web project in Netbeans with the project name is TomcatDatasource, a Tomcat web server and context path to bee /TomcatDatasource.

  1. Define the JDBC Resource

    • Since you chose a Tomcat server when you created the project then a file context.xml, was created.
    • The context.xml file will contain the root path for the application and the file is placed in the META-INF path. This path is included in the deployment to the the server.
    • We must change the context.xml file to include a Datasource resource specification as shown below. This is done for a MySQL RDBMS database with the name CarDB.
    JDBC resource specification for a Tomcat server (context.xml file):
    <?xml version="1.0" encoding="UTF-8"?>
    <Context antiJARLocking="true" path="/TomcatDatasource">
      <Resource auth="Container" 
                driverClassName="com.mysql.jdbc.Driver" 
                maxActive="100" 
                maxIdle="30" 
                maxWait="10000" 
                name="jdbc/CarDB" 
                username="root"
                password="root" 
                type="javax.sql.DataSource" 
                url="jdbc:mysql://localhost:3306/CarDB" 
                />
    </Context>
    <!-- You may need to change the username and password for your MYSQL database -->
    • As for the Driver Manager connection type you also need here at least specify a JDBC vendor specific driver (driverClassName="com.mysql.jdbc.Driver"), a URL including a communications port (3306) and enter a database (CarDB) (url="jdbc:mysql://localhost:3306/CarDB" ), an user (username="root") and a password (password="root") (You may need to change the username and password for your MYSQL database).
    • You must also specify the JNDI name (name = "jdbc/CarDB") and resource type (type ="javax.sql.DataSource"). In this way, a Tomcat web-server knows how to handle the resource.
  2. Create Connection with DataSource class

    In a server application, you then need to do a lookup through the JNDI for the DataSource:
    InitialContext context = new InitialContext();
    DataSource dataSource =
               (DataSource) context.lookup("java:comp/env/jdbc/CarDB");
    Connection conn = dataSource.getConnection();
    1. Inside the web server deployed application, you will then create an initial Context Object.
    2. Use the lookup method on the InitialContext object to retrieve a DataSource object for a JNDI name ("java:comp/env/jdbc/CarDB").
    3. The prefix "java:comp/env" is used when you perform a lookup of a resource that exists in the web server where your application runs.
    4. The lookup method returns a Object, which you must cast to a DataSource and then you can use this DataSource to get a database Connection.
    Here is a servlet example that uses a JDBC DataSource Connection:
    import java.io.IOException;
    import java.io.PrintWriter;
    import java.sql.Connection;
    import java.sql.DatabaseMetaData;
    import java.sql.SQLException;
    import javax.naming.InitialContext;
    import javax.naming.NamingException;
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import javax.sql.DataSource;
    
    public class JDBCServer extends HttpServlet {
    
     protected void doGet(HttpServletRequest request,
                    HttpServletResponse response)
             throws ServletException, IOException {
      response.setContentType("text/html;charset=UTF-8");
      PrintWriter out = response.getWriter();
      try {
       InitialContext context = new InitialContext();
       DataSource dataSource =
                 (DataSource)context.lookup("java:comp/env/jdbc/CarDB");
       // User the DataSource to get a Connection to the database
       Connection conn = dataSource.getConnection();
       out.println("<html>");
       out.println("<head>");
       out.println("<title>Servlet Cardata</title>");
       DatabaseMetaData dbmd = conn.getMetaData();
       out.println("<p>");
       out.println("db name is " + dbmd.getDatabaseProductName());
       out.println("</p>");
       out.println("</body>");
       out.println("</html>");
      } catch (SQLException ex) {
       ex.printStackTrace();
      } catch (NamingException ex) {
       ex.printStackTrace();
      } finally {
       out.close();
      }
     }
    }

    For those who participate in the review: create a Servlet in Netbeans and replace generated code for the servlet with that shown above (the servlet name is JDBCServer in a package web).

    The next is to specify the JNDI name and the servlet in your Deployment descriptor file, web.xml.

    Deployment descriptor file, web.xml, should then look like:
    <?xml version="1.0" encoding="UTF-8"?>
    <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
    http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
        <servlet>
            <servlet-name>JDBCServerName</servlet-name>
            <servlet-class>web.JDBCServer</servlet-class>
        </servlet>
        <servlet-mapping>
            <servlet-name>JDBCServerName</servlet-name>
            <url-pattern>/JDBCServer</url-pattern>
        </servlet-mapping>
        <session-config>
            <session-timeout>
                30
            </session-timeout>
        </session-config>
        <resource-ref>
            <res-ref-name>jdbc/CarDB</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <res-auth>Container</res-auth>
            <res-sharing-scope>Shareable</res-sharing-scope>
        </resource-ref>
        <welcome-file-list>
            <welcome-file>JDBCServer</welcome-file>
        </welcome-file-list>
    </web-app>
    • This is a standard way to specify a JNDI resource for a Data Source database connection in a deployment descriptor.
    • It is also a standard way to specify a servlet with servlet-mapping in deployment descriptor.
    • The welcome-file must bee the url to the servlet (JDBCServer) related to the application context.
    • At last right click the project and select Properties in Netbeans. Select Libraries under Categories and press the Add Library... button to add the MySQL JDBC Driver.
    • If you have not created the CarDB database in MySQL RDBMS you can download an application, JDBCCreateDB, and run it to create the CarDB database.
    • You can now right click the project and select run and the browser will show the result to bee:

      db name is: MySQL

Example with a Glassfish application-server:

You can download this example here (needed tools can be found in the right menu on this page).

If you like to participate in the review of this example you must first create a Web project in Netbeans with the project name is DataSourceConnection, a Tomcat web server and context path to bee /DataSourceConnection.

  1. Define the JDBC Resource

    You can of course open the Glassfish Admin Console and create a JDBC Connection Pool with selected drivers and properies for a database connection and then create a JDBC Resource that use that connection, but I will show how to do it outside with a xml specification file.

    Open the File tab in Netbeans and create a XML file, javadb_mysql_datasource.xml, with the following content:

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE resources PUBLIC "-//Sun Microsystems Inc.//DTD Application Server 9.0 Domain//EN"
    "*<install directory>/lib/dtds/sun-resources_1_3.dtd*">
    <resources>
     <jdbc-connection-pool
            name="MysqlPool"
            datasource-classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource"
            res-type="javax.sql.DataSource">
      <property name="user" value="root"/>
      <property name="password" value="root"/>
      <property name="databaseName" value="CarDB"/>
      <property name="serverName" value="localhost"/>
      <property name="portNumber" value="3306"/>
      <property name="Url" value="jdbc:mysql://localhost:3306/CarDB"/>
     </jdbc-connection-pool>
     <jdbc-resource
            enabled="true"
            jndi-name="jdbc/CarDB"
            object-type="user"
            pool-name="MysqlPool"/>
    </resources> 

    This is a xml specification file for Glassfish server to create a JDBC connection pool, MysqlPool, and a JDBC resource with JNDI name jdbc/CarDB.

    • In the JDBC connectoin pool, with the name="MysqlPool" we must specify a JDBC vendor specific driver (datasource-classname = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource"), a URL including a communications port (3306) and a database (CarDB) (url="jdbc:mysql://localhost:3306/CarDB" ), an MySQL user (user="root") and a MySQL password (password="root") (You may need to change the username and password for your MYSQL database).
    • You must also specify the resource type (res-type = "javax.sql.DataSource"), database name (databaseName = "CarDB"), communications port to use (portNumber = "3306") and the server name (serverName ="localhost").
    • JDBC resource must specify the JNDI name (jndi-name = "jdbc/CarDB") and which JDBC connection pool to use (pool-name ="MysqlPool").

    We must use an ant-file to build this in the Glassfish server, so open the File tab in Netbeans and create a XML file, buildGlassFish.xml, with the following content:

    <?xml version="1.0" encoding="UTF-8"?>
    <project name="createResources" default="AddResources" basedir=".">
     <target name="AddResources" >
      <!-- Delete the JDBC Connection Pool if Exists from the Server
           and include removal of the JDBC Resource as well (cascade=true) -->
      <exec  executable="C:/oracle/glassfish-4.1/bin/asadmin.bat" spawn="true" >
       <arg line="delete-jdbc-connection-pool --cascade=true MysqlPool"/>
      </exec>
      <!-- Add the JDBC Connection Pool and jdbc Resource as specified
           in the file javadb_mysql_datasource.xml -->
      <exec  executable="C:/oracle/glassfish-4.1/bin/asadmin.bat" spawn="true" >
       <arg line="add-resources   javadb_mysql_datasource.xml "/>
      </exec>
     </target>
    </project>

    If you now run the target AddResources in this and-build file the JDBC Connection Pool and the jdbc Resource will be created in the Glassfish server.

  2. Create Connection with DataSource class

    In a server application, you then need to do a lookup through the JNDI for the DataSource:
    InitialContext context = new InitialContext();
    DataSource dataSource =
               (DataSource) context.lookup("java:comp/env/jdbc/CarDB");
    Connection conn = dataSource.getConnection();
    1. Inside the web server deployed application, you will then create an initial Context Object.
    2. Use the lookup method on the InitialContext object to retrieve a DataSource object for a JNDI name ("java:comp/env/jdbc/CarDB").
    3. The prefix "java:comp/env" is used when you perform a lookup of a resource that exists in the web server where your application runs.
    4. The lookup method returns a Object, which you must cast to a DataSource and then you can use this DataSource to get a database Connection.
    Here is a servlet example that uses a JDBC DataSource Connection:
    package serv;
    
    import java.io.IOException;
    import java.io.PrintWriter;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    import javax.naming.InitialContext;
    import javax.naming.NamingException;
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import javax.sql.DataSource;
    
    /**
     *
     * @author kaare
     */
    public class Cardata extends HttpServlet {
    
     protected void doGet(HttpServletRequest request, HttpServletResponse response)
             throws ServletException, IOException {
      response.setContentType("text/html;charset=UTF-8");
      PrintWriter out = response.getWriter();
      try {
    
       InitialContext context = new InitialContext();
       DataSource dataSource = (DataSource) context.lookup("java:comp/env/jdbc/CarDB");
       Connection conn = dataSource.getConnection();
       // Create a  Statement for a ResultSet that is
       // sensitive for other users updating of tables in the database and
       // the ResultSet is updatable for a selected table in the database.
       Statement stmt = conn.createStatement();
       // Create a  ResultSet with a query that returns
       // all the columns  from the dB table Carpark
       ResultSet resultSet = stmt.executeQuery("select * from Carpark ");
       // As cursor is at the before first row position
       // we use the next() method to
       // test and read the first row in the ResultSet.
       out.println("<html>");
       out.println("<head>");
       out.println("<title>Servlet Cardata</title>");
       if (resultSet.next()) {
        // Then we use a loop to retrieve rows and column data
        // and creates a html coded table output
        out.println("<table border='1' >");
        do {
         out.println("<tr>");
         out.print("<td>" + resultSet.getString("regNo") + "</td>");
         out.print("<td>" + resultSet.getString("cartype") + "</td>");
         out.println("<td>" + resultSet.getInt("model") + "</td>");
         out.println("<td>" + resultSet.getDouble("dayPrice") + "</td>");
         out.println("</tr>");
        } while (resultSet.next());
        out.println("</table>");
       }
       out.println("</body>");
       out.println("</html>");
      } catch (SQLException ex) {
       Logger.getLogger(Cardata.class.getName()).log(Level.SEVERE, null, ex);
      } catch (NamingException ex) {
       Logger.getLogger(Cardata.class.getName()).log(Level.SEVERE, null, ex);
      } finally {
       out.close();
      }
     }
    }

    For those who participate in the review: create a Servlet in Netbeans and replace generated code for the servlet with that shown above (the servlet name is Cardata in a package serv).

    The next is to specify the JNDI name and the servlet in your Deployment descriptor file, web.xml.

    Deployment descriptor file, web.xml, should then look like:
    <?xml version="1.0" encoding="UTF-8"?>
    <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
    http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
     <servlet>
      <servlet-name>Cardata</servlet-name>
      <servlet-class>serv.Cardata</servlet-class>
     </servlet>
     <servlet-mapping>
      <servlet-name>Cardata</servlet-name>
      <url-pattern>/Cardata</url-pattern>
     </servlet-mapping>
      <session-config>
      <session-timeout>
                30
      </session-timeout>
     </session-config>
     <welcome-file-list>
      <welcome-file>Cardata</welcome-file>
     </welcome-file-list>
     <resource-ref>
      <res-ref-name>jdbc/CarDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
      <res-sharing-scope>Shareable</res-sharing-scope>
     </resource-ref>
    </web-app>
    • This is a standard way to specify a JNDI resource for a Data Source database connection in a deployment descriptor.
    • It is also a standard way to specify a servlet with servlet-mapping in deployment descriptor.
    • The welcome-file must bee the url to the servlet (Cardata) related to the application context.

    The Glassfish server need to know about the resource reference database name used in the application and link that to the JNDI name defined in the Glassfish server.

    For this we must create a Glassfish Descriptor so right click the project and select a Glassfish Descriptor and let the file look like:

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE glassfish-web-app 
    PUBLIC "-//GlassFish.org//DTD GlassFish Application Server 3.1 Servlet 3.0//EN" 
    "http://glassfish.org/dtds/glassfish-web-app_3_0-1.dtd">
    <glassfish-web-app error-url="">
      <context-root>/DataSourceConnection</context-root>
      <resource-ref>
        <res-ref-name>jdbc/CarDB</res-ref-name>
        <jndi-name>jdbc/CarDB</jndi-name>
      </resource-ref>
      <class-loader delegate="true"/>
      <jsp-config>
        <property name="keepgenerated" value="true">
          <description>Keep a copy of the generated servlet class' java code.</description>
        </property>
      </jsp-config>
    </glassfish-web-app>

    The Glassfish server needs the MySQL JDBC driver so download this and copy the driver (mysql-connector-java-[version]-bin,jar) to [glassfishHome]glassfish/domains/domain1/lib directory. You must start/restart the Glassfish server after this.

    If you have not created the CarDB database in MySQL RDBMS you can download an application, JDBCCreateDB, and run it to create the CarDB database.

    If we run this application the result should be:
    As pure output Translated by a Browser
    <table border='1' >
    <tr>
    <td>AD23443</td><td>BMW</td><td>2001</td>
    <td>300.0</td>
    </tr>
    <tr>
    <td>AX72345</td><td>AUDI</td><td>2003</td>
    <td>350.0</td>
    </tr>
    <tr>
    <td>CE23473</td><td>AUDI</td><td>2001</td>
    <td>400.0</td>
    </tr>
    <tr>
    <td>DE12345</td><td>AUDI</td><td>2003</td>
    <td>250.0</td>
    </tr>
    <tr>
    <td>DE23453</td><td>FORD</td><td>2002</td>
    <td>375.0</td>
    </tr>
    <tr>
    <td>DE34562</td><td>OPEL</td><td>2001</td>
    <td>340.0</td>
    </tr>
    <tr>
    <td>RE23456</td><td>FORD</td><td>2005</td>
    <td>350.0</td>
    </tr>
    </table>
    AD23443BMW2001 300.0
    AX72345AUDI2003 350.0
    CE23473AUDI2001 400.0
    DE12345AUDI2003 250.0
    DE23453FORD2002 375.0
    DE34562OPEL2001 340.0
    RE23456FORD2005 350.0
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.