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.sql;

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

public class QueryTimeOut {
    public static void main(String[] args) {
        Connection connection = null;

        try {
            connection = getConnection();
            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")
                        + " ,qty: " + rs.getInt("qty"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection)
            throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

How do I limit MySQL query result?

package org.kodejava.example.sql;

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

public class SqlLimitExample {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();

            // Create PreparedStatement to get all data from database.
            String query = "select count(*) from products";
            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet result = ps.executeQuery();

            int total = 0;
            while (result.next()) {
                total = result.getInt(1);
            }

            System.out.println("Total number of data in database: " + total + "n");

            // Create PreparedStatement to the first 5 records only.
            query = "select * from products limit 5";
            ps = connection.prepareStatement(query);
            result = ps.executeQuery();

            System.out.println("Result fetched with specified limit 5");
            System.out.println("====================================");
            while (result.next()) {
                System.out.println("id:" + result.getInt("id") +
                        ", code:" + result.getString("code") +
                        ", name:" + result.getString("name") +
                        ", price:" + result.getString("price") +
                        ", qty:" + result.getString("qty"));
            }

            // Create PreparedStatement to get data from the 4th
            // record (remember the first record is 0) and limited
            // to 3 records only.
            query = "select * from products limit 3, 3";
            ps = connection.prepareStatement(query);
            result = ps.executeQuery();

            System.out.println("nResult fetched with specified limit 3, 3");
            System.out.println("====================================");
            while (result.next()) {
                System.out.println("id:" + result.getInt("id") +
                        ", code:" + result.getString("code") +
                        ", name:" + result.getString("name") +
                        ", price:" + result.getString("price") +
                        ", qty:" + result.getString("qty"));
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Get a connection to database.
     *
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * Close a connection to database.
     *
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection)
            throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

An example result of our program is:

Total number of data in database: 10

Result fetched with specified limit 5
====================================
id:1, code:P0001, name:Product 01, price:19.99, qty:10
id:2, code:P0002, name:Product 02, price:29.99, qty:20
id:3, code:P0003, name:Product 03, price:39.99, qty:30
id:4, code:P0004, name:Product 04, price:49.99, qty:40
id:5, code:P0005, name:Product 05, price:59.99, qty:50

Result fetched with specified limit 3, 3
====================================
id:4, code:P0004, name:Product 04, price:49.99, qty:40
id:5, code:P0005, name:Product 05, price:59.99, qty:50
id:6, code:P0006, name:Product 06, price:69.99, qty:60

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.sql;

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

public class SetFetchSizeExample {

    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();
            Statement statement = connection.createStatement();

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

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

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

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection) 
            throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

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.sql;

import java.sql.*;

public class SetMaxRowExample {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();
            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 number of 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, qty 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")
                         + ", QTY: " + rs.getInt("qty"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }


    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection("jdbc:mysql://localhost/kodejavadb", "root", "");
    }

    /**
     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection) throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

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 number of Products: 10

Data read after the MaxRows is set.
ID: 1, CODE: P0001, NAME: Product 01, PRICE: 19.99, QTY: 10
ID: 2, CODE: P0002, NAME: Product 02, PRICE: 29.99, QTY: 20
ID: 3, CODE: P0003, NAME: Product 03, PRICE: 39.99, QTY: 30
ID: 4, CODE: P0004, NAME: Product 04, PRICE: 49.99, QTY: 40
ID: 5, CODE: P0005, NAME: Product 05, PRICE: 59.99, QTY: 50

How do I convert java.util.Date to java.sql.Date?

package org.kodejava.example.sql;

import java.util.Date;

public class UtilDateToSqlDate {
    public static void main(String[] args) {
        //
        // Create a new instance of java.util.Date
        //
        java.util.Date date = new Date();
        
        //
        // To covert java.util.Date to java.sql.Date we need to create
        // an instance of java.sql.Date and pass the long value of 
        // java.util.Date as the parameter.
        //
        java.sql.Date sqlDate = new java.sql.Date(date.getTime());

        System.out.println("Date    = " + date);
        System.out.println("SqlDate = " + sqlDate);
    }
}

How do I get system functions supported by database?

package org.kodejava.example.sql;

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

public class SystemFunction {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    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 {
        Connection connection = null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            DatabaseMetaData metadata = connection.getMetaData();

            //
            // Get system functions supported by database
            //
            String[] functions = metadata.getSystemFunctions().split(",\s*");

            for (int i = 0; i < functions.length; i++) {
                String function = functions[i];
                System.out.println("Function = " + function);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

Here are MySQL database supported system functions.

Function = DATABASE
Function = USER
Function = SYSTEM_USER
Function = SESSION_USER
Function = PASSWORD
Function = ENCRYPT
Function = LAST_INSERT_ID
Function = VERSION

How do I get date time functions supported by database?

package org.kodejava.example.sql;

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

public class DateTimeFunction {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    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 {
        Connection connection = null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            DatabaseMetaData metadata = connection.getMetaData();

            //
            // Get date and time functions supported by database
            //
            String[] functions = metadata.getTimeDateFunctions().split(",\s*");

            for (int i = 0; i < functions.length; i++) {
                String function = functions[i];
                System.out.println("Function = " + function);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

Date and time functions supported by MySQL database.

Function = DAYOFWEEK
Function = WEEKDAY
Function = DAYOFMONTH
Function = DAYOFYEAR
Function = MONTH
Function = DAYNAME
Function = MONTHNAME
Function = QUARTER
Function = WEEK
Function = YEAR
Function = HOUR
Function = MINUTE
Function = SECOND
Function = PERIOD_ADD
Function = PERIOD_DIFF
Function = TO_DAYS
Function = FROM_DAYS
Function = DATE_FORMAT
Function = TIME_FORMAT
Function = CURDATE
Function = CURRENT_DATE
Function = CURTIME
Function = CURRENT_TIME
Function = NOW
Function = SYSDATE
Function = CURRENT_TIMESTAMP
Function = UNIX_TIMESTAMP
Function = FROM_UNIXTIME
Function = SEC_TO_TIME
Function = TIME_TO_SEC

How do I get numeric functions supported by database?

package org.kodejava.example.sql;

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

public class NumericFunction {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    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 {
        Connection connection = null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            DatabaseMetaData metadata = connection.getMetaData();

            //
            // Get numeric functions supported by database
            //
            String[] functions = metadata.getNumericFunctions().split(",\s*");

            for (int i = 0; i < functions.length; i++) {
                String function = functions[i];
                System.out.println("Function = " + function);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

Here is the numeric functions supported by MySQL database.

Function = ABS
Function = ACOS
Function = ASIN
Function = ATAN
Function = ATAN2
Function = BIT_COUNT
Function = CEILING
Function = COS
Function = COT
Function = DEGREES
Function = EXP
Function = FLOOR
Function = LOG
Function = LOG10
Function = MAX
Function = MIN
Function = MOD
Function = PI
Function = POW
Function = POWER
Function = RADIANS
Function = RAND
Function = ROUND
Function = SIN
Function = SQRT
Function = TAN
Function = TRUNCATE

How do I get string functions supported by database?

package org.kodejava.example.sql;

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

public class StringFunction {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    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 {
        Connection connection = null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            DatabaseMetaData metadata = connection.getMetaData();

            //
            // Get string functions supported by database
            //
            String[] functions = metadata.getStringFunctions().split(",\s*");
            
            for (int i = 0; i < functions.length; i++) {
                String function = functions[i];
                System.out.println("Function = " + function);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

Here is the result of string functions supported by MySQL database:

Function = ASCII
Function = BIN
Function = BIT_LENGTH
Function = CHAR
Function = CHARACTER_LENGTH
Function = CHAR_LENGTH
Function = CONCAT
Function = CONCAT_WS
Function = CONV
Function = ELT
Function = EXPORT_SET
Function = FIELD
Function = FIND_IN_SET
Function = HEX
Function = INSERT
Function = INSTR
Function = LCASE
Function = LEFT
Function = LENGTH
Function = LOAD_FILE
Function = LOCATE
Function = LOCATE
Function = LOWER
Function = LPAD
Function = LTRIM
Function = MAKE_SET
Function = MATCH
Function = MID
Function = OCT
Function = OCTET_LENGTH
Function = ORD
Function = POSITION
Function = QUOTE
Function = REPEAT
Function = REPLACE
Function = REVERSE
Function = RIGHT
Function = RPAD
Function = RTRIM
Function = SOUNDEX
Function = SPACE
Function = STRCMP
Function = SUBSTRING
Function = SUBSTRING
Function = SUBSTRING
Function = SUBSTRING
Function = SUBSTRING_INDEX
Function = TRIM
Function = UCASE
Function = UPPER

How do I get the max concurrent connection to a database?

package org.kodejava.example.sql;

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

public class MaxConcurrentConnection {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    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 {
        Connection connection = null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            DatabaseMetaData metadata = connection.getMetaData();

            //
            // Get the information of maximum concurrent connection to the
            // database. The maximum number of active connections possible at
            // one time; a result of zero means that there is no limit or the
            // limit is not known
            //
            int maxConnection = metadata.getMaxConnections();
            System.out.println("Maximum Connection = " + maxConnection);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}