Java Tutorial: Connecting to Database Using JDBC

Chapters

• Overview
• Connecting Java to MySQL Database
• Connecting Java to SQLite

Overview

The Java Database Connectivity (JDBC) API provides universal data access from the Java programming language. Using the JDBC API, you can access virtually any data source, from relational databases to spreadsheets and flat files. In this tutorial, we're going to use some classes in java.sql package in order to connect to a database.

Java can connect to many types of databases. In this tutorial, I'm gonna demonstrate on how to connect to MySQL and SQLite.

Connecting Java to MySQL Database

To connect our java application to a database, we need to create a connection between our application and the database. To do that, we're going to use Connection inferface. To establish a connection, we're going to use DriverManager class.

Let's demonstrate database connection between. Before running the example below, you must install MySQL first then create a database with the following table, columns and inserts:

Table Name: account_info
Columns
idtable1 INT (PRIMARY_KEY)
             (AUTO_INCREMENT)
             (UNSIGNED)
first_name: VARCHAR(25)
last_name: VARCHAR(25)

Inserts
1st row
firstname: Martin
lastname: Mayers

2nd row
firstname: Oliver
lastname: Crawford

Also, download JDBC Driver for MySQL (Connector/J). Connector/J can be included during installation of MySQL. Make sure your MySQL server is running. This example demonstrates java connecting to mysql.

import java.sql.*;

public class SampleClass{

  public static void main(String[] args){
  
    //in JDBC 3.9 and below, it's required
    //to explicity register a driver.
    //since JDBC 4.0 and later versions, 
    //it's not required to explicity register
    //a driver. Once the vendor jar is loaded
    //in our app, java will automatically
    //register the driver inside that jar file
    //Class.forName("com.mysql.jc.jdbc.Driver");
    
    try(
    Connection con = 
    DriverManager.getConnection
    ("jdbc:mysql://localhost:3306/mydb","root","root")){
      Statement statement = con.createStatement();
      ResultSet rs = 
      statement.executeQuery("SELECT * from account_info");
      
      System.out.println("Elements");
      while(rs.next())
        System.out.println
        (rs.getInt(1) + " | " +
         rs.getString(2) + " | " +
         rs.getString(3));
    }
    catch(Exception e){
      e.printStackTrace();
    }
    
  }
}

Result
Elements
1 | Martin | Mayers
2 | Oliver | Crawford

Note that the information in getConnection method shouldn't be written in the source code in production scenario for security purposes. It's only acceptable to write those information in source code during development phase.

Note: If you're using build tool like maven, you don't need to add a new classpath in order to use Connector/J JAR file.
Before running the example above, we will temporarily add a new classpath where Connector/J is located. Command syntax:
set classpath=[root]:\[path];
e.g.
set classpath=C:\MySQL\ConnectorJ\mysql-connector-java-8.0.28.jar;
Once the new classpath is added, we can execute the example above. Once we close cmd/terminal, number of classpaths in our system will return to normal. Now, let's discuss the example above. com.mysql.jc.jdbc.Driver denotes the location of Driver.class in Connector/J jar file. This location com.mysql.jdbc.Driver is deprecated.

If you're gonna bundle your program in a JAR file and you need to link another JAR file for example, mysql-connector.jar, you should read this article.

getConnection(String url, String user, String password) attempts to establish a connection to the given database URL. Returns a Connection object. This method has multiple forms which can be found in the documentation. user and password are the username and password that you put in your MySQL software. Next, let's examine the url in the getConnection() in the example above.

Let's examine this URL: jdbc:mysql://localhost:3306/mydb
jdbc is the connection type between our java app and mysql.
: is a separator.
mysql is the database software we're connected to.
//localhost is "//+hostname".
3306 is a port number. You can learn more about ports in this article.
/mydb is "/+database-name".

createStatement() Creates a Statement object for sending SQL statements to the database. createStatement() has more forms which can be found in the documentation. Once a Statement object is created, we need to write SQL statement. executeQuery() Executes the given SQL statement, which returns a single ResultSet object.

ResultSet is a table of data representing a database result set, which is usually generated by executing a statement that queries the database. next() method returns true if the cursor(selector or pointer) of the result set doesn't past beyond the max row count of the result set. Otherwise, it returns false.

getInt(1) returns an int whereas getString(2) returns a String. The arguments are the column index based on the database. In some database like MySQL, column index starts at 1.

There are different tyes of getters in the ResultSet interface that get SQL data based on their types. For example getInt() gets integer-based data of MySQL like INT data type of MySQL. getString() gets character sequence data type of MySQL like VARCHAR. More getters can be found in the ResultSet interface. Don't forget to close any database connection.

Connecting Java to SQLite

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. Unlike MySQL, SQLite doesn't need a server. Also, the licenses of SQLite and some of its connectors are less restrictive the MySQL.

You can go to the official website and download the latest version of SQLite. Alternatively, you can use SQLiteStudio if you don't have time setting up SQLite yourself.

To connect java to SQLite database, we need to download a connector. Go to this download page and select SQLite JDBC connection version that you want. At the time of this writing, the latest version is 3.36.0.3

Before executing the example below, create a database first. Name it dbtest and create the following tables, columns and data.

Table Name: account_info
Columns
idtable1 INTEGER (PRIMARY_KEY)
                 (AUTO_INCREMENT)
                 (UNIQUE)
first_name: VARCHAR
last_name: VARCHAR

Inserts
1st row
firstname: Martin
lastname: Mayers

2nd row
firstname: Oliver
lastname: Crawford

This example demonstrates java connecting to SQLite.

import java.sql.*;

public class SampleClass{

  public static void main(String[] args){
  
    try(
    Connection con = 
    DriverManager.getConnection
    ("jdbc:sqlite:C:/test/sqlitetest/dbtest.db")){
      Statement statement = con.createStatement();
      ResultSet rs = 
      statement.executeQuery("SELECT * from account_info");
      
      System.out.println("Elements");
      while(rs.next())
        System.out.println
        (rs.getInt(1) + " | " +
         rs.getString(2) + " | " +
         rs.getString(3));
    }
    catch(Exception e){
      e.printStackTrace();
    }
    
  }
}

Result
Elements
1 | Martin | Mayers
2 | Oliver | Crawford

Note: If you're using build tool like maven, you don't need to add a new classpath in order to use sqlite-jdbc JAR file.
Before running the example above, we will temporarily add a new classpath where sqlite-jdbc is located. Command syntax:
set classpath=[root]:\[path];
e.g.
set classpath=C:\test\sqlitetest\sqlite-jdbc-3.36.0.3.jar;
Once the new classpath is added, we can execute the example above. Once we close cmd/terminal, number of classpaths in our system will return to normal.

If you're gonna bundle your program in a JAR file and you need to link another JAR file for example, sqlite-jdbc.jar, you should read this article.

Next, let's examine this URL: jdbc:sqlite:C:/test/sqlitetest/dbtest.db
jdbc is the connection type between our java app and mysql.
: is a separator.
sqlite is the database software we're connected to.
C:/test/sqlitetest/dbtest.db is the path where dbtest.db is located.

In sqlite, if the database in the getConnection doesn't exist, it will create a new database with the specified name, Assuming you have permission to create files in the specified directory.

This example is very similar to the example in this topic. You should read my explanation there before reading this topic.