JDBC Basics – Overview

What is JDBC

Here is a small tutorial on how to get started with JDBC.

JDBC (Java Database Connectivity) is a Java API for connecting to relational databases and executing SQL statements. It provides a standard interface for Java applications to access and manipulate data stored in databases. JDBC enables Java developers to write database-independent code, making it easy to switch between different databases without changing the application code. The API includes a set of classes and interfaces for establishing connections, executing statements, and processing the results of the statements. With JDBC, Java applications can interact with databases, such as Oracle, MySQL, and Microsoft SQL Server.

Testing

If you need a database to test JDBC, I would suggest starting an in-memory database like H2 with SpringBoot or Quarkus or setting up a database with Docker.
For reference, I implemented an H2 database with SpringBoot in this project.

Statements

The statement is an object to execute SQL Statements. It provides methods for conducting various types of SQL statements, such as SELECT, INSERT, UPDATE, and DELETE.

Here is a short list of the most used methods for statements:

statement.execute(sql) – Normally for DDL like CREATE or DROP
statement.executeUpdate(sql) – Normally for DML like INSERT, UPDATE, DELETE
statement.executeQuery(sql) – Run SELECT query and return a ResultSet

In the following, there will be four examples of typical SQL-Commands, which are SELECT, INSERT, UPDATE, and DELETE.

If you are looking for a specific operation, and much more in detail, check the other blog posts here 🙂

In the following, you will see an example of a select statement

JDBC Select

Java

import java.sql.*;

public class JDBCExample {
    public static void main(String[] args) throws SQLException {
        // Load the JDBC driver
        Class.forName("com.mysql.cj.jdbc.Driver");

        // Connect to the database
        Connection conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname", "username", "password");

        // Execute an INSERT statement
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO employees (name) VALUES (?)");
        stmt.setString(1, "John Doe");
        stmt.executeUpdate();

        // Close the resources
        stmt.close();
        conn.close();
    }
}

JDBC Insert

In the following, you will see an example of an Insert statement

Java

import java.sql.*;

public class JDBCExample {
    public static void main(String[] args) throws SQLException {
        // Load the JDBC driver
        Class.forName("com.mysql.cj.jdbc.Driver");

        // Connect to the database
        Connection conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname", "username", "password");

        // Execute an INSERT statement
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO students (name) VALUES (?)");
        stmt.setString(1, "Max Mustermann");
        stmt.executeUpdate();

        // Close the resources
        stmt.close();
        conn.close();
    }
}

JDBC Update

In the following, you will see an example of an update statement

Java

import java.sql.*;

public class JDBCExample {
    public static void main(String[] args) throws SQLException {
        // Load the JDBC driver
        Class.forName("com.mysql.cj.jdbc.Driver");

        // Connect to the database
        Connection conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname", "username", "password");

        // Execute an UPDATE statement
        PreparedStatement stmt = conn.prepareStatement("UPDATE students SET name=? WHERE id=?");
        stmt.setString(1, "Frau Mustermann");
        stmt.setInt(2, 1);
        stmt.executeUpdate();

        // Close the resources
        stmt.close();
        conn.close();
    }
}

JDBC Delete

In the following, you will see an example of an delete statement

Java

import java.sql.*;

public class JDBCExample {
    public static void main(String[] args) throws SQLException {
        // Load the JDBC driver
        Class.forName("com.mysql.cj.jdbc.Driver");

        // Connect to the database
        Connection conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname", "username", "password");

        // Execute a DELETE statement
        PreparedStatement stmt = conn.prepareStatement("DELETE FROM students WHERE id=?");
        stmt.setInt(1, 1);
        stmt.executeUpdate();

        // Close the resources
        stmt.close();
        conn.close();
    }
}

These are just a few examples of the many ways that JDBC can be used to interact with a relational database. Other useful features of JDBC include the ability to use prepared statements for increased security, the ability to use transactions to ensure data consistency, and the ability to interact with stored procedures and functions.

It is important to note that, these are just examples and you should consider the best practices and security measures while working with JDBC like using Connection pooling, Proper exception handling, Prepared statements to prevent SQL injection attacks, etc.

In conclusion, JDBC is a powerful tool that allows Java developers to interact with relational databases easily. With its rich set of features and its support for a wide range of databases, it is an essential tool for any Java developer working with databases.

See for more blog posts about much more details on how to do these operations

What is JDBC

Testing

Statements

JDBC Select

JDBC Insert

JDBC Update

JDBC Delete

Related Articles

Kommentar verfassen Kommentieren abbrechen