Java JDBC (Java Database Connectivity)

JDBC stands for Java Database Connectivity. It is a Java API to make connection and execution of the query with the database. It comes from JavaSE (Java Standard Edition). JDBC API uses JDBC drivers to connect with the database.

Technology:- The specification (High-level API ) is provided by SUN (Java Vendor) JDBC, Servlets, JSP, EJB, etc.

Framework:- The specification (High-level API) is provided by third party vendor struts, Hibernate, Spring, etc.

If you are developing the application using any UI and you want to store the data in the DB then JDBC will be used.

In Java.sql package, Sun has provided interfaces and classes that will be used to interact the Java Application with Database.
These methods are throwing java.sql.SQL.Execution(checked exceptions).
The JDBC Specification has implemented by various vendors

  • SUN (Java Vendor)
  • DB Vendor

Steps to writing JDBC Application:-

  1. Loading the Driver/Registering the Driver with JVM
  2. Creating the connection with Java App and Database
  3. Prepare the SQL Statement(Query)
  4. Prepare the JDBC Statement
  5. Using JDBC Statement submit the SQL Statement to DB
  6. Process the response
  7. Close/Release the resources(Connection, Statement, ResultSet etc)

List of JDBC Version

  • J2SE
  • JDBC 3.0
  • JSE5
  • JSE6
  • JSE7
  • JDBC 4.0
  • JDBC 4.1

Types of JDBC Driver

  • Type I Driver – JDBC ODBC Bridge Driver
  • Type II Driver – Partial Native and Java Driver
  • Type III Driver – Net protocol Driver
  • Type IV Driver- Pure Java Driver

Type I Driver

Name: JDBC ODBC Bridge Driver
Vendor: SUN (Java Vendor)
Driver Class: sun.jdbc.odbc.JbdcOdbcDriver
URL: jdbc:odbc:<DSN>
UserName: <dbUsername>
Password: <dbPassword>
PATH: bin directory of JDK
CLASSPATH: lib directory of JDK/ JRE software: JDK, DB

Note:- Type I driver uses ODBC to interact with DB.
ODBC- Open Database Connectivity
DSN- Data Source Name
DSN is the configuration name in ODBC.

Steps to configure DSN (Data Source Name) for Oracle in Windows Operating System

  1. Open Control Panel
  2. Open Administrative Tools
  3. Open Data Source (ODBC)
  4. Click on Add button under User DSN tab
  5. Select the Driver Name from the list (Oracle in XE)
  6. Click on Finish
  7. Provide the following information
    Data Source Name – b13oracledsn
    TNS Service Name- XE (Select from List)
    User Id: system (username in DB)
  8. To test the connection click on Test Connection
    Provide the Password
    Click on OK
  9. Click on the Ok button on the Configuration Window
  10. Click on Ok Button of ODBC Administrator Window

Table Required:
Create table b13stud( sid int, name varchar(15), email varchar(50), phone long, fee float);

Driver1.java

package com.xadmin.b13.jdbc;
import java.sql.*;
public class Xadmin
{
public static void main(String [] args)
{
Connection con=null;
Statement st=null;
try{
// 1. Loading the Driver ClassClass.forName(“sun.jdbc.odbc.jdbcOdbcDriver”);
// 2. Create the Connectioncon= DriverManager.getConnection(“jdbc:odbc:b13oracledsn”, “system”, “ashish”);
// 3. Prepare SQL StatementString sql=”insert into b13stud values(1,’ashish’, ‘aashish2012@gmail.com’, 0223434343,16000.0)”;
// 4. Create the JDBC Statementst= con.createStatement();
// 5. Submit the query to Database int res=st.executeUpdate(sql);
// 6. Process the Result System.out.println(“Result:” +res);
if(res>=1)
{
System.out.println(“Record inserted”);
}
else
{
System.out.println(“Record not Inserted”);
}
}
catch(ClassNotFoundException e)
{
System.out.println(“Driver class not found”);
}
catch(SQLException e){e.printStackTreace();
}
finally {
// 7. Close /Release the resources
try
{
if(st !=null) st.close();
}
catch(Exceptuion e)
{
e.printStackTrace();
}
}
}
}

DriverManager

  • getConnection(String url, String name, String password)
  • getConnection(String url, Properties p)
  • getConnection(String url)

Using JDBC-ODBC Driver for MySQL
 Create the Database & Table in MySQL
 Create the table in your database.

Steps to Configure DNS (Data Source Name) for MySQL:-

  1. After selecting the add button in ODBC configuration
  2. Select the driver name from the list (MySQL ODBC 5.1 Driver)
  3. Click on finish
  4. Provide the following information
    Data Source Name-> b13mysql
    TCP/IP Server -> localhost
    User: root(username in DB)
    Password: enter the password of DB
    Select the database from the list

    5. To test the connection click on Test

    6. Click on the Ok button on the configuration windows.

    7. Click on the Ok button of the ODBC Administrator window.

Changes Required in Java Code for MySQL:
con=DriverManager.getConnection(“jdbc:odbc:b13mysqldsn”, “root”, “password”);

Note:- In the case of Type 1 – Sun has implemented Driver class and subclasses for all the interfaces in java.sql package.
Following are the subclasses of the Connection interface and Statement interface.

  1. Sun.jdbc.odbc.JdbcOdbcDriver
  2. Sun.jdbc.odbc.JdbcOdbcConnection
  3. Sun.jdbc.odbc.JdbcOdbcStatement

Limitation:
ODBC was implemented by Microsoft with C language and is available only in Windows OS.
It is Platform dependent.

Type 2 Driver:

  1. Name: Partial native partial java driver
  2. Vendor: DB Vendor
  3. Software: Oracle client-server edition
  4. Driver class: oracle.jdbc.driver.OracleDriver
  5. URL: jdbc:oracle:oci8:@hostname:portno:serviceName
  6. Username: DB username
  7. Password: DB password

Type II driver solves the limitation of Type 1 driver by removing ODBC.

Limitation:
The library is implemented by DB vendor so it database dependent and implemented in the native language so it is platform dependent.
DB Related client libraries must be installed in the client machine. All DB may not be available with client-server edition. If you want to change the DB then it will give Maintenance problem.

Type 3 Driver:

  1. Name: Net-protocol driver
  2. Vendor: IDS software vendor
  3. Software: IDS software
  4. Driver class: com.ids.Driver
  5. URL: jdbc:ids://localhost/dbname
  6. Username: DB username
  7. Password: DB password

Limitation:
You need to maintain the IDS server

Type IV Driver

  1. Name: Pure JAVA Driver
  2. Vendor: DB Vendor
  3. Software: SE or Enterprise Edition
  4. Username: DB username
  5. Password: DB password

For Oracle:
Driver class: oracle.jdbc.driver.OracleDriver
Url : jdbc:oracle:thin:@<host>:<portNumber>:<serviceName>
Example:
jdbc:oracle:thin:@localhost:1521:XE

For MySQL:
Driver class: com.mysql.jdbc.Driver
Url: jdbc:mysql://<host>:<portNumber>/<dbName>
Example:
jdbc:mysql://localhost:3333/acb13db

Example program:-

package com.xadmin.b13.jdbc;
import java.sql.*;
public class xadmin
{
public static void main(String[] args)
{
Connection con=null;
Statement st= null;
ResultSet rs= null;
try
{
// Class.forName(“oracle.jdbc.driver.OracleDriver”);
// con= DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:XE”, “system”,”Ashish”);

Class.forName(“com.mysql.jdbc.Driver”);
con= DriverManager.getConnection(“jdbc:mysql://localhost:3333/acb13db”,”root”, “Ashish”);

String sql= “insert into b13stud values(14, ‘Ashish’, ‘aashish2012@gmail.com’, 6526668, 14000.0)”;

st=con.createStatement();
int res=st.executeUpdate(sql);
System.out.println(“Result: “+res);
if(res > =1)
{
System.out.println(“Record inserted”);
}
else
{
System.out.println(“Record not inserted”);
}
rs=st.executedQuery(“select * from b13stud”);
while(res.next())
{
int id=es.getInt(“sid”);
String nm=rs.getString(2);
String nm=rs.getString(“email”);
long phone=rs.getLong(4);
float fee=rs.getFloat(5);
}
}
catch(ClassNotFoundException e)
{
System.out.println(“Driver class not Found”);
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(st!=null) st.close();
if(con!=null) con.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
}

Conclusion:

JDBC is one of Java’s oldest APIs, providing an easy-to-use solution for the development of Java application. Knowing just a few JDBC calls ways in this article will get you started using JDBC to connect with any of two databases i.e, MySQL or Oracle. In this article, we learned regarding all the four JDBC drivers and their respective limitations with program example for you to understand better. we also learned to configure DNS in both MySQL and Oracle database in the Windows Operating System. In the next article, we are going to learn regarding the JDBC Statements and there uses with examples. So stay tuned!
Please feel free to drop a comment in the below comment box, if you have any doubt regarding the topic or you want to share more information about the topic. Happy Learning!

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *