Java and SQLite – Generating Data

In order to test the performance of a database, or application that uses it, where there are large volumes of data, it may be useful to generate data rather than having to enter it manually.

The following example demonstrates how to generate data for a table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting, importing and exporting data.

Firstly, a check is made to see if the database file actually exists. If it doesn’t, a message is displayed and execution of the program is halted. If successfully found, a connection to the database is established and the number of records to generate is specified. This can be set to any value, as long as there is enough space in the database to accommodate the records. A number of arrays are defined so that a random first name, last name and title can be selected for each record. The first name array also holds the gender associated with the name so that an appropriate title can be selected. A date range is also specified to allow for the generation of a random date of birth, along with other variables.

A ‘for’ loop is used to generate the desired number of records. A random number is generated to select a first name from the corresponding array and this is extracted, along with the gender. The same process occurs to extract a random last name. The gender is then used to extract a title from the appropriate array. The final random value generated is the date of birth.

Once all the values have been generated, an SQL ‘insert‘ statement is constructed and then executed. Feedback is provided as to the number of records added to the database. A ‘try-catch’ block is also used to catch any errors that may occur.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.Calendar;
import java.util.Date;
import java.util.Random;

public class DemoSQLite {

    public static void main(String[] args) {

        // Database and connection variables.
        File database = new File("C://Demo//testDB.db");
        Connection connect = null;

        // Check if database file exists.
        if (!database.isFile()) {

            // Confirm incorrect database location and stop program execution.
            System.out.println("Error locating database.");


        try {

            // Connect to database.
            connect = DriverManager.getConnection("jdbc:sqlite:" + database.getPath());

        } catch (SQLException e) {

            // Confirm unsuccessful connection and stop program execution.
            System.out.println("Database connection unsuccessful.");


        // Number of records to generate.
        int recordsToGenerate = 500;

        // First names.
        String[][] fname = {
                { "Oliver", "M"}, { "Noah", "M"}, { "Harry", "M"},
                { "Leo", "M"}, { "Charlie", "M"}, { "Jack", "M"},
                { "Freddie", "M"}, { "Alfie", "M"}, { "Archie", "M"},
                { "Theo", "M"}, { "Olivia", "F"}, { "Sophia", "F"},
                { "Amelia", "F"}, { "Emily", "F"}, { "Ava", "F"},
                { "Isla", "F"}, { "Isabelle", "F"}, { "Charlotte", "F"},
                { "Layla", "F"}, { "Freya", "F"}

        // Last names.
        String[] lname = {
                "Smith", "Johnson", "Williams", "Jones",
                "Brown", "Davis", "Miller", "Wilson",
                "Taylor", "Anderson", "Thomas", "White",
                "Martin", "Thompson", "Robinson", "Clark",
                "Walker", "Young", "Wright", "Hill"

        // Male titles.
        String[] mtitle = {
                "Mr", "Dr", "Prof"

        // Female titles.
        String[] ftitle = {
                "Miss", "Mrs", "Ms", "Dr", "Prof"

        // Start and end dates for random date of birth range.
        // Dates converted in to epoch days, relative to 01/01/1970.
        Date dateToday = new Date();
        Calendar calendar = Calendar.getInstance();
        int startDay = (int) LocalDate.of(calendar.get(Calendar.YEAR)-100,
        int endDay = (int) LocalDate.of(calendar.get(Calendar.YEAR)-20,

        // Random epoch day.
        long randomDay;

        // Random class and number.
        Random random = new Random();
        int randomNumber;

        // New record values.
        String firstname;
        String lastname;
        String gender;
        String title;
        LocalDate dob;

        // SQL variables.
        String sqlPersonInfo;
        PreparedStatement statement;

        // Record count.
        int recordCount = 0;

        try {

            // Generate specified number of records.
            for (int i = 1; i <= recordsToGenerate; i++) {

                // Randomly select a first name and associated gender.
                randomNumber = random.nextInt(fname.length);
                firstname = fname[randomNumber][0];
                gender = fname[randomNumber][1];

                // Randomly select a last name.
                randomNumber = random.nextInt(lname.length);
                lastname = lname[randomNumber];

                // Randomly select a title based on the gender.
                if (gender.equals("M")) {

                    randomNumber = random.nextInt(mtitle.length);
                    title = mtitle[randomNumber];

                } else {

                    randomNumber = random.nextInt(ftitle.length);
                    title = ftitle[randomNumber];


                // Randomly select a date of birth.
                randomDay = startDay + random.nextInt(endDay - startDay);
                dob = LocalDate.ofEpochDay(randomDay);

                // Query text.
                sqlPersonInfo =
                        "INSERT INTO person " +
                        "       (firstname, lastname, title, dob) " +
                        "VALUES (?, ?, ?, ?) ";

                // Create statement and bind the parameters.
                statement = connect.prepareStatement(sqlPersonInfo);
                statement.setString(1, firstname);
                statement.setString(2, lastname);
                statement.setString(3, title);
                statement.setString(4, dob.toString());

                // Execute query.

                // Increment the record count.
                recordCount += 1;


            // Provide feedback on the number of records added.
            if (recordCount == 0) {

                System.out.println("No new person records added.");

            } else if (recordCount == 1) {

                System.out.println(recordCount + " person record added.");

            } else {

                System.out.println(recordCount + " person records added.");


        } catch (Exception e) {

            // Confirm error adding person information and exit.
            System.out.println("Error adding person information.");