In this lab we will use GitHub issues to start tracking our work. This is stage one of starting a Kanban system to monitor our work. We will start by defining our Vision for the application, using this to define features, and from these User Stories.
After this lab you will be able to:
- Create issues on GitHub.
- Define a basic user story.
- Create a composed Docker service using Docker Compose.
- Use an SQL database from Java.
Here is a cut-down Vision Statement for the product we will develop in the labs:
The purpose of the application is to manage employee records for a company. The system will primarily used by the Human Resources Department (HR) and managers. An existing SQL database exists with this data.
The application will provide a front-end to the employee database with the ability to add, delete, and update records. This will allow the HR team to be more efficient in processing employee data, and provide managers with information about current salary and role quickly for appraisal meetings.
The users are mainly HR advisors who have limited technical knowledge and require reports to be produced and employee records updated. The HR advisors work via web interfaces mainly, although a desktop application linked to a server is a possible work-around.
The application will produce a report on the current salary of all employees (HR), employees in a department (HR and department manager), and employees by job title (HR). The current salary reports are used in financial reporting and planning.
The application will allow HR advisors to add a new employee when they start. This is to ensure new employees are paid.
The application will allow an HR advisor of department manager to view the record for an employee in a department. The view is used during any promotion request.
The application will allow an HR advisor to update an employee record. This is to ensure records are kept up-to-date.
The application will allow an HR advisor to delete an employee record. This is to ensure the organisation does not retain date it no longer needs.
You need to set-up your application as last week. Check that everything works after you've pulled down your code.
Our job is to define the initial requirements for the system to be developed. We will do this by specifying User Stories (see Lecture 10 for more details). A user story has the following form:
As a role I want feature so that value.
From the vision statement, we can define the following initial user stories:
- As an HR advisor I want to produce a report on the salary of all employees so that I can support financial reporting of the organisation.
- As an HR advisor I want to produce a report on the salary of employees in a department so that I can support financial reporting of the organisation.
- As an department manager I want to produce a report on the salary of employees in my department so that I can support financial reporting for my department.
- As an HR advisor I want to produce a report on the salary of employees of a given role so that I can support financial reporting of the organisation.
- As an HR advisor I want to add a new employee's details so that I can ensure the new employee is paid.
- As an HR advisor I want to view and employee's details so that the employee's promotion request can be supported.
- As an HR advisor I want to update an employee's details so that employee's details are kept up-to-date.
- As an HR advisor I want to delete an employee's details so that the company is compliant with data retention legislation.
From an initial pass we have eight features to implement. We will break down these tasks further as required.
We are going to use GitHub issues to track our User Stories. From the main GitHub page for your repository you will see Issues on the toolbar:
Select the Issues tab. This will open the Issues window:
Click the New Issue button. We can now add a new issue. We will add the first User Story above:
Click Submit New Issue. Your issue will be added to GitHub.
Add the other seven User Stories as GitHub issues.
We will now work on a User Story. The simplest one is:
- As an HR advisor I want to view and employee's details so that the employee's promotion request can be supported.
First, create a new branch from develop called feature/view-record. This will be our work branch to add this feature.
The User Story contains a number of tasks we need to extract:
- Connect to the existing database.
- Add function to extract employee record based on ID.
- Display the employee record.
For Task 1 we have to have a database of employee data. We will use the MySQL Employee Sample Database provided on GitHub (instructions here). To use this, we will have to switch our database container to MySQL, upload the sample data, and integrate with our existing application. To make this easier, we will use Docker Compose.
Docker Compose allows us to define a collection of containers that operate together. We are going to have two containers: a MySQL container and an Application container. First, we need to define our database in our application.
A Docker container is available for MySQL. That is the easy part. What we need to do is add an existing database to the container. We can do this in a Dockerfile if we have the database on the local file system. We can get the database from GitHub, and we can incorporate an external Git submodule easily. Let us do that first.
A Git Submodule is when we have other Git repositories linked to our main one. This is useful when we want to include external libraries in our build pipeline. Git submodules are not included in the tracking of the local repository, so do not add anything to your main repository. Unfortunately, IntelliJ is not very good at managing submodules, so we will have to do this in the terminal. IntelliJ does provide a terminal - there is a tab at the bottom:
From the terminal we need to execute the following two commands:
git submodule add https://github.com/datacharmer/test_db db/test_db
git submodule init
git submodule updateThis will create a .gitmodules file to the root of the project folder. The contents of this file are:
[submodule "db/test_db"]
path = db/test_db
url = https://github.com/datacharmer/test_db
This will add the files from example SQL database to the folder db/test_db once we initialise the Git submodule.
Git will pull the repository. You should now have the db/test_db folder.
Next we need a Dockerfile to run a MySQL database instance with the given files. The following Dockerfile should be stored in the db folder:
# Use the latest MySQL image
FROM mysql
# Set the working directory
WORKDIR /tmp
# Copy all the files to the working directory of the container
COPY test_db/*.sql /tmp/
COPY test_db/*.dump /tmp/
# Copy the main SQL file to docker-entrypoint-initdb.d.
# Scripts and SQL files in this folder are executed on container startup.
# This is specific to MySQL.
COPY test_db/employees.sql /docker-entrypoint-initdb.d
# Set the root password
ENV MYSQL_ROOT_PASSWORD exampleThe comments explain what the Dockerfile does.
We are now in a position to test that our database works. To do this, just click the green play button in the database Dockerfile and select Run on Docker. Look at the Log (give it some time to get finished), and you should see:
...
mysql: [Warning] Using a password on the command line interface can be insecure.
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salariesNow you should commit this change and push it to the remote. We have completed part of the new feature add.
Now we can create a Docker Compose file. This is a configuration file that has more than one service defined. We want to have our application and our database services up and running. Therefore, our initial file (saved in docker-compose.yml in the root) is as follows:
version: '3'
services:
# Application Dockerfile is in same folder which is .
app:
build: .
# db is is db folder
db:
build: db/.
command: --default-authentication-plugin=mysql_native_password
restart: alwaysWhen running Docker from the command line, we use docker-compose up to build and run a composed service. IntelliJ understands Docker compose files, so we don't have to worry. We will modify our GitHub Actions file.
First, we need to update the pom.xml file to add MySQL support. Open the file in IntelliJ and replace the mongo dependency that you added last week with the following:
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
</dependencies>Now we need to update our main application to move from MongoDB to MySQL. A MySQL server takes a bit more time to start-up, so we need to have code to attempt to connect multiple times. The Java code below is our new application.
package com.napier.sem;
import java.sql.*;
public class App
{
public static void main(String[] args)
{
try
{
// Load Database driver
Class.forName("com.mysql.cj.jdbc.Driver");
}
catch (ClassNotFoundException e)
{
System.out.println("Could not load SQL driver");
System.exit(-1);
}
// Connection to the database
Connection con = null;
int retries = 100;
for (int i = 0; i < retries; ++i)
{
System.out.println("Connecting to database...");
try
{
// Wait a bit for db to start
Thread.sleep(30000);
// Connect to database
con = DriverManager.getConnection("jdbc:mysql://db:3306/employees?useSSL=false", "root", "example");
System.out.println("Successfully connected");
// Wait a bit
Thread.sleep(10000);
// Exit for loop
break;
}
catch (SQLException sqle)
{
System.out.println("Failed to connect to database attempt " + Integer.toString(i));
System.out.println(sqle.getMessage());
}
catch (InterruptedException ie)
{
System.out.println("Thread interrupted? Should not happen.");
}
}
if (con != null)
{
try
{
// Close connection
con.close();
}
catch (Exception e)
{
System.out.println("Error closing connection to database");
}
}
}
}Now we can test the application and MySQL database together by undertaking the following steps:
- Compile the application.
- Package the application.
- Deploy the composed Docker services - we do this by running the
docker-compose.ymlfile as any other Dockerfile. - Wait for the application to start-up.
- Check that "Successfully Connected" is displayed.
Now we can update our Actions file to use the Docker compose file. This is below:
name: A workflow for my Hello World App
on: push
jobs:
build:
name: Hello world action
runs-on: ubuntu-20.04
steps:
- name: Checkout
uses: actions/checkout@v2
with:
submodules: recursive
- name: Set up JDK 11
uses: actions/setup-java@v2
with:
java-version: '11'
distribution: 'adopt'
- name: Build with Maven
run: mvn package
- name: Run docker compose
run: docker-compose up --abort-on-container-exit
The --abort-on-container-exit parameter tells Docker to stop all services once one container has finished. This will gracefully exit the MySQL container when the main application exits in GitHub Actions.
With our files updated we can test that our CI build still works. To do this, follow the following two steps:
- Commit the file changes.
- Push the changes to your GitHub repository.
And that is it.
Finally look at the logs on GitHub Actions and check that the build is successful. Hopefully you will get something as follows:
Expand the Section Named Run docker compose and scroll to the end and you should see a successful connection. (below on line 201)
Our next step is to extract an employee's information from the SQL database. Looking at the information about the database we can determine an initial SQL query as:
SELECT emp_no, first_name, last_name
FROM employees
WHERE emp_no = <ID>Hopefully you remember these basic ideas from database systems. Constructing this query string is easy. First we will extract our current application behaviour to make our good easier to work with.
At the moment we have two pieces of behaviour: connecting to the database and disconnecting from the database. We can separate these behaviours into two methods: connect and disconnect:
/**
* Connection to MySQL database.
*/
private Connection con = null;
/**
* Connect to the MySQL database.
*/
public void connect()
{
try
{
// Load Database driver
Class.forName("com.mysql.cj.jdbc.Driver");
}
catch (ClassNotFoundException e)
{
System.out.println("Could not load SQL driver");
System.exit(-1);
}
int retries = 10;
for (int i = 0; i < retries; ++i)
{
System.out.println("Connecting to database...");
try
{
// Wait a bit for db to start
Thread.sleep(30000);
// Connect to database
con = DriverManager.getConnection("jdbc:mysql://db:3306/employees?useSSL=false", "root", "example");
System.out.println("Successfully connected");
break;
}
catch (SQLException sqle)
{
System.out.println("Failed to connect to database attempt " + Integer.toString(i));
System.out.println(sqle.getMessage());
}
catch (InterruptedException ie)
{
System.out.println("Thread interrupted? Should not happen.");
}
}
}
/**
* Disconnect from the MySQL database.
*/
public void disconnect()
{
if (con != null)
{
try
{
// Close connection
con.close();
}
catch (Exception e)
{
System.out.println("Error closing connection to database");
}
}
}As we update our application you will see why this change is useful. Next we will test the new version of the application:
The updated main method is as follows:
public static void main(String[] args)
{
// Create new Application
App a = new App();
// Connect to database
a.connect();
// Disconnect from database
a.disconnect();
}We now create an App object and call connect and disconnect on the object. This is fundamentally the same as the previous application version. Run it to test.
Even this small change is a commit point through our history. Create a new commit with these changes and push it to GitHub.
We are now ready to add a new method to extract the employee information. To make life easier, we will create an Employee class. To do this, perform the following steps:
- Right-click on the com.sem.napier package in the Project explorer in IntelliJ.
- Select New, Java Class to open the Create New Class window.
- Call the class Employee and click OK.
We are now ready to add the Employee class.
The Employee class is just data. An example code listing is given below, and should be straightforward to understand.
package com.napier.sem;
/**
* Represents an employee
*/
public class Employee
{
/**
* Employee number
*/
public int emp_no;
/**
* Employee's first name
*/
public String first_name;
/**
* Employee's last name
*/
public String last_name;
/**
* Employee's job title
*/
public String title;
/**
* Employee's salary
*/
public int salary;
/**
* Employee's current department
*/
public String dept_name;
/**
* Employee's manager
*/
public String manager;
}We can now extract employee information from the database. Remember our SQL statement:
SELECT emp_no, first_name, last_name
FROM employees
WHERE emp_no = <ID>The method will therefore:
- Return an Employee (or
null). - Requires an ID to lookup (and
int).
Working with SQL in Java requires a bit of knowledge. A more comprehensive tutorial covers the details. Here we will cover the following points:
- Create a SQL statement - a
Statementobject from the database connection. - Define the SQL query string to execute.
- Execute a query (
executeQuery) to extract data from the database. This will return aResultSetobject. - Test that the
ResultSethas a value - callnexton theResultSetand check this istrue. - Extract the information from the current record in the
ResultSetusinggetIntfor integer data,getStringfor string data, etc.
The code for getEmployee is below. Read the description above to understand the lines of code provided.
public Employee getEmployee(int ID)
{
try
{
// Create an SQL statement
Statement stmt = con.createStatement();
// Create string for SQL statement
String strSelect =
"SELECT emp_no, first_name, last_name "
+ "FROM employees "
+ "WHERE emp_no = " + ID;
// Execute SQL statement
ResultSet rset = stmt.executeQuery(strSelect);
// Return new employee if valid.
// Check one is returned
if (rset.next())
{
Employee emp = new Employee();
emp.emp_no = rset.getInt("emp_no");
emp.first_name = rset.getString("first_name");
emp.last_name = rset.getString("last_name");
return emp;
}
else
return null;
}
catch (Exception e)
{
System.out.println(e.getMessage());
System.out.println("Failed to get employee details");
return null;
}
}Now run the application and hopefully you will get no errors.
Once again, it is time to commit your updates. Do so now.
We cannot really test our get employee functionality until we display the output. At the moment, we will just display to the console. The displayEmployee method for our App is below:
public void displayEmployee(Employee emp)
{
if (emp != null)
{
System.out.println(
emp.emp_no + " "
+ emp.first_name + " "
+ emp.last_name + "\n"
+ emp.title + "\n"
+ "Salary:" + emp.salary + "\n"
+ emp.dept_name + "\n"
+ "Manager: " + emp.manager + "\n");
}
}And we can now update our main to test the application:
public static void main(String[] args)
{
// Create new Application
App a = new App();
// Connect to database
a.connect();
// Get Employee
Employee emp = a.getEmployee(255530);
// Display results
a.displayEmployee(emp);
// Disconnect from database
a.disconnect();
}And running this version of the application will give us:
Connecting to database...
Successfully connected
255530 Ronghao Garigliano
null
Salary:0
null
Manager: nullOK, if this didn't work, try the following first:
- Make sure you have performed the following steps via Maven: Compile and Package.
- Stop all the running containers, delete them, and delete the current
sem_dbandsem_appDocker images. Then rebuild everything and restart. - Make sure the SQL connection string is correct and the logs from the running database and application.
If this doesn't solve the problem them ask for help.
Complete the SQL query so the employee's current (most recent) job title, salary, department, and manager is displayed. The SQL schema diagram is available here for reference. The database uses an end-date of 9999-01-01 to represent the current entry.
We can now close our issue on GitHub. Go to GitHub, open the issues, and select the view record issue. At the bottom of the page you will find the Close Issue button
Click the button to close the issue. And we are done.
We are now ready to merge everything together. Remember what we did last week:
- Updated the version number in Maven. Remember to update the copied JAR file name in the Dockerfile as well.
- Merged our feature branch into
develop. - Merged
developintorelease. - Created a release - including version tag.
- Merged
releaseintomaster. - Merged
releaseintodevelop. - Clean up.
Our current process has not changed from last week, except we are now using GitHub issues to drive our work. Therefore we have:
- Select an issue to work on.
- Pull the latest
developbranch. - Start a new feature branch for the issue.
- Once feature is finished, create JAR file.
- Update and test Docker configuration with GitHub Actions.
- Update feature branch with
developto ensure feature is up-to-date. - Check feature branch still works.
- Merge feature branch into
develop. - Repeat 2-7 until release is ready.
- Merge
developbranch intoreleaseand create release. - Merge
releaseintomasteranddevelop. - Close the issue.
Follow the SQL and Java tutorial to explore this topic further. You will find it useful.
The following supplementary notes are provided to allow quicker debugging and testing of SQL queries when using docker databases.
So far we have used docker-compose to call two Dockerfile configuration scripts that create and run two Docker images that are connected on the same network (this is automatic using docker-compose)
Our docker-compose file shown below creates and runs 2 images.
Lines 4-5 create an image called app (if it does not exist) using the Dockerfile in the root directory of our project (signified by a "." at the end of line 5 below)
Lines 8-11 create an image called db using the Dockerfile in the directory name db/.
1 version: '3'
2 services:
3 # Application Dockerfile is in same folder which is .
4 app:
5 build: .
6
7 # db is is db folder
8 db:
9 build: db/.
10 command: --default-authentication-plugin=mysql_native_password
11 restart: alwaysBy running the Docker images using docker-compose the two containers can communicate on a shared network (something we did manually last week for a mongo database)
However, we cannot see the MySQL database outside of the internal docker network.
To expose the database to the local machine we can set up port forwarding by adding the last two lines of the following to our docker.compose.yml file.
1 version: '3'
2 services:
3 # Application Dockerfile is in same folder which is .
4 app:
5 build: .
6
7 # db is is db folder
8 db:
9 build: db/.
10 command: --default-authentication-plugin=mysql_native_password
11 restart: always
12 ports:
13 - "33060:3306"This tells docker to forward requests from our local machine on port 33060 to port 3306 inside the docker container.
We can now connect locally without having to do so from another docker container.
IntelliJ allows database queries to be executed using a plugin named Database Navigator. To install this plugin select File->Settings then Plugins From the marketplace tab search for and install the plugin.
After doing this you should see a database tab on the right of IntelliJ where you can set up a new MySQL Connection. The fields should all be filled automatically with the exception of the password which is example
This will allow you to test SQL queries before coding them in Java and testing in a docker container.