Java Database Connectivity – JDBC

Introduction to JDBC driver

JDBC stands for Java Database Connectivity, which is a Java Application Programming Interface (API) for a client to access databases.

To connect to a database (here we use the MySQL database), first we need the JDBC driver. Go to the site:

https://dev.mysql.com/downloads/connector/j/

Choose the Platform Independent in the OS options, and download the zip file. Unzip the zip file and find in the folder the .jar file (the file name will contain the string mysql-connector-java). Now just rename it to jdbc.jar and copy it to src folder in our current project folder. We will need it later.

The JDBC classes are inside the package java.sql so we need to import the package, in Test.java:

import java.sql.*;

In the previous section, when we want to work with the test database, we have to connect to the mysql server with the username testuser and password. Only then we can manipulate the test database with the SQL queries. It is just the same here, only that our program is not the mysql server, in fact, it is a client. So Java needs a URL to connect. It is just like the URL we use in websites, for example:

https://google.com

The JDBC url is almost the same, only that it does not use the https:// schema, but the jdbc:// schema. The mysql server runs on our laptop, so we do not need an internet connection to connect to the mysql server. The default jdbc url for mysql server is:

jdbc:mysql://localhost/test

jdbc:mysql:// is the standard schema for mysql jdbc. Note that localhost/ is our laptop, and test is the database that we want to connect to. We can call this part the database url part. The next part of jdbc url is the question mark, yes, the question mark:

jdbc:mysql://localhost/test?

That means we request a connection to the test database. Note that other relational database’s jdbc urls will change a little bit. Say in postgresql, the line will be:

jdbc:postgresql://localhost/test?

Now, the next part will be username and password, without them mysql server will deny access to the test database.

In our example with test database, the line is simple:

user=testuser&password=Test@12345

Client connection to a SQL database

OK, enough said, we will create a connection now, in Test.java, we will create a connection with the DriverManager class, using the getConnection() static method. The getConnection() method require a parameter which is exactly the jdbc url we discussed above:

import java.sql.*;
class Test {
  static public void main(String[] args){
    //database part
    var db = "jdbc:mysql://localhost/test?";
    //username and password part
    var u = "user=testuser&password=Test@12345";
    try{
      //create the connection
      Connection conn = DriverManager
        .getConnection(db + u);
      //test the connection
      System.out.println(conn.isClosed());
    }catch (SQLException e){
      System.out.println("error" + e.toString());
    }
  }
}

Great, now run the code and expect an exception to be thrown. Yep, the SQLException. The reason is that we need the jdbc.jar that we downloaded from the MySQL website. How to do it? Two ways:

Using classpath

First way, we do it manually, we declare the driver (the jdbc.jar file) in the classpath. And make sure that the jdbc.jar stay in src folder in our current project folder. Then make sure that we at the src folder in the terminal:

$ java -cp jdbc.jar Test.java 

-cp is the short form for -classpath. Here the jdbc.jar stays in the parent folder of the src folder thus the path is ../jdbc.jar. We will have the output:

false

This is a signal that our connection to the test database works great.

Using Intellij 

Second way, in Intellij Idea (Community Edition) we open File/Project Structure.

In Project Settings/Libraries we click the + icon to add the jdbc.jar to libraries. Then we click the Run icon in the toolbar or Ctr + R and we have the same output. Note that the geConnection() method is overloaded, we can pass the db url, user and password in different parameters:

import java.sql.*;
class Test {
  static public void main(String[] args)
      throws SQLException {
    //database part
    var db = "jdbc:mysql://localhost/test";
    //username
    var user = "testuser";
    var password = "Test@12345";
    //create the connection with 3 parameters
    //db url, user, password
    Connection conn = DriverManager
      .getConnection(db, user, password);
    //test the connection
    System.out.println(conn.isClosed());
  }
}

Now with the connection we can start to connect to the test database and retrieve data.