Java JDBC Tutorial - Java Database Connectivity
Java JDBC Framework
Fundamentals of Java JDBC
Java JDBC Tutorial
Java Database Connectivity
Java JDBC is stands for Java Database Connectivity. JDBC is the trademarked name and is not an acronym. The Java JDBC is an API that can access any kind of tabular data, especially data stored in a Relational Database.
JDBC API (Application Programming Interface) uses JDBC drivers to connect with the database. with the JDBC API, you can access to Oracle database, MySQL database, Microsoft SQL Server database, IBM DB2 database, Sybase database, PostgreSQL database, MS Access database and so on.
What is Java JDBC ?
The Java JDBC is a Java API to connect and execute the query with the database. JDBC API uses JDBC drivers to connect with the database.
What is Java Database Connectivity ?
Java Database Connectivity is nothing but a Java JDBC.
What are the Advantages of JDBC ?
- Connect to a database.
- Send queries and update statements to the database.
- Retrieve and process the results received from the database by using the query.
- Update records in a table.
- Delete records from table.
- Create database.
- Create and alter tables in database.
- Create and alter views in a database.
- Create indexes on tables.
All the above points are the uses of Java JDBC.
- Two Tier Architecture
- Three Tier Architecture
The Java JDBC API supports both two-tier and three-tier processing models for database access.
Java JDBC Library
Java JDBC Drivers
What is Java JDBC Drivers ?
Java JDBC driver is a software component that enables java application to interact with the database.
Which Java Package is used for Java JDBC Operations ?
The java.sql package is used for Java JDBC operations. The javax.sql package is also used for advanced JDBC operations. The Java JDBC API is divided into two packages java.sql and javax.sql. Both packages are included in the Java SE and Java EE platforms.
How Many Types of JDBC Drivers Available in Java ?
There are four types of JDBC drivers available in Java. They are as follows.
- Java JDBC Type-1 Driver ( Java JDBC-ODBC Bridge Drivers )
- Java JDBC Type-2 Driver ( Java-Native API Drivers )
- Java JDBC Type-3 Driver (Java Network Protocol Drivers )
- Java JDBC Type-4 Driver (Java Native Protocol Drivers also called Thin Drivers )
Java JDBC Type-1 Driver
What is Java JDBC Type-1 Driver ?
Java JDBC type 1 driver also known as Java JDBC ODBC Bridge 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 drivers are not recommended because these are outdated and removed from Java 8.
JDBC Type 1 drivers that implement the JDBC API as a mapping to another data access API, such as ODBC (Open Database Connectivity). Drivers of this type are generally dependent on a native library, which limits their portability. The JDBC-ODBC Bridge is an example of a Type 1 driver.
Java 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 driver for Java jdbc oracle connection.
Note : The JDBC-ODBC Bridge should be considered a transitional solution. It is not supported by Oracle. Consider using this only if your DBMS does not offer a Java-only JDBC driver.
What are the Advantages of Java JDBC Type-1 Driver ?
Following are the advantages of JDBC type 1 driver.
- Java JDBC Type 1 drivers are easy to use.
- They connect to any database.
What are the Disadvantages of Java JDBC Type-1 Driver ?
Following are the disadvantages of JDBC type 1 driver.
- The ODBC driver needs to be installed on the client machine.
- Performance degraded because JDBC method call is converted into the ODBC function calls.
How to use Java JDBC Type-1 Driver ?
JDBC Type 1 Driver class name is sun.jdbc.odbc.JdbcOdbcDriver
JDBC Type 1 Driver URL is dbc:odbc:your_data_source_name
your_data_source_name is an ODBC datasource name which is used by ODBC driver to locate one of the ODBC Service Provider implementation API which can in-turn connect to your database.
What are the Steps to Create Data Source Name ( DSN ) ?
Following are the steps to create data source name ( DSN ) in windows 7.
- run Data Sources (ODBC) from Control Panal\AdministrativeTools\
Click on Add button available on the above displayed screen. this opens a new window titled Create New Data Source which displays all the available databases ODBC drivers currently installed on your system.
Note : If your system is x64 bit then you run ODBC drivers from C:\Windows\SysWOW64\odbc32.exe
- Select the suitable driver and click on Finish
- Give the required info to the driver (like username, service id etc)
Java JDBC Type-2 Driver
What is Java JDBC Type-2 Driver ?
Java JDBC type 2 drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited. Oracle's OCI (Oracle Call Interface) client-side driver is an example of a Type 2 driver.
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.
This type of drivers are suitable to be used in server side applications. Not recommended to use with the applications using two tire model.
What are the Advantages of Java JDBC Type-2 Driver ?
Following are the advantages of JDBC type 2 driver.
- Performance is better than JDBC-ODBC bridge driver.
What are the Disadvantages of Java JDBC Type-2 Driver ?
Following are the disadvantages of JDBC type 2 driver.
- The Native driver needs to be installed on each client machine.
- The Vendor specific client library needs to be installed on client machine.
How to use Java JDBC Type-2 Driver ?
OCI 8(Oracle Call Interface) for Oracle implemented by Oracle Corporation.
Driver class name:oracle.jdbc.driver.OracleDriver
Driver URL: jdbc:oracle:oci8:@TNSName
Note: TNS Names of Oracle is available in Oracle installed folder %ORACLE_HOME%\Ora81\network\admin\tnsnames.ora
Software: Oracle client software has to be installed in client machine
Classpath : %ORACLE_HOME%\ora81\jdbc\lib\classes111.zip
Path :% ORACLE_HOME%\ora81\bin
Java JDBC Type-3 Driver
What is Java JDBC Type-3 Driver ?
Java JDBC type 3 drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client's requests to the data source.
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.
What are the Advantages of Java JDBC Type-3 Driver ?
Following are the advantages of JDBC type 3 driver.
- No client side library is required because of application server that can perform many tasks like auditing, load balancing and logging.
- Performance is better than JDBC-ODBC bridge driver and Java JDBC Type 2 Driver ( Java-Native API Drivers ).
What are the Disadvantages of Java JDBC Type-3 Driver ?
Following are the disadvantages of JDBC type 3 driver.
- Network required on client machine.
- Requires database-specific coding in the middle tier.
- Maintenance of Network Protocol driver becomes costly because it requires database specific coding to be done in the middle tier.
How to use Java JDBC Type-3 Driver ?
Driver class name : ids.sql.IDSDriver
Driver URL : jdbc:ids://localhost:12/conn?dsn=IDSExamples
Note: DSN Name must be created in ServerDSN
IDS Server (Intersolv) driver available for most of the Databases.
Setting environment to use Java JDBC Type 3 driver
Software: IDS software required to be downloaded from the following URL http://www.idssoftware.com/idsserver.html
Classpath : C:\IDSServer\classes\jdk14drv.jar
Java JDBC Type-4 Driver
What is Java JDBC Type-4 Driver ?
Java JDBC Type 4 Drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source. The Java JDBC Type 4 Driver are also called Thin Drivers. The thin driver converts JDBC calls directly into the vendor specific database protocol.
Check which driver types comes with your DBMS. Java DB comes with two Type 4 drivers, an Embedded driver and a Network Client Driver. Java jdbc MySQL driver Connector/J is a Type 4 driver.
Installing a JDBC driver generally consists of copying the driver to your computer, then adding the location of it to your class path. In addition, many JDBC drivers other than Type 4 drivers require you to install a client-side API. No other special configuration is usually needed.
This type of drivers are suitable to be used with server side applications, client side application.
What are the Advantages of Java JDBC Type-4 Driver ?
Following are the advantages of JDBC type 4 driver.
- No client native libraries required to be installed in client machine.
- Comes with most of the Databases.
- Better performance than type 1, type 2 and type 3 drivers.
What are the Disadvantages of Java JDBC Type-4 Driver ?
Following are the disadvantages of JDBC type 4 driver.
- Slower in execution compared with other JDBC Driver due to Java libraries are used in socket communication with the database.
- Drivers depend on the Database.
How to use Java JDBC Type-4 Driver ?
Following are the use of Java JDBC Type 4 Driver with different databases.
How to Connect to Oracle Database in Java using Java JDBC Type 4 Driver ?
Following steps are creating a Java jdbc Oracle database connection.
Driver class name : oracle.jdbc.driver.OracleDriver
Driver URL : jdbc:oracle:thin:@HostName:1521:ORCL
Classpath : %ORACLE_HOME%\ora81\jdbc\lib\classes111.zip
Note: To know the SID and port number use tnsnames.ora file from %ORACLE_HOME%/product\10.1.0\db_3\network\admin\tnsnames.ora
How to Connect to MySQL Database in Java using Java JDBC Type 4 Driver ?
Following steps are creating a Java jdbc mysql connection.
Driver class name : com.mysql.jdbc.Driver
Driver URL : jdbc:mysql://localhost:3306/your_mysql_database_name
classpath : C:\mysql\mysql-connector-java-3.0.8-stable\mysql-connector-java-3.0.8-stable-bin.jar
Related Topic Java Byte Array to MySQL : How to store byte array in MySQL using Java ?
How to Connect to MS SQL Server Database in Java using Java JDBC Type 4 Driver ?
Following steps are creating a Java jdbc ms sql server database connection.
Driver class name : com.microsoft.sqlserver.jdbc.SQLServerDriver
Driver URL : jdbc:sqlserver://localhost:1433;databaseName=your_ms_sql_server_database;user=sa;password=your_db_password
classpath : sqljdbc4.jar
Related Topic Java Byte Array to SQL Server : How to store byte array in SQL Server using Java ?
Java JDBC API Versions
What are the Java JDBC API Versions ?
- Java JDBC 4.3 API
- Java JDBC 4.2 API
- Java JDBC 4.1 API
- Java JDBC 4.0 API
- Java JDBC 3.0 API
- Java JDBC 2.0 API
- Java JDBC 1.0 API
What is the Current Version of Java JDBC API ?
The JDBC 4.3 API is the present version of Java JDBC API.
What the JDBC 4.3 API Includes ?
The Java JDBC 4.3 API having both the java.sql package, referred to as the JDBC core API, and the javax.sql package, referred to as the JDBC Optional Package API. This Java JDBC API is included in the Java Standard Edition (Java SE). The javax.sql package extends the functionality of the JDBC API from a client-side API to a server-side API, and it is an essential part of the Java Enterprise Edition (Java EE) technology.
What are the Features Introduced in the Java JDBC 4.3 API ?
The Java JDBC 4.3 API is new in Java 1.9 and part of the Java SE platform, version 9
- Added Sharding support
- Enhanced Connection to be able to provide hints to the driver that a request, an independent unit of work, is beginning or ending
- Enhanced DatabaseMetaData to determine if Sharding is supported
- Added the method drivers to DriverManager to return a Stream of the currently loaded and available JDBC drivers
- Added support to Statement for enquoting literals and simple identifiers
- Clarified the Java SE version that methods were deprecated
What are the Features Introduced in the Java JDBC 4.2 API ?
The Java JDBC 4.2 API is new in Java 1.8 and part of the Java SE platform, version 8
- Added JDBCType enum and SQLType interface
- Support for REF CURSORS in CallableStatement
- DatabaseMetaData methods to return maximum Logical LOB size and if Ref Cursors are supported
- Added support for large update counts
What are the Features Introduced in the Java JDBC 4.1 API ?
The Java JDBC 4.1 API is new in Java 1.7 and part of the Java SE platform, version 7
- Allow Connection, ResultSet and Statement objects to be used with the try-with-resources statement
- Support added to CallableStatement and ResultSet to specify the Java type to convert to via the getObject method
- DatabaseMetaData methods to return PseudoColumns and if a generated key is always returned
- Added support to Connection to specify a database schema, abort and timeout a physical connection.
- Added support to close a Statement object when its dependent objects have been closed
- Support for obtaining the parent logger for a Driver, DataSource, ConnectionPoolDataSource and XADataSource
What are the Features Introduced in the Java JDBC 4.0 API ?
The Java JDBC 4.0 API is new in Java 1.6 and part of the Java SE platform, version 6
- auto java.sql.Driver discovery - no longer need to load a java.sql.Driver class via Class.forName
- National Character Set support added
- Support added for the SQL:2003 XML data type
- SQLException enhancements - Added support for cause chaining; New SQLExceptions added for common SQLState class value codes
- Enhanced Blob/Clob functionality - Support provided to create and free a Blob/Clob instance as well as additional methods added to improve accessibility
- Support added for accessing a SQL ROWID
- Support added to allow a JDBC application to access an instance of a JDBC resource that has been wrapped by a vendor, usually in an application server or connection pooling environment.
- Availability to be notified when a PreparedStatement that is associated with a PooledConnection has been closed or the driver determines is invalid
What are the Features Introduced in the Java JDBC 3.0 API ?
The Java JDBC 3.0 API is new in Java 1.4 and part of the Java SE platform, version 1.4
- Pooled statements - reuse of statements associated with a pooled connection
- Savepoints - allow a transaction to be rolled back to a designated savepoint
- Properties defined for ConnectionPoolDataSource - specify how connections are to be pooled
- Metadata for parameters of a PreparedStatement object
- Ability to retrieve values from automatically generated columns
- Ability to have multiple ResultSet objects returned from CallableStatement objects open at the same time
- Ability to identify parameters to CallableStatement objects by name as well as by index
- ResultSet holdability - ability to specify whether cursors should be held open or closed at the end of a transaction
- Ability to retrieve and update the SQL structured type instance that a Ref object references
- Ability to programmatically update BLOB, CLOB, ARRAY, and REF values.
- Addition of the java.sql.Types.DATALINK data type - allows JDBC drivers access to objects stored outside a data source
- Addition of metadata for retrieving SQL type hierarchies
What are the Features Introduced in the Java JDBC 2.0 API and 2.1 ?
The Java JDBC 2.0 API is new in Java 1.2 and part of the Java SE platform, version 1.2
- Scrollable result sets - using new methods in the ResultSet interface that allow the cursor to be moved to a particular row or to a position relative to its current position
- Batch updates
- Programmatic updates - using ResultSet updater methods
- New data types - interfaces mapping the SQL3 data types
- Custom mapping of user-defined types (UDTs)
- Miscellaneous features, including performance hints, the use of character streams, full precision for java.math.BigDecimal values, additional security, and support for time zones in date, time, and timestamp values.
- The DataSource interface as a means of making a connection. The Java Naming and Directory Interface (JNDI) is used for registering a DataSource object with a naming service and also for retrieving it.
- Pooled connections - allowing connections to be used and reused
- Distributed transactions - allowing a transaction to span diverse DBMS servers
- RowSet technology - providing a convenient means of handling and passing data
What are the steps to use Java Database Connectivity ( JDBC ) ?
Following are the steps for connecting to a database with JDBC are as follows :
- First register the jdbc drivers.
- Second create Java jdbc connection to database.
- Third create statement.
- Fourth execute query to database.
- Fifth and finally close the Java jdbc resultset, statement and database connection.