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 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
Set of classes included with the java development.
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)
- 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
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();
- Interfaces:
- Statement
- ResultSet
- Connection
- PreparedStatement
- Class Name:
- Date
- DriverManager
- Download MySQL Connector.jar from mysql.com
- Paste the mysql connector.jar in the lib folder of source directory.
- DriverManager class is an Interface between user and driver.
- A Connection is the session between java application and database.
- Connection is the factory of Statements
Methods of Connection Interface
- Public Statement createStatement();
- Public void open();
- Public void commit();
- Public void close();
- 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.
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);
}
}
}
- 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);
- 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.
- 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”);
}
}