JDBC Stands for Java Database Connectivity. JDBC AI used to connect java application with Database. JDBC API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases & SQL databases and other tabular data sources, such as spreadsheets or flat files.
Java Database Connectivity (JDBC) is a standard Java API to interact with relational databases form Java. JDBC has set of classes and interfaces which can use from Java application and talk to database without learning RDBMS details and using Database Specific JDBC Drivers. The JDBC library includes APIs for each of the tasks mentioned below that are commonly associated with database usage. Connection to a database Creating SQL or MySQL Database statements Executing SQL or MySQL queries or built in SQL editor in the database Viewing and modifying the resulting records JDBC API has 3 Interfaces and their key features are as follows: Statement: It is used to run simple SQL statements like select and update. Statement interfaces use for general-purpose access to your database. It is useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters. PreparedStatement: A SQL statement is pre-compiled and stored in a PreparedStatement object. It is used to run Pre compiled SQL. This object can then be used to efficiently execute this statement multiple times. The object of Prepared Statement class can be created using Connection.prepareStatement() method. This extends Statement interface. CallableStatement: This interface is used to execute the stored procedures. This extends Prepared Statement interface. The object of Callable Statement class can be created using Connection.prepareCall() method. The JDBC Driver provides vendor-specific implementations of the abstract classes provided by the JDBC API. This driver is used to connect to the database. There are four types of JDBC drivers. JDBC-ODBC Bridge plus ODBC Driver: 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: 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: 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: 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. There are 5 steps to connect any java application with the database in java using JDBC. They are as follows: The java.sql package contains interfaces and classes for JDBC API. Interfaces: Classes: In case of Statement, query is complied each time whereas in case of PreparedStatement, query is complied only once. So performance of PreparedStatement is better than Statement. Some of the benefits of PreparedStatement over Statement are: 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. There are different types of ResultSet objects that we can get based on the user input while creating the Statement. If you will look into the Connection methods, you will see that createStatement() and prepareStatement() method are overloaded to provide ResultSet type and concurrency as input argument. There are three types of ResultSet object. Based on the concurrency there are two types of ResultSet object. 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. Sometimes a transaction can be group of multiple statements and we would like to rollback to a particular point in the transaction. JDBC Savepoint helps us in creating checkpoints in a transaction and we can rollback to that particular checkpoint. Any savepoint created for a transaction is automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question. JDBC DataSource is the interface defined in javax.sql package and it is more powerful that DriverManager for database connections. We can use DataSource to create the database connection and Driver implementation classes does the actual work for getting connection. Apart from getting Database connection, DataSource provides some additional features such as: A RowSet is an object that encapsulates a set of rows from either Java Database Connectivity (JDBC) result sets or tabular data sources like a file or spreadsheet. RowSets support component-based development models like JavaBeans, with a standard set of properties and an event notification mechanism. There are two types of RowSet are there. RowSet objects are derived from ResultSet, so they have all the features of ResultSet with some additional features. One of the huge benefit of RowSet is that they can be disconnected and that makes it lightweight and easy to transfer over a network. Whether to use ResultSet or RowSet depends on your requirements but if you are planning to use ResultSet for longer duration, then a disconnected RowSet is better choice to free database resources. Some of the common JDBC Exceptions are: java.sql.SQLException – This is the base exception class for JDBC exceptions. java.sql.BatchUpdateException – This exception is thrown when Batch operation fails, but it depends on the JDBC driver whether they throw this exception or the base SQLException. java.sql.SQLWarning – For warning messages in SQL operations. java.sql.DataTruncation – when a data values is unexpectedly truncated for reasons other than its having exceeded MaxFieldSize. 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. The values are defined in the class java.sql.Connection and are: The java.sql and javax.sql are the primary packages for JDBC 4.0.What is JDBC?
What are the types of statements in JDBC?
What is JDBC Driver?
What are different types of JDBC Drivers?
What are the steps to connect to the database in java?
What are the JDBC API components?
What is the difference between Statement and PreparedStatement interface?
What are the benefits of PreparedStatement over Statement?
What is the limitation of PreparedStatement and how to overcome it?
What are different types of ResultSet?
How to rollback a JDBC transaction?
What is JDBC Savepoint? How to use it?
What is JDBC DataSource and what are it’s benefits?
What is rowset?
What are the different types of RowSet?
What is the different between ResultSet and RowSet?
What are common JDBC Exceptions?
What is CLOB and BLOB datatypes in JDBC?
What are the different types of locking in JDBC?
What are the standard isolation levels defined by JDBC?
What are the package new features changes in JDBC?
Related posts:
- Core Java Interview Questions and Answers
- Hibernate Interview Questions and Answers 2021
- Java Multithreading Interview Question and Answers
- Java Threading Interview Questions and Answers
- JAX-WS Interview Questions and Answers
- JSF Interview Questions and Answers
- Portal and Portlet Interview Questions and Answers
- Swing Interview Questions and Answers