• The JDBC (Java Database Connectivity) API helps a Java program to access a database in a standard way

• JDBC is a specification that tells the database vendors how to write a driver program to interface Java programs with their database

• A Driver written according to this standard is called the JDBC Driver

• All related classes and interfaces are present in the java.sql package

• All JDBC Drivers implement the interfaces of java.sql

JDBC Drivers

There are 4 types of drivers --Type1, Type2, Type3,Type4









Database interaction
The steps involved in a database interaction are:
– Loading the specific driver
– Making a connection to the database
– Sending SQL statements to the database
– Processing the results


Statement

A statement object is used to send SQL statements to a database.
Three kinds :
– Statement
– Execute simple SQL without parameters
– PreparedStatement
– Used for pre-compiled SQL statements with or without parameters
– CallableStatement
– Execute a call to a database stored procedure or function

JDBC - classes and interfaces

DriverManager class -
– Manages all the JDBC Drivers that are loaded in the memory
– Helps in dynamic loading of Drivers

Methods in DriverManager class -
– getConnection() : to establish a connection to a database.
• Connection getConnection(String url, Properties info)
• Connection getConnection(String url)
• Connection getConnection(String url, String userID, String password)
– registerDriver(java.sql.Driver)

Connection interface - defines methods for interacting with the database
via the established connection.
– A connection object represents a connection with a database.
– A connection session includes the SQL statements that are executed and the
results that are returned over that connection.
– A single application can have one or more connections with a single database,
or it can have many connections with many different databases.

The different methods of Connection interface are:
– close() - closes the database connection
– createStatement() - creates an SQL Statement object
– prepareStatement() - creates an SQL PreparedStatement object.
(PreparedStatement objects are precompiled SQL statements)
– prepareCall() - creates an SQL CallableStatement object using an SQL string.
(CallableStatement objects are SQL stored procedure call statements)

Statement interface - defines methods that are used to interact with
database via the execution of SQL statements.
The different methods are:
– executeQuery(String sql) - executes an SQL statement (SELECT) that queries
a database and returns a ResultSet object.
– executeUpdate(String sql) - executes an SQL statement (INSERT,UPDATE,or
DELETE) that updates the database and returns an int, the row count associated
with the SQL statement
– execute(String sql) - executes an SQL statement that is written as String object
– getResultSet() - used to retrieve the ResultSet object

ResultSet Interface - maintains a pointer to a row within the tabular results.
The next() method is used to successively step through the rows of the
tabular results.
The different methods are:
– getBoolean(int) - Get the value of a column in the current row as a Java
boolean.
– getByte(int) - Get the value of a column in the current row as a Java byte.
– getDouble(int) - Get the value of a column in the current row as a Java double.
– getInt(int) - Get the value of a column in the current row as a Java int.

Using Statement and ResultSet

import java.sql.*;
class JDBCTest{
public static void main(String args[]) {
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@DB,
IPaddress:port_no:host string",“uid",“password");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from
Student");
while(resultSet.next()){
System.out.println(resultSet.getInt("ClassNo"));
}
}
catch(Exception exception) {
System.out.println(exception)
}
}
}

import java.sql.*;
class PreparedStatementTest{
public static void main(String args[]) throws Exception{
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = DriverManager.getConnection(“url",
“UID", “password");
PreparedStatement preparedStatement =
connection.prepareStatement("select * from Emp where ename=?");
preparedStatement.setString(1, 7521);
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
System.out.println(resultSet.getString("ename"));
}
}
catch(Exception exception){
System.out.println(exception);
}
}
}

CallableStatement callableStatement =
connection.prepareCall("begin GetSalary(?,?); end;");
callableStatement.setInt(1,29418);
// OUT parameters must be registered.
callableStatement.registerOutParameter(2,Types.DOUBLE);
callableStatement.execute();
System.out.println("Salary : " + callableStatement.getDouble(2));

...
connection.setAutoCommit(false); //by default it is true
try{
//Statements
connection.commit();
}
catch(Exception exception){
connection.rollback();
}