.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> General >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Introduction to Java Database Connectivity

Posted By:satyapriyanayak       Posted Date: July 21, 2014    Points: 200    Category: General    URL: http://www.dotnetspark.com  

In this article we will know about Java Database Connectivity.
 

JDBC (Java Database Connectivity)

This is a technology to establish communication between java program and a DBMS.It uses SQL (structure query language) for storing, updating, or removing data from a DBMS .The java program can be stand alone application, web application or enterprise application. The DBMS can be of any type as oracle, Sqlserver or Ms access

Driver Manager

This is an application available to Java virtual machine for recognizing appropriate driver from the list. The java program has to supply path of driver to the driver manager for its initialization. This is an interface between the java program and DBMS.It makes available to the DBMS specific code to the java program and DBMS understandable code to the DBMS.It provides a set of driver for communicating with various types of DBMS.These are available in a package as ODBC (open database connective). JDK provides a driver to communicate with the ODBC.Combination of these two drivers is known as JDBC.ODBC bridge driver. This is also known as default driver. A java program may use some explicit JDBC driver available from DBMS vendors or any other external company. These drivers are distributed into three different types as type-2, type-3, and type-4.

Type 2 Drivers - the Native-API Driver:

The JDBC type 2 drivers, also known, as the Native-API driver is a database driver. The driver converts JDBC method calls into native calls of the database API. The type 2 drivers are not written entirely in Java as it interfaces with non-Java code that makes the final database calls.
 
Type 3 Drivers - the Network-Protocol Driver:
The JDBC type 3 driver, also known as the network-protocol driver is a database driver implementation which makes use of a middle-tier between the calling program and the database. The middle-tier (application server) converts JDBC calls directly or indirectly into the vendor-specific database protocol.

Type 4 Drivers - the Native-Protocol Driver:
The JDBC type 4 drivers, also known as the native-protocol driver is a database driver implementation that converts JDBC calls directly into the vendor-specific database protocol. The type 4 drivers are written completely in Java and are hence platform independent. It is installed inside the Java Virtual Machine of the client. It provides better performance over the type 1 and 2 drivers, as it does not have the overhead of conversion of calls into ODBC or database API calls

Steps to follow for connecting to DBMS
1.Load the driver class into runtime environment by using forName() method of java.lang class.This method accepts a string parameter to contain the class name.This method throws java.lang class not found exception. Class is a predefined class and forName is the static method of the class, which is used to load the driver in the memory for connectivity.
Ex:- Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")

2.Create a reference of java.sql.connection by using getConnection() method of java.sql. DriverManager class.This method access three parameters as path of the driver in JDBC:ODBC bridge.We required DSN(Data source name) in this position.Dsn can be created in control panel

Ex: Connection con=DriverManager.getConnection("jdbc:odbc:dsnname","system","password");

The getConnection() method throws java.sql.sql exception.

What is DSN?

This is a name provided to DBMS driver present in ODBC.This recognizes the database to be used of a specific DBMS.

Creation of dsn(database source name) for Oracle
 
Start-Control panel- Administrative Tools- Data Sources (ODBC)-go to system dsn tab-click add button-select a driver for which you want to set up data source (for Oracle- Oracle in XE)-select it and click finish-give any name in data source name textbox-then click ok button.

3.Create an object of java.sql statement by using create statement () method of connection. Statement is being used to execute various sql commands.
Ex:- Statement stmt=con.createStatement();

4.Use appropriate method of statement to execute sql command for select command use executeQuery() method and for insert, update and delete use executeUpdate() method.

Note:-

execute()- is for invoking the functions or stored procedures of SQL by the CallableStatement.
executeUpdata()- is for the operations such as insert,update or delete on SQL by Statement ,PreparedStatement.
executeQuery() - is for operation select of Sql by PreparedStatement or Statement.

Example:- To display data from database through jdbc and show the ouput in console.

Create table employee (empno int,empname varchar(50))

Insert into employee values (1,'Raj')
Insert into employee values (2,'Ravi')
Insert into employee values (3,'Rahul')

jdbcconnection.java file

/*This uses Oracle through a DSN  */
import java.sql.*;
public class jdbcconnection
{
public static void main(String args[]) throws Exception{
 //Step-1
//load the class for driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Step -2
Connection con=DriverManager.getConnection("jdbc:odbc:dsn1","system","pintu");
//Step -3
System.out.println("Connected to database");
Statement stmt=con.createStatement();
//Step-4
ResultSet rs=stmt.executeQuery("select * from employee");
//Fetching data from ResultSet and display
while(rs.next())
{
//to fetch value from a column having number type of value
int y=rs.getInt("empno");
//to fetch value from a column having varchar/text type of value
String x=rs.getString("empname");
System.out.println(y+"   "+x);
}
//Step-5
con.close();
}
}

Compile

Javac jdbcconnection.java
Java jdbcconnection
 


JDBC statements

They are of three types
1. Statement
2. Prepared statement
3. Callable statement

Statement interface-createStatement() method is useful to create statement object and to execute simple queries. Statement interface fires Static queries.

Prepared Statement interface-It is derived from the Statement interface. Prepare statement () method used to create prepared statement object and used to execute queries with unknown parameters or where in the parameters are provided at the run-time. Prepared Statement used to send SQL Statement to the Database It not only contains the SQL Statement, but also the precompiled SQL Statement. It means that when the Prepared Statement is executed, the DBMS just run the SQL Statement without compiling it. Prepared statement is the type of statement used to interact with database. To avoid syntactical complexities it's the better approach (eg: double quotes used with query strings)
We can insert many rows using single prepared statement
Syntax:
String str="insert into table values ("?,?,?")";
PreparedStatement stmt=con.prepareStatement (str);
 
It will execute before commit sql query and also execute multiple select statement, it will save time, faster execution. PreparedStatement interface fires Dynamic queries.


Ex:- To insert record into a table by using PreparedStatement

Table Creation

create table employee(empno int,empname varchar(50),sal int)

/*To insert record into a table by using PreparedStatement*/
import java.sql.*;
import java.util.*;
public class prepareDemo
{
public static void main(String args[]) throws Exception
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:dsn1","system","pintu");
//step-1 -Reference creation of PreparedStatement
PreparedStatement pstmt=con.prepareStatement("insert into employee(empno,empname,sal) values(?,?,?)");
//step -2 reading from console and providing into sql
Scanner sc=new Scanner(System.in);
System.out.print("Enter the Employee Number : ");
int empno=sc.nextInt();
System.out.print("Enter the Employee Name : ");
String empname=sc.next();
System.out.print("Enter the Employee's salary : ");
int sal=sc.nextInt();
pstmt.setInt(1,empno);
pstmt.setString(2,empname);
pstmt.setInt(3,sal);
//step-3
pstmt.executeUpdate();
System.out.println("record inserted");
con.close();
}
}

Compile

Javac prepareDemo.java
Java prepareDemo



CallableStatement interface-It is derived from the prepared statement interface and helpful in executing stored procedures.prepareCall () method is used to create the callable statement object.
It is an interface in JDBC API, which can be used to calling the stored procedures and functions in backend like oracle, Sybase.
Syntax:
CallableStatement cst=con.prepareCall("{call <procedure name>(?,?)}");


Ex:- To insert record into a table by using CallableStatement

Table Creation

create table employee(empno int,empname varchar(50),sal int)

Store procedure

Create or replace procedure addemp(no number,nm varchar,s number)
as
begin
insert into employee(empno,empname,sal) values(no,nm,s);
end;

callableDemo.java file

/*To use CallableStatement */
import java.sql.*;
import java.util.*;
public class callableDemo
{
public static void main(String args[]) throws Exception
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:dsn1","system","pintu");
//Step-1
CallableStatement cstmt=con.prepareCall("call addemp(?,?,?)");
Scanner sc=new Scanner(System.in);
System.out.print("Enter the Employee No: ");
int x=sc.nextInt();
System.out.print("Enter the Employee Name: ");
String str=sc.next();
System.out.println("Enter the Salary: ");
String j=sc.next();
//Step-2
cstmt.setInt(1,x);
cstmt.setString(2,str);
cstmt.setString(3,j);
//Step -3
cstmt.execute();
System.out.println("***Procedure called****");
System.out.println("Record Sucessfully Inserted");
con.close();
}
}

Compile

Javac callableDemo.java
Java callableDemo



Advantages of JDBC

JDBC is used to Provide Database Connectivity from java to database. Using Java Database Connectivity We can update/retrieve data to/from database with java programs

Disadvantages of JDBC

1.JDBC is not easily if it is used in large projects. There is a big programming overhead.
2.Programmer must hardcode the Transactions and concurrency code in the application.
3.Handling the JDBC connections and properly closing the connection is also a big issue. Properly closing the connection is must.
4.JDBC is not good for big applications



 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend