Understanding JDBC a Little Deeper(Advanced)

This article is a continuation of my previous post, "Understanding Basics of JDBC", where I explained the foundational concepts of Java Database Connectivity (JDBC). If you’re new to JDBC or need a refresher on its core components and how to get started, I highly recommend checking that out first.

Life Cycle of an SQL Query Execution

Before diving deep into JDBC’s advanced capabilities, it's important to understand how an SQL query actually gets processed behind the scenes. When a Java application sends an SQL query to a database via JDBC, the database engine doesn’t just execute it directly—it goes through several systematic stages to ensure correctness and optimization.

SQL Query from Java Application:The JDBC layer sends the query string to the Database Engine.

Compilation Phase: The database engine initiates the compilation of the query, which involves three major steps:

• Tokenization: The query string is broken into individual tokens (keywords, identifiers, operators, etc.).
• Parsing: These tokens are used to construct a Query Tree, which checks for syntax correctness and structure.
• Query Optimization: The parsed query tree is optimized into the most efficient version—considering indexes, joins, and execution paths.

Execution Phase: The optimized query is then sent for execution. Based on the query type:

• For SELECT queries, a ResultSet is returned.
• For INSERT, UPDATE, or DELETE, a row count is returned indicating affected rows.

Result to Java Application:Finally, the result (ResultSet or row count) is sent back to the Java application where it can be processed further.

Problem with Statement and the Need of PreparedStatement

When working with JDBC, many beginners start by using the normal Statement interface to execute SQL queries. While this works, it is not efficient or secure in many real-time applications.

In the case of normal Statement, whenever we are executing SQL Query, every time compilation and execution will be happened at database side.

Statement st = con.createStatement();

ResultSet rs = st.executeQuery ("select * from employees");

From the above img its clear that time taken for 1 query is 4ms and the time taken for 1000 queries is 4*1000 = 4000ms , this clearly says that how the performance is going down to overcome this, we prefer Prepared Statements.

We can create PreparedStatement by using prepareStatement() method of Connection interface. The main advantage of PreparedStatement is the query will be compiled only once even though we are executing multiple times, so that overall performance of the application will be improved.

PreparedStatment pst=con.prepareStatement(sqlQuery);

At this line, sqlQuery will be sent to the database. Database engine will compile that query and stores in the database. That pre compiled query will be returned to the java application in the form of PreparedStatement object. Hence PreparedStatement represents "pre compiled sql query". Whenever we call execute methods,database engine won't compile query once again and it will directly execute that query,so that overall performance will be improved.

From the above img its clear that time taken for 1 query is 3ms since in Prepared Statement the query will be compiled only once and the time taken for 1000 queries is 3*1000 = 3000ms , this clearly says that when compared to normal Statement, the preparedStatement is having the high performance rate.

Steps to develop JDBC Application by using PreparedStatement

Step1: Prepare SQLQuery either with parameters or without parameters.

Eg: insert into employees values(100,'durga',1000,'hyd');

From the above img we can observe that, in place of values we have given '?' we can call these as Positional Parameters or Place Holders or IN Parameter. These '?' will be replaced with the acutal values in the further steps.

Step2: Create PreparedStatement object with our sql query

PreparedStatement pst = con.prepareStatement(sqlQuery);

At this line only the query will get compiled.

Step3 : If the query is parameterized query then we have to set input values to these parameters by using corresponding setter methods. We have to consider these positional parameters from left to right and these are 1 index based. i.e index of first positional parameter is 1 but not zero.

Note: Before executing the query, for every positional parameter we have to provide input values otherwise we will get SQLException.

Step4: Execute SQL Query

PreparedStatement is the child interface of Statement and hence all methods of Statement interface are bydefault available to the PreparedStatement.Hence we can use same methods to execute sql query.

Note: We can execute same parameterized query multiple times with different sets of input values. In this case query will be compiled only once and we can execute multiple times.

These are the steps to develop a JDBC application using PreparedStatement...

Static Query vs Dynamic Query:

The sql query without positional parameter(?) is called static query.

eg: delete from employees where ename='durga';

The sql query with positional parameter(?) is called dynamic query.

eg: select * from employees where esal>? ;

Advantages of PreparedStatement:

1. Performance will be improved when compared with simple Statement b'z query will be compiled only once.
2. Network traffic will be reduced between java application and database b'z we are not required to send query every time to the database.
3. We are not required to provide input values at the beginning and we can provide dynamically so that we can execute same query multiple times with different sets of values.
4. It allows to provide input values in java style and we are not required to convert into database specific format.
5. Best suitable to insert Date values
6. Best Sutitable to insert Large Objects (CLOB,BLOB)
7. It prevents SQL Injection Attack.

Limitation of PreparedStatement:

We can use PreparedStatement for only one sql query , but we can use simple Statement to work with any number of queries.

Note: Simple Statement can be used only for static queries where as PreparedStatement can used for both static and dynamic queries.

Differences between Statement And PreparedStatement

Code:

1. APP26_INSERT_RECORDS_USING_PREPARED_STATEMENT
2. APP27_DELETE_RECORD_USING_PREPARED_STATEMENT

SQL Injection Attack

In the case of using a simple Statement, the SQL query is built by concatenating user input directly into the query string. This approach can be dangerous because malicious users can inject special characters or SQL code as input, which may alter the behavior of the query. This is known as a SQL Injection Attack, one of the most common and dangerous security vulnerabilities in applications.

The Problem with Statement

Every time a Statement is used:

• The full query (with user input included) is sent to the database.
• The database compiles and executes the query.
• Malicious input can change the logic of the query.

let us consider an example

Here we took two variables and concatenated them in the query to become a query. when we enter username = satya and password =java. finally query will be like...

This is valid query and behaves as expected, i.e, if the given credientials are correct the result will be fetched otherwise it will not.

But if the attacker inputs username = satya'-- and password = fghjskuyh

the resultant query will be like below

here the query will be considered till username='satya' and the rest of that will be treated as comment since in sql we use '--' as single line comment.

• Here, -- makes the rest of the SQL query a comment.
• The password check is ignored, and the attacker logs in with just the username.
• This changes the intended behavior of the query—a classic SQL Injection Attack.

How PreparedStatement Prevents SQL Injection

PreparedStatement separates SQL logic from user input, treating user data as parameters (not part of the SQL code). This means that even if a user provides special characters or malicious code, it will not change the SQL query structure.

Now even if the hacker inputs username = satya'-- and password = fghjskuyh

the result query will be like

The database will not compile it as part of the SQL logic, but will treat it as a string literal, resulting in a safe and secure execution.

NOTE: Never concatenate user inputs directly into SQL queries. Always use PreparedStatement or ORM frameworks that handle it for you.

Code:

1. APP28_TO_SHOW_SQL_INJECTION_ATTACT_WITH_STATEMENT_OBJ
2. APP29_TO_SHOW_NO_POSSIBILITY_OF_SQL_INJECTION_WITH_PREPARED_STATEMENT

Stored Procedures and CallableStatement

In regular Java programming, when a block of code is needed multiple times, we extract it into a method and call it whenever required. This promotes code reusability.

Similarly, in database programming, if a group of SQL statements is frequently used together to perform a specific task, we can define them in a Stored Procedure and invoke it whenever needed. This is a powerful way to encapsulate logic at the database level.

A Stored Procedure is a group of SQL statements that perform a particular task and are stored permanently in the database for future use.

• It improves reusability and performance.
• Usually created and managed by the Database Administrator (DBA).
• Stored Procedures can take parameters, just like methods.

Stored Procedures support three types of parameters, similar to function arguments:

1. IN – Used to pass input values to the procedure.
2. OUT – Used to retrieve output values from the procedure.
3. INOUT – Used to both pass input and receive output values.

This procedure takes two numbers (a, b) as input and returns their sum as output in result.

Example for creating Stored Procedure that selects all the records from the table

If we want to call stored procedure from java application, then we should go for CallableStatement. CallableStatement is an interface present in java.sql package and it is the child interface of PreparedStatement.

We can create CallableStatement object by using following method of Connection interface.

Eg: CallableStatement cst=con.prepareCall("{call addProc(?,?,?)}");

Whenever JVM encounters this line, JVM will send call to database.Database engine will check whether the specified procedure is already available or not. If it is available then it returns CallableStatement object representing that procedure.

Process to call Stored Procedure from java application by using CallableStatement:

Step1: Make sure Stored procedure available in the database. For Example.

Step2: Create a CallableStatement with the procedure call.

CallableStatement cst = con.prepareCall("{call addProc(?,?,?)}");

Step3: Provide values for every IN parameter by using corresponding setter methods.

Step4: Register every OUT parameter with JDBC Types.

Eg: cs.registerOutParameter(3, java.sql.Types.INTEGER);

Step5: execute procedure call

Eg: cst.execute();

Step6: Get the result from OUT parameter by using the corresponding getXxx() method.

Eg: int result=cst.getInt(3);

These are the steps to call a stored Procedure from java application by using CallableStatement.

Advantages of Using Stored Procedures

• Encapsulation of complex logic at DB level
• Improved performance (compiled once and reused)
• Reduced network traffic (send fewer queries)
• Centralized business logic
• Enhanced security and access control

Code:

1. APP30_TO_CALL_STORED_PROCEDURE_FROM_JAVA_APPLICATION_THROUGH_CALLABLE_STATEMENT
2. APP31_EXAMPLE1_FOR_CALLABLE_STATEMENTS
3. APP32_EXAMPLE2_FOR_CALLABLE_STATEMENTS

Functions in JDBC

Functions in SQL are very similar to Stored Procedures, but with a key difference: A function always returns a value directly, while a procedure typically returns output using OUT parameters.

Both are stored permanently in the database and are reusable. However, their usage depends on the requirement:

• Use a procedure to perform business logic or database operations.
• Use a function when you need to perform calculations or return a single value.

Example Function:

To call the function from Java application

• Use functions when the goal is to compute and return a single value.
• Avoid placing business logic in functions — that’s better suited for procedures.
• Always register the return type as the first parameter in the CallableStatement.

Code:

1. APP35_CODE_TO_UNDERSTAND_FUNCTIONS

Statement vs PreparedStatement vs CallableStatement:

We can use normal Statement to execute multiple queries.

• st.executeQuery(query1)
• st.executeQuery(query2)
• st.executeUpdate(query2)

if we want to work with multiple queries then we should go for Statement object.

If we want to work with only one query,but should be executed multiple times then we should go for PreparedStatement.

If we want to work with stored procedures and functions then we should go for CallableStatement.

Batch Updates

When a Java application needs to execute multiple similar queries, such as inserting 1000 records or updating several rows, sending each SQL statement individually to the database is inefficient.

This is where Batch Updates come into play.

From Img2 and Img3 we can clearly see that Incase of Normal Statement the time taken for 1000 queries is 4000ms. And Incase of Prepared Statement the time taken for 1000 queries is 3000ms.

Here It increases network traffic between Java application and database and even creates performance problems also. To overcome these problems, we should go for Batch updates. We can group all related SQL Queries into a single batch and we can send that batch at a time to the database.

From the Img22, we can observe that the java application will send the multiple queries i.e, a batch of queries at a time, this will reduced the request time and response time hence the performance will be improved.

We can implement batch updates by using the following two methods

• public void addBatch(String sqlQuery) : To add query to batch
• int[] executeBatch() : To execute a batch of sql queries

We can implement batch updates either by simple Statement or by PreparedStatement. But Always use PreparedStatement for batch updates involving dynamic input to gain performance and security benefits.

Example..

The advantages of Batch updates are

• We can reduce network traffic
• We can improve performance.

The Disadvantages of Batch Updates are

• We can use Batch Updates concept only for non-select queries. If we are trying to use for select queries then we will get RE saying BatchUpdateException.
• In batch if one sql query execution fails then remaining sql queries wont be executed.

Code:

1. APP36_EXAMPLE1_BATCH_UPDATES_USING_SIMPLE_STATEMENTS
2. APP37_EXAMPLE2_BACHT_UPDATE_USING_PREPARED_STATEMENT

In JDBC How Many Execute Methods Are Avaialble?

In total there are 4 methods are available

1. executeQuery() ➔ For select queries
2. executeUpdate() ➔ For non-select queries(insert|delete|update)
3. execute() ➔ For both select and non-select queries And also Stored Procedures
4. executeBatch()➔ For Batch Updates

Handling Date Values For Database Operations

Sometimes as the part of programing requirement,we have to insert and retrieve Dates like DOB,DOJ,DOM,DOP...with respect to database. It is not recommended to maintain date values in the form of String,b'z comparisons will become difficult. In Java we have two Date classes:

• java.util.Date
• java.sql.Date

java.sql.Date is the child class of java.util.Date.

java.sql.Date is specially designed class for handling Date values with respect to database. Otherthan database operations,if we want to represent Date in our java program then we should go for java.util.Date. Moreover java.util.Date can represent both Date and Time where as java.sql.Date represents only Date but not time.

Note: In sql package Time class is availble to represent Time values and TimeStamp class is available to represent both Date and Time.

Code: APP38_TO_UNDERSTAND_THE_DATE_HANDLING

Inserting Date Values into Database:

Various databases follow various styles to represent Date. If we use simple Statement object to insert Date values then we should provide Date value in the database supported format,which is difficult to the programmer. If we use PreparedStatement,then we are not required to worry about database supported form, just we have to call: pst.setDate (2, java.sql.Date);

This method internally converts date value into the database supported format. Hence it is highly recommendedto use PreparedStatement to insert Date values into database.

Steps to Insert Date values into database:

Step1: Create a table in the database with the date value

Step2: Read Date from the end user(in String form)

Step3: Convert date from String form to java.util.Date form by using SimpleDateFormat object

Step4: convert date from java.util.Date to java.sql.Date

Step5: set sdate to query

These are the steps to insert the date values into the database...

Code:

1. APP39_INSERT_DATES_INTO_DATABASE_EXAMPLE1
2. APP40_INSERT_INTO_DATABASE_EXAMPLE2

Retrieving Date values from the database:

For this we can use either simple Statement or PreparedStatement. The retrieved Date values are Stored in ResultSet in the form of "java.sql.Date" and we can get this value by using getDate() method. Once we got java.sql.Date object,we can format into our required form by using SimpleDateFormat object.

Code:

1. APP41_RETRIEVING_DATE_VALUES_FROM_DATABASE_EXAMPLE1
2. APP42_RETRIEVING_DATE_VALUES_FROM_DATABASE_EXAMPLE2

Working with Large Objects

Sometimes as the part of programming requirement,we have to insert and retrieve large files like images,video files,audio files,resume etc with respect to database

To store and retrieve large information we should go for Large Objects(LOBs). There are 2 types of Large Objects:

• Binary Large Object (BLOB)
• Character Large Object (CLOB)

Binary Large Object (BLOB)

A BLOB is a collection of binary data stored as a single entity in the database. BLOB type objects can be images,video files,audio files etc.. BLOB datatype can store maximum of "4GB" binary data.

Steps to insert BLOB type into database:

Step1: create a table in the database which can accept BLOB type data.

Step2: Represent image file in the form of Java File object.

Step3: Create FileInputStream to read binary data represented by image file

Step4: Create PreparedStatement with insert query.

Step5: Set values to positional parameters.

Step6: execute sql query

These are the steps to insert the BLOB type into Database...

Code: APP43_TO_INSERT_BLOB_TYPE_INTO_DATABASE

Now lets see how to retrieve the BLOB type from the database

Steps to Retrieve BLOB type from database:

Step1: Prepare ResultSet object with BLOB type

Step2: Read Normal data from ResultSet

Step3: Get InputStream to read binary data from ResultSet

Step4: Prepare target resource to hold BLOB data by using FileOutputStream

Step5: Read Binary Data from InputStream and write that Binary data to output Stream.

These are the steps to retrieve the BLOB type from the database...

Code: APP44_TO_RETRIEVE_BLOB_TYPE_FROM_DATABASE

Now lets see how to insert the CLOB type into the database

CLOB (Character Large Objects)

A CLOB is a collection of Character data stored as a single entity in the database. CLOB can be used to store large text documents(may plain text or xml documents) CLOB Type can store maximum of 4GB data.

Steps to insert CLOB type file in the database:

All steps are exactly same as BLOB, except the following differences

Instead of FileInputStream, we have to take FileReader. And Instead of setBinaryStream() method we have to use setCharacterStream() method.

Example code

Code: APP45_TO_INSERT_CLOB_TYPE_INTO_DATABASE

Retrieving CLOB Type from Database:

All steps are exactly same as BLOB, except the following differences..

1. Instead of using FileOutputStream,we have to use FileWriter
2. Instead of using getBinaryStream() method we have to use getCharacterStream() method

Example code:

Code: APP46_TO_RETRIEVE_CLOB_TYPE_FROM_DATABASE

Note: Use BLOB when storing binary files, and CLOB for large text-based content.

These are the Large Objects...

Connection Pooling

When a Java application needs to communicate with the database frequently or at scale, repeatedly creating and closing database connections becomes a performance bottleneck. Establishing a new Connection object every time is time-consuming and resource-heavy.

Disadvantages of Creating and destroying a Connection object:

• Takes time (for authentication, network latency...)
• Consumes CPU and memory
• Reduces application performance under high load

A Connection Pool is a pre-initialized set of connection objects stored in memory. These connections are ready to be used and reused multiple times without needing to recreate them.

If we want to communicate with database then we request Connection pool to provide Connection. Once we got the Connection, by using that we can communicates with database. After completing our work, we can return Connection to the pool instead of destroying. Hence the main advantage of Connection Pool is we can reuse same Connection object multiple times, so that overall performance of application will be improved.

In the case of DriverManager.getConnection(), always a new Connection object will be created and returned. But in the case of DataSourceObject.getConnection(), a new Connection object won't be created and existing Connection object will be returned from Connection Pool.

Process to implement Connection Pooling:

Step1: Creation of DataSource object

DataSource is responsible to manage connections in Connection Pool. DataSource is an interface present in javax.sql package.

Step2: Set required JDBC Properties to the DataSource object

Step3: Get Connection from DataSource object

Once we got Connection object then remaining process is as usual.

Code: APP47_TO_UNDERSTAND_CONNECTION_POOLING

Note: This way of implementing Connection Pool is useful for Standalone applications. In the case of web and enterprise applications, we have to use server level connection pooling. Every web and application server can provide support for Connection Pooling.

Properties

In Java Program if anything which changes frequently(like jdbc url, username, pwd etc) is not recommended to hard code in our program. The problem in this approach is if there is any change in java program,to reflect that change we have to recompile,rebuild and redeploy total application and even some times server restart also required,which creates a big business impact to the client.

To overcome this problem, we should go for Properties file. The variable things we have to configure in Properties file and we have to read these properties from java program.

The main advantage of this approach is if there is any change in Properties file and to reflect that change just redeployment is enough, which won't create any business impact to the client.

To execute the Properties in Java application we need to create a seperate file called db.properties

After that in the java program , create a Properties object and FileInputSteam object to read the db.properties file and then load the FileInputStream object through Properties Object . Then get the information from the db.properties file

This way of coding is highly secured since the sensitive data like username, password and url are shown no where in the code.

There are 3 getConnection() methods are available in DriverManager class.

• Connection con=DM.getConnection(url,user,pwd);

It is the regular method we often use

• Connection con=DM.getConnection(url,Properties);

here we will create a db1.properties file containing the username and password . After loading the properties file. In connection object we will pass the jdbc url and properties object.

• Connection con=DM.getConnection(url);

here only the url is given as parameter, if we observe carefully the url itself contains the username and the password merged using '?' and '&'.

Code:

1. APP48_TO_UNDERSTAND_PROPERTIES_CONCEPT
2. APP49_TO_UNDERSTAND_USE_OF_PROPERTIES_CONCEPT_PROGRAM1
3. APP50_TO_UNDERSTAND_USE_OF_PROPERTIES_CONCEPT_PROGRAM2

This the overview about the Properties Concept...

Transaction Management in JDBC

Process of combining all related operations into a single unit and executing on the rule "either all or none", is called transaction management.

Hence transaction is a single unit of work and it will work on the rule "either all or none".

Transaction Properties:

Every Transaction should follow the following four ACID properties.

1. A ➔ Atomiticity : Either all operations should be done or None.
2. C ➔ Consistency(Reliabile Data) : It ensures bringing database from one consistent state to another consistent state.
3. I ➔ isolation (Sepatation) : Ensures that transaction is isolated from other transactions
4. D ➔ Durability : It means once transaction committed, then the results are permanent even in the case of system restarts, errors etc.

Types of Transactions:

There are two types of Transactions

1. Local Transactions
2. Global Transactions

Local Transactions:

All operations in a transaction are executed over same database.

Eg: Funds transfer from one accoun to another account where both accounts in the same bank.

Global Transactions:

All operations is a transaction are expected over different databases.

Eg: Funds Transfer from one account to another account and accounts are related to different banks.

Note: JDBC can provide support only for local transactions. If we want global transactions then we have to go for EJB or Spring framework.

Process of Transaction Management in JDBC:

• Disable auto commit mode of JDBC : By default auto commit mode is enabled. i.e after executing every sql query, the changes will be committed automatically in the database. We can disable auto commit mode as follows

• If all operations completed then we can commit the transaction by using the following method.

• . If any sql query fails then we have to rollback operations which are already completed by using rollback() method.

These steps are used in Transaction management of JDBC...

Code:

1. APP51_EXAMPLE_CODE_FOR_TRANSACTIONS
2. APP52_TO_UNDERSTAND_SAVEPOINT_CONCEPT

Transaction Concurrency Problems:

Whenever multiple transactions are executing concurrently then there may be a chance of transaction concurrency problems.

The following are the most commonly occurred concurrency problems.

1. Dirty Read Problem
2. Non Repeatable Read Problem
3. Phantom Read Problem

Dirty Read Problem:

Also known as uncommitted dependency problem. Before committing the transaction, if its intermediate results used by any other transaction then there may be a chance of Data inconsistency problems. This is called Dirty Read Problem.

At the end, T1 point of view, durga has 50000 balance and T2 point of view durga has 1Lakh. There may be a chance of data inconsistency problem. This is called Dirty Read Problem.

Non-Repeatable Read Problem:

For the same Read Operation, in the same transaction if we get different results at different times, then such type of problem is called Non-Repeatable Read Problem.

In the above example Transaction-1 got different results at different times for the same query.

Phantom Read Problem:

A phantom read occurs when one transaction reads all the rows that satisfy a where condition and second transaction insert a new row that satisfy same where condition. If the first transaction reads for the same condition in the result an additional row will come. This row is called phantom row and this problem is called phantom read problem.

In the above code whenever transaction-1 performing read operation second time, a new row will come in the result

To overcome these problems we should go for Transaction isolation levels.

Connection interface defines the following 4 transaction isolation levels.

1. TRANSACTION_READ_UNCOMMITTED ➔ 1
2. TRANSACTION_READ_COMMITTED ➔ 2
3. TRANSACTION_REPEATABLE_READ ➔ 4
4. TRANSACTION_SERIALIZABLE ➔ 8

TRANSACTION_READ_UNCOMMITTED:

It is the lowest level of isolation. Before committing the transaction its intermediate results can be used by other transactions. Internally it won't use any locks. It does not prevent Dirty Read Problem, Non-Repeatable Read Problem and Phantom Read Problem. We can use this isolation level just to indicate database supports transactions. This isolation level is not recommended to use.

TRANSACTION_READ_COMMITTED:

This isolation level ensures that only committed data can be read by other transactions. It prevents Dirty Read Problem. But there may be a chance of Non Repeatable Read Problem and Phantom Read Problem.

TRANSACTION_REPEATABLE_READ:

This is the default value for most of the databases. Internally the result of SQL Query will be locked for only one transaction. If we perform multiple read operations, then there is a guarantee that for same result. It prevents Dirty Read Problem and Non Repeatable Read Problems. But still there may be a chance of Phantom Read Problem.

TRANSACTION_SERIALIZABLE:

It is the highest level of isolation. The total table will be locked for one transaction at a time. It prevents Dirty Read, Non-Repeatable Read and Phantom Read Problems. Not Recommended to use because it may creates performance problems. Connection interface defines the following method to know isolation level: getTransactionIsolation()

Note: For MySql database, the default isolation level is: 4(TRANSACTION_REPEATABLE_READ). MySql database can provide support for all isolation levels (1, 2, 4 and 8).

Summary Table of Isolation Levels

Code:

1. APP53_TO_DEMONSTRATE_MYSQL_DATABASE_ISOLATION_LEVEL

MetaData

Metadata means data about data. I.e. Metadata provides extra information about our original data.

Eg1: Metadata about database is nothing but database product name, database version etc..

Eg2: Metadata about ResultSet means no of columns, each column name, column type etc..

JDBC provides support for 3 Types of Metadata

1. DatabaseMetaData
2. ResultSetMetaData
3. ParameterMetaData

DatabaseMetaData

It is an interface present in java.sql package. Driver Software vendor is responsible to provide implementation. We can use DatabaseMetaData to get information about our database like database product name, driver name, version, number of tables etc.. We can also use DatabaseMetaData to check whether a particular feature is supported by DB or not like stored procedures, full joins etc.. We can get DatabaseMetaData object by using getMetaData() method of Connection interface.

ResultSetMetaData

It is an interface present in java.sql package. Driver software vendor is responsible to provide implementation. It provides information about database table represented by ResultSet object. Useful to get number of columns, column types etc.. We can get ResultSetMetaData object by using getMetaData() method of ResultSet interface.

ParameterMetaData

It is an interface and present in java.sql package. Driver Software vendor is responsible to provide implementation. In General we can use positional parameters(?) while creating PreparedStatement object.

We can use ParameterMetaData to get information about positional parameters like parameter count,parameter mode, and parameter type etc... We can get ParameterMetaData object by using getParameterMetaData() method of PreparedStatement interface.

Code:

1. APP54_TO_DEMONSTRATE_DATABASEMETADATA
2. APP55_TO_DISPLAY_TABLE_NAMES_PRESENT_IN_DATABASE_USING_DATABASEMETADATA
3. APP56_TO_DISPLAY_COLUMN_META_INFO_USING_RESULTSETMETADATA
4. APP57_TO_DISPLAY_TABLE_DATA_INCLUDING_COLUMN_NAMES_USING_RESULTSETMEDTADATA
5. APP58_TO_DISPLAY_PARAMETER_META_INFO_USING_PARAMETERMETADATA

ResultSet Types

There are 2 Divisions in ResultSet Types.

Division-1:

Based on operations performed on ResultSet, we can divide ResultSet into 2 types

1. Read Only ResultSets (Static ResultSets)
2. Updatable ResultSets (Dynamic ResultSets)

Read Only ResultSets:

We can perform only read operations on the ResultSet by using corresponding getter methods and we cannot perform any updations. By default ResultSet is Read Only. We can specify explicitly ResultSet as Read only by using the following constant of ResultSet.

public static final int CONCUR_READ_ONLY ➔ 1007

Updatable ResultSets:

The ResultSet which allows programmer to perform updations, such type of ResultSets are called Updatable ResultSets. In this case we can perform select, insert, delete and update operations. We can specify ResultSet explicitly as Updatable by using the following constant of ResultSet.

public static final int CONCUR_UPDATABLE ➔ 1008

Division-2:

Based on Cursor movement, ResultSets will be divided into the following 2 types.

1. Forward only (Non-Scrollable) ResultSet
2. Scrollable ResultSets

Forward Only ResultSets

It allows the programmers to iterate records only in forward direction ie from top to bottom sequentially. By default every ResultSet is forward only. We can specify explicitly ResultSet as Forward only by using the following constant of ResultSet

public static final int TYPE_FORWARD_ONLY ➔ 1003

Scrollable ResultSets

It allows the programmers to iterate in both forward and backward directions. We can also jump to a particular position randomly, or relative to current position. Here we can move to anywhere.

There are two types of Scrollable ResultSets.

• Scroll Insensitive ResultSet
• Scroll Sensitive ResultSet

Scroll Insensitive ResultSet:

After getting ResultSet if we are performing any change in Database and if those changes are not reflecting to the ResultSet, such type of ResultSets are called scroll insensitive ResultSets. i.e ResultSet is insensitive to database operations. We can specify explicitly ResultSet as Scroll insensitive by using the following constant

public static final int TYPE_SCROLL_INSENSITIVE ➔ 1004

Scroll sensitive ResultSets:

After getting the ResultSet if we perform any change in the database and if those changes are visible to ResultSet, such type of ResultSet is called Scroll sensitive ResultSet. i.e ResultSet is sensitive to database operations We can specify explicitly ResultSet as scroll sensitive by using the following constant..

public static final int TYPE_SCROLL_SENSITIVE ➔ 1005

Differnences Between Scroll Insensitive And Scroll Sensitive ResultSets

Differnences between Forward only and Scrollable ResultSets

RowSets

It is alternative to ResultSet. We can use RowSet to handle a group of records in more effective way than ResultSet. RowSet interface present in javax.sql package RowSet is child interface of ResultSet. RowSet implementations will be provided by Java vendor and database vendor. By default RowSet is scrollable and updatable. By default RowSet is serializable and hence we can send RowSet object across the network. But ResultSet object is not serializable. ResultSet is connected i.e to use ResultSet compulsary database Connection must be required. RowSet is disconnected. ie to use RowSet database connection is not required.

Types of RowSets

There are two types of RowSets

1. Connected RowSets
2. Disconnected RowSets

Connected RowSets:

Connected RowSets are just like ResultSets. To access RowSet data compulsary connection should be available to database. We cannot serialize Connected RowSets

Eg: JdbcRowSet

Disconnected RowSets:

Without having Connection to the database we can access RowSet data. We can serialize Disconnected RowSets.

Eg: CachedRowSet, WebRowSet, FilteredRowSet, JoinRowSet

How to create RowSet objects:

We can create different types of RowSet objects as follows

JdbcRowSet:

It is exactly same as ResultSet except that it is scrollable and updatable. JdbcRowSet is connected and hence to access JdbcRowSet compulsary Connection must be required. JdbcRowSet is non serializable and hence we cannot send RowSet object across the network.

CachedRowSet:

It is the child interface of RowSet. It is bydefault scrollable and updatable. It is disconnected RowSet. ie we can use RowSet without having database connection. It is Serializable. The main advantage of CachedRowSet is we can send this RowSet object for multiple people across the network and all those people can access RowSet data without having DB Connection. If we perform any update operations(like insert,delete and update) to the CachedRowSet,to reflect those changes compulsary Connection should be established. Once Connection established then only those changes will be reflected in Database.

WebRowSet:

It is the child interface of CachedRowSet. It is bydefault scrollable and updatable. It is disconnected and serializable WebRowSet can publish data to xml files,which are very helpful for enterprise applications.

JoinRowSet:

It is the child interface of WebRowSet. It is by default scrollable and updatable It is disconnected and serializable If we want to join rows from different rowsets into a single rowset based on matched column(common column) then we should go for JoinRowSet. We can add RowSets to the JoinRowSet by using addRowSet() method.

FilteredRowSet:

It is the child interface of WebRowSet. If we want to filter rows based on some condition then we should go for FilteredRowSet. We can define the filter by implementing Predicate interface.

Differences Between ResultSet and RowSet

Code:

1. APP59_CREATION_OF_ROWSET_PROGRAM1
2. APP60_TO_RETRIEVE_RECORDS_FROM_JDBCROWSET
3. APP61_INSERT_RECORDS_INTO_TABLE_USING_JDBCROWSET
4. APP62_TO_UPDATE_RECORDS_USING_JDBCROWSET
5. APP63_TO_DELETE_RECORDS_USING_JDBCROWSET
6. APP64_TO_RETRIEVE_RECORDS_FROM_CACHED_ROWSET
7. APP65_TO_INSERT_RECORDS_USING_CACHE_ROWSET
8. APP66_TO_UPDATE_USING_CACHE_ROWSET
9. APP67_TO_DELETE_RECORDS_USING_CACHED_ROWSET
10. APP68_TO_RETRIEVE_RECORDS_USING_WEB_ROWSET
11. APP69_TO_INSERT_RECORDS_USING_WEB_ROWSET
12. APP70_TO_DELETE_RECORDS_USING_WEB_ROWSET
13. APP71_TO_RETRIEVE_RECORDS_USING_JOIN_ROWSET
14. APP72_TO_RETRIEVE_RECORDS_USING_FILTERED_ROWSET

I have pushed all the codes into the github : Click Here

This is the overview of Understanding JDBC a Little Deeper(Advanced).

Thank you.