What happens in the background within DBMS when an SQL Statement is submitted to it.

Advanced Java: 08

Write a java program to display the Account no & name of the accounts whose balance is more than Rs. 70000

Note: When you execute a program, program should not be silent, what it says with this balance no record is there. (Application should respond good or bad application must not be silent)

So, changes are

Note: Now application starts responding but we miss first record due to this logic (Application should respond good or bad application must not be silent) Just check the following code.

Now how to access first record then. very easy instead of using while loop or condition you have to use do while loop.

Just check out the following code.

Q.1 What happens in the background within DBMS when an SQL Statement is submitted to it?

 

Use case of interactive balance enquiry using java JDBC.

 

Advanced Java: 07

Q. Write a Java program to implement the use case of interactive balance enquiry.

Use Case 1: Enter account no: 1001                                        Use Case 2: Enter account no: 10001

                     Balance is Rs. 66022.0                                                             Account does not exist.

---------------------------------------------------------------------------------------------------------------------------

Memory Representation of above program:

Explanation of above program:

JDBC driver first duty is establishing the connection. Driver is connection creator.

> javac BalanceEnquiryApplication.java  // it compiles the code

> java BalanceEnquiryApplication

Java command goes to the operating system then OS calls the JVM. now JVM loads the .class file into RAM, after that JVM gives call to main method. accordingly, STACK AREA gets created for the main method and which remains present in main memory still main method is in execution.

Once main method completely executes then control goes to JVM. Now JVM will release memory of all the stack area.

Heap Area all the three objects 1. Connection object. 2. Statement Object and 3. Resultset object are there, and they become unreachable objects.

JVM internally keeps eyes on the Heap Area, and he observes there are three unreachable objects, so it automatically releases the memory of the objects, that is deallocation of memory. This is called Garbage Collection in JVM. Even if you don't close then also JVM will take care of this.

Q. How does database server know where the java program in the world is?

Ans: Client IP address and Port Number available in Socket inside database server as a client socket.

Now, imagine 100 of client connected to database server, then its duty of database server to maintain the client socket of each client. It's a burden on database server.

Now, assume if some java program without closing the database connection exit from the connection, then foolish (poor) database server still maintaining that socket of client.

if a greater number of such connections are there which ultimately blocks the real people who wants to connect the database server due to connection limit. So, until and unless you are not closing connection.close() in your java program database server maintains its client socket.

Q. Why to close the connection and statement object?

Ans: To release networking resources maintained by database server. when statement object created at database server some memory is allocated for SQL Statement from client side. So, once we done release that memory also that's why statement.close() instruction must be there in client-side java program.

Q. Why to close Resultset.close()?

Ans: The answer is as follows:

• When the java program submit statement to database server, it gets data from the Hard disk. after that it keeps that data in cache memory (Faste memory) and then send it to JDBC driver.
• Now, question comes to your mind is why do we need cache memory? So, as we know always hard disk operation are slower and time consuming (database is secondary memory slower 1000 times than cache memory) so; to improve the performance we keep data in cache memory. Cache memory is faster and costlier.
• To close this cache memory, we have to close resultSet object. So, this is called graceful exit.
• Note: if same query comes it fetch from cache memory for faster retrieval, if not it returns from the database. 

How to retrieve data from the database into a JDBC Client?

Advanced Java: 06

 Q. How to retrieve data from the database into a JDBC client?

By Submitting "SELECT SQL" Statement we can retrieve data from the database. executeQuery() method of statement object is used to submit select statement to the DBMS.

• executeQuery() method takes SELECT SQL statement as a string argument.
• It returns Resultset object.

Q. Explain about executeQuery() Method?

• executeQuery() method is JDBC API Method.
• used to send SELECT Statement.
• It belongs to statement object.
• It's return type is ResultSet.
• It throws SQLException if anything goes wrong.

Q. Explain about ResultSet()?

• Object oriented representation of database records received from the DBMS (Database server) is nothing but Resultset Object.
• ResultSet Object is logically divided into 3 Partitions.

1. Zero Record Area.
2. Record Area.
3. No Record Area.

• How resultset object internally look like?

• When ResultSet is open, a logical pointer known as Cursor points to zero record area of the resultset.
• When the cursor is pointing to ZERO Record Area or NO RECORD AREA, we should not try to read the data from the resultset. otherwise SQLException is raised.
• When the cursor is pointing to Record holding area then only you have to read the data. so how to move the cursor to record holding area as a programmer?
• java.sql.resultset has a method. "Next ()" to move the cursor.
• This method does two things: 1. Moves the cursor in forward direction by one record area. 2. After moving cursor, it returns true if record is present. It returns false if record is not there.
• ResultSet Object has a getter method to read data from the resultset record. these method takes column number of the resultset record as arguments and return the value.

After Execution of above code output will look like as follows.

Note:

getter methods are overloaded method you can give column names also or column number.

Program to implement an interactive deposit service using JDBC programming model.

 Advanced Java: 05

Q.1 Explain about getConnection() method in Driver-Manager.?

• getConnection() is a JDBC API method (Library Method)
• It belongs to java.sql.DriverManager class.
• It is a static method, so it is being called by class name. like DriverManager.getConnection()
• This method takes 3 Arguments.1. Database URL: Address of the database server. 2. Username. 3. Password.
• This method throws java.sql.SQLException if anything goes wrong while establishing connection with the database server.
• This method is factory method: Inside a method also an object is going to be created and send reference of it to the connection interface.
• This method provides database connection to a JDBC client.

Q.2 What is Port number? Please explain with example.

• Consider a scenario where you want to book a railway ticket manually, so what is the first thing he must know is the railway station address he must know. Similarly, Server IP address reach to server machine. accordingly, getConnection() method connect us to the server.
• As we know on railway station there are several counters for each one like ladies there is sperate counter, senior citizen there is a separate counter, in all general there is a separate counter. accordingly, the counter can work and proceed the task. 
• So counter number is nothing but port number. port number is unique number on server. MySQL 3306 is port number.

Q.3 What is the meaning of D:\> java DepositApplication

• java command given to operating system (OS) then OS calls the JVM, It's JVM duty to load DepositeApplication.class file into memory.
• Now, how can JVM know where this file is located? It knows through classpath. SET Classpath=""
• Note: Without installing MySQL driver, you cannot connect with database server. (mysql-connector-java 8.0.19)
• d:/> SET CLASSPATH=.; D:\mysql-connector-java 8.0.19
• Classpath is an environment variable used by JVM to locate class file.

Q.4 Explain about executeUpdate() method?

• executeUpdate() is a JDBC API method.
• It belongs to java.sql.Statement interface object.
• In real time projects 90% of java objects are interface objects only.
• This method is used to submit INSERT, UPDATE, DELETE SQL statements from the JDBC client to the DBMS.
• This method returns an integer this number represents the number of records affected in the database a result of that submitted SQL Statement.

Q.5 Write a JDBC Program to implement an interactive deposit service.

• Our program should ask the user to enter the account number and amount to be deposited.
• The database status before program execution is as follows.

Write a java program to connect database (MySQL) using JDBC.

 

Advanced Java: 04

Q.1 Write a java Program to connect Database (MySQL) using JDBC.

Q.2 Write a java Program to Store an accounts data (accountno, name, balance) into the database.

• Before writing and executing this program the following things should have done in MySQL DBMS.
• Open MySQL Client Console. enter the password root.
• mysql > create database hdfcdb;
• mysql > use hdfcdb;
• mysql > show tables.
• mysql > create table account (accuntno int (11), name varchar (20), balance double (10,2)).
• mysql > select * from account.

Q.3 Write a java Program to increase the balance of all the accounts by Rs. 1000.

After execution of code:

Detailed explanation of JDBC Programming Model.

Advanced Java: 03

Q.1 Explain about JDBC Programming Model?

a. Requesting for connection or log in to the database server:

• Java program and database server are communicating with each other is like client server architecture.
• It's a client duty request for connection.
• Java program being a client should get connected to the database server.
• In two ways java program gets connected to database server.

1. Driver Manager: (without connection pooling)
2. Datasource: (With Connection pooling)

Q.2 How java program gets connection to the database server?

• Java program makes use of Driver-Manager or DataSource.
• By calling getConnection( ) method on Driver Manager or DataSource, JDBC client request for database connection. 
• Connection connection = DriverManager.getConnection( )
• getConnection( ) is a library method.
• It is a static Method.
• It is used for database connection.
• java program logged into the database server that logging is called one session.
• Object oriented representation of JDBC client session with the database server is nothing but connection object.
• connection is an object. it represents java program session.
• As long as this session is active (i: e Connection is open) so long java program can perform CRUD operation with the database.

Q.3 How to create Statement Object?

• By calling createStatement() method on the connection object, statement object is created.
• Statement statement = connection.createStatement( )
• createStatement( ) method is a instance method.
• It is not a static method; it is on the object method.

Q.4 Why to Create Statement Object?

• Statement object is the designatory object to submit an SQL Statement from a java program to the DBMS.
• when we want to send a SQL, statement use statement object.

Q.5 How to submit the SQL Statement?

• java program objective is performing CRUD operation.
• Without statement we cannot perform CRUD Operation.
• Statement methods have two methods to submit an SQL Statement to the DBMS.

1. executeUpdate( String DML)
2. executeQuery( String DRL)

• executeUpdate() method is used to submit INSERT, UPDATE & DELETE Statement to the DBMS.
• executeQuery() method is used to submit SELECT Statement to the DBMS.

Q.6 Why and How to close the statement?

• by calling close () method on the statement object, statement is closed.
• statement.close()
• To release JDBC resources of the JDBC client maintained by the database server, statement should be in closed in JDBC Client.

Q. 7 Why and How to Close the Statement?

• By calling Close () method on the connection object, connection is closed in the JDBC Client.
• To release networking resources of the JDBC client maintained by the database server, connection should be closed in JDBC client.
• 
  
  

Q.8 What is Database?

• Database is not MySQL, MongoDB, oracle 
• Database is a collection of tables of the project.
• Database = Tables of the project + associated data.
• database is maintained by database server/DBMS/ RDBMS.
• 
  
  

Q.9 How Many Types of DBMS?

• DBMS are of so many types.

1. Network based Database Management System.
2. Object Oriented based DBMS
3. RDBMS almost used in industry.

What is the JDBC Programming Model?

Advanced Java: 02

Q.1 What is JDBC?

• JDBC is a java-based data access technology from sun microsystem.
• JDBC is not a software, not a programming language it's a technology.
• JDBC is a trademark name given to a technology.
• No framework in the world without JDBC they can communicate with database.
• There is no expansion of JDBC.

Q.2 What is the architecture of JDBC?

• Please remember if you know JDBC then only you understand database.
• Java database communication is to perform CRUD (create, read, update, delete) operation is acts like a client server architecture. JDBC follows client server architecture.
• A software or a program that request for resources is a client.
• A Software that provides requested resources to client is a server.

Q.3 What is a JDBC Client?

• Any java program that uses JDBC Technology to communicate with the database is known as a JDBC client.
• Why it is a client because java program is requesting for connection, requesting for CRUD operation.

Q.4 What is the JDBC Programming Model?

• There are certain steps that you have to follow to understand JDBC Programming model.

Note: First you should be conceptually strong and then you should be programmatically strong.

Q.5 What are the JDBC client-side requirements to communicate with the database?

• The movement we install JDK following things automatically get install along with it.

1. JDBC API
2. JDBC DRIVER

• Without JDBC API (library Methods) and JDBC Driver you cannot communicate with the database.

Q.6 What is JDBC API?

• Please note in interview if they ask what API is? then tell API is Application Programming Interface they are not asking long form of it please note. you have to tell them the correct answer is "A Collection of library methods" as you know in java methods belongs to Classes and Interfaces. Classes and Interfaces belongs to Packages. 
• likewise, to communicate with the database java has java. SQL package which having library classes and library interfaces.
• Now the answer for what is JDBC API? is "A Collection of library methods (that belongs to the library classes and library interfaces) of java SQL package."
• Note: You installed JDK means you install JDBC API, but you need to import the package (java. SQL) in application. we required this to perform CRUD operation with the database.
• JDBC API, is nothing but the tool.

Q.7 What is a JDBC Driver?

• JDBC Driver is a translation software written in java according to JDBC specification.
• JDBC Driver performs the following operation or duties to enable java-database communication.

1. when java program (JDBC Client) makes a method call, JDBC Driver will receive that method call. JDBC Driver translates this method call into Pure SQL statement and send it to DBMS Software. DBMS handover this statement to MySQL for execution.
2. Once the MySQL execute these statements (Query) it generates SQL Type response from the DBMS to JDBC Driver. now once again JDBC Driver translate this SQL Response into java format and handing over to java program (JDBC Client).

Imp Note: Once you understand JDBC, you will enjoy 6 to 7 ways of database connection.

 

Why do we require Advanced Java?

Advanced Java: 1

We are going to learn advanced java in question answer form.

Q1. What is CORE JAVA?

• Core java is used to developed standalone applications.
• Core java means only language learning.
• Core java is required to learn language but not sufficient.
• Core java 25years outdated because now a days no one use standalone application for businesses.

Q2. What is Advanced Java?

• To make the project online that is web application learn advanced java.
• if you want to develop internet-based application learn advanced java.
• if you want to become java professional you must learn advanced java.
• Collection of various tools and technologies through which we can develop dynamic application.
• To communicate with the database located at remote location we need Advanced Java.
• We can develop network centric, web based, enterprise applications using advanced java.

Q3. What is the basis for company to provide services to customer?

• For Ex. Bank --- Provides --- services to customers. Now using what they provide services to customer, so the answer is DATA. Online banking, online shopping any application to provide the services data is more important. (Joke in IT industry: More than wife data is important)

Q4. Which feature should data possess?

• Now question comes to your mind why data requires features? data must require features because if data is not having any feature, then there is no use of such data.
• Data of web application must possess following features:

1. Persistence (Permanence): for ex. bank having data customers account no if after few days bank forgot customer account number, can we think that application or project or software work?
2. Security: your data must be secure.
3. Easy accessibility: This feature is also required for easy access of data.

For example, consider a scenario if i stored data in hard disk, data is permanent, but it is not secure and not easily accessible. so can you think you will get all those things in one place. Yes, if we store data in a database data will possess all features permanence, secure, easy accessibility, instead of writing 500 lines of code to access data from file system we easily get data from database through SQL statements.

Q5. Now if database is there then why java is required?

• To answer this question let's assume, SBI bank all customers details stored in database. SBI is having 1000 branches all over India. suppose customer from Pune wants to know the balance enquiry so to know the balance can customer open SQL prompt and write SQL Statement for the same or can bank person open the SQL prompt and write SQL statement for the same as SELECT balance FROM Account WHERE Accountno=123.
• What do you think can bank person knows the SQL Statement definitely not. secondly how SQL appears in Pune branch because database server is located somewhere in Mumbai branch.
• From this we come's to know that database is weak in data processing and only database is not sufficient.

Q6. What is the Role of Java in Web application?

• Role of java is Backend processing.
• Communicating with the database and perform CRUD operations.
• For example, consider the use case of balance enquiry.
• When user enters his or her Account no: 1234 and click on getBalance button, java program will send select statement to database. similarly for CRUD operation java program will send insert, update and delete statement to the database.
• Data processing is done by java. 
• Making processed data to the frontend.
• java role is backend processing. (How to communicate with database is the first part of advanced java JDBC and Servlet)

Q7. What is the Obstacle for java -database communication?

• Java Program     --------------------------------------      Database
• As we know in core java when we want to do any operation using java it will do it with java method calls. now using java program, we want to retrieve balance from the database for that purpose from java side it will call method for the same. 
• now who will receive that method call answer is database will receive that method call but database does not understand method call. database understand only SQL Statement.
• Now we have to make request in the form of SQL Statement using java, but java compiler does not accept SQL directly.
• So, what we can say that java and database are heterogeneous to each other. 
• This is the main obstacle for java database communication.

but still java wants to communicate with database, so we can simulate this problem with real world entities like PM of India wants to communicate with PM of Russia. both doesn't understand language of each other. So, to overcome this obstacle they use mediator or translator for the same so that they can understand each other's opinion.

Similarly, we use JDBC as a mediator or translator between 

Java (method calls) --------------------    JDBC     -------------------------   DBMS (SQL Statement only)

Q8. How java database communication?

• By using JDBC java database connection is possible.

Next session will discuss.:

1. What is JDBC?

2. What is architecture of JDBC?

3. What is a JDBC client?

4. What is the JDBC Programming Model?

5. What are the JDBC Client side requirements to communicate with the database?

6. What is JDBC API?

7. What is a JDBC Driver?