Statement - Learn Java by Examples

How do I make updates in updatable ResultSet?

By I Wayan Saryada in JDBC Last modified: May 31, 2024 0 Comment

Using an updatable result set enables our program to update record in the database from the ResultSet object. The operation on the ResultSet object can be updated, inserted or deleted. With this mechanism, we can update a database without executing a query.

In the example below we have a product table with the id, code, name, and price columns. In the first step after we load the result set, we update the product name of the first record. Then we move to the next record and delete it. At last, we insert a new record to a database.

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 UpdatableResultSetDemo {
    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 an updatable result set. It means that instead of
            // using a separate sql command to update the data, we can
            // update it directly in the result set object.
            //
            // What makes it updatable is because, when creating the
            // statement, we ask the connection object to create a statement
            // with CONCUR_UPDATABLE. The updatable doesn't need to be
            // TYPE_SCROLL_SENSITIVE, but adding this parameter to the
            // statement enables us to go back and forth to update the data.
            Statement statement = connection.createStatement(
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            String query = "SELECT id, code, name, price FROM product";
            ResultSet rs = statement.executeQuery(query);

            System.out.println("id\tcode\tname\tprice");

            while (rs.next()) {
                System.out.println(rs.getLong("id") + "\t"
                                   + rs.getString("code") + "\t"
                                   + rs.getString("name") + "\t"
                                   + rs.getDouble("price"));
            }

            // Move to the first row and update the result set data. After
            // we update the row value, we call the updateRow() method to
            // update the data in the database.
            rs.first();
            rs.updateString("name", "UML Distilled 3rd Edition");
            rs.updateRow();

            // Move to the next result set row and delete the row in the
            // result set and apply it to the database.
            rs.next();
            rs.deleteRow();

            // Insert a new row in the result set object with the
            // moveToInsertRow() method. Supply the information to be
            // inserted and finally call the insertRow() method to insert
            // record to the database.
            rs.moveToInsertRow();
            rs.updateString("code", "P0000010");
            rs.updateString("name", "Data Structures, Algorithms");
            rs.updateDouble("price", 50.99);
            rs.insertRow();

            rs.beforeFirst();
            System.out.println();
            System.out.println("id\tcode\tname\tprice");

            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();
        }
    }
}

The code snippet prints out the following output:

id  code    name    price
1   P0000001    Java 2 Notebook 25.0
2   P0000002    Java Servlet Programming    30.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

id  code    name    price
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.4.0</version>
</dependency>

How do I create a batch update in JDBC?

By I Wayan Saryada in JDBC Last modified: May 31, 2024 0 Comment

A batch statement can be used to execute multiple update commands as single unit in a database manipulation. This statement in the database is not executed one by one but as a single execution instead. In some cases, using a batch update can be more efficient than to execute the commands separately.

In this example, you are shown how to create a batch command to insert some products into a database table.

package org.kodejava.jdbc;

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

public class JDBCBatchExample {
    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)) {

            // Turn of the auto-commit mode
            connection.setAutoCommit(false);

            try (Statement statement = connection.createStatement()) {
                // And some batch to insert some product information into
                // the product table
                statement.addBatch("INSERT INTO product (code, name) " +
                                   "VALUE ('P0000006', 'Championship Manager')");
                statement.addBatch("INSERT INTO product (code, name) " +
                                   "VALUE ('P0000007', 'Transport Tycoon Deluxe')");
                statement.addBatch("INSERT INTO product (code, name) " +
                                   "VALUE ('P0000008', 'Roller Coaster Tycoon 3')");
                statement.addBatch("INSERT INTO product (code, name) " +
                                   "VALUE ('P0000009', 'Pro Evolution Soccer')");

                // To execute a batch command, we must call the executeBatch()
                // method.
                int[] updateCounts = statement.executeBatch();
                System.out.println("updateCounts = " + Arrays.toString(updateCounts));

                // Commit our transaction
                connection.commit();
            } catch (SQLException e) {
                connection.rollback();
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

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

How do I drop table from a database?

By I Wayan Saryada in JDBC Last modified: May 31, 2024 0 Comment

This example is to show you how to delete or drop a table from your database. Basically we just send a DROP TABLE command and specify the table name to be deleted to the database. The example below show you how to do it in MySQL database.

package org.kodejava.jdbc;

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

public class DropTableExample {
    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)) {

            // To delete a table from database we use the DROP TABLE
            // command and specify the table name to be dropped
            String sql = "DROP TABLE IF EXISTS book";

            // Create a statement
            Statement statement = connection.createStatement();
            // Execute the statement to delete the table
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

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

How do I create a table in a database?

By I Wayan Saryada in JDBC Last modified: May 31, 2024 0 Comment

In this example you can see how to create a table in MySQL database. We create a table called book with the following fields, id, isbn, title, published_year and price. We start by creating a connection to the database and execute the create table query.

package org.kodejava.jdbc;

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

public class CreateTableExample {
    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 = """
                    CREATE TABLE book
                    (
                        id             bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                        isbn           varchar(50)         NOT NULL,
                        title          varchar(100)        NOT NULL,
                        published_year int(11)                      DEFAULT NULL,
                        price          decimal(10, 2)      NOT NULL DEFAULT '0.00',
                        PRIMARY KEY (id)
                    )
                    """;

            Statement statement = connection.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

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

How do I query records from a table?

By I Wayan Saryada in JDBC Last modified: May 31, 2024 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>