Rutam Prita Mishra

Posted on Jun 6, 2023

Predict Sales of Manufacturing with MindsDB using Java

#tutorial #machinelearning #database #java

Introduction

MindsDB is a powerful AutoML framework. Developers and data scientists highly rely on it to build predictive models that are highly accurate, without requiring extensive knowledge of machine learning algorithms. With MindsDB, you can extend the power of AI to predict or forecast certain target outcomes and gain valuable insights from your data.

Java, on the other hand, is a versatile and popular programming language that provides robustness and scalability for developing various applications. By integrating MindsDB with Java, you can harness the capabilities of both technologies to create a comprehensive solution.

In this tutorial, we will explore the seamless integration of MindsDB and Java to predict sales in the manufacturing sector. Let's dive in and discover the exciting possibilities that arise when combining the cutting-edge technology of MindsDB with the robustness and flexibility of Java!

Setting up a Java Project

You are free to choose the editor as per your convenience. However, we will use Eclipse as the editor for this tutorial. You can follow the steps below to successfully set up a Java project and deal with all the required dependencies.

Open your Eclipse Editor. You can download the Eclipse editor from here.
Click on the File menu, select New and then click on Project....

In the New Project wizard, select Java Project and click on Next. Finally provide a name for the project, leave everything else at default and click on Next.

In the next page, you can configure the build settings for the project. To successfully complete this tutorial, we will need the MySQL Java connector jar. You can download it from here.

Now simply click on Libraries, select Classpath and then hit the Add External JARs... button. Finally click on Finish button.

If a popup appears to display the default perspective for the project, click on Open Perspective to proceed.

It is not really advised to create Java class files in the /src root i.e., default package. So, we will create a package first.

Right click on /src, select New and then click on Package. Provide a name for the package and then click on Finish.

Now the Java project is ready, and we can continue with further steps below.

Creating a Connection to MindsDB Cloud

The first step we want to take is to successfully create a connection to MindsDB Cloud.

Let's create a new Class file inside mindsdb package and name it as connections.java.

You can use the code snippet below to establish the connection.

// Import the necessary packages
import java.sql.Connection;
import java.sql.DriverManager;

public class Connections {
    public static void main(String[] args) {
        final String host = "jdbc:mysql://cloud.mindsdb.com:3306/files";
        final String username = "YOUR_MINDSDB_ACC_EMAIL";
        final String password = "YOUR_MINDSDB_ACC_PASSWORD";

        Connection conn = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(host, username, password);
            System.out.println("Connection to the database created successfully");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Connection Failed. Please check your credentials.");
        }
    }
}

Here is what the code does.

It imports the necessary packages, including java.sql.Connection and java.sql.DriverManager.
The connection details, such as the host, username, and password, are specified in the code and is specific to each user.
The getConnection() method is used to establish a connection to the database, and if successful, a message indicating a successful connection is printed. If there is an exception or the connection fails, an error message is displayed.

Fetching Table data from MindsDB Cloud

If we are able to successfully connect to MindsDB Cloud, we can now try to fetch some table data.

Simply create another new Class file and name it as Tables.java.

For this tutorial, we are using this dataset from Kaggle. Download this dataset and upload it on MindsDB Cloud as a table so that we can use it now.

You can use the code snippet below to fetch the data from the Sales table.

// Import the necessary packages
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Tables {
    public static void main(String[] args) {
        final String host = "jdbc:mysql://cloud.mindsdb.com:3306/files";
        final String username = "YOUR_MINDSDB_ACC_EMAIL";
        final String password = "YOUR_MINDSDB_ACC_PASSWORD";

        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(host, username, password);
            System.out.println("Connection to the database created successfully");

            String query = "SELECT * FROM files.Sales LIMIT 3";
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);

            while (rs.next()) {
                String date = rs.getString("Date of Order");
                String storeCode = rs.getString("Store Code");
                String itemCode = rs.getString("Item Code");
                String sales = rs.getString("sales");
                String district = rs.getString("District");
                String state = rs.getString("State");

                System.out.println("\n\n Date: " + date + "\n Store Code: " + storeCode + "\n Item Code: " + itemCode
                        + "\n Sales: " + sales + "\n District: " + district + "\n State: " + state);
            }

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("An error occurred while fetching table data.");
        }
    }
}

Let's understand what this code does below.

The code establishes a connection to a MySQL database hosted on the MindsDB Cloud.
It imports the necessary packages, including java.sql.Connection, java.sql.DriverManager, java.sql.ResultSet, and java.sql.Statement.
The connection details, such as the host, username, and password, are specified in the code.
A SQL query is executed to retrieve data from the Sales table in the files database. The retrieved data is then displayed in the console, including columns like "Date of Order," "Store Code," "Item Code," "Sales," "District," and "State."

Creating a Predictor Model

Now we can similarly establish a connection and create a Predictor model using a Java Class file. Create another new class named Predictor.java and use the code snippet given below.

// Import the necessary packages
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Predictor {
    public static void main(String[] args) {
        final String host = "jdbc:mysql://cloud.mindsdb.com:3306/files";
        final String username = "YOUR_MINDSDB_ACC_EMAIL";
        final String password = "YOUR_MINDSDB_ACC_PASSWORD";

        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(host, username, password);
            System.out.println("Connection to the database created successfully");

            String query = "CREATE PREDICTOR mindsdb.sales_predictor FROM files (SELECT `Store Code`,`Item Code`,sales,District,State FROM Sales LIMIT 10000) PREDICT sales";
            statement = connection.createStatement();
            boolean isSuccess = statement.execute(query);

            System.out.println("Is the predictor model created successfully: " + isSuccess);

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("An error occurred while creating the predictor. " + e);
        }
    }
}

Find a summary of what the code does below.

The code establishes a connection to a MySQL database hosted on the MindsDB Cloud.
It imports the necessary packages, including java.sql.Connection, java.sql.DriverManager, and java.sql.Statement.
The connection details, such as the host, username, and password, are specified in the code.
A SQL query is executed to create a predictor model named sales_predictor using the data from the Sales table in the "files" database. The predictor model is trained to predict the sales column. The query also limits the data to 10,000 rows.
The result of the model creation operation is printed to the console, indicating whether the predictor model was created successfully or not.

Note: The model name needs to be unique. So, if the model is created once successfully and you try to execute this code multiple times, you will get an error stating the following.
Error: model 'sales_predictor' already exists in project mindsdb!

Checking Status of the Model

Once you create a model, it takes some time to complete it's training. You can create a new class named Status.java and use the code snippet below to fetch the creation status of the current model.

// Import the necessary packages
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Status {
    public static void main(String[] args) {
        final String host = "jdbc:mysql://cloud.mindsdb.com:3306/files";
        final String username = "YOUR_MINDSDB_ACC_EMAIL";
        final String password = "YOUR_MINDSDB_ACC_PASSWORD";

        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(host, username, password);
            System.out.println("Connection to the database created successfully");

            String statusQuery = "SELECT status FROM mindsdb.predictors WHERE name='sales_predictor'";
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(statusQuery);

            while (rs.next()) {
                String status = rs.getString("status");
                System.out.println("\n\n Status: " + status);
            }

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("An error occurred while fetching table data.");
        }
    }
}

Here is a summary of what the code does.

The code establishes a connection to a MySQL database hosted on the MindsDB Cloud.
It imports the necessary packages, including java.sql.Connection, java.sql.DriverManager, and java.sql.ResultSet.
The connection details, such as the host, username, and password, are specified in the code.
A SQL query is executed to fetch the status of the specific predictor model named sales_predictor from the mindsdb.predictors table.
The status of the predictor model is retrieved from the result set and printed to the console.

Making Predictions

We have now successfully created a model to predict the sales of medium scale units located in Maharashtra, India.

We can predict the sales in two ways i.e., Single or Batch. Let us see how we can do this one by one below.

Single Predictions

In this type of prediction, we predict a single target value based on a set of given feature set values.

You can create another new Class file named SinglePrediction.java and use the code snippet below.

// Import the necessary packages
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Prediction {
    public static void main(String[] args) {
        final String host = "jdbc:mysql://cloud.mindsdb.com:3306/files";
        final String username = "YOUR_MINDSDB_ACC_EMAIL";
        final String password = "YOUR_MINDSDB_ACC_PASSWORD";

        Connection connection = null;
        Statement prediction = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(host, username, password);
            System.out.println("Connection to the database created successfully");

            String query = "SELECT * FROM mindsdb.sales_predictor WHERE `Item Code`=1 AND District='Kolhapur'";
            prediction = connection.createStatement();
            ResultSet rs = prediction.executeQuery(query);

            while (rs.next()) {
                int sales = rs.getInt("sales");
                float confidence = rs.getFloat("sales_confidence");
                float sales_min = rs.getFloat("sales_min");
                float sales_max = rs.getFloat("sales_max");

                System.out.println("\n\n Sales: " + sales + "\n Confidence: " + confidence + "\n Max Sales: " + sales_max + "\n Min Sales: " + sales_min);
            }

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("An error occurred. Please try again!" + e);
        }
    }
}

You can understand what the code exactly does below.

The code imports necessary packages for establishing a connection to a MySQL database and performing SQL operations.
It establishes a connection to the MindsDB Cloud MySQL database using the provided host, username, and password.
A SQL query is executed to fetch prediction results from the sales_predictor model based on specified conditions (Item Code=1 and District='Kolhapur').
The fetched prediction results, including sales, confidence, maximum sales, and minimum sales, are printed to the console.

Batch Predictions

We can also choose to predict the target value for a set of data records i.e., a Batch. To achieve this, we can JOIN the sales_predictor model with the Sales table.

Now create a new file again named BatchPredictions.Java and use the code snippet given below.

// Import the necessary packages
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Batch {
    public static void main(String[] args) {
        final String host = "jdbc:mysql://cloud.mindsdb.com:3306/files";
        final String username = "YOUR_MINDSDB_ACC_EMAIL";
        final String password = "YOUR_MINDSDB_ACC_PASSWORD";

        Connection connection = null;
        Statement prediction = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(host, username, password);
            System.out.println("Connection to the database created successfully");

            String query = "SELECT t.`Date of Order`, t.`Item Code`, t.`Store Code`, t.District, s.sales, s.sales_confidence, s.sales_min, s.sales_max, t.State FROM files.Sales as t JOIN mindsdb.sales_predictor AS s ORDER BY t.`Date of Order` LIMIT 5;";
            prediction = connection.createStatement();
            ResultSet rs = prediction.executeQuery(query);

            while (rs.next()) {
                int sales = rs.getInt("sales");
                float confidence = rs.getFloat("sales_confidence");
                float sales_min = rs.getFloat("sales_min");
                float sales_max = rs.getFloat("sales_max");
                String date = rs.getString("Date of Order");
                String storeCode = rs.getString("Store Code");
                String itemCode = rs.getString("Item Code");
                String district = rs.getString("District");
                String state = rs.getString("State");

                System.out.println("\n\n Date: " + date + "\n Store Code: " + storeCode + "\n Item Code: " + itemCode
                        + "\n District: " + district + "\n State: " + state + "\n Sales: " + sales + "\n Confidence: "
                        + confidence + "\n Max Sales: " + sales_max + "\n Min Sales: " + sales_min);
            }

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("An error occurred. Please try again!" + e);
        }
    }
}

We can understand what the code does with the summary given below.

The code imports necessary packages for establishing a connection to a MySQL database and performing SQL operations.
It establishes a connection to the MindsDB Cloud MySQL database using the provided host, username, and password.
A SQL query is executed to fetch batch prediction results from the Sales table and sales_predictor model joined based on a condition, with results ordered by the Date of Order column and limited to 5 records.
The fetched batch prediction results, including various attributes such as sales, confidence, minimum sales, maximum sales, date, store code, item code, district, and state, are printed to the console.

Note: Don't forget to replace the username and password strings in all of the code snippets above with your own MindsDB Cloud account credentials. If you don't have a MindsDB Cloud account, then you can create one here.

Conclusion

In this tutorial, you learnt how to predict sales for medium manufacturing units using MindsDB with Java. You explored how to set up a Java project in Eclipse, create a connection to MindsDB Cloud, fetch table data, create a predictor model, check the status of the model, and make single and batch predictions.

The next thing you can try to do on top of it is to modify the code and enable user input values from the console to be passed onto the query which will used to predict the sales value accordingly.

Another thing to note here is that MindsDB has already launched several SDKs for different platforms like JavaScript, Python, etc. Feel free to explore them as well.

Lastly, before you leave this page, please leave your suggestions, if any, in the comment box and don't forget to drop a Like.