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
- What is JDBC? Introduction to Java Database Connectivity
- Establishing a Connection
- Setting Up Tables
- Java 8 – Join String Array – Convert Array to String
- SQL General Data Types
- SQL CREATE DATABASE Keyword
- Connector/J Installation
- How to solve java.sql.SQLException: No suitable driver found for
- CREATE TABLE IF NOT EXISTS
- SQL INSERT INTO Statement
- JDBC – Select Records Example
- ArrayList add/replace element at specified index in Java
