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

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.

JDBC Architecture Diagram

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!

How do I export MySQL database schema into markdown format?

The following code example demonstrate how to export MySQL database schema into markdown table format. We get the table structure information by executing MySQL’s DESCRIBE statement.

The steps we do in the code snippet below:

  • Connect to the database.
  • We obtain the list of table name from the database / schema.
  • Executes DESCRIBE statement for each table name.
  • Read table structure information such as field, type, null, key, default and extra.
  • Write the information into markdown table format and save it into table.md.

And here are the complete code snippet.

package org.kodejava.jdbc;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DescribeMySQLToMarkDown {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        String tableQuery = """
                select table_name
                from information_schema.tables
                where table_schema = 'kodejava'
                  and table_type = 'BASE TABLE'
                order by table_name;
                """;

        try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            Statement stmt = connection.createStatement();
            ResultSet resultSet = stmt.executeQuery(tableQuery);
            List<String> tables = new ArrayList<>();
            while (resultSet.next()) {
                tables.add(resultSet.getString("table_name"));
            }

            System.out.println(tables.size() + " tables found.");

            try (BufferedWriter writer = new BufferedWriter(new FileWriter("table.md"))) {
                for (String table : tables) {
                    System.out.println("Processing table: " + table);
                    Statement statement = connection.createStatement();
                    ResultSet descResult = statement.executeQuery("DESCRIBE " + table);

                    writer.write(String.format("Table Name: **%s**%n%n", table));
                    writer.write("| Field Name | Data Type | Null | Key | Default | Extra |\n");
                    writer.write("|:---|:---|:---|:---|:---|:---|\n");
                    while (descResult.next()) {
                        String field = descResult.getString("field");
                        String type = descResult.getString("type");
                        String nullInfo = descResult.getString("null");
                        String key = descResult.getString("key");
                        String defaultInfo = descResult.getString("default");
                        String extra = descResult.getString("extra");
                        String line = String.format("| %s | %s | %s | %s | %s | %s |%n",
                                field, type, nullInfo, key, defaultInfo, extra);
                        writer.write(line);
                    }
                    writer.write("\n<br/>\n<br/>\n");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code snippet will produce something like below. I have tidy up the markdown for a better presentation.

Table Name: **books**

| Field Name | Data Type       | Null | Key | Default | Extra          |
|:-----------|:----------------|:-----|:----|:--------|:---------------|
| id         | bigint unsigned | NO   | PRI | null    | auto_increment |
| isbn       | varchar(30)     | NO   |     | null    |                |

<br/>
<br/>

Maven dependencies

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

Maven Central

How do I read MySQL data from Android using JDBC?

This example show you how to connect and read data from MySQL database directly from Android. The following steps and code snippet will show you how to do it.

Add the MySQL JDBC driver into your project dependencies. Open the app/build.gradle file and add the dependency.

...
...

dependencies {
    ...
    ...
    implementation 'mysql:mysql-connector-java:5.1.49'
}

If you want to connect to MariaDB you can change the JDBC driver dependency using 'org.mariadb.jdbc:mariadb-java-client:1.8.0', also update the JDBC url in the code snippet by replacing mysql with mariadb.

Next, add internet permission to our application in AndroidManifest.xml file.

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
          package="org.kodejava.android">

    <uses-permission android:name="android.permission.INTERNET" />

    ...
    ...

</manifest>

Let’s connect, read data from the database and display the information on the screen. In the code snippet we create an AsyncTask to read the information from the database. In the doInBackground() method we open a connection to the database, create a PreparedStatement, execute a query, get a ResultSet and read the information from it. We pack the data into a Map and return it.

After the doInBackground() method finish its execution the onPostExecute() method will be called. In this method we take the result, the Map returned by the doInBackground() method, and set the values into the TextView components for display.

package org.kodejava.android;

import android.annotation.SuppressLint;
import android.os.AsyncTask;
import android.util.Log;
import android.widget.TextView;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

public class MainActivity extends AppCompatActivity {
    private static final String URL = "jdbc:mysql://192.168.0.107:3306/kodejava";
    private static final String USER = "kodejava";
    private static final String PASSWORD = "kodejava";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        new InfoAsyncTask().execute();
    }

    @SuppressLint("StaticFieldLeak")
    public class InfoAsyncTask extends AsyncTask<Void, Void, Map<String, String>> {
        @Override
        protected Map<String, String> doInBackground(Void... voids) {
            Map<String, String> info = new HashMap<>();

            try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
                String sql = "SELECT name, address, phone_number FROM school_info LIMIT 1";
                PreparedStatement statement = connection.prepareStatement(sql);
                ResultSet resultSet = statement.executeQuery();
                if (resultSet.next()) {
                    info.put("name", resultSet.getString("name"));
                    info.put("address", resultSet.getString("address"));
                    info.put("phone_number", resultSet.getString("phone_number"));
                }                
            } catch (Exception e) {
                Log.e("InfoAsyncTask", "Error reading school information", e);
            }

            return info;
        }

        @Override
        protected void onPostExecute(Map<String, String> result) {
            if (!result.isEmpty()) {
                TextView textViewName = findViewById(R.id.textViewName);
                TextView textViewAddress = findViewById(R.id.textViewAddress);
                TextView textViewPhoneNumber = findViewById(R.id.textViewPhone);

                textViewName.setText(result.get("name"));
                textViewAddress.setText(result.get("address"));
                textViewPhoneNumber.setText(result.get("phone_number"));
            }
        }
    }
}
  • Finally, here is the screenshot of our Android application.
Android - MySQL JDBC

Android – MySQL JDBC

The complete source code can be accesses in our GitHub repository here: android-mysql-example.

How do I create MySQL database programmatically in Java?

There are times that you might need to create a database or tables right after you run your program instead of manually creating it. In this example, I will show you how you can do this using JDBC and MySQL database. The first thing we need to do as usual when creating a JDBC program is to define a JDBC URL. One thing that you’ll notice here is that we don’t define the database name in the URL. So the URL will be like jdbc:mysql://localhost.

After defining the URL, we need to create a connection to the database. We issued the DriverManager.getConnection() method and pass the URL, username and password as the arguments. The next step is to create a PreparedStatement. When we call the preparedStatement() method we pass an SQL command to create the database, which is CREATE DATABASE IF NOT EXISTS demodb. This will create a new database if there is no database with demodb name exists in the database. Finally, call the PreparedStatement‘s execute() method

Now you can try for your self, start typing the following code snippet in your text editor or IDE, and execute it to create the database.

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class CreateMySQLDatabaseExample {
    public static void main(String[] args) {
        // Defines the JDBC URL. As you can see, we are not specifying
        // the database name in the URL.
        String url = "jdbc:mysql://localhost";

        // Defines username and password to connect to database server.
        String username = "root";
        String password = "root";

        // SQL command to create a database in MySQL.
        String sql = "CREATE DATABASE IF NOT EXISTS demodb";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

After you are executing the code snippet above you will find a new database named demodb created in your MySQL database server.

Maven Dependencies

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

Maven Central

How to establish connection to a database using Properties object?

In the following code snippet, you will see how to pass some connection arguments when connecting to a database. To do this, we can use the java.util.Properties class. We can put some key value pairs as a connection arguments to the Properties object
before we pass this information into the DriverManager class.

Let’s see the example below:

package org.kodejava.jdbc;

import java.sql.*;
import java.util.Properties;

public class GetConnectionWithProperties {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        GetConnectionWithProperties demo = new GetConnectionWithProperties();
        try (Connection connection = demo.getConnection()) {
            // do something with the connection.
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM product");
            while (rs.next()) {
                System.out.println("Code = " + rs.getString("code"));
                System.out.println("Name = " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Connection getConnection() throws SQLException {
        Properties connectionProps = new Properties();
        connectionProps.put("user", USERNAME);
        connectionProps.put("password", PASSWORD);

        Connection connection = DriverManager.getConnection(URL, connectionProps);
        System.out.println("Connected to database.");
        return connection;
    }
}

Maven Dependencies

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

Maven Central