Java and MySQL - A JDBC Introduction

Best experience for the blog post here : )

Java DataBase Connectivity (JDBC)

What is JDBC?

Java Database Connectivity is a tool or an application programming interface (API) that establishes a connection between a standard Database (say MYSQL) and a Java Application that intends to use that database.

Need for JDBC ?

Before JDBC, ODBC API was the database API to connect and execute the query with the database. But, ODBC API uses ODBC driver which is written in C language (i.e. platform dependent and unsecured). That is why Java has defined its own API (JDBC API) that uses JDBC drivers (written in Java language)

We can use JDBC API to handle database using the Java program and can perform the following activities:

1. Connect to the DataBase
2. Execute Queries and update statements to the database
3. Retrieve the result received from the database

So, we can make an analogy of JDBC as the API which will allow us to create our own MySQL workbench of sorts, or more realistically allow us to create the controller for the database in our application. (if we are building a MVC application)

Wait wait, what the hell is an API anyway?

API (Application programming interface) is a document that contains a description of all the features of a product or software. It represents classes and interfaces that software programs can follow to communicate with each other. An API can be created for applications, libraries, operating systems, etc.

Let's perform a thought experiment, we went to a restaurant and ordered some food the food here is the data that we want in programming terms now we ask the waiter to take our order and deliver a message to the kitchen the backend of an application. The backend does its magic and get back out food or the data.

Here, the waiter is the API.

---

Now that we have a basic understanding of what JDBC is let's move forward but before we can start coding there are a few things that we need to know.

Data Types in JDBC

More Data Types

BINARY	byte[]
DATE	java.sql.Date
TIMESTAMP	java.sql.Timestamp
ARRAY	java.sql.Array
REF	java.sql.Ref
STRUCT	java.sql.Struct
BIGINT	long
CLOB	java.sql.Clob
BLOB	java.sql.Blob

JDBC Architecture

Setting up the Dev Environment

Setting up Java Environment

1. Download the Latest SDK
2. Set Java_Home
3. Set Java Path

Setting up MySQL Environment

1. Download the Latest MySQL Workbench
2. Extract the file
3. Run the setup file

Note: You can use whichever Database application that you like as long as it is a SQL database

Steps to Connect JDBC

1. Importing Packages This procedure is followed to make sure the relevant packages are readily available for the API
2. Load and Register Drivers The imported drivers need to be loaded and registered for establishing a connection between the application and the database
3. Connecting Database After the first two steps, the next stage is to connect the database. This can be done by getConnection()
4. Writing the Queries Here, we write in the SQL Command or Query that we need to execute
5. Executing the Queries Now the query from the API is executed using executeQuery()
6. Processing Request After the above five requests, the system basically does two things:
   • Process Output Value
   • Retrieve Values
7. Closing Statement The resultSet and statement need to be closed explicitly.
8. Closing Connections The last stage is to ourselves or the API from the Database

Let's Start Coding then 😃

Getting our Database Ready

• First, we need to launch MySQL, which we can do by launching the MySQL workbench or through the terminal for the sake of simplicity of this tutorial we are gonna be using the terminal
• Now that MySQL is running, we can create a database named university by typing in the command CREATE DATABASE university;, and then to check whether the database is running or not we can execute SHOW DATABASES; this is my output, notice the university database.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| University |
| customerdb |
| mysql |
| openmrs |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.02 sec)
mysql>

• Next, we will create a table named engineeringStudents with {id, department, first_name, last_name, passout_year, uni_rank} by executing the following command:

CREATE TABLE `customerdb`.`EngineeringStudents` (
`student_id` INT NOT NULL,
`department` VARCHAR(45) NOT NULL,
`first_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`passout_year` INT NOT NULL,
`university_rank` INT NOT NULL,
PRIMARY KEY (`student_id`),
UNIQUE INDEX `student_id_UNIQUE` (`student_id` ASC));

• Now the last step to make our Database ready is to insert some data into it, which we will be done by the following commands

insert into engineeringstudents
   value(10201, 'CSE', 'Kiran', 'Acharya', 2018, 1272);
insert into engineeringstudents
   value(10202, 'ME', 'Jayson', 'Robert', 2020, 1249);
insert into engineeringstudents
   value(10203, 'CSE', 'Praveen', 'Kumar', 2017, 1238);
insert into engineeringstudents
   value(10204, 'ECE', 'Rajesh', 'Kumar', 2019, 1243);
insert into engineeringstudents
   value(10205, 'CSE', 'Trevor', 'Krugar', 2018, 1000);
insert into engineeringstudents
   value(10206, 'CSE', 'Jogn', 'Denver', 2018, 1);
insert into engineeringstudents
   value(10207, 'ECE', 'Jennifer', 'Charles', 2019, 13232);

The Java Module 🥲

Creating the Module

• You can use any IDE/Text Editor that you want, although I would recommend you download and install IntelliJ IDEA CE
• Launch the IDE
• Next, create a Java Project (Not a Maven or Gradle, etc)
• I am going to call it jdbc-intro
• And then create a JDBC directory with a JDBC.java class in it, the directory structure should look like this:

|-- jdbc-intro
    |-- lib
    |-- src
        |-- JDBC
            |-- JDBC.java

Creating a basic SQL query in Java

• Now, we would start writing the code in the src/JDBC/JDBC.java file:

package JDBC;

import java.sql.*;

public class JDBC {
    public static void main(String args[]) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/customerdb";
        String uname = "root";
        String password = "password";
        String query = "select * from engineeringstudents";
    }
}

• This code is pretty self-explanatory, except for the import java.sql.*; line.
• This is the line that provides us with all of the function that is present in the JDBC Library/API

• But we haven't added JDBC to our project, which we are going to use a JAR

  • First, we will download the Jar file for JDBC from this link
  • Next, to add this JAR file to our project we can follow this article on GFG

• Checking if our package is imported correctly by using the try and catch block which finds the package className

package JDBC;

import java.sql.*;

public class JDBC {
    public static void main(String args[]) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/customerdb";
        String uname = "root";
        String password = "password";
        String query = "select * from engineeringstudents";

        // Checking if the package is present
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

Connecting to the Database and printing the result to the console

• To create a connection to the database we use the Connection class present in the JDBC Driver and pass the url, uname, and password to the getConnection() method in the DriverManager
• Then to convert our query String into a JDBC Statement we are going to use the Statement class and createStatement() method

Connection con = DriverManager.getConnection(url, uname, password );
Statement statement = con.createStatement();

• Now that we have created a Query Statement, it needs to be executed and the result stored in a variable, which will be of the type ResultSet which is also provided by the JDBC Driver
• The statement is to be executed using the executeQuery() method by providing it the query as a parameter

ResultSet result = statement.executeQuery(query);

• Now, the only thing remaining is to print the result obtained to the console.
• while(result.next()) this part is pretty self explanatory, until the next result is null
  • We will iterate over all the 6 properties: {id, department, first_name, last_name, passout_year, uni_rank} which can be accessed by the result.getString(index) method by passing the index to it.
  • Then we will concatenate this result into a temporary variable.
  • When the iteration is completed (no more properties of index: i remain) we can print this temporary variable

while(result.next()) {
      String UniversityData =  "";
      for(int i=1; i <= 6; i++) {
         UniversityData += result.getString(i) + ":";
      }
      System.out.println(UniversityData);
}

Final Code

package JDBC;

import java.sql.*;

public class JDBC {
    public static void main(String args[]) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/customerdb";
        String uname = "root";
        String password = System.getenv("SQLPassword");
        String query = "select * from engineeringstudents";

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            Connection con = DriverManager.getConnection(url, uname, password );
            Statement statement = con.createStatement();

            ResultSet result = statement.executeQuery(query);

            while(result.next()) {
                String UniversityData =  "";
                for(int i=1; i <= 6; i++) {
                    UniversityData += result.getString(i) + ":";
                }
                System.out.println(UniversityData);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}