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

 

 

 

 

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);

}

 

}

}

 

 

 

• 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)

 

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);

}

}

}

 

 

 

• 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();

 

 

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);

}

}

 

 

• 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.

 

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();

}

 

}

 

 

 

 

• 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”);

 

 

 

}

 

}