JDBC stands for Java Data Base Connectivity, which is a standard Java API for database-independent connectivity between the Java Programming Language and a wide range of Data Bases. JDBC is a Java API to connect and execute the query with the database. JDBC is a part of JavaSE (Java Standard Edition). JDBC API uses JDBC drivers to connect with the database. This is Service Technology. Any Java program connecting any data base in the standard. Any kind of Java Programme in order to communicate with any kind of data base in a standard manner, JDBC Technology is used. Java is mostly used in business applications. Business data (Enterprise Data] is stored in a data base. To offer business services to the customers, Java Applications should contact the data bases. Java Programs can make only method calls. These method calls are not understandable to the data bases. Data Bases (DBMS) understand only SQL. This is nothing but Hetrogeneous Environment.
JDBC also provides support for handling database metadata that allows us to retrieve information about the database, such as its tables, columns, and indexes. We can use the DatabaseMetaData interface to obtain this information that can be useful for dynamically generating SQL queries or for database schema introspection. Another important feature of JDBC is its support for batch processing that allows us to group multiple SQL statements into a batch and execute them together. It can improve performance by reducing the number of round trips between the application and the database.
Fundamentally, JDBC is a specification that provides a complete set of interfaces that allows for portable access to an underlying database. Java can be used to write different types of executables.
1. Java Application
2. Java Applets
3. Java Servlets
4. Java Server Pages (JSP)
5. Enterprise Java Beans (EJB)
6. Struts Framework
7. Spring Framework
JDBC Driver is a software component that enables java application to interact with the database. There are 4 types of JDBC drivers:
1. JDBC-ODBC Bridge Driver
2. Native-API Driver
3. Network Protocol Driver
4. Thin Driver
The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls. This is now discouraged because of thin driver. Oracle does not support the JDBC-ODBC Bridge from Java 8. Oracle recommends that you use JDBC drivers provided by the vendor of your database instead of the JDBC-ODBC Bridge.
The Native API driver uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API. It is not written entirely in java.
The Network Protocol driver uses middleware (application server) that converts JDBC calls directly or indirectly into the vendor-specific database protocol. It is fully written in java.
The thin driver converts JDBC calls directly into the vendor-specific database protocol. That is why it is known as thin driver. It is fully written in Java language.
There are 5 steps to connect any java application with the database using JDBC. These steps are as follows:
1. Register the Driver class
2. Create Connection
3. Create Statement
4. Execute Queries
5. Close Connection
To connect java application with the oracle database, we need to follow 4 following steps.
1. Driver class: The driver class for the oracle database is oracle.jdbc.driver.OracleDriver.
2. Connection URL: The connection URL for the oracle10G database is jdbc:oracle:thin:@localhost:1521:xe where jdbc is the API.
3. Username: The default username for the oracle database is system.
4. Password: It is the password given by the user at the time of installing the oracle database.
5. Close Connection
To connect java application with the oracle database, we need to follow 4 following steps.
1. Driver class: The driver class for the oracle database is com.mysql.jdbc.Driver.
2. Connection URL: The connection URL for the oracle10G database is jdbc:mysql://localhost:3306/sonoo where jdbc is the API.
3. Username: The default username for the oracle database is system.
4. Password: It is the password given by the user at the time of installing the oracle database.
5. Close Connection
The DriverManager class is the component of JDBC API and also a member of the java.sql package. The DriverManager class acts as an interface between users and drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. It contains all the appropriate methods to register and deregister the database driver class and to create a connection between a Java application and the database. The DriverManager class maintains a list of Driver classes that have registered themselves by calling the method DriverManager.registerDriver(). Note that before interacting with a Database, it is a mandatory process to register the driver; otherwise, an exception is thrown.
A Connection is a session between a Java application and a database. It helps to establish a connection with the database. The Connection interface is a factory of Statement, PreparedStatement, and DatabaseMetaData, i.e., an object of Connection can be used to get the object of Statement and DatabaseMetaData.
The Statement interface provides methods to execute queries with the database. The statement interface is a factory of ResultSet i.e. it provides factory method to get the object of ResultSet.
The object of ResultSet maintains a cursor pointing to a row of a table. Initially, cursor points to before the first row.
The PreparedStatement interface is a subinterface of Statement. It is used to execute parameterized query.
The metadata means data about data i.e. we can get further information from the data. If you have to get metadata of a table like total number of column, column name, column type etc. , ResultSetMetaData interface is useful because it provides methods to get metadata from the ResultSet object.
DatabaseMetaData interface provides methods to get meta data of a database such as database product name, database product version, driver name, name of total number of tables, name of total number of views.
CallableStatement interface is used to call the stored procedures and functions. We can have business logic on the database by the use of stored procedures and functions that will make the performance better because these are precompiled. Suppose you need the get the age of the employee based on the date of birth, you may create a function that receives date as the input and returns age of the employee as the output.
An instance of RowSet is the Java bean component because it has properties and Java bean notification mechanism. It is the wrapper of ResultSet. A JDBC RowSet facilitates a mechanism to keep the data in tabular form. It happens to make the data more flexible as well as easier as compared to a ResultSet. The connection between the data source and the RowSet object is maintained throughout its life cycle. The RowSet supports development models that are component-based such as JavaBeans, with the standard set of properties and the mechanism of event notification. It was in the JDBC 2.0, the support for the RowSet was introduced using the optional packages. But the implementations were standardized for RowSet in the JDBC RowSet Implementations Specification by the Sun Microsystems that is being present in the JDK (Java Development Kit) 5.0.
Definition : Connection pooling in JDBC is a technique used to manage and reuse database connections, significantly improving the performance and scalability of applications that frequently interact with a database. Connection pooling in Java is a technique used to improve the performance of database-driven applications. Instead of creating a new connection every time the application needs to interact with the database, a pool of connections is created and managed by a connection pool manager. This eliminates the overhead of establishing a new connection each time, resulting in faster response times and better resource utilization.
| How it works Connection Pooling | |
|---|---|
| Pool Creation | A connection pool is initialized with a set number of database connections when the application starts or when the pool is first accessed. These connections are maintained in a pool or cache. |
| Connection Request | When an application needs to perform a database operation, instead of creating a new physical connection using DriverManager.getConnection(). It requests a connection from the pool. |
| Connection Loan | The connection pool provides an available connection to the application. If no connections are available, the request might wait or a new connection might be created, depending on the pool's configuration. |
| Connection Usage | The application uses the loaned connection to execute SQL queries and perform database operations. |
| Connection Return | Once the application finishes its database operations, it "closes" the connection. However, instead of physically closing the connection, the connection is returned to the pool, making it available for reuse by other parts of the application or other threads. |
| Benefits of Connection Pooling | |
| Reduced Overhead | Creating and closing database connections are resource-intensive operations. Connection pooling eliminates this overhead for subsequent requests by reusing existing connections. |
| Improved Performance | Applications can access database connections more quickly, leading to faster response times and improved overall performance. |
| Increased Scalability | By efficiently managing a limited number of connections, connection pooling allows more concurrent users or threads to access the database without overwhelming it with connection requests. |
| Resource Management | Connection pools provide a centralized mechanism to manage and monitor database connections, ensuring efficient resource utilization and preventing resource leaks. |
| Controlled Concurrency | Connection pools can be configured to limit the maximum number of active connections, preventing the database from being overloaded. |
| Benefits of Connection Pooling | |
| Several popular libraries and application servers offer JDBC connection pooling capabilities, such as HikariCP, Apache Commons DBCP, C3P0, and built-in pooling in Web and Application Servers like Apache Tomcat, JBoss, Glassfish, WebSphere and Oracle WebLogic. | |
| Interface | Description |
|---|---|
| Array | The mapping in the Java programming language for the SQL type ARRAY. |
| Blob | The representation (mapping) in the Java™ programming language of an SQL BLOB value. |
| CallableStatement | The interface used to execute SQL stored procedures. |
| Clob | The mapping in the Java™ programming language for the SQL CLOB type. |
| Connection | A connection (session) with a specific database. |
| DatabaseMetaData | Comprehensive information about the database as a whole. |
| Driver | The interface that every driver class must implement. |
| DriverAction | An interface that must be implemented when a Driver wants to be notified by DriverManager. |
| NClob | The mapping in the Java™ programming language for the SQL NCLOB type. |
| ParameterMetaData | An object that can be used to get information about the types and properties for each parameter marker in a PreparedStatement object. |
| PreparedStatement | An object that represents a precompiled SQL statement. |
| Ref | The mapping in the Java programming language of an SQL REF value, which is a reference to an SQL structured type value in the database. |
| ResultSet | A table of data representing a database result set, which is usually generated by executing a statement that queries the database. |
| ResultSetMetaData | An object that can be used to get information about the types and properties of the columns in a ResultSet object. |
| RowId | The representation (mapping) in the Java programming language of an SQL ROWID value. |
| Savepoint | The representation of a savepoint, which is a point within the current transaction that can be referenced from the Connection.rollback method. |
| SQLData | The interface used for the custom mapping of an SQL user-defined type (UDT) to a class in the Java programming language. |
| SQLInput | An input stream that contains a stream of values representing an instance of an SQL structured type or an SQL distinct type. |
| SQLOutput | The output stream for writing the attributes of a user-defined type back to the database. |
| SQLType | An object that is used to identify a generic SQL type, called a JDBC type or a vendor specific data type. |
| SQLXML | The mapping in the JavaTM programming language for the SQL XML type. |
| Statement | The object used for executing a static SQL statement and returning the results it produces. |
| Struct | The standard mapping in the Java programming language for an SQL structured type. |
| Wrapper | Interface for JDBC classes which provide the ability to retrieve the delegate instance when the instance in question is in fact a proxy class. |
| Classes | Description |
| Date | A thin wrapper around a millisecond value that allows JDBC to identify this as an SQL DATE value. |
| DriverManager | The basic service for managing a set of JDBC drivers. NOTE: The DataSource interface, new in the JDBC 2.0 API, provides another way to connect to a data source. |
| DriverPropertyInfo | Driver properties for making a connection. |
| SQLPermission | The permission for which the SecurityManager will check when code that is running an application with a SecurityManager enabled, calls the DriverManager.deregisterDriver method, DriverManager.setLogWriter method, DriverManager.setLogStream (deprecated) method, SyncFactory.setJNDIContext method, SyncFactory.setLogger method, Connection.setNetworktimeout method, or the Connection.abort method. |
| Time | A thin wrapper around the java.util.Date class that allows the JDBC API to identify this as an SQL TIME value. |
| Timestamp | A thin wrapper around java.util.Date that allows the JDBC API to identify this as an SQL TIMESTAMP value. |
| Types | The class that defines the constants that are used to identify generic SQL types, called JDBC types. |
| Enum | Description |
| ClientInfoStatus | Enumeration for status of the reason that a property could not be set via a call to Connection.setClientInfo |
| JDBCType | Defines the constants that are used to identify generic SQL types, called JDBC types. |
| PseudoColumnUsage | Enumeration for pseudo/hidden column usage. |
| RowIdLifetime | Enumeration for RowId life-time values. |
| Exception | Description |
| BatchUpdateException | The subclass of SQLException thrown when an error occurs during a batch update operation. |
| DataTruncation | An exception thrown as a DataTruncation exception (on writes) or reported as a DataTruncation warning (on reads) when a data values is unexpectedly truncated for reasons other than its having exceeded MaxFieldSize. |
| SQLClientInfoException | The subclass of SQLException is thrown when one or more client info properties could not be set on a Connection. |
| SQLDataException | The subclass of SQLException thrown when the SQLState class value is '22', or under vendor-specified conditions. |
| SQLException | An exception that provides information on a database access error or other errors. |
| SQLFeatureNotSupportedException | The subclass of SQLException thrown when the SQLState class value is '0A' ( the value is 'zero' A). |
| SQLIntegrityConstraintViolationException | The subclass of SQLException thrown when the SQLState class value is '23', or under vendor-specified conditions. |
| SQLInvalidAuthorizationSpecException | The subclass of SQLException thrown when the SQLState class value is '28', or under vendor-specified conditions. |
| SQLNonTransientConnectionException | The subclass of SQLException thrown for the SQLState class value '08', or under vendor-specified conditions. |
| SQLNonTransientException | The subclass of SQLException thrown when an instance where a retry of the same operation would fail unless the cause of the SQLException is corrected. |
| SQLRecoverableException | The subclass of SQLException thrown in situations where a previously failed operation might be able to succeed if the application performs some recovery steps and retries the entire transaction or in the case of a distributed transaction, the transaction branch. |
| SQLSyntaxErrorException | The subclass of SQLException thrown when the SQLState class value is '42', or under vendor-specified conditions. |
| SQLTimeoutException | The subclass of SQLException thrown when the timeout specified by Statement.setQueryTimeout, DriverManager.setLoginTimeout, DataSource.setLoginTimeout,XADataSource.setLoginTimeout has expired. |
| SQLTransactionRollbackException | The subclass of SQLException thrown when the SQLState class value is '40', or under vendor-specified conditions. |
| SQLTransientConnectionException | The subclass of SQLException for the SQLState class value '08', or under vendor-specified conditions. |
| SQLTransientException | The subclass of SQLException is thrown in situations where a previously failed operation might be able to succeed when the operation is retried without any intervention by application-level functionality. |
| SQLWarning | An exception that provides information on database access warnings. |