Junit test case for database insert method with DA

Question

I am implementing a webservice based university management system. This system adds certain courses to database. here below is the code that I am using.

Course.java

public class Course {

    private String courseName;
    private String location;
    private String courseId;


       public String getCourseId()
               {
        return courseId;
            }

    public void setCourseId(String courseId) {
        this.courseId = courseId;
    }

    public String getCourseName() {
        return courseName;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }

        public String getLocation() {
        return location;
    }
    public void setLocation(String location) {
        this.location = location;
    }
}

then another file is as below

CourseDaoImpl.java

public class CourseDaoImpl implements IDao {
   Connection conn = null;
   Statement stmt = null;

public CourseDaoImpl(){

try {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    conn = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/univesitydb", "root", "root");
    stmt = conn.createStatement();

    if (!conn.isClosed())
        System.out.println("Successfully connectiod");
} catch (SQLException e) {
    e.printStackTrace();
} catch (InstantiationException e) {
    e.printStackTrace();
} catch (IllegalAccessException e) {
    e.printStackTrace();
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}
}

      @Override
  public String add(Object object) {

Course c = (Course) object ;

String courseId = c.getCourseId();
String courseName = c.getCourseName();
String location = c.getLocation();

String result = "";
int rowcount;

try {
    String query = "Insert into course (courseId,courseName,location) values"
            + " ('"
            + courseId
            + "', '"
            + courseName
            + "', '"
            + location
            + "')";
    rowcount = stmt.executeUpdate(query);
    if (rowcount > 0) {
        result = "true";
        System.out.println("Course inserted successful");
    } else {
        result = "false:The data could not be inserted in the databse";
    }
} catch (SQLException e) {
    e.printStackTrace();
}

return result;
}

the third is the Web service file as follows which interacts with the previous two and adds data to database.

CourseService.java

package edu.service;

          import edu.dao.IDao;
          import edu.dao.impl.CourseDaoImpl;
          import edu.db.entity.Course;

       public class CourseService {

     public String addCourse(String courseId, String courseName, String location)
    {   
       Course c = new Course();
       c.setCourseId(courseId);
       c.setCourseName(courseName);
       c.setLocation(location);     
       IDao dao = new CourseDaoImpl();
       return dao.add(c);   
     }

Looking at my code listings can any body suggest me how do I write test case for my add method. I am totally beginner for JAVA, I took help from my friends to learn this java part and Now need to implement Junit test for my database methods like add course above.

Please suggest some thing that I can learn , read and use to implement Junit testing for my database methods.

Answer 1

This is one sample dao test using junit in spring project.

import java.util.List;

import junit.framework.Assert;

import org.jboss.tools.example.springmvc.domain.Member;
import org.jboss.tools.example.springmvc.repo.MemberDao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.transaction.annotation.Transactional;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:test-context.xml",
"classpath:/META-INF/spring/applicationContext.xml"})
@Transactional
@TransactionConfiguration(defaultRollback=true)
public class MemberDaoTest
{
    @Autowired
    private MemberDao memberDao;

    @Test
    public void testFindById()
    {
        Member member = memberDao.findById(0l);

        Assert.assertEquals("John Smith", member.getName());
        Assert.assertEquals("[e-mail]", member.getEmail());
        Assert.assertEquals("2125551212", member.getPhoneNumber());
        return;
    }

    @Test
    public void testFindByEmail()
    {
        Member member = memberDao.findByEmail("[e-mail]");

        Assert.assertEquals("John Smith", member.getName());
        Assert.assertEquals("[e-mail]", member.getEmail());
        Assert.assertEquals("2125551212", member.getPhoneNumber());
        return;
    }

    @Test
    public void testRegister()
    {
        Member member = new Member();
        member.setEmail("[e-mail]");
        member.setName("Jane Doe");
        member.setPhoneNumber("2125552121");

        memberDao.register(member);
        Long id = member.getId();
        Assert.assertNotNull(id);

        Assert.assertEquals(2, memberDao.findAllOrderedByName().size());
        Member newMember = memberDao.findById(id);

        Assert.assertEquals("Jane Doe", newMember.getName());
        Assert.assertEquals("[e-mail]", newMember.getEmail());
        Assert.assertEquals("2125552121", newMember.getPhoneNumber());
        return;
    }

    @Test
    public void testFindAllOrderedByName()
    {
        Member member = new Member();
        member.setEmail("[e-mail]");
        member.setName("Jane Doe");
        member.setPhoneNumber("2125552121");
        memberDao.register(member);

        List<Member> members = memberDao.findAllOrderedByName();
        Assert.assertEquals(2, members.size());
        Member newMember = members.get(0);

        Assert.assertEquals("Jane Doe", newMember.getName());
        Assert.assertEquals("[e-mail]", newMember.getEmail());
        Assert.assertEquals("2125552121", newMember.getPhoneNumber());
        return;
    }
}

Answer 2

@Test
public void testSearchManagementStaff() throws SQLException
{
    boolean res=true;
    ManagementDaoImp mdi=new ManagementDaoImp();
    boolean b=mdi.searchManagementStaff("[e-mail]"," 123456");
    assertEquals(res,b);
}

Answer 3

The design of your classes will make it hard to test them. Using hardcoded connection strings or instantiating collaborators in your methods with new can be considered as test-antipatterns. Have a look at the DependencyInjection pattern. Frameworks like Spring might be of help here.

To have your DAO tested you need to have control over your database connection in your unit tests. So the first thing you would want to do is extract it out of your DAO into a class that you can either mock or point to a specific test database, which you can setup and inspect before and after your tests run.

A technical solution for testing db/DAO code might be dbunit. You can define your test data in a schema-less XML and let dbunit populate it in your test database. But you still have to wire everything up yourself. With Spring however you could use something like spring-test-dbunit which gives you lots of leverage and additional tooling.

As you call yourself a total beginner I suspect this is all very daunting. You should ask yourself if you really need to test your database code. If not you should at least refactor your code, so you can easily mock out all database access. For mocking in general, have a look at Mockito.

Answer 4

/*

public class UserDAO {

public boolean insertUser(UserBean u) {
    boolean flag = false;
    MySqlConnection msq = new MySqlConnection();
    try {

        String sql = "insert into regis values(?,?,?,?,?)";

        Connection connection = msq.getConnection();
        PreparedStatement statement = null;
        statement = (PreparedStatement) connection.prepareStatement(sql);
        statement.setString(1, u.getname());
        statement.setString(2, u.getlname());
        statement.setString(3, u.getemail());
        statement.setString(4, u.getusername());
        statement.setString(5, u.getpasswords());
        statement.executeUpdate();

        flag = true;
    } catch (Exception e) {
    } finally {
        return flag;
    }

}

public String userValidate(UserBean u) {
    String login = "";
    MySqlConnection msq = new MySqlConnection();
    try {
        String email = u.getemail();
        String Pass = u.getpasswords();

        String sql = "SELECT name FROM regis WHERE email=? and passwords=?";
        com.mysql.jdbc.Connection connection = msq.getConnection();
        com.mysql.jdbc.PreparedStatement statement = null;
        ResultSet rs = null;
        statement = (com.mysql.jdbc.PreparedStatement) connection.prepareStatement(sql);
        statement.setString(1, email);
        statement.setString(2, Pass);
        rs = statement.executeQuery();
        if (rs.next()) {
            login = rs.getString("name");
        } else {
            login = "false";
        }

    } catch (Exception e) {
    } finally {
        return login;
    }
}

public boolean getmessage(UserBean u) {
    boolean flag = false;
    MySqlConnection msq = new MySqlConnection();
    try {


        String sql = "insert into feedback values(?,?)";

        Connection connection = msq.getConnection();
        PreparedStatement statement = null;
        statement = (PreparedStatement) connection.prepareStatement(sql);
        statement.setString(1, u.getemail());
        statement.setString(2, u.getfeedback());
        statement.executeUpdate();

        flag = true;
    } catch (Exception e) {
    } finally {
        return flag;
    }

}

public boolean insertOrder(cartbean u) {
    boolean flag = false;
    MySqlConnection msq = new MySqlConnection();
    try {

        String sql = "insert into cart (product_id, email, Tprice, quantity) values (?,?,2000,?)";

        Connection connection = msq.getConnection();
        PreparedStatement statement = null;
        statement = (PreparedStatement) connection.prepareStatement(sql);
        statement.setString(1, u.getpid());
        statement.setString(2, u.getemail());
        statement.setString(3, u.getquantity());

        statement.executeUpdate();

        flag = true;
    } catch (Exception e) {
        System.out.print("hi");
    } finally {
        return flag;
    }

}

}