Database Connectivity and Operations Comparison : Java and Force.com

This section demonstrates how Java server side and Apex interact with the Database using code snippets.

Connectivity Code Sample

Java

import java.sql*;
public class DBTest
{
   public static void main(String[] args)
   {
      try{
      //step1 Load the driver class
      Class.forName("oracle.jdbc.driver.OracleDriver");

      //step2 Create the connection object
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe",
          "system","oracle");

      //step3 Create the statement object
      Statement stmt = con.createStatement();

      //step4 Execute query
      ResultSet rs = stmt.executeQuery("select * from emp");
      while(rs.next())
        System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));

      //step 5 Close the connecttion object
      con.close();
     }catch(Exception e){System.out.println(e);}
  }
}

Disadvantages

  1. Database connections to be managed by developer
  2. Database security has to be exclusively managed by DBA
  3. Mapping between the Domain objects and database tables are to be exclusively defined by developer

Apex

 @isTest
 public class DBTest {
    public static testMethod void anyName(){
       try{
           List<EMP> emps = [select emp_id,name,age from EMP];
           System.debug('The Employee Details'+ emps);
       }
       catch(Exception e){
           System.debug('No records found');
       }
   }
}

Advantages

  1. Database connections are managed by default within the platform

  2. Database security can be managed by the developer

  3. Sobjects are directly mapped as Classes

    Model Controller
    Sobject Class
    Field Data members
    Buttons and Links Member methods
    Record Object
    Records Collections

Development process using a sample Use Case

Technologies and runtime components used to implement a basic Java application is illustrated in Figure.1 and described below:

  1. Implement a controller using Java servlet. The client requests are received by this controller.

  2. Implement a helper for the controller using Java class (POJO) or session EJB. Need to implement all the required beans out of which the controller picks the respective bean based on the parameters received in the request.

  3. Implement and configure a set of classes to establish a connection to the database (JDBC). The Controller servlet then by itself or through a controller helper communicates with the middle tier or directly to the database to fetch the required data.

  4. Implement the beans so that the controller sets the resultant JavaBeans (either same or a new one) in one of the following contexts – request, session or application.

  5. The controller then dispatches the request to the next view based on the request URL.

  6. The View uses the resultant JavaBeans from Step 4 to display data. Note that there is no presentation logic in the JSP. The sole function of the JSP is to display the data from the JavaBeans set in the request, session or application scopes.

    ../../_images/java-servlet-flow.png

    Figure 1: Java Application flow

Implementation above can be done in Force.com platform in a very simple and concise steps as illustrated in Figure. 2 and described below:

  1. Create and configure a Sobject using declarative approach (Model)

  2. Create a Visual Force page for the Sobject (View)

  3. Implement an Apex Class for Controller. Most of the code are auto-generated. Only business logic need to be implemented. (Controller).

    ../../_images/force.com-servlet-flow.png

Comparison of Development process

Create a simple application to accept employee details, store it in the database and display the list of employees using MVC pattern.

Pictorial Representation of the Development process for this Use case

../../_images/Java-dev-process.png
../../_images/Force.com-dev-process.png

Employee creation using MVC pattern in Java

  1. Create a Database named testdb and an Employee table in MySql as indicated in the table
Field type Key Extra
emp_id int Primary key Auto_increment
emp_name varchar(255)    
salary double    
dept_name varchar(255)    
  1. Create Dynamic Web Project in Eclipse and name it ServletPRGMVC
  2. Download MySQL Connector for Java. It can be downloaded from `http://dev.mysql.com/downloads/connector/j/
  3. Configure the JNDI DataSource in Tomcat by adding a declaration for your resource to your Context in context.xml. This can be done by creating the following context.xml in META-INF folder of the project.
<?xml version=“1.0” encoding=“UFT-8”?>
 <!— The contents of this file will be loaded for each web application—>
 <Context crossContext=“true”>

      <!—Default set of monitored resources—>
  <WatchedResource>WEB-INF/web.xml</WatchedResource>

   <Resource name=“jdbc/testDb” auth=“Container”
                     type=“javax.sql.DataSource”
                     maxActive=“100” maxIdle=“30” Wait=“10000”
                username=“<YOUR _DATABASE_USERNAME>”
                password=“<YOUR _DATABASE_PASSWORD>”
                driverClassName=“com.mysql.jdbc.Driver”
                url=“adbc:mysql://localhost/exampledb/”

 </Context>
  1. Create Transfer Object/ Domain Object class and name it Employee.java as illustrated below.

    package com.demo.to;
    public class Employee {
            private int employeeId;
            private String employeeName;
            private double salary;
            private String deptName;
    
            public int getEmployeeId() {
                return employeeId;
            }
            public void setEmployeeId(int employeeId) {
                this.employeeId = employeeId;
            }
            public String getEmployeeName() {
                return employeeName;
            }
            public void setEmployeeName(String employeeName) {
                this.employeeName = employeeName;
            }
            public double getSalary() {
                return salary;
            }
            public void setSalary(double salary) {
                this.salary = salary;
            }
            public String getDeptName() {
                return deptName;
            }
            public void setDeptName(String deptName) {
                this.deptName = deptName;
            }
            @Override
            public String toString() {
                return "Employee [employeeId=" + employeeId + ", employeeName="
                        + employeeName + ", salary=" + salary + ", deptName="
                        + deptName + "]";
            }
    }
    
  2. Create Custom Application Exception class

    package com.demo.exception;
    public class ApplicationException extends Exception {
     private static final long serialVersionUID = 1L;
     public ApplicationException() { }
     public ApplicationException(String message) {
        super(message);
    }
     public ApplicationException(String message, Throwable e) {
        super(message, e);
    }
    }
    
  3. Create a DBUtil class in the file DbUtil.java

package com.demo.db;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

  public class DbUtil {
   public static void close(Connection connection) {
      if (connection != null) {
          try {
             connection.close();
          } catch (SQLException e) { }
      }
   }

   public static void close(Statement statement) {
      if (statement != null) {
          try {
             statement.close();
          } catch (SQLException e) { }
      }
   }

   public static void close(ResultSet resultSet) {
       if (resultSet != null) {
          try {
             resultSet.close();
          } catch (SQLException e) { }
       }
   }
 }
  1. Create a Data Access Object (DAO) class and call it EmployeeDAO.java. The constructor of the class gets the data source object and stores it. DAO method creates Connection and Statement object. After executing the query, it returns the result to the upper layer. If any exception occurs, it creates ApplicationException with a message and cause (Throwable object) of the exception and throws it.
 package com.demo.dao;

 import java.sql.Connection;
 import java.sql.SQLException;
 import java.sql.Statement;
 import javax.sql.DataSource;
 import com.demo.db.DbUtil;
 import com.demo.exception.ApplicationException;
 import com.demo.to.Employee;

 public class EmployeeDAO {

    DataSource ds;

 public EmployeeDAO(DataSource ds) {
     this.ds = ds;
 }

 public int addEmployee(Employee employee) throws ApplicationException {
     Connection connection = null;
     Statement stmt = null;
     String query = "insert into employee(emp_name, salary, dept_name) values('"
             + employee.getEmployeeName()
             + "',"
             + employee.getSalary()
             + ",'" + employee.getDeptName() + "')";

     int row = -1;
     try {
         connection = ds.getConnection();
         stmt = connection.createStatement();
         row = stmt.executeUpdate(query);
     } catch (SQLException e) {
         ApplicationException exception = new ApplicationException(
                 "Unable to insert data: " + e.getMessage(), e);
         throw exception;
     } finally {
         DbUtil.close(stmt);
         DbUtil.close(connection);
     }
     return row;
  }
}
  1. Create a Controller class (Servlet) and name it EmployeeServlet.java.
package com.demo.servlets;

import java.io.IOException;
import javax.annotation.Resource;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import com.demo.dao.EmployeeDAO;
import com.demo.exception.ApplicationException;
import com.demo.to.Employee;

public class EmployeeServlet extends HttpServlet {
  private static final long serialVersionUID = 1L;

  @Resource(name="jdbc/testDB")
  DataSource ds;

  public EmployeeServlet() {
        super();
  }

  public void doGet(HttpServletRequest request,
          HttpServletResponse response)
          throws ServletException, IOException {
      RequestDispatcher view = request.getRequestDispatcher("displayEmployee.jsp");
      view.forward(request, response);
  }

  public void doPost(HttpServletRequest request,
          HttpServletResponse response)
          throws ServletException, IOException {

          //Get Request parameters from form
          String empName = request.getParameter("employeeName");
          String deptName = request.getParameter("deptName");
          double salary = Double.parseDouble(request.getParameter("salary"));

          //Create Employee Object
          Employee employee = new Employee();
          employee.setEmployeeName(empName);
          employee.setSalary(salary);
          employee.setDeptName(deptName);

          //Invoke method in DAO class passing employee object
          EmployeeDAO empDAO = new EmployeeDAO(ds);

          int rows;
          int success = 0;
          try {
              rows = empDAO.addEmployee(employee);
              /*Using PRG Pattern.
               * Instead of forwarding from doPost() method, we are doing a
               * redirection to avoid duplicate form submission.
               */
              if(rows > 0)
                  success = 1;
          } catch (ApplicationException e) {
              //Log the error
              request.setAttribute("error", e.getMessage());
          }

          response.sendRedirect("displayEmployee.do?s=" +
                        success);
      }
  }
  1. When the application is deployed in the tomcat container, the container looks up the JDBC resource and injects it into Servlet.
  2. In Servlet doPost() method, Employee Transfer Object is created – method in DAO class is invoked which takes TO as a parameter. The result is returned to the EmployeeServlet
  3. This Servlet redirects to another Servlet setting the success value.
  1. Create a display servlet and name it DisplayEmployeeServlet.java. This Servlet constructs the result message and forwards it to the JSP view page
package com.demo.servlets;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class DisplayEmployeeServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    public DisplayEmployeeServlet() {
        super();
    }

    protected void doGet(HttpServletRequest request,
        HttpServletResponse response) throws ServletException, IOException {
        int success = Integer.parseInt(request.getParameter("s"));
        if (success == 1)
          request.setAttribute("result", "Employee Successfully Inserted");
        else
          request.setAttribute("result",
                  "Employee Not Inserted: " + request.getAttribute("error"));
        RequestDispatcher view = request
              .getRequestDispatcher("displayEmployee.jsp");
        view.forward(request, response);
    }
 }
  1. Create a new JSP page(View of the MVC) in WebContent folder and name it as “displayEmployee.jsp“.It displays the form if the result attribute is empty, else it displays the result (stored in the request scope) forwarded by the DisplayEmployeeServlet.
<%@page language="java" contentType="text/html;
  charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@tablib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Employees</title>
</head>
<body>
    <c:if test="${not empty result}">
         <h5><c:out value="${result}"></c:out></h5>
    </c:if>
    <%---Displays Form----%>
    <form action="employee.do" method="post">
    <table border="1" cellpadding="5" cellspacing="5">
    <tr>
          <td>Employee Name:</td>
          <td>input type="text" name="employee Name"</td>
    </tr>
    <tr>
           <td>Salary:</td>
           <td>input type="text" name="salary"></td>
    </tr>
    <tr>
           <td>Department Name:</td>
           <td>input type="text" name="deptName"></td>
    </tr>
    <tr>
           <td colspan="2"><input type="submit" name="submit" value="Insert"></td>
    </tr>
    </table>
    </form>
 </body>
 </html>
  1. Configure web.xml to add servlet mappings and DB Configurations
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javae/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
 <resource-ref>
     <description>DB Connection</description>
     <res-ref-name>jdbc/textDB</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
 </resource-ref>

 <servlet>
     <servlet-name>EmployeeServlet</servlet-name>
     <servlet-class>
        com.demo.servlets.EmployeeServlet
     </servlet-class>
 </servlet>
 <servlet-mapping>
     <servlet-name>EmployeeServlet</servlet-name>
     <url-pattern>/employee.do</url-pattern>
 </servlet-mapping>
 <servlet>
     <servlet-name>DisplayEmployeeServlet</servlet-name>
     <servlet-class>
        com.demo.servlets.DisplayEmployeeServlet
     </servlet-class>
 </servlet>
 <servlet-mapping>
     <servlet-name>DisplayEmployeeServlet</servlet-name>
     <url-pattern>/displayEmployee.do</url-pattern>
 </servlet-mapping>
</web-app>
  1. Deploy the project in tomcat server and open the url in the browser http://localhost:8080/ServletPRGMVC/employee.do

Input Form

../../_images/emp-input-form.png

Input Form 2

../../_images/emp-input-form2.png
  1. Create a Servlet to display the list of employees and name it EmployeeListServlet.java
package com.demo.servlets;

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 javax.annotation.Resource;
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 EmployeeListServlet extends HttpServlet{
     private static final long serialVersionUID=1L;

     @Resource(name = "jdbc/testDB")
     DataSource ds;

     public EmployeeServlet(){
         super();
     }

     public void doGet(HttpServletRequest request,HttpServletResponse response)
         throws ServletException, IOException{
         try{
             Connection con=ds.getConnection();

             Statement stmt = con.createStatement();
             String query = "select *from employee";
             ResultSet rs = stmt.executeQuery(query);

             PrinterWriter out = response.getWriter();
             response.setContentType("text/html");
             out.print("<center><h1>Employee Details</h1></center>");
             out.print("<html><body>");
             out.print("<table border=\"1\" cellspacing=10 cellpadding=5>");
             out.print("<tr><th>Employee ID</th>");
             out.print("<th>Employee Name</th>");
             out.print("<th>Salary</th>");
             out.print("<th>Department</th></tr>");

             while(rs.next()){
                out.print("<tr>");
                out.print("<td>"+rs.getInt("emp_id")+"</td>");
                out.print("<td>"+rs.getString("emp_name")+"</td>");
                out.print("<td>"+rs.getDouble("salary")+"</td>");
                out.print("<td>"+rs.getString("dept_name")+"</td>");
                out.print("</tr>");
             }
             out.print("</table></body></html>");
         }catch(SQLException e){
             e.printStackTrace();
         }
        }
       public void doPost(HttpServletRequest request, HttpServletResponse response)
             throws ServletException,IOException{
             }
     }
  1. Change web.xml to include the mapping for EmployeeListServlet.
<servlet>
  <servlet-name> EmployeeListServlet</servlet-name>
  <servlet-class>
    <com.demo.servlets.EmployeeListServlet</servlet-class>
 </servlet>
 <servlet-mapping>
   <servlet-name> EmployeeListServlet</servlet-name>
   <url-pattern> /employeelist.do</url.pattern>
 </servlet-mapping>
  1. Deploy the project on tomcat and restart the server.

Open url at http://localhost:8080/ServletPRGMVC/employeelist.do and the output will be as illustrated.

Employee Details
Employye Id Employee Name Salary Department
1 Kumar 30000.0 Developement
2 John 40000.0 Developement
  1. Program Control Flow:
../../_images/Control-flow.png

The program control flows as listed in the following steps:

  1. Web client (browser) sends requests to EmployeeServlet through request URL. Since the request is sent directly to Servlet, it is an HTTP GET Request
  2. Servlet in doGet() method forwards the request to JSP.
  3. JSP sends HTTP Response with HTML content to client.
  4. Client submits the form to EmployeeServlet as HTTP POST request.
  5. Servlet creates Transfer Object (TO pattern).
  6. Servlet invokes a method in Data Access Object (DAO pattern).
  7. DAO accesses database to insert a row.
  8. Database Result is returned to DAO method.
  9. DAO returns the result to Servlet.
  10. Servlet checks the result and sets a variable and redirects the result to another Servlet
  11. Client makes a new HTTP GET request to DisplayServlet.
  12. DisplayServlet sets request attribute and forward it to JSP.
  13. JSP displays the result to the client by reading the request attribute.
  1. Click Setup
../../_images/Setupnew.png
  1. Select Create and then click Objects from the drop down menu
../../_images/Createobjects.png
  1. Create a new Custom object and fill Employee object(table) details. Finally click save.
../../_images/CreateCustomObjnew.png ../../_images/EmployeeDetailsnew.png
  1. Enter the details for the Tab for UI and then click on next
../../_images/EmployeeTabnew.png
  1. Click on the Schema Builder in the Quick Find Box and then select the Employee object to add a new field. In the Elements tab, select Auto Number and drop in the Employee object.
../../_images/SchemaBuildernew.png ../../_images/EmploeeSBnew.png ../../_images/AutoNumbernew.png
  1. Now navigate to the Employee object (refer the step2). Click on Edit within the page layout block within the Employee object.
../../_images/EmployeeObjnew.png ../../_images/EmpPageLayoutnew.png
  1. Select the EmployeeID, Employee Name, Department and Salary from top pane and drop in the Information Section as shown in the diagram and click on save.
../../_images/PageLayoutnew.png
  1. Now click on the Employees Tab and enter data sample data and save.
../../_images/EmployeeNew.png ../../_images/EmpDetailsnew.png

9.Click Employees tab to see the list of data.

../../_images/ListViewnew.png

Several views can be created to display the records with appropriate fields.

Disadvantages Advantages
Database connections to be managed
by developer
Database connections are managed by default with in the platform
CRUD operations over the records have
to be defined by the developer
CRUD operations are implemented in the Metadata API

Summary

In this tutorial we learnt differences in the development as well as runtime components between Java and Apex.