How do I insert rows with JDBC?

By Wayan in JDBC 0 Comment

To insert rows into a database using JDBC, you typically use the executeUpdate(String sql) method of a Statement or PreparedStatement object.

Here are the two primary ways to do it:

1. Using PreparedStatement (Recommended)

This is the standard approach because it prevents SQL Injection and is more efficient for repeated inserts.

package org.kodejava.jdbc;

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

public class InsertRecordExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/kodejava";
        String user = "root";
        String password = "password";

        String sql = "INSERT INTO users (username, email) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // Set the values for the placeholders (?)
            pstmt.setString(1, "john_doe");
            pstmt.setString(2, "[email protected]");

            // executeUpdate returns the number of rows affected
            int rowsInserted = pstmt.executeUpdate();
            if (rowsInserted > 0) {
                System.out.println("A new user was inserted successfully!");
            }

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

2. Using Statement

Use this only for simple, static SQL queries where no user input is involved.

// ... existing code ...
    try (Connection conn = DriverManager.getConnection(url, user, password);
         Statement stmt = conn.createStatement()) {

        String sql = "INSERT INTO users (username, email) VALUES ('jane_doe', '[email protected]')";
        int rows = stmt.executeUpdate(sql);

        System.out.println("Rows affected: " + rows);
    } catch (SQLException e) {
        e.printStackTrace();
    }
// ... existing code ...

Key Takeaways:

  • executeUpdate(): Unlike executeQuery() (which returns a ResultSet), executeUpdate() returns an int representing how many rows were added, changed, or deleted.
  • Try-with-resources: Always wrap your Connection, Statement, or PreparedStatement in a try-with-resources block to ensure they are closed automatically, even if an error occurs.
  • Placeholders: In a PreparedStatement, indices for ? parameters start at 1.

How do I update rows with executeUpdate?

By Wayan in JDBC 0 Comment

In JDBC, the executeUpdate method is used for SQL statements that modify data, such as UPDATE, INSERT, or DELETE. Unlike executeQuery, which returns a ResultSet, executeUpdate returns an int representing the number of rows affected by the operation.

Here is how you can update rows using PreparedStatement (the recommended way) and Statement.

1. Using PreparedStatement (Recommended)

This approach is more secure as it prevents SQL injection and is more efficient for queries used multiple times.

package org.kodejava.jdbc;

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

public class UpdateExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/musicdb";
        String user = "root";
        String password = "password";

        String sql = "UPDATE album SET title = ? WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // Bind values to the placeholders (?)
            pstmt.setString(1, "The White Album");
            pstmt.setLong(2, 10L);

            // Execute the update
            int rowsAffected = pstmt.executeUpdate();

            System.out.println("Rows updated: " + rowsAffected);

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

2. Using Statement

Use this only for simple, static SQL queries where no user input is involved.

String sql = "UPDATE album SET title = 'Rubber Soul' WHERE id = 5";

try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement()) {

    int rowsAffected = stmt.executeUpdate(sql);
    System.out.println("Rows updated: " + rowsAffected);
}

Key Points to Remember:

  • Return Value: The method returns the number of rows changed. If it returns 0, it means no rows matched your WHERE clause.
  • Transaction Control: If you have set connection.setAutoCommit(false), you must call connection.commit() after executeUpdate() to persist the changes to the database.
  • Resource Management: Always use a try-with-resources block to ensure the Connection and Statement are closed automatically, which prevents database connection leaks.

How do I fetch results with ResultSet?

By Wayan in JDBC 0 Comment

To fetch results using a ResultSet in Java JDBC, you follow a standard pattern of executing a query, iterating through the rows, and extracting data using “getter” methods.

Basic Fetching Pattern

  1. Execute the Query: Use stmt.executeQuery(sql) (for Statement) or pstmt.executeQuery() (for PreparedStatement).
  2. Iterate through Rows: Use a while (rs.next()) loop. The next() method moves the cursor to the next row and returns false when there are no more rows.
  3. Retrieve Values: Use methods like getString(), getInt(), getLong(), or getBigDecimal() based on the column type.

Example

try (Connection conn = DriverManager.getConnection(url, user, pass);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT id, name, created_at FROM users")) {

    while (rs.next()) {
        // 1. Fetch by Column Name (Recommended for readability)
        int id = rs.getInt("id");
        String name = rs.getString("name");

        // 2. Fetch by Column Index (1-based, often faster)
        java.sql.Timestamp date = rs.getTimestamp(3);

        System.out.println("User: " + id + " | Name: " + name);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Key Considerations

  • 1-Based Indexing: If you use column indices (e.g., rs.getString(1)), remember that JDBC indices start at 1, not 0.
  • Column Names vs. Indices:
    • Names: More maintainable and easier to read.
    • Indices: Slightly better performance as the driver doesn’t have to look up the column mapping.
  • Try-with-Resources: Always wrap the ResultSet in a try-with-resources block (or ensure it is closed in a finally block) to prevent database cursor leaks.
  • Data Types: Use the getter that matches your database schema:
    • rs.getInt() for INTEGER
    • rs.getString() for VARCHAR/TEXT
    • rs.getBigDecimal() for DECIMAL/NUMERIC (best for currency)
    • rs.getBoolean() for BIT/BOOLEAN
  • Handling Nulls: If a column allows NULL, primitive getters (like getInt) will return 0. To check if the value was actually null in the database, call rs.wasNull() immediately after the getter.

How do I use CallableStatement for stored procedures?

By Wayan in JDBC 0 Comment

Using a CallableStatement in JDBC is the standard way to execute stored procedures. It allows you to handle input parameters (IN), output parameters (OUT), and even result sets returned by the database.

Here is a guide on how to use it for different scenarios.

1. Basic Syntax

The syntax for calling a stored procedure uses the escape sequence {call procedure_name(?, ?)}.

// Standard format for calling a procedure with parameters
String query = "{call get_user_details(?, ?)}";
CallableStatement stmt = connection.prepareCall(query);

2. Working with Input Parameters (IN)

If your procedure requires data, you set them just like a PreparedStatement using index-based setters (starting at 1).

try (CallableStatement stmt = conn.prepareCall("{call get_product_by_id(?)}")) {
    stmt.setInt(1, 101); // Set the first parameter

    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            System.out.println("Product: " + rs.getString("name"));
        }
    }
}

3. Working with Output Parameters (OUT)

If the procedure returns a value via an OUT parameter, you must register its SQL type before execution and retrieve it afterward.

String sql = "{call get_employee_count_by_dept(?, ?)}";
try (CallableStatement stmt = conn.prepareCall(sql)) {
    // 1. Set the IN parameter
    stmt.setString(1, "Engineering");

    // 2. Register the OUT parameter type
    stmt.registerOutParameter(2, java.sql.Types.INTEGER);

    // 3. Execute
    stmt.execute();

    // 4. Retrieve the value from the OUT parameter
    int count = stmt.getInt(2);
    System.out.println("Total employees: " + count);
}

4. Handling Stored Functions

If you are calling a database function that returns a value directly, the syntax changes slightly to include a return placeholder at the beginning.

// The first '?' is the return value
String sql = "{? = call calculate_tax(?)}";
try (CallableStatement stmt = conn.prepareCall(sql)) {
    stmt.registerOutParameter(1, java.sql.Types.DOUBLE);
    stmt.setDouble(2, 5000.00);

    stmt.execute();
    double tax = stmt.getDouble(1);
    System.out.println("Tax: " + tax);
}

Key Best Practices:

  • Try-with-resources: Always wrap your Connection and CallableStatement in a try-with-resources block to ensure they are closed automatically.
  • Parameter Indexing: Remember that JDBC parameters are 1-indexed.
  • SQL Types: Use java.sql.Types when registering OUT parameters to match your database schema.
  • Performance: CallableStatement objects are often pre-compiled by the database driver, making them more efficient than raw SQL strings for repeated calls.

How do I use PreparedStatement to prevent SQL injection?

By Wayan in JDBC 0 Comment

Using PreparedStatement is one of the most effective ways to prevent SQL injection in Java. It works by separating the SQL query structure from the data, ensuring that user input is treated strictly as data and never as part of the executable SQL command.

Here is how you use it:

1. The Key Concept: Placeholders

Instead of concatenating strings (which is where the danger lies), you use a question mark (?) as a placeholder for every dynamic value.

2. Implementation Example

package org.kodejava.jdbc;

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

public class SecureQueryExample {
    public void getUserDetails(String username) {
        // 1. Define SQL with placeholders (?)
        String sql = "SELECT id, email, status FROM users WHERE username = ?";

        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "user", "pass");
             // 2. Prepare the statement
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 3. Bind the values (index starts at 1)
            pstmt.setString(1, username);

            // 4. Execute the query
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    System.out.println("User ID: " + rs.getInt("id"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Why this prevents SQL Injection

Imagine a malicious user provides this as a “username”: ' OR '1'='1.

  • Vulnerable (String Concatenation):
    SELECT * FROM users WHERE username = '' OR '1'='1' — This changes the logic to return all users.
  • Secure (PreparedStatement):
    The database receives the query structure first. When the input is sent, the database looks literally for a user whose name is the string ' OR '1'='1. Since no such user exists, the attack fails, and the query remains safe.

Best Practices

  • Use setXXX methods: Always use the specific setter for your data type (e.g., setInt(), setString(), setTimestamp()). This adds an extra layer of type validation.
  • Never Concatenate: Even if you use a PreparedStatement, if you build the SQL string using + or StringBuilder before passing it to prepareStatement(), you are still vulnerable.
  • Try-with-resources: As shown above, use try-with-resources to ensure the Connection and PreparedStatement are closed automatically, preventing resource leaks.