JDBC
Pre-Requisites:
· Core JAVA
· MySQL
What is JDBC?
JDBC - Java Database Connectivity
JDBC is a standard Java API for database-independent connectivity
JDBC connects the Java programming language and databases.
JDBC is a specification for access to an underlying database
JDBC library includes APIs for:
· Connection to a database.
· Creating SQL statements.
· Executing SQL queries in the database.
· Viewing & changing the records.
All the below executables, for using a JDBC driver to access a database and retrieve data:
· Java Applications
· Java Applets
· Java Servlets
· Java ServerPages (JSPs)
· Enterprise JavaBeans (EJBs).
Architecture:
JDBC API supports
two-tier
three-tier
· JDBC API: for application-to-JDBC Manager connection.
· JDBC Driver API: for JDBC Manager-to-Driver Connection.
Diagram
The JDBC API provides the following interfaces and classes −
· DriverManager: (class):
Manages multiple database drivers.
Matches java application connection requests with correct database driver using sub protocol communication.
· Driver: (interface):
To handle communications the DB server.
· Connection: (Interface)
Connection object for communication context,
Communicating database through connection object only.
· Statement: (interface)
To submit the SQL statements to the database.
· ResultSet:
To hold data retrieved from a database executed SQL query.
It as an iterator to allow you to move through its data.
· SQLException: (class)
Handles DB application errors.
The JDBC 4.0 Packages
java.sql
javax.sql
New features impact:
· Automatic database driver loading.
· Exception handling improvements.
· Enhanced BLOB/CLOB functionality.
· Connection and statement interface enhancements.
· National character set support.
· SQL ROWID access.
· SQL 2003 XML data type support.
· Annotations.
Structured Query Language (SQL)
Allows to perform operations on a database
creating entries,
reading content,
updating content, and
deleting entries.
Create Database
Example
SQL> CREATE DATABASE EMP;
Create Table
SQL> CREATE TABLE Emp
(
id INT NOT NULL,
age INT NOT NULL,
first VARCHAR(255),
last VARCHAR(255),
PRIMARY KEY ( id )
);
Creating JDBC Application steps
· Import the packages:
import java.sql.*
· Register the JDBC driver:
For initializing a driver to open a communication channel with the database.
· Open a connection:
DriverManager.getConnection() method for creating a Connection object physical connection with the database.
· Execute a query: Requires using an object of type Statement for building and submitting an SQL statement to the database.
· Extract data from result set:
ResultSet.getXXX() method for retrieving the data from the result set.
· Clean up the environment:
explicitly database resources closing (or depends on JVM's garbage collection).
Sample Code: FirstExample.java
//STEP 1. Import required packages
package com.example;
import java.sql.*;
public class FirstExample{
public static void main(String[] args) {
System.out.println("Inserting values in Mysql database table!");
Connection con = null;
//String url = "JDBC:mysql://localhost:3306/";
//String db = "EMP";
//String driver = "com.mysql.JDBC.Driver";
//Note: com.mysql.cj.JDBC.Driver will be used in case of exception in newer version
try{
Class.forName("com.mysql.JDBC.Driver");
con = DriverManager.getConnection("JDBC:mysql://localhost:3306/EMP","root","");
try{
Statement st = con.createStatement();
int val = st.executeUpdate("insert into emp (id, age, first, last) VALUES(1, 27, 'abc', 'xyz')");
System.out.println(val + " rows affected");
}
catch (SQLException s){
System.out.println(s+"SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
To connect java application with the mysql database, mysqlconnector jar file is required to be loaded.
JDBC - Driver Types
Uses Defined interfaces in the JDBC API for interacting with database.
JDBC driver implementations vary considering operating systems and hardware configurations where Java operates.
4 Types of Drivers:
Type 1: JDBC-ODBC Bridge Driver
Used to access ODBC drivers installed on individual client machine.
ODBC need configuring Data Source Name (DSN) that represents the target database.
Most databases only supported ODBC access but now this type of driver is obsolete and only used for experimental (when no other available alternatives).
Advantages
The JDBC-ODBC Bridge allows access to almost any database since the database’s ODBC drivers are already available.
Disadvantages
1. Since the Bridge driver is not written fully in Java, Type 1 drivers are not portable.
2. A performance issue is seen as a JDBC call goes through the bridge to the ODBC driver, then to the database, and this applies even in the reverse process. They are the slowest of all driver types.
3. The client system requires the ODBC Installation to use the driver.
4. Not good for the Web.
Type 2: JDBC-Native API
• This type of driver converts JDBC class to the client API for the RDBMS servers.
• We should have database client API installed at the machine from which we want to make database connection.
• Because of extra dependency on database client API drivers, this is also not preferred driver.
Advantages
The distinctive characteristic of type 2 JDBC drivers are that they are typically offer better performance than the JDBC-ODBC Bridge as the layers of communication (tiers) are less than that of Type 1 and it uses Native API which is Database specific.
Disadvantages
1. Native API must be installed in the Client System and hence type 2 drivers cannot be used for the Internet.
2. Like Type 1 drivers, it is not written in Java Language which forms a portability issue.
3. If we change the Database, we must change the native API as it is specific to a database
4. Mostly obsolete now
5. Usually not thread safe.
Type 3: JDBC-Net pure Java
• This type of driver sends the JDBC calls to a middleware server that can connect to different type of databases.
• We should have a middleware server installed to work with this kind of driver.
• This adds to extra network calls and slow performance.
• Hence this is also not widely used JDBC driver.
• This driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server.
• This net server middleware can connect all its Java technology-based clients to many different databases.
• The specific protocol used depends on the vendor.
• If vender changes then application will not work.
• Because of extra dependency on database client API drivers, this is also not preferred driver.
Advantage
1. This driver is server-based, so there is no need for any vendor database library to be present on client machines.
2. This driver is fully written in Java and hence Portable. It is suitable for the web.
3. There are many opportunities to optimize portability, performance, and scalability.
4. The net protocol can be designed to make the client JDBC driver very small and fast to load.
5. The type 3 driver typically provides support for features such as caching (connections, query results, and so on), load balancing, and advanced
system administration such as logging and auditing.
6. This driver is very flexible allows access to multiple databases using one driver.
7. They are the most efficient amongst all driver types.
Disadvantage
It requires another server application to install and maintain. Traversing the recordset may take longer, since the data comes through the backend server.
Type 4: 100% Pure Java
• This is the preferred driver because it converts the JDBC calls to the network protocol understood by the database server.
• This solution does not require any extra APIs at the client side and suitable for database connectivity over the network.
• However, for this solution, we should use database specific drivers, for example OJDBC jars provided by Oracle for Oracle DB and MySQL Connector/J for MySQL databases.
Advantages
1. The major benefit of using a type 4 JDBC drivers are that they are completely written in Java to achieve platform independence and eliminate deployment administration issues.
It is most suitable for the web.
2. Number of translation layers is very less i.e., type 4 JDBC drivers do not have to translate database requests to ODBC or a native connectivity interface or to pass the request on to another server, performance is typically quite good.
3. You do not need to install special software on the client or server. Further, these drivers can be downloaded dynamically.
Disadvantages
With type 4 drivers, the user needs a different driver for each database.
Which Driver to Use?
one type of database – preferred driver type is 4.
multiple types of databases at the same time – type 3 is the preferred.
Type 2 useful where type 3 or type 4 driver is not available.
Type 1 driver is not for deployment-level (only for development & testing purposes).
Database Connections
After installing the driver, establish a database connection using JDBC.
1 Import JDBC Packages: Add import statements.
2 Register JDBC Driver: JVM loads the required driver implementation into memory to fulfill JDBC requests.
3 Database URL Formulation: For creating formatted address of database for connection.
4 Create Connection Object: call to the DriverManager object's getConnection() method for establishing database connection.
Import JDBC Packages
import java.sql.* ;
// for standard JDBC programs
import java.math.* ;
// for BigDecimal and BigInteger support
Register JDBC Driver
Register the driver in program before usage.
This process by which driver's class file is loaded into memory.
Two ways:
1 - Class.forName()
To dynamically load the driver's class file into memory, which implicitly registers it.
This allows to make the configurable driver registration & portable.
try {
Class.forName("com.mysql.JDBC.Driver");
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
Use getInstance() method to work around two extra Exceptions
try {
Class.forName("oracle.JDBC.driver.OracleDriver").newInstance();
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
catch(IllegalAccessException ex) {
System.out.println("Error: access problem while loading!");
System.exit(2);
catch(InstantiationException ex) {
System.out.println("Error: unable to instantiate driver!");
System.exit(3);
}
2 - DriverManager.registerDriver()
use of static DriverManager.registerDriver()
use the registerDriver() method for non-JDK compliant JVM
try {
Class.forName("com.mysql.JDBC.Driver");
con = DriverManager.getConnection("JDBC:mysql://localhost:3306/EMP","root","");
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
Database URL Formulation
After loading the driver, establish connection using DriverManager.getConnection() method.
Three overloaded DriverManager.getConnection() methods −
· getConnection(String url)
· getConnection(String url, Properties prop)
· getConnection(String url, String user, String password)
Database URL is address which refers database.
JDBC driver name and database URL.
URL format of MySQL
com.mysql.JDBC.DriverJDBC:mysql://hostname/databaseName
Create Connection Object
Three forms of DriverManager.getConnection() method to create a connection object.
1 Using a Database URL with a username and password
String url = "JDBC:mysql://localhost:3306/";
String db = "EMP";
String driver = "com.mysql.JDBC.Driver";
Connection conn = DriverManager.getConnection(URL, USER, PASS);
2 Using Only a Database URL
DriverManager.getConnection(String url);
JDBC:oracle:driver:username/password@database
Class.forName("com.mysql.JDBC.Driver");
con = DriverManager.getConnection("JDBC:mysql://localhost:3306/EMP","root","");
Using a Database URL and a Properties Object
DriverManager.getConnection(String url, Properties info);
Properties object contains a set of keyword-value pairs.
It is used to pass driver properties to the driver during a call to the getConnection() method.
import java.util.*;
String URL = " JDBC:mysql://localhost:3306/";
Properties info = new Properties( );
info.put( "user", "username" );
info.put( "password", "password" );
Connection conn = DriverManager.getConnection(URL, info);
Closing JDBC Connections
At the end required explicitly to close all the connections.
Note: Java's garbage collector will close the connection.
To ensure connection is closed, write in 'finally' block.
finally{
conn.close();
}
DriverManager class:
The DriverManager class comes from java.sql package (part of API – represents classes and interfaces which for each other communication).
The DriverManager class provides communication between users and drivers.
DriverManager class maintains drivers used by connection established and DB and the driver used.
DriverManager class includes methods for registering and deregistering the DB driver.
DriverManager class uses method DriverManager.registerDriver() to identify the loaded driver.
Exception is thrown in case registering the driver is not done before interacting with DB.
Methods:
1) public static synchronized void registerDriver(Driver driver)
2) public static synchronized void deregisterDriver(Driver driver)
3) public static Connection getConnection(String url) throws SQLException
4) public static Connection getConnection(String url,String userName,String password)
5) public static Driver getDriver(String url)
6) pubic static int getLoginTimeout()
7) pubic static void setLoginTimeout(int sec)
8) public static Connection getConnection(String URL, Properties prop)
Connection
Connection interface is a session.
Connection interface connects between Java application and a database.
Connection interface consists of Statement, PreparedStatement, and DatabaseMetaData
Connection interface provides methods transact such as commit(), rollback(), setAutoCommit()
Methods
1) public Statement createStatement()
2) public Statement createStatement(int resultSetType,int resultSetConcurrency) Video
3) public void setAutoCommit(boolean status)
4) public void commit()
5) public void rollback()
6) public void close()
Statements
Methods of statement interface can be used to execute the queries.
The result will be stored in ResultSet
interfaces: for defining methods and properties which enables to send SQL or PL/SQL commands & receive database data.
1) public ResultSet executeQuery(String sql):
2) public int executeUpdate(String sql)
3) public boolean execute(String sql)
4) public int[] executeBatch()
Example on Statement Interface for inserting the data:
package com.example;
import java.sql.*;
public class FirstExample{
public static void main(String[] args) {
System.out.println("Inserting values in Mysql database table!");
Connection con = null;
//String url = "JDBC:mysql://localhost:3306/";
//String db = "EMP";
//String driver = "com.mysql.JDBC.Driver";
try{
Class.forName("com.mysql.JDBC.Driver");
con = DriverManager.getConnection("JDBC:mysql://localhost:3306/EMP","root","");
try{
Statement st = con.createStatement();
int val = st.executeUpdate("insert into emp (id, age, first, last) VALUES(1, 27, 'abc', 'xyz')");
System.out.println(val + " rows affected");
}
catch (SQLException s){
System.out.println(s+"SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
Example on Statement interface for selecting the data:
package com.example;
import java.sql.*;
public class FirstExample{
public static void main(String[] args) {
System.out.println("Inserting values in Mysql database table!");
Connection con = null;
//String url = "JDBC:mysql://localhost:3306/";
//String db = "EMP";
//String driver = "com.mysql.JDBC.Driver";
try{
Class.forName("com.mysql.JDBC.Driver");
con = DriverManager.getConnection("JDBC:mysql://localhost:3306/EMP","root","");
try{
con.setAutoCommit(false);
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getInt(2)+" "+rs.getString(3)+" "+rs.getString(4));
con.close();
}catch(SQLException se){
con.rollback();
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
Example Statement interface for delete record:
Statement stmt=con.createStatement();
int result=stmt.executeUpdate("delete from emp765 where id=33");
System.out.println(result+" records affected");
con.close();
ResultSet
object of ResultSet points cursor to a row of a table (before first row by default).
ResultSet interface object moves forward only and not updatable (by default).
To move forward and backward direction use TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE in createStatement(int,int)
Example
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
Methods:
public boolean next()
public boolean previous()
public boolean first()
public boolean last()
public int getInt(String columnName)
public String getString(String columnName)
PreparedStatement
To use the SQL statements many times.
PreparedStatement interface accepts input parameters at runtime.
To execute parameterized query.
String sql="insert into emp values(?,?,?)";
Needs to pass values for parameters (?) by calling the setxxx() methods
try{
Class.forName("com.mysql.JDBC.Driver");
con = DriverManager.getConnection("JDBC:mysql://localhost:3306/EMP","root","");
try{
con.setAutoCommit(false);
PreparedStatement stmt=con.prepareStatement("insert into emp values(?,?,?,?)");
stmt.setInt(1,101);
stmt.setInt(2,27);
stmt.setString(3,"abcd");
stmt.setString(4,"xyz");
int i=stmt.executeUpdate();
System.out.println(i+" records inserted");
}catch(SQLException se){
// If there is any error.
con.rollback();
}
}
catch (Exception e){
e.printStackTrace();
}
Updating the records from java application
try{
Class.forName("com.mysql.JDBC.Driver");
con = DriverManager.getConnection("JDBC:mysql://localhost:3306/EMP","root","");
try{
con.setAutoCommit(false);
PreparedStatement stmt=con.prepareStatement("update emp set first=? where id=?");
stmt.setString(1,"vishwa");
stmt.setInt(2,101);
int i=stmt.executeUpdate();
System.out.println(i+" records updated");
con.commit();
}catch(SQLException se){
// If there is any error.
con.rollback();
}
}
Output:
1 records updated
Example on PreparedStatement statement
try{
Class.forName("com.mysql.JDBC.Driver");
con = DriverManager.getConnection("JDBC:mysql://localhost:3306/EMP","root","");
try{
con.setAutoCommit(false);
PreparedStatement stmt=con.prepareStatement("delete from emp where id=?");
stmt.setInt(1,101);
int i=stmt.executeUpdate();
System.out.println(i+" records deleted");
}
catch(SQLException se){
con.rollback();
}
}
catch (Exception e){
e.printStackTrace();
}
Output:
1 records deleted
ResultSetMetaData interface
PreparedStatement ps=con.prepareStatement("select * from emp");
ResultSet rs=ps.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
System.out.println("Total columns: "+rsmd.getColumnCount());
System.out.println("Column Name of 1st column: "+rsmd.getColumnName(1));
System.out.println("Column Type Name of 1st column: "+rsmd.getColumnTypeName(1));
Output:
Inserting values in Mysql database table!
Total columns: 4
Column Name of 1st column: id
Column Type Name of 1st column: INT
DatabaseMetaData interface
DatabaseMetaData dbmd=con.getMetaData();
System.out.println("Driver Name: "+dbmd.getDriverName());
System.out.println("Driver Version: "+dbmd.getDriverVersion());
System.out.println("UserName: "+dbmd.getUserName());
System.out.println("Database Product Name: "+dbmd.getDatabaseProductName());
System.out.println("Database Product Version: "+dbmd.getDatabaseProductVersion());
To get how many tables in the database:
DatabaseMetaData dbmd=con.getMetaData();
String table[]={"TABLE"};
ResultSet rs=dbmd.getTables(null,null,null,table);
while(rs.next()){
System.out.println(rs.getString(3));
}
Working with Images
Alter TABLE emp add "PHOTO" BLOB ; // LONGBLOB
package com.example;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
public class FirstExample{
public static void main(String[] args) {
System.out.println("Inserting values in Mysql database table!");
Connection con = null;
//String url = "JDBC:mysql://localhost:3306/";
//String db = "EMP";
//String driver = "com.mysql.JDBC.Driver";
try{
Class.forName("com.mysql.JDBC.Driver");
con = DriverManager.getConnection("JDBC:mysql://localhost:3306/EMP","root","");
try{
con.setAutoCommit(false);
File file= new File("D:\\abc.png");
FileInputStream inputStream= new FileInputStream(file);
PreparedStatement ps=con.prepareStatement("insert into emp values(?,?,?,?,?)");
ps.setInt(1,110);
ps.setInt(2,40);
ps.setString(3,"Anu");
ps.setString(4,"S");
ps.setBlob(5, inputStream);
int i=ps.executeUpdate();
System.out.println(i+" records affected");
con.commit();
}
catch(SQLException se){
con.rollback();
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
Generating Image files from the database blob/ longblob files
PreparedStatement ps=con.prepareStatement("select * from emp");
ResultSet rs=ps.executeQuery();
if(rs.next()){//now on 1st row
Blob b=rs.getBlob(5);//2 means 2nd column data
byte barr[]=b.getBytes(1,(int)b.length());//1 means first image
FileOutputStream fout=new FileOutputStream("D:\\abcd.jpg");
fout.write(barr);
fout.close();
CallableStatement
For accessing database stored procedures.
CallableStatement interface will accept runtime input parameters.
Creating Statement Object
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}
Three types of execute methods:
· boolean execute (String SQL):
Returns true if ResultSet object is retrieved; else, returns false.
Use to execute SQL DDL statements
· int executeUpdate (String SQL):
Returns number of rows affected by the SQL statement.
Use to execute SQL statements which expect to get several rows affected –
DML: INSERT, UPDATE, or DELETE.
· ResultSet executeQuery (String SQL):
Returns ResultSet object.
Use to expect to get a result set
SELECT statement.
Statement smt = con.createStatement();
ResultSet rs = smt.executeQuery(“Select_Queries”);
int n = smt.executeUpdate(“DML_Queries”);
boolean b = smt.execute(“Other_Queries”);
Closing Statement Object
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
stmt.close();
}
PreparedStatement Objects
PreparedStatement interface for supplying arguments dynamically.
PreparedStatement pstmt = null;
try {
String SQL = "Update Emp SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}
Parameters represented by the ? symbol (called parameter marker).
Supply values for every parameter prior to SQL statement execution.
The setXX() methods bind values to the parameters.
Every parameter marker is referred by ordinal position.
First marker represents position 1, the next position 2, and so on.
Closing PreparedStatement Object
PreparedStatement pstmt = null;
try {
String SQL = "Update Emp SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
pstmt.close();
}
Example:
PreparedStatement ps = con.prepareStatement(“select * from emp where empno=?”);
ps.setInt(int, int);
ps.setInt(1, 74);
PreparedStatement ps = con.prepareStatement(“insert into emp(empno, ename, sal, deptno) values(?,?,?,?)”);
Int eno=74;
String ename=“vishwa”;
ps.setInt(1, eno);
ps.setString(2, ename);
ps.setInt(3, 74000);
ps.setInt(4, 1);
CallableStatement Objects
Used to execute a call to a database stored procedure.
Creating CallableStatement Object
CREATE OR REPLACE PROCEDURE getEmpName
(EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS
BEGIN
SELECT first INTO EMP_FIRST
FROM Emp
WHERE ID = EMP_ID;
END;
DELIMITER $$
DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$
CREATE PROCEDURE `EMP`.`getEmpName`
(IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
SELECT first INTO EMP_FIRST
FROM Emp
WHERE ID = EMP_ID;
END $$
DELIMITER ;
Three types of parameters:
IN, OUT, and INOUT.
PreparedStatement object only uses the IN parameter.
CallableStatement object can use all the three.
IN
parameter whose value is unknown when the SQL statement is created.
You bind values to IN parameters with the setXXX() methods.
OUT
parameter whose value is supplied by the SQL statement which it returns.
retrieve values from theOUT parameters with the getXXX() methods.
INOUT
parameter that provides both input and output values.
bind variables with the setXXX() methods
retrieve values with the getXXX() methods.
Example
CallableStatement cstmt = null;
try {
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn.prepareCall (SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}
Closing CallableStatement Object
try {
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn.prepareCall (SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
cstmt.close();
}
ResultSet
SQL statements which read data from a database query, returns the data in result set.
SELECT statement is to select rows from a database and view and return as result set.
java.sql.ResultSet interface for result set of a database query.
ResultSet object have a cursor which points to current row in result set.
The methods of ResultSet interface have three categories −
· Navigational methods: Used for moving the cursor around.
· Get methods: Used for viewing the data in the columns of the current row which pointed by cursor.
· Update methods: Used for updating data in the columns of the current row.
cursor is movable based on the ResultSet properties.
connection methods to create statements with ResultSet −
· createStatement(int RSType, int RSConcurrency);
· prepareStatement(String SQL, int RSType, int RSConcurrency);
· prepareCall(String sql, int RSType, int RSConcurrency);
Type of ResultSet
If no ResultSet type specified, TYPE_FORWARD_ONLY automatically considered.
ResultSet.TYPE_FORWARD_ONLY
Cursor can only move forward in result set.
ResultSet.TYPE_SCROLL_INSENSITIVE
cursor can scroll forward and backward.
result set is not sensitive to changes done by others on database
ResultSet.TYPE_SCROLL_SENSITIVE.
cursor can scroll forward and backward
the result set is sensitive to changes done by others on database
Concurrency of ResultSet
ResultSet.CONCUR_READ_ONLY
Creates a read-only result set. (default)
ResultSet.CONCUR_UPDATABLE
Creates an updateable result set.
try {
Statement stmt = conn.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
}
catch(Exception ex) {
....
}
finally {
....
}
methods in the ResultSet interface which used for moving the cursor
1 public void beforeFirst() throws SQLException
Moves the cursor just before the first row.
2 public void afterLast() throws SQLException
Moves the cursor just after the last row.
3 public boolean first() throws SQLException
Moves the cursor to the first row.
4 public void last() throws SQLException
Moves the cursor to the last row.
5 public boolean absolute(int row) throws SQLException
Moves the cursor to the specified row.
6 public boolean relative(int row) throws SQLException
Moves cursor the given number of rows forward or backward, from where it is pointing currently.
7 public boolean previous() throws SQLException
Moves the cursor to the previous row which returns false if the previous row is off the result set.
8 public boolean next() throws SQLException
Moves the cursor to the next row which returns false if there are no more rows in the result set.
9 public int getRow() throws SQLException
Returns the row number that the cursor is pointing to.
10 public void moveToInsertRow() throws SQLException
Moves the cursor to a special row in the result set that can be used to insert a new row into the database.
11 public void moveToCurrentRow() throws SQLException
Moves the cursor back to the current row if the cursor is currently at the insert row; otherwise, this method does nothing
Viewing a Result Set
· One that takes in a column name.
· One that takes in a column index.
1 public int getInt(String columnName) throws SQLException
Returns the int in the current row in the column named columnName.
2 public int getInt(int columnIndex) throws SQLException
Returns the int in the current row in the specified column index.
The column index starts at 1, meaning the first column of a row is 1, the second column of a row is 2, and so on.
Updating a Result Set
The ResultSet interface contains a collection of update methods to update the data of a result set.
· One that takes in a column name.
· One that takes in a column index.
1 public void updateString(int columnIndex, String s) throws SQLException
Changes the String in the specified column to the value of s.
2 public void updateString(String columnName, String s) throws SQLException
column is specified by its name instead of its index.
1 public void updateRow()
Updates the current row by updating the corresponding row in the database.
2 public void deleteRow()
Deletes the current row from the database
3 public void refreshRow()
Refreshes data in the result set to reflect any recent changes in the database.
4 public void cancelRowUpdates()
Cancels any updates made on the current row.
5 public void insertRow()
Inserts a row into the database.
Data Types
Date & Time Data Types
java.sql.Date class maps to the SQL DATE type
java.sql.Time and java.sql.Timestamp classes map to the SQL TIME and SQL TIMESTAMP data types
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.*;
public class SqlDateTime {
public static void main(String[] args) {
//Get standard date and time
java.util.Date javaDate = new java.util.Date();
long javaTime = javaDate.getTime();
System.out.println("The Java Date is:" +
javaDate.toString());
//Get and display SQL DATE
java.sql.Date sqlDate = new java.sql.Date(javaTime);
System.out.println("The SQL DATE is: " +
sqlDate.toString());
//Get and display SQL TIME
java.sql.Time sqlTime = new java.sql.Time(javaTime);
System.out.println("The SQL TIME is: " +
sqlTime.toString());
//Get and display SQL TIMESTAMP
java.sql.Timestamp sqlTimestamp =
new java.sql.Timestamp(javaTime);
System.out.println("The SQL TIMESTAMP is: " +
sqlTimestamp.toString());
}//end main
}//end SqlDateTime
Output:
The Java Date is:Thu Mar 02 16:03:28 IST 2023
The SQL DATE is: 2023-03-02
The SQL TIME is: 16:03:28
The SQL TIMESTAMP is: 2023-03-02 16:03:28.465
Handling NULL Values
SQL's use of NULL values and Java's use of null are different.
Example
Statement stmt = con.createStatement();
ResultSet res = stmt.executeQuery("SELECT * from emp");
// checking if ResultSet is empty
if (res.next() == false)
{
System.out.println("ResultSet in empty in Java");
}
else {
while (res.next()) {
System.out.println(res.getInt(1)+"\t"+res.getInt(2)+"\t"+res.getString(3)+"\t"+res.getString(4));
}
}
Transactions
JDBC Connection by default auto-commit.
Every SQL statement is committed in database after completion.
Three reasons to turn off the auto-commit to manage transactions −
· Performance increase.
· Integrity of business processes maintenance.
· Use of distributed transactions.
Transactions enable you to control if, and when, changes are applied to the database. It treats a single SQL statement or a group of SQL statements as one logical unit, and if any statement fails, the whole transaction fails.
use the Connection object's setAutoCommit() method
pass a boolean false to setAutoCommit( ), turn off auto-commit
pass boolean true to turn on.
conn.setAutoCommit(false);
Commit & Rollback
con.commit( );
con.rollback( );
A transaction is a set of logically related operations.
Example:
transferring money from your bank account to your friend’s account, the set of operations:
Simple Transaction Example
1. Read your account balance
2. Deduct the amount from your balance
3. Write the remaining balance to your account
4. Read your friend’s account balance
5. Add the amount to his account balance
6. Write the new updated balance to his account
This whole set of operations can be called a transaction.
Transaction failure in between the operations
The main problem that can happen during a transaction is that the transaction can fail before finishing the all the operations in the set.
This can happen due to power failure, system crash etc.
This is a serious problem that can leave database in an inconsistent state.
Assume that transaction fail after third operation (see the example above) then the amount would be deducted from your account but your friend will not receive it.
To solve this problem, we have the following two operations
Commit: If all the operations in a transaction are completed successfully then commit those changes to the database permanently.
Rollback: If any of the operation fails then rollback all the changes done by previous operations.
Even though these operations can help us avoiding several issues that may arise during transaction but they are not sufficient when two transactions are running concurrently.
To handle those problems, we need to understand database ACID properties.
To ensure the integrity of data during a transaction (A transaction is a unit of program that updates various data items, read more about it here), the database system maintains the following properties.
These properties are widely known as ACID properties:
Atomicity:
This property ensures that either all the operations of a transaction reflect in database or none.
Let’s take an example of banking system to understand this: Suppose Account A has a balance of 400$ & B has 700$.
Account A is transferring 100$ to Account B.
This is a transaction that has two operations a) Debiting 100$ from A’s balance b) Creating 100$ to B’s balance.
Let’s say first operation passed successfully while second failed, in this case A’s balance would be 300$ while B would be having 700$ instead of 800$.
This is unacceptable in a banking system.
Either the transaction should fail without executing any of the operation or it should process both the operations.
The Atomicity property ensures that.
Consistency:
To preserve the consistency of database, the execution of transaction should take place in isolation (that means no other transaction should run concurrently when there is a transaction already running).
For example account A is having a balance of 400$ and it is transferring 100$ to account B & C both.
So we have two transactions here.
Let’s say these transactions run concurrently and both the transactions read 400$ balance, in that case the final balance of A would be 300$ instead of 200$.
This is wrong.
If the transaction were to run in isolation then the second transaction would have read the correct balance 300$ (before debiting 100$) once the first transaction went successful.
Isolation:
For every pair of transactions, one transaction should start execution only when the other finished execution.
I have already discussed the example of Isolation in the Consistency property above.
Durability:
Once a transaction completes successfully, the changes it has made into the database should be permanent even if there is a system failure.
The recovery-management component of database systems ensures the durability of transaction.
try{
//Assume a valid connection object conn
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
String SQL = "INSERT INTO Emp " +
"VALUES (106, 20, 'Abcd', 'Xyz')";
stmt.executeUpdate(SQL);
//Submit a malformed SQL statement that breaks
String SQL = "INSERTED IN Emp " +
"VALUES (107, 22, 'Sita', 'Singh')";
stmt.executeUpdate(SQL);
// If there is no error.
conn.commit();
}catch(SQLException se){
// If there is any error.
conn.rollback();
}
In this case, none of the above INSERT statement would success and everything would be rolled back.
For a better understanding, let us study the Commit - Example Code.
Using Savepoints
The new JDBC 3.0 Savepoint interface gives you the additional transactional control. Most modern DBMS, support savepoints within their environments such as Oracle's PL/SQL.
When you set a savepoint you define a logical rollback point within a transaction. If an error occurs past a savepoint, you can use the rollback method to undo either all the changes or only the changes made after the savepoint.
The Connection object has two new methods that help you manage savepoints −
· setSavepoint(String savepointName): Defines a new savepoint. It also returns a Savepoint object.
· releaseSavepoint(Savepoint savepointName): Deletes a savepoint. Notice that it requires a Savepoint object as a parameter. This object is usually a savepoint generated by the setSavepoint() method.
There is one rollback (String savepointName) method, which rolls back work to the specified savepoint.
The following example illustrates the use of a Savepoint object –
try{
//Assume a valid connection object conn
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
//set a Savepoint
Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
String SQL = "INSERT INTO Emp " +
"VALUES (106, 20, 'Abcd', 'Xyz')";
stmt.executeUpdate(SQL);
//Submit a malformed SQL statement that breaks
String SQL = "INSERTED IN Emp " +
"VALUES (107, 22, 'Sita', 'Xyz')";
stmt.executeUpdate(SQL);
// If there is no error, commit the changes.
conn.commit();
}catch(SQLException se){
// If there is any error.
conn.rollback(savepoint1);
}
Examples:
#1: Adding Column
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter table name:");
String table = bf.readLine();
System.out.println("Enter column name:");
String col = bf.readLine();
System.out.println("Enter data type:");
String type = bf.readLine();
int n = st.executeUpdate("ALTER TABLE "+table+" ADD "+col+" "+type);
System.out.println("Query OK, "+n+" rows affected");
#2: Add Table Name
DatabaseMetaData dbm = con.getMetaData();
String[] types = {"TABLE"};
ResultSet rs = dbm.getTables(null,null,"%",types);
System.out.println("Table name:");
while (rs.next()){
String table = rs.getString("TABLE_NAME");
System.out.println(table);
con.close();
#3: Change the Column Name
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter table name:");
String table = bf.readLine();
System.out.println("Enter old column name:");
String old_col = bf.readLine();
System.out.println("Enter new column:");
String new_col = bf.readLine();
System.out.println("Enter data type:");
String type = bf.readLine();
int n = st.executeUpdate("ALTER TABLE "+table+" CHANGE "+old_col+" "+new_col+" "+type);
System.out.println("Query OK, "+n+" rows affected");
#4: Fetching Ascending Order
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter table name:");
String table = bf.readLine();
System.out.println("Enter column name which have to see ascending order:");
String col_name = bf.readLine();
ResultSet res = st.executeQuery("SELECT "+col_name+" FROM "+table+" ORDER BY "+col_name+" ASC");
System.out.println("Ascending order of given column:");
while (res.next()){
int col = res.getInt(1);
System.out.println(col);
#5: Counting Records
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter table name:");
String table = bf.readLine();
String sql = "SELECT COUNT(*) FROM "+table;
PreparedStatement prest = con.prepareStatement(sql);
ResultSet rs = prest.executeQuery();
while (rs.next()){
records = rs.getInt(1);
}
System.out.println("Number of records: " + records);
con.close();
#6: Creating Database
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter Database name:");
String database = bf.readLine();
st.executeUpdate("CREATE DATABASE "+database);
System.out.println("1 row(s) affacted");
#7: Create MySQL Table
Statement st = con.createStatement();
String table = "CREATE TABLE java_DataTypes2("+ "typ_boolean BOOL, " + "typ_byte TINYINT, " + "typ_short SMALLINT, " + "typ_int INTEGER, " + "typ_long BIGINT, " + "typ_float FLOAT, " + "typ_double DOUBLE PRECISION, " typ_bigdecimal DECIMAL(13,0), #VALUE! + "typ_blob BLOB)";
st.executeUpdate(table);
System.out.println(table);
con.close();
#8: Creating a Table
Class.forName(driverName).newInstance();
con = DriverManager.getConnection(url+dbName, userName, password);
try{
Statement st = con.createStatement();
String table = "CREATE TABLE Employee11(Emp_code integer, Emp_name varchar(10))";
st.executeUpdate(table);
System.out.println("Table creation process successfully!");
#9: Delete All Records
Statement st = con.createStatement();
String sql = "DELETE FROM employee6";
int delete = st.executeUpdate(sql);
if(delete == 0){
System.out.println("All rows are completelly deleted!");
#10: Deleting the column
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter table name: ");
String table = bf.readLine();
#11. Get All Records
Statement st = con.createStatement();
ResultSet res = st.executeQuery("SELECT * FROM employee6");
System.out.println("Emp_code: " + "\t" + "Emp_name: ");
while (res.next()) {
int i = res.getInt("Emp_code");
String s = res.getString("Emp_name");
System.out.println(i + "\t\t" + s);
System.out.println("Enter column name: ");
String col = bf.readLine();
st.executeUpdate("ALTER TABLE "+table+" DROP "+col);
System.out.println("Column is deleted successfully!");
#12: Inserting Records
String sql = "INSERT movies VALUES(?,?)";
PreparedStatement prest = con.prepareStatement(sql);
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter movie name:");
String mov = bf.readLine();
prest.setString(1, mov);
System.out.println("Enter releases year of movie:");
int year = Integer.parseInt(bf.readLine());
prest.setInt(2, year);
int count = prest.executeUpdate();
System.out.println(count + "row(s) affected");
con.close();
#13: Insert Values
Statement st = con.createStatement();
int val = st.executeUpdate("INSERT into student VALUES("+'vihas'+","+'vihas@gmail.com'+","+63020+")");
System.out.println(val + " rows affected");
#14: Inserting with PreparedStatement
String sql = "SELECT title,year_made FROM movies WHERE year_made >= ? AND year_made <= ?";
PreparedStatement prest = con.prepareStatement(sql);
prest.setInt(1,1980);
prest.setInt(2,2004);
ResultSet rs = prest.executeQuery();
while (rs.next()){
String mov_name = rs.getString(1);
int mov_year = rs.getInt(2);
count++;
System.out.println(mov_name + "\t" + "- " + mov_year);
#15: PreparedStatement with Date inserting
PreparedStatement prest = con.prepareStatement("INSERT Records VALUES(?,?,?)");
prest.setInt(1,003);
prest.setString(2,"vinod kumar");
prest.setDate(3,date.valueOf("1984-01-12"));
int row = prest.executeUpdate();
System.out.println(row + " row(s) affected");
#16: Updating Records
String sql = "UPDATE movies SET title = ? WHERE year_made = ?";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1,"Sanam We wafafa");
prest.setInt(2,2005);
prest.executeUpdate();
System.out.println("Updating Successfully!");
con.close();