Java Database Connectivity (JDBC)
The Java Database Connectivity (JDBC) API enable Java application to interact with a database.
It’s a java API for communicating to relational database,API has java classes and interfaces using that developer can easily interact with database.
What are the main steps in java to make JDBC connectivity?
1.Load a Driver
2.Establish a connection
(i) driver name
(ii) port no.
(iii) ip address
(iv) user name
(v) passward
3.Statement object
4.Execute the Query(ResultSet)
(i)Process the Results
5.Close the connection
The Java Database Connectivity (JDBC) API enable Java application to interact with a database.
It’s a java API for communicating to relational database,API has java classes and interfaces using that developer can easily interact with database.
What are the main steps in java to make JDBC connectivity?
1.Load a Driver
2.Establish a connection
(i) driver name
(ii) port no.
(iii) ip address
(iv) user name
(v) passward
3.Statement object
4.Execute the Query(ResultSet)
(i)Process the Results
5.Close the connection
1.Load a Driver:- Before you can connect to a database, you need to load the driver. Use Class.forName(String) method. This method takes a string representing a fully qualified class name and loads the corresponding class.your code implicitly loads the driver using the Class.forName() method.Here is an example:
try {
Class.forName("connect.microsoft.MicrosoftDriver");
//Class.forName("oracle.jdbc.driver.OracleDriver"); for Oracle driver
//Class.forName("com.sybase.jdbc.SybDriver"); for sybase driver
} catch(ClassNotFoundException e) {
System.err.println("Error loading driver: " + e);
}
Note:-The forName() method can throw a ClassNotFoundException if the driver is not available.
2.Establish a connection:- To make the actual network connection, pass the URL, the database username, and the password to the getConnection method of the DriverManager class,as illustrated in the following example.
Connection con=DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:xe","system","password");
3.Statement object:- A Statement object is used to send queries and commands to the database and is created from the Connection as follows:
Statement stmt = connection.createStatement();
4.Execute the Query(ResultSet):- Once you have a Statement object, you can use it to send SQL queries by using the executeQuery method, which returns an object of type ResultSet. Here is an example:
String query = "SELECT col1, col2, col3 FROM sometable";
ResultSet rs = statement.executeQuery(query);
(i)Process the Results:- The simplest way to handle the results is to process them one row at a time, using the ResultSet’s next method to move through the table a row at a time. Within a row, ResultSet provides various getXxx methods that take a column index or column name as an argument and return the result as a variety of different Java types. For instance, use getInt if the value should be an integer, getString for a String, and so on for most other data types. If you just want to display the results, you can use getString regardless of the actual column type. However, if you use the version that takes a column index, note that columns are indexed starting at 1 (following the SQL convention), not at 0 as with arrays, vectors, and most other data structures in the Java programming language.
Note that the first column in a ResultSet row has index 1, not 0. Here is an example that prints the values of the first three columns in all rows of a ResultSet.
while(resultSet.next()) {
System.out.println(results.getString(1) + " " +
results.getString(2) + " " +
results.getString(3));
}
5.Close the connection:- To close the connection explicitly, you should do
connection.close();
_________________________________________________________________________________
Java Database Connectivity (JDBC) Driver
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 (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)
1. JDBC-ODBC bridge driver:- The JDBC type 1 driver which is also known as a JDBC-ODBC Bridge is a convert JDBC methods into ODBC function calls.
Sun provides a JDBC-ODBC Bridge driver by “sun.jdbc.odbc.JdbcOdbcDriver”.
The driver is a platform dependent because it uses ODBC which is depends on native libraries of the operating system and also the driver needs other installation for example, ODBC must be installed on the computer and the database must support ODBC driver.
The JDBC-ODBC Bridge is use only when there is no PURE-JAVA driver available for a particular database.
Process:-
Java Application → JDBC APIs → JDBC Driver Manager → Type 1 Driver → ODBC Driver → Database library APIs → Database
Advantages:-
1.easy to use.
2.can be easily connected to any database.
Disadvantages:-
1.Performance degraded because JDBC method call is converted into the ODBC funcion calls.
2.The ODBC driver needs to be installed on the client machine.
2. Native-API driver (partially java driver):- The JDBC type 2 driver is uses the libraries of the database which is available at client side and this driver converts the JDBC method calls into native calls of the database so this driver is also known as a Native-API driver.
try {
Class.forName("connect.microsoft.MicrosoftDriver");
//Class.forName("oracle.jdbc.driver.OracleDriver"); for Oracle driver
//Class.forName("com.sybase.jdbc.SybDriver"); for sybase driver
} catch(ClassNotFoundException e) {
System.err.println("Error loading driver: " + e);
}
Note:-The forName() method can throw a ClassNotFoundException if the driver is not available.
2.Establish a connection:- To make the actual network connection, pass the URL, the database username, and the password to the getConnection method of the DriverManager class,as illustrated in the following example.
Connection con=DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:xe","system","password");
3.Statement object:- A Statement object is used to send queries and commands to the database and is created from the Connection as follows:
Statement stmt = connection.createStatement();
4.Execute the Query(ResultSet):- Once you have a Statement object, you can use it to send SQL queries by using the executeQuery method, which returns an object of type ResultSet. Here is an example:
String query = "SELECT col1, col2, col3 FROM sometable";
ResultSet rs = statement.executeQuery(query);
(i)Process the Results:- The simplest way to handle the results is to process them one row at a time, using the ResultSet’s next method to move through the table a row at a time. Within a row, ResultSet provides various getXxx methods that take a column index or column name as an argument and return the result as a variety of different Java types. For instance, use getInt if the value should be an integer, getString for a String, and so on for most other data types. If you just want to display the results, you can use getString regardless of the actual column type. However, if you use the version that takes a column index, note that columns are indexed starting at 1 (following the SQL convention), not at 0 as with arrays, vectors, and most other data structures in the Java programming language.
Note that the first column in a ResultSet row has index 1, not 0. Here is an example that prints the values of the first three columns in all rows of a ResultSet.
while(resultSet.next()) {
System.out.println(results.getString(1) + " " +
results.getString(2) + " " +
results.getString(3));
}
5.Close the connection:- To close the connection explicitly, you should do
connection.close();
_________________________________________________________________________________
Java Database Connectivity (JDBC) Driver
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 (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)
1. JDBC-ODBC bridge driver:- The JDBC type 1 driver which is also known as a JDBC-ODBC Bridge is a convert JDBC methods into ODBC function calls.
Sun provides a JDBC-ODBC Bridge driver by “sun.jdbc.odbc.JdbcOdbcDriver”.
The driver is a platform dependent because it uses ODBC which is depends on native libraries of the operating system and also the driver needs other installation for example, ODBC must be installed on the computer and the database must support ODBC driver.
The JDBC-ODBC Bridge is use only when there is no PURE-JAVA driver available for a particular database.
Process:-
Java Application → JDBC APIs → JDBC Driver Manager → Type 1 Driver → ODBC Driver → Database library APIs → Database
Advantages:-
1.easy to use.
2.can be easily connected to any database.
Disadvantages:-
1.Performance degraded because JDBC method call is converted into the ODBC funcion calls.
2.The ODBC driver needs to be installed on the client machine.
2. Native-API driver (partially java driver):- The JDBC type 2 driver is uses the libraries of the database which is available at client side and this driver converts the JDBC method calls into native calls of the database so this driver is also known as a Native-API driver.
Process:-
Java Application → JDBC APIs → JDBC Driver Manager → Type 2 Driver → Vendor Client Database library APIs → Database
Advantage:-
1.There is no implantation of JDBC-ODBC Bridge so it’s faster than a type 1 driver; hence the performance is better as compare the type 1 driver (JDBC-ODBC Bridge).
2.Performance upgraded than JDBC-ODBC bridge driver.
Disadvantage:-
1.The Native driver needs to be installed on the each client machine.
2.The Vendor client library needs to be installed on client machine.
3. Network Protocol driver (fully java driver):- Type 3 database requests are passed through the network to the middle-tier server. The middle-tier then translates the request to the database.
Process:-
Java Application → JDBC APIs → JDBC Driver Manager → Type 3 Driver → Middleware (Server)→ any Database
Advantage:-
1.There is no need for the vendor database library on the client machine because the middleware is database independent and it communicates with client.
2.Type 3 driver can be used in any web application as well as on internet also because there is no any software require at client side.
3.A single driver can handle any database at client side so there is no need a separate driver for each database.
4.The middleware server can also provide the typical services such as connections, auditing, load balancing, logging etc.
Disadvantage:-
1.An Extra layer added, may be time consuming.
2.At the middleware develop the database specific coding, may be increase complexity.
3.Network support is required on client machine.
4. Thin driver (fully java driver):- The JDBC type 4 driver converts JDBC method calls directly into the vendor specific database protocol and in between do not need to be converted any other formatted system so this is the fastest way to communicate quires to DBMS and it is completely written in JAVA because of that this is also known as the “direct to database Pure JAVA driver”.
Process:-
Java Application → JDBC APIs → JDBC Driver Manager → Type 4 Driver (Pure JAVA Driver) → Database Server
Advantage:-
1.It’s a 100% pure JAVA Driver so it’s a platform independence.
2.No translation or middleware layers are used so consider as a faster than other drivers.
3.The all process of the application-to-database connection can manage by JVM so the debugging is also managed easily.
Disadvantage:-
1.There is a separate driver needed for each database at the client side.
2.Drivers are Database dependent, as different database vendors use different network protocols.
Java Application → JDBC APIs → JDBC Driver Manager → Type 2 Driver → Vendor Client Database library APIs → Database
Advantage:-
1.There is no implantation of JDBC-ODBC Bridge so it’s faster than a type 1 driver; hence the performance is better as compare the type 1 driver (JDBC-ODBC Bridge).
2.Performance upgraded than JDBC-ODBC bridge driver.
Disadvantage:-
1.The Native driver needs to be installed on the each client machine.
2.The Vendor client library needs to be installed on client machine.
3. Network Protocol driver (fully java driver):- Type 3 database requests are passed through the network to the middle-tier server. The middle-tier then translates the request to the database.
Process:-
Java Application → JDBC APIs → JDBC Driver Manager → Type 3 Driver → Middleware (Server)→ any Database
Advantage:-
1.There is no need for the vendor database library on the client machine because the middleware is database independent and it communicates with client.
2.Type 3 driver can be used in any web application as well as on internet also because there is no any software require at client side.
3.A single driver can handle any database at client side so there is no need a separate driver for each database.
4.The middleware server can also provide the typical services such as connections, auditing, load balancing, logging etc.
Disadvantage:-
1.An Extra layer added, may be time consuming.
2.At the middleware develop the database specific coding, may be increase complexity.
3.Network support is required on client machine.
4. Thin driver (fully java driver):- The JDBC type 4 driver converts JDBC method calls directly into the vendor specific database protocol and in between do not need to be converted any other formatted system so this is the fastest way to communicate quires to DBMS and it is completely written in JAVA because of that this is also known as the “direct to database Pure JAVA driver”.
Process:-
Java Application → JDBC APIs → JDBC Driver Manager → Type 4 Driver (Pure JAVA Driver) → Database Server
Advantage:-
1.It’s a 100% pure JAVA Driver so it’s a platform independence.
2.No translation or middleware layers are used so consider as a faster than other drivers.
3.The all process of the application-to-database connection can manage by JVM so the debugging is also managed easily.
Disadvantage:-
1.There is a separate driver needed for each database at the client side.
2.Drivers are Database dependent, as different database vendors use different network protocols.
No comments:
Post a Comment