JUnit test for DB connection using ReflectionTestUtils with PreparedStatement

Here’s a JUnit test case demonstrating how to test a database connection using a PreparedStatement and SELECT * FROM tableName with ReflectionTestUtils. This test will mock Connection, PreparedStatement, and ResultSet objects to verify the execution flow.

Steps Covered:

Mock JDBC Components: Connection, PreparedStatement, and ResultSet. and DataSource
Use ReflectionTestUtils: Inject the mocked connection into the target class.
Execute Query: Simulate fetching data from a table.
Verify Data Retrieval: Ensure data is correctly fetched and held.

JUnit Test Implementation

java

import static org.mockito.Mockito.*;

import static org.junit.jupiter.api.Assertions.*;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import org.junit.jupiter.api.BeforeEach;

import org.junit.jupiter.api.Test;

import org.junit.jupiter.api.extension.ExtendWith;

import org.mockito.InjectMocks;

import org.mockito.Mock;

import org.mockito.junit.jupiter.MockitoExtension;

import org.springframework.test.util.ReflectionTestUtils;

@ExtendWith(MockitoExtension.class)

class DatabaseServiceTest {

@Mock

private Connection mockConnection;

@Mock

private PreparedStatement mockPreparedStatement;

@Mock

private ResultSet mockResultSet;

@InjectMocks

private DatabaseService databaseService; // Target class

@BeforeEach

void setUp() throws SQLException {

// Injecting the mocked connection into the service class

ReflectionTestUtils.setField(databaseService, "connection", mockConnection);

when(mockConnection.prepareStatement("SELECT * FROM StaffEmployees"))

.thenReturn(mockPreparedStatement);

when(mockPreparedStatement.executeQuery()).thenReturn(mockResultSet);

}

@Test

void testFetchAllEmployees() throws SQLException {

// Mock result set behavior

when(mockResultSet.next()).thenReturn(true, true, false); // Two rows

when(mockResultSet.getInt("id")).thenReturn(1, 2);

when(mockResultSet.getString("name")).thenReturn("KCM", "Kartik");

when(mockResultSet.getString("department")).thenReturn("Dev", "IT");

// Invoke method

List<Employee> employees = databaseService.fetchAllEmployees();

// Assertions

assertNotNull(employees);

assertEquals(2, employees.size());

assertEquals(1, employees.get(0).getId());

assertEquals("KCM", employees.get(0).getName());

assertEquals("Dev", employees.get(0).getDepartment());

assertEquals(2, employees.get(1).getId());

assertEquals("Kartik", employees.get(1).getName());

assertEquals("IT", employees.get(1).getDepartment());

// Verify method calls

verify(mockPreparedStatement, times(1)).executeQuery();

verify(mockResultSet, times(3)).next(); // Two records + 1 false

}

Best Way (Recommended - using constructor)

Here’s how to write your test without needing ReflectionTestUtils:

java

@ExtendWith(MockitoExtension.class)

public class DatabaseServiceTest {

@Mock

private Connection mockConnection;

@Mock

private PreparedStatement mockPreparedStatement;

@Mock

private ResultSet mockResultSet;

private DatabaseService databaseService;

@BeforeEach

void setup() throws SQLException {

// Pass mocked connection to service via constructor

databaseService = new DatabaseService(mockConnection);

when(mockConnection.prepareStatement("SELECT * FROM employees")).thenReturn(mockPreparedStatement);

when(mockPreparedStatement.executeQuery()).thenReturn(mockResultSet);

}

@Test

void testFetchAllEmployees() throws SQLException {

when(mockResultSet.next()).thenReturn(true, true, false); // simulate 2 rows

when(mockResultSet.getInt("id")).thenReturn(1, 2);

when(mockResultSet.getString("name")).thenReturn("Alice", "Bob");

when(mockResultSet.getString("department")).thenReturn("HR", "IT");

List<Employee> employees = databaseService.fetchAllEmployees();

assertEquals(2, employees.size());

assertEquals("Alice", employees.get(0).getName());

assertEquals("Bob", employees.get(1).getName());

}

❓ But If You Didn't Have the Constructor

If your DatabaseService had no constructor and the connection field was private and final, you'd do this:

java

DatabaseService databaseService = new DatabaseService(); // default constructor

// inject connection using ReflectionTestUtils

ReflectionTestUtils.setField(databaseService, "connection", mockConnection);

But since your class has a constructor, just use it. No need for reflection or DataSource.

JUnit Test Implementation another one approach

java

import static org.mockito.Mockito.*;

import static org.junit.jupiter.api.Assertions.*;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import org.junit.jupiter.api.BeforeEach;

import org.junit.jupiter.api.Test;

import org.junit.jupiter.api.extension.ExtendWith;

import org.mockito.InjectMocks;

import org.mockito.Mock;

import org.mockito.junit.jupiter.MockitoExtension;

import org.springframework.test.util.ReflectionTestUtils;

@ExtendWith(MockitoExtension.class)

class DatabaseServiceTest {

@Test

void testFetchAllEmployees() throws SQLException {

DatabaseService databaseService = new DatabaseService();

//Arrange

ResultSet resultSet = mock(ResultSet.class);

//when(resultSet.getString((String) any())).thenReturn(“String”); //default value

when(resultSet.getInt("id")).thenReturn(1, 2);

when(resultSet.getString("name")).thenReturn("KCM", "Kartik");

when(resultSet.getString("department")).thenReturn("Dev", "IT");

// Mock result set behavior

when(resultSet.next()).thenReturn(true). thenReturn(true). thenReturn(false)// Two rows

doNothing().when(resultSet).close();

PreparedStatement preparedStatement = mock(PreparedStatement.class);

when(preparedStatement.executeQuery()).thenReturn(resultSet);

doNothing().when(preparedStatement).close();

Connection connection = mock(Connection.class);

String query= “select * from staffEmployee”;

when(connection.prepareStatement(query)).thenReturn(preparedStatement);

doNothing().when(connection).close();

DatsSosurce datsSosurce = mock(DatsSosurce.class);

when(datsSosurce.getConnection()).thenReturn(connection);

databaseService.setDataSource(datsSosurce);

//set data

//ReflectionTestUtils.setField(databaseService, “variableName”,”variableValue”);

ReflectionTestUtils.setField(databaseService, "connection", connection);

// Invoke method

List<Employee> employees = ReflectionTestUtils.invokeMethod(databaseService, ”fetchAllEmployees”);

// Assertions

assertNotNull(employees);

assertEquals(2, employees.size());

assertEquals(1, employees.get(0).getId());

assertEquals("KCM", employees.get(0).getName());

assertEquals("Dev", employees.get(0).getDepartment());

assertEquals(2, employees.get(1).getId());

assertEquals("Kartik", employees.get(1).getName());

assertEquals("IT", employees.get(1).getDepartment());

}

Service Class (DatabaseService)

java

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

public class DatabaseService {

private Connection connection; // Injected using ReflectionTestUtils

public List<Employee> fetchAllEmployees() throws SQLException {

List<Employee> employees = new ArrayList<>();

String query = "SELECT * FROM employees";

try (PreparedStatement preparedStatement = connection.prepareStatement(query);

ResultSet resultSet = preparedStatement.executeQuery()) {

while (resultSet.next()) {

Employee emp = new Employee();

emp.setId(resultSet.getInt("id"));

emp.setName(resultSet.getString("name"));

emp.setDepartment(resultSet.getString("department"));

employees.add(emp);

}

return employees;

}

Employee Model (Employee.java)

java

public class Employee {

private int id;

private String name;

private String department;

// Getters and Setters

public int getId() { return id; }

public void setId(int id) { this.id = id; }

public String getName() { return name; }

public void setName(String name) { this.name = name; }

public String getDepartment() { return department; }

public void setDepartment(String department) { this.department = department; }

}

Pom.xml

<junit.version>4.12</junit.vsersion>

<spring.version>5.3.25</spring.vsersion>

<mockito.version>4.12</mockito.vsersion>

<mockito-inline.version>4.12</mockito-inline.version>

</properties>

<build>

<groupId>org.apache.maven.plugins</groupId>

<artificateId>maven-depnedency-plugin</artificateId>

<id>unpack-COVID19-rh7</id>

<phase>prepare-package</phase>

<goals>

<goal>unpack-dependencies</goal>

</goals>

<groupId>com.kcm.quotation</groupId>

<artificateId>Covid19_rh7_64_release-gcc11.2.1</artificateId>

<includeGroupIds>com.kcm.quotation</includeGroupIds>

<includeArtificateIds>Covid19_rh7_64_release-gcc11.2.1</includeArtificateIds>

<outputDiretcory>${project.build.diretcory}/lib_COVID19/linux</outputDiretcory>

</configuration>

</execution>

<id>unpack-COVID19-win64</id>

<phase>prepare-package</phase>

<goals>

<goal>unpack-dependencies</goal>

</goals>

<groupId>com.kcm.quotation</groupId>

<artificateId>Covid19_rh7_64_release-gcc11.2.1</artificateId>

<includeGroupIds>com.kcm.quotation</includeGroupIds>

<includeArtificateIds>Covid19_win_x64_release-vs222</includeArtificateIds>

<outputDiretcory>${project.build.diretcory}/lib_COVID19/windows</outputDiretcory>

</configuration>

</execution>

<id>copy-dependencies</id>

<phase>prepare-package</phase>

<goals>

<goal>copy-dependencies</goal>

</goals>

<outputDiretcory>${project.build.diretcory}/lib</outputDiretcory>

</configuration>

</execution>

</executions>

</plugin>

<groupId>org.apache.maven.plugins</groupId>

<artificateId>maven-antrun-plugin</artificateId>

<id>copy-COVID19</id>

<phase>prepare-package</phase>

<goals>

</goals>

</fileset>

</copy>

</fileset>

</copy>

</target>

</configuration>

</execution>

</executions>

</plugin>

<groupId>org.apache.maven.plugins</groupId>

<artificateId>maven-assembly-plugin</artificateId>

<id>KcmInterface</id>

<phase>package</phase>

<goals>

<goal>single</goal>

</goals>

<outputDiretcory>${project.build.diretcory}/assemblies</outputDiretcory>

<appendAssemblyId>false</appendAssemblyId>

<descriptor> ${project.basedir}KcmInterface.xml</descriptor>

</descriptors>

<fileName>KcmInterface</fileName>

</configuration>

</execution>

</executions>

</plugin>

</build>

<groupId>junit</groupId>

<artificateId>junit</artificateId>

<version>${junit.version}</version>

</dependency>

<groupId>org.mockito</groupId>

<artificateId>mockito-core</artificateId>

<version>${mockito.version}</version>

</dependency>

<groupId>org.assertj</groupId>

<artificateId>assertj-core</artificateId>

<version>${assertj.version}</version>

</dependency>

<groupId>org.mockito</groupId>

<artificateId>mockito-inline</artificateId>

<version>${mockito-inline.version}</version>

</dependency>

<groupId>org.springframework</groupId>

<artificateId>spring-test</artificateId>

<version>${spring.version}</version>

</dependency>

</dependencies>

Key Features of this Test

· Uses Mockito or power Mockito to mock database dependencies (Connection, PreparedStatement, ResultSet, DataSource).

· injects Connection into the service class using ReflectionTestUtils.

· Mocks ResultSet iteration to simulate retrieving multiple rows.

· Verifies query execution and ensures data is correctly fetched.

JUnit test for DB connection using ReflectionTestUtils

For Chemistry information, visit:

Ø Molecular weight of chemistry in Java code

Ø To generate a chemical formula look using HTML

Ø Orbitals and Electron Configuration Hund’s Rule

For Cloud information, visit:

Ø creating a hierarchical diagram for cloud logging

Ø A hierarchical structure that includes a broader range of google cloud services

For Other information, visit

Ø String to xml or html Beautifier

Ø How to convert XML to Object and Object to XML

Ø Convert Floating-Point Values from SQL Server to Oracle in Java

Header Ads Widget

JUnit test for DB connection using ReflectionTestUtils with PreparedStatement

Post a Comment

0 Comments

Popular Posts

TLS 1.3 Configuration

Spring Cloud Gateway uses a RewritePath filter

Find Itinerary from a given list of tickets

Labels

Tags

Popular Posts

TLS 1.3 Configuration

Spring Cloud Gateway uses a RewritePath filter

Java 8 support static method or default method or both in the interface

Contact Form

Wikipedia

Categories

Menu Footer Widget