How do I execute a simple SQL query with Statement?

By Wayan in JDBC 0 Comment

Executing a simple SQL query using a Statement object in JDBC follows a straightforward pattern: establish a connection, create the statement, execute the query, and process the results.

Here is a clean example of how to perform a SELECT query:

Simple SQL Query Example

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 SimpleQueryExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "username";
        String password = "password";

        String sql = "SELECT id, username, email FROM users";

        // Use try-with-resources to ensure resources are closed automatically
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // Iterate through the result set
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("username");
                String email = rs.getString("email");

                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }

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

Key Methods to Know

The Statement interface provides different methods depending on the type of SQL you are running:

  1. executeQuery(String sql): Used for SELECT statements. It returns a ResultSet containing the data.
  2. executeUpdate(String sql): Used for INSERT, UPDATE, or DELETE statements. It returns an int representing the number of rows affected.
  3. execute(String sql): A general-purpose method that can execute any SQL statement. It returns true if the result is a ResultSet (query) and false if it is an update count or there are no results.

Important Tips

  • Try-with-Resources: Always use the try-with-resources block (shown above) for Connection, Statement, and ResultSet. This prevents memory leaks by ensuring the database handles are closed even if an exception occurs.
  • Security: While Statement is great for simple or static queries, use PreparedStatement if your query includes variables provided by a user. This prevents SQL Injection attacks.
  • Indices vs. Names: When reading from a ResultSet, you can use column names (e.g., rs.getString("username")) or 1-based indices (e.g., rs.getString(1)). Names are generally more readable and maintainable.

How do I limit MySQL query result?

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

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

public class SqlLimitExample {
    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 PreparedStatement to get all data from a database.
            String query = "select count(*) from product";
            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 product 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"));
            }

            // Create PreparedStatement to get data from the 4th
            // record (remember the first record is 0) and limited
            // to 3 records only.
            query = "select * from product 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"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

An example result of our program is:

Total number of data in database: 9

Result fetched with specified limit 5
====================================
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

Result fetched with specified limit 3, 3
====================================
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

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 Wayan 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