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]]

Advertisement

Penggunaan Serializable di Java

Kegunaan Serializable

Jika anda menggunakan Java untuk membuat proyek web application, pasti anda akan menjumpai interface Serializable. Serializable digunakan agar object yang dibuat dari class yang implement interface tersebut dapat diubah menjadi byte stream. Tujuan utamanya adalah agar object tersebut dapat disimpan menjadi file atau dikirim melalui network.

Object pada java disimpan dalam heap dalam memory. Jika aplikasinya mati, maka OS akan mengalokasikan ulang memory dan konsekuensinya object akan hilang. Agar object dapat diambil dari heap untuk disimpan ke dalam harddisk atau dikirim melalui network, maka object tersebut harus diubah menjadi byte stream dulu melalui proses Serialization.

Contohnya pada penggunaan HTTP request berikut:

public static void main(String[] args) {

    HttpClient client = new DefaultHttpClient();
    HttpPost post = new HttpPost(“https://www.example.com&#8221;);

    try {
        List<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>(1);
        nameValuePairs.add(new BasicNameValuePair(“Email”, “youremail”));
        post.setEntity(new UrlEncodedFormEntity(nameValuePairs));
        HttpResponse response = client.execute(post);
        BufferedReader rd = new BufferedReader(new InputStreamReader(
                response.getEntity().getContent()));
        String line = "";
        while ((line = rd.readLine()) != null) {
            System.out.println(line);
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Pada code snippet di atas, agar object nameValuePair dapat diambil dari heap space dan bisa dikirim sebagai byte melalui internet, maka harus implement Serializable. Jika merujuk pada javadoc, maka benar saja, class tersebut implements Serializable.

serialVersionUID

Jika class anda implement Serializable, maka anda harus menetapkan serialVersionUID pada class tersebut. Biasanya oleh IDE, misalnya Eclipse anda punya pilihan untuk generate default (1L) atau generated berdasarkan variable dalam class anda.

Kegunaan serialVersionUID ini adalah menentukan apakah byte stream atau file hasil yang berhasil di-serialize dapat di-deserialize atau diubah kembali menjadi object dengan serialVersionUID yang sama.

Jadi, misalnya hari ini dengan serialVersionUID = 1L, kemudian melakukan serialisasi object menjadi satu file.

class Employee {
    private static final long serialVersionUID = 1L;
    String name;
    String email;
}

Besoknya, kita menambahkan class variable yang baru, yaitu mobileNo, maka jika kita tidak mengubah serialVersionUID, file tersebut dapat diubah ulang menjadi object. Walaupun mobileNo tetap null. Wajar, karena memang di awal variable tersebut tidak ada.

class Employee {
    private static final long serialVersionUID = 1L;
    String name;
    String email;
    String mobileNo;
}

Namun, ada kalanya kita tidak mau jika file lama bisa diubah menjadi object lagi. Mungkin dengan adanya penambahan variable baru bisa mengakibatkan business logic yang berbeda. Maka kita tinggal mengubah serialVersionUID, misalnya menjadi serialVersionUID = 2L.

Dengan mengubah serialVersionUID, maka jika program mencoba untuk mengubah file menjadi object (deserialisasi), maka program akan melempar exception, yaitu : InvalidClassException.

Demikian penggunaan Serializable di Java. Jika ada pertanyaan, bisa disampaikan melalui comments.