What is JDBC? Why we use JDBC?
JDBC is a Java API that is used to connect and execute query to the database. JDBC API uses jdbc drivers to connects to the database.
Before JDBC, ODBC API was the database API to connect and execute query with the database. But, ODBC API uses ODBC driver which is written in C language (i.e. platform dependent and unsecured). That is why Java has defined its own API (JDBC API) that uses JDBC drivers (written in Java language).
What is JDBC API and when do we use it?
Java DataBase Connectivity API allows us to work with relational databases. JDBC API interfaces and classes are part of
java.sqland javax.sqlpackage. We can use JDBC API to get the database connection, run SQL queries and stored procedures in the database server and process the results.
JDBC API is written in a way to allow loose coupling between our Java program and actual JDBC drivers that makes our life easier in switching from one database to another database servers easily.
What are different types of JDBC Drivers?
There are four types of JDBC drivers. Any java program that works with database has two parts, first part is the JDBC API and second part is the driver that does the actual work.
- JDBC-ODBC Bridge plus ODBC Driver (Type 1): It uses ODBC driver to connect to database. We should have ODBC drivers installed to connect to database, that’s why this driver is almost obsolete.
- Native API partly Java technology-enabled driver (Type 2): This driver converts JDBC class to the client API for the database servers. We should have database client API installed. Because of extra dependency on database client API drivers, this is also not preferred driver.
- Pure Java Driver for Database Middleware (Type 3): This driver sends the JDBC calls to a middleware server that can connect to different type of databases. We should have a middleware server installed to work with this driver. This adds to extra network calls and slow performance and thats why not widely used JDBC driver.
- Direct-to-Database Pure Java Driver (Type 4): This driver converts the JDBC calls to the network protocol understood by the database server. This solution is simple and suitable for database connectivity over the network. However for this solution, we should use database specific drivers, for example OJDBC jars by Oracle for Oracle DB and MySQL Connector/J for MySQL databases.
What are the steps to connect to the database in java?
- Registering the driver class
- Creating connection
- Creating statement
- Executing queries
- Closing connection
What are the JDBC statements?
There are 3 types of JDBC Statements, as given below:
- Statement: It will execute SQL query (static SQL query) against the database.
- Prepared Statement: Used when we want to execute SQL statement repeatedly. Input data is dynamic and taken input at the run time.
- Callable Statement: Used when we want to execute stored procedures.
How does JDBC API helps us in achieving loose coupling between Java Program and JDBC Drivers API?
JDBC API uses Java Reflection API to achieve loose coupling between java programs and JDBC Drivers. If you look at a simple JDBC example, you will notice that all the programming is done in terms of JDBC API and Driver comes in picture only when it’s loaded through reflection using
I think this is one of the best example of using Reflection in core java classes to make sure that our application doesn’t work directly with Drivers API and that makes it very easy to move from one database to another.
What is the role of JDBC DriverManager class?
DriverManageris the factory class through which we get the Database Connection object. When we load the JDBC Driver class, it registers itself to the DriverManager, you can look up the JDBC Driver classes source code to check this.
Then when we call
DriverManager.getConnection()method by passing the database configuration details, DriverManager uses the registered drivers to get the Connection and return it to the caller program.
What does the JDBC Connection interface?
The Connection interface maintains a session with the database. It can be used for transaction management. It provides factory methods that returns the instance of Statement, PreparedStatement, CallableStatement and DatabaseMetaData.
What does the JDBC ResultSet interface?
The ResultSet object represents a row of a table. It can be used to change the cursor pointer and get the information from the database.
What does the JDBC ResultSetMetaData interface?
The ResultSetMetaData interface returns the information of table such as total number of columns, column name, column type etc.
What does the JDBC DatabaseMetaData interface?
The DatabaseMetaData interface returns the information of the database such as username, driver name, driver version, number of tables, number of views etc.
Which interface is responsible for transaction management in JDBC?
The Connection interface provides methods for transaction management such as commit(), rollback() etc.
How can we store and retrieve images from the database?
By using batch processing technique in JDBC, we can execute multiple queries. It makes the performance fast.
What are types of ResultSet?
There are three types of ResultSet is available. If we do not declare any ResultSet that means we are calling TYPE_FORWARD_ONLY
- TYPE_FORWARD_ONLY: cursor can move only forward.
- TYPE_SCROLL_INSENSITIVE: cursor can move forward and backward but not sensitive.
- TYPE_SCROLL_SENSITIVE: cursor can move forward and backward, but it is sensitive
Can I get a null ResultSet?
No, we cannot get null Resultset. ResultSet.next() can return null if the next record does not contain a row.
What is the difference between executing, executeQuery, executeUpdate in JDBC?
- execute(): it can be used for any kind of SQL Query.
- executeQuery(): it can be used for select query.
- executeUpdate(): it can be used to change/update table.
How many RowSet are available in JDBC?
There are two types of row sets are available:
- Connected – A connected RowSet object connects to the database instantaneously. If the application terminates then connected RowSet object also terminates.
- Disconnected – A disconnected RowSet object connects to the database after execution of the required query.
Explain JDBC Savepoint?
A savepoint represents a point that the current transaction can roll back to. Instead of rolling all its changes back, it can choose to roll back only some of them.
What is JDBC PreparedStatement?
PreparedStatementobject represents a precompiled SQL statement. We can use it’s setter method to set the variables for the query.
Since PreparedStatement is precompiled, it can then be used to efficiently execute this statement multiple times. PreparedStatement is better choice that Statement because it automatically escapes the special characters and avoid SQL injection attacks.
How to set NULL values in JDBC PreparedStatement?
We can use PreparedStatement setNull() method to bind the null variable to a parameter. The setNull method takes index and SQL Types as argument, for example
What is the use of getGeneratedKeys() method in Statement?
Sometimes a table can have auto generated keys used to insert the unique column value for primary key. We can use Statement
getGeneratedKeys()method to get the value of this auto generated key.
What are the benefits of PreparedStatement over Statement?
Some of the benefits of PreparedStatement over Statement are:
- PreparedStatement helps us in preventing SQL injection attacks because it automatically escapes the special characters.
- PreparedStatement allows us to execute dynamic queries with parameter inputs.
- PreparedStatement is faster than Statement. It becomes more visible when we reuse the PreparedStatement or use it’s batch processing methods for executing multiple queries.
- PreparedStatement helps us in writing object Oriented code with setter methods whereas with Statement we have to use String Concatenation to create the query. If there are multiple parameters to set, writing Query using String concatenation looks very ugly and error prone.
What is the limitation of PreparedStatement and how to overcome it?
One of the limitation of PreparedStatement is that we can’t use it directly with IN clause statements. Some of the alternative approaches to use PreparedStatement with IN clause are;
- Execute Single Queries – very slow performance and not recommended
- Using Stored Procedure – Database specific and hence not suitable for multiple database applications.
- Creating PreparedStatement Query dynamically – Good approach but looses the benefit of cached PreparedStatement.
- Using NULL in PreparedStatement Query – A good approach when you know the maximum number of variables inputs, can be extended to allow unlimited parameters by executing in parts.
What is the use of setFetchSize() and setMaxRows() methods in Statement?
We can use
setMaxRows(int i)method to limit the number of rows that the database returns from the query. You can achieve the same thing using SQL query itself. For example in MySQL we can use LIMIT clause to set the max rows that will be returned by the query.
Understanding fetchSize can be tricky, for that you should know how Statement and ResultSet works. When we execute a query in the database, the result is obtained and maintained in the database cache and ResultSet is returned. ResultSet is the cursor that has the reference to the result in the database.
Let’s say we have a query that returns 100 rows and we have set fetchSize to 10, so in every database trip JDBC driver will fetch only 10 rows and hence there will be 10 trips to fetch all the rows. Setting optimal fetchSize is helpful when you need a lot of processing time for each row and number of rows in the result is huge.
We can set fetchSize through Statement object but it can be overridden through ResultSet object setFetchSize() method.
How to rollback a JDBC transaction?
We can use Connection object
rollback()method to rollback the transaction. It will rollback all the changes made by the transaction and release any database locks currently held by this Connection object.
What is CLOB and BLOB datatypes in JDBC?
Character Large OBjects (CLOBs) are character string made up of single-byte characters with an associated code page. This data type is appropriate for storing text-oriented information where the amount of information can grow beyond the limits of a regular VARCHAR data type (upper limit of 32K bytes).
Binary Large OBjects (BLOBs) are binary string made up of bytes with no associated code page. This data type can store binary data larger than VARBINARY (32K limit). This data type is good for storing image, voice, graphical, and other types of business or application- specific data.
What do you understand by DDL and DML statements?
Data Definition Language (DDL) statements are used to define the database schema. Create, Alter, Drop, Truncate, Rename statements comes under DDL statements and usually they don’t return any result.
Data Manipulation Language (DML) statements are used to manipulate data in the database schema. Select, Insert, Update, Delete, Call etc are example of DML statements.
What is difference between java.util.Date and java.sql.Date?
java.util.Datecontains information about the date and time whereas java.sql.Datecontains information only about the date, it doesn’t have time information. So if you have to keep time information in the database, it is advisable to use Timestamp or DateTime fields.
How to insert an image or raw data into database?
We can use BLOB to insert image or raw binary data into database.