Java Tips and Tricks: February 2013

Every web programmer thinks about the database connectivity before starting about application. Good database connectivity can increase the performance of the web application. We are explaining about different types of java database connectivity to choose among the best. We have number of reason to choose different way of doing connectivity with database.

1. Simple JSP database connectivity

2. JavaBean database connectivity

3. web.xml and Servlet database connectivity

4. Properties file database connectivity

5. XML file database connectivity

6. Connection pooling java database connectivity

1. Simple JSP database connectivity
This is first most commonly used database connectivity by entry level java applications. We have to write our code in JSP file and open connection in same file. Advance JSP database connection can include a connection JSP file in all related JSP file where database connection is required.

<%@ page language="java" import="java.sql.*"%>
<%
    Connection conn = null;
    
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    
    String sURL="jdbc:mysql://localhost:3306/DBName";
    String sUserName="UserName";
    String sPwd="Password";
    
    conn = DriverManager.getConnection(sURL,sUserName,sPwd);
    
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<html>
<head>
<title>Simple JSP Java database connectivity</title>
</head>
<body>

</body>
</html>
<%
  if(conn!=null)
    conn.close();
%>

Advance JSP connection by including connection file in connection required JSP
dbConn.jsp

<%
    Connection conn = null;
    
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    
    String sURL="jdbc:mysql://localhost:3306/DBName";
    String sUserName="UserName";
    String sPwd="Password";
    
    conn = DriverManager.getConnection(sURL,sUserName,sPwd);
    
%>

include this file in JSP where connection is required

<%@ page language="java" import="java.sql.*"%>
<%@ include file="dbConn.jsp" %>
<%
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<html>
<head>
<title>Simple JSP Advance Java database connectivity</title>
</head>
<body>

</body>
</html>
<%
  if(conn!=null)
    conn.close();
%>

2. javaBean database connectivity

javaBean database connection is similar to JSP connection file. But all connection code is written in a javaBean and this javaBean can be used in JSP file with JSP useBean tag to make connection with database.

DBConnection.java

package com.db;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {

    public Connection getDBConnection() throws Exception{
        Connection conn = null;
        Class.forName("com.mysql.jdbc.Driver").newInstance();

        String sURL="jdbc:mysql://localhost:3306/DBName";
        String sUserName="UserName";
        String sPwd="Password";

        conn = DriverManager.getConnection(sURL,sUserName,sPwd);
      return conn;
    }
}

Use this javabean connection in JSP

<%@ page language="java" import="java.sql.*"%>
<jsp:useBean id="dbConn" scope="request" class="com.db.DBConnection"/>
<%
    Connection conn=dbConn.getDBConnection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<html>
<head>
<title>JavaBean java database connectivity</title>
</head>
<body>

</body>
</html>
<%
  if(conn!=null)
    conn.close();
%>

3. web.xml and servlet database connectivity

This is little good and advance technique of connectivity with database. We can define all database variables in web.xml and on load of servlet we can open connection in servletӳ init method through javaBean. In this method, ServletConfig object can be used to get variable parameter field name and value. This method is flexible and can change database name, server name, user and password in web.xml file without changing in the source code.

web.xml

<servlet>
         <servlet-name>DBInit</servlet-name>
         <servlet-class>com.db.DBInit</servlet-class>
         <init-param>
           <param-name>DriverName</param-name>
           <param-value>com.mysql.jdbc.Driver</param-value>
        </init-param>
        <init-param>
           <param-name>ServerName</param-name>
           <param-value>localhost</param-value>
        </init-param>
        <init-param>
           <param-name>Port</param-name>
           <param-value>3306</param-value>
        </init-param>
        <init-param>
           <param-name>DatabaseName</param-name>
           <param-value>dbName</param-value>
        </init-param>
        <init-param>
           <param-name>UserName</param-name>
           <param-value>userName</param-value>
        </init-param>
        <init-param>
           <param-name>Password</param-name>
           <param-value>yourPassword</param-value>
        </init-param>
         <load-on-startup>1</load-on-startup>
</servlet>

Servlet to initialize the values

DBInit.java

package com.db;

import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import com.db.DBConnection;

public class DBInit extends HttpServlet {

    public void init(ServletConfig config) throws ServletException {

        String jdbcDriverName=config.getInitParameter("DriverName");
        String jdbcServerName=config.getInitParameter("ServerName");
        String jdbcPort=config.getInitParameter("Port");
        String jdbcDatabaseName=config.getInitParameter("DatabaseName");
        String jdbcUserName=config.getInitParameter("UserName");
        String jdbcPassword=config.getInitParameter("Password");

        ServletContext sc = config.getServletContext();
        // set in application scope of web application to access in future

        DBConnection dbConn=null;

        try{
            dbConn=new DBConnection();

            dbConn.setSDriverName(jdbcDriverName);
            dbConn.setSServerName(jdbcServerName);
            dbConn.setSPort(jdbcPort);
            dbConn.setSDatabaseName(jdbcDatabaseName);
            dbConn.setSUserName(jdbcUserName);
            dbConn.setSPassword(jdbcPassword);

            sc.setAttribute("dbConn", dbConn);

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

    }

}

DBConnection.java

package com.db;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {

    public String sDriverName=null;
    public String sServerName=null;
    public String sPort=null;
    public String sDatabaseName=null;
    public String sUserName=null;
    public String sPassword=null;

    public String getSDriverName() {
        return sDriverName;
    }

    public void setSDriverName(String driverName) {
        sDriverName = driverName;
    }

    public String getSServerName() {
        return sServerName;
    }

    public void setSServerName(String serverName) {
        sServerName = serverName;
    }

    public String getSPort() {
        return sPort;
    }

    public void setSPort(String port) {
        sPort = port;
    }

    public String getSDatabaseName() {
        return sDatabaseName;
    }

    public void setSDatabaseName(String databaseName) {
        sDatabaseName = databaseName;
    }

    public String getSUserName() {
        return sUserName;
    }

    public void setSUserName(String userName) {
        sUserName = userName;
    }

    public String getSPassword() {
        return sPassword;
    }

    public void setSPassword(String password) {
        sPassword = password;
    }

    public Connection getDBConnection() throws Exception{
        Connection conn = null;
        Class.forName(sDriverName).newInstance();

        String sURL ="jdbc:mysql://"+sServerName+":"+sPort+"/"+sDatabaseName;

        conn = DriverManager.getConnection(sURL,sUserName, sPassword);
      return conn;
    }
}

Connection can be used in JSP as shown in this JSP file

<%@ page language="java" import="java.sql.*"%>
<jsp:useBean id="dbConn" scope="application" class="com.db.DBConnection"/>
<%
    Connection conn=dbConn.getDBConnection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<html>
<head>
<title>web.xml and Servlet database connectivity</title>
</head>
<body>
<%
while(rs.next())
{
 rs.getString("id");    
}
%>
</body>
</html>
<%
  if(conn!=null)
    conn.close();
%>

4. properties file database connectivity

When you need more flexible environment of web application and database connectivity, properties file is another good option for database connectivity. You can change database variable name without changing restarting web server and without changing in java source code. In this technique we have to use properties file and define all database variables values in this file. This properties value can be accessed through ResourceBundle of java. Connection code can be written in javaBean or in JSP file to make connection.
This properties file should be copied in WEB-INF/classes folder

connection_config.properties

# Usually com.mysql.jdbc.Driver
driver.name=com.mysql.jdbc.Driver

# Usually localhost
server.name=localhost

# Usually 3306
port.no=3306

# Usually project
database.name=dbName

# Usually you define
user.name=UserName

# Usually you define
user.password=Password

javaBean is used to make connection

DBConnection.java

package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ResourceBundle;

public class DBConnection {

    public String sDriverName=null;
    public String sServerName=null;
    public String sPort=null;
    public String sDatabaseName=null;
    public String sUserName=null;
    public String sPassword=null;

    public Connection getDBConnection() throws Exception{
        Connection conn = null;

        ResourceBundle rb=ResourceBundle.getBundle("connection_config");

         sDriverName=rb.getString("driver.name");
         sServerName=rb.getString("server.name");
         sPort=rb.getString("port.no");
         sDatabaseName=rb.getString("database.name");
         sUserName=rb.getString("user.name");
         sPassword=rb.getString("user.password");

        Class.forName(sDriverName).newInstance();

        String sURL ="jdbc:mysql://"+sServerName+":"+sPort+"/"+sDatabaseName;

        conn = DriverManager.getConnection(sURL,sUserName, sPassword);
      return conn;
    }
}

connection can be used in JSP file

<%@ page language="java" import="java.sql.*"%>
<jsp:useBean id="dbConn" scope="request" class="com.db.DBConnection"/>
<%
    Connection conn=dbConn.getDBConnection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<html>
<head>
<title>properties file database connectivity</title>
</head>
<body>
<%
while(rs.next())
{
 rs.getString("id");    
}
%>
</body>
</html>
<%
  if(conn!=null)
    conn.close();
%>

5. XML file database connectivity

When your application is more focused on XML based pattern. You can use XML file to make database connection in java. XML is getting more popularity and easy to handle use and implement in web application. These results, we can define all database properties in XML node tree. These values can be getting from any XML parser. Connection code can be defined in javaBean and in JSP file. This is more flexible method. We do not have to restart web server when we do change in XML file and no change will be required to do in source code.

dbConnection.xml

<?xml version="1.0" encoding="iso-8859-1"?>
 <connection-config>
   <driverName>com.mysql.jdbc.Driver</driverName>
   <serverName>localhost</serverName>
   <port>3306</port>
   <databaseName>dbName</databaseName>
   <userName>root</userName>
   <pwd></pwd>
 </connection-config>

DBConnection.java

package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import org.w3c.dom.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

public class DBConnection {

    public String sDriverName=null;
    public String sServerName=null;
    public String sPort=null;
    public String sDatabaseName=null;
    public String sUserName=null;
    public String sPassword=null;

    public Connection getDBConnection() throws Exception{
        Connection conn = null;

        DocumentBuilderFactory dbf=DocumentBuilderFactory.newInstance();
        DocumentBuilder db =dbf.newDocumentBuilder();
        Document doc=db.parse("c:\\tomcat\\webapps\\myApp\\dbConnection.xml");

        NodeList driverName = doc.getElementsByTagName("driverName");
        NodeList serverName = doc.getElementsByTagName("serverName");
        NodeList port = doc.getElementsByTagName("port");
        NodeList databaseName = doc.getElementsByTagName("databaseName");
        NodeList userName = doc.getElementsByTagName("userName");
        NodeList pwd = doc.getElementsByTagName("pwd");

        Node nDriverName = driverName.item(0);
        sDriverName=nDriverName.getFirstChild().getNodeValue().trim();

        Node nServerName = serverName.item(0);
        sServerName=nServerName.getFirstChild().getNodeValue().trim();

        Node nPort = port.item(0);
        sPort=nPort.getFirstChild().getNodeValue().trim();

        Node nDatabaseName = databaseName.item(0);
        sDatabaseName=nDatabaseName.getFirstChild().getNodeValue().trim();

        Node nUserName = userName.item(0);
        sUserName=nUserName.getFirstChild().getNodeValue().trim();

        Node nPwd = pwd.item(0);
        sPassword=nPwd.getFirstChild().getNodeValue().trim();

        Class.forName(sDriverName).newInstance();

        String sURL ="jdbc:mysql://"+sServerName+":"+sPort+"/"+sDatabaseName;

        conn = DriverManager.getConnection(sURL,sUserName, sPassword);
      return conn;
    }
}

Use in JSP

<%@ page language="java" import="java.sql.*"%>
<jsp:useBean id="dbConn" scope="request" class="com.db.DBConnection"/>
<%
    Connection conn=dbConn.getDBConnection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<%
  if(conn!=null)
    conn.close();
%>

6. Connection pooling in java database connectivity

Connection pooling gives good performance on database connectivity over all previous database connection. How it increase performance and efficiency of the database connectivity. When we open new connection in database and after using that connection we closed that connection and it returns again to the connection pool for next waiting. The next time when you need a connection with database, it takes connection from connection pool and reuses the previous connection.

This is database Connection Pooling with Tomcat
tomcat/config/context.xml

<?xml version='1.0' encoding='utf-8'?>

<Context>

    <WatchedResource>WEB-INF/web.xml</WatchedResource>

    <Resource name="jdbc/PooledDB"
    auth="Container"
    type="javax.sql.DataSource"
    factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
    username="DBUserName"
    password="DBPassword"
    driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/dbName?autoReconnect=true"
    maxWait="1000"
    removeAbandoned="true"
    maxActive="30"
    maxIdle="10"
    removeAbandonedTimeout="60"
    logAbandoned="true"/>

</Context>

Copy mysql-connector-java.jar MySQL jdbc driver in tomcat/lib folder

DBConnection.java

package com.db;

import java.sql.Connection;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DBConnection {

    public static Connection getConnection() throws Exception
    {
          return getPooledConnection();
    }

    public static Connection getPooledConnection() throws Exception{
       Connection conn = null;

        try{
          Context ctx = new InitialContext();
          if(ctx == null )
              throw new Exception("No Context");

          DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/PooledDB");

          if (ds != null) {
             conn = ds.getConnection();
            return conn;
          }else{
              return null;
          }

        }catch(Exception e) {
            e.printStackTrace();
            throw e;
        }
    }
}

connection can be used in JSP file

<%@ page language="java" import="java.sql.*"%>
<jsp:useBean id="dbConn" scope="request" class="com.db.DBConnection"/>
<%
    Connection conn=dbConn.getConnection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<%
  if(conn!=null)
    conn.close();
%>

Java Tips and Tricks

About Me

Pages

Wednesday, 20 February 2013

Java – Choose best Database connectivity with web application