JDBC

Java Database Connectivity (JDBC) provides a standard interface for connecting Java applications to relational databases. This tutorial introduces the basics of JDBC, including setting up a connection, executing SQL queries, and handling results.

Setting up JDBC:

Include the JDBC Driver: Before you start, you need the JDBC driver specific to your database (e.g., MySQL, Oracle, PostgreSQL). This is typically distributed as a .jar file. Include it in your project's classpath. Driver Registration: Modern JDBC drivers are self-registering via the Java ServiceLoader mechanism, so manual driver registration is not usually needed. However, in older systems, you might see the following line:

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

JDBC Steps:

Get a Connection:

To connect to a database, you use the DriverManager.getConnection() method:

String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "mysql";
Connection conn = DriverManager.getConnection(url, username, password);

Execute Statements:

You can use the Statement or PreparedStatement classes to execute SQL queries.

Using Statement:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");

Using PreparedStatement (preferred when parameters are involved):

PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users WHERE age > ?");
pstmt.setInt(1, 18);  // set parameter 1 (the first "?") to value 18
ResultSet rs = pstmt.executeQuery();

Process the ResultSet:

For queries that return data, you'll handle a ResultSet:

while(rs.next()) {
  int id = rs.getInt("id");
  String name = rs.getString("name");
  System.out.println("ID: " + id + ", Name: " + name);
}

Close Resources:

It's crucial to close resources like Connection, Statement, and ResultSet when done to free up resources:

rs.close();
stmt.close();
conn.close();

In modern Java, you can use the try-with-resources statement to ensure resources are closed automatically:

try (Connection conn = DriverManager.getConnection(url, username, password);
  PreparedStatement pstmt = conn.prepareStatement("...")) {
  // Use connection and statement...
}

Tips:

Exercises

Setting up H2 with Maven:

To set up H2 in a Maven project, add the following dependency to your pom.xml:

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>1.4.200</version>
</dependency>

The version number may vary, so make sure you check for the latest version on the Maven repository.

String JDBC_DRIVER = "org.h2.Driver";
String DB_URL = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";

String USER = "sa";
String PASS = "";

Connection

Connection conn = null;
Statement stmt = null;
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PASS);

Statement

Statement stmt = null;
stmt = conn.createStatement();

Drop Table

String sqlDrop = "DROP TABLE TODO";
stmt.executeUpdate(sqlDrop)

Create Table

String sqlCreate =
"CREATE TABLE TODO " +
"(id INTEGER not NULL, " +
" task VARCHAR(255), " +
" PRIMARY KEY ( id ))";
stmt.executeUpdate(sqlCreate);

Insert into

stmt = conn.createStatement();
String sqlInsert = "INSERT INTO TODO VALUES (1,'afwassen')";
stmt.executeUpdate(sqlInsert);

Prepared Statement

PreparedStatement ps = conn.prepareStatement(
"INSERT INTO TODO VALUES( ?, ?)");
ps.setInt(1, 2);
ps.setString(2, "afdrogen");
ps.executeUpdate();

Select

stmt = conn.createStatement();
String sqlSelect = "SELECT id, task FROM TODO";
ResultSet rs = stmt.executeQuery(sqlSelect);

Process ResultSet

while (rs.next()) {
  int id = rs.getInt("id");
  String task = rs.getString("task");
  System.out.print("ID: " + id);
  System.out.println(", task: " + task);
}

Close

rs.close();
stmt.close();
conn.close();

MySQL

Now with MySql

Setting up MySQL JDBC with Maven:

To set up the MySQL JDBC driver in a Maven project, add the following dependency to your pom.xml:

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.23</version>
</dependency>

Make sure to check for the latest version on the Maven repository.

Establishing a JDBC Connection:

Before connecting, ensure you have a running MySQL server and you've created a database. Here's how you can establish a connection:

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

public class MySQLConnectionTest {
  public static void main(String[] args) {
    String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
    String jdbcUsername = "root"; // Replace with your MySQL username
    String jdbcPassword = "mysql"; // Replace with your MySQL password

    try {
      Connection connection = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);
      System.out.println("Connection to MySQL established!");
      connection.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}