JDBC:

JDBC (Java Database Connectivity) is uses for connect java application with database. It is Java SE
technology, which is install automatically with the JDK software. JDBC is an API (Application
programming interface) used to communicate Java application to database in database independent
and platform independent manner. It provides classes and interfaces to connect or communicate Java
application with database.

Jdbc ia an API (Application programming interface) used to communicate Java application to

database in database independent and platform independent manner. It provides classes and
interfaces to connect or communicate Java application with database.

Jdbc ia a part of JDK software so no need to install separate software for Jdbc API

Jdbc API consists of two packages

 java.sql package
 javax.sql package

Jdbc Architecture
Jdbc API is a java API that can access any kind of tabular data and specific data stored in
relational database management system (RDBMS).

Packages

 java.sql package
 javax.sql package

Classes In Jdbc
 DriverManager
 SQLException
 Types
 Date
 Time

Interfaces In Jdbc
  Connection
 Statement
 PreparedStatement
 CallableStatementResultset
 ResultSetMetaData
 DatabaseMetaData
 Driver
 Blob
 Clob

There are 6 steps to connect any java application with the database using JDBC. They are as
follows:

1. Load the JDBC driver class or register the JDBC driver.

2. Establish the connection
3. Create a statement
4. Execute the sql commands on database and get the result
5. Print the result
6. Close the connection

1. Register the driver class

In this step we load the JDBC driver class into JVM. This step is also called as registering the
JDBC driver. The forName() method of Class class is used to register the driver class. This
method is used to dynamically load the driver class. This step can be completed in two ways.

 class.forName("fully qualified classname")

 DriveManager.registerDriver(object of driver class)

Syntax of forName() method

public static void forName(String className)throws ClassNotFoundException

Sun.Jdbc.Odbc.JdbcOdbcDriver is a driver class provided by Sun MicroSystem and it can be

loaded into jvm like the following.

Syntax
class.forName("Sun.Jdbc.Odbc.JdbcOdbcDriver");

Syntax
Sun.Jdbc.Odbc.JdbcOdbcDriver jod=new Sun.Jdbc.Odbc.JdbcOdbcDriver();
DriverManager.registerDriver(jod);
2. Create the connection object

In this step connection between a java program and a database will be opened. To open the
connection, we call getConnection() method of DriverManager class.
For getConnection() method we need to pass three parameters.

 url
 username
 password

url: url is used to select one register JDBC driver among multiple registered driver by
DriverManager class.

username and password: username and password are used for authentication purpose.

Syntax of getConnection() method

1) public static Connection getConnection(String url)throws SQLException
2) public static Connection getConnection(String url,String name,String
password)
throws SQLException

Example to establish connection with the Oracle database

Connection con=new DriverManager.getConnection(url, username, password);
Example:
Connection con=new DriverManager.getConnection(Jdbc:Odbc:< dsn >",
"scott","tiger");

3. Create the Statement object

To transfer sql commands from java program to database we need statement object. To create a
statement object we call createStatement() method of connection interface. The
createStatement() method of Connection interface is used to create statement. The object of
statement is responsible to execute queries with the database.

Syntax of createStatement() method

public Statement createStatement()throws SQLException

Example to create the statement object

Statement stmt=new createStatement();
4. Executing queries

Call any one of the following three methods of Statement interface is used to execute queries to
the database and to get the output.

 executeUpdate(): Used for non-select operations.

 executequery(): Used for select operation.
 execute(): Used for both select or non-select operation.

import java.sql.*;
class CreateTable
{
public static void main(String[] args) throws Exception
{
//step-1

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("driver is laoded");

//step-2
Connection
con=DriverManager.getConnection("jdbc:odbc:ramadsn","system","system");
System.out.println("connection is established");

//step-3
Statement stmt=con.createStatement();
System.out.println("statement object is cretaed");

//step-4
int i=stmt.executeUpdate("create table student(sid number(3),sname
varchar2(10),marks number(5))");
//step-5

System.out.println("Result is="+i);
System.out.println("table is created");

//step-6
stmt.close();
con .close();
}
}
What is JDBC Driver?
JDBC drivers implement the defined interfaces in the JDBC API, for interacting with your
database server.

For example, using JDBC drivers enable you to open database connections and to interact with it
by sending SQL or database commands then receiving results with Java.

The Java.sql package that ships with JDK, contains various classes with their behaviours defined
and their actual implementaions are done in third-party drivers. Third party vendors implements
the java.sql.Driver interface in their database driver.

JDBC Drivers Types

JDBC driver implementations vary because of the wide variety of operating systems and
hardware platforms in which Java operates. Sun has divided the implementation types into four
categories, Types 1, 2, 3, and 4, which is explained below

 Jdbc-Odbc Bridge Driver

 Native-API driver (partially java driver)
 Network Protocol driver (fully java driver)
 Thin driver (fully java driver)
Type 1: JDBC-ODBC Bridge Driver
This driver connect a java program with a database using Odbc driver. It is install automatically along
with JDK software. It is provided by Sun MicroSystem for testing purpose this driver can not be used in
real time application. This driver convert JDBC calls into Odbc calls(function) So this is called a bridge
driver.
Advantage of bridge driver
 Easy to use
 It is a database independent driver
 Can be easily connected to any database.
 This driver software is built-in with JDK so no need to install separately.

Disadvantage of bridge driver

 It is a slow driver so not used in real time application
 Because of Odbc connectivity it is a platform dependent driver.
 It is not a portable driver.
 It is not suitable for applet to connect with database.

The JDBC-ODBC Bridge that comes with JDK 1.2 is a good example of this kind of driver.

Type 2: JDBC-Native API

Native API Driver
Native API driver uses native API to connect a java program directly to the database. Native API
id s C, C++ library, which contains a set of function used to connect with database directly.
Native API will be different from one database to another database. So this Native API driver is
a database dependent driver.
The Oracle Call Interface (OCI) driver is an example of a Type 2 driver.

Advantage of Thin driver

 Native API driver comparatively faster than JDBC-ODBC bridge driver.

Disadvantage of Thin driver

 Native API driver is database dependent and also platform dependent because of Native
API.

Type 3: JDBC-Net pure Java

Network Protocol Driver in JDBC
The Network Protocol driver uses middle-ware (application server) that converts JDBC calls
directly or indirectly into the vendor-specific database protocol. It is fully written in java..
Advantage of Network Protocol driver
 No client side library is required because of application server that can perform many
tasks like auditing, load balancing, logging etc.
 This driver is both database and platform independent driver

Disadvantage of Network Protocol driver

 Network support is required on client machine.
 Requires database-specific coding to be done in the middle tier.
 Maintenance of Network Protocol driver becomes costly because it requires database-
specific coding to be done in the middle tier.

Thin Driver in JDBC

The thin driver converts JDBC calls directly into the vendor-specific database protocol. That is
why it is known as thin driver. It is fully written in Java language.

This thin driver uses the following three information to connect with a database.

 Ip address of a machine (system), where the database server is running.

 Port number of the database server.
 Database name, also called SID (service ID).
Advantage of Thin driver
 Thin driver is the fastest driver among all Jdbc drivers.
 No software is required at client side or server side.
 It is portable driver because it is platform independent.
 It can be used to connect an applet with the database.

Disadvantage of Thin driver

 Thin driver is a database dependent driver.

Why thin driver is database dependent driver ?

Because thin driver internally uses native protocol. Native protocol is a server dependent
protocol it means the protocol can establish connection with a particular server only.

Thin driver connect with database

Oracle corporation has provided two JDBC driver software for connection java application to a
database of oracle server.

 Oracle oci driver.

 Oracle thin driver.
The following are the connection properties of oracle thin driver.

driver name: Oracle.Jdbc.OracleDriver

url: Jdbc:Oracle:thin@ipaddress:sid

name System

password Tiger

Type 4: 100% Pure Java

In a Type 4 driver, a pure Java-based driver communicates directly with the vendor's database
through socket connection. This is the highest performance driver available for the database and
is usually provided by the vendor itself.

This kind of driver is extremely flexible, you don't need to install special software on the client
or server. Further, these drivers can be downloaded dynamically

MySQL's Connector/J driver is a Type 4 driver. Because of the proprietary nature of their
network protocols, database vendors usually supply type 4 drivers.

Which Driver should be used?

If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver
type is 4.
If your Java application is accessing multiple types of databases at the same time, type 3 is the
preferred driver.

Type 2 drivers are useful in situations, where a type 3 or type 4 driver is not available yet for
your database.

The type 1 driver is not considered a deployment-level driver, and is typically used for
development and testing purposes only.

Example to Connect Java Application with Oracle database

import java.sql.*;
class OracleCon
{
public static void main(String args[]){
try
{
//step1 load the driver class
Class.forName("oracle.jdbc.driver.OracleDriver");

//step2 create the connection object

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

//step3 create the statement object

Statement stmt=con.createStatement();

//step4 execute query

ResultSet rs=stmt.executeQuery("select * from student");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));

//step5 close the connection object

con.close();

}
catch(Exception e)
{
System.out.println(e);
}
}
}

Drivermanager Class of JDBC

The DriverManager class acts as an interface between user and drivers. It keeps track of the
drivers that are available and handles establishing a connection between a database and the
appropriate driver. The DriverManager class maintains a list of Driver classes that have
registered themselves by calling the method DriverManager.registerDriver().
Register JDBC Driver
You must register the driver in your program before you use it. Registering the driver is the
process by which the Oracle driver's class file is loaded into the memory, so it can be utilized as
an implementation of the JDBC interfaces.

You need to do this registration only once in your program. You can register a driver in one of
two ways.

Approach I - Class.forName()
The most common approach to register a driver is to use Java's Class.forName() method, to
dynamically load the driver's class file into memory, which automatically registers it. This
method is preferable because it allows you to make the driver registration configurable and
portable.

The following example uses Class.forName( ) to register the Oracle driver −

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}

Approach I - Class.forName()
The most common approach to register a driver is to use Java's Class.forName() method, to
dynamically load the driver's class file into memory, which automatically registers it. This
method is preferable because it allows you to make the driver registration configurable and
portable.

The following example uses Class.forName( ) to register the Oracle driver −

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
Database URL Formulation
After you've loaded the driver, you can establish a connection using the
DriverManager.getConnection() method. For easy reference, let me list the three overloaded
DriverManager.getConnection() methods −

 getConnection(String url)
 getConnection(String url, Properties prop)
 getConnection(String url, String user, String password)

Here each form requires a database URL. A database URL is an address that points to your
database.

Formulating a database URL is where most of the problems associated with establishing a
connection occurs.

Following table lists down the popular JDBC driver names and database URL.

Following table lists down the popular JDBC driver names and database URL.

RDBMS JDBC driver name URL format

MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname/ databaseName
jdbc:oracle:thin:@hostname:port
ORACLE oracle.jdbc.driver.OracleDriver
Number:databaseName
DB2 COM.ibm.db2.jdbc.net.DB2Driver jdbc:db2:hostname:port Number/databaseName
jdbc:sybase:Tds:hostname: port
Sybase com.sybase.jdbc.SybDriver
Number/databaseName

Statement interface in JDBC

Statement interface resides in java.sql package and it is used to execute a static

SQL statement and returning the result of the executed query.

Statement interface has two sub-interfaces CallableStatement and

PreparedStatement.

PreparedStatement– PreparedStatement object stores the SQL statement in its

pre-compiled state. That way it can efficiently execute the same SQL statement
multiple times with different parameters.

Difference Between PreparedStatement and Statement

If the sql command is same then actually no need to compiling it for each time before it is
executed. So the performance of an application will be Increased. In this case
PreparedStatement is used.

PreparedStatement Interface is derived Interface of statement and CallableStatement is derived

Interface of PreparedStatement.

Why use PreparedStatement

We know that when working with Statement Interface of JDBC the sql command will be
compiled first and then it is executed at database side even through the same sql command is
execute repeatedly but each time the command is compiled and then executed at database. Due to
this performance of application will be decreased So overcome this problem use
PreparedStatement. In PreparedStatement, If the sql command is same then actually no need to
compiling it for each time before it is executed.

In case of preparedStatement

 First sql command is send to database for compilation and then compiled code will be stored in
preparedStatement object.
 The compiled code will be executed for n number of time without recompiling the sql command.
 The criteria to use preparedStatement is when we want to execute same sql query for multiple
times with different set of values.
 Comparatively preparedStatement is faster than Statement Interface.
Difference between PreparedStatement and Statement

Create an object of PreparedStatement

Syntax
Connection con; // con is reference of connection
PreparedStatement pstmt=con.prepareStatement("sql command");

Why use '?' symbol in PreparedStatement

To pre-compile a command only syntax of the command is required so we can use '?' symbol for
value in the command. '?' symbol is called parameter or replacement operator or place-resolution
operator.

import java.sql.*;
import javax.sql.*;//PreparedStatement;
import java.util.*;
class PrepardTest1
{
Connection con;
void openConnection()throws Exception
{
Class.forName("oracle.jdbc.OracleDriver");
System.out.println("driver is loaded");
con=DriverManager.getConnection("jdbc:oracle:thin:@John-
pc:1521:xe","system","system");
System.out.println("connection is opend");
}
void insertTest()throws Exception
{
PreparedStatement pstmt=con.prepareStatement("insert into student
values(?,?,?) ");
Scanner s=new Scanner(System.in);
String Choice="yes";
while(Choice.equals("yes"))
{
System.out.println("enter student id");
int sid=s.nextInt();
System.out.println("enter student name");
String sname=s.next();
System.out.println("enter Student marks");
int marks=s.nextInt();

//setting the values

pstmt.setInt(1,sid);
pstmt.setString(2,sname);
pstmt.setInt(3,marks);
int i=pstmt.executeUpdate();
System.out.println(i+"Row inserted");
System.out.println("do you want to inset another row(Yes/no)");
Choice=s.next();
}//end while
pstmt.close();
}
void closeConnection()throws Exception
{
con.close();
System.out.println("connection is closed");
}
public static void main(String[] args) throws Exception
{
PrepardTest1 pt=new PrepardTest1();
pt.openConnection();
pt.insertTest();
pt.closeConnection();
}
}

Callablestatement in Jdbc
To call the procedures and functions of a database, CallableStatement interface is used.

CallableStatement is a derived Interface of preparedStatement. It has one additional feature over

PreparedStatement that is calling procedures and function of a database.

Because of CallableStatement is inherited from PreparedStatement all the features of

PreparedStatement are also available with CallableStatement.

Create object of CallableStatement

The prepareCall() method of Connection interface returns the instance of CallableStatement. To
create a reference of CallableStatement we have two syntaxes one with command and the other
is wih calling procedure or function.

Syntax of prepareCall() method

public CallableStatement prepareCall("{ call procedurename(?,?...?)}");

Syntax
CallableStatement cstmt=con.prepareCall("sql command");

Syntax
CallableStatement cstmt=con.prepareCall("{call procedure name or function
name}");

Syntax
CallableStatement stmt=con.prepareCall("{call myprocedure(?,?)}");

One problem though is, that Statement interface is used to execute static SQL
statements with no option to send parameters. Though you can always use a
StringBuilder to append parameters to the SQL and use it with Statement but there
is a better option provided by JDBC itself in the form of PreparedStatement which
is a sub-interface of Statement. In this post we'll see how to use
PreparedStatement in Java with examples.

How to get PreparedStatement object

You can get the PreparedStatement object by calling the prepareStatement method
of the Connection class.

PreparedStatement preparedStatement = connection.prepareStatement(sql);

Advantages of using PreparedStatement in JDBC

As stated above one advantage of PreparedStatement is that you can use

PreparedStatement object for SQL statement with or without parameters. The
advantage of using SQL statement with parameters is that you can use the same
statement and supply it with different parameter values each time you execute it.
That brings us to the second advantage of PreparedStatement, it is more efficient.
Unlike Statement object, PreparedStatement is given the SQL statement when it is
created. So the SQL is sent to the DB right away where it is already compiled.
When you come to execute() method to actually execute the SQL that SQL is pre-
compiled making it more efficient for repeated executions.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCPrepStmt {

public static void main(String[] args) {

Connection connection = null;
try {
// Loading driver
Class.forName("com.mysql.jdbc.Driver");

// Creating connection
connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs",
"root", "admin");
JDBCPrepStmt prep = new JDBCPrepStmt();
prep.insertEmployee(connection, "Kate", 24);
prep.updateEmployee(connection, 22, 30);
prep.displayEmployee(connection, 22);

//prep.deleteEmployee(connection, 24);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null){
//closing connection
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} // if condition
}// finally
}

/**
* @param connection
* @throws SQLException
*/
private void insertEmployee(Connection connection, String name, int age)
throws SQLException{
 String insertSQL = "Insert into employee (name, age) values (?, ?)";
PreparedStatement prepStmt = null;
try {
prepStmt = connection.prepareStatement(insertSQL);
prepStmt.setString(1, name);
prepStmt.setInt(2, age);
int count = prepStmt.executeUpdate();
System.out.println("Count of rows inserted " + count);
}finally{
if(prepStmt != null){
prepStmt.close();
}
}
}

/**
* @param connection
* @param id
* @param age
* @throws SQLException
*/
private void updateEmployee(Connection connection, int id, int age) throws
SQLException{
String updateSQL = "Update employee set age = ? where id = ?";
PreparedStatement prepStmt = null;
try {
prepStmt = connection.prepareStatement(updateSQL);
prepStmt.setInt(1, age);
prepStmt.setInt(2, id);
int count = prepStmt.executeUpdate();
System.out.println("Count of rows updated " + count);
}finally{
if(prepStmt != null){
prepStmt.close();
}
}
}
/**
* @param connection
* @param id
* @throws SQLException
*/
private void deleteEmployee(Connection connection, int id) throws
SQLException {
String deleteSQL = "Delete from employee where id = ?";
PreparedStatement prepStmt = null;
try {
prepStmt = connection.prepareStatement(deleteSQL);
prepStmt.setInt(1, id);
int count = prepStmt.executeUpdate();
System.out.println("Count of rows deleted " + count);
}finally{
if(prepStmt != null){
prepStmt.close();
}
}
}
 /**
* @param connection
* @param id
* @throws SQLException
*/
private void displayEmployee(Connection connection, int id) throws
SQLException{
String selectSQL = "Select * from employee where id = ?";
PreparedStatement prepStmt = null;
try {
prepStmt = connection.prepareStatement(selectSQL);
prepStmt.setInt(1, id);
ResultSet rs = prepStmt.executeQuery();
while(rs.next()){
System.out.println("id : " + rs.getInt("id") + " Name : "
+ rs.getString("name") + " Age : " + rs.getInt("age"));
}
}finally{
if(prepStmt != null){
prepStmt.close();
}
}
}

 Parameterized statement– In the example you can see that all the SQL
statements are parameterized and ‘?’ is used as a placeholder in
parameterized statements.

As example -

String insertSQL = "Insert into employee (name, age) values (?, ?)";

 Setter methods– Values for these placeholders are provided through setter
methods. PreparedStatement has various setter methods for different data
types i.e. setInt(), setString(), setDate() etc.

General form of the setter method is setXXX(int parameterIndex, value)

Here parameterIndex is the index of the parameter in the statement, index

starts from 1.

As example -

String insertSQL = "Insert into employee (name, age) values (?, ?)";

For this sql where the first parameter is String (name) and second parameter
is of type int (age), you need to set the parameters on the
PreparedStatement object as follows -
 prepStmt.setString(1, name);
prepStmt.setInt(2, age);

 Executing PreparedStatement objects– You can use execute methods for

executing the queries.
1. boolean execute()- Executes the SQL statement in this
PreparedStatement object, (it can be any kind of SQL query), which
may return multiple results.
Returns a boolean which is true if the first result is a ResultSet object;
false if it is an update count or there are no results.
2. ResultSet executeQuery(String sql)- Executes the SQL statement
in this PreparedStatement object, which returns a single ResultSet
object. If you want to execute a Select SQL query which returns
results you should use this method.
3. int executeUpdate() - Executes the SQL statement in this
PreparedStatement object, which may be an INSERT, UPDATE, or
DELETE statement or an SQL statement that returns nothing, such as
an SQL DDL statement.
Returns an int denoting either the row count for the rows that are
inserted, deleted, updated or returns 0 if nothing is returned.

import java.sql.*;
class PreparedStatDemo
{
public static void main(String args[])
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","username", "password");
PreparedStatementps = con.prepareStatement("insert into Student values(?, ?, ?)");
ps.setInt(1, 101);
ps.setString(2, "Surendra");
ps.setString(3, "MCA");
ps.executeUpdate();
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}

CallableStatement Interface
The CallableStatement interface is used to execute the SQL stored procedure in a database. The JDBC
API provides stored procedures to be called in a standard way for all RDBMS.

A stored procedure works like a function or method in a class. The stored procedure makes the
performance better because these are precompiled queries.

Creating CallableStatement Interface

The instance of a CallableStatement is created by calling prepareCall() method on a Connection object.

For example:
CallableStatementcallableStatement = con.prepareCall("{call procedures(?,?)}");

Example : CallableStatement Interface using Stored procedure

Example : CallableStatement Interface using Stored procedure

Creating stored procedure

create or replace procedure "insertStudents" (rollno IN NUMBER, name IN VARCHAR2, course IN

VARCHAR2)
is
begin
insert into Students values(rollno, name, course);
end;
/

// ProcedureDemo.java

import java.sql.*;
classProcedureDemo
{
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");
CallableStatementstmt = con.prepareCall("{call insertStudents(?, ?) }");
stmt.setInt(1, 101);
stmt.setString(2, Vinod);
stmt.setString(3, BE);
stmt.execute();
 System.out.println("Record inserted successfully");
con.close();
stmt.close();
}
catch(Execption e)
{
e.printStackTrace();
}
}
}

ResultSet Metadata:

java.sql.ResultSetMetaData is also one of the frequently used interface in the JDBC API. This interface
provides quick overview about a ResultSet object like number of columns, column name, data type of a
column etc. You often need this info about a ResultSet object before processing the actual data of a
ResultSet. In this post, we will discuss ResultSetMetaData in detail with some simple examples.

ResultSetMetaData In JDBC
ResultSetMetaData is an interface in java.sql package of JDBC API which is used to get the metadata
about a ResultSet object. Whenever you query the database using SELECT statement, the result will be
stored in a ResultSet object. Every ResultSet object is associated with one ResultSetMetaData object.
This object will have all the meta data about a ResultSet object like schema name, table name, number
of columns, column name, datatype of a column etc. You can get this ResultSetMetaData object using
getMetaData() method of ResultSet.

How To Get ResultSetMetaData Object?

getMetaData() method of java.sql.ResultSet interface returns ResultSetMetaData object
associated with a ResultSet object. Below is the syntax to get the ResultSetMetaData object.

ResultSetMetaData rsmd = rs.getMetaData();

1. import java.sql.*;
2. class Rsmd{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6. Connection con=DriverManager.getConnection(
7. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
8.
9. PreparedStatement ps=con.prepareStatement("select * from emp");
 10. ResultSet rs=ps.executeQuery();
11. ResultSetMetaData rsmd=rs.getMetaData();
12.
13. System.out.println("Total columns: "+rsmd.getColumnCount());
14. System.out.println("Column Name of 1st column: "+rsmd.getColumnName(1));
15. System.out.println("Column Type Name of 1st column: "+rsmd.getColumnTypeName(1)
);
16.
17. con.close();
18. }catch(Exception e){ System.out.println(e);}
19. }
20. }

Java DatabaseMetaData interface

DatabaseMetaData interface provides methods to get meta data of a database such as database
product name, database product version, driver name, name of total number of tables, name of
total number of views etc.

Commonly used methods of DatabaseMetaData interface

 public String getDriverName()throws SQLException: it returns the name of the JDBC driver.
 public String getDriverVersion()throws SQLException: it returns the version number of the JDBC
driver.
 public String getUserName()throws SQLException: it returns the username of the database.
 public String getDatabaseProductName()throws SQLException: it returns the product name of
the database.
 public String getDatabaseProductVersion()throws SQLException: it returns the product version
of the database.
 public ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern,
String[] types)throws SQLException: it returns the description of the tables of the specified
catalog. The table type can be TABLE, VIEW, ALIAS, SYSTEM TABLE, SYNONYM etc.

How to get the object of DatabaseMetaData:

The getMetaData() method of Connection interface returns the object of DatabaseMetaData.

Syntax:

1. public DatabaseMetaData getMetaData()throws SQLException

Simple Example of DatabaseMetaData interface :

1. import java.sql.*;
 2. class Dbmd{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.
7. Connection con=DriverManager.getConnection(
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9. DatabaseMetaData dbmd=con.getMetaData();
10.
11. System.out.println("Driver Name: "+dbmd.getDriverName());
12. System.out.println("Driver Version: "+dbmd.getDriverVersion());
13. System.out.println("UserName: "+dbmd.getUserName());
14. System.out.println("Database Product Name: "+dbmd.getDatabaseProductName());
15. System.out.println("Database Product Version: "+dbmd.getDatabaseProductVersion());
16.
17. con.close();
18. }catch(Exception e){ System.out.println(e);}
19. }
20. }

Insert images in database

you can not Insert a picture in database directly. but you can store binary data of picture file. To
insert image in database we need a column of type BLOB (Binary Large Object).

Important Points
 In Jdbc only PreparedStatement support the binary data transfer between a Java
application to database.
 Jdbc supports only gif or jpeg or png type of images to insert or read from a database.
 To set binary data into a parameter of PreparedStatement object, you need to call
setBinaryStream().

Parameters of setBinaryStream()
 parameter index
 fileInputStream object
 Size of the file

Find size of Image

To find the size of an image file we need File class object for that file.

Example
File f=new File("c:/img001.gif");
int size=(int)f.length();

Note: In Java forward slash (/) is allowed but backward slash (\) is not allowed at the time of
writing of path.

import java.sql.*;
import java.util.*;
import java.io.*;
class PhotoInsert
{
Connection con;
public void openCon()throws Exception
{
Class.forName("oracle.jdbc.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@rama-
pc:1521:xe","system","system");
System.out.println("connection is opened");
}
public void insert()throws Exception
{
Scanner s=new Scanner(System.in);
PreparedStatement pstmt=con.prepareStatement("insert into emp_info
values(?,?,?)");
System.out.println("enter emp id");
int empid=s.nextInt();
pstmt.setInt(1,empid);
System.out.println("enter emp name");
String empname=s.next();
pstmt.setString(2,empname);
System.out.println("enter photo file path");
String path=s.next();

File f=new File("c:/pho001.gif");

int size=(int) f.length();
FileInputStream fis=new FileInputStream(f);
pstmt.setBinaryStream(3,fis,size);
int i=pstmt.executeUpdate();
System.out.println(i+"row inserted");
pstmt.close();
fis.close();
}
public void closeCon()throws Exception
{
con.close();
}

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

{
PhotoInsert p1= new PhotoInsert();
p1.openCon();
p1.insert();
p1.closeCon();
}
}
Retrieve Images from DataBase in JDBC
While retrieving a image from a database, the binary data of image will be selected from the
database.

Important Points
 Again we need to convert binary data to image, because in database binary data of image
is store.
 When we select image from database, it will be store in a ResultSet object..
 From ResultSet object we need to read the binary data and we need to store in a
InputStream object.

Convert binary data into image

To convert binary data into a image again we write the data into a file using FileOutputStream
object.

import java.sql.*;
import java.util.*;
import java.io.*;
class PhotoSelect
{
Connection con;
public void openCon()throws Exception
{
Class.forName("oracle.jdbc.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@rama-
pc:1521:xe","system","system");
System.out.println("connection is opened");
}
public void select()throws Exception
{
Scanner s=new Scanner(System.in);
PreparedStatement pstmt=con.prepareStatement("select photo from emp_info
where empid=?");
System.out.println("enter emp id");
int empid=s.nextInt();
pstmt.setInt(1,empid);
ResultSet rs=pstmt.executeQuery();
rs.next();
InputStream is=rs.getBinaryStream(1);
rs.close();
FileOutputStream fos=new FileOutputStream("c:/img001.gif");
int k;
while((k=is.read())!=-1)
{
fos.write(k);
}
System.out.println("picture is ready open c:drive");
pstmt.close();
fos.close();
}//end of
public void closeCon()throws Exception
{
con.close();
}//end of select

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

{
PhotoSelect ps= new PhotoSelect();
ps.openCon();
ps.select();
ps.closeCon();
}//end of main
}//end of cl