Database Connection with JDBC in Java

In this example we will connect to a MariaDB database connection with JDBC to get the data from a SELECT query to a “Users” table. First of all, you should know that there are practices to make the recurring connection to a database more efficient, one of the most used is the use of connection coils, which allow you to optimize connections and are the standard method in business applications.

» See more examples

This example is for you to learn to make the connection to the database with JDBC in a basic way and it is not necessarily the optimal way since you can use more robust frameworks in large projects, this will depend on the needs of your project.

Create the database and a Users table

We have created a sample database called “Tests” with a single table.

This is the table we created:

CREATE TABLE Users (
  ID int(11) NOT NULL AUTO_INCREMENT,
  USERNAME varchar(100) NOT NULL,
  PASSWORD varchar(100) NOT NULL,
  NAME varchar(100) DEFAULT NULL,
  PRIMARY KEY (ID)
)

Let’s add some data:

For the Java code this is the Maven dependency for the MariaDB driver:

<dependency>
 <groupId>org.mariadb.jdbc</groupId>
 <artifactId>mariadb-java-client</artifactId>
 <version>2.2.3</version>
</dependency>

Let’s go to the code

The code is simple and we will quickly explain how it works:

package com.geekole;  // Name of your java package

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

/**
 *
 * @author geekole.com
 */
public class ConnectionMariaDB {

    private static final String DRIVER = "org.mariadb.jdbc.Driver";
    private static final String URL_CONNECTION = "jdbc:mariadb://localhost:3306/Tests";

    public static void main(String args[]) throws SQLException {
        final String user = "user_db";
        final String password = "password_db";
        Connection dbConnection = null;
        Statement statement = null;
        try {
            Class.forName(DRIVER);

            Connection conn = DriverManager.getConnection(URL_CONNECTION, user, password);
            String selectTableSQL = "SELECT ID,USERNAME,PASSWORD,NAME FROM Users";
            statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(selectTableSQL);
            while (rs.next()) {
                String id = rs.getString("ID");
                String usr = rs.getString("USERNAME");
                String psw = rs.getString("PASSWORD");
                String name = rs.getString("NAME");
                System.out.println("userid : " + id);
                System.out.println("usr : " + usr);
                System.out.println("psw : " + psw);
                System.out.println("name : " + name);
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (dbConnection != null) {
                dbConnection.close();
            }
        }
    }   
}

There are several things you should understand from this code. The first is that you must load in the Java environment, the driver that will be in charge of managing the connection with the database and there is one for each database.

private static final String DRIVER = “org.mariadb.jdbc.Driver”;

Class.forName(DRIVER);

The second is the connection URL. This is also formed depending on the database, although the format is similar for different databases.

private static final String URL_CONEXION = “jdbc:mariadb://localhost:3306/Tests”;

With the url, username and password, we create a connection.

Connection conn = DriverManager.getConnection(URL_CONNECTION, user, password);

With the connection, we create a statement and it is this statement that allows us to specify the form that our SQL query will take.

String selectTableSQL = “SELECT ID,USERNAME,PASSWORD,NAME FROM Users”;

statement = conn.createStatement();

ResultSet rs = statement.executeQuery(selectTableSQL);

The result is a ResultSet object that contains the data from our query.

while (rs.next()) {…}

You must be careful when retrieving the data of your query in Java variables, using the names of the columns that you have included in the query.

String id = rs.getString("ID");
tring usr = rs.getString("USERNAME");
String psw = rs.getString("PASSWORD");
String name = rs.getString("NAME");

When you run the code you will get the data from the Users table.

An important observation: You must close the sentence and the connection once you have stopped using them, this will prevent you from leaving connections open and access to the DB is blocked.

if (statement != null) { statement.close(); }

if (dbConnection != null) { dbConnection.close(); }

It is a very simple example to make a database connection with JDBC, you can update or insert new records. You can delete or create new tables, but we will see them in another example soon.

More about Java JDBC: https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/