Keys must be always be strings and values must be a valid JSON data type i.e. one of string, number, object, array, boolean or null
Looking at the data helps us decide what column names and data types we need to set up in our database. This is called the database schema. We have this already setup.
To insert the data into database tables, we need to firstly asychronously load the file, then iterate over the data and insert it using Prepared Statements. Before we do this, let's use the best practice of Test Driven Development (TDD) and write a unit test.
Write a failing test
Best practice is that unit tests should not make any network or database connections, these should be "mocked" or "stubbed". One of the reasons for this is that unit tests are often run as part of an automated build pipeline which needs to complete quickly to confirm there are no issues. Integration tests do however "integrate" different parts of a software system so will connect to a database.
Think about how you could create an integration test which asserts that the database has been populated successfully from the JSON seed file.
Remember that a test should be self-contained, it should not rely on other tests/code. Unit test frameworks typically provide the ability to execute code prior to each/all tests running, for example, Jest provides us with a beforeAll method which runs before any of tests are run, we can use this to intialise an in-memory database with the database schema.
Our test can run a SQL SELECT query to ensure the data was loaded successfully by counting the number of rows inserted.
Reading JSON data from a file
/* Reading from a file may take a long time hence it is performed asynchronously.
Here is a version of a `load()` method which makes use of `async`/`await` to load the data into a JavaScript array, you can also write this using Promises or Callbacks.
*/const fsp = require('fs').promises; // Node.js file system module with promisesasyncfunctionload() {
console.log('calling load');
// wait for the file to be readconst buffer = await fsp.readFile('./restaurants.json');
const restaurants = (JSON.parse(String(buffer)));
return restaurants;
}
Add the following dependency to pom.xml
<dependency>
<groupId>com.googlecode.json-simple</groupId>
<artifactId>json-simple</artifactId>
<version>1.1.1</version>
</dependency>
/*
Code snippet which reads from a restaurants.json file located in the src/main/resources directory
*/
InputStreamReader fileReader = new InputStreamReader(JSONReader.class.getResourceAsStream("/restaurants.json"));
try {
JSONArray restaurants = (JSONArray) new JSONParser().parse(fileReader);
} finally {
// close the file resource
if (fileReader!=null) {
fileReader.close();
}
}
Assignment
Download this restaurant data file to the directory where you completed the previous lesson.
Write a unit test to verify your seed data has loaded into the database ok. This will fail initially but should pass once you complete the rest of the assignment.
Modify the PopulateDB file/class you created in the previous lesson to include code to load the restaurant JSON data and recursively insert each Restaurant, Menu & MenuItem into the database. You will be looping through a multi-dimensional array. Remember to use try/catch/finally blocks to handle errors and ensure you close database resources.
Commit your code into Github and share the link with your coach for review.
Assignment extension tasks
Add additional unit tests to simulate exception conditions such as the seed file not being found or the database table not existing.
Additional resources
To support this lesson we recommend watching the following videos: