JDBC
What is JDBC?
It stands for Java database connectivity. It is an application programming interface (API) which defines how user is going to access the database. When you want to connect database with your java code no matter whether it’s one of any framework(jsp, server, swing) or a simple java code, you can use facilities from JDBC or ODBC.
For example, if you are trying to implement login module in swing or jsp server:
1. Fetch values from text box.
2. Make a connection to database.
3. Fire an query.
4. Fetch result in Java code.
5. Use that result to authenticate user whether he or she is logging in with valid credentials or not.
The JDBC library includes APIs for each of the tasks mentioned below that are commonly associated with database usage.
· Making a connection to a database.
· Creating SQL or MySQL statements.
· Executing SQL or MySQL queries in the database.
· Viewing & Modifying the resulting records.
It is part of java standard edition from oracle corporation. This is oriented towards relational database like MySQL. We can update the database using operations like create, insert update and delete these statements return an update count that indicates how many rows were affected in the database.
When a Java application needs a database connection, one of the DriverManager.getConnection() methods is used to create a JDBC connection. The URL used is dependent upon the particular database and JDBC driver. If database operation fails, JDBC rises an SQLException.
JDBC Architecture:
generally speaking, Its architecture has two layers listed below:
- JDBC API: This layer supports the connection to application-to-JDBC Manager. It makes use of the driver manager as well as database-specific drivers so as to give transparent connectivity to databases that are heterogeneous.
- JDBC Driver Manager: It is interface between user and drivers. It keeps track of drivers that are available and handles establishing a connection between a database and the appropriate drivers.
- JDBC Driver API: This layer provides the connection of JDBC Manager to Driver. This driver manager makes sure that the correct driver is being used in accessing each of the data sources. It is also capable of supporting many concurrent drivers that are connected to various heterogeneous databases.
JDBC Drivers:
JDBC drivers are client-side adapters (installed on the client machine, not on the server) that convert requests from Java programs to a protocol that the DBMS can understand.
Commercial and free drivers provide connectivity to most relational-database servers. These drivers fall into one of the following types:
· Type 1:JDBC-ODBC bridge drivers that calls native code of the locally available ODBC driver. The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. it converts JDBC method calls into the ODBC function calls. This is now discouraged because of thin driver.
Advantages:
- easy to use.
- can be easily connected to any database.
Disadvantages:
- Performance degraded because JDBC method call is converted into the ODBC function calls.
- The ODBC driver needs to be installed on the client machine.
- Type 2: Native API drivers. 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.
Advantages:
- performance upgraded than JDBC-ODBC bridge driver.
Disadvantages:
- The Native driver needs to be installed on the each client machine.
- The Vendor client library needs to be installed on client machine.
- Type 3: Network protocol drivers. 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.
Advantages:
- No client side library is required because of application server that can perform many tasks like auditing, load balancing, logging etc.
Disadvantages:
- Network support is required on client machine.
- Requires database-specific coding to be done in the middle tier.
- Maintenance of Network Protocol driver becomes costly because it requires database-specific coding to be done in the middle tier.
- Type 4: Thin drivers. 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.
Advantages:
- Better performance than all other drivers.
- No software is required at client side or server side.
Disadvantages:
- Drivers depend on the Database.
Where it is used?
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, such as −
· Java Applications
· Java Applets
· Java Servlets
· Java ServerPages (JSPs)
· Enterprise JavaBeans (EJBs).
All of these different executables are able to use a JDBC driver to access a database, and take advantage of the stored data.
JDBC provides the same capabilities as ODBC, allowing Java programs to contain database-independent code.
Advantages
Before JDBC came into existence, we used the ODBC API database in order to connect as well as to execute queries along with the database. However, ODBC API makes use of ODBC drive in C language. Also, it is platform-dependent and in addition, unsecured. This is the reason that Java defined its own API known as JDBC API using JDBC drivers and also it is written in Java language.
- It is capable of reading any database. The only requirement for it to do so is the proper installation of all the drivers.
- Businesses can continue to use their installed databases and access information even if it is stored on different database management systems.
- The combination of the Java API and the JDBC API makes application development easy and cost effective.
- The JDBC API includes a way to identify and connect to a data source, using a DataSource object. This makes code even more portable and easier to maintain.
Conclusion:
The Java Database Connectivity (JDBC) API provides universal data access from the Java programming language. Using the JDBC API, you can access virtually any data source, from relational databases to spreadsheets and flat files. JDBC is a very important concept which every Java developer needs to understand to develop projects. We hope this blog helps our reader to understand various concepts related to JDBC.
Some FAQs:
What is JDBC and ODBC?
ODBC is an SQL-based Application Programming Interface (API) created by Microsoft that is used by Windows software applications to access databases via SQL. JDBC is an SQL-based API created by Sun Microsystems to enable Java applications to use SQL for database access.
How does JDBC work?
The JDBC Driver is a set of classes that implement the JDBC interfaces to process JDBC calls and return result sets to a Java application. An application uses the connection object to create statements. Statement, PreparedStatement, and CallableStatement objects are used for executing SQL statements.
What is application of JDBC?
JDBC on the client. Java client applications can make JDBC calls to a database server. The connection takes place through a JDBC driver.
JDBC in the database. Java classes installed into a database can make JDBC calls to access and modify data in the database using an internal JDBC driver.
Which JDBC driver is the fastest driver?
Type 4 driver or Native-protocol, pure Java driver, is the fastest driver.
What are the JDBC API components?
There are four types of components
1. JDBC API
2. JDBC Driver Manager
3. JDBC Test Suite
4. JDBC-ODBC Bridge
What are the JDBC statements?
There are 3 types of JDBC Statements, as given below:
1. Statement: It will execute SQL query (static SQL query) against the database.
2. Prepared Statement: Used when we want to execute SQL statement repeatedly. Input data is dynamic and taken input at the run time.
3. Callable Statement: Used when we want to execute stored procedures.
What is ResultSet?
The java.sql.ResultSet interface means the result set of a SQL query. It means a cursor is pointing a row of a table; it points to before the first row.
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
1. TYPE_FORWARD_ONLY: cursor can move only forward.
2. TYPE_SCROLL_INSENSITIVE: cursor can move forward and backward but not sensitive.
3. TYPE_SCROLL_SENSITIVE: cursor can move forward and backward, but it is sensitive
Explain the difference between RowSet vs. ResultSet in JDBC?
In a ResultSet handle connection to a DB, we cannot make Result as a serialized object.
Because of above issue, we cannot pass Resultset across the network.
RowSet extends the ResultSet interface, so it holds all methods from ResultSet. RowSet is serialized.
So, we can pass Rowset from one class to another class because it has no connection with the database.
Why would you use setAutoCommit(false) in JDBC?
If you want to turn Off the Auto Commit then set connection.setAutoCommit(false)
What are database warnings in JDBC and how can we handle database warnings in JDBC?
SQL warning or Database warning is the subclass of SQLException class. We can handle it by using getWarnings() method on Connection, Statement, and ResultSet
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 do you mean by Metadata and why we are using it?
Metadata means data or information about other data. We use metadata to get database product version, driver name, the total number of tables and views.
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.
References:
Thank You!
Regards…
Abhijit Patil
Amitesh Patil
Kshitij Patil
Nikita Patil
Pranit Patil