How do I set the query timeout limit?

The Statement.setQueryTimeout() method set the limit in seconds for query execution time. When the value is set to zero it means that the execution has no timeout limit.

package org.kodejava.example.jdbc;

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

public class QueryTimeout {
    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)) {
            Statement stmt = connection.createStatement();

            // Sets the number of seconds the driver will wait for
            // a statement object to execute to the given number of
            // seconds. If the limit is exceeded, an SQLException
            // is thrown.
            stmt.setQueryTimeout(60);

            // Execute sql query
            ResultSet rs = stmt.executeQuery("select * from products");

            while (rs.next()) {
                System.out.println("code: " + rs.getString("code")
                    + " ,product: " + rs.getString("name")
                    + " ,price: " + rs.getDouble("price"));
            }
        } catch (SQLException 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

How do I set the fetch size of a statement?

Fetch size is the number of rows that should be fetched from the database on a single database network trip, when more rows are needed another request is sent by the application to the database server.

Setting the correct fetch size will help our program to perform well regarding to the number of network communication generated between the program and the database server.

package org.kodejava.example.jdbc;

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

public class SetFetchSizeExample {
    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)) {
            Statement statement = connection.createStatement();

            // Set the fetch size to 100.
            statement.setFetchSize(100);

            // Execute the given sql query
            String q = "select id, code, name, price from products";
            ResultSet rs = statement.executeQuery(q);

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

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Example result of the code snippet above is:

id:1, code:P0000001, name:UML Distilled 3rd Edition, price:25.00
id:3, code:P0000003, name:PHP Programming, price:20.00
id:4, code:P0000004, name:Longman Active Study Dictionary, price:40.00
id:5, code:P0000005, name:Ruby on Rails, price:24.00
id:6, code:P0000006, name:Championship Manager, price:0.00
id:7, code:P0000007, name:Transport Tycoon Deluxe, price:0.00
id:8, code:P0000008, name:Roller Coaster Tycoon 3, price:0.00
id:9, code:P0000009, name:Pro Evolution Soccer, price:0.00
id:10, code:P0000010, name:Data Structures, Algorithms, price: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 set the maximum rows to read in a query?

If you want to limit the result of your query you can use the Statement.setMaxRows(int max) method call. This call will allow the ResultSet object to contains a maximum number of records specified in the parameter of the setMaxRows method.

Another way to limit the number of data returned in a query is to use the database specific command such as the MySQL limit command.

package org.kodejava.example.jdbc;

import java.sql.*;

public class SetMaxRowExample {
    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)) {
            Statement statement = connection.createStatement();

            // Executes an SQL query to get the total number of data
            // in products table.
            String query = "select count(*) from products";
            ResultSet rs = statement.executeQuery(query);

            while (rs.next()) {
                System.out.println("Total Products: " + rs.getInt(1));
            }

            // Set the maximum row of data that can be stored in the
            // ResultSet.
            statement.setMaxRows(5);

            // Executes an SQL query to retrieve data from Products
            // table.
            query = "select id, code, name, price from products";
            rs = statement.executeQuery(query);

            System.out.println("Data read after the MaxRows is set.");
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id")
                    + ", CODE: " + rs.getString("code")
                    + ", NAME: " + rs.getString("name")
                    + ", PRICE: " + rs.getBigDecimal("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

When running the code we’ll see that only 5 records was read from the Products table instead of 10 records. This is the result of setting the maximum rows in the Statement object.

Below is the output of our code.

Total Products: 9
Data read after the MaxRows is set.
ID: 1, CODE: P0000001, NAME: UML Distilled 3rd Edition, PRICE: 25.00
ID: 3, CODE: P0000003, NAME: PHP Programming, PRICE: 20.00
ID: 4, CODE: P0000004, NAME: Longman Active Study Dictionary, PRICE: 40.00
ID: 5, CODE: P0000005, NAME: Ruby on Rails, PRICE: 24.00
ID: 6, CODE: P0000006, NAME: Championship Manager, PRICE: 0.00

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 use enum in switch statement?

This example show you how to use enumeration or enum type in a switch statement.

package org.kodejava.example.fundamental;

enum RainbowColor {
    RED, ORANGE, YELLOW, GREEN, BLUE, INDIGO, VIOLET
}

public class EnumSwitch {
    public static void main(String[] args) {
        RainbowColor color = RainbowColor.INDIGO;

        EnumSwitch es = new EnumSwitch();
        String colorCode = es.getColorCode(color);
        System.out.println("ColorCode = #" + colorCode);
    }

    public String getColorCode(RainbowColor color) {
        String colorCode = "";

        // We use the switch-case statement to get the hex color code of our
        // enum type rainbow colors. We can pass the enum type as expression
        // in the swith. In the case statement we only use the enum named
        // constant excluding its type name.
        switch (color) {
            // We use RED instead of RainbowColor.RED
            case RED:
                colorCode = "FF0000";
                break;
            case ORANGE:
                colorCode = "FFA500";
                break;
            case YELLOW:
                colorCode = "FFFF00";
                break;
            case GREEN:
                colorCode = "008000";
                break;
            case BLUE:
                colorCode = "0000FF";
                break;
            case INDIGO:
                colorCode = "4B0082";
                break;
            case VIOLET:
                colorCode = "EE82EE";
                break;
            default:
                break;
        }
        return colorCode;
    }
}

How do I make updates in Updatable ResultSet?

Using an updatable result set enable our program to update record in the database from the ResultSet object. The operation on the ResultSet object can be update, insert or delete. With this mechanism we can update database without executing an sql command.

In the example below we have a products table with the id, code, name, and price. In the first step after we load the result set, 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 database.

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

    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 statement
            // with CONCUR_UPDATABLE. The updatable doesn't need to be
            // TYPE_SCROLL_SENSITIVE, but adding this parameter to the
            // statement enable 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 products";
            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 print 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

<!-- 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 create a batch update in JDBC?

A batch statement can be use 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 data into database.

package org.kodejava.example.jdbc;

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

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

            // 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 products (code, name) " +
                    "VALUE ('P0000006', 'Championship Manager')");
                statement.addBatch("INSERT INTO products (code, name) " +
                    "VALUE ('P0000007', 'Transport Tycoon Deluxe')");
                statement.addBatch("INSERT INTO products (code, name) " +
                    "VALUE ('P0000008', 'Roller Coaster Tycoon 3')");
                statement.addBatch("INSERT INTO products (code, name) " +
                    "VALUE ('P0000009', 'Pro Evolution Soccer')");

                // To execute a batch command we must call the executeBatch()
                // method.
                int[] updateCounts = statement.executeBatch();

                // Commit our transaction
                connection.commit();
            } catch (SQLException e) {
                connection.rollback();
                e.printStackTrace();
            }
        } catch (SQLException 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

How do I drop table from database?

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

    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 books";

            // Create a statement
            Statement statement = connection.createStatement();
            // Execute the statement to delete the table
            statement.execute(sql);
        } catch (SQLException 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

How do I create a table in database?

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

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

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

            String sql = "CREATE TABLE books (" +
                "  isbn varchar(50) NOT NULL, " +
                "  title varchar(100) DEFAULT NULL, " +
                "  published_year int(11) DEFAULT NULL, " +
                "  price decimal(10,2) DEFAULT NULL, " +
                "  PRIMARY KEY (isbn) " +
                ")";

            Statement statement = connection.createStatement();
            statement.execute(sql);
        } catch (SQLException 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

How do I query records from table?

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

    public static void main(String[] args) throws Exception {
        // 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 books";
            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"));
            }
        }
    }
}

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 insert a record into database table?

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 string.

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

    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 books (isbn, title, published_year) " +
                "VALUES ('9781617291999', 'Java 8 in Action', 2015)";

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

Below is the script from creating the books table.

CREATE TABLE `books` (
  `isbn` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `published_year` int(11) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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