Simple CRUD with JDBC, Gradle and JUnit Testing

You can view and download the complete source code of this tutorial from my github account.

In this tutorial, we will create a simple CRUD User Management Console Application using Java, MySQL, JDBC. We will generate and build the project using Gradle and perform unit testing using Junit 5.

For this tutorial, we will need the following tools :

1. Eclipse (i use Spring Tool Suite v4.x)

2. XAMPP or MySQL server

3. Gradle (i use version 5.6.2)


First, lets make a new project called “simpleGradleJunit”.

$> mkdir simpleGradleJunit

$> cd simpleGradleJunit

$> gradle init

Starting a Gradle Daemon (subsequent builds will be faster)

Select type of project to generate:2
   1: basic
   2: application
   3: library
   4: Gradle plugin
 Select implementation language:3
   1: C++
   2: Groovy---> 0% EXECUTING [3s]
   3: Java
   4: Kotlin
   5: Swift
 Select build script DSL:1
   1: Groovy
   2: Kotlin---> 50% EXECUTING [1m 20s]
 Select test framework:4
   1: JUnit 4
   2: TestNG---> 50% EXECUTING [1m 27s]
   3: Spock
   4: JUnit Jupiter
 Project name (default: simpleGradleJunit):
 Source package (default: simpleGradleJunit): id.danielniko.simpleGradleJunit

> Task :init
 Get more help with your project: https://docs.gradle.org/5.6.2/userguide/tutorial_java_projects.html

BUILD SUCCESSFUL in 2m 28s
 2 actionable tasks: 2 executed

Import the resulted directory to your Eclipse workspace using Import –> Existing Gradle Project.

The resulted directory is as follows:

Lets make sure the Gradle generated class file, App and AppTest works well. Right click on the App.java file and select Run As –> Java Application. You will get “Hello World.” in the console window.

Now, run AppTest.java. Right click and select Run As –> JUnit Test. You will encounter the following error :

java.lang.NoClassDefFoundError: org/junit/platform/commons/PreconditionViolationException

If that is the case, edit build.gradle to the following

apply plugin: 'application'

mainClassName = 'id.danielniko.simpleGradleJunit.App'

repositories {
    jcenter()
}

dependencies {
    testImplementation 'org.junit.jupiter:junit-jupiter-api:5.6.0'
    testRuntimeOnly 'org.junit.jupiter:junit-jupiter-engine:5.6.0'
}

test {
    // Use junit platform for unit tests
    useJUnitPlatform()
}

Now run the AppTest again as JUnit Test. It will show green bar and no Failures.

This means our settings are complete. Now, its time to finally make our application.


Create our database “budget” and table “users” with the following sql. We also insert 1 row to test when our program can connect successfully.

drop database budget;
create database budget;
use budget;

CREATE TABLE `users` (
  `username` varchar(30) NOT NULL,
  `password` varchar(40) NOT NULL,
  `full_name` varchar(45) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`username`)
) 

Next, we will add MySQL connector to build.gradle. Dont forget to right click your project, go to Gradle –> Refresh Gradle Project when you edit build.gradle. This will trigger gradle to download the dependencies.

dependencies {
    implementation 'mysql:mysql-connector-java:8.0.21'
    testImplementation 'org.junit.jupiter:junit-jupiter-api:5.6.0'
    testRuntimeOnly 'org.junit.jupiter:junit-jupiter-engine:5.6.0'
}

Create a new class in id.danielniko.util package and name it DbUtil.java. This class handles the database connection to our MySQL server. In this class, we read a .properties file which contains the information necessary for the connection.

package id.danielniko.util;

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

public class DbUtil {
	
	private static Connection connection = null;
    private static Properties prop = null;

	public static String getProperty(String key) {
		if (prop != null) {
			return prop.getProperty(key);
			
		} else {
			Properties prop = new Properties();
            InputStream inputStream = DbUtil.class.getClassLoader().getResourceAsStream("database.properties");
            try {
				prop.load(inputStream);
				return prop.getProperty(key);
			} catch (IOException e) {
				e.printStackTrace();
				return null;
			}
		}
	}
	
    public static Connection getConnection() {
        if (connection != null)
            return connection;
        else {
            try {
                String driver = getProperty("driver");
                String url = getProperty("url");
                String user = getProperty("user");
                String password = getProperty("password");
                Class.forName(driver);
                connection = DriverManager.getConnection(url, user, password);
                connection.setAutoCommit(false);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } 
            return connection;
        }

    }

}

Create the properties file directly under the src/main/resources folder. Create a new file, name it database.properties. Put the following information inside.

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/budget
user=root
password=your_password

Now, lets test the .properties file read method and database connection class.

Create new class “DbUtilTest.java” under src/test/java with the same package as class to be tested, id.danielniko.util. In this unit test, we check whether .properties file can be read properly and database connection has been properly configured.

package id.danielniko.util;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;

import java.sql.Connection;

import org.junit.jupiter.api.Test;

class DbUtilTest {
	
	@Test 
    void getPropertyTest() {
    	String driver = DbUtil.getProperty("driver");
    	assertEquals("com.mysql.cj.jdbc.Driver", driver, "Db driver should match");
    }

    @Test 
    void getConnectionTest() {
    	Connection dbConnection = DbUtil.getConnection();
        assertNotNull(dbConnection, "connection should be successfull.");

    }
}

Right click on the class and Run as JUnit test. Make sure that the test runs successfully.


Now we can concentrate on the business process itself.

Create new POJO (Plain Old Java Object) class named User.java under id.danielniko.model

package id.danielniko.model;

public class User {

	private String username;
	private String password;
	private String fullName;
	private String email;
	
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getFullName() {
		return fullName;
	}
	public void setFullName(String fullName) {
		this.fullName = fullName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	@Override
	public String toString() {
		return "User [username=" + username + ", password=" + password + ", fullName=" + fullName + ", email=" + email
				+ "]";
	}
	
}

Next, we can create DAO (Data Access Object) and name it UserDao.java inside package id.danielniko.dao

package id.danielniko.dao;

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;

import id.danielniko.model.User;
import id.danielniko.util.DbUtil;

public class UserDao {

    private Connection connection;

    public UserDao() {
        connection = DbUtil.getConnection();
    }
    
    public UserDao(Connection conn) {
        connection = conn;
    }

    public void addUser(User user) {
        try {
            PreparedStatement preparedStatement = connection
                    .prepareStatement("insert into users(username,password,full_name,email) values (?, ?, ?, ? )");
            // Parameters start with 1
            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getFullName());
            preparedStatement.setString(4, user.getEmail());
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } 
    }

    public void deleteUser(String username) {
        try {
            PreparedStatement preparedStatement = connection
                    .prepareStatement("delete from users where username=?");
            // Parameters start with 1
            preparedStatement.setString(1, username);
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateUser(User user) {
        try {
            PreparedStatement preparedStatement = connection
                    .prepareStatement("update users set password=?, full_name=?, email=?" +
                            "where username=?");
            // Parameters start with 1
            preparedStatement.setString(1, user.getPassword());
            preparedStatement.setString(2, user.getFullName());
            preparedStatement.setString(3, user.getEmail());
            preparedStatement.setString(4, user.getUsername());
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<User> getAllUsers() {
        List<User> users = new ArrayList<User>();
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("select * from users");
            while (rs.next()) {
                User user = new User();
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setFullName(rs.getString("full_name"));
                user.setEmail(rs.getString("email"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return users;
    }

    public User getUserByUsername(String username) {
        User user = new User();
        try {
            PreparedStatement preparedStatement = connection.
                    prepareStatement("select * from users where username=?");
            preparedStatement.setString(1, username);
            ResultSet rs = preparedStatement.executeQuery();

            if (rs.next()) {
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setFullName(rs.getString("full_name"));
                user.setEmail(rs.getString("email"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return user;
    }
}

As with previous classes, lets make unit test for this DAO class. We create UserDaoTest.java inside the id.danielniko.dao inside src/test/java. In this class, we use @BeforeAll to initialize before testing that the operation to DB is not auto committed because by default, whenever we call executeUpdate() it will be committed to the DB. We dont want that to happen on our testing.

In this case, the DB operation in test scenario will run in the actual DB but not committed so any changes will be discarded after the test finishes.

package id.danielniko.dao;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNull;

import java.sql.Connection;
import java.sql.SQLException;

import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

import id.danielniko.model.User;
import id.danielniko.util.DbUtil;

class UserDaoTest {
	
	private static UserDao dao;
	
	@BeforeAll
	static void init() {
		Connection conn = DbUtil.getConnection();
		try {
			// set auto commit false so any operation in this test will be discarded.
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		dao = new UserDao(conn);
	}
	
	@AfterAll
	static void teardown() {
		Connection conn = DbUtil.getConnection();
		try {
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	void addUserTest() {
		User user = new User();
		user.setUsername("steven");
		user.setPassword("mypass");
		user.setFullName("Steven Gerrard");
		user.setEmail("steven@example.com");
		dao.addUser(user);
		User userFromDb = dao.getUserByUsername("steven");
		assertEquals("mypass", userFromDb.getPassword(), "Password must be equals");
	}
	
	@Test
	void deleteUserTest() {
		dao.deleteUser("danielniko");
		User userFromDb = dao.getUserByUsername("danielniko");
		assertNull(userFromDb.getUsername(), "Username should be null");
	}
	
	@Test
	void updateUserTest() {
		User user = new User();
		user.setUsername("danielniko");
		user.setPassword("secret");
		user.setFullName("Daniel Niko");
		user.setEmail("danielniko@example.com");
		dao.addUser(user);
		user.setPassword("verysecret");
		dao.updateUser(user);
		User userFromDb = dao.getUserByUsername("danielniko");
		assertEquals("verysecret", userFromDb.getPassword(), "Updated password must be equal.");
	}

}

Once again, right click on the test class and run as JUnit. After all green then we can move on to App.class inside id.danielniko.simpleGradleJunit

package id.danielniko.simpleGradleJunit;

import id.danielniko.dao.UserDao;
import id.danielniko.model.User;

public class App {

    public static void main(String[] args) {
    	UserDao dao = new UserDao();
    	// Add new user
    	User user = new User();
    	user.setUsername("luiz");
    	user.setPassword("secret");
    	user.setFullName("Luiz Suarez");
    	user.setEmail("luiz@example.com");
    	// Update user
    	dao.addUser(user);
    	user.setPassword("verysecret");
    	dao.updateUser(user);
    	System.out.println(dao.getAllUsers());
    	
    }
    
}

Actually, not much is happening in this class since this is only use to show how to connect Java with MySQL with unit testing.

Now, we need to build our program using the following command. Go to project root directory and execute the following.

gradlew build

You will see that it will trigger all of our unit tests to be run also. If you successfully run unit test previously, then it should not be a problem.

Now, if you want to distribute our program as executables, you can run below command.

gradlew distZip

This command will trigger building a simpleGradleJunit.zip file inside the build\distribution folder. Unzip it and run the .bat file inside bin folder using double click or from command prompt. You will get the following :

[User [username=luiz, password=verysecret, fullName=Luiz Suarez, email=luiz@example.com], User [username=steven, password=mypass, fullName=Steven Gerrard, email=steven@example.com]]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: