How do I store BLOBs data into database?

Database BLOBs (Binary Large Objects) can be used to stored any data, it could be image, 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 it size.

package org.kodejava.example.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 = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection conn =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            conn.setAutoCommit(false);

            String sql = "INSERT INTO product_images " +
                "(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_images table.

CREATE TABLE `product_images` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image` blob,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `product_images_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB;

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I execute stored procedure?

Stored procedure are user-generated functions or procedures that, once created in the database, 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.example.jdbc;

import java.sql.*;

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

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

            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 `GetAllProducts`()
BEGIN 
    SELECT * FROM products;
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

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</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 database.

package org.kodejava.example.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 = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) throws Exception {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            String sql = "UPDATE books " +
                "SET title = ?, published_year = ? " +
                "WHERE isbn = ?";

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

            System.out.printf("%d row(s) updated!", rows);
        }
    }
}

The result of the code snippet above:

1 row(s) updated!

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

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 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 (eg. 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.example.jdbc;

import java.sql.*;

public class ResultSetExample {
    public static void main(String[] args) throws Exception {

        String url = "jdbc:mysql://localhost/kodejava";
        String username = "root";
        String password = "";

        try (Connection connection =
                 DriverManager.getConnection(url, username, password)) {

            String query = "SELECT isbn, title, published_year, price " +
                "FROM books";

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

                // Read values using column index
                double price = rs.getDouble(4);

                System.out.printf("%s, %s, %d, %.2f\n", isbn, title,
                    publishedYear, price);
            }
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central

How do I delete record from 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 database.

package org.kodejava.example.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
        // reside and kodejava is our database name.
        String url = "jdbc:mysql://localhost/kodejava";

        // To connect to a database we will need a user and password for
        // the database server to allow us to manipulate its database.
        String username = "root";
        String password = "";

        // Then we ask a connection from the DriverManager by passing
        // the connection URL 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 books WHERE isbn = ?";
            String isbn = "9781617294945";

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

            // Pass a value of a isbn that will tell the database which
            // records 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

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central