I am learning Java in 2020 – Beginner – Java and a MySQL Database

Operation System : Windows 10

IDE : Intellij IDEA Community

Python Implementation : Making a simple database for a restaurant menu

Problem

Goal

Learn how to connect to a mysql database in Java.

Pseudo-code

  • Make a connection to the mysql db
  • Create the “restaurants” database, if not exists
  • Populate the “Nigerian” table in the restaurants database, if not populated
  • Print out the table content

Solution

Create a Java file and import all dependencies.

Why use ArrayList?

Since I couldn’t dynamically manipulate String[], I opted for Arraylists.

Why use collections ?

I honestly was unsure of how to add arraylists into an arraylist. But luckily the IDE highlighted an error and provided a solution, where the collections.singleton function was used.

Why use properties ?

I needed a key → value data type ( in js – object), googled/stack-overflowed and found it.

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Properties;


public class ConnectDB {

... 
 }
    public static void main(String[] args){
         new ConnectDB();

    }

Create database, If…

Statement

This object will be used for making sql statements. I see it as a wrapper for simple sql statement strings.

SQLException

An error thrown when something goes wrong while, for instance connecting to a mysql database or an invalid statement was used etc.

private  void setupDB() throws SQLException {
        System.out.println("Creating database...");
        Statement statement = this.connection.createStatement();
        statement.execute("CREATE DATABASE IF NOT EXISTS " + this.dbname);

    }

Create a mysql table …

This method creates the nigerian table in the restaurant’s database. Additionally if an error occurs, the function that called this method will catch the SQLException.

private  String createColumnStatementString(String columnName,String type, String otherProps){
        String statement = columnName + " "  + type +  " " + otherProps;
        return statement;
    }
private void setupNigerianRestaurantTable() throws SQLException {

            String[] createColumnStatements = {
                    this.createColumnStatementString("id", "int", "NOT NULL AUTO_INCREMENT "),
                    this.createColumnStatementString("dish_name", "varchar(30)", "NOT NULL"),
                    this.createColumnStatementString("dish_price", "float", "NOT NULL"),
                    this.createColumnStatementString("number_sold", "int", "DEFAULT 0")};

        this.createTable(this.tablename , createColumnStatements, "id");
    }
public void createTable(String tablename, String[] createColumnStatements, String primaryKey) throws  SQLException{
        System.out.println("Creating table...");
        String createTableStatement = "\n" +
                "\tCREATE TABLE IF NOT EXISTS " +  tablename + "(";
        String columnStatements = String.join(",", createColumnStatements);
        String primaryKeyDefinition  = ",PRIMARY KEY ( " + primaryKey + " ))";
        String executionString = createTableStatement  + columnStatements  + primaryKeyDefinition;
        Statement statement = null;
        statement = this.connection.createStatement();
        statement.execute(executionString);

    }

Add data to the table …

Before we can print the table, there has to be something in it. Therefore the method adds rows to the nigerian table, which presumably is already created by the function above. If this function is called before creating the table, the SQLException will be thrown.

private  void populateTable() throws SQLException {
        System.out.println("Populating table...");
        String[][] values = {{"Egusi Soup and Eba", "24.5"}, { "Porridge Yam", "12"}, {"Porridge Beans", "13"}};
        String[] columns = {"dish_name", "dish_price"};
        String columnsString = String.join(",",columns);
        String preInsertExecString = "INSERT INTO " +  this.dbname + "." + this.tablename + "(" + columnsString + ")";
         for (int i = 0; i < values.length; i++){
            String[] row = values[i];
            String executionString = preInsertExecString + "VALUES (";
            for (int k = 0 ; k < row.length; k++){
                executionString += "'" + row[k] + "'";
                if(k < row.length - 1){
                    executionString += ",";
                }
            }
            executionString += ")";
            System.out.println(executionString);
             Statement statement = this.connection.createStatement();
             statement.execute(executionString);
        }
    }

Read contents of table…

Why not use the “execute” method ? Why use “executeQuery” ?

I need a list of all rows in the “nigerian” table and according to the documentation the “execute” method returns the first result and the getResultSet method should be used when the entire result is needed. The executeQuery method on the other hand returns a ResultObject, which we can “loop” through to access the individual rows of the table.

ResultSet

This holds the cursor to the current row of a queried table. When the next method of this object is called, we move to the next row. If there are no other rows existing in the table, then the method, when called, returns false else true. The getString method returns the column values of the current row being pointed at.

public ArrayList<ArrayList<String> > getTable() throws SQLException {
        ArrayList<ArrayList<String> > data = new ArrayList<ArrayList<String>>();
        String[] columns = {"id", "dish_name", "dish_price", "number_sold"};
        Statement statement = this.connection.createStatement();
        ResultSet rs  = statement.executeQuery("SELECT * FROM " + this.dbname + "." + this.tablename);
        while (rs.next()){
            String id = rs.getString("id");
            String dish_name = rs.getString("dish_name");
            String dish_price = rs.getString("dish_price");
            String number_sold = rs.getString("number_sold");
            ArrayList<String> row = new ArrayList<String>();
            row.add(id);row.add(dish_name);row.add(dish_price);row.add(number_sold);
            data.addAll(Collections.singleton(row));
        }
        return data;
    }

Print out table …

The goal was to print the table content in this format, but I did not have enough time to invest in it, so I just took a less complicated route. sorrie.

public void printOutTable() throws SQLException {
        /***
         *------------------------------------------------------
         * | id | dish_name          | dish_price | number_sold |
         * ------------------------------------------------------
         * |  1 | Egusi Soup and Eba |       24.5 |           0 |
         * |  2 | Egusi Soup and Eba |       24.5 |           0 |
         * |  3 | Porridge Yam       |         12 |           0 |
         * |  4 | Porridge Beans     |         13 |           0 |
         * ------------------------------------------------------
         */
        ArrayList<ArrayList<String>> data = this.getTable();
        String header = "";
        String values = "";
        String line ="--------------------------------------------";
        String[] columns = {"id", "dish_name", "dish_price", "number_sold"};
        for(int j = 0; j < columns.length; j++){
            System.out.print("| "  + columns[j] + " ");
        }
        System.out.println();
        System.out.println(line);
        for(int i = 0; i < data.size(); i++){

                ArrayList<String> row = data.get(i);
                for(int k = 0; k < row.size(); k++){

                    System.out.print("| " + row.get(k) + " ");


                }
                System.out.println();
        }
    }

Conclusion

DriverManager

Interface that allows a program to communicate to a datasource such as SQL.

ConnectDB(){
        this.properties = new Properties();
        this.properties.put("user", "root");
        this.properties.put("password", "");

       try{

           //  jdbc:subprotocol:subname e.g.  jdbc:mysql://localhost:3306/
           String url  = "jdbc:" + this.dbtype + "://" + this.servername + ":" + this.portNumber + "/" + this.dbname;
           System.out.println(url);
           this.connection = DriverManager.getConnection(url, this.properties);
           this.setupDB();
           this.setupNigerianRestaurantTable();
           if(this.populated == false){
               this.populateTable();
           }
           this.printOutTable();
       }catch (SQLException e){
           e.printStackTrace();
       }
    }

Result

jdbc:mysql://localhost:3306/restaurants
Creating database...
Creating table...
| id | dish_name | dish_price | number_sold 
--------------------------------------------
| 1 | Egusi Soup and Eba | 24.5 | 0 
| 2 | Porridge Yam | 12.0 | 0 
| 3 | Porridge Beans | 13.0 | 0 

Process finished with exit code 0

Author Notes

This is most likely not the cleanest implementation, just a heads up.

Links

  1. What is JDBC? Introduction to Java Database Connectivity
  2. Establishing a Connection
  3. Setting Up Tables
  4. Java 8 – Join String Array – Convert Array to String
  5. SQL General Data Types
  6. SQL CREATE DATABASE Keyword
  7. Connector/J Installation
  8. How to solve java.sql.SQLException: No suitable driver found for
  9. CREATE TABLE IF NOT EXISTS
  10. SQL INSERT INTO Statement
  11. JDBC – Select Records Example
  12. ArrayList add/replace element at specified index in Java