Java Database Connectivity(JDBC) Using MySql
ADVANCE JAVA PROGRAMING
Subject Code (3360701)
Presented By- Dattani Dhyey-136250307505
JDBC Two Tier Architecture
• Java Application talks
Application Space
directly to the database.
• Accomplished through the Java Application
JDBC driver which sends
commands directly to the
database. JDBC Driver
• Results sent back directly to SQL Result
the application Command Set
Database
JDBC Three Tier Architecture
• JDBC driver sends Application Space
commands to a middle
tier, which in turn sends Java Application
commands to database.
JDBC Driver
• Results are sent back to SQL Result
the middle tier, which Command Set
communicates them back Application Server
(middle-tier)
to the application
Proprietary
Protocol
Database
The JDBC API
The JDBC API stands for Java Database Connectivity Application Programming Interface. It
allows an application written in java to communicate and interacts with database.
It allows JAVA application to:
1) Create and open connection with database.
2) Specify and executes various SQL queries against database.
3) Retrieve records from database.
The JDBC API defines various classes and interfaces to communicate with database.
The JDBC classes are defined inside java.sql package.
JDBC Components
1) The java.sql package :
The java.sql package contains set of classes and interfaces that are used to
communicate with database.
Following are most common interfaces of java.sql package.
Interface Purpose
Driver Is used to create a connection object using connect()
method.
Connection Is used to monitor and maintain database sessions.
createStatement() method is used create statement.
Statement Is used to execute SQL statements and retrieve records
from database.
ResultSet Is used to retrieve records that are returned by
executing SQL query.
JDBC Components
Following are most common classes of java.sql package.
Class Purpose
DriverManager Is used to manage multiple drivers. And also used to
load and register the JDBC drivers and establish
connection with database. The getconnection() method
of DriverManager class is used to create connection
object.
SQLException This class handles any errors that occur in a database
application.
JDBC Components
2) JDBC Test Suite:
The JDBC driver test suite helps you to determine that JDBC drivers will run your program. These
tests are not comprehensive or exhaustive, but they do exercise many of the important features in
the JDBC API.
3) JDBC-ODBC Bridge :
The Java Software bridge provides JDBC access via ODBC drivers. Note that you need to load
ODBC binary code onto each client machine that uses this driver. As a result, the ODBC driver is
most appropriate on a corporate network where client installations are not a major problem, or for
application server code written in Java in a three-tier architecture.
JDBC-ODBC Bridge
Advantages Of JDBC.
o Can read any database.
o Creates XML structure of data from database.
o No content conversion
o Query and stored procedure supported.
Disadvantages Of JDBC.
o Not good for large project.
o It needs specific database queries.
o Multiple connections may have complexities
o Exception handling is a big issue with JDBC.
JDBC-ODBC Bridge
JDBC Drivers
JDBC Driver is a software component that enables java application to interact with the
database.
To help you understand what different drivers require, the following driver categorization
system id defined :-
o Type 1: JDBC-ODBC Bridge driver (Bridge).
o Type 2: Native-API/partly Java driver (Native).
o Type 3: All Java/Net-protocol driver (Middleware).
o Type 4: All Java/Native-protocol driver (Pure).
Type 2: Native-API, Partly Java Driver
• Native-API driver converts Application Space
JDBC commands into
DBMS-specific native calls Java Application
Type 2 JDBC Driver
• Directly interfaces with the
database SQL Result
Command Set
Native Database
Library
Proprietary
Protocol
Database
Type 4: Native-Protocol, Pure Java Driver
Pure Java drivers that Application Space
communicate directly with the
vendor’s database Java Application
JDBC commands converted to
database engine’s native protocol Type 4 JDBC Driver
directly
SQL Command Result Set
Advantage: no additional Using Proprietary Using Proprietary
Protocol Protocol
translation or middleware layer
Database
Improves performance
Creating Database
Step-1 : Import JAVA SQl statement.
o import.java.sql.*;
Step-2 : Load and Register JDBC driver.
o Syntax : Class.forName (“Driver Name”);
Step-3 : Establish Connection with Database.
o Syntax : Connection conn= DriverManager.getConnection (“URL”, “Username”, ”Password”);
Step-4 : Create Statement.
o Statement stmt = conn.createstatement();
Continued…..
Step-5 : Execute Query.
o ResultSet rs= stmt.executeQuery("SELECT * FROM STUDENT");
o stmt.executeUpdate("INSERT INTO STUDENT VALUES(7,'abc','Chennai')”);
Step-6 : Retrieve Results (applied for select query)
o while(rs.next())
{
int id = rs.getInt("enroll");
String name= rs.getString("name");
String city= rs.getString("city");
System.out.println(id+"\t\t");
System.out.println(name+"\t\t");
System.out.println(city+"\t\t");
}
Step-7 : Closing Connection and Statement.
o conn.close();
ostmt.close();
// Step-1 : Import java.sql package
import java.sql.*;
public class database
{
public static void main(String args[])
{
Connection conn= null;
Statement stmt= null;
try
{
//Step-2: Load and register the JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//Step-3 : Establish connection with Database.
System.out.println("Trying to connect with Database");
conn= DriverManager.getConnection("jdbc:mysql://localhost/","root","");
System.out.println("Connection Established Successfully");
//Step-4 : Create Statement.
System.out.println("Trying to create Database");
//Step-5 : Execute Query.
stmt=conn.createStatement();
String sql= "CREATE DATABASE jdemo";
stmt.executeUpdate(sql);
System.out.println("Database created successfully");
//Step-6: Close Connection.
conn.close();
stmt.close();
}
catch(SQLException se)
{
se.printStackTrace();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
Insertion Using PrepareStatement
import java.sql.*;
public class dbpreparestmt
{
public static void main(String args[])
{
Connection conn = null;
Statement stmt = null;
try
{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Trying to connect with Database");
conn=DriverManager.getConnection("jdbc:mysql://localhost/jdemo","root","");
System.out.println("Connection Established Successfully");
System.out.println("Trying to insert data in table");
stmt = conn.createStatement();
PreparedStatement pst=conn.prepareStatement("INSERT INTO dhyey
VALUES(?,?,?)");
pst.setInt(1,7057);
pst.setString(2,"raj");
pst.setString(3,"gujrat");
pst.executeUpdate();
System.out.println("Data inserted successfully");
conn.close();
stmt.close();
}
catch(SQLException se)
{
se.printStackTrace();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
