Java Database Access Model

This blog is going to explain how to design java database access model?. It contains 3 part, 1st part is for database model code, 2nd part is for access model code and 3rd part is for stored procedure sql code.

Database access model is used to connect database, retrieve records and passing input/output parameters using Stored Procedures. All the connections and transactions are in one class file. Designed simple method which is used to connect database and to produce the result set.

In the class file, one method is taking care of connecting the database, another method is to retrieve the result set and last method is doing for insert, modify and delete the record.

This database access model contains all the database transactions are in one place and easy to maintain the error code instead of checking multiple methods in different classes.

For example if page want to get result set from employee table with some condition, then pass the stored procedure name and the in/out parameter name/value to the class object. This class object will use Callable Statement to built the stored procedure with in/out parameters, connect to the database and retrieve the result set.

The below codes are one single class file, and explained each method functionality in the commented area.

Part 1 - StoredProcedure.java

import java.sql.*;
import java.util.*;
import javax.naming.*;
import javax.sql.DataSource;

public class StoredProcedure
{
  private Connection sqlConn = null;
  private CallableStatement calStmt;
  private List <Hashtable<String,Object>> lstResults = new ArrayList<Hashtable>();
  private String strError = "";

  //Constructor method is used to create database connection
  //Pass the stored procedure name and number of parameters
  //if no of parameters is '0', then code won't generate in/out arguments
  //if no of parameters more then '0', let us assume iNoOfParameters = 3,
  //then code generate in/out arguments like that 'call storedprocedurename(?,?,?)'

  public StoredProcedure(String sProcedureName, int iNoOfParameters)
  {
    try
    {
      //Get database connection value from web.xml using JNDI
      //Refer JNDI with Environment Variables
      //Refer JNDI InitialContext Program?

      Context initCtx = new InitialContext();
      Context envCtx = (Context) initCtx.lookup("java:comp/env");
      DataSource sqlDS = (DataSource)envCtx.lookup("jdbc/dns_cip_DB");

      sqlConn = sqlDS.getConnection();
      sProcedureName += "(" + RepeatStr("?",iNoOfParameters) + ")";
      calStmt = sqlConn.prepareCall("{call " + sProcedureName + "}");
    }
    catch (Exception e){}
  }

  //List method is used to get result set from the database using stored procedure,
  //stored to the Hashtable object with column name and value

  public List<Hashtable<String,Object>> List()
  {
    ResultSet rs;
    ResultSetMetaData rsmd;
    try
    {
      calStmt.execute();
      rs = calStmt.getResultSet();
      rsmd = rs.getMetaData();
      while (rs.next())
      {
        Hashtable hasRS = new Hashtable();
        for (int i = 1; i<=rsmd.getColumnCount(); i++)
        {
          hasRS.put(rsmd.getColumnName(i), rs.getObject(i));
        }
        lstResults.add(hasRS);
      }
      rs.close();
    }
    catch (Exception e) {}
    finally
    {
      if (sqlConn != null)
      {
        try
        {
          sqlConn.close();
        }
        catch (SQLException e) { }
      }
    }
    return lstResults;
  }

  //IUD method is used to execute stored procedure.

  public String IUD()
  {
    try
    {
      calStmt.execute();
    }
    catch (Exception e){}
    finally
    {
      if (sqlConn != null)
      {
        try
        {
          sqlConn.close();
        }
        catch (SQLException e) { }
      }
    }
    return strError;
  }

  //AddIn method is used for passing input parameters

  public void AddIn(String sParameter, Object oValue) throws SQLException
  {
    try
    {
      calStmt.setObject(sParameter, oValue);
    }
    catch (Exception e){}
  }

  //AddOut method is used for passing output parameters

  public void AddOut(String sParameter, int iDataType)
  {
    try
    {
      calStmt.registerOutParameter(sParameter, iDataType);
    }
    catch (Exception e){}
  }

  //GetOut method is used to get values of passed output parameters

  public Object GetOut(String sParameter)
  {
    Object objOutValue = null;
    try
    {
      if (calStmt.getObject(sParameter) != null)
      objOutValue = calStmt.getObject(sParameter);
    }
    catch (Exception e){}
    return objOutValue;
  }

  //RepeatStr method is used to generate no of parameters

  public String RepeatStr(String str, int times)
  {
    StringBuilder ret = new StringBuilder();
    for(int i = 0;i < times;i++)
    {
      ret.append(str);
      if (i!=times-1) ret.append(",");
    }
    return ret.toString();
  }

Part 2 - testsp.jsp

<%@ page import="java.sql.*,java.util.*,com.dns.db.StoredProcedure" %>

//Insert/Modify/Delete new record
//Passing 3 input arguments and 1 output argument
//Passing arguments contains by different data type
//Calling StoredProcedure constructor, passing stored procedure name and number
of arguments
//sDML - is used to identity the functions is for insert(i), modify(m) and delete(d) record

<%
  List<Hashtable<String,Object>> lstResults = new ArrayList<Hashtable<String,Object>>();
  StoredProcedure spList1 = new ("spAttachementTypeIUD", 4);
  spList1.AddIn("sDesc", "Context Added 1");
  spList1.AddIn("iRID", 0);
  spList1.AddIn("sDML", "i");
  spList1.AddOut("sResult", java.sql.Types.VARCHAR);
  spList1.IUD();
%>

//Retrieving result set
//Passing arguments is 0

<%
  StoredProcedure spList = new StoredProcedure("spAttachementTypeList", 0);
  lstResults = spList.List();
  for(int iRow = 0; iRow<lstResults.size();iRow++)
  {
    Hashtable hashRS = new Hashtable();
    hashRS = (Hashtable)lstResults.get(iRow);
    out.println(hashRS.get("rid")+ " - " + hashRS.get("rdesc"));
  }
%>

Part 3 - Stored Procedure Code

//spAttachementTypeIUD code

CREATE PROCEDURE spAttachementTypeIUD
  @iRID int,
  @sDML varchar(1),
  @sDesc varchar(100),
  @sResult varchar(100) OUTPUT
AS
BEGIN
  SET @sResult = 'test'
  IF @sDML ='i'
  BEGIN
    INSERT INTO
      tblattachementtype(sattachementtypedesc)
    VALUES
      (@sDesc)
    SET @iRID = @@IDENTITY
  END
  ELSE IF @sDML ='m'
  BEGIN
    UPDATE
      tblattachementtype
    SET
      sattachementtypedesc = @sDesc
    WHERE
      iattachementtypeid = @iRID
    SET @iRID = @iRID
  END
  ELSE IF @sDML ='d'
  BEGIN
    DELETE FROM tblattachementtype WHERE iattachementtypeid = @iRID
    SET @iRID = @iRID
  END
END

//spAttachementTypeList code

CREATE PROCEDURE spAttachementTypeList AS
BEGIN
  SELECT
    iattachementtypeid rid, sattachementtypedesc rdesc,
    0 rtotal
  FROM
    tblattachementtype
END