Statement | Kode Java - Learn Java by Examples

How do I create a batch update in JDBC?

By Wayan in JDBC Last modified: July 28, 2023 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.1.0</version>
</dependency>

How do I drop table from a database?

By Wayan in JDBC Last modified: July 24, 2023 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.1.0</version>
</dependency>

How do I create a table in a database?

By Wayan in JDBC Last modified: July 24, 2023 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.1.0</version>
</dependency>

How do I query records from a table?

By Wayan in JDBC Last modified: July 24, 2023 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.1.0</version>
</dependency>

How do I insert a record into database table?

By Wayan in JDBC Last modified: July 24, 2023 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.1.0</version>
</dependency>