JDBC - Learn Java by Examples

How do I set up JDBC in a Java project?

By Wayan in JDBC Last modified: January 4, 2026 0 Comment

Setting up JDBC (Java Database Connectivity) involves a few key steps: adding the database driver to your project, establishing a connection, and executing your SQL statements.

1. Add the JDBC Driver Dependency

First, you need the driver for your specific database (e.g., MySQL, PostgreSQL, H2) in your pom.xml.

For MySQL, you would add:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>9.1.0</version>
</dependency>

2. Establish a Connection

You use the DriverManager.getConnection() method. It requires a Connection URL, username, and password.

URL Format: jdbc:mysql://[host]:[port]/[database_name]
Modern Java Tip: You no longer need to call Class.forName("com.mysql.cj.jdbc.Driver") in modern JDBC (4.0+).

3. Use Try-With-Resources

Always use try-with-resources to ensure that the Connection, Statement, and ResultSet are closed automatically, preventing memory leaks.

Basic Example

Here is a concise template to get you started:

package org.kodejava.jdbc;

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

public class JdbcSetup {
    private static final String URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        String query = "SELECT id, name FROM users";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name"));
            }
        } catch (SQLException e) {
            System.err.println("Connection failed!");
            e.printStackTrace();
        }
    }
}

How do I update records in MS Access database?

By Wayan in JDBC, Microsoft Access Last modified: July 16, 2024 0 Comment

In this post, we will learn how to update records in a database using the Java Database Connectivity (JDBC) API. JDBC is a Java API which is used to connect and execute query in the database.

We will be working with an MS Access database file named musicdb.accdb for this example. Our goal is to update an album’s title and release date in our album table.

The following code snippet show you how to do it. The selectAlbum() method is just a helper method to show the album data before and after the data was updated.

package org.kodejava.jdbc;

import java.sql.*;
import java.time.LocalDate;
import java.time.Month;

public class MSAccessUpdate {
    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
        try (Connection conn = DriverManager.getConnection(url)) {
            conn.setAutoCommit(false);
            selectAlbum(conn, 2L);

            String sql = """
                    update album
                        set title = ?,
                        release_date = ?
                    where id = ?
                    """;

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "A Hard Day's Night");
            ps.setDate(2, Date.valueOf(LocalDate.of(1964, Month.JULY, 10)));
            ps.setLong(3, 2);

            int rowCount = ps.executeUpdate();
            System.out.printf("%s (rows) updated.%n", rowCount);

            selectAlbum(conn, 2L);

            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Executes a SQL query to select an album from the database based on the
     * provided ID.
     *
     * @param conn the Connection object representing the database connection
     * @param id   the ID of the album to be selected
     * @throws SQLException if a database access error occurs or the SQL query
     * is invalid
     */
    private static void selectAlbum(Connection conn, Long id) throws SQLException {
        String sql = "select * from album where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.printf("id=%s, title=%s, released_date=%s%n",
                    rs.getLong("id"), rs.getString("title"),
                    rs.getDate("release_date"));
        }
    }
}

The following is an output produced by the code snippet above:

id=2, title=With the Beatles, released_date=1963-11-22
1 (rows) updated.
id=2, title=A Hard Day's Night, released_date=1964-07-10

Maven Dependencies

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.1</version>
</dependency>

How do I insert a record into Microsoft Access database and get the generated key?

By Wayan in JDBC, Microsoft Access Last modified: July 15, 2024 0 Comment

In this tutorial, we’ll guide you through the process of inserting records into an MS Access Database. In the code snippet below we are using the UCanAccess JDBC driver, you can find the Maven dependencies at the end of this tutorial.

Setting Up The Connection

Firstly, we need to set up a connection to our MS Access database. This is done by specifying the path of your database file (.mdb or .accdb) in the JDBC URL and then getting a connection from DriverManager.

String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
try (Connection connection = DriverManager.getConnection(url)) {
    ...
} catch (SQLException e) {
    e.printStackTrace();
}

Preparing the SQL Statement

Next step is to prepare our SQL insert statement. We use a PreparedStatement – it handles the tedious task of substituting the respective parameters into the placeholders ("?") in our SQL statement.

String sql = "insert into album (title, release_date, created) values (?, ?, ?)";

PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

Setting Parameters and Executing the SQL Statement

After defining our SQL statement with placeholders, we replace these placeholders with actual values using the setter methods (setInt(), setString(), setDate(), etc.) provided by the PreparedStatement class. Once, the placeholders have been replaced with actual values, we call executeUpdate() method on the PreparedStatement object to execute the SQL statement.

ps.setString(1, "With the Beatles");
ps.setDate(2, Date.valueOf(LocalDate.of(1963, Month.NOVEMBER, 22)));
ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
int rowCount = ps.executeUpdate();
System.out.println("rowCount = " + rowCount);

In the code snippet above we set all placeholders with values we want to insert into the database, and then the statement is executed. The new record gets added to the database. We also keep the rowCount returned by the executeUpdate() method.

Get the Generated Key Value

When creating the PreparedStatement object we also passes the Statement.RETURN_GENERATED_KEYS parameter, this will allow us to get the generated key from the statement object after executing the query.

ResultSet generatedKeys = ps.getGeneratedKeys();
long generatedKey = 1L;
while (generatedKeys.next()) {
    generatedKey = generatedKeys.getLong(1);
}
System.out.println("Generated Key = " + generatedKey);

The Entire Code Snippet

Here is the entire Java class.

package org.kodejava.jdbc;

import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.Month;

public class MSAccessInsert {
    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
        try (Connection connection = DriverManager.getConnection(url)) {
            String sql = "insert into album (title, release_date, created) values (?, ?, ?)";

            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, "With the Beatles");
            ps.setDate(2, Date.valueOf(LocalDate.of(1963, Month.NOVEMBER, 22)));
            ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));

            int rowCount = ps.executeUpdate();
            System.out.println("rowCount = " + rowCount);

            ResultSet generatedKeys = ps.getGeneratedKeys();
            long generatedKey = 1L;
            while (generatedKeys.next()) {
                generatedKey = generatedKeys.getLong(1);
            }
            System.out.println("Generated Key = " + generatedKey);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The code above inserts a new record into the album table in the Access database. Remember to handle any SQLException that may be thrown while interacting with the database.

And that’s it! You have successfully inserted a record into an MS Access database using UCanAccess in Java. Whether you’re creating a small application or dealing with large-scale data management, UCanAccess is an easy, efficient way to work with MS Access databases in Java applications.

Maven Dependencies

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.1</version>
</dependency>

How do I select a record from Microsoft Access Database?

By Wayan in JDBC, Microsoft Access Last modified: July 15, 2024 0 Comment

UCanAccess is a pure Java JDBC Driver implementation which allows Java developers and JDBC client programs to read and write Microsoft Access database files (.mdb and .accdb).

In this tutorial, we will demonstrate how to configure a project with UCanAccess and create a simple Java application to select data from an MS Access database.

Establishing a Connection

Before you can interact with a database, you need to establish a connection to it. The following is an example of how to establish a connection using UCanAccess:

String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb;";
try (Connection connection = DriverManager.getConnection(url)) {
    System.out.println("connection = " + connection);
    // Rest of the code goes here...
} catch (SQLException e) {
    e.printStackTrace();
}

In the code above, we create a connection URL using the absolute path to our Access database file. Then, we obtain a connection object by calling DriverManager.getConnection(url).

Fetching Data from the Database

Once the connection is established, we can execute SQL queries against the database. Here, let’s create a query to select some data:

String query = "select id, title, release_date from album where id = ?";

PreparedStatement ps = connection.prepareStatement(query);
ps.setLong(1, 1L);

ResultSet rs = ps.executeQuery();
while (rs.next()) {
    System.out.printf("id=%s, title=%s, released_date=%s%n",
            rs.getLong("id"), rs.getString("title"),
            rs.getDate("release_date"));
}

In this code, we create a PreparedStatement object, which enables us to execute parameterized SQL queries in a secure and efficient manner. Here, our SQL query includes a parameter, represented by the ? symbol, which we then set using setLong(). This would replace the ? with the value 1L.

We then execute our query by calling executeQuery() on the PreparedStatement object which returns a ResultSet object. This object represents the result set of the query.

We then loop through the result set and print each record using rs.next(), which is used to iterate through the ResultSet.

Full Code

package org.kodejava.jdbc;

import java.sql.*;

public class MSAccessSelect {
    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb;";
        try (Connection connection = DriverManager.getConnection(url)) {
            System.out.println("connection = " + connection);

            String query = "select id, title, release_date from album where id = ?";

            PreparedStatement ps = connection.prepareStatement(query);
            ps.setLong(1, 1L);

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.printf("id=%s, title=%s, released_date=%s%n",
                        rs.getLong("id"), rs.getString("title"),
                        rs.getDate("release_date"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Remember to handle SQL exceptions that might be thrown during the interaction with the database. And there you have it! Now you know how to work with MS Access Database using UCanAccess in Java. Happy coding!

Maven Dependencies

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.1</version>
</dependency>

Java Database Connectivity (JDBC) – A Simple Tutorial for Students

By Lydia Havens in General Last modified: September 21, 2023 0 Comment

Welcome, future developers! If you’re looking to connect your Java applications with databases, you’re in the right place. Java Database Connectivity, commonly known as JDBC, is the bridge that links them together. It’s a powerful tool in a developer’s toolkit, allowing seamless interaction between Java and various databases. Dive into this tutorial, and by the end, you’ll have a clear understanding of the basics. Let’s embark on this journey together!

JDBC Architecture

When you think of JDBC, visualize it as a bridge. This bridge connects your Java application to a database. Central to this are the JDBC Drivers – they facilitate the actual connection. As for the inner workings, the JDBC API provides key components: DriverManager manages a list of database drivers, Connection connects to the actual database, Statement lets you run SQL queries, ResultSet fetches results, and PreparedStatement helps with pre-compiled SQL statements.

Setting Up Your Environment

Before we dive deep, let’s set the stage. First, you’ll need to install a JDBC driver specific to the database you’re using. Now, balancing coding with school can be overwhelming. If you’re swamped with assignments, consider using services like Essay Pro to manage your workload. This way, you can allocate more time to coding. Once you’re set, proceed to set up a database for testing. This foundation is crucial for hands-on learning ahead.

Establishing a Connection

First things first: to chat with our database using Java, we need to establish a connection. Begin by loading the JDBC driver – think of it as dialing a friend’s number. Once that’s done, you can actually ‘call’ or connect to the database. It’s like establishing a direct line of communication between your Java code and the database.

try (Connection connection = 
        DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
        ...
} catch (Exception e) {
    e.printStackTrace();
}

Executing SQL Statements

Now that we’re connected, let’s converse! Start by creating a Statement object. This is your tool to “speak” SQL. Whether you want to ask the database a question (query) or tell it to do something (update), the Statement object has your back.

Statement statement = connection.createStatement();
String query = "SELECT * FROM book";
ResultSet books = statement.executeQuery(query);

Working with ResultSet

So, you’ve asked your database a question. Where’s the answer? Enter ResultSet. As you iterate through the ResultSet, it’s like flipping through the pages of a book, gathering the information you asked for. Want specifics? You can retrieve data based on the column, making it easy to pinpoint exactly what you’re after.

while (books.next()) {
    System.out.println(books.getString("isbn") + ", " +
            books.getString("title") + ", " +
            books.getInt("published_year"));
}

Using PreparedStatement

Imagine sending a tailor-made invitation; it’s more efficient and safer. That’s what PreparedStatement offers over regular statements. This nifty tool lets you create SQL statements with placeholders, reducing errors and speeding things up. Once your structure is set, you can easily customize the message by filling in specific parameters. It’s like having a template for smoother, more personalized database interactions.

String orderDetailQuery = """
        INSERT INTO purchase_order_detail (order_id, product_id, quantity, price)
        VALUES (?, ?, ?, ?)
        """;

PreparedStatement detailStmt = conn.prepareStatement(orderDetailQuery);
detailStmt.setLong(1, 1L);
detailStmt.setInt(2, 1);
detailStmt.setInt(3, 10);
detailStmt.setBigDecimal(4, new BigDecimal("29.99"));
detailStmt.execute();

Handling SQL Exceptions

Even in the world of coding, things don’t always go as planned. When your Java application and the database have a miscommunication, SQL exceptions occur. But don’t fret! By catching an SQLException, you’re prepared to deal with these hiccups. This allows you to gracefully handle any bumps in the conversation. And the best part? You can retrieve specific error information, give you insights to troubleshoot and keep the conversation flowing smoothly.

Batch Processing with JDBC

Ever thought of sending multiple messages at once instead of one by one? That’s batch processing for you. In JDBC, this means executing multiple SQL commands in a single go. The perks? Faster operations and reduced server round trips. With addBatch() you line up your messages, and executeBatch() sends them all together in a neat package.

connection.setAutoCommit(false);
try (Statement statement = connection.createStatement()) {
    statement.addBatch("INSERT INTO product (code, name) " +
            "VALUE ('P0000006', 'Championship Manager')");
    statement.addBatch("INSERT INTO product (code, name) " +
            "VALUE ('P0000007', 'Transport Tycoon Deluxe')");

    int[] updateCounts = statement.executeBatch();
    System.out.println("updateCounts = " + Arrays.toString(updateCounts));
    connection.commit();
} catch (SQLException e) {
    connection.rollback();
    e.printStackTrace();
}

Transactions in JDBC

Think of a transaction as a promise. You’re telling the database, “I’ll make a series of changes, and if everything goes smoothly, let’s finalize them.” If something’s amiss, you can revert to the start, ensuring data integrity. This commitment is done using the ‘commit’ command. However, if there’s an issue, ‘rolling back’ undoes the changes, keeping your data safe and sound.

try (Connection conn =
             DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
    conn.setAutoCommit(false);

    String orderQuery = """
            INSERT INTO purchase_order (username, order_date)
            VALUES (?, ?)
            """;

    try (PreparedStatement stmt = conn.prepareStatement(orderQuery,
            PreparedStatement.RETURN_GENERATED_KEYS)) {
        stmt.setString(1, "jduke");
        stmt.setDate(2, new Date(System.currentTimeMillis()));
        stmt.execute();

        ResultSet keys = stmt.getGeneratedKeys();
        long orderId = 1L;
        if (keys.next()) {
            orderId = keys.getLong(1);
        }

        // This is an invalid statement that will cause exception to
        // demonstrate a rollback.
        String orderDetailQuery = """
                INSERT INTO purchase_order_detail (order_id, product_id, quantity, price)
                VALUES (?, ?, ?, ?)
                """;

        PreparedStatement detailStmt = conn.prepareStatement(orderDetailQuery);
        detailStmt.setLong(1, orderId);
        detailStmt.setInt(2, 1);
        detailStmt.setInt(3, 10);
        detailStmt.setBigDecimal(4, new BigDecimal("29.99"));
        detailStmt.execute();

        // Commit transaction to mark it as a success database operation
        conn.commit();
        System.out.println("Transaction commit...");
    } catch (SQLException e) {
        // Rollback any database transaction due to exception occurred
        conn.rollback();
        System.out.println("Transaction rollback...");
        e.printStackTrace();
    }
} catch (Exception e) {
    e.printStackTrace();
}

Connection Pooling

Remember those kiddie pools filled with balls? Connection pooling is kinda like that, but for database connections. Instead of making a new connection each time, you just grab one from the pool. It’s faster and conserves resources. When you’re done, toss it back! And setting up? It’s a one-time thing to ensure a reservoir of ready connections.

Closing Resources and Best Practices

Always tidy up after a chat! In JDBC, this means closing the Connection, Statement, and ResultSet to free up resources. Ever heard of the try-with-resources statement? It’s a Java gem that ensures resources are closed properly. And a quick tip: if managing JDBC feels overwhelming with your academic load, consider the best dissertation writing service to help with school, so you can focus on coding right.

try (Connection conn =
             DriverManager.getConnection(URL, USERNAME, PASSWORD);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM product")) {

    while (rs.next()) {
        String code = rs.getString("code");
        String name = rs.getString("name");

        System.out.println("Code: " + code + "; Name: " + name);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

JDBC Limitations and Alternatives

JDBC is great, but it’s not perfect. For complex applications, it might feel a bit low-level or verbose. Enter ORM tools. They offer a more intuitive way to interact with databases, abstracting away much of the SQL. Think of them as an evolved, sophisticated version of JDBC for certain use cases.

Summary and Next Steps

And that’s a wrap! You’ve journeyed through JDBC’s landscape, grasped its core, and glimpsed its limitations. What’s next? Dive deeper, explore more advanced resources, and keep building. The coding world awaits your creations!