Building a simple console app with a database integration in Java
Hello readers! Recently, as part of my time learning Java, I created a console application that can read user input, manipulate it and provide output as appropriate, and store (and read) the data in (and from) a connected database. I'll be taking a deep dive into the parts of the app I find the most interesting. You can follow along with the full source code here.
Design overview
I wrote the app using Java 21 in IntelliJ IDEA, an IDE that simplifies the programming experience, removing the need for writing too much repetetive, boilerplate code. You can download it here.
The database I chose to integrate with is SQLite, as the entire database can be stored and run from a single file, vastly simplifying the setup process. It also comes built-in in many operating systems, such as MacOS. My program interfaces with the database using the Data Access Object pattern[1] (DAO), which decouples code that uses data from the code that accesses and writes that data.
The program is a simple simulation of a 'Point of Sale' (PoS) or customer/stock management-type system, with functionality to view, update and add customers and products, and builds the structure to add more complex logic like creating orders.
Let's start by breaking up the classes and interfaces:
- Main: the entrypoint of the app
- PointOfSale: a 'holding' class for everything else
- Config: a singleton class to store the app config
- Customer (and CustomerDao): logic for customers
- Item (and ItemDao): logic for items
- Dao: an interface that the Dao classes implement
- Address: acts as a custom data structure for a customer's address
- Utils: static utility classes, such as the menu
- Constants: an enum to store application constants, such as the database path
PointOfSale.java
PointOfSale, once created by Main.java, provides the main menu loop and stores all the objects needed to provide app functionality. Let's take a look at the structure:
package me.nathangreen.digitalartefact;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import static me.nathangreen.digitalartefact.Utils.menu;
public class PointOfSale {
public Connection conn;
public Scanner sc;
public Config config;
public CustomerDao customerDao;
public ItemDao itemDao;
PointOfSale(String databasePath) {
try {
this.conn = DriverManager.getConnection(databasePath);
} catch (SQLException e) {
throw new RuntimeException(e);
}
this.sc = new Scanner(System.in);
this.config = makeConfig();
this.customerDao = new CustomerDao(conn);
this.itemDao = new ItemDao(conn);
}
// more methods...
}
The PointOfSale constructor method takes the path to the database (i.e., the .sqlite file) as a string, and creates the connection to the database (crashing the app if it does not exist), and then creates a new Scanner[2] (used to get user input), a config object, and then creates the DAOs needed to read/write from the database.
The mainMenuLoop() method and the menu() method that it calls cover most of the Java basics.
public void mainMenuLoop() {
boolean cont = true;
while (cont) {
int menuChoice = menu("Main menu for " + config.getCompany(), new String[]{
"Open point of sale",
"Manage stock and inventory",
"Manage customers"
}, sc);
switch (menuChoice) {
case 1:
pointOfSaleMenu();
break;
case 2:
stockManagementMenu(sc, itemDao);
break;
case 3:
customerManagementMenu();
break;
default:
System.out.println("Please enter a valid menu option.");
}
System.out.print("\nReturning to menu...\nContinue? [Y/n] ");
cont = !sc.nextLine().trim().toLowerCase().startsWith("n");
}
}
First, a boolean cont (continue) is set to true, and then a while loop begins, which will repeat for as long as cont remains true. In the loop, an integer menuChoice is created, which stores the return value from the menu method (which was imported from the Utils class). Note that the app's scanner, sc, is passed into this method as a parameter, to avoid managing multiple scanners. Also passed in is a string array (String[]) of menu choices, which allows the menu method to dynamically render different menus.
After the menu function, a switch[3] is used to decide what to do next. This calls the different sub-menus based on the value of menuChoice, and uses System.out.println() to print an error message if the choice was invalid.
Finally, the next value of cont is decided, by checking if the next scanner input (sc.nextLine()) begins with either n or N (.trim().toLowerCase().startsWith("n")) or not. If it doesn't, the loop continues.
Utils.java
menu()
public static int menu(String title, String[] choices, Scanner sc) {
int max = choices.length > 0 ? Stream.of(choices).map(String::length).max(Integer::compareTo).get() : 0;
max = Math.max(title.length(), max);
System.out.printf("""
%n╔%s╗
║ %s%s║
╠%s╣%n""", "═".repeat(max + 6), title, " ".repeat(max - title.length() + 4), "═".repeat(max + 6));
for (int i = 0; i < choices.length; i++) {
System.out.printf("║ %2s. %-" + max + "s ║%n", i + 1, choices[i]);
}
System.out.printf("╚%s╝%n", "═".repeat(max + 6));
boolean valid = false;
int choice = 0;
while (!valid) {
System.out.printf("Choose an option (1–%s): ", choices.length);
try {
choice = sc.nextInt();
sc.nextLine();
valid = true;
} catch (InputMismatchException e) {
System.out.println("Please enter a valid integer.");
sc.nextLine();
}
}
return choice;
}
Some of this code might look a little unusual, so I'll break it down.
The first line is using a ternary operator, which acts like an inline if statement, e.g.: condition ? 'value if true' : 'value if false'. This is used to calculate max, the longest of the menu choices, so that a box can be drawn around them at the correct width. The condition in this ternary is if choices is longer than 0, then a Stream[4] is used, which simplifies the processing of arrays and lists. It uses a map, which applies the same transformation to all values, in this case returning the length of each string. Finally, the largest of these values is found and returned.
After printing the table, Scanner.nextInt() is used to get the next integer from the user input, followed by a nextLine() to clear the buffer. This is inside a while loop, repeating until the input is a valid integer, using a try/catch loop to allow the user to retry when the system exception InputMismatchException is thrown, instead of crashing the app.
Data Access Objects
DAOs are classes used to decouple the code that uses the data from the code to update and access it in the database, adding a layer of abstraction. First, an interface was created, which is 'a group of related methods with empty bodies' that 'allows a class to become more formal about the behavior it promises to provide'[5].
package me.nathangreen.digitalartefact;
import java.util.List;
import java.util.Optional;
public interface Dao<T> {
Optional<T> getById(String id);
List<T> getAll();
void add(T t);
void save(T t);
void delete(T t);
}
This is the simple interface for the Dao. It is a generic interface, indicated by T, which represents the class type used in each implementation (e.g. Item). The interface then defines methods to get an item by its ID, returning an 'Optional' of the type (allowing for a result where there is no matching item found), a method to get all items, and void (non-returning) methods to add, save, and delete items.
This interface is then implemented by Dao classes, such as CustomerDao. (public class CustomerDao implements Dao<Customer>). This class is constructed with a connection to the database, and constructs and executes the required SQL queries to fulfil the requirements of the Dao. For example, the implementation of getById is as follows:
@Override
public Optional<Customer> getById(String id) {
String query = "select id, fname, lname, phone, add_l1, add_l2, add_town, add_postcode from customer where id = ?";
try {
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, id);
ResultSet resultSet = statement.executeQuery();
if (resultSet.first()) {
Customer result = resultSetToCustomer(resultSet);
return Optional.of(result);
} else {
return Optional.empty();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
Note the return type is now Optional<Customer>, instead of the generic <T>. The method uses the 'prerared statements' feature of the Java SQL library[6], which is created with placeholders ('?'), which are then filled just before execution (like statement.setString(1, id) above). The SQL library then returns a resultSet, which is transformed to a Customer object using resultSetToCustomer, which creates a Customer with an Address inside it with the data from the table columns, using the constructors of the classes, as below:
return new Customer(
resultSet.getString("id"),
resultSet.getString("phone"),
resultSet.getString("fname"),
resultSet.getString("lname"),
new Address(
resultSet.getString("add_l1"),
resultSet.getString("add_l2"),
resultSet.getString("add_town"),
resultSet.getString("add_postcode")
)
);
Constructing classes
This section will focus on Customer.java, and how constructors can be used not just to set data attributes as they come, but store data in more useful or complex datatypes.
Customer.java has two constructors; one that has a parameter for each attribute of the class, and one that calls the longer one, whilst creating the ID of the customer automatically. These look like this:
public Customer(String Id, String phoneNumberStr, String firstName, String lastName, Address address) {
this.Id = Id;
this.setPhoneNumber(phoneNumberStr);
this.setFirstName(firstName);
this.setLastName(lastName);
this.setAddress(address);
}
public Customer(String phoneNumberStr, String firstName, String lastName, Address address) {
this(UUID.randomUUID().toString(), phoneNumberStr, firstName, lastName, address);
}
The second constructor is useful, as it removes the need to generate a UUID in each place a customer could be created, but having the seperation of the two constructors allows for flexibility, for example if custom IDs was to be implemented. Now, the constructors take the user's phone number as a string, but the underlying getters and setters (to read and write attributes, respectively) use an external library, com.google.i18n.phonenumbers[7] to parse these phone numbers consistently.
public void setPhoneNumber(String phoneNumberStr) {
PhoneNumberUtil phoneUtil = PhoneNumberUtil.getInstance();
try {
this.phoneNumber = phoneUtil.parse(phoneNumberStr, "GB");
} catch (NumberParseException e) {
System.err.println("NumberParseException was thrown: " + e.toString());
}
}
public String getPhoneNumberFormatted() {
PhoneNumberUtil phoneUtil = PhoneNumberUtil.getInstance();
return phoneUtil.format(this.phoneNumber, PhoneNumberUtil.PhoneNumberFormat.INTERNATIONAL);
}
The setPhoneNumber method takes a string, and creates a PhoneNumberUtil, which parses the string into a PhoneNumber object, defaulting to a UK number if no country code is supplied. getPhoneNumberFormatted then returns this PhoneNumber as a string, but consistently formated in the international format. For example, an input of 07987123456 would be returned as "+44 7987 123456".
Submenus — using the DAOs
The submenu's of the app are where the DAOs are actually used. Let's look at the stock management menu. It takes the scanner and the ItemDao as parameters, and starts by using the DAO to get all the items, so they can be used in the menu. Another Stream is used, this time with a 'lambda' (a small anonymous function) inside the map, which is used to create a formatted string for each item in the list.
This uses String.format(), where you create a string with tokens like "%s" in it, and then provide values that are then put in those places. It can also format decimals nicely or pad out a string to a specified width, for example.
List<Item> items = itemDao.getAll();
List<String> menuItems = items.stream().map(item -> String.format(
"Manage %s (%s, %s in stock)",
item.getName(),
item.getPriceFormatted(),
item.getStock()
)).toList();
This List is then converted to an ArrayList, which has variable length and so allows a new option 'Add new item' to be added to the list of menu options.
menuItems = new ArrayList<>(menuItems);
menuItems.add("Add new item");
This ArrayList is finally passed to the menu() utility, which returns the user choice as an integer.
int choice = menu("Stock management", menuItems.toArray(new String[0]), sc);
The required user input is then captured using the scanner, before it is used to construct or modify the relevant Item object. This object is then saved to the database using the relevant methods of the ItemDao. For an existing item, itemDao.save(item) suffices, which matches on the item's ID, and for a new item, the following is used:
Item item = new Item(price, name, stock, rating);
this.itemDao.add(item);
Testing
Aside from the most basic form of testing that occurs when building the app and fixing and issues or bugs that arise, another form of testing that can be implemented is unit testing. In Java, the easiest way to do this is using a library such as JUnit, which allows for tests to be written for different classes and methods to ensure that they behave as expected in different situations. Generally, these use the 'Arrange, Act, Assert' pattern, setting up data for the test, acting on it (perform the target behaviour), and checking the result is as expected. Here is an example for names and phone numbers in the Customer class:
public class CustomerTests {
Customer customer;
@BeforeEach
void setUp() {
customer = new Customer("07123 123123", "Test", "Person", new Address("Line 1", "Line 2", "Town", "POST CODE"));
}
@Test
void testPhoneNumber() {
assertEquals("+44 7123 123123", customer.getPhoneNumberFormatted());
customer.setPhoneNumber("7333123322");
assertEquals("+44 7333 123322", customer.getPhoneNumberFormatted());
}
@Test
void testName() {
assertEquals("Test", customer.getFirstName());
assertEquals("Person", customer.getLastName());
assertEquals("Test Person", customer.getName());
}
}
This code uses annotations that are read by JUnit, declaring a setUp() method that runs before each test creating a basic fake Customer, and marking tests with @Test.
testPhoneNumber() sets up the Customer with 2 different phone numbers, and 'asserts' that getPhoneNumberFormatted() returns the expected result for each of these.
testName() checks that the various 'get name' methods construct the forms of the customer's name correctly.
By writing tests for your code with a reasonably high level of coverage, the chance of it containing undetected bugs, for example due to edge cases, is significantly reduced.
That's all for this blog post! Feel free to get in touch if you have any questions. Otherwise, thanks for reading :)
https://www.oracle.com/java/technologies/data-access-object.html ↩︎
https://docs.oracle.com/javase/8/docs/api/java/util/Scanner.html ↩︎
https://docs.oracle.com/javase/tutorial/java/nutsandbolts/switch.html ↩︎
https://docs.oracle.com/javase/8/docs/api/java/util/stream/Stream.html ↩︎
https://docs.oracle.com/javase/tutorial/java/concepts/interface.html ↩︎
https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html ↩︎