JDBC Connection example

JDBC (Java Database Connectivity) is an API provided by Oracle offering a standard for communication between java code and a wide range of databases based on SQL or files (flat files, spreadsheets).

You need a driver for each type of database. That driver is often developed and offered by the company or the open source project maintaining the DBMS (Database Management System) . It comes usually as jar archive that you include manually in your classpath or dynamically using a dependency management system like maven.

The following is a basic example of connection and interrogation of a MySQL database, the same code (with some changes) apply to other databases like Oracle DB,  MariaDB and Postgresql. These changes may include at least the driver name and in some cases  the SQL syntax.

(The explanation of the different steps will follow the code example )

package tutoref;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCMySQLExample {
 
 // Database URL and Driver
 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
 static final String DB_URL = "jdbc:mysql://localhost/TUTOREFDB";
 
 //  Database username and password
 static final String DB_USERNAME ="username";
 static final String DB_PASSWORD ="password";

 public static void main(String[] args) {
  
  try {
   
   // registering the driver
   Class.forName(JDBC_DRIVER);
   
   // creating a connection
   Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
   
   // creating a normal statement
   Statement statement = connection.createStatement();
   
   // execute a SELECT query and create a ResultSet that holds the results
   String query="SELECT * from USERS";
   ResultSet resultSet = statement.executeQuery(query);
   
   // iterating through the ResultSet (Display results)
   while(resultSet.next()){
    System.out.println(resultSet.getString("username"));
   }
   
   // create a prepared INSERT query
   String preparedQuery="INSERT INTO USERS(username, password, email, first_name, last_name) VALUES(?,?,?,?,?)";
   PreparedStatement preparedStatement = connection.prepareStatement(preparedQuery);
   
   // fill the values
   preparedStatement.setString(1, "john.doe"); // username, the 1st index=1 and not 0
   preparedStatement.setString(2, "123demo"); // password
   preparedStatement.setString(2, "john.doe@tutoref.com"); // email
   preparedStatement.setString(2, "John"); // first name
   preparedStatement.setString(2, "Doe"); // last name
   
   // execute the query
   boolean success=preparedStatement.execute();

   // free the resources
   if(resultSet != null){
    resultSet.close();
   }
   if(statement!=null){
       statement.close();
   }
   if(preparedStatement!=null){
    preparedStatement.close();
   }
   if(connection != null){
    connection.close();
   }
   
  } catch (ClassNotFoundException | SQLException e) {
   e.printStackTrace();
  }
 }

}

 

Explanation

Registering the driver

Most of the database specific classes are included in the provided driver. You need to register the driver in order to be able to create a connection.

Class.forName("com.mysql.jdbc.Driver");

Creating a connection

The connection object is the link ensuring the communication between the code and the database, you must get a connection to be able to interrogate the database.

The connection object is obtained via the static method getConnection() of the class DriverManager.

Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

Creating a Statement

A statement object is obtained from the connection object, it is used for executing a static SQL statement and returning the results it produces.

Statement statement = connection.createStatement();

CREATING a Prepared STATEMENT

In some cases it is more suitable to use a Prepared Statement in the place of a static Statement. A common example is when you want to protect your application against the SQL Injections.

To obtain a prepared statement you must use one of the prepareStatement(…) static methods of the class DriverManager, and specify a query containing interrogation symbols in the place of values.

You have to specify the values before you execute the query and get the results.

PreparedStatement preparedStatement=connection.prepareStatement(
"INSERT INTO USERS(username, password, email, first_name, last_name) VALUES(?,?,?,?,?)")
   
// fill the values
preparedStatement.setString(1, "john.doe"); // username, the 1st index=1 and not 0
preparedStatement.setString(2, "123demo"); // password
preparedStatement.setString(2, "john.doe@tutoref.com"); // email
preparedStatement.setString(2, "John"); // first name
preparedStatement.setString(2, "Doe"); // last name

Executing the query and getting the results

When your Statement or Prepared Statement is ready, you can execute the query and get the results.

If you execute a query of type SELECT, you must use the executeQuery method of the statement object, the results are returned inside an iterable object of type ResultSet.

ResultSet resultSet = statement.executeQuery(query);

while(resultSet.next()){
  System.out.println(resultSet.getString("username"));
}

The above code will print the usernames on the console.

If you execute a query of type UPDATE, DELETE or INSERT, you can use the execute() method, which returns true if the first result is a ResultSet object, and false if the first result is an update count or there is no result. You also have the possibility to use the executeUpdate() method, that returns either  the number of deleted, updated or inserted rows or 0 for SQL statements that return nothing.

Freeing the resources

When you work with databases, you have to close the resources manually, otherwise you can have problems with the DBMS limitations. Elements having to be closed include (in this order) , the ResultsSets, The statements and the connection.