How do I store BLOBs data into database?

Database BLOBs (Binary Large Objects) can be used to store any data, such as images, audio, or video files, for instance. This example shows you how we use JDBC library to store image in our database. To send the binary information to the database, we can call the PreparedStatement.setBinaryStream() method and pass the appropriate input stream and its size.

package org.kodejava.jdbc;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BlobDemo {
    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) {
        try (Connection conn =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            conn.setAutoCommit(false);

            String sql = "INSERT INTO product_image " +
                         "(product_id, name, description, image) VALUES (?, ?, ?, ?)";
            String image = "/uml-distilled-3rd-edition.jpg";

            try (PreparedStatement stmt = conn.prepareStatement(sql);
                 InputStream is = BlobDemo.class.getResourceAsStream(image)) {

                stmt.setLong(1, 1L);
                stmt.setString(2, "uml-distilled-3rd-edition.jpg");
                stmt.setString(3, "UML Distilled 3rd Edition");
                stmt.setBinaryStream(4, is);
                stmt.execute();

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

The structure of product_image table.

CREATE TABLE `product_image`
(
    `id`          bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `product_id`  bigint(20) unsigned NOT NULL,
    `name`        varchar(255)        NOT NULL,
    `description` varchar(255) DEFAULT NULL,
    `image`       blob,
    PRIMARY KEY (`id`),
    KEY `product_id` (`product_id`),
    CONSTRAINT `product_image_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
) ENGINE = InnoDB;

Maven Dependencies

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

Maven Central

How do I execute stored procedure?

Stored-procedure are user-generated functions or procedures that, once created in the database, it can be called by the client applications, such as Java application. In this example we’ll demonstrate how to use the JDBC java.sql.CallableStatement to call a stored procedure. The store procedure in this example is just for selecting records from the products table.

package org.kodejava.jdbc;

import java.sql.*;

public class CallableStatementDemo {
    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) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Create a CallableStatement to execute the GetAllProducts() 
            // procedure.
            CallableStatement stmt =
                    connection.prepareCall("{CALL Get_All_Products()}");

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getLong("id") + "\t"
                        + rs.getString("code") + "\t"
                        + rs.getString("name") + "\t"
                        + rs.getDouble("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Below is the stored procedure definition that was executed in the code above.

DELIMITER ;;
CREATE PROCEDURE `Get_All_Products`()
BEGIN
    SELECT * FROM product;
END;;
DELIMITER ;

When running the code snippet, the result will be something like this:

1   P0000001    UML Distilled 3rd Edition   25.0
3   P0000003    PHP Programming 20.0
4   P0000004    Longman Active Study Dictionary 40.0
5   P0000005    Ruby on Rails   24.0
6   P0000006    Championship Manager    0.0
7   P0000007    Transport Tycoon Deluxe 0.0
8   P0000008    Roller Coaster Tycoon 3 0.0
9   P0000009    Pro Evolution Soccer    0.0
10  P0000010    Data Structures, Algorithms 50.99

Maven Dependencies

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

Maven Central

How do I know the number of rows affected when updating data in database table?

In this example you can see how to get number of rows or records affected when we update records in the database. The executeUpdate() method of Statement or PreparedStatement return an integer value which tell us how many records was affected by the executed command.

Note that when the return value for executeUpdate() method is 0, it can mean one of two things: (1) the statement executed was an update statement that affected zero row, or (2) the statement executed was a DDL statement such as statement to create a table in a database.

package org.kodejava.jdbc;

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

public class HowManyRowExample {
    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) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            String sql = "UPDATE book SET title = ?, published_year = ? WHERE id = ?";

            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, "Java 8 in Action, First Edition");
            ps.setInt(2, 2014);
            ps.setLong(3, 1L);
            int rows = ps.executeUpdate();

            System.out.printf("%d row(s) updated!", rows);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The result of the code snippet above:

1 row(s) updated!

Maven Dependencies

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

Maven Central

How do I retrieve values from ResultSet?

Here is another example on how to read data from a ResultSet returned by executing an SQL query in a database.

We start by creating a connection to the database. Creates a PreparedStatement to execute a query to get some data from the books table.

After executing the PreparedStatement we will have a ResultSet object. To iterate all the data in the ResultSet we call the next() method in a while-loop. When no more record to read the method return false. The ResultSet object also provides some methods to read value of the fields, the name of the method is corresponded to the type of data stored on each field of the table.

To read data using the ResultSet‘s methods (e.g. getString(), getInt(), getFloat(), etc) we can either use the column name, or the column index of the field read in the SQL statement.

Let’s see the complete code snippet below:

package org.kodejava.jdbc;

import java.math.BigDecimal;
import java.sql.*;

public class ResultSetExample {
    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) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            String query = """
                    SELECT id,
                        isbn,
                        title,
                        published_year,
                        price
                    FROM book
                    """;

            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                // Read values using column name
                Long id = rs.getLong("id");
                String isbn = rs.getString("isbn");
                String title = rs.getString("title");
                int publishedYear = rs.getInt("published_year");

                // Read values using column index
                BigDecimal price = rs.getBigDecimal(5);

                System.out.printf("%s, %s, %s, %d, %.2f\n", id, isbn, title,
                        publishedYear, price);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

As an example, running the code above will give us the following output:

1, 978-1491910771, Head First Java: A Brain-Friendly Guide, 2022, 45.49
2, 978-1617293566, Modern Java in Action, 2019, 54.99

Maven Dependencies

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

Maven Central

How do I delete record from a table?

In this example we are showing you how to delete a record from table in the database. We use a standard JDBC library for this purpose. For the database we use MySQL, you can use any type of database you want. All you need to do is to find the JDBC driver for the database and configure it accordingly.

So here is the code example for deleting records from a table in a database.

package org.kodejava.jdbc;

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

public class DeleteRecordExample {

    public static void main(String[] args) {
        // This is our connection url to MySQL database, where jdbc is the
        // prefix for all jdbc connection. The mysql sub telling that we
        // are using MySQL database. Localhost is where our database is
        // resided and kodejava is our database name.
        String url = "jdbc:mysql://localhost/kodejava";

        // To connect to a database we will need a username and a password
        // for the database server to allow us to manipulate its data.
        String username = "kodejava";
        String password = "s3cr*t";

        // Then we ask a connection from the DriverManager by passing
        // the connection URL, the username and the password.
        try (Connection connection =
                     DriverManager.getConnection(url, username, password)) {

            // To delete records from tables we create an SQL delete command.
            // The question mark that we used in the where clause will be the
            // holder of value that will be assigned by PreparedStatement
            // class.
            String sql = "DELETE FROM book WHERE isbn = ?";
            String isbn = "9781617294945";

            // Create a statement object. We use PreparedStatement here.
            PreparedStatement statement = connection.prepareStatement(sql);

            // Pass a value of an ISBN that will tell the database which
            // record in the database to be deleted. Remember that when
            // using a statement object the index parameter is start from
            // 1 not 0 as in the Java array data type index.
            statement.setString(1, isbn);

            // Tell the statement to execute the command. The executeUpdate()
            // method for a delete command returns number of records deleted
            // as the command executed in the database. If no records was
            // deleted it will simply return 0
            int rows = statement.executeUpdate();

            System.out.println(rows + " record(s) deleted.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Maven dependencies

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

Maven Central