Java Database Connectivity
JDBC stands for Java Database Connectivity and has been developed by Sun
Microsystems.
It is a standard Java API that defines how the front-end application may access the
database.
JDBC applications are platform independent, and thus they can be used for connecting
with Internet applications.
JDBC applications are simpler and easier to develop when comparing to ODBC and they
ensure security.
JDBC API uses JDBC drivers in order to connect with the database.
JDBC API and JDBC driver form the important components in order to fetch/store the
information to the database.
JDBC API is installed at the client side
JDBC manager needs a medium to communicate with database
JDBC driver provides this medium.
Web/java JDBC API JDBC Driver JDBC Driver
application Manager
Database
JDBC Architecture:
JDBC API supports both two-tier and three-tier processing models for database access.
Two-tier Architecture for Data Access:
JDBC driver is required and it can establish direct communication with the database.
Java application Client Machine
JDBC
DBMS-Proprietary protocol
DBMS Database Server
Three-tier Architecture for Data Access:
User’s commands are first sent to the application server forming the middle tier.
Application server containing the JDBC API sends the SQL statements to the database located
on the database server.
The commands are processed and the result is sent to the middle tier, which then sends it to
the user.
Java program
Application server (Java)
JDBC
DBMS
Installing MySQL and MySQL Connector/J
1. [Link]
2. MySQL 5.6 is the latest production release
3. MySQL Server 5.6 setup Wizard
4. License agreement page
5. Click on install button
6. Finish
7. MySQL Server Instance Configuration Wizard would open up and choose server type as
Developer Machine
8. Select Multifunctional database for general purpose database.
9. Keep the default port as 3306 and specify the root password
SQL statements
1. Select statement
2. Insert statement
3. Where clause
4. Delete statement
JDBC Environment setup
How to set up the connection to MySQL database from NetBeans IDE.
NetBeans IDE supports MySQL RDBMS.
1. Open the NetBeans IDE, right click the database node in the services
window.
2. Select “Register MySQL Server”. It open dialog box.
3. Confirm that the server host name and port are correct. Default server host
name is localhost and 3306 is the default server port name.
4. Enter the administrator password or default or blank password.
5. At the top of dialog box, click on the Admin properties tab., enter the
path/URL(location of MYSQL Administration).
6. Next, you have to enter the path to start the command
7. If database server is not connected, it will show ‘disconnected’.
8. For connecting it, right click the database and choose connect.
9. When a new project is created in NetBeans, copy the mysql-connector/java
JAR file into the library folder.
JDBC Connectivity Model and API:
1. Driver Manager:
Methods:
Connection getConnection(String url)
Connection getConnection(String url, String username, String password)
2. Driver : It handles communication with the database server.
JDBC Driver
JDBC Driver is a software component that enables java application to interact with the database. There are
1. JDBC-ODBC bridge driver
2. Native-API driver (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)
5. Native- Protocol driver:
1) JDBC-ODBC bridge driver
The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBC-ODBC bridge driver co
JDBC method calls into the ODBC function calls.
In Java 8, the JDBC-ODBC Bridge has been removed.
Oracle does not support the JDBC-ODBC Bridge from Java 8. Oracle recommends that you
use JDBC drivers provided by the vendor of your database instead of the JDBC-ODBC Bridge.
Advantages:
o easy to use.
o can be easily connected to any database.
Disadvantages:
o Performance degraded because JDBC method call is converted into the ODBC
function calls.
o The ODBC driver needs to be installed on the client machine.
2) Native-API driver
The Native API driver uses the client-side libraries of the database.
The driver converts JDBC method calls into native calls of the database API. It is not written entirely in java.
Advantage:
o performance upgraded than JDBC-ODBC bridge driver.
Disadvantage:
o The Native driver needs to be installed on the each client machine.
o The Vendor client library needs to be installed on client machine.
3) Network Protocol driver
The Network Protocol driver uses middleware (application server) that converts JDBC calls
directly or indirectly into the vendor-specific database protocol. It is fully written in java.
Advantage:
o No client side library is required because of application server that can perform many
tasks like auditing, load balancing, logging etc.
Disadvantages:
o Network support is required on client machine.
o Requires database-specific coding to be done in the middle tier.
o Maintenance of Network Protocol driver becomes costly because it requires database-
specific coding to be done in the middle tier.
4) Thin driver
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.
Advantage:
o Better performance than all other drivers.
o No software is required at client side or server side.
Disadvantage:
o Drivers depend on the Database.
Native- Protocol driver:
it is completely written in Java and thus it is platform
independent.
The driver converts JDBC calls directly into Vendor-specific
database protocol.
It provides better performance when compared to Type1 and 2
drivers.
There are two packages that makeup the JDBC API
1. [Link]
2. [Link]
Connection
Statement
ResultSet
SQL Exception
Establishing JDBC Database connections:
1. Load and register the JDBC driver
[Link](“driverclassname”);
2. Defining the Connection URL
String url=”jdbc:mysql://localhost:3306/studentdata”;
3. Establishing the connection:
String user=”root”;
String pass=”root”;
Connection con=[Link](url,user,pass);
4. Creating a statement object:
Statement st;
Try
{
St=[Link]();
…………………
……………………
} catch(SQLException e)
5. Closing statement object
[Link]();
6. Executing a query or update:
String sql=”select * from Ec_student”;
Rs=[Link](sql);
ResultSet Interface:
A ResultSet consists of records. Each record comprises a set of columns.
A ResultSet can be created by executing a statement or PreparedStatement as shown
Statement st;
St=[Link]();
// PreparedStatement
ResultSet rs=[Link](“select * from Ec_Student”);
PreparedStatement st=[Link](sql);
ResultSet rs=[Link]();
The next() method of ResultSet is used to move through the table one row at a time. It
maintains the cursor pointing to one particular row of data.
It returns true, If the ResultSet has a next record and it moves the ResultSet to point the
next record.
While([Link]())
………..
Methods:
1. Boolean next()
2. Int getInt(int columnIndex)
3. String getString(int columnIndex)
4. Int getInt(String columnName)
5. String getString(String columnName)
Ex: [Link](“ID”);
[Link](“FirstName”);
Statement st=[Link]();
String sql=”select * from tablename”;
ResultSet rs=[Link](sql)
[Link](2); //records of 2nd row is retrieved.
Navigating the ResultSet:
1. ResultSet.TYPE_FORWARD_ONLY
2. ResultSet.TYPE_SCROLL_INSENSITIVE
3. ResultSet.TYPE_SCROLL_SENSITIVE
Methods:
1. First()
2. Last()
3. Next()
4. Previous()
5. Relative()
6. Absolute()
7. Afterlast()
8. beforeFirst()
ResultSetMetaData Interface:
It provides methods to get metadata through the ResultSet object.
Methods:
1. String getColumnName(int index)
2. Int getColumnCount()
3. String getColumnTypeName(int index)
4. String getTableName(int index)