Java Tutorials and Projects: JAVA DATABASE CONNECTIVITY (JDBC)

JDBC :-JAVA DATABASE CONNECTIVITY

Ø In java.sql package there are some set of classes and interface to connect to the database.

INTERFACES:-

Connection

Statement

PreparedStatement

CallableStatement

ResultSet

ResultSetMetaData

DatabaseMetaData

CLASSES:-

DriverManager

SQLException

SQLWarning

Ø To connect to a database we have to get the following information. They are:

1) Driver Class Name

2) URL

3) Username of the data base

4) Passoword of the data base

Ø To connect to a database we have to write thefollowing syntax in the program:-

Class.forName("driver calss name”);

Connection con=DriverManager.getConnection("url","username","password");

EXPLANATION:-

Class - class

forName- method

Connection - interface

DriverManager- class

getConnection- method

TYPES OF DRIVERS:-

1.Type one driver (or) JDBC ODBC bridge

2.Type two driver (or) Native API partly Java driver

3.Type three driver (or) Net pure Java driver

4.Type four driver (or) Pure Java driver

Type one driver (or) JDBC ODBC bridge:

Ø In JDBC ODBC bridge, first the jdbc driver is loaded in the program and this JDBC connect to ODBC and then ODBC connect to database and some query is

executed in the database and this query will return some result and this result come to ODBC and then to JDBC and then to program and the output displayed according to the logic present in the program.

Ø To connect to oracle data base using type 1 driver we have to write the following syntax:

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection ("jdbc: odc: dsn","scott","tiger");

HOW TO CREATE DSN:-

Start->Control Panel ->AdminstrativeTools ->data sources(odbc) ->(window open) In user DSN click add button->create new data source (window open)

In that select Microsoft ODBC for oracle ->click finish ->(window open)

Data source Name :hello

Description : leave blank

username :scott

server : leave blank ->click ok and again click ok

NOTE:-

Ø Odbc means Open Database Connectivity.

Ø Odbc is language independent and platform independent.

Ø It is introduced by Microsoft andSql Access Group companies in the year 1992.

Ø JDBC was designed by Sun MicroSystems Incorporation in the year 1997 in the version j2se1.1 by name JDBC ODBC Bridge.

PROGRAM:- To connect to oracle database using type 1 driver.

import java.sql.*;

classConnectdemo

{

public static void main(String args[])

{

try

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

System.out.println("connected to database");

}

catch(ClassNotFoundException e)

{

System.out.println(e);

}

catch(SQLException e)

{

System.out.println(e);

}

FILE NAME:-Connectdemo.java

COMMANDS:-

javac Connectdemo.java

javaConnectdemo

OUTPUT:

connected to database

PROGRAM:- To connect to oracle database using type 4 driver.

import java.sql.*;

class Connectdemo1

{

public static void main(String args[])

{

try

{

Class.forName ("oracle.jdbc.driver.OracleDriver");

Connection con=DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe","scott","tiger");

System.out.println("connected to database");

}

catch(ClassNotFoundException e)

{

System.out.println(e);

}

catch(SQLException e)

{

System.out.println(e);

}

FILE NAME:- Connectdemo1.java

COMMANDS:-

javac Connectdemo1.java

java Connectdemo1

OUTPUT:

connected to database

NOTE:-

Ø When we execute the program we will error like “The driver classname not found oracle.jdbc.driver.OracleDriver”.

Ø To solve this error we have to set classpath to ojdbc14.jar file present in Oracle Installation folder.

DatabaseMetaData:-

Ø This interface is used to get the information about the database we have connected.

PROGRAM:- To get the information about database we have connected.

import java.sql.*;

class Connectdemo2

{

public static void main(String args[])

{

try

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

DatabaseMetaDatadb=con.getMetaData();

System.out.println(db.getDatabaseProductName());

System.out.println(db.getDatabaseProductVersion());

System.out.println(db.getDriverName());

System.out.println(db.getDriverVersion());

System.out.println(db.getDriverMajorVersion());

System.out.println(db.getDriverMinorVersion());

}

catch(ClassNotFoundException e)

{

System.out.println(e);

}

catch(SQLException e)

{

System.out.println(e);

}

FILE NAME:- Connectdemo2.java

COMMANDS:-

javac Connectdemo2.java

java Connectdemo2

OUTPUT:

Oracle

PROGRAM:- To create table in the database

import java.sql.*;

class Tabledemo

{

public static void main(String args[]) throws Exception

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

Statement stmt=con.createStatement();

stmt.executeUpdate("create table employee(eno number(5) primary key,ename varchar2(10),desig varchar2(10))");

System.out.println("table created");

}

FILE NAME:-Tabledemo.java

COMMANDS:-

javac Tabledemo.java

javaTabledemo

OUTPUT:

table created

RESULT: A table is created by name employee in the database.

PROGRAM:- To insert data in the table using Statement interface

import java.sql.*;

class Insertdemo

{

public static void main(String args[]) throws Exception

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

Statement stmt=con.createStatement();

stmt.executeUpdate("insert into employee values(10,'kumar','SE')");

System.out.println("data inserted");

}

FILE NAME:-Insertdemo.java

COMMANDS:-

Javac Insertdemo.java

Java Insertdemo

OUTPUT:

data inserted

RESULT: data is inserted inemployee table

PROGRAM:- To insert data in the table using PreparedStatement interface

import java.sql.*;

class Insertdemo1

{

public static void main(String args[]) throws Exception

{

int no=20;

String name="kiran",desig="TL";

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection ("jdbc:odbc:hello","scott","tiger");

PreparedStatementstmt=con.prepareStatement("insert into employee values(?,?,?)");

stmt.setInt(1,no);

stmt.setString(2,name);

stmt.setString(3,desig);

stmt.executeUpdate();

System.out.println("data inserted");

}

FILE NAME: -Insertdemo1.java

COMMANDS:-

javac Insertdemo1.java

java Insertdemo1

OUTPUT:

data inserted

RESULT: data is inserted in employee table

PROGRAM:- To insert data in the table using PreparedStatement interface by giving input at runtime.

import java.sql.*;

import java.io.*;

class Insertdemo2

{

public static void main(String args[]) throws Exception

{

BufferedReaderbr=new BufferedReader(new InputStreamReader(System.in));

System.out.println("Enter Emp Number:");

int no=Integer.parseInt(br.readLine());

System.out.println("Enter Emp Name:");

String name=br.readLine();

System.out.println("Enter EmpDesig:");

String desig=br.readLine();

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection ("jdbc:odbc:hello","scott","tiger");

PreparedStatementstmt=con.prepareStatement("insert into emplogin values(?,?,?)");

stmt.setInt(1,no);

stmt.setString(2,name);

stmt.setString(3,desig);

stmt.executeUpdate();

System.out.println("data inserted");

}

FILE NAME: - Insertdemo2.java

COMMANDS:-

javac Insertdemo2.java

java Insertdemo2

OUTPUT:

Enter Emp Number:

Enter Emp Name:

Raghu

Enter EmpDesig:

SSE

data inserted

RESULT: data is inserted in employee table

PROGRAM:- To retrieve data from the table of selected employee by giving employee number at runtime.

import java.sql.*;

import java.io.*;

class Selectdemo

{

public static void main(String args[]) throws Exception

{

try

{

BufferedReader br=new BufferedReader(new InputStreamReader(System.in));

System.out.println("Enter Emp Number:");

int no=Integer.parseInt(br.readLine());

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

Statement stmt=con.createStatement();

ResultSetrs=stmt.executeQuery("select * from employee where eno="+no+"");

rs.next();

String name=rs.getString(2);

String desig=rs.getString(3);

System.out.println("Emp Number:"+no);

System.out.println("Emp Name:"+name);

System.out.println("Emp Desig:"+desig);

}

catch(SQLException e)

{

System.out.println("empdoesnot exists");

}

FILE NAME: - Selectdemo.java

COMMANDS:-

javac Selectdemo.java

java Selectdemo

OUTPUT:

Enter Emp Number:

Emp Number: 30

Emp Name: Raghu

Emp Desig: SSE

RESULT: data is retrieved from employee table

PROGRAM:- To retrieve all data from the table.

import java.sql.*;

class Selectdemo1

{

public static void main(String args[]) throws Exception

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

Statement stmt=con.createStatement();

ResultSetrs=stmt.executeQuery("select * from employee");

while(rs.next())

{

int no=rs.getInt(1);

String name=rs.getString(2);

String desig=rs.getString(3);

System.out.println("Emp Number:"+no);

System.out.println("Emp Name:"+name);

System.out.println("Emp Desig:"+desig);

System.out.println("----------------------------");

}

FILE NAME: - Selectdemo1.java

COMMANDS:-

javac Selectdemo1.java

java Selectdemo1

OUTPUT:

Emp Number: 10

Emp Name: kumar

Emp Desig: SE

------------------------------

Emp Number: 20

Emp Name: kiran

Emp Desig: TL

------------------------------

Emp Number: 30

Emp Name: Raghu

Emp Desig: SSE

RESULT: All data is retrieved from employee table

PROGRAM:- To update the designation for selected employee .

import java.sql.*;

import java.io.*;

class Updatedemo

{

public static void main(String args[]) throws Exception

{

BufferedReader br=new BufferedReader(new InputStreamReader(System.in));

System.out.println("Enter Emp Number:");

int no=Integer.parseInt(br.readLine());

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

Statement stmt1=con.createStatement();

ResultSetrs=stmt1.executeQuery("select * from employee where eno="+no+"");

if(rs.next())

{

System.out.println("Enter Emp Desig:");

String desig=br.readLine();

PreparedStatementstmt=con.prepareStatement("update employee set desig=? whereeno=?");

stmt.setString(1,desig);

stmt.setInt(2,no);

stmt.executeUpdate();

System.out.println("data updated");

}

else

{

System.out.println("employee doesnot exists");

}

FILE NAME: - Updatedemo.java

COMMANDS:-

javac Updatedemo.java

java Updatedemo

OUTPUT:

Enter Emp Number:

Enter Emp Desig:

data updated

Enter Emp Number:

employee doesnot exists

RESULT: Data is updated in employee table

PROGRAM:- To delete selected employee date from the table.

import java.sql.*;

import java.io.*;

class Deletedemo

{

public static void main(String args[]) throws Exception

{

BufferedReaderbr=new BufferedReader(new InputStreamReader(System.in));

System.out.println("Enter Emp Number:");

int no=Integer.parseInt(br.readLine());

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

Statement stmt1=con.createStatement();

ResultSetrs=stmt1.executeQuery("select * from employee where eno="+no+"");

if(rs.next())

{

PreparedStatementstmt=con.prepareStatement("delete from employee where eno=?");

stmt.setInt(1,no);

stmt.executeUpdate();

System.out.println("data deleted");

}

else

{

System.out.println("empdoesnot exists");

}

FILE NAME: - Deletedemo.java

COMMANDS:-

javac Deletedemo.java

java Deletedemo

OUTPUT:

Enter Emp Number:

data deleted

Enter Emp Number:

employee doesnot exists

RESULT: Data is deleted from employee table

ResultSetMetaData:-

Ø This interface is used to get the information about the table present in the database.

PROGRAM:- To get the information about the table.

import java.sql.*;

class Rsdemo

{

public static void main(String args[]) throws Exception

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

Statement stmt=con.createStatement();

ResultSetrs=stmt.executeQuery("select * from employee");

ResultSetMetaDatarsmd=rs.getMetaData();

System.out.println(rsmd.getColumnCount());

System.out.println(rsmd.getColumnLabel(1));

System.out.println(rsmd.getColumnName(2));

System.out.println(rsmd.getColumnType(1));

System.out.println(rsmd.getColumnType(2));

System.out.println(rsmd.getColumnTypeName(1));

System.out.println(rsmd.getColumnTypeName(2));

System.out.println(rsmd.getColumnDisplaySize(3));

}

FILE NAME: - Rsdemo.java

COMMANDS:-

javac Rsdemo.java

java Rsdemo

CONSTANT PRESENT RESULTSET INTERFACE:-

CONSTANTS BUILT IN VALUES

--------------------------------------------------------------------------------------

FETCH_FORWARD 1000

FETCH_REVERSE 1001

FETCH_UNKNOWN 1002

TYPE_FORWARD_ONLY 1003

TYPE_SCROLL_INSENSITIVE 1004

TYPE_SCROLL_SENSITIVE 1005

CONCUR_READ_ONLY 1007

CONCUR_UPDATABLE 1008

TYPES OF RESULTSET:-

1. Resultset.Type_FORWARD_ONLY

2. Resultset.Type_SCROLL_INSENSITIVE

3. Resultset.Type_SCROLL_SENSITIVE

4. Resultset.CONCUR_READ_ONLY

5. Resultset.Type_CONCUR_UPDATABLE

NOTE:

Ø The default resultset is TYPE_FORWARD_ONLY

PROGRAM:- To get the default values for ResultSet interface.

import java.sql.*;

class Fetchdemo

{

public static void main(String args[]) throws Exception

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

Statement stmt=con.createStatement();

ResultSetrs=stmt.executeQuery("select * from employee");

System.out.println(rs.getType());

System.out.println(rs.getRow());

rs.next();

System.out.println(rs.getRow());

System.out.println(rs.getStatement());

System.out.println(rs.getConcurrency());

System.out.println(rs.getFetchDirection());

}

FILE NAME: - Fetchdemo.java

COMMANDS:-

javac Fetchdemo.java

java Fetchdemo

PROGRAM:- To get the data in reverse direction from the table.

import java.sql.*;

class Fetchdemo1

{

public static void main(String args[]) throws Exception

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection ("jdbc:odbc:hello","scott","tiger");

Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

ResultSetrs=stmt.executeQuery("select * from employee");

rs.setFetchDirection(1001);

try

{

while (rs.next())

{

int no=rs.getInt(1);

String name=rs.getString(2);

String desig=rs.getString(3);

System.out.println("Emp Number:"+no);

System.out.println("Emp Name:"+name);

System.out.println("EmpDesig:"+desig);

System.out.println("------------------------------");

}

catch(SQLException e)

{

System.out.println("before first row");

}

FILE NAME: - Fetchdemo1.java

COMMANDS:-

javac Fetchdemo1.java

java Fetchdemo1

PROGRAM:- To get the data by using methods present in ResultSet interface.

import java.sql.*;

class Fetchdemo2

{

public static void main(String args[]) throws Exception

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

ResultSetrs=stmt.executeQuery("select * from employee");

while(rs.next())

{

showRow(rs);

}

rs.first();

System.out.println("First Row");

showRow(rs);

rs.last();

System.out.println("Last Row");

showRow(rs);

rs.previous();

System.out.println("Previous Row");

showRow(rs);

try

{

rs.absolute(5);

System.out.println("Absolute Row");

showRow(rs);

}

catch(SQLException e)

{

System.out.println("row doestnot exists");

}

public static void showRow(ResultSetrs) throws Exception

{

int no=rs.getInt(1);

String name=rs.getString(2);

String desig=rs.getString(3);

System.out.println("Emp Number:"+no);

System.out.println("Emp Name:"+name);

System.out.println("EmpDesig:"+desig);

System.out.println("------------------------------");

}

FILE NAME: - Fetchdemo2.java

COMMANDS:-

javac Fetchdemo2.java

java Fetchdemo2

PROGRAM:- To insert,update and delete data in the table by using methods present in ResultSet interface.

import java.sql.*;

class Fetchdemo3

{

public static void main(String args[]) throws Exception

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hello","scott","tiger");

Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSetrs=stmt.executeQuery("select * from empdemo");

rs.absolute(2);

rs.updateString(3,"PM");

rs.updateRow();

rs.updateInt(1,250);

rs.updateString(2,"kumar");

rs.updateString(3,"manager");

rs.insertRow();

rs.absolute(2);

rs.deleteRow();

}

FILE NAME: - Fetchdemo3.java

COMMANDS:-

javac Fetchdemo3.java

java Fetchdemo3