Introduction

In the previous chapter of our JDBC series, we built the foundation for database interaction using Java. We learned how to establish a connection, create a statement, and fetch records from a database table. This gave us a practical understanding of how Java communicates with relational databases such as MySQL.

Now, as we step into Chapter 2, we will move beyond the basics and explore how JDBC handles both retrieval and manipulation of data. In this chapter, we will cover the core SQL operations—Select, Insert, Update, and Delete—directly from Java programs. Along the way, we will discuss how JDBC evolved with features like autoloading, why certain best practices exist (such as closing resources in reverse order), and how to structure code for reusability using a utility class.

By the end of this chapter, you will not only know how to implement CRUD operations in Java but also understand the behind-the-scenes mechanisms of JDBC, which will give you an edge in technical interviews and real-world projects.

---

Section 1: Revisiting Previous Code – The SelectApp Program

Before diving into new concepts, let us revisit the SelectApp program we wrote earlier. This simple program connected to a MySQL database, executed a query, and displayed the results. While basic in appearance, it introduced us to the fundamental flow of JDBC: connecting, querying, processing, and closing resources.

Here’s the code once again:

package in.rohit.main;
import java.sql.*;

public class SelectApp {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        try {
            // Step 1: Load and register the driver 
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("Driver loaded succesfully...");

            // Step 2: Establish the Connection
            String url = "jdbc:mysql://localhost:3306/Rohit";
            String user = "Rohit";
            String password = "Rohit";
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("Connection object created...");

            // Step 3: Create statement Object and send the Query
            statement = connection.createStatement();
            System.out.println("Statement object created...");

            // Step 4: Execute the Query and Process the resultset 
            String sqlSelectQuery = "select id, name, age, address from student"; // include id also
            resultset = statement.executeQuery(sqlSelectQuery);

            System.out.println("SID\tSNAME\t\tSAGE\tSADD");
            while (resultset.next()) {
                int sid = resultset.getInt("id"); // column: id
                String sname = resultset.getString("name"); // column: name
                int sage = resultset.getInt("age"); // column: age
                String sadd = resultset.getString("address"); // column: address
                System.out.println(sid + "\t" + sname + "\t" + sage + "\t" + sadd);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Step 5: Closing the resources 
            try {
                if (resultset != null) resultset.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

This program may look straightforward, but it contains several important lessons about JDBC.

The first concept to highlight is closing resources in reverse order. When we open resources such as Connection, Statement, and ResultSet, they are created in a nested sequence. The ResultSet depends on the Statement, and the Statement depends on the Connection. Therefore, when closing them, we reverse this order—first ResultSet, then Statement, and finally Connection. This ensures that dependencies are safely released without causing errors.

Another interesting detail in this program is how we retrieved column values. Notice that we used:

int sid = resultset.getInt("id");  
String sname = resultset.getString("name");

Here we used column names to fetch values. This approach makes code more readable, but JDBC also allows using column numbers, such as:

int sid = resultset.getInt(1); // column: id

When column numbers are used, performance improves slightly because JDBC doesn’t need to map the column name to its position. However, readability might suffer, especially in large queries. This creates a trade-off between performance and clarity, and developers often choose based on project needs.

Through this revision, we reinforced three fundamental practices:

1. Always close resources in reverse order.

2. Understand the difference between accessing columns by name vs. by index.

3. Appreciate how JDBC structures the execution of a simple SELECT query.

This foundation will now help us explore more advanced aspects of JDBC in the coming sections.

---

Section 2: JDBC URL and Localhost Simplification

In the previous section, we revisited the SelectApp program and understood the flow of loading a driver, connecting to the database, executing a query, and closing resources. Now, let us zoom in on a detail that often confuses beginners—the JDBC URL.

When we first wrote the program, our connection string looked like this:

String url = "jdbc:mysql://localhost:3306/Rohit";

At first glance, this URL seems natural. It specifies three key elements:

1. The database vendor (mysql).

2. The host where the database is running (localhost).

3. The port number on which the MySQL server is listening (3306).

4. The database name (Rohit).

But here’s an interesting fact: if your Java program and database server are running on the same machine, you don’t need to explicitly write localhost:3306.

Why is that the case?

By default, MySQL listens on port 3306. If your Java application is running on the same machine as the database, the JVM automatically assumes the connection is local and uses the default port. That means this URL:

String url = "jdbc:mysql:///Rohit";

is just as valid as the earlier one. The three forward slashes after mysql: indicate that the host is the local machine, and the port defaults to 3306.

This might look like a small optimization, but it reflects how JDBC was designed to simplify a developer’s life. When everything is hosted on the same machine, writing less configuration makes the code cleaner and reduces errors.

Let’s think about this in a practical way. Imagine a developer is running a Java program during testing. Their MySQL database is also installed on the same laptop. Instead of repeatedly typing out localhost:3306, they can simply use jdbc:mysql:///databaseName. This shorthand avoids redundancy and makes the code more concise.

However, it’s important to understand that this simplification works only when the database is running on the same machine and the default port is being used. The moment you try to connect to a remote database server or if the database is configured on a non-standard port, you must explicitly provide the host and port.

For example, if MySQL is running on a remote server with IP 192.168.1.50 and listening on port 3307, the connection string would look like this:

String url = "jdbc:mysql://192.168.1.50:3307/Rohit";

So, while the shorthand is useful during development and testing, professional applications typically use the full form to make the configuration explicit. This avoids hidden assumptions when deploying the application on different environments such as testing, staging, or production.

Another subtle point is that this URL is not just a string—it acts as a contract between the programmer and JDBC. The structure of the URL tells the JVM which database is being used. For instance, if you used jdbc:oracle:thin:@localhost:1521:xe, JDBC would know you’re connecting to Oracle. If you used jdbc:postgresql://localhost:5432/mydb, it would identify PostgreSQL. In our case, jdbc:mysql:///Rohit clearly indicates MySQL.

This means the URL does far more than simply point to a database; it influences how JDBC initializes the environment and selects the correct driver. In the next section, we’ll uncover how this selection process works and how JDBC evolved to make driver loading more intelligent and automated.

---

Section 3: Driver Loading & Autoloading in JDBC 4.x

When Sun Microsystems first introduced JDBC, the programmer had to explicitly tell Java which database driver to use. This was done with the classic line:

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

At first glance, this line looks simple. But behind the scenes, it is performing a critical role: it loads the MySQL JDBC driver class into memory. Once loaded, the driver registers itself with the DriverManager, making it possible for Java to establish a connection to MySQL databases.

Think of this as introducing a translator into a conversation. The driver is the translator who knows how to speak both Java (your application) and MySQL (your database). Without explicitly bringing this translator into the room, the two parties cannot communicate.

The Old Way: Manual Loading

In earlier versions of JDBC (before JDBC 4.0), developers had no choice but to include the Class.forName() call. If you forgot this line, your program would fail with an error like “No suitable driver found”.

For example:

Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(
        "jdbc:mysql:///Rohit", "Rohit", "password");

This worked, but it wasn’t very developer-friendly. Why should every programmer have to manually load the driver when the connection URL already makes it obvious that MySQL is being used?

The Revolution: JDBC 4.0 Autoloading

Starting with JDBC 4.0 (Java 6 and above), the Java team introduced a feature called Service Provider Mechanism, which changed the game.

Now, when you include the MySQL JDBC JAR in your project (such as mysql-connector-j-8.0.xx.jar), it automatically registers itself with the DriverManager as soon as your program starts.

This means you can completely skip the Class.forName() line. Your code becomes simpler:

Connection connection = DriverManager.getConnection(
        "jdbc:mysql:///Rohit", "Rohit", "password");

Behind the scenes, the MySQL driver JAR contains a special file:

META-INF/services/java.sql.Driver

Inside this file, the fully qualified class name of the driver is mentioned:

com.mysql.cj.jdbc.Driver

When the JVM starts, it scans all JARs in the classpath for this file. The moment it finds one, it automatically loads and registers the driver class. This process is invisible to the developer but ensures the right driver is available without requiring explicit code.

Why Does This Matter?

This improvement may look small, but it reflects a deep philosophy in modern Java—removing boilerplate and making the developer’s job easier. Just like we saw with the JDBC URL shorthand in Section 2, JDBC is designed to minimize repetitive tasks and reduce the chance of human error.

In real-world enterprise projects, developers often switch databases during different phases (development may use MySQL, staging may use PostgreSQL, production may use Oracle). With autoloading, the driver selection becomes almost seamless. You just change the JAR file and connection URL, and the rest works automatically.

But Should We Still Use Class.forName()?

Interestingly, many developers still keep Class.forName() in their code out of habit. In fact, some production systems prefer the explicit call because it makes the driver loading step very clear. It also avoids edge cases where the autoloading might not trigger due to misconfigured JAR files.

So while JDBC 4.0 freed us from the burden of manual loading, it’s not “wrong” to include the line. Think of it as a safety net. But for interview preparation and modern project practices, it’s good to know that explicit driver loading is no longer mandatory.

Real-World Analogy

Imagine you walk into an international airport. Before JDBC 4.0, you had to manually hire a translator before talking to customs. With JDBC 4.0 and later, translators are already waiting in a common pool. The moment you arrive and show your nationality (the JDBC URL), the right translator steps forward automatically.

Answer (Interview-Ready):
In earlier JDBC versions, we had to explicitly load the driver using Class.forName("com.mysql.cj.jdbc.Driver"). This was necessary to register the driver with DriverManager.

From JDBC 4.0 onwards, explicit loading is not required because of the Service Provider Mechanism. The JDBC driver JAR contains a META-INF/services/java.sql.Driver file, and the JVM automatically loads and registers the driver at runtime.

So, in modern Java, Class.forName() is optional. However, some developers still use it explicitly for clarity or backward compatibility.

---

Section 4: Revised SelectApp Program with Autoloading

In the earlier sections, we discussed how JDBC traditionally required the explicit Class.forName() call to load and register the database driver. But with JDBC 4.0 and above, autoloader support allows us to skip that boilerplate. Let us now see how this simplifies our real JDBC program.

We will build a simple SelectApp that retrieves student records from the student table in the Rohit database, but this time without using Class.forName().

---

Full Code Example

package in.rohit.main;

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

public class SelectApp {

    public static void main(String[] args) {
        // Resources
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            // Step 1: Establish the Connection (Driver autoloaded in JDBC 4.x)
            String url = "jdbc:mysql:///Rohit";   // Database 'Rohit'
            String user = "Rohit";                // MySQL username
            String password = "yourPassword";     // MySQL password

            connection = DriverManager.getConnection(url, user, password);
            System.out.println("Connection established successfully...");

            // Step 2: Create Statement object
            statement = connection.createStatement();

            // Step 3: Execute SQL Query
            String sqlQuery = "SELECT id, name, age, address FROM student";
            resultSet = statement.executeQuery(sqlQuery);

            // Step 4: Process the ResultSet
            System.out.println("ID\tNAME\tAGE\tADDRESS");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String address = resultSet.getString("address");

                System.out.println(id + "\t" + name + "\t" + age + "\t" + address);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Step 5: Close resources
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

---

Explanation of Execution Flow

1. Driver Autoloading

   • Notice that there is no Class.forName("com.mysql.cj.jdbc.Driver") line.

   • Since JDBC 4.0, the MySQL Connector JAR automatically registers its driver with DriverManager.

2. Establishing Connection

   • The DriverManager.getConnection(url, user, password) method looks at the JDBC URL (jdbc:mysql:///Rohit) and automatically selects the MySQL driver.

   • The connection handshake happens, and if successful, we get a Connection object.

3. Creating Statement

   • From the connection, we create a Statement object.

   • This object is used to send SQL commands to the database.

4. Executing Query

   • The query "SELECT id, name, age, address FROM student" is executed.

   • A ResultSet is returned, which represents a virtual table of the results.

5. Processing ResultSet

   • We use resultSet.next() to iterate row by row.

   • Each column value is fetched using methods like getInt(), getString().

6. Closing Resources

   • Finally, we close the ResultSet, Statement, and Connection to avoid memory leaks.

---

Sample Output

Suppose our student table in database Rohit has the following data:

The program will output:

Connection established successfully...
ID    NAME    AGE    ADDRESS
1    Rohit Gawande    21    Nagpur
2    Rohit Sharma    39    Mumbai
3    Virat Kohli    37    Delhi

---

Answer (Interview-Ready):
In the revised SelectApp, we no longer use Class.forName() because JDBC 4.0 supports driver autoloading through the Service Provider Mechanism. When we call DriverManager.getConnection(), it automatically picks the MySQL driver based on the JDBC URL. This simplifies the code and reduces boilerplate. The program then executes a SELECT query, iterates through the ResultSet, and displays student records.

---

Section 5: SQL Command Classification

---

Why SQL Classification Matters

When we start working with JDBC, it is not enough to just learn how to write code for select, insert, update, or delete. To truly understand the flow of operations, we must step back and look at SQL itself. Every JDBC program ultimately communicates with the database engine through SQL commands. These commands are not random — they are carefully categorized by database administrators (DBAs) into distinct groups, each serving a very specific purpose.

Data Definition Language (DDL)

According to DBA specifications, SQL commands fall into six broad categories. The first is DDL (Data Definition Language), which deals with the structure of database objects. Whenever we create a table, alter its structure, or drop it entirely, we are issuing DDL commands. For example, CREATE TABLE, ALTER TABLE, and DROP TABLE belong to this category. These operations define the schema and are structural in nature rather than data-focused.

Data Manipulation Language (DML)

The second category is DML (Data Manipulation Language). As the name suggests, these commands manipulate the actual data inside the tables. Classic examples are INSERT, UPDATE, and DELETE. These commands directly change the records stored in the database, and this is what we as application developers most frequently use when working with JDBC.

Data Query Language (DQL)

The third category is DQL (Data Query Language). Interestingly, this category is very small but extremely important. It primarily consists of the SELECT command, which is used to retrieve data from one or more tables. In JDBC, every SELECT query we execute results in a ResultSet object, which represents the group of records retrieved from the database.

Data Control Language (DCL)

Next comes DCL (Data Control Language). These commands are more about controlling user permissions and security. For example, when we alter a user’s password, grant access to a certain user, or revoke privileges, we are executing DCL statements. From an application developer’s point of view, these are not very common, but from a database administration standpoint, they are crucial.

Database Administration (DA) Commands

The fifth classification is sometimes called Database Administration (DA) commands. These commands are used for administrative operations like starting or stopping audits. For example, START AUDIT and STOP AUDIT fall into this category. These are generally executed by DBAs, not by typical application developers through JDBC code.

Transaction Control Language (TCL)

Finally, there is TCL (Transaction Control Language). This category includes commands that manage database transactions, such as COMMIT, ROLLBACK, and SAVEPOINT. Whenever we want to ensure data consistency or recover from errors, TCL commands play an essential role. For example, if multiple update statements are part of a single transaction, a rollback can undo them all if something goes wrong.

Java Developer’s Simplified View

Now, this DBA classification is extremely valuable for understanding how SQL is organized at a higher level. But as Java developers writing JDBC code, it may seem a bit overwhelming to remember all these categories. From a Java developer’s perspective, things can be simplified dramatically.

Select vs Non-Select Operations

For us, all SQL operations can essentially be divided into just two types: Select operations and Non-Select operations. Select operations correspond to DQL, where the query retrieves data and returns a ResultSet. Non-Select operations cover everything else — DML, DDL, DCL, DA, and TCL. These commands may insert new data, modify schema, update permissions, or manage transactions, but from JDBC’s standpoint, they are all treated as “Non-Select” operations.

The Practical Distinction

This simplification makes coding much easier. Instead of worrying about whether a query is DML or DDL, we only need to ask one question: Does it return a result set, or does it simply affect rows or change schema? If it returns data, it is Select. If not, it is Non-Select. This distinction also explains why the Statement interface in JDBC provides different methods for query execution. Some methods are designed specifically for Select operations, while others handle Non-Select operations.

---

Interview Questions on SQL Command Classification

Q1. What are the main categories of SQL commands according to DBA specifications?
Answer: According to DBA classification, SQL commands are categorized into six groups:

1. DDL (Data Definition Language) – e.g., CREATE, ALTER, DROP

2. DML (Data Manipulation Language) – e.g., INSERT, UPDATE, DELETE

3. DQL (Data Query Language) – e.g., SELECT

4. DCL (Data Control Language) – e.g., GRANT, REVOKE, ALTER PASSWORD

5. DA (Database Administration commands) – e.g., START AUDIT, STOP AUDIT

6. TCL (Transaction Control Language) – e.g., COMMIT, ROLLBACK, SAVEPOINT

---

Q2. How are SQL commands classified from a Java developer’s point of view?
Answer: For Java developers working with JDBC, SQL commands are simplified into two categories:

1. Select operations (DQL) – which return a ResultSet.

2. Non-Select operations (DDL, DML, DCL, DA, TCL) – which do not return a ResultSet but instead affect rows or schema.

---

Section 6: Statement Object Methods Explained

When we talk about interacting with a database through JDBC, the Statement object becomes the real bridge that carries our SQL queries from Java to the database engine. Think of the Statement object as a messenger: once we have established the connection between our Java program and the database, it is the Statement object that actually carries out the command and delivers the response back. But depending on the type of SQL command we want to execute, JDBC provides different methods within the Statement object.

From a database perspective, SQL commands are classified as DDL, DML, DQL, DCL, and TCL. But for a Java developer, the most practical classification is much simpler: Select operations and Non-Select operations. A Select operation means retrieving records from the database using the SELECT command, whereas Non-Select operations include everything else, such as inserting, updating, or deleting rows, or modifying the structure of tables.

Now let’s look at how the Statement object provides three different methods to handle these cases—executeQuery(), executeUpdate(), and execute()—each designed for a specific scenario.

---

1. executeQuery() – For Select Operations

The executeQuery() method is designed exclusively for Select operations, which means it should be used only when the SQL command begins with a SELECT keyword. When executed, it returns a group of records from the database, packaged neatly inside a ResultSet object.

The method signature is:

public ResultSet executeQuery(String sqlSelectQuery) throws SQLException;

For example, suppose we want to fetch student details from the database. The code would look like this:

ResultSet resultSet = statement.executeQuery("select id, name, age, address from student");

Here, the database processes the query and sends back multiple records. These records are stored inside the ResultSet object, and we can traverse through them using the next() method. This is why executeQuery() is strictly tied to retrieval operations—it always expects a set of results in return.

---

2. executeUpdate() – For Non-Select Operations

If executeQuery() is the method to retrieve records, then executeUpdate() is its counterpart for operations that modify the database but don’t necessarily return records. This includes INSERT, UPDATE, and DELETE statements, as well as certain DDL operations like creating or dropping tables.

Unlike executeQuery(), this method doesn’t return a ResultSet. Instead, it gives back an integer value that indicates the number of rows affected by the operation.

The method signature is:

public int executeUpdate(String sqlNonSelectQuery) throws SQLException;

For example:

int rowAffected = statement.executeUpdate("delete from student where id = 10");
System.out.println("No. of rows affected is :: " + rowAffected);

If one row is deleted, the output will be No. of rows affected is :: 1. This return value is particularly useful when we want to confirm whether our database modification was successful and how many rows were impacted.

---

3. execute() – For Dynamic Queries

While executeQuery() and executeUpdate() are straightforward, there is a third method—execute()—that acts as a more flexible alternative. The execute() method can handle both Select and Non-Select operations, making it useful when the type of query is not known in advance or when it is determined dynamically at runtime.

The method signature is:

public boolean execute(String sql) throws SQLException;

This method returns a boolean value. If the return value is true, it indicates that the executed SQL statement was a Select query, and a ResultSet is available. If it returns false, it means the query was a Non-Select operation, and an integer row count can be obtained using getUpdateCount().

Here is an example of how it works:

boolean value = statement.execute(dynamicQuery);

if (value == true) {
    // It is a Select query
    ResultSet resultSet = statement.getResultSet();
    // process the resultSet
} else {
    // It is a Non-Select query
    int rowCount = statement.getUpdateCount();
    System.out.println("Number of rows affected is :: " + rowCount);
}

This approach is particularly useful in scenarios where queries are being generated dynamically, for instance, when the type of SQL command is not fixed but depends on user input or configuration.

---

Putting It All Together

To summarize, the Statement object provides three different ways to execute SQL commands:

• executeQuery() – used exclusively for Select operations and always returns a ResultSet.

• executeUpdate() – used for Insert, Update, Delete, and DDL operations, and returns the number of rows affected.

• execute() – a flexible method that can handle both Select and Non-Select queries, returning a boolean to indicate the type of result.

This classification ensures that as Java developers, we have clear control over how to interact with the database depending on the type of SQL operation we want to perform.

---

Interview Summary – Statement Object Methods in JDBC

In JDBC, the Statement object is used to send SQL queries to the database. It provides three main methods:

1. executeQuery(String sql) –

   • Used only for SELECT statements.

   • Returns a ResultSet object containing the records fetched.

2. executeUpdate(String sql) –

   • Used for Non-Select operations like INSERT, UPDATE, DELETE, and DDL commands.

   • Returns an int representing the number of rows affected.

3. execute(String sql) –

   • Used when the type of query is not known in advance.

   • Returns a boolean:

     • true → query is a Select → retrieve data using getResultSet().

     • false → query is Non-Select → get update count using getUpdateCount().

---

👉 If an interviewer asks:

• Q: What is the difference between executeQuery and executeUpdate?
  A: executeQuery is only for Select and returns a ResultSet, while executeUpdate is for non-select queries and returns rows affected.

• Q: When do we use execute()?
  A: When the query type (Select or Non-Select) is not known until runtime.

Tricky Interview Questions on Statement Object Methods

Q1: What happens if you call executeQuery() with an INSERT or UPDATE statement?

• Answer: It will throw an SQLException. executeQuery() is strictly meant for SQL Select operations and always expects a ResultSet.

Q2: What happens if you call executeUpdate() with a SELECT statement?

• Answer: It will also throw an SQLException because executeUpdate() is only meant for DML/DDL operations (Insert, Update, Delete, Create, Drop, etc.) and expects an integer update count, not a ResultSet.

Q3: Why do we need the execute() method if we already have executeQuery() and executeUpdate()?

• Answer:

  • In most cases, developers know the SQL type in advance, so they use the specific methods.

  • However, in situations like executing dynamic queries (for example, queries built at runtime or stored procedures where the output may vary), we may not know whether it is Select or Non-Select.

  • execute() is used in such cases because it can handle both, returning a boolean (true for Select, false for Non-Select).

---

Q4: Between executeQuery() and executeUpdate(), which one is more commonly used in real-time projects?

• Answer:

  • executeQuery() is very commonly used because applications often need to fetch and display data.

  • executeUpdate() is equally important for CRUD operations (Insert, Update, Delete).

  • In real-time, both are used frequently, but developers usually prefer them over execute() because they are more specific and less error-prone.

---

Q5: Can you explain the return types of all three methods?

• Answer:

  • executeQuery(String sql) → ResultSet (holds data).

  • executeUpdate(String sql) → int (number of rows affected).

  • execute(String sql) → boolean (true if Select, false if Non-Select).

---

7. Non-Select Operations with Code Examples

In the previous sections, we explored how executeQuery() is used in JDBC for Select operations, where the output is a ResultSet containing multiple rows of data. However, not all database interactions are about retrieving records. Very often in real-world projects, we need to perform operations such as inserting new records, updating existing records, or deleting unwanted data. Collectively, these are referred to as non-select operations in JDBC.

From a developer’s perspective, these operations are equally crucial as Select queries because they represent the data manipulation part of database-driven applications. In JDBC, non-select operations are executed using the executeUpdate() method of the Statement object. Unlike executeQuery(), which returns a ResultSet, the executeUpdate() method returns an integer value that represents the number of rows affected by the operation.

To truly understand non-select operations, let us walk through three practical examples: DeleteApp, InsertApp, and UpdateApp. Each example highlights how a specific non-select query is written, executed, and verified in both Java and MySQL environments.

---

7.1 DeleteApp – Deleting Records Using JDBC

Deletion is a common requirement in almost every application. For example, when a student leaves an institution, their record may need to be removed from the student table. In JDBC, this is done with a DELETE SQL statement and the executeUpdate() method.

Below is the Java program that demonstrates how to delete a record with a specific id from the student table.

package in.rohit.main;

import java.sql.*;

public class DeleteApp {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        try {
            // Step 2: Establish the Connection
            String url = "jdbc:mysql:///Rohit";
            String user = "Rohit";
            String password = "Rohit2004";
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("Connection object created...");

            // Step 3: Create statement Object and send the Query
            statement = connection.createStatement();
            System.out.println("Statement object created...");

            // Step 4: Execute the Query
            String sqlDeleteQuery = "delete from student where id=2"; 
            int rowAffected = statement.executeUpdate(sqlDeleteQuery);
            System.out.println("No of Rows affected is::" + rowAffected);

        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Step 6: Closing the resources 
            try {
                if (resultset != null) resultset.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

Execution Output (Console):

Connection object created...
Statement object created...
No of Rows affected is::1

The console confirms that one record has been deleted from the table.

Verifying in MySQL:

mysql> select * from student;
+----+---------------+------+---------+
| id | name          | age  | address |
+----+---------------+------+---------+
|  1 | Rohit Gawande |   21 | Nagpur  |
|  3 | Virat Kohli   |   37 | Delhi   |
|  4 | Shreyas       |   32 | PBKS    |
+----+---------------+------+---------+
3 rows in set (0.00 sec)

The output shows that the record with id = 2 has been removed successfully.

---

7.2 InsertApp – Inserting Records with Static Values

Insertion is the first step in working with any data-driven system. For instance, when a new student joins the college, their details must be recorded in the database. In JDBC, an INSERT SQL query is executed using the executeUpdate() method.

Below is a simple program that inserts a new student record into the student table using static values hardcoded in the query.

package in.rohit.main;

import java.sql.*;

public class InsertApp {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        try {
            // Step 2: Establish the Connection
            String url = "jdbc:mysql:///Rohit";
            String user = "Rohit";
            String password = "Rohit2004";
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("Connection object created...");

            // Step 3: Create statement Object and send the Query
            statement = connection.createStatement();
            System.out.println("Statement object created...");

            // Step 4: Execute the Query
            String sqlInsertQuery = "insert into student (`name`,`age`,`address`) values ('Rohit',49,'MI')"; 
            int rowAffected = statement.executeUpdate(sqlInsertQuery);
            System.out.println("No of Rows affected is::" + rowAffected);

        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Step 6: Closing the resources 
            try {
                if (resultset != null) resultset.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

Execution Output (Console):

Connection object created...
Statement object created...
No of Rows affected is::1

Verifying in MySQL:

mysql> select * from student;
+----+---------------+------+---------+
| id | name          | age  | address |
+----+---------------+------+---------+
|  1 | Rohit Gawande |   21 | Nagpur  |
|  3 | Virat Kohli   |   37 | Delhi   |
|  4 | Shreyas       |   32 | PBKS    |
|  5 | Rohit         |   49 | MI      |
+----+---------------+------+---------+
4 rows in set (0.00 sec)

The record with id = 5, name = Rohit, age = 49, and address = MI has been successfully added.

---

7.3 UpdateApp – Updating Existing Records

Updating is equally important because data changes over time. For example, if a student changes their name or address, the system must update the existing record rather than insert a new one. In JDBC, updates are performed using the UPDATE SQL command with executeUpdate().

Below is the program demonstrating how to update a record in the student table.

package in.rohit.main;

import java.sql.*;

public class UpdateApp {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        try {
            // Step 2: Establish the Connection
            String url = "jdbc:mysql:///Rohit";
            String user = "Rohit";
            String password = "Rohit2004";
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("Connection object created...");

            // Step 3: Create statement Object and send the Query
            statement = connection.createStatement();
            System.out.println("Statement object created...");

            // Step 4: Execute the Query
            String sqlUpdateQuery = "update student set name='Rohit Sharma' where id=5"; 
            int rowAffected = statement.executeUpdate(sqlUpdateQuery);
            System.out.println("No of Rows affected is::" + rowAffected);

        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Step 6: Closing the resources 
            try {
                if (resultset != null) resultset.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

Execution Output (Console):

Connection object created...
Statement object created...
No of Rows affected is::1

Verifying in MySQL:

mysql> select * from student;
+----+---------------+------+---------+
| id | name          | age  | address |
+----+---------------+------+---------+
|  1 | Rohit Gawande |   21 | Nagpur  |
|  3 | Virat Kohli   |   37 | Delhi   |
|  4 | Shreyas       |   32 | PBKS    |
|  5 | Rohit Sharma  |   49 | MI      |
+----+---------------+------+---------+
4 rows in set (0.00 sec)

The record with id = 5 has been updated successfully. The name field, which earlier contained "Rohit", has now been modified to "Rohit Sharma".

---

Summary of Non-Select Operations

Through these three programs—DeleteApp, InsertApp, and UpdateApp—we can clearly observe how JDBC enables direct communication with the database for modifying data.

• DeleteApp removes a specific record.

• InsertApp adds a new record with predefined values.

• UpdateApp modifies existing records based on a condition.

All three operations rely on the executeUpdate() method, which always returns the number of rows affected by the operation. This makes it easier for the developer to verify whether the database update was successful.

---

8. Dynamic Input for SQL Queries

In the previous section, we saw how Non-Select operations (Insert, Update, Delete) can be executed in JDBC using the Statement object. However, one major limitation still exists in the programs we wrote earlier:

All the queries contained hardcoded values.

For example, in InsertApp, the values "Rohit", 21, and "Nagpur" were directly written inside the query string. While this works for small demonstrations, in real-world applications we almost never know the values at compile time. Instead, we collect them from users at runtime — through a GUI form, a web application, or a command-line interface.

This leads us to the concept of Dynamic SQL Input.

---

8.1 Why Hardcoding is a Problem

1. Lack of Flexibility – If values are fixed in the source code, we need to recompile the program each time we want to insert or update different data.

2. Not User-Friendly – In a practical application, end-users should provide input dynamically.

3. Not Scalable – For applications like banking systems, student management systems, or e-commerce platforms, queries must adapt based on user actions, not static data.

That’s why accepting user input at runtime is essential.

---

8.2 Approaches for Dynamic Query Construction

Before we directly jump into coding, let us understand the three major approaches to dynamically prepare SQL queries.

Approach 1 – String Concatenation

In this method, we take user input using Scanner and then concatenate it directly into the SQL string.

Example:

String name = sc.next();
int age = sc.nextInt();
String address = sc.next();

String sql = "INSERT INTO student (name, age, address) VALUES ('" + name + "', " + age + ", '" + address + "')";

This works, but the query becomes messy and hard to maintain. More importantly, it makes the application vulnerable to SQL Injection attacks (a serious security issue, which we will discuss later when we introduce PreparedStatement).

---

Approach 2 – String Concatenation with Placeholders (Manual Formatting)

Here we still use concatenation, but we try to keep the query string cleaner by separating query structure from values.

Example:

String sql = "INSERT INTO student (name, age, address) VALUES ('" + name + "', " + age + ", '" + address + "')";

The structure is better organized, but still prone to SQL injection and formatting issues if not handled carefully.

---

Approach 3 – Using String.format()

This is a more elegant way. Instead of messy concatenations, we use placeholders (%s, %d) inside the SQL string and replace them with actual user input using String.format().

Example:

String sql = String.format(
    "INSERT INTO student (name, age, address) VALUES ('%s', %d, '%s')",
    name, age, address
);

This improves readability and reduces mistakes, though it still does not fully eliminate SQL injection risks. But from a learning perspective, it’s a clean way to build dynamic queries when using Statement.

---

8.3 Full Example Program – Dynamic Insert Using User Input

Now let’s write a complete JDBC program where the user can enter student details at runtime, and those details will be inserted into the student table dynamically.

package in.rohit.main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class InsertAppDynamic {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        Scanner scanner = null;

        try {
            // Step 1: Take user input
            scanner = new Scanner(System.in);
            System.out.print("Enter student name: ");
            String name = scanner.nextLine();
            System.out.print("Enter student age: ");
            int age = scanner.nextInt();
            scanner.nextLine(); // consume leftover newline
            System.out.print("Enter student address: ");
            String address = scanner.nextLine();

            // Step 2: Establish the Connection
            String url = "jdbc:mysql:///Rohit";
            String user = "Rohit";
            String password = "root";
            connection = DriverManager.getConnection(url, user, password);

            // Step 3: Create Statement object
            statement = connection.createStatement();

            // Step 4: Construct SQL Query dynamically using String.format()
            String sqlQuery = String.format(
                "INSERT INTO student (name, age, address) VALUES ('%s', %d, '%s')",
                name, age, address
            );

            // Step 5: Execute query
            int rowCount = statement.executeUpdate(sqlQuery);

            // Step 6: Process result
            if (rowCount == 1) {
                System.out.println("Record inserted successfully!");
            } else {
                System.out.println("Record insertion failed.");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Step 7: Close resources
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
                if (scanner != null) scanner.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

---

8.4 Sample Execution

User Input (Console):

Enter student name: Rahul
Enter student age: 25
Enter student address: Pune

Program Output:

Record inserted successfully!

Database Verification in MySQL:

mysql> SELECT * FROM student;
+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  1 | Rohit |  21 | Nagpur  |
|  2 | Rahul |  25 | Pune    |
+----+-------+-----+---------+

---

8.5 Key Takeaways

1. Hardcoding values in SQL queries is impractical for real applications.

2. Using Scanner allows us to collect dynamic user input at runtime.

3. String.format() provides a cleaner way to construct queries compared to raw concatenation.

4. However, these approaches are still vulnerable to SQL Injection.

5. The safer solution is to use PreparedStatement, which we will explore in upcoming chapters.

---

9. Reusable JDBC Utility Class

When we started writing JDBC programs, one thing quickly became apparent: every single piece of code had a lot of repetitive steps. Whether it was the SelectApp, InsertApp, DeleteApp, or UpdateApp, the first few lines always followed the same pattern. We established a connection, created a statement, and finally, when everything was done, we closed the resources.

At first, this might not feel like an issue. After all, repetition is common when you are learning something new. But as soon as you begin to scale your project or write multiple JDBC applications in a real-world environment, repeating the same boilerplate code in every program becomes not only tedious but also error-prone. Imagine writing a dozen applications where you need to connect to the same database. If your database URL, username, or password changes, you would have to manually modify each file, increasing the chances of missing one. The problem is not about whether it works but about how maintainable and scalable it is.

This is where the idea of reusability comes into the picture. In software development, reusable code is like having a toolkit where essential tools are stored in one place. Instead of building a hammer every time you want to drive a nail, you simply reuse the one you already have. Similarly, with JDBC, we want to write our connection setup and resource cleanup code once, store it in a centralized place, and then reuse it in all other applications.

Why Reuse Connection Setup and Cleanup Code?

The importance of reusing JDBC connection and cleanup code can be understood from three perspectives.

First, it ensures consistency. When you centralize the logic for database connection, all your applications follow the same standard procedure. You do not risk writing slightly different versions in different programs, which could lead to unexpected issues.

Second, it improves maintainability. If tomorrow your database credentials change or you decide to switch from MySQL to another database, you will only need to make changes in a single file instead of modifying dozens of applications individually.

Third, it enhances readability and focus. By removing boilerplate code from each application, the main class (like SelectApp or InsertApp) can focus solely on the business logic, such as executing queries and processing results, rather than being cluttered with repetitive setup code. This separation of concerns aligns perfectly with professional development practices.

Designing the JdbcUtil Class

To achieve this reusability, we can design a dedicated utility class. In Java, a utility class is a class that provides common, reusable functionality through static methods. Such classes usually cannot be instantiated because they are not meant to represent objects but to act as helpers.

Our JdbcUtil class will contain two main responsibilities:

1. Establishing the Connection: A static method that returns a Connection object.

2. Cleaning Up Resources: A static method that takes a Connection, Statement, and ResultSet and closes them safely.

The design of this class has a few key features.

• Private Constructor: To prevent object creation. Since all methods are static, there is no reason for anyone to create an instance of JdbcUtil. Declaring a private constructor enforces this rule.

• Static Block: To ensure the JDBC driver is loaded only once when the class is loaded into memory. This eliminates the need to write Class.forName("com.mysql.cj.jdbc.Driver") in every application.

• Static Methods: For providing a connection (getConnection) and cleaning up resources (cleanUp). These methods can be called directly using the class name, making them easily accessible across all applications.

Here’s the complete implementation:

package in.rohit.util;

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

public class JdbcUtil {

    // Private constructor to prevent instantiation
    private JdbcUtil() {
    }

    // Static block for loading the driver
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException ce) {
            ce.printStackTrace();
        }
    }

    // Method to establish the connection
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:mysql:///Rohit";
        String user = "Rohit";
        String password = "Rohit2004";
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("Connection object created...");
        return connection;
    }

    // Method to close resources
    public static void cleanUp(Connection con, Statement statement, ResultSet resultset) throws SQLException {
        if (con != null) {
            con.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (resultset != null) {
            resultset.close();
        }
    }
}

Centralized Resource Management

With this JdbcUtil class in place, resource management becomes centralized and highly efficient. Every application can now rely on the same methods for establishing and closing connections.

For example, in your SelectApp or InsertApp, instead of manually writing repetitive connection code, you can simply write:

connection = JdbcUtil.getConnection();

And when closing resources, you just call:

JdbcUtil.cleanUp(connection, statement, resultset);

This design not only saves time but also enforces a disciplined way of handling database resources. By placing all resource-handling logic in one place, we minimize the chances of leaving connections open, which is one of the most common mistakes beginners make with JDBC.

In real-world enterprise projects, such utility classes are a standard practice. As the project grows, developers may even extend this concept into more sophisticated frameworks, but at its core, the principle remains the same: centralize repetitive logic to improve efficiency, maintainability, and clarity.

---

10. Formatting Output Using printf

When working with JDBC applications, displaying the output neatly is just as important as fetching it from the database. Raw, unformatted output can be difficult to read and interpret, especially when dealing with multiple columns and rows. Imagine a student management system where you retrieve student data—if the names, ages, and addresses are all printed unevenly, the console output looks messy and confusing.

This is where Java’s printf method comes into play. Instead of printing plain values one after another, printf allows us to format the output in a structured, tabular style. It gives us control over spacing, alignment, precision, and readability, making console-based JDBC applications appear more professional.

Why Use printf for JDBC Output?

1. Readability: Properly aligned columns make it easier to compare and analyze data at a glance.

2. Professional Output: A neatly formatted table looks like something generated by an actual reporting tool.

3. Control Over Data Representation: You can control the number of decimal places, align text to the left or right, and define the width of each column.

For instance, when printing a list of students, you might want names aligned to the left, ages aligned to the right, and addresses occupying a wider column for readability.

The Basics of printf Formatting

The printf method works with format specifiers, which act like placeholders in the string. Each placeholder corresponds to a value you pass. Some common specifiers are:

• %s → String

• %d → Integer

• %f → Floating-point number

• %n → New line (platform-independent, better than \n)

You can also control width and alignment:

• %-15s → Left-align a string in a field of 15 characters

• %10d → Right-align an integer in a field of 10 characters

Example: Printing Student Data

Here’s how we can combine JDBC with printf for formatted output:

package in.rohit.main;

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

import in.rohit.util.JdbcUtil;

public class SelectAppFormatted {

    public static void main(String[] args) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;

        try {
            // Establish connection using utility class
            connection = JdbcUtil.getConnection();

            // Create statement
            statement = connection.createStatement();

            // Execute query
            String query = "SELECT id, name, age, address FROM student";
            resultset = statement.executeQuery(query);

            // Print header
            System.out.printf("%-5s %-20s %-5s %-20s%n", "ID", "NAME", "AGE", "ADDRESS");
            System.out.println("--------------------------------------------------------");

            // Print each row
            while (resultset.next()) {
                int id = resultset.getInt("id");
                String name = resultset.getString("name");
                int age = resultset.getInt("age");
                String address = resultset.getString("address");

                System.out.printf("%-5d %-20s %-5d %-20s%n", id, name, age, address);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JdbcUtil.cleanUp(connection, statement, resultset);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

Sample Output

If your student table has the following data:

The console output will look like this:

ID    NAME                 AGE   ADDRESS             
--------------------------------------------------------
1     Rohit Gawande        21    Nagpur              
2     Virat Kohli          37    Delhi               
3     Rohit Sharma         39    Mumbai

Notice how the columns are perfectly aligned. Even if the names or addresses differ in length, the formatting keeps the output structured and easy to read.

Real-World Relevance

In enterprise-level applications, console output formatting might feel less significant since most projects rely on GUI or web interfaces. However, during development, debugging, or even in simple reporting tools, formatted console output can save developers a lot of time. It is also invaluable in interviews and assignments, where showing clean, professional output leaves a strong impression.

In short, printf is not just about beautifying console output—it’s about writing programs that are easy to use, interpret, and maintain.

---

Section 11: Conclusion of Chapter

As we come to the conclusion of this chapter, it is important to step back and recognize the bigger picture behind all the programs we have written so far. Whether we are executing a simple SELECT statement to fetch data or performing operations such as INSERT, UPDATE, or DELETE, there are certain steps that remain constant in every JDBC program. These steps form the backbone of database interaction in Java and should be second nature to any developer who wishes to work with JDBC effectively.

The first common step across all programs is establishing a connection with the database. Without a successful connection, no SQL command can be executed, and no interaction with the underlying database can take place. That is why we always begin with obtaining a Connection object, either directly through the DriverManager.getConnection() method or indirectly through utility classes that we create for cleaner code management.

The second common step is handling exceptions. Since database operations are error-prone due to factors like invalid queries, incorrect credentials, missing drivers, or connectivity issues, we must always surround our code with proper try-catch blocks. The structured handling of exceptions ensures that our program does not fail abruptly and also provides meaningful messages for debugging.

The third common step is closing the resources. Database resources such as Connection, Statement, and ResultSet consume memory and system-level handles. If left open, they can lead to performance degradation and memory leaks. Therefore, it is a golden rule in JDBC programming to close all resources in the reverse order of their creation, typically within a finally block or through a utility cleanup method. This ensures that even if an exception occurs midway, the resources will still be released safely.

While these steps remain the same, what varies in JDBC programs is the type of query we are executing. If the query is a SELECT operation, we receive a ResultSet object containing multiple rows of data that need to be processed one by one. In contrast, if the query is a NON-SELECT operation such as INSERT, UPDATE, or DELETE, the program does not return a collection of records. Instead, it provides an integer value indicating the number of rows affected. This distinction is fundamental, and it dictates how we write the processing logic after executing the SQL statement.

Another important takeaway from this chapter is the significance of writing reusable and utility-based code. As we progressed through multiple programs, one thing became evident: the steps of establishing a connection, closing resources, and handling exceptions were repeated in every single program. If we continued writing JDBC applications in this repetitive way, the code would become cluttered, harder to maintain, and prone to human error. To address this, we introduced the concept of a utility class, such as JdbcUtil, which encapsulates the repetitive logic into reusable static methods. This approach ensures that our main program remains clean, focused on the actual SQL operation, and free from unnecessary boilerplate code.

In essence, the journey of this chapter highlights two complementary aspects of JDBC programming. On one side, we have the core steps that never change, which give JDBC its structure and reliability. On the other side, we have the flexibility of handling different query types, which allows us to adapt JDBC to a wide variety of database operations. By combining these two aspects with the discipline of writing clean and reusable code, we create programs that are both efficient and maintainable.

This brings us to the end of Chapter 2, where we explored not only the technical execution of select, insert, update, and delete operations, but also the underlying philosophy of how JDBC programs should be structured. From here onward, every new concept we encounter in JDBC will build upon these foundations, reinforcing the idea that a good developer is not just someone who can write code that works, but someone who can write code that is clean, maintainable, and scalable for real-world projects.

---

Section 12: Interview Questions and Answers

As we conclude this chapter, let us move into a space that directly connects learning with interviews. JDBC is one of those areas where interviewers often test both coding ability and conceptual understanding. Below are some frequently asked questions, explained in detail.

---

Q1. What is the difference between executeQuery(), executeUpdate(), and execute()?

This is one of the most common and foundational JDBC questions.

• executeQuery() is specifically designed for SELECT operations. It always returns a ResultSet object that contains the rows fetched from the database.

• executeUpdate() is used for data manipulation operations such as INSERT, UPDATE, or DELETE. Instead of returning rows, it returns an integer indicating how many records were affected.

• execute() is a more general-purpose method. It can execute both select and non-select queries. The method returns a boolean: true if the result is a ResultSet, and false if it is an integer update count.

Interviewers like this question because it distinguishes candidates who understand query behavior from those who have only copied code.

---

Q2. What is autoloading in JDBC and how does it work internally?

Before JDBC 4.x, programmers had to manually load and register the driver using Class.forName(). This meant remembering and tightly coupling your code with the driver’s fully qualified class name.

With JDBC 4.x, autoloading was introduced. When you call DriverManager.getConnection(), the JVM looks at the JDBC URL to determine the target database. It then scans the classpath, finds the database JAR, and reads its META-INF/services/java.sql.Driver file. The driver class listed there is automatically loaded and registered.

This reduces boilerplate code, decouples applications from third-party driver names, and makes code cleaner.

---

Q3. Why should we close JDBC resources in reverse order?

JDBC objects are dependent on each other.

• A ResultSet depends on the Statement that created it.

• A Statement depends on the Connection that opened it.

If you close the Connection first, the Statement and ResultSet become invalid instantly, which can throw exceptions. That’s why the recommended order is the reverse of creation: first close ResultSet, then Statement, and finally Connection.

This logic-based answer shows the interviewer that you understand object dependencies, not just rules.

---

Q4. Why is it better to use column index instead of column name while retrieving data from a ResultSet?

When you use a column name like getString("name"), the driver internally resolves that name into a column index. This involves an additional lookup step.

If you directly use a column index like getString(2), you skip this resolution, which makes retrieval slightly faster. This performance gain becomes noticeable in applications handling large datasets.

However, column names improve readability and maintainability. A strong interview answer here acknowledges both sides: indexes improve performance, names improve clarity.

---

Q5. What are the common steps across all JDBC programs, and what varies between them?

Every JDBC program follows a skeleton process:

1. Establish the connection.

2. Create a statement.

3. Execute the query.

4. Handle exceptions.

5. Close resources.

What varies is how queries are executed and how results are processed. For SELECT operations, we work with a ResultSet. For INSERT, UPDATE, or DELETE, we focus on an integer update count.

This distinction shows that while the workflow is universal, the query type defines the processing.

---