07. WAP program to implement the use-case of interactive bal enquiry. By LN RAO 29-Oct-24 | Ashok IT

Summary

The lesson demonstrates a JDBC balance-inquiry program. The program prompts the user for an account number, queries the database for that account’s balance, and displays either the balance or a message indicating that the account does not exist.

The instructor presents this as an exercise in JDBC fundamentals. In a real application, the account number might come from a web form, but the database-access concepts would be the same.

Main Ideas and Implementation Steps

1. Connect to the database

   • Import the JDBC classes and establish a connection using DriverManager.getConnection(...).
   • The connection URL includes the database type, server address, port, and database name, along with a username and password.
   • The instructor distinguishes between the driver manager, which provides the connection, and the JDBC driver, which establishes it.

2. Create a statement

   • Use the connection to create a Statement object, which submits the SQL query.

3. Read the account number

   • Use a Scanner to prompt the user and read the account number from keyboard input.
   • Supply the account number stored in the Java variable to the SQL query.

4. Query for the balance

   • Select only the balance column. For example: sql SELECT balance FROM account WHERE account_number = ...

   • Because the account number is treated as a unique key, the query can return zero or one record. Therefore, the program uses an if statement rather than a loop.

5. Check for a result and display the response

   • The query returns a ResultSet, whose cursor initially sits before the records.
   • Call resultSet.next() to attempt to move to the first record:
     • If it returns true, a record exists. Retrieve the balance using a getter such as getDouble(1) and display it.
     • If it returns false, no matching account was found; display a message such as “Account does not exist.”
   • The column index passed to a getter refers to the columns in the query’s result set, not the column’s position in the database table. Since this query selects only balance, the result has one column, so the index is 1. Getter methods can also use a column name instead of an index.

6. Close JDBC resources

   • Close resources in this order:
     1. ResultSet
     2. Statement
     3. Connection
   • Java garbage collection may reclaim unreachable Java objects after main finishes, but this does not necessarily release resources held by the database server. Closing the connection signals that the client is finished and allows server-side networking resources, including the client socket, to be released. Closing the statement and result set similarly releases resources associated with query execution and returned data.

Additional Concepts Covered

• Stack and heap memory: Local variables and references belong to a method’s stack area, while objects are created in the heap. After main ends, its local variables are released, and objects without references become eligible for garbage collection.
• JDBC operation types: executeUpdate() is used for insert, update, and delete operations, while executeQuery() is used to retrieve data. The lesson focuses on retrieval and does not demonstrate table creation or other DDL operations.
• Example outcomes: One run displays a balance of ₹66,000 for an existing account; another reports that the account does not exist.
• Class notes and follow-up: The instructor uploads notes for the session, answers student questions about result-set column indexing, resource-closing order, memory, and server sockets, and says the next lesson will discuss how CRUD operations are handled in real projects.

Speakers and Sources

• LN Rao — instructor and primary speaker.
• Unnamed students — ask questions and offer brief feedback during the class.