Saturday, January 12, 2013

Data Access With Spring JDBC (Note 1)

When developing enterprise applications, we have lots of options when it comes to the implementation of the DAO (Data access Object) layer. Either we may use an ORM framework like Hibernate, iBatis or pure JDBC. 

JDBC fulfills almost all the necessary data access requirements. and provides low level access to the data access operations. But we have to handle connections and lots of other things. 

Spring Data Access framework provides a nice way to access data while the framework take cares of managing connections and so on. So we can focus on business requirement thoroughly rather than worrying about JDBC nuts and bolts :) .

A major advantage of using Spring JDBC over hibernate is we can effectively fine tune the data access layer. so if the application is time critical Spring JDBC is a good choice.

OK. sorry for the long story. lets have a look at our cup of Spring JDBC :)

In order to preserve the simplicity, I have selected a very simple scenario where we are going to apply Spring JDBC. It is a many to many relationship between Employee and Project(Please see below EER diagram).
 OK, lets create model classes to represent this scenario.
Employee.java

package model;

import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Kanishka
 */
public class Employee {
    private int empID;
    private String empName;
    private String empTel;
    private float salary;
    private List<Project> projects;

    public Employee() {
        projects=new ArrayList<Project>();
    }

    public int getEmpID() {
        return empID;
    }

    public void setEmpID(int empID) {
        this.empID = empID;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public String getEmpTel() {
        return empTel;
    }

    public void setEmpTel(String empTel) {
        this.empTel = empTel;
    }

    public float getSalary() {
        return salary;
    }

    public void setSalary(float salary) {
        this.salary = salary;
    }

    public List<Project> getProjects() {
        return projects;
    }

    public void setProjects(List<Project> projects) {
        this.projects = projects;
    }

    @Override
    public String toString() {
        return "Employee{" + "empID=" + empID + ", empName=" + empName + ", empTel=" + empTel + ", salary=" + salary + ", projects=" + projects + '}';
    }
}

Project.java
package model;

import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Kanishka
 */
public class Project {
    private int projID;
    private String projName;
    private String projCode;
    private int projDuration;
    private List employeeList;

    public Project() {
        employeeList=new ArrayList();
    }

    public int getProjID() {
        return projID;
    }

    public void setProjID(int projID) {
        this.projID = projID;
    }

    public String getProjName() {
        return projName;
    }

    public void setProjName(String projName) {
        this.projName = projName;
    }

    public String getProjCode() {
        return projCode;
    }

    public void setProjCode(String projCode) {
        this.projCode = projCode;
    }

    public int getProjDuration() {
        return projDuration;
    }

    public void setProjDuration(int projDuration) {
        this.projDuration = projDuration;
    }

    public List getEmployeeList() {
        return employeeList;
    }

    public void setEmployeeList(List employeeList) {
        this.employeeList = employeeList;
    }

    @Override
    public String toString() {
        return "Project{" + "projID=" + projID + ", projName=" + projName + ", projCode=" + projCode + ", projDuration=" + projDuration + ", employeeList=" + employeeList + '}';
    }
}

Now we are ready to configure the project. I am using Maven in this sample project. Maven dependencies are
  • Spring context
  • Spring JDBC
  • MySql JDBC connector
  • Commons DBCP (for the data source)
Pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.kani</groupId>
    <artifactId>JDBCTemplateDemo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>JDBCTemplateDemo</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
  
    <dependency>
        <groupId>commons-dbcp</groupId>
        <artifactId>commons-dbcp</artifactId>
        <version>1.4</version>
    </dependency>
      
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>3.2.0.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>3.2.0.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.9</version>
    </dependency>
</project>
The next plan is implementing DAO layer. Here I am using programming to an interface(P2I) in my DAO layer as I am planning to use DAO design pattern in my sample solution. 

Spring declares that they will take care of following actions.
  • Opening connections.
  • Prepare and execute the statement.
  • Set up the loop to iterate through the results (if any).
  • Process any exception.
  • Handle transactions.
  • Close the connection, statement and resultset.
So we can focus on queries, parameters and entity mapping stuff.

In my DAO implementation I will consider only basic operations that will sufficient to demonstrate JDBC template.

Interfaces.
  • IEmployeeDAO
  • IProjectDAO
IProjectDAO.java
package dao;

import java.util.List;
import model.Employee;
import model.Project;

/**
 *
 * @author Kanishka
 */
public interface IProjectDAO {
    public String QUERY_CREATE_NEW_PROJECT="INSERT INTO project(projName,projCode,projDuration) VALUES (?,?,?)";
    public String QUERY_GET_PROJECTS_OF_EMPLOYEE="SELECT  proj.projID AS `projID`,  proj.projName AS `projName`,  proj.projCode AS `projCode`,  proj.projDuration AS `projDuration`,  ehp.Employee_empID AS `empID` FROM  employee_has_project ehp  INNER JOIN project proj ON ehp.Project_projID = proj.projID WHERE  ehp.Employee_empID = ?";
    
    public List<Project> getProjectsOfEmployee(Employee emp);
    public boolean createNewProject(Project proj);
}
IEmployeeDAO.java
package dao;

import java.util.List;
import model.Employee;

/**
 *
 * @author Kanishka
 */
public interface IEmployeeDAO {
    public String QUERY_GET_ALL_EMPLOYEES="SELECT empID,empName,empTel,salary FROM employee";
    public String QUERY_FIND_EMPLOYEE_BY_ID="SELECT empID,empName,empTel,salary FROM employee e WHERE e.empID=?";
    public String QUERY_ADD_NEW_EMPLOYEE="INSERT INTO employee(empName,empTel,salary) VALUES (?,?,?)";
    
    public List<Employee> getAllEmployees();
    public Employee findEmployeeByID(int id);
    public boolean createNewEmployee(Employee empl);
}

Lets see the implementation classes for IEmployeeDAO and IProjectDAO. 
When it comes to the implementation of above interfaces, we can use capabilities of the spring framework. In this case I have injected configured datasource into my DAO Implementations using Spring framework dependency injection support. Then I have used Spring JDBC template provided by the Spring framework in my data access logic.
ProjectDAOImpl.java


package dao;

import dao.mappers.ProjectMapper;
import java.util.List;
import javax.sql.DataSource;
import model.Employee;
import model.Project;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Kanishka
 */
@Repository
public class ProjectDAOImpl implements IProjectDAO{
    private JdbcTemplate jdbcTemplate;

    @Autowired( required=true)
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    
    public List<Project> getProjectsOfEmployee(Employee emp) {
        System.out.println("Executing : " + QUERY_GET_PROJECTS_OF_EMPLOYEE);
        
        List<Project> projectList=
                this.jdbcTemplate.query(QUERY_GET_PROJECTS_OF_EMPLOYEE,
                new ProjectMapper(),emp.getEmpID());
        
        return projectList;
    }

    public boolean createNewProject(Project proj) {
        System.out.println("Executing : " + QUERY_CREATE_NEW_PROJECT);
        
        int affectedRows=jdbcTemplate.update(QUERY_CREATE_NEW_PROJECT, 
                proj.getProjName(),proj.getProjCode(),proj.getProjDuration() );
        
        return (affectedRows == 1);
    }
    
}
EmployeeDAOImpl.java
package dao;

import dao.mappers.EmployeeMapper;
import java.util.List;
import javax.sql.DataSource;
import model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Kanishka
 */
@Repository
// Indicates that an annotated class is a "Repository" (or "DAO"). 
public class EmployeeDAOImpl implements IEmployeeDAO{
    private JdbcTemplate jdbcTemplate;

    @Autowired(required=true)
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    
    public List<employee> getAllEmployees() {
        System.out.println("Executing : " + QUERY_GET_ALL_EMPLOYEES);
        
        List<employee> employees=
                this.jdbcTemplate.query(QUERY_GET_ALL_EMPLOYEES,
                new EmployeeMapper());
        
        return employees;
    }

    public Employee findEmployeeByID(int id) {
        System.out.println("Executing : " + QUERY_FIND_EMPLOYEE_BY_ID);
        
        Employee employee=new Employee();
        try{
            employee=
                    this.jdbcTemplate.queryForObject(QUERY_FIND_EMPLOYEE_BY_ID,
                    new EmployeeMapper(),id);
            
        }catch(DataAccessException de){
            System.err.println(de.getMessage());
        }
        return employee;
    }

    public boolean createNewEmployee(Employee empl) {
        System.out.println("Executing : " + QUERY_ADD_NEW_EMPLOYEE);
        
        int affectedrows=
                this.jdbcTemplate.update(QUERY_ADD_NEW_EMPLOYEE,
                empl.getEmpName(),empl.getEmpTel(),empl.getSalary());
        
        return (affectedrows == 1);
    }
    
}
Mapping between model classes and relational data is achieved by implementing the RowMapper<T> interface. As our scenario has 2 POJO classes, we have to implement 2 RowMappers for each class type.
  • RowMapper<Employee>
EmployeeMapper.java


package dao.mappers;

import java.sql.ResultSet;
import java.sql.SQLException;
import model.Employee;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author Kanishka
 */
public final class EmployeeMapper implements RowMapper<Employee> {

    /*implement abstract method for declaring mapping
     *between POJO attributes and relational table attributes
     */
    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
        Employee employee=new Employee();
        employee.setEmpID(rs.getInt("empID"));
        employee.setEmpName(rs.getString("empName"));
        employee.setEmpTel(rs.getString("empTel"));
        employee.setSalary(rs.getFloat("salary"));
        return employee;
    } 
}
  • RowMapper<Project>
 ProjectMapper.java
package dao.mappers;

import java.sql.ResultSet;
import java.sql.SQLException;
import model.Project;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author Kanishka
 */
public class ProjectMapper implements RowMapper<Project>{

     /*implement abstract method for declaring mapping
     *between POJO attributes and relational table attributes
     */
    public Project mapRow(ResultSet rs, int rowNum) throws SQLException {
        Project proj=new Project();
        proj.setProjCode(rs.getString("projCode"));
        proj.setProjDuration(rs.getInt("projDuration"));
        proj.setProjID(rs.getInt("projID"));
        proj.setProjName(rs.getString("projName"));
        return proj;
    }
}
RowMapper<T> implementation will be reused in each iteration if our query returns a list of results. 

Now it is time to have a look at our spring configuration.
Beans.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd">
    
    <bean id="employeeDAOImpl" class="dao.EmployeeDAOImpl">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    <bean id="projectDAOImpl" class="dao.ProjectDAOImpl">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
    </bean>
</beans>

DBCP  data source is configured and referred as a property of both DAO implementations. We can replace Apache commons DBCP with any other compatible data source easily with this configuration. JDBC template is constructed when it injects the configured data source into our DAO implementation.

Finally it is time to invoke our DAO layer :) Actually we don't need to wait for the implementation of the DAO layer. If we have 2 development teams where one team is working on BLL (Business logic layer) and other team is working on DAO (Data access object layer) it is possible for those teams to work in parallel.

App.java
package com.kani.jdbctemplatedemo;

import dao.EmployeeDAOImpl;
import dao.ProjectDAOImpl;
import java.util.List;
import model.Employee;
import model.Project;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * @author Kanishka
 */
public class App 
{
    public static void main( String[] args )
    {
        ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
        
        ProjectDAOImpl proDaoImplBean = context.getBean("projectDAOImpl", ProjectDAOImpl.class);
        EmployeeDAOImpl emplDaoImplBean = context.getBean("employeeDAOImpl",EmployeeDAOImpl.class);
        
        //Operation 1 : creating a new roject
        Project proj=new Project();
        proj.setProjCode("P11");
        proj.setProjDuration(23);
        proj.setProjName("MACYS");
        System.out.println(proDaoImplBean.createNewProject(proj));
        
        //operation 2 : create new employee
        Employee newEmpl=new Employee();
        newEmpl.setEmpName("Madhuranga Sampath");
        newEmpl.setEmpTel("0773284329");
        newEmpl.setSalary(2000);
        System.out.println(emplDaoImplBean.createNewEmployee(newEmpl));
        
        //operation 3 : get employee by id
        Employee empl=emplDaoImplBean.findEmployeeByID(2);

        //operation 4 : load employee's projects
        List<Project> projs=proDaoImplBean.getProjectsOfEmployee(empl);
        empl.setProjects(projs);
        System.out.println(empl);
        
        //operation 5 : get all employees
        List<Employee> employees=emplDaoImplBean.getAllEmployees();
        for (Employee employee : employees) {
            System.out.println(employee);
        }
    }
}
Output
Executing : INSERT INTO project(projName,projCode,projDuration) VALUES (?,?,?)
true
Executing : INSERT INTO employee(empName,empTel,salary) VALUES (?,?,?)
true
Executing : SELECT empID,empName,empTel,salary FROM employee e WHERE e.empID=?
Executing : SELECT  proj.projID AS `projID`,  proj.projName AS `projName`,  proj.projCode AS `projCode`,  proj.projDuration AS `projDuration`,  ehp.Employee_empID AS `empID` FROM  employee_has_project ehp  INNER JOIN project proj ON ehp.Project_projID = proj.projID WHERE  ehp.Employee_empID = ?
Employee{empID=2, empName=Sajith Athukorala, empTel=0777345678, salary=2000.0, projects=[Project{projID=2, projName=Proj2, projCode=PD452, projDuration=12, employeeList=[]}]}
Executing : SELECT empID,empName,empTel,salary FROM employee
Employee{empID=1, empName=Kanishka Dilshan, empTel=0777123123, salary=1000.0, projects=[]}
Employee{empID=2, empName=Sajith Athukorala, empTel=0777345678, salary=2000.0, projects=[]}
Employee{empID=3, empName=Ishan Primal, empTel=07123588, salary=1500.0, projects=[]}
Employee{empID=4, empName=Madhuranga Sampath, empTel=0773284329, salary=2000.0, projects=[]}

The demo project can be downloaded from the following location.
Thank you for your time!

References : 
Springsource Documentation
http://static.springsource.org/spring/docs/3.1.x/spring-framework-reference/html/jdbc.html 
Kaushik's youtube channel
http://www.youtube.com/playlist?list=PL1A506B159E5BD13E

Friday, January 11, 2013

Playnig With HTTP Methods

I am highly interested in RESTful web services these days :) . So I tried to implement my own RESTful API and wrote REST client to communicate with the service. GET, POST, PUT, DELETE are basic HTTP methods used in REST services (details of HTTP methods can be found here) . Lets see how I implemented basic CRUD operations using HTTP methods.

For the sake of simplicity I have simply echoed the request body and selected request headers back as the service implementation.

Service implementation in PHP (MyService.php)

<?php 
/**
 * Sample REST controller
 * 
 */
$method = $_SERVER['REQUEST_METHOD'];
$request = explode("/", substr(@$_SERVER['PATH_INFO'], 1));

echo "Request Method : ".$method;
echo "\n";

if(strlen($request[0])>0){
    
    if(strcmp($method, "GET")==0){
        printHeaders();
        print_r($_GET);
        
    }else if(strcmp($method, "POST")==0){
        printHeaders();
        print_r($_POST);
        
    }else if(strcmp($method, "PUT")==0){
        printHeaders();
        parse_str(file_get_contents("php://input"),$post_vars);
        print_r($post_vars);
    }
    
}else{
    echo "no resources found!";
}

function printHeaders(){
        $headers = apache_request_headers();
        echo "Authorization : ".$headers['Authorization'];
        echo "\n";
        echo "MyOwnHeader : ".$headers['MyOwnHeader'];
}
?>

REST client code for invoking REST service with a POST request.

public void postRequest() throws MalformedURLException, IOException{
 URL url=new URL("http://kanishka-d/rest/MyService.php/myresource");
 HttpURLConnection httpCon=(HttpURLConnection) url.openConnection();
 httpCon.setRequestMethod("POST");
 //set request headers
 httpCon.setRequestProperty("Authorization", "Client-ID 1133");
 httpCon.setRequestProperty("MyOwnHeader", "Hello Sri Lanka!");

 httpCon.setDoOutput(true);
 
 String urlParameters = "PARAM1=" + URLEncoder.encode("Param val 1", "UTF-8")+"&";
 urlParameters+="PARAM2="+URLEncoder.encode("Param val 2", "UTF-8");
 //Send request
 DataOutputStream wr = new DataOutputStream(
   httpCon.getOutputStream());
 wr.writeBytes(urlParameters);
 wr.flush();
 wr.close();
 
 //Get service Response 
 InputStream is = httpCon.getInputStream();
 BufferedReader rd = new BufferedReader(new InputStreamReader(is));
 String line;
 StringBuilder response = new StringBuilder();
 while ((line = rd.readLine()) != null) {
  response.append(line);
  response.append('\r');
 }
 rd.close();
 wr.close();
 httpCon.disconnect();
 
 System.out.println(response.toString());
}

Output:
Request Method : POST
Authorization : Client-ID 1133
MyOwnHeader : Hello Sri Lanka!
Array
(
    [PARAM1] => Param val 1
    [PARAM2] => Param val 2
)

Making PUT request is simple as just changing request method.


httpCon.setRequestMethod("POST");


client side implementation for DELETE and GET methods is different. Because DELETE and GET doesn't expect HTTP request body.

implementing GET request is slightly different from the implementation of the POST/PUT request. Main difference is embedding data in URL itself. and no data in request body.(see below code).

public void getRequest() throws MalformedURLException, IOException{
    String url="http://kanishka-d/rest/MyService.php/myresource?";
    url+="PARAM1=" + URLEncoder.encode("Param val 1", "UTF-8")+"&";
    url+="PARAM2="+URLEncoder.encode("Param val 2", "UTF-8");
    URL urlObj=new URL(url);
    HttpURLConnection httpCon=(HttpURLConnection) urlObj.openConnection();
    httpCon.setRequestMethod("GET");
    //set request headers
    httpCon.setRequestProperty("Authorization", "Client-ID 1133");
    httpCon.setRequestProperty("MyOwnHeader", "Hello Sri Lanka!");

    httpCon.setDoOutput(false);

    //Get service Response 
    InputStream is = httpCon.getInputStream();
    BufferedReader rd = new BufferedReader(new InputStreamReader(is));
    String line;
    StringBuilder response = new StringBuilder();
    while ((line = rd.readLine()) != null) {
        response.append(line);
        response.append('\r');
    }
    rd.close();
    httpCon.disconnect();

    System.out.println(response.toString());
}
Output :
Request Method : GET
Authorization : Client-ID 1133
MyOwnHeader : Hello Sri Lanka!
Array
(
    [PARAM1] => Param val 1
    [PARAM2] => Param val 2
)

Implementation of the DELETE client request is almost same as GET.

Please note that I have used Java for the client side. I have used HttpURLConnection . We can do same with the apache http client with less lines of code.

We can implement a REST API and Client with tease building blocks easily.

References :
Web API Design (Apigee)

Monday, December 17, 2012

Using SQLite with JDBC

I had to implement an application which should utilize a portable database. For better portability the application was implemented in java. As my first choice I selected several flat files for storing data in a hierarchical manner .

At the run time of the application , read operations were much higher than the write operations. It took minutes for traversing and search through each file on a search operation.

Considering all above facts I selected "SQLite" for storing and managing data in my application. Though it was bit slow while feeding data to the application , it was pretty fast when reading data from the database.

Xerial SQLite JDBC driver is a good solution as a JDBC driver for SQLite. Lets see some examples on how to use above JDBC driver in java applications.

Creating Database File :
in order to create the DB file we can either use a tool like SQLiteAdministrator or write code(execute DDL code using JDBC) to create tables and other SQL objects inside the DB. In most of the cases we can use the first approach.
I have used SQLiteAdministrator for creating database file. For the sake of simplicity I have created a table with 2 columns.

CREATE TABLE [student] (
[name] VARCHAR(50)  NULL,
[description] TEXT  NULL
)


Creating a JDBC connection to the SQLite database : 
download your preferred  SQLite JDBC driver version from here. Add it to the build path of your java application.

Connection Manager(ConnectionMgr.java)
import java.sql.Connection;
import java.sql.DriverManager;


public class ConnectionMgr {
 
 public static Connection getConnection(){
     Connection connection = null;
  try {
   Class.forName("org.sqlite.JDBC");
   connection = DriverManager.getConnection("jdbc:sqlite:C:/Users/kanishkad/Desktop/sample.s3db");
  } catch (Exception e) {

  }
  return connection;
 }
}

It accepts absolute path as well as the relative path. in above code sample I have added the absolute path for the DB file.

The rest part is nothing other than using JDBC API.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;

import org.sqlite.SQLiteJDBCLoader;


public class MainDemo {

 /**
  * @param args
  * @throws SQLException 
  */
 public static void main(String[] args) throws SQLException {
  addData();
  selectData();
 }
 
 public static void selectData() throws SQLException{
  long t1=System.currentTimeMillis();
  Connection conn=ConnectionMgr.getConnection();
  String query="SELECT * FROM student WHERE description LIKE \'%app%'";
  Statement prep=conn.createStatement();
  ResultSet rs=prep.executeQuery(query);
  while(rs.next()){
   String name=rs.getString(1);
   String desc=rs.getString(2);
   System.out.println(name+"\t"+desc);
  }
  conn.close();
  long t2=System.currentTimeMillis();
  System.out.println("Elapsed time : " + (t2-t1)/1000.0);
 }
 
 public static void addData() throws SQLException{
  long t1=System.currentTimeMillis();
  Connection conn=ConnectionMgr.getConnection();
  String query="INSERT INTO STUDENT(name,description) VALUES(?,?)";
  PreparedStatement prep=conn.prepareStatement(query);
  for (int j = 0; j < 1000; j++) {
   prep.setString(1,getRandomString(50));
   prep.setString(2,getRandomString(500));
   prep.addBatch();
   System.out.println(j);
  }
  prep.executeBatch();
  conn.close();
  long t2=System.currentTimeMillis();
  System.out.println("Elapsed time : " + (t2-t1)/1000.0);
 }
 
 public static String getRandomString(int size){
  StringBuilder stb=new StringBuilder();
  String allowedCahrs="QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm1234567890";
  Random rand=new Random();
  for(int i=0;i<size;i++){
   int randIdx=rand.nextInt(allowedCahrs.length());
   stb.append(allowedCahrs.charAt(randIdx));
  }
  return stb.toString();
 }

}


Hope you may understand the JDBC part.

Thursday, December 13, 2012

Monitoring JDBC Performance with JDbMonitor

There may be some situations where we need to monitor our JDBC calls. There are few mechanisms available for monitoring JDBC calls. I have tried JDbMonitor in some of my projects because it is easy to use and remove without any code change.

JDbMonitor works as a proxy between our application the JDBC driver. Essentially there may be some performance issue if we use this in a production environment. JDbMonitor provides a GUI application to monitor our JDBC calls.
In the current version(1.1) it supports
  • Statemt
  • PreparedStatement
but callable and batch operations are not supported yet.

Lets have a look on how to configure JDbMonitor to monitor our JDBC calls in step by step.
Step 1 : Add (jdbmonitor-driver.jar,jdbmonitor-common.jar and the JDBC driver) to your class path.
In my case I am using MySQL driver.


Step 2 : Point JDBC Driver to com.jdbmonitor.MonitorDriver where the connection is created.

package com.kani;

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

public class ConnectionManager {
 
 private static final String HOST="localhost";
 private static final String DBNAME="test_db";
 private static final String USERNAME="root";
 private static final String PASSWORD="";
 

    public static Connection getNewDBConnection() {
        Connection conn = null;
        try {
            Class.forName("com.jdbmonitor.MonitorDriver");
            conn = DriverManager.getConnection("jdbc:mysql://"+HOST+"/"+DBNAME, USERNAME, PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}

Step 3 : Run jdbmonitor client program. and connect(f2)
it should show all the supported JDBC operations if it is connected successfully.

Note : If you are using a property file you can remove JDBmonitor without any code change. And it is possible to monitor underlying Hibernate JDBC operations easily.

Tuesday, May 29, 2012

XML Serialization With XStream

XML Serialization is essential in some situations. Most probably we may use XML serialization when we need higher level of portability. XStream is a great library for xml serialization.
Serializing

import com.thoughtworks.xstream.XStream;

import model.TestCase;
import model.TestStep;
import model.TestSuite;


public class XStreamSerializeDemo {

 /**
  * @param args
  */
 public static void main(String[] args) {
  TestStep tstp1=new TestStep();
  tstp1.setId(1);
  tstp1.setName("Enter unamex on username");
  TestStep tstp2=new TestStep();
  tstp2.setId(1);
  tstp2.setName("Enter passwd on password");
  TestStep tstp3=new TestStep();
  tstp3.setId(1);
  tstp3.setName("Click on login button");
  
  TestCase tc=new TestCase();
  tc.setName("User Login");
  tc.getTestSteps().add(tstp1);
  tc.getTestSteps().add(tstp2);
  tc.getTestSteps().add(tstp3);
  
  TestCase tc1=new TestCase();
  tc1.setName("Fill dummy Vals");
  tc1.getTestSteps().add(tstp1);
  tc1.getTestSteps().add(tstp2);
  
  TestSuite ts=new TestSuite();
  ts.setName("Test Suite 1");
  ts.getTestCases().add(tc);
  ts.getTestCases().add(tc1);
  
  //initializing the serializer
  XStream xstream=new XStream();
  
  //mapping part *not essential
  xstream.alias("testcase", TestCase.class);
  xstream.alias("teststep", TestStep.class);
  xstream.alias("testsuite", TestSuite.class);
  
  String xml=xstream.toXML(ts);
  
  System.out.println(xml);
 }

} 
 
Deserializing
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;

import model.TestCase;
import model.TestStep;
import model.TestSuite;

import com.thoughtworks.xstream.XStream;


public class XStreamDeserializeDemo {

 /**
  * @param args
  * @throws FileNotFoundException 
  */
 public static void main(String[] args) throws FileNotFoundException {
  XStream xstream=new XStream();
  //mapping part *not essential
  xstream.alias("testcase", TestCase.class);
  xstream.alias("teststep", TestStep.class);
  xstream.alias("testsuite", TestSuite.class);
  
  //TestSuite ts=(TestSuite) xstream.fromXML("xml string goes here");
  FileInputStream fin=new  FileInputStream(new File("C:\\Users\\kanishkad\\Desktop\\output.xml"));
  TestSuite ts=new TestSuite();
  ts=(TestSuite) xstream.fromXML(fin,ts);
  
  System.out.println(ts.getTestCases().get(0).getTestSteps().get(0).getName());
 }

}

Serialized object :

<testsuite>
  <name>Test Suite 1</name>
  <testCases>
    <testcase>
      <name>User Login</name>
      <testSteps>
        <teststep>
          <name>Enter unamex on username</name>
          <id>1</id>
        </teststep>
        <teststep>
          <name>Enter passwd on password</name>
          <id>1</id>
        </teststep>
        <teststep>
          <name>Click on login button</name>
          <id>1</id>
        </teststep>
      </testSteps>
    </testcase>
    <testcase>
      <name>Fill dummy Vals</name>
      <testSteps>
        <teststep reference="../../../testcase/testSteps/teststep"/>
        <teststep reference="../../../testcase/testSteps/teststep[2]"/>
      </testSteps>
    </testcase>
  </testCases>
</testsuite>

Monday, April 23, 2012

Ajax with Dojo

It is very safe to use Dojo like java script frameworks for Ajax operations since they ensure browser compatibility. We don't have to change the code based on different browsers. dojo will handle those stuff for us. Any way , If we are using such java script frameworks for Ajax operations  we must carefully choose a proper framework with wide range of browser support.

In this example I have used a very simple Servlet and a simple HTML page to demonstrate the client server model.

Friday, April 20, 2012

Browser Automation With Selenium

Selenium is a browser automation framework. it is available in many flavors such as Java , .net , python . I am interested in Java implementation of Selenium.  Selenium can be successfully used in regression automation. It provides all the necessary functionalists required to automate the browser. Such as navigating, verifying, drag and drop,  clicking , selecting..etc.

Selenium can be downloaded from here
Getting started guide


Following program will demonstrate some of the basic functionalists of the selenium.
import java.io.File;
import java.io.IOException;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.openqa.selenium.By;
import org.openqa.selenium.OutputType;
import org.openqa.selenium.TakesScreenshot;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.firefox.FirefoxProfile;



public class SeleniumBasics {

  /**
   * @param args
   * @throws IOException 
   */
  public static void main(String[] args) throws IOException {
    //get configured webdriver
    WebDriver driver=getWebdriver();
    
    //goto google.lk
    driver.get("http://www.google.lk");
    WebElement searchBox=driver.findElement(By.xpath("//input[@type='text' and @name='q']"));
    //enter some text in the search textbox
    searchBox.sendKeys("Namo Buddhaya!!!");
    
    //cick on search button
    WebElement searchButton=driver.findElement(By.xpath("//button[@aria-label='Google Search' and @name='btnG']"));
    searchButton.click();
    
    //waiting until results div is loading successfully or 5sec is over
    long endTime=System.currentTimeMillis()+5000;
    while(System.currentTimeMillis()<endTime){
      WebElement resultsDiv=driver.findElement(By.xpath("//div[@id='ires']"));
      if(resultsDiv.isDisplayed()){
        break;
      }
    }
    
    //list down the search results
    List<WebElement> searchResults=driver.findElements(By.xpath("//a[@class='l']"));
    for(WebElement w : searchResults){
      System.out.println(w.getText());
    }

    //saving a screenshot
    File scrFile = ((TakesScreenshot)driver).getScreenshotAs(OutputType.FILE);
    FileUtils.copyFile(scrFile, new File("c:\\tmp\\screenshot.png"));
    
    System.out.println("-==DONE==-");
  }
  
  //creates the default driver
  public static WebDriver getWebdriver() {
    WebDriver driver = null;
    try {
      File firebug = new File("C:\\FFPlugins\\firebug-1.7.3.xpi");
      File xpathChecker = new File("C:\\FFPlugins\\xpath_checker-0.4.4-fx.xpi");
      FirefoxProfile profile = new FirefoxProfile();
      profile.setAcceptUntrustedCertificates(true);
      profile.setAssumeUntrustedCertificateIssuer(true);
      profile.addExtension(firebug);  //installing pligins
      profile.addExtension(xpathChecker);
      profile.setPreference("extensions.firebug.currentVersion", "1.7.3");
      profile.setPreference("extensions.xpath_checker.currentVersion", "0.4.4");
      driver=new FirefoxDriver(profile);
    } catch(IOException e) {
      e.printStackTrace();
    }
    return driver;
  }
}
 
© Copyright 2035 kani.stack.notez
Theme by Yusuf Fikri