Home Discover Knowledge What is Java Database Connectivity? How to connect Java with MySQL ?...

What is Java Database Connectivity? How to connect Java with MySQL ? Advantages of JDBC.

0
1598
Introduction: Database Programming

A database is a repository of information managed by a database engine which ensures integrity of data and fast access to the data.

A very common implementation of a database is a Relational Database Management System (RDBM). To users, the information in a database can be accessed by using Structured Query Language (SQL) a database language common to most databases. However, SQL only provides commands to access and manipulate the data in the database.

JDBC (Java Database Connectivity)
  • JDBC is a Java-Based data access technology.
  • JDBC classes are contained in the java package java.sql and javax.sql
  • JDBC 4.2 – Java SE8

API (Application Programming Interface)

Set of classes included with the java development.

 

JDBC Driver:

 

Type 1: JDBC-ODBC bridge driver

Type 2: Native-API driver (partially java driver)

Type 3: Network protocol driver(fully java driver)

Type 4: Thin driver (fully java driver)

Service title
  • Two-Tier Architecture: -> Client and Server Architecture
  • No intermediate between client and server.

 

  • Three Tier Architecture: -> presentation tier(UI)
  • Business or Data Access(Validation + Calculation + Insertion)
  • Data Layer(DB,MySql)
  • Client Layer

 

3 Types of Layers:

1) Client Layer

2) Business Layer

3) Data Layer

Service title

 

Steps for Database Access

  • Firstly import the package java.sql
  • Register and load the driver:
  • forName(): It is the method of class which is used for registering and loading the driver

class.forName(“conn.mysql.jdbc.Driver”);

  • Creating the Connection Object:
  • Getconnection() method of DriverManager class is used to establish connection with the Database.

Connection conn=DriverManager.getConnection(“jdbc:mysql://localhost=3306/DB name”,”root”,”password”);

Where Connection is the interface and DriverManager is the class Name.

 

  • Creating Statement Object:

Statement stmt=conn.CreateStatement();

  • Object of Statement is responsible to execute queries to Database.

 

  • Execute Query:

ResultSet rs=Stmt.executeQuery(“Select * from emp”);

 

While(rs.next())               //Display the result

{

System.out.println(rs.getInt(1)+” “+rs.getString(2));

}

  • Executequery() method of Statement Interface is used to execute queries to the Database.

 

  • Closing Connection:

Conn.close();

Service title
  1. Interfaces:
  • Statement
  • ResultSet
  • Connection
  • PreparedStatement
  1. Class Name:
  • Date
  • DriverManager

Service title

 

  • Download MySQL Connector.jar from mysql.com
  • Paste the mysql connector.jar in the lib folder of source directory.

 

 

 

 

Service title

 

  • DriverManager class is an Interface between user and driver.
  • A Connection is the session between java application and database.

 

”Connection
  • Connection is the factory of Statements

Methods of Connection Interface

 

  • Public Statement createStatement();
  • Public void open();
  • Public void commit();
  • Public void close();

 

 

Service title
  • Statement is the factory of ResultSet

Methods of Statement Interface

  • Public ResultSet executeQuery(String sql):   It returns Object of ResultSet.
  • Public int executeUpdate(String sql): It executes specified query.
  • Public Boolean execute(String sql): It returns multiple results.

 

Example:

Statement stmt = conn.createStatement();

Int result = stmt.executeUpdate(“delete from table where id= xy”);

System.out.println(result + “records affected”);

Conn.close();

  • For DML operation , result is stored in Integer.
  • For Select , result is stored in ResultSet.

 

 

Service title

1) next()
2) getRow()
3) get<Type>()

 

Methods of ResultSet Interface:

  • Next()
  • Previous()
  • First()
  • Last()

 

 

Example:

ResultSet rs   = stmt.executeQuery(“Select * from Table”);

 

//getting record of 3rd row

 

rs.absolute(3);

System.out.println(“ “);

Con.close();

 

Example: Display Data from database

package JDBC;

 

import java.sql.Statement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

public class MysqlCon {

public static void main(String[] args) {

// TODO Auto-generated method stub

try

{

Class.forName(“com.mysql.cj.jdbc.Driver”);  //cj is a latest driver  //loading and registering the driver

Connection con=DriverManager.getConnection(“jdbc:mysql://localhost:3307/classicmodels”, “root”, “connection”);

Statement stmt=con.createStatement();

ResultSet rs=stmt.executeQuery(“Select * from employees”);  //Result Set is virtual table and result is stored in result set

while(rs.next())

{

System.out.println(rs.getInt(1)+ ” ” +rs.getString(2)+ ” ” + rs.getString(3) + ” ” + rs.getString(5));

 

}

con.close();

}

 

catch(Exception e)

{

System.out.println(e);

}

 

}

}

//Connection,Statement is an Interface

 

 

Example: Insert Data into database

 

package JDBC;

import java.sql.Statement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

 

public class InsertDemo {

 

public static void main(String[] args) {

// TODO Auto-generated method stub

 

Statement stmt;

ResultSet rs;

int cnt=0;

try

{

Class.forName(“com.mysql.cj.jdbc.Driver”);  //cj is a latest driver  //loading and registering the driver

Connection con=DriverManager.getConnection(“jdbc:mysql://localhost:3307/classicmodels”, “root”, “connection”);

String str=”insert into offices values(9,’Greater Noida’,’+91 650 219 3556′,’Knowledge Park’,’Utility Building A’,’KB’,’INDIA’,’56003′,’NA’)”;

stmt=con.createStatement();

int rowcount=stmt.executeUpdate(str);

if(rowcount>0)

{

System.out.println(“Record Inserted Successfully”);

}

String str1=”Select count(officeCode) from offices”;

rs=stmt.executeQuery(str1);

while(rs.next())

{

cnt=rs.getInt(1);

System.out.println(“Total no. of records is: “+cnt);

}

con.close();

 

}

 

catch(Exception e)

{

System.out.println(e);

}

}

}

 

 

Example: Delete Data from database

package JDBC;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

 

public class DeleteDemo {

public static void main(String[] args)

{

Statement stmt;

ResultSet rs;

int cnt=0;

try

{

Class.forName(“com.mysql.cj.jdbc.Driver”);  //cj is a latest driver  //loading and registering the driver

Connection con=DriverManager.getConnection(“jdbc:mysql://localhost:3307/classicmodels”, “root”, “connection”);

String sql=”Delete from offices where city=’Greater Noida’;”;

stmt=con.createStatement();

cnt=stmt.executeUpdate(sql);

if(cnt>0)

{

System.out.println(“Record Deleted”);

 

}

 

con.close();

}

catch(Exception e)

{

System.out.println(e);

}

 

}

}

 

 

Service title

 

  • Non-scrollable ResultSet in jdbc: It is a by default and works only in forward direction.
  • It cannot move randomly.
  • It gives n+1 iterations.

 

  • Scrollable ResultSet : It works in backward as well as Forward direction.
  • It can move randomly.

Example: Statement stmt = conn.CreateStatement(param1, param2);

 

CreateStatement(type,mode);

 

  1. Types:
    -ResultSet.TYPE_FORWARD_ONLY : default
    -ResultSet.TYPE_SCROLL.INSENSTIVE: ResultSet is not sensitive to changes made by others to the Database that occur after the resultset was created.

-ResultSet.TYPE_SCROLL.SENSITIVE: It is case sensitive.

 

  1. Mode:
  • CONCUR_READ_ONLY: It is for read only.
  • CONCUR_UPDATABLE: It is for update only.

 

Absolute: It is for specified row

Relative: It gives number of rows and it works in forward or backward direction.

Example: If we have 100 rows,

Relative(100) –it gives 110 record

 

PreparedStatement Interface

  • It is a sub interface of Statement.
  • It executes the parametrized Query.

 

String sql = insert into emp values(?,?,?);

Where ?- Dynamic Values

  • Value is given at the run time.
  • It improves the runtime Efficiency.
  • PreparedStatement Objects are faster than a Statement Objects.

  • Statement,Prepared Statement and Callable Statement are Interfaces.
  • It is for reading or writing binary data.
  • ‘?’ (Parameter or replacement operator or place_resolution operator).

 

 

Methods of PreparedStatement

  • Public void SetInt (int paramIndex , int value)
  • Public void getString(String paramIndex , String value)
  • Public void getFloat(String paramIndex , Float value)

 

Service title

Example: To make use of Prepared Statement()

 

package JDBC;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.PreparedStatement;

import java.util.Scanner;

public class UpdateDemo {

public static void main(String[] args) {

// TODO Auto-generated method stub

//User giving values at run time

PreparedStatement pstmt;

ResultSet rs;

int cnt=0;

try

{

Class.forName(“com.mysql.cj.jdbc.Driver”);  //cj is a latest driver  //loading and registering the driver

Connection con=DriverManager.getConnection(“jdbc:mysql://localhost:3307/classicmodels”, “root”, “connection”);

String sqlUpdate=”UPDATE employees ” + “SET jobTitle=?” + “WHERE employeeNumber = ?”;  //? – Parameter Operator

pstmt=con.prepareStatement(sqlUpdate);

Scanner s=new Scanner(System.in);

System.out.println(“Enter Employee ID:”);

int eid=s.nextInt();  //1702

s.nextLine();

System.out.println(“Enter new job Title”);

String strm=s.nextLine(); //Sales Manager

pstmt.setString(1, strm);

pstmt.setInt(2,eid);

int rowAffected=pstmt.executeUpdate();

System.out.println(“Row Affected” + rowAffected);

//reuse the Prepared Statement

strm=”Sales Head”;

eid=1370;

pstmt.setString(1, strm);  //using hard code values( because prepared statements are Pre-Compiled statements)

pstmt.setInt(2, eid);

rowAffected=pstmt.executeUpdate();

System.out.println(“Row Affected “+ rowAffected);

con.close();

}

catch(Exception e)

{

System.out.println(e);

}

}

}

 

Service title

 

 

  • To call all the functions and procedures of a database , Callable Statement Interface is used.
  • It is a derived Interface of Prepared Statement.
  • It calls the procedures and function of a Database.

 

Stored Procedures and Functions

 

  • It have business logic on the Database by the use of stored procedures and functions.

 

Stored Procedures Functions
1)     It used to perform business logic.

 

2)     It does not have the return type.

 

3)     It return zero or more values.

1)     It is used to perform calculation.

 

2)     It have the return type.

 

3)     It return only one value

 

Create Object of Callable Statement

 

  • prepareCall() : It is a method of Connection Interface and it returns the instance of Callable Statement.
  • Syntax of prepareCall() method

Public CallableStatement prepareCall( “ { call procedureName(? ? …?) }”  )

  • General Syntax of calling a Stored Procedure

{ ? = call procedure_name(param1 , param2 , …) }

 

 

Jdbc MySQL stored procedure example:

 

String query = “ { CALL get_candidate_skill(?) };

CallableStatement stmt =  conn.prepareCall(query)

Stmt_setIn(1 , candidateId);

 

ResultSet rs = stmt.executeQuery();

Service title

 

 

Example: To make use of Callable Statement()

package JDBC;

import java.sql.*;

public class CallableDemo1 {

Connection con;

CallableStatement cstmt;

ResultSet rs;

CallableDemo1() {     }

void createConnection() {

try {

Class.forName(“com.mysql.cj.jdbc.Driver”); // cj is a latest driver //loading and registering the driver

con = DriverManager.getConnection(“jdbc:mysql://localhost:3307/mysqljdbc”, “root”, “connection”);

}

catch (Exception e) {

System.out.println(e);

}

}

public void getSkills(int candidateId) {

String query = “{ call get_candidate_skill(?) }”;

try {

CallableStatement stmt = con.prepareCall(query);

stmt.setInt(1, candidateId);

rs = stmt.executeQuery();

while (rs.next()) {

System.out.println(String.format(“%s – %s”,rs.getString(“first_name”) + ” ” + rs.getString(“last_name”), rs.getString(“skill”)));

}

}

 

catch (SQLException ex) {

System.out.println(ex.getMessage());

}

}

 

public static void main(String[] args) {

// TODO Auto-generated method stub

 

CallableDemo1 cd = new CallableDemo1();

cd.createConnection();

cd.getSkills(133);

}

}

 

 

Service title
  • It execute a batch(group) of queries using batch processing.
  • sql.Statement and java.sql.PreparedStatement Interface is used to execute bacth processing.

 

Need: It helps in increasing the number of round trips.

Advantages: It increases the performance of an application.

Methods: 1) void addBatch(String query) : It adds query into batch.

Int []  executeBatch() : It executes batch of queries.

  • Only DML operation is allowed.
  • sql.BatchUpdate Exception is thrown.
Service title

 

Example: To make use of Batch Processing

 

package JDBC;

import java.sql.*;

public class BatchDemo {

Connection con;

Statement stmt;

ResultSet rs;

BatchDemo() {   }

void createConnection() {

try {

Class.forName(“com.mysql.cj.jdbc.Driver”); // cj is a latest driver //loading and registering the driver

con = DriverManager.getConnection(“jdbc:mysql://localhost:3307/mysqljdbc”, “root”, “connection”);

}

catch (Exception e) {

System.out.println(e);

}

}

public void createBatch()

{

try

{

stmt=con.createStatement();

stmt.addBatch(“insert into skills (name) values (‘Hybernate’)”);

stmt.addBatch(“Update skills Set name= ‘Web App’ Where id=4”);

stmt.addBatch(“Delete from skills where id=11 “);

//disable auto-commit mode

con.setAutoCommit(false);

}

catch(Exception e)

{

System.out.println(e);

}

 

 

try

{

int i[]= stmt.executeBatch();

con.commit();

System.out.println(“Batch is successfully executed”);

con.rollback();

}

 

catch(Exception e)

{

System.out.println(“batch is failed”);

 

}

 

}

 

public static void main(String[] args) {

// TODO Auto-generated method stub

 

BatchDemo bdObj =  new BatchDemo();

bdObj.createConnection();

bdObj.createBatch();

}

 

}

 

 

 

Service title

 

  • It is a group of operations used to perform the one task.
  • Transaction is successful when all three DML operations will be succeed,
  • Transaction is failed then it will be rollback.

Examples: Movie Ticket Booking

  • Verify the seats
  • Reserve the seats
  • Payment
  • Issue Tickets

 

Properties of Transaction Management:

A  -> Atomicity: All operations done successfully

C -> Consistency: Reliable Data

I ->  Isolation : Separation

D -> Durability: Long Life

 

 

 

 

 

 

 

Type of Transaction

  • Local Transaction: In interacts only with one database.
  • Global Transaction: It interacts with one or more database.

 

  • JDBC : Local Transaction
  • EJB or Spring Framework: Global Transaction

 

 

Things required for Transaction in jdbc

Step 1: Disable auto commit mode of JDBC.

Step 2: Put all operation of a transaction in try block.

Step 3: If all operation are done successfully, commit in try block otherwise rollback in catch block.

 

Example:

package JDBC;

import java.sql.*;

public class DemoTransaction {

 

// TODO Auto-generated method stub

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

{

Connection con;

Class.forName(“com.mysql.cj.jdbc.Driver”); // cj is a latest driver //loading and registering the driver

con = DriverManager.getConnection(“jdbc:mysql://localhost:3307/mysqljdbc”, “root”, “connection”);

 

System.out.println(“driver is loaded”);

 

Statement stmt = con.createStatement();

 

con.setAutoCommit(false);

//DML OPERATIONS

 

try

 

{

 

int i1 = stmt.executeUpdate(“Insert into candidate_skills values(100,3)”);

 

int i2= stmt.executeUpdate(“Update skills set name= ‘Ruby’ where id=11”);

 

int i3= stmt.executeUpdate(“Delete from candidates where id=120”);

 

con.commit();

 

System.out.println(“Transaction is Successfull!!”);

}

 

catch(Exception e)

{

try

{

con.rollback();

System.out.println(“Transaction is Failed”);

}

 

catch(Exception ex)

{

 

System.out.println(ex);

}

}//end of catch

 

stmt.close();

con.close();

System.out.println(“Connection is closed”);

 

 

 

}

 

}

 

Service title

NO COMMENTS