Executing SQL Queries with JDBC

JDBC (Java Database Connectivity) provides a standard API to execute SQL queries and retrieve results from relational databases. SQL operations are broadly classified into Data Definition Language (DDL) and Data Manipulation Language (DML) operations.

1. Steps to Execute SQL Queries

1. Load JDBC Driver

Class.forName("com.mysql.cj.jdbc.Driver");

2. Establish Database Connection

Connection con = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/mydb", "username", "password");

3. Create Statement Object

Statement stmt = con.createStatement();

4. Execute SQL Query

• Use executeQuery() for SELECT statements

• Use executeUpdate() for INSERT, UPDATE, DELETE, CREATE, DROP

5. Process ResultSet (for SELECT)

ResultSet rs = stmt.executeQuery("SELECT * FROM students");
while(rs.next()) {
    System.out.println(rs.getInt("id") + " " + rs.getString("name"));
}

6. Close Resources

rs.close();
stmt.close();
con.close();

2. Example 1: SELECT Query

import java.sql.*;

public class SelectExample {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/mydb","root","password");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM students");

        while(rs.next()) {
            System.out.println(rs.getInt("id") + " " + rs.getString("name"));
        }

        rs.close();
        stmt.close();
        con.close();
    }
}

3. Example 2: INSERT Query

import java.sql.*;

public class InsertExample {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/mydb","root","password");
        Statement stmt = con.createStatement();

        int rows = stmt.executeUpdate(
            "INSERT INTO students(id, name) VALUES(4, 'Ravi')");
        System.out.println("Rows inserted: " + rows);

        stmt.close();
        con.close();
    }
}

4. Example 3: UPDATE and DELETE Queries

UPDATE

int rows = stmt.executeUpdate(
    "UPDATE students SET name='Rahul' WHERE id=4");
System.out.println("Rows updated: " + rows);

DELETE

int rows = stmt.executeUpdate(
    "DELETE FROM students WHERE id=4");
System.out.println("Rows deleted: " + rows);

5. Using PreparedStatement (Recommended)

• Prevents SQL injection

• Precompiles SQL queries for better performance

String sql = "INSERT INTO students(id, name) VALUES(?, ?)";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, 5);
ps.setString(2, "Anita");
ps.executeUpdate();
ps.close();

6. Key Points

• executeQuery() → For SELECT statements, returns ResultSet

• executeUpdate() → For INSERT, UPDATE, DELETE, returns number of affected rows

• Always close JDBC resources to prevent memory leaks

• Prefer PreparedStatement over Statement for dynamic queries and security

7. Conclusion

Executing SQL queries using JDBC allows Java applications to interact dynamically with relational databases, enabling CRUD operations, transaction management, and data retrieval. Proper use of Statement, PreparedStatement, and ResultSet ensures efficient, secure, and maintainable database programming.