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

By I Wayan Saryada in JDBC 5 Comments

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.4.0</version>
</dependency>

Maven Central

How do I retrieve values from ResultSet?

By I Wayan Saryada in JDBC 0 Comment

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.4.0</version>
</dependency>

Maven Central

How do I delete record from a table?

By I Wayan Saryada in JDBC 0 Comment

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.4.0</version>
</dependency>

Maven Central

How do I query records from a table?

By I Wayan Saryada in JDBC 0 Comment 
package org.kodejava.jdbc;

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

public class JdbcQueryExample {
    // Database connection information
    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) {
        // Get a connection to database.
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            // Create a statement object.
            Statement statement = connection.createStatement();

            // Executes a query command to select isbn and the book title
            // from books table. The execute query returns a ResultSet
            // object which is the result of our query execution.
            String query = "SELECT isbn, title, published_year FROM book";
            ResultSet books = statement.executeQuery(query);

            // To get the value returned by the statement.executeQuery we
            // need to iterate the books object until the last items.
            while (books.next()) {
                // To get the value from the ResultSet object we can call
                // a method that correspond to the data type of the column
                // in database table. In the example below we call
                // books.getString("isbn") to get the book's ISBN 
                // information.
                System.out.println(books.getString("isbn") + ", " +
                        books.getString("title") + ", " +
                        books.getInt("published_year"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

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

Maven Central

How do I insert a record into database table?

By I Wayan Saryada in JDBC 0 Comment

In this example you’ll learn how to create a program to insert data into a database table. To insert a data we need to get connected to a database. After a connection is obtained you can create a java.sql.Statement object from it, and using this object we can execute some query strings.

package org.kodejava.jdbc;

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

public class InsertStatementExample {
    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 statement object.
            Statement stmt = connection.createStatement();
            String sql = "INSERT INTO book (isbn, title, published_year) " +
                    "VALUES ('978-1617293566', 'Modern Java in Action', 2019)";

            // Call execute() method of the statement object and pass the
            // query.
            stmt.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Below is the script from creating the book table.

CREATE TABLE `book`
(
    `id`             bigint(20) unsigned                  NOT NULL AUTO_INCREMENT,
    `isbn`           varchar(50) COLLATE utf8_unicode_ci  NOT NULL,
    `title`          varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `published_year` int(11)                                       DEFAULT NULL,
    `price`          decimal(10, 2)                       NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci;

Maven Dependencies

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

Maven Central