JUnit – Sample Maven Project With JDBC

The quality of software should be of a high standard and then only it is liked and used by most people. A good programmer is one who prepares software error-free, JUnit helps us a lot in achieving that. In this article, let us see a MySQL database connecting with Java via JDBC, and the data is checked against JUnit.

Example Maven Project

Project Structure:

Maven Project and hence let us look at the dependencies specified in

pom.xml

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>javajdbcsample</groupId>
  <artifactId>javajdbcsample</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

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

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

  <dependencies>
    <!-- MySQL usage dependency -->
      <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
    </dependency>
    <!-- JUNIT dependency -->
   <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
  </dependencies>
</project>

MySQL connectivity is specified in

ds-connection.properties

# DataSource 
ds.database-driver=com.mysql.jdbc.Driver
ds.url=jdbc:mysql://localhost:3306/geeksforgeeks
ds.username=*** #Specify username
ds.password=*** #Specify password

Let us assume that there exists a database named “geeksforgeeks ” and it has a table named “worker” the structure of “worker” table is given below

worker table db data:

Here we can get the connection

ConnectionClass.java

Java

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class ConnectionClass {

    public Connection getFileFromResources()
    {

        Properties prop = new Properties();
        try {

            prop.load(getClass().getResourceAsStream("/resources/ds-connection.properties"));

            String dname = (String)prop.get("ds.database-driver");

            String dbConnUrl = (String)prop.get("ds.url");
            String dbUserName = (String)prop.get("ds.username");
            String dbPassword = (String)prop.get("ds.password");

            Class.forName(dname);
            Connection dbConn = DriverManager.getConnection(dbConnUrl,
                                                            dbUserName, dbPassword);

            if (dbConn != null) {
                System.out.println("Connection Successful");
            }
            else {
                System.out.println("Failed to make connection!");
            }
            return dbConn;
        }
        catch (IOException e) {

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

            e.printStackTrace();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void close(Connection conn)
    {
        if (conn != null) {
            try {
                conn.close();
            }
            catch (SQLException e) {
                System.out.println("SQL Exception in close connection method");
            }
        }
    }

    public static void close(Statement stmt)
    {
        if (stmt != null) {
            try {
                stmt.close();
            }
            catch (SQLException e) {
                System.out.println("SQL Exception in close statement method");
            }
        }
    }

    public static void close(ResultSet rSet)
    {
        if (rSet != null) {
            try {
                rSet.close();
            }
            catch (SQLException e) {
                System.out.println("SQL Exception in close resultset method");
            }
        }
    }
}

Let us create the Worker class now

Worker.java

Java

public class Worker {
    // Instance variables datatype should
    // match with "worker" table datatype
    private int workerId;
    private String workerName;
    private float salary;
    private int additionalBenefits;
    private String contractPeriod;
    private int pricePerHour;
    private String type;

    // Getter and setter methods
    public int getWorkerId()
    {
        return workerId;
    }
    public void setWorkerId(int workerId)
    {
        this.workerId = workerId;
    }
    public String getWorkerName()
    {
        return workerName;
    }
    public void setWorkerName(String workerName)
    {
        this.workerName = workerName;
    }
    public float getSalary()
    {
        return salary;
    }
    public void setSalary(float salary)
    {
        this.salary = salary;
    }
    public int getAdditionalBenefits()
    {
        return additionalBenefits;
    }
    public void setAdditionalBenefits(int additionalBenefits)
    {
        this.additionalBenefits = additionalBenefits;
    }
    public String getContractPeriod()
    {
        return contractPeriod;
    }
    public void setContractPeriod(String contractPeriod)
    {
        this.contractPeriod = contractPeriod;
    }
    public int getPricePerHour()
    {
        return pricePerHour;
    }
    public void setPricePerHour(int pricePerHour)
    {
        this.pricePerHour = pricePerHour;
    }
    public String getType()
    {
        return type;
    }
    public void setType(String type)
    {
        this.type = type;
    }
}

WorkerQueries.java

Java

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class WorkerQueries {
    // We can write our required business logic methods here
    public List<Worker> listWorkerDetails()
    {
        ConnectionClass connClass = new ConnectionClass();
        Connection con = connClass.getFileFromResources();
        List<Worker> workerList = new ArrayList<Worker>();
        Worker worker = null;
        ResultSet resultSet = null;
        Statement statement = null;
        String query = "select * from worker";
        try {
            statement = con.createStatement();
            resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                worker = new Worker();
                worker.setWorkerId(resultSet.getInt(1));
                worker.setWorkerName(resultSet.getString(2));
                worker.setSalary(resultSet.getFloat(3));
                worker.setAdditionalBenefits(resultSet.getInt(4));

                // We can get by columnaname as well
                worker.setPricePerHour(resultSet.getInt("pricePerHour"));
                worker.setContractPeriod(resultSet.getString("contractPeriod"));
                worker.setType(resultSet.getString("type"));
                // System.out.println(rs.getInt(1) + " " + rs.getString(2));
                workerList.add(worker);
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            connClass.close(con);
            connClass.close(statement);
            connClass.close(resultSet);
        }
        return workerList;
    }

    public String getWorkerNameById(int workerId)
    {
        ConnectionClass connClass = new ConnectionClass();
        Connection con = connClass.getFileFromResources();
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        String workerName = null;
        try {
            String query = "select * from worker where workerId=?";
            pStmt = con.prepareStatement(query);
            pStmt.setInt(1, workerId);
            rs = pStmt.executeQuery();

            while (rs.next()) {
                workerName = rs.getString("workerName");
                System.out.println(rs.getString(2));
            }
        }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally {
            connClass.close(con);
            connClass.close(pStmt);
            connClass.close(rs);
        }
        return workerName;
    }

    public int getWorkerPricePerHour(int workerId)
    {
        ConnectionClass connClass = new ConnectionClass();
        Connection con = connClass.getFileFromResources();
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        int pricePerHour = 0;
        try {
            String query = "select * from worker where workerId=?";
            pStmt = con.prepareStatement(query);
            pStmt.setInt(1, workerId);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                pricePerHour = rs.getInt("pricePerHour");
                System.out.println(pricePerHour);
            }
        }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally {
            connClass.close(con);
            connClass.close(pStmt);
            connClass.close(rs);
        }
        return pricePerHour;
    }
}

This java program helps us to test the available DB data is helpful for subsequent operations.

1. It will help to set the integrity of the project
2. By testing in these ways, we can find wrongly entered data and avoid it.
3. Quality of software is improved and customizing them helps to prepare error-free software.

WorkerApplicationTest.java

Java

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotEquals;
import java.util.List;
import org.junit.Test;
import com.javajdbc.sample.Worker;
import com.javajdbc.sample.WorkerQueries;

public class WorkerApplicationTest {
    WorkerQueries workerQueriesObject = new WorkerQueries();
    @Test
    public void listWorkerDetails_positive()
    {
        List<Worker> listWorkerDetails = workerQueriesObject.listWorkerDetails();

        // Checking whether totally 3 workers are available
        assertEquals(listWorkerDetails.size(), 3);

        // Checking whether first worker id is 1
        assertEquals(listWorkerDetails.get(0).getWorkerId(), 1);

        // Checking whether first worker name is Rachel Green
        assertEquals(listWorkerDetails.get(0).getWorkerName(), "Rachel Green");

        // Checking whether second worker name is Rachel Green
        assertEquals(listWorkerDetails.get(1).getWorkerName(), "Ross");

        // Checking whether first  worker priceperhour is 20
        assertEquals(listWorkerDetails.get(0).getPricePerHour(), 1000);

        // Checking whether second  worker price per hour  is 2000
        assertEquals(listWorkerDetails.get(1).getPricePerHour(), 1000);

        // checking whether third worker contract period is 25 hours
        assertEquals(listWorkerDetails.get(2).getContractPeriod(), "25 hours");

        // checking whether third worker type is contractworker
        assertEquals(listWorkerDetails.get(2).getType(), "contractworker");
    }

    @Test
    public void listworkerDetails_negative()
    {
        List<Worker> listworkerDetails = workerQueriesObject.listWorkerDetails();
        // As this is negative testing we need to check with assertNotEquals
        assertNotEquals(listworkerDetails.size(), 11);

        // Checking whether first worker id is not 10
        assertNotEquals(listworkerDetails.get(0).getWorkerId(), 10);

        // Checking whether first worker name is not Monica
        assertNotEquals(listworkerDetails.get(0).getWorkerName(), "Monica");

        // Checking whether second  worker salary is not 30000
        assertNotEquals(listworkerDetails.get(1).getSalary(), 30000);

        // Checking whether second  worker price per hour  is not 400
        assertNotEquals(listworkerDetails.get(1).getPricePerHour(), 400);

        // Checking whether third  worker is not a regular worker
        assertNotEquals(listworkerDetails.get(2).getType(), "regularworker");
    }

    @Test
    public void getworkerName_PositiveTestCase()
    {
        String workerName = workerQueriesObject.getWorkerNameById(1);
        assertEquals(workerName, "Rachel Green");
        workerName = workerQueriesObject.getWorkerNameById(2);
        assertEquals(workerName, "Ross");
        workerName = workerQueriesObject.getWorkerNameById(3);
        assertEquals(workerName, "Joey Tribbiani");
    }

    @Test
    public void getdworkerName_NegativeTestCase()
    {
        String workerName = workerQueriesObject.getWorkerNameById(2);
        assertNotEquals(workerName, "Phoebe");
    }

    @Test
    public void getFirstworkerPricePerHour_PositiveTestCase()
    {
        int pricePerHour = workerQueriesObject.getWorkerPricePerHour(1);
        assertEquals(pricePerHour, 1000);
    }

    @Test
    public void getSecondworkerPricePerHour_NegativeTestCase()
    {
        int pricePerHour = workerQueriesObject.getWorkerPricePerHour(2);
        assertNotEquals(pricePerHour, 5000);
    }
}

This file can be run as a JUnit Test 

When expected values and actual DB data values match for all test cases, we can see like below output

In case of any errors, they can be shown as follows