08. WAP to display the ACCNO and NAME of the accounts whose bal less Rs.70000 30-Oct-24 | Ashok IT

Summary

The class covers a JDBC exercise for retrieving account numbers and account-holder names, then introduces how database servers process SQL and why real applications generally use PreparedStatement instead of Statement for repeated queries.

1. JDBC exercise: Display accounts below a balance threshold

• The exercise is to display only the account number and name of accounts whose balance is less than 70,000.
• The instructor initially says “more than 70,000,” but the explanation and demonstration use less than 70,000. The title also specifies “less Rs. 70000.”
• The program uses an existing JDBC setup:
  1. Establish a connection.
  2. Create a Statement.
  3. Execute a SELECT query that requests only the account-number and name columns, rather than using SELECT *.
  4. Read the returned rows through a ResultSet.
  5. Close the result set, statement, and connection.
• Because a query can return multiple rows, a loop is needed to process the results.
• The instructor demonstrates that a program may appear to do nothing if the query returns no rows. To make the application’s response clear, it should print a message such as “With this balance range, no account exists.”
• In the example, the initial balance range returns no matching rows. When the threshold is changed to less than 70,000, the instructor says there are three matching rows.

2. Handling the first ResultSet row correctly

A common mistake is to use if (rs.next()) to check whether a row exists, then use a while (rs.next()) loop to process rows. The if call advances the cursor to the first row, and the next call in the while loop advances it again, skipping that first row.

The demonstrated approach is to:

1. Use if (rs.next()) to check whether a first row exists.
2. If it does, process the current row inside a do-while loop.
3. Call rs.next() in the loop condition to advance through the remaining rows.
4. Use an else branch to report that no matching account exists.

This illustrates a practical use for do-while: its body runs once after the initial condition has positioned the cursor on the first row.

3. What happens when a DBMS receives SQL

The instructor describes three stages inside the database management system:

1. Query compilation

   • The DBMS checks whether the SQL statement is syntactically valid, including its keywords and clauses.
   • If the SQL is invalid, the DBMS reports an error, which can eventually surface in the Java program as an SQL exception.

2. Query-plan generation

   • If the syntax is valid, the DBMS considers ways to execute the statement efficiently.
   • It selects an execution plan and, as described in the lesson, keeps the query or plan in cache for subsequent use.

3. Query execution

   • The DBMS executes the statement and returns the result to the client.

4. Why repeated queries should use PreparedStatement

A Statement can submit SQL statements to the DBMS and is useful for learning basic database operations. However, the instructor cautions against using Statement in real applications when the same SQL statement is submitted repeatedly, because it can compromise performance.

This pattern is common in online services. For example, many bank customers may use the same balance-inquiry service. The SQL structure remains the same while values such as the account number change.

The proposed alternative is java.sql.PreparedStatement, described as the appropriate choice when the same query is used repeatedly. A detailed explanation and examples are deferred to a later class.

The instructor outlines the general workflow for a prepared-statement example:

1. Establish the connection.
2. Create the prepared statement.
3. Bind its parameters.
4. Execute it.
5. Close the resources.

5. Brief Eclipse demonstration

The instructor demonstrates how to run a JDBC program from Eclipse, while emphasizing that this is not a full Eclipse lesson.

The workflow shown is:

1. Create a Java project and package, then add a class.
2. Write the JDBC code, using Eclipse’s code-completion features.
3. If execution fails because the driver is unavailable, right-click the project and use Build Path → Add External Archives to add the JDBC driver JAR.
4. Run the program again after adding the driver.

The instructor notes that relying on an IDE can distract learners from the JDBC concepts themselves.

6. Questions and clarifications

• Retrieving selected columns: Column positions in the ResultSet correspond to the order in the SELECT list. In the example, the account number is the first column and the name is the second.
• Discovering an unknown number of columns: The instructor mentions ResultSetMetaData, but says it is beyond the scope of the current lesson.
• Commits: The instructor distinguishes between database access through a SQL command-line client and access through a Java connection. JDBC connections are normally opened with auto-commit enabled, so changes are committed automatically unless the connection is configured differently.
• Other questions: Students ask about Eclipse installation, accessing class recordings, and configuring the JDBC driver. The instructor directs students to the institute’s administrators for some matters and says a separate Eclipse session will be provided.

Speakers and sources featured

• Instructor — Leads the Advanced Java/JDBC class; the video title identifies the instructor as associated with Ashok IT.
• Student participants — Ask questions during the class. Names audible in the subtitles include Rajkumar, Lakshmi, Abhinav, ROV (transcription uncertain), and Apu; other participants are unnamed.
• Technical sources discussed — JDBC, a DBMS/database server, Eclipse, and a MySQL JDBC driver are referenced as tools or concepts, not as separate speakers.