What are the different types of JDBC ResultSet?
When creating a Statement, you can specify the different types of ResultSet that you receive.
Three types of ResultSet objects:
ResultSet.TYPE_FORWARD_ONLY: the default type. Supports cursor movement only in the forward direction.
ResultSet.TYPE_SCROLL_INSENSITIVE: Bi-directional cursor. The object is not sensitive to the changes that occurred with the table after the result was obtained.
ResultSet.TYPE_SCROLL_SENSITIVE: Bi-directional cursor. The object is sensitive to changes that occurred with the database after the ResultSet object was created.
Two types of thread-safe objects are ResultSet:
ResultSet.CONCUR_READ_ONLY: Supports read only. It is used by default.
ResultSet.CONCUR_UPDATABLE: Supports the ResultSet update method to update rows in the data table.
How are the setFetchSize () and SetMaxRows () methods used in Statement?
To limit the number of rows that a query can return, the setMaxRows (int i) method is used. Of course, this result can be obtained using a SQL query (for example, for MySQL, the LIMIT command exists).
To understand the setFetchSize () method, you need to understand the work of Statement and ResultSet. When a database query is executed, the result is processed and stored in the database cache and returned as a ResultSet. ResultSet is the cursor that references the result in the database. Now suppose we have a query that returns 100 rows and we set setFetchSize (10). Now for every database access, only 10 rows are allocated and 10 requests will be needed to get all the data. Choosing the optimal amount of fetchSize () can improve the performance of executing a large number of calls to each row and in the case of a large number of rows in the output result.
The value of fetchSize can be specified inside the Statement object, but it can be overridden in the ResultSet object with setFetchSize ().
What is JDBC Batch Processing and what are its advantages?
Sometimes it is necessary to perform a group of similar requests at once, for example, when loading data from CSV files of a relational database. You can do this simply by using Statement or PreparedStatement to step through these queries. In the JDBC API, there is another option that provides the ability to execute a query group at a time. This type of task is performed using the JDBC API Batch Processing.
The JDBC API supports batch processing using the addBatch () and executeBatch () methods of Statement and PreparedStatement. The advantages of this approach include faster work, because calls to the database can be significantly less.
What is JDBC Transaction Management and why is it needed?
By default, when you create a connection to the database, auto-commit mode is selected. This means that each time the query is executed, it will be confirmed automatically when completed. Each SQL query is transactional and executing any DML or DDL requests to complete the change will be accepted (saved) by the database. If we need to refuse to save the execution of any query (or query groups) in case something went wrong, then we can use transaction support in the JDBC API.
Using the setAutoCommit (boolean flag) method, you can disable auto commit in a specific connection. It should be noted that if you disable auto-commit, not one change will be stored in the database until the commit () method is called and you should monitor it. The database server will block the necessary part of the database before the transaction is confirmed, and since this is a resource-intensive task, the transaction must be confirmed immediately after the task is completed.
What is JDBC Savepoint and how is it used?
JDBC Savepoint allows you to create “checkpoints” in a transaction with which we can roll back not the whole transaction, but only a part to the save point. Any savepoint is automatically released and becomes unavailable after the transaction is confirmed or its rollback. A rollback to the save point makes all subsequent saves unavailable and can no longer be returned.
Tell us about the JDBC DataSource. What advantages does it give?
JDBC DataSource is the interface of the javax.sql package and is more advanced than the DriverManager to connect to the database. We can use DataSource to create a connection to the database and implement a driver class that will perform all the work to maintain the connection. In addition to connecting through the Database, DataSource provides the following additional features:
- Caching PreparedStatement for faster query processing
- Connection timeout settings
- Logging capabilities
- Maximum ResultSet Threshold
- Support for Connection Pooling in a servlet container that uses JNDI support.