Java JDBC Tutorial - Java Database Connectivity

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.

1. Java JDBC Type 1 drivers are easy to use.
2. They connect to any database.

What are the Disadvantages of Java JDBC Type-1 Driver ?

Following are the disadvantages of JDBC type 1 driver.

1. The ODBC driver needs to be installed on the client machine.
2. 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.

1. run Data Sources (ODBC) from Control Panal\AdministrativeTools\

2. 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

3. Select the suitable driver and click on Finish
4. Give the required info to the driver (like username, service id etc)

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.

1. 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.

1. The Native driver needs to be installed on each client machine.
2. 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

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.

1. No client side library is required because of application server that can perform many tasks like auditing, load balancing and logging.
2. 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.

1. Network required on client machine.
2. Requires database-specific coding in the middle tier.
3. 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

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.

1. No client native libraries required to be installed in client machine.
2. Comes with most of the Databases.
3. 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.

1. Slower in execution compared with other JDBC Driver due to Java libraries are used in socket communication with the database.
2. 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 ?

What are the Java JDBC API Versions ?

1. Java JDBC 4.3 API
2. Java JDBC 4.2 API
3. Java JDBC 4.1 API
4. Java JDBC 4.0 API
5. Java JDBC 3.0 API
6. Java JDBC 2.0 API
7. 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

1. Added Sharding support
2. Enhanced Connection to be able to provide hints to the driver that a request, an independent unit of work, is beginning or ending
3. Enhanced DatabaseMetaData to determine if Sharding is supported
4. Added the method drivers to DriverManager to return a Stream of the currently loaded and available JDBC drivers
5. Added support to Statement for enquoting literals and simple identifiers
6. 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

1. Added JDBCType enum and SQLType interface
2. Support for REF CURSORS in CallableStatement
3. DatabaseMetaData methods to return maximum Logical LOB size and if Ref Cursors are supported
4. 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

1. Allow Connection, ResultSet and Statement objects to be used with the try-with-resources statement
2. Support added to CallableStatement and ResultSet to specify the Java type to convert to via the getObject method
3. DatabaseMetaData methods to return PseudoColumns and if a generated key is always returned
4. Added support to Connection to specify a database schema, abort and timeout a physical connection.
5. Added support to close a Statement object when its dependent objects have been closed
6. 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

1. auto java.sql.Driver discovery - no longer need to load a java.sql.Driver class via Class.forName
2. National Character Set support added
3. Support added for the SQL:2003 XML data type
4. SQLException enhancements - Added support for cause chaining; New SQLExceptions added for common SQLState class value codes
5. Enhanced Blob/Clob functionality - Support provided to create and free a Blob/Clob instance as well as additional methods added to improve accessibility
6. Support added for accessing a SQL ROWID
7. 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.
8. 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

1. Pooled statements - reuse of statements associated with a pooled connection
2. Savepoints - allow a transaction to be rolled back to a designated savepoint
3. Properties defined for ConnectionPoolDataSource - specify how connections are to be pooled
4. Metadata for parameters of a PreparedStatement object
5. Ability to retrieve values from automatically generated columns
6. Ability to have multiple ResultSet objects returned from CallableStatement objects open at the same time
7. Ability to identify parameters to CallableStatement objects by name as well as by index
8. ResultSet holdability - ability to specify whether cursors should be held open or closed at the end of a transaction
9. Ability to retrieve and update the SQL structured type instance that a Ref object references
10. Ability to programmatically update BLOB, CLOB, ARRAY, and REF values.
11. Addition of the java.sql.Types.DATALINK data type - allows JDBC drivers access to objects stored outside a data source
12. 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

1. 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
2. Batch updates
3. Programmatic updates - using ResultSet updater methods
4. New data types - interfaces mapping the SQL3 data types
5. Custom mapping of user-defined types (UDTs)
6. 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.
7. 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.
8. Pooled connections - allowing connections to be used and reused
9. Distributed transactions - allowing a transaction to span diverse DBMS servers
10. 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 :

1. First register the jdbc drivers.
2. Second create Java jdbc connection to database.
3. Third create statement.
4. Fourth execute query to database.
5. Fifth and finally close the Java jdbc resultset, statement and database connection.