Connect your Java application with any SQL databases

Java, one of the biggest and largest used languages in the industry. It would be better to get this language on your pack as a Software Engineer.

During my study of Java at the university since 2017, in more than one class, I have always noticed that 90% of my colleagues can't connect to the database, and all their projects were only dependent on the temporary interaction stored in memory.

So, today through this article I try to help them and all the community by providing a deep guide to explain each line you can write to connect your java application to SQL databases through JDBC.

JDBC 🤔 ?!

JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and execute the query with any relational database through a JDBC driver. It is a part of JavaSE (Java Standard Edition).

Why Should We Use 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).

Connectivity 🪁 ?!

With this article, we use MySQL but almost steps are applied to all relational databases like PostgreSQL, SQLite, MariaDB, Oracle ...etc

First, let's setup our database by running this sql queries in your engine;

CREATE DATABASE myjavadb;
USE myjavadb;
CREATE TABLE user(
  id int(10),
  name varchar(40),
  age int(3)
);

Then, you can create a java project with your own IDE/editor and start adding the jdbc connector by following these 2 steps;

1. Install the Java connector (mysqlconnector.jar) here.

2. Load the mysqlconnector.jar connector file to your project by paste it in
   JRE/lib/ext folder.

Now, we can start the connection behave by create 2 classes.

1. MySQLConnector to generate the database connection url based on the configuration.

public final class MySQLConnector {
  private final static String dbHost = "localhost";
  private final static String dbPort = "3306";
  private final static String dbName = "myjavadb";
  private final static String dbUsername = "root";
  private final static String dbPassword = "mypassword";

  public static String getConnectionURL() {
    return String.format(
      "jdbc:mysql//%s:%s/%s?user=%s&password=%s",
      dbHost,
      dbPort,
      dbName,
      dbUsername,
      dbPassword
    );
  }
}

1. DBContext to wrap all the logic behind the database.

import java.sql.*;

public class DBContext {
  private Connection connection;
  private Boolean connected;

  public DBContext() {
    this.connection = null;
    this.connected = false;
  }

  public Boolean connect() throws SQLException, ClassNotFoundException {
    // You can find the class name for any relational database with samll search on google.
    // PostgreSQL example: https://jdbc.postgresql.org/documentation/81/load.html
    Class.forName("mysql.jdbc.Driver");
    // We use DriverManager.getConnection() to connect.
    this.connection = DriverManager.getConnection(
      MySQLConnector.getConnectionURL()
    );
    this.connected = true;
    return this.connected;
  }

  public Boolean close() throws SQLException {
    // Close the opened connection.
    this.connection.close();
    if (this.connection.isClosed()) {
      this.connection = null;
      this.connected = false;
    }

    return !this.connected;
  }
}

Queries 🐱‍💻 ?!

Finally, we are on the sexiest part 🐱‍🏍! SQL databases give us the ability to fetch or mutate data based on the SQL CRUD operations through the Data manipulation language (DML).

📍 You can learn more on DML and SQL through this ressource.

If we need to make a query we need to create a statement and execute it but we have 2 type of statement in JDBC;

1. Statement: an interface provides methods to execute queries with the database.
2. PreparedStatement: an subinterface of Statement. It is used to execute parameterized query.

Summary:

If we want to pass params to the query and/or prevent the sql injection problems you should use PreparedStatement.

For our case, we will go with PreparedStatement;

public class DBContext {
  // ...
  private PreparedStatement preparedStatement;

  public DBContext() {
    // ...
    this.preparedStatement = null;
  }

  // ...

  public PreparedStatement preparedQuery(String sqlQuery) throws SQLException {
    // Not we can add our custom exception. Soon, I will write an article on it 😅!
    if (!this.connected)
      throw new SQLException();
    this.preparedStatement = this.connection.prepareStatement(sqlQuery);
    return this.preparedStatement;
  }

  // ...
}

Then we make a DBContext instance, and inject all needed params like this;

DBContext myDBContextInstance = new DBContext();
myDBContextInstance
  .preparedQuery("SELECT * FROM ?;")
  .setString(1, "users");

📍 You can check all params setter through the java offical web site.

At this stage, we prepared the SQL query but we don't interact with database yet!
To do that, we should execute it ... but we have 2 method to do that;

1.executeQuery: to execute fetching SQL queries (SELECT).

ResultSet result = myDBContextInstance
  .preparedQuery("SELECT * FROM ?;")
  .setString(1, "users")
  .executeQuery();

while(result.next())
  System.out.println(result.string('name'));

2.executeUpdate: to execute mutating SQL queries (INSERT, UPDATE, DELETE).

int result = myDBContextInstance
  .preparedQuery("DELETE * FROM ? WHERE id=?;")
  .setString(1, "users")
  .setString(2, "1")
  .executeUpdate();

System.out.println(result + " deleted!");

Note ⚠:
It would be better to improve the code by using patterns like the Singleton pattern to keep only one instance of the connection.

📚 Ressources to deep dive into JDBC:

• https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
• https://www.javatpoint.com/java-jdbc
• https://www.geeksforgeeks.org/introduction-to-jdbc/

Did I miss something? Let me know in the comment section and let’s work on that.

Thank you for reading. I hope this will help you on your journey! ❤️