JAVA DATABASE CONNECTIVITY

What is JDBC?
 The Java API for developing Java database applications is called
JDBC.
 JDBC stands for Java Database Connectivity.
 JDBC is the trademarked name of a Java API that supports Java
programs that access relational databases.
 JDBC provides Java programmers with a uniform interface for
accessing and manipulating a wide range of relational databases.
 Using the JDBC API, applications written in the Java can execute
SQL statements, retrieve results, present data in a user-friendly
interface, and propagate changes back to the database.
 The JDBC API can also be used to interact with multiple data sources
in a distributed, heterogeneous environment.
What is JDBC?
 JDBC is the standard method of accessing databases from Java.
 Individual database management systems require a JDBC driver to
be accessed via JDBC.
 The JDBC API uses a Driver Manager and database-specific drivers
to provide transparent connectivity to heterogeneous databases.
 The JDBC driver manager ensures that the correct driver is used to
access each data source.
 The Driver Manager is capable of supporting multiple concurrent
drivers connected to multiple heterogeneous databases.
What is JDBC?
 The JDBC API is a set of Java interfaces and classes used to write Java
programs for accessing and manipulating relational databases.
 Since a JDBC driver serves as the interface to facilitate communications
between JDBC and a proprietary database, JDBC drivers are database
specific.
 They are normally provided by the database vendors.
 You need MySQL JDBC drivers to access the MySQL database, and Oracle
JDBC drivers to access the Oracle database.
 For the Access database, use the JDBC-ODBC bridge driver included in
JDK.
 ODBC is a technology developed by Microsoft for accessing databases on
the Windows platform.
 An ODBC driver is preinstalled on Windows.
 The JDBC-ODBC bridge driver allows a Java program to access any ODBC
data source.
Core JDBC Components
 The JDBC API provides a number of interfaces and classes that allow to connect
to database and process the data.
 The following are the most commonly used interfaces and classes:
 Driver: A JDBC driver is a software component enabling a Java application to
interact with a database. To connect with individual databases, JDBC requires
drivers for each database. This interface handles the communications with the
database server. You will interact directly with Driver objects very rarely.
Instead, you use DriverManager objects, which manages driver objects. It also
abstracts the details associated with working with Driver objects.
 DriverManager: this class manages a list of database drivers. It matches
connection requests from a java application with the proper database driver
using communication subprotocol. The first driver that recognizes a certain
subprotocol under JDBC will be used to establish a database Connection.
 Connection: This interface has methods for communicating with a database. The
connection object represents communication context, i.e., all communication with
database is through connection object only.
Core JDBC Components…
 Statement: You use objects created from this interface to submit the SQL
statements to the database. The Statement interface contains the methods
necessary to send SQL statements to the database for execution and return
the results. In particular, you use the executeQuery() method to execute a
select statement or the executeUpdate() method to execute an insert,
update, or delete statement.
 ResultSet: These objects hold data retrieved from a database after you
execute an SQL query using Statement objects. It acts as an iterator to
allow you to move through its data.
 The ResultSet interface represents rows returned from a query. It provides
methods you can use to move from row to row and to get the data of each
column.
 SQLException: This class handles any errors that occur in a database
application.
Connecting to DB
 Connecting to database involves the following steps:
 Registering the driver with the DriverManager

 Connecting to the database using the registered Driver

 Executing SQL statements on the database

 Closing the connection

 Before starting this process, it is necessary to download the JDBC

driver for the specific database you want to connect to.
 Then, add the location of the drivers to the class path.
Connecting to DB…
Registering Drivers
 Before you can use JDBC to access a database, you must first establish a
connection to the database.
 The first step to establishing a connection involves registering the driver class so
the class is available.
 To do that, you use the forName method of the class Class, specifying the
package and class name of the driver.
try {
Class.forName("JDBCDriverClass");
} catch (ClassNotFoundException e) {
// Report the exception
}
 The forName method throws ClassNotFoundException if the driver class can’t be
located.
 So you have to enclose it in a try/catch block that catches
ClassNotFoundException.
 For example, to register the MySQL connector, use this statement:
Class.forName("com.mysql.jdbc.Driver");
Connecting to DB…
 To register the standard ODBC driver, use this statement instead:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 A driver class loaded in this fashion should create an instance of itself and
register it with the driver manager.
 You can also register drivers with driver manager by calling
DriverManager.registerDriver() static method.
 Before registering the driver, you have to create instance object of the
driver.
 You can do this as follows:

try {
Driver myDriver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver( myDriver );
} catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
 Connecting to DB…
 The following table lists down popular JDBC driver names and database URL.
RDBMS JDBC driver class URL format
MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname:port/dbName

ORACLE oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@hostname:portnumber:dbName

DB2 COM.ibm.db2.jdbc.net.DB2Driver jdbc:db2:hostname:portnumber/dbName

Sybase com.sybase.jdbc.SybDriver jdbc:sybase:Tds:hostname: portNumber/dbName

SQL com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc:sqlserver://server:port;DbName=dbname

 To use the JDBC-ODBC bridge in a Java, a suitable ODBC data source must be
configured.
 The data source should be configured as a System DSN, not a User DSN.
 The driver class name is sun.jdbc.odbc.JdbcOdbcDriver if the Sun JVM is used.
 The database URL used in the getConnection() statement is jdbc:odbc:dsn,
where dsn is the data source name.
Connecting to DB…
Connecting to Database
 After you register the driver class, you can call the static getConnection
method of DriverManager class to open connection.
 This method takes three String parameters: the database URL, the user

name, and a password.

String url = “jdbc:mysql://localhost:3306/ university_record”;
con = DriverManager.getConnection(url, “root”, “vertrigo”);
 DriverManager provides three different methods to get connection:
DriverManager.getConnection(String url)
DriverManager.getConnection(String url, String username, String password)
DriverManager.getConnection(String url, Properties prop)
 The methods throw SQLException if a database access error occurs or the
url is null.
 They also throw SQLTimeoutException if the timeout value specified by the
setLoginTimeout method has been exceeded.
Connecting to DB…
 The key argument to DriverManager.getConnection() is a JDBC URL,
which is a string with three components separated by semicolons:
<protocol>:<subprotocol>:<subname>
 where
 protocol - is always jdbc.
 subprotocol - is a vendor-specific string that identifies the driver
to be used. The driver indicates whether it can handle that
subprotocol when asked by the driver manager. For example, the
JDBC-ODBC bridge uses the reserved value odbc as its
subprotocol. This value is intended to be unique across all driver
vendors.
 subname - identifies the specific database to connect to. This
string contains whatever the driver needs to identify the
database. It may also contain connection parameters the
database needs.
Connecting to DB…
 Examples of JDBC URLs are
jdbc:odbc:finance
 This would indicate an ODBC data source named finance that is
accessed by the JDBC-ODBC bridge driver.
 You can also specify like this
jdbc:idb:c:/path/database.prp
 For MySQL database, it can be specified using the following syntax:
jdbc:mysql://server:port/databasename
 Example:
jdbc:mysql://localhost:3306/university_record
Connecting to DB…
 Example: connecting to MySQL server
Connection getConnection(){
Connection con = null;
try {
Class.forName(“com.mysql.jdbc.Driver”);
String url = “jdbc:mysql://localhost:3306/university_record”;
String user = “root”;
String pw = “vertrigo”;
con = DriverManager.getConnection(url, user, pw);
} catch (ClassNotFoundException e){
System.out.print(e.getMessage());
System.exit(0);
} catch (SQLException e){
System.out.print(e.getMessage());
System.exit(0);
}
return con;
}
Querying a Database
 After you establish a connection to a database, you can execute SQL
statements to retrieve data.
 To do so, you have to use several classes and interfaces:
 Connection: the createStatement() method of Connection interface returns a
Statement object, which you then use to execute statements.
 Statement: the Statement interface contains the methods necessary to send
SQL statements to the database for execution and returning the results.
 Use the executeQuery() method to execute a select statement
 Use the executeUpdate() method to execute an insert, update, or delete
statement.
 ResultSet: the ResultSet interface represents rows returned from a query.
 It provides methods you can use to move from row to row and to get the
data for a column.
 Querying a Database…
 Methods of Connection interface

Connection interface methods Description

void close() Closes the connection.

Creates a Statement object that can execute a SQL

Statement createStatement()
statement on the database connected by the connection.

Statement createStatement (int type, Creates a Statement object that can execute an SQL
int concur) statement on the database connected by the connection.

PreparedStatement prepareStatement( Creates PreparedStatement object that can execute an SQL

String SQL, int type, int Concur) statement on the database connected by the connection

CallableStatement prepareCall(String
Creates a CallableStatement object
sql, int type, int Concur)
 Querying a Database…
 Once you create the Statement object using one of the above
methods of Connection interface, you can now execute an SQL
statement on the database.
 Statement interface provides few methods to enable programmers
to do this.
 Statement Interface methods
Methods Description
Executes the select statement contained in the
ResultSet executeQuery(String sql) string parameter and returns the result data as a
ResultSet object.
Executes the insert, update, or delete statements
int executeUpdate(String sql) contained in the string parameter and returns the
result data as a ResultSet object.
Querying a Database…
 The createStatement method of Connection interface has the following syntax:
Statement createStatement (int type, int concur);
 The first parameter of the createStatement method specifies the type of result
set that is created, and can be one of the following:
 ResultSet.TYPE_FORWARD_ONLY - the result set cannot be scrolled; its cursor
moves forward only, from before the first row to after the last row.
 This is the default value.
 ResultSet.TYPE_SCROLL_INSENSITIVE - the result can be scrolled; its cursor can
move both forward and backward, and it can move to an absolute position.
 The result set is insensitive to changes made to the underlying data source while
it is open.
 It contains the rows that satisfy the query at either the time the query is
executed or as the rows are retrieved.
 ResultSet.TYPE_SCROLL_SENSITIVE - the result can be scrolled; its cursor can
move both forward and backward, and it can move to an absolute position.
 The result set reflects changes made to the underlying data source while the
result set remains open.
Querying a Database…
 The second parameter is the concurrency of a ResultSet object.
 The concurrency of a ResultSet object determines what level of
update functionality is supported.
 This indicates whether the result set is read-only or updatable, and
can be one of the following:
 ResultSet.CONCUR_READ_ONLY - creates a read-only result set.
This is the default value.
 ResultSet.CONCUR_UPDATABLE - creates an updateable result
set. If insertion, updating and deletion is required, it is necessary
to open it as ResultSet.CONCUR_UPDATABLE.
Querying a Database…
 The following snippet executes a select statement and puts the result
in Resultset called rows:
Connection con = null;
try {
Class.forName(“com.mysql.jdbc.Driver”);
String url = “jdbc:mysql://localhost:3306/university_record”;
String user = “root”;
String pw = “vertrigo”;
con = DriverManager.getConnection(url, user, pw);
} catch (Exception ex){
System.out.println(ex.getMessage());
}
Statement st = con.createStatement();
String select = “Select * from student”;
ResultSet rows = st.executeQuery(select);
Navigating through the ResultSet
 The ResultSet object returned by the executeQuery() contains all the rows that
are retrieved by the select statement.
 You can only access one of those rows at a time.
 The result set maintains a pointer called a cursor to keep track of the current row.
 You can use the following methods to move the cursor through a result set.
Method Description
void close() Closes the Resultset
boolean next() Moves cursor to the next row
boolean previous() Moves the cursor to the previous row in the ResultSet
boolean first() Moves the cursor to the first row in the ResultSet
void last() Moves the cursor to the last row.
Moves the cursor to the front of this ResultSet object, just before the first
void beforeFirst()
row.
void afterLast() Moves the cursor to the end of the ResultSet object, just after the last row
boolean absolute(int row) Moves the cursor to the given row number in this ResultSet object.
boolean relative(int row) Moves the cursor forward or backward the number of rows specified
int getRow() Gets the current row number
boolean isBeforeFirst() True if the cursor is before the first row
boolean isAfterLast() True if the cursor is after the last row
boolean isFirst() True if the cursor is positioned on the first row
boolean isLast() True if the cursor is positioned on the last row
Querying a Database…
 boolean absolute(int row) - moves the cursor to the given row number in
this ResultSet object.
 If the row number is positive, the cursor moves to the given row number with
respect to the beginning of the result set.
 The first row is row 1, the second is row 2, and so on.
 If the given row number is negative, the cursor moves to an absolute row
position with respect to the end of the result set.
 Calling the method absolute(-1) positions the cursor on the last row; calling
the method absolute(-2) moves the cursor to the next-to-last row, etc.
 An attempt to position the cursor beyond the first/last row in the result set
leaves the cursor before the first row or after the last row.
 Calling absolute(1) is the same as calling first().
 Calling absolute(-1) is the same as calling last().
Connection con = null;
Statement statement = null;
ResultSet records = null;
String sql;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/university_record", "root", "vertrigo");
sql = "select * from student";
statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
records = statement.executeQuery(sql);
}catch(SQLException xx) {
out.print(“Error: " + xx.getMessage());
} catch(Exception xx) {
out.print(“Error: " + xx.getMessage());
}
while(records.next()) {
System.out.print(records.getString("ID"));
System.out.print(records.getString("firstname"));
System.out.print(records.getString("middlename"));
System.out.print(records.getString("lastname"));
System.out.print(records.getString("sex"));
System.out.print(records.getString("email"));
System.out.print(records.getInt("telephone"));
}
con.close();
Querying a Database…
 The data returned from the database can be accessed using
column number/index or column names.
 To access the columns of the database table from the ResultSet,
we use the get methods of ResultSet.
 There is a get method for every data type that can be stored
in database.
Method Description
BigDecimal getBigDecimal(String columnName)
Gets the value of the specified column as a BigDecimal
BigDecimal getBigDecimal(int columnIndex)
boolean getBoolean(String columnName)
Gets the value of the specified column as a boolean
boolean getBoolean(int columnIndex)
Date getDate(String columnName)
Gets the value of the specified column as a Date
Date getDate(int columnIndex)
double getDouble(String columnName)
Gets the value of the specified column as a double
double getDouble(int columnIndex)
float getFloat(String columnName)
Gets the value of the specified column as a float
float getFloat(int columnIndex)
int getInt(String columnName)
Gets the value of the specified column as a int
int getInt(int columnIndex)
long getLong(String columnName)
Gets the value of the specified column as a long
long getLong(int columnIndex)
short getShort(String columnName)
Gets the value of the specified column as a short
short getShort(int columnIndex)
String getString(String columnName)
Gets the value of the specified column as a String
String getString(int columnIndex)
Blob getBlob(String columnName)
returns the value of the specified column as a Blob object
Blob getBlob(String columnIndex)
byte getByte(String columnName)
Gets the value of the specified column as a byte
byte getByte(String columnIndex)
byte[] getBytes(String columnName)
Returns the value of the specified column as a byte array
byte[] getBytes(String columnIndex)
Array getArray(String columnName)
Returns the value of the specified column as an Array object
Array getArray(String columnIndex)
public class Database extends JFrame {
Connection con = null;
Statement statement = null;
ResultSet records = null;
protected void retrieveData() throws SQLException, IOException {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/university_record", "root", "vertrigo");
String sql = "select * from student";
statement = con.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
records = statement.executeQuery(sql);
//records.next();
while(records.next()) {
System.out.print(records.getString("ID") + "\t");
System.out.print(records.getString("firstname") + "\t");
System.out.print(records.getString("middlename") + "\t");
System.out.print(records.getString("lastname") + "\t");
System.out.print(records.getString("sex") + "\t");
System.out.print(records.getDate("birthdate") + "\t");
System.out.print(records.getString("email") + "\t");
System.out.print(records.getInt("phone") + "\t");
System.out.println();
}
con.close();
} catch(Exception xx) {
System.out.print(“Error querying: " + xx.getMessage());
}
}
Updating Database
 Besides executing select statements, you can also use a Statement
object to execute insert, update, or delete statements as well.
 To do that, you call the executeUpdate() method instead of the
executeQuery() method.
 This method returns an int value that indicates how many rows were
updated.
 You can test the return value to determine whether the data was
properly updated.
Class.forName(“com.mysql.jdbc.Driver”);
String url = “jdbc:mysql://localhost:3306/teaching”;
Connection con = DriverManager.getConnection(url, “root”, “vertrigo”);
try {
Statement stmt = con.createStatement();
sql = “insert into student values(‘1256/07’, ‘David’, ’Jacques’, ‘George’, ’Male’,
‘1980-05-20’, 8954578693567, ‘jacques@yahoo.com’)”;
int i = stmt.executeUpdate(sql);
if (i == 1)
System.out.println(“Student registered.”);
else
System.out.println(“Student registration failed.”);
} catch (SQLException e){
System.out.println(e.getMessage());
System.exit(0);
}
  In addition to executing any type of update using executeUpdate() method,
the ResultSet interface provides methods that will allow to do updating
without writing any SQL statement.
 These methods make updating easier.
 The updating methods for ResultSet are:

Method Description

void cancelRowUpdates() Cancels the updates made to the current row in this ResultSet object.

Deletes the current row from this ResultSet object and from the
void deleteRow()
underlying database.
Inserts the contents of the insert row into this ResultSet object and
void insertRow()
into the database.
Moves the cursor to the remembered cursor position, usually the
void moveToCurrentRow()
current row.
void moveToInsertRow() Moves the cursor to the insert row.

void refreshRow() Refreshes the current row with its most recent value in the database.

Updates the underlying database with the new contents of the

void updateRow()
current row of this ResultSet object.
Updating Database…
Deleting Rows
 To delete the current row, call the deleteRow() method.

 This method cannot be called when the cursor is on the insert row.

 It throws an SQLException if:

 A database access error occurs

 the result set concurrency is CONCUR_READ_ONLY
 this method is called on a closed result set
 if this method is called when the cursor is on the insert row.
 Example: deleting a row

try {
rs.absolute(3);
rs.deleteRow();
}catch (SQLException e) {
out.println(e.getMessage());
}
Updating Database…
Updating Rows
 For a result set to be updated, it must have been produced by a
Statement object created with a concurrency type of
ResultSet.CONCUR_UPDATABLE.
 JDBC provides updateXXX() methods, where XXX is the JDBC data
type, similar to the existing getXXX() methods.
 These methods take a column number or column name parameter,
and a value parameter.
Update by Column name or index description
updateBoolean (String colname, boolean value) Updates the designated column with a boolean value. The updater methods are used
updateBoolean(int colIndex, boolean value) to update column values in the current row or the insert row.

updateDate(String colname, Date value) Updates the designated column with a java.sql.Date value. The updater methods are
updateDate(int colIndex, Date value) used to update column values in the current row or the insert row.

updateDouble (String colname, double value) Updates the designated column with a double value. The updater methods are used
updateDouble(int colIndex, double value) to update column values in the current row or the insert row.

updateFloat(String colname, float value) Updates the designated column with a float value. The updater methods are used to
updateFloat(int colIndex, float value) update column values in the current row or the insert row.

updateInt(String colname, int value) Updates the designated column with an int value. The updater methods are used to
updateInt(int colIndex, int value) update column values in the current row or the insert row.

updateLong(String colname, long value) Updates the designated column with a long value. The updater methods are used to
updateLong(int colIndex, long value) update column values in the current row or the insert row.

updateShort(String colname, short value) Updates the designated column with a short value. The updater methods are used to
updateShort(int columnIndex, short value) update column values in the current row or the insert row.

updateBigDecimal (String colname, BigDecimal value) Updates the designated column with a java.sql.BigDecimal value. The updater
updateBigDecimal(int colIndex, BigDecimal value) methods are used to update column values in the current row or the insert row.

updateString(String colname, value) Updates the designated column with a String value. The updater methods are used to
updateString(int String colIndex, String value) update column values in the current row or the insert row.

updateBlob(String columnName, Blob x) Updates the designated column with a java.sql.Blob value.The updater methods are
updateBlob(String columnIndex, Blob x) used to update column values in the current row or the insert row.

updateByte(String columnName, byte x) Updates the designated column with a byte value. The updater methods are used to
updateByte(String columnIndex, byte x) update column values in the current row or the insert row.

updateBytes(int columnName, byte x[]) Updates the designated column with a byte array value. The updater methods are
updateBytes(int columnIndex, byte x[]) used to update column values in the current row or the insert row.
Updating Database…
 The following example illustrates how to update the current row of
the ResultSet:
rs.updateString(“lastname”,”William”);
rs.updateString(“sex”, “female”);
rs.updateInt(“phone”, 3456218769)
rs.updateString(“email”, “hima@gmail.com”);
rs.updateRow();
Updating Database…
Inserting New Rows
 New rows can be added to the result set and the underlying table with insertRow().
 This involves a special cursor position known as the insert row.
 You can add/ insert data into the insert row using the updateXXX() methods shown
above.
 The following example illustrates how this works:
rs.moveToInsertRow();
rs.updateString(“ID”, “1256/07”);
rs.updateString(“firstname”, “David”);
rs.updateString(“middlename”, “Jacques”);
rs.updateString(“lastname”, “George”);
rs.updateString(“sex”, “Male”);
rs.updateString(“email”, “jacques@yahoo.com”);
rs.updateInt(“telephone”, 8954578693567);
rs.insertRow();
rs.moveToCurrentRow(); //return to where we were
PreparedStatement Object
 The PreparedStatement interface extends the Statement interface
which gives you added functionality with many advantages over a
generic Statement object.
 This statement gives you the flexibility of supplying arguments
dynamically.
 The Statement interface is used to execute static SQL statements that
contain no parameters.
 The PreparedStatement interface is used to execute a precompiled
SQL statement with or without parameters.
 Since the SQL statements are precompiled, they are efficient for
repeated executions.
 The main feature of a PreparedStatement object is that it is given
an SQL statement when it is created.
 The advantage to this is that the SQL statement is sent to the DBMS
right away, where it is compiled.
PreparedStatement Object…
 As a result, the PreparedStatement object contains an SQL statement
that has been precompiled.
 So, when PreparedStatement is executed, the DBMS can just run the
PreparedStatement SQL statement without having to compile it first.
 Although PreparedStatement objects can be used for SQL
statements with no parameters, you probably use them most often
for SQL statements that take parameters.
 The advantage of using SQL statements that take parameters is that
you can use the same statement and supply it with different values
each time you execute it.
 Besides, PreparedStatement can help to avoid SQL injection attack.
 Attaching parameters later on reduces the risk of malicious user
performing SQL injection attack on the program.
 PreparedStatement Object…
 To create PreparedStatement object, call the prepareStatement method of
Connection interface.
 PreparedStatement prepareStatement(String sql) – creates a PreparedStatement object
for sending parameterized SQL statements to the database. It throws SQLException.
 PreparedStatement prepareStatement(String sql, int resultType, int concurrency) –
creates a PreparedStatement object that will generate ResultSet objects with the
given type and concurrency.
 resultType – is a result set type; one of TYPE_FORWARD_ONLY,
TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE.
 concurrency – is a concurrency type; one of CONCUR_READ_ONLY or
CONCUR_UPDATABLE.
 PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) - creates a
PreparedStatement object that has the capability to retrieve auto-generated keys.
 The given constant tells the driver whether it should make auto-generated keys
available for retrieval.
 This parameter is ignored if the SQL statement is not an INSERT statement, or an SQL
statement able to return auto-generated keys.
 autoGeneratedKeys – one of Statement.RETURN_GENERATED_KEYS or
Statement.NO_GENERATED_KEYS.
PreparedStatement Object…
 All parameters in JDBC are represented by the ? symbol, which is
known as the parameter marker.
 You must supply values for every parameter before executing the
SQL statement.
 The setXXX() methods bind values to the parameters, where XXX
represents the Java data type of the value you wish to bind to the
input parameter.
 If you forget to supply the values, you will receive an SQLException.
 Each parameter marker is referred to by its ordinal position.
 The first marker represents position 1, the next position 2, and so
forth.
 This method differs from that of Java array indices, which start at 0.
Method description
Sets the designated parameter to the given Java String value. The driver converts this to an
setString(int paramIndex, String value) SQL VARCHAR or LONGVARCHAR value (depending on the argument's size relative to the
driver's limits on VARCHAR values) when it sends it to the database.

Sets the designated parameter to the given Java double value.The driver converts this to
setDouble(int paramIndex, double x)
an SQL DOUBLE value when it sends it to the database.
Sets the designated parameter to the given Java float value. The driver converts this to an
setFloat(int paramIndex, float x)
SQL REAL value when it sends it to the database.
Sets the designated parameter to the given Java long value. The driver converts this to an
setLong(int paramIndex, long x)
SQL BIGINT value when it sends it to the database.
Sets the designated parameter to the given Java int value. The driver converts this to an
setInt(int paramIndex, int x)
SQL INTEGER value when it sends it to the database.
Sets the designated parameter to the given Java short value. The driver converts this to an
setShort(int paramIndex, short x)
SQL SMALLINT value when it sends it to the database.
Sets the designated parameter to the given Java byte value. The driver converts this to an
setByte(int paramIndex, byte x)
SQL TINYINT value when it sends it to the database.
Sets the designated parameter to the given java.math.BigDecimal value. The driver
setBigDecimal(int paramIndex, BigDecimal x)
converts this to an SQL NUMERIC value when it sends it to the database.
Sets the designated parameter to the given Java boolean value. The driver converts this to
setBoolean(int paramIndex, boolean x)
an SQL BIT or BOOLEAN value when it sends it to the database.
Sets the designated parameter to the given java.sql.Date value. The driver converts this to
setDate(int paramIndex, Date x)
an SQL DATE value when it sends it to the database.
Sets the designated parameter to the given java.sql.Blob object. The driver converts this to
setBlob (int paramIndex, Blob x)
an SQL BLOB value when it sends it to the database.
Sets the designated parameter to the given java.sql.Array object. The driver converts this
setArray (int paramIndex, Array x)
to an SQL ARRAY value when it sends it to the database.
setNull(int paramIndex, int sqlType) Sets the designated parameter to SQL NULL.
PreparedStatement Object…
 All of the Statement object's methods for interacting with the database
execute(), executeQuery(), and executeUpdate() also work with the
PreparedStatement object.
 However, the methods are modified to use SQL statements that can take
input parameters.
Method Description
void clearParameters() Resets all of the PreparedStatment’s query parameters
Runs the prepared query against the database; this
boolean execute() method is used primarily if multiple ResultSets are
expected.
ResultSet executeQuery() Executes the prepared query.
Executes the prepared query; this method is used for
queries that do not produce a ResultSet (such as
Int executeUpdate()
Update). It returns the number or rows affected or 0 if
nothing is returned by the SQL command
PreparedStatement pstmt = null;
Connection con = null;
void insert() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "vertrigo");
String SQL = "INSERT INTO student VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
pstmt = con.prepareStatement(SQL);
pstmt.setString(1, "4536/05");
pstmt.setString(2, "Anna");
pstmt.setString(3, "Tom");
pstmt.setString(4, "Bill");
pstmt.setString(5, "female");
pstmt.setDate(6, new Date(95,10, 25));
pstmt.setInt(7, 748538857);
pstmt.setString(8, "anna@gmail.com");
int suc = pstmt.executeUpdate();
if (suc == 1)
System.out.print("Data inserted");
else
System.out.print("Data insertion failed");
}catch (Exception e) {
System.out.print("Error: " + e.getMessage());
}
}
PreparedStatement Object…
 Once a PreparedStatement is prepared, it can be reused again and again.
 You reuse a PreparedStatement by setting new values for the parameters
and then execute it again.
String sql = "update student set firstname=? , middlename=?, lastname=?
where id=?";
PreparedStatement pstatement = connection.prepareStatement(sql);
pstatement.setString(1, "Gary");
pstatement.setString(2, "Larson");
pstatement.setString(3, "Walter");
pstatement.setString (4, "123 ");
int rf = pstatement.executeUpdate();
pstatement.setString(1, "Chan");
pstatement.setString(2, "Lee");
pstatement.setString(3, "Huang");
pstatement.setString(4, " 456 ");
int rg = pstatement.executeUpdate();
Batch Updating
 JDBC 2.0 introduced the capability to submit a group of update statements
to be executed as a batch.
 Batch Processing allows you to group related SQL statements into a batch
and submit them with one call to the database.
 In some cases, this can represent a significant performance improvement.
 When you send several SQL statements to the database at once, you
reduce the amount of communication overhead, thereby improving
performance.
 A batch update is a batch of updates grouped together, and sent to the
database in one "batch", rather than sending the updates one by one.
 Sending a batch of updates to the database in one go, is faster than
sending them one by one, waiting for each one to finish.
 There is less network traffic involved in sending one batch of updates, and
the database might be able to execute some of the updates in parallel.
 The speed up compared to executing the updates one by one, can be quite
big.
Batch Updating…
 JDBC drivers are not required to support this feature.
 You should use the DatabaseMetaData.supportsBatchUpdates() method to
determine if the target database supports batch update processing.
 The method returns true if your JDBC driver supports this feature.
 The methods used in connection with batch updates are these:
 void clearBatch() - resets a batch to the empty state.
 It empties this Statement object's current list of SQL commands.
 It throws SQLException.
 void addBatch(String sql) - adds the given SQL command to the batch.
 It throws SQLException if a database access error occurs.
 int[] executeBatch() - submits the batch and collects update counts.
 It submits a batch of commands to the database for execution and if all
commands execute successfully, returns an array of update counts.
 The int elements of the array that is returned are ordered to correspond
to the commands in the batch.
 It throws SQLException, BatchUpdateException and SQLTimeoutException.
Class.forName("com.mysql.jdbc.Driver");
Connection con = null;
try {
con = DriverManager.getConnection("jdbc:mysql://localhost/demo", "root", "vertrigo");
Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE TABLE employee (lastName VARCHAR(20), " + "firstName VARCHAR(20), age INTEGER )");
//add insert statements to a batch
stmt.clearBatch();
stmt.addBatch("INSERT INTO employee VALUES ('Rodrigo','Joaquin',30)");
stmt.addBatch("INSERT INTO employee VALUES ('Gossec','Francois-Joseph',36)");
stmt.addBatch("INSERT INTO employee VALUES ('Ruggles','Carl',36)");
stmt.addBatch("INSERT INTO employee VALUES ('Widor','Charles’,34)");
stmt.addBatch("INSERT INTO employee VALUES ('Sibelius','Jean',23)");
stmt.addBatch("INSERT INTO employee VALUES('Copland','Aaron',21)");
stmt.addBatch("INSERT INTO employee VALUES('Auber','Daniel Francois',30)");
stmt.addBatch("INSERT INTO employee VALUES('Stravinsky','Igor',40)");
// Execute the batch and check the update counts
int[] counts = stmt.executeBatch();
boolean allGood = true;
for (int i = 0; i < counts.length; i++) {
if (counts[i] != 1)
allGood = false;
}
if(allGood)
System.out.println(“All data inserted”);
else
System.out.println(“At least one error occurred”);
stmt.close();
}catch(Exception ex) {}
Batch Updating…
 Batch can also be used with PreparedStatement in addition to Satatement.
 In this case, the compiled SQL will be added to batch and finally executed as a group.
String query = "INSERT INTO Student(ID, firstname, middlename, lastname) VALUES (?,?,?,?)";
PreparedStatement pstatement= con.prepareStatement(query);

pstatement.setString(1, "345/06");
pstatement.setString(2, "Tesfaye");
pstatement.setString(3, "Mekonen");
pstatement.setString(4, "Getahun");
pstatement.addBatch();

pstatement.setString(1, 102);
pstatement.setString(2, “Tesfaye");
pstatement.setString(3, “Bekele");
pstatement.setString(4, “Abebe”);
pstatement.addBatch();
pstatement.executeBatch();
Transaction
 A transaction is a set of actions to be carried out as a single, atomic action.
 Either all of the actions are carried out, or none of them are.
 Transactions enable you to control if, and when, changes are applied to the
database.
 It treats a group of SQL statements as one logical unit, and if any statement
fails, the whole transaction fails.
 The classic example of when transactions are necessary is the example of
bank accounts.
 You need to transfer $100 from one account to the other.
 You do so by subtracting $100 from the first account, and adding $100 to
the second account.
 If this process fails after you have subtracted the $100 from the first bank
account, the $100 are never added to the second bank account.
 The money is lost in cyber space.
Transaction…
 By default, JDBC Connection is in auto-commit mode.
 This means every SQL statement is executed by the database
immediately.
 That may be fine for simple applications, but there are three
reasons why you may want to turn off the auto-commit and use
transactions:
 To increase performance
 To maintain the integrity of business processes
 To use distributed transactions
 In order to allow a transaction, the Connection interface has the
method setAutoCommit() that can turn on transactions.
 The syntax of the method is:
setAutoCommit(boolean)
 If true is passed to the method, transaction is turned off.
 If false is passed to the method, then transaction is activated.
Transaction…
 Once you have finished writing the queries and you want to commit the
changes, then call commit() method on Connection object:
commit()
 This makes all changes made since the previous commit permanent and
releases any database lock currently held by this Connection object.
 This method should be used only when auto-commit mode has been
disabled.
 It is possible that commit() may fail to successfully execute.
 In such cases, to make the data in the database consistent, it is necessary to
roll back.
 To roll back updates to the database, use the following code:
rollback()
 This undo all changes made in the current transaction and releases any
database locks currently held by this Connection object.
 This method should be used only when auto-commit mode has been
disabled.
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(("jdbc:mysql://localhost/university_record",
"root", "vertrigo");
//allow transaction
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
String SQL = "INSERT INTO employee VALUES ('Rita', 'Tez', '30')";
stmt.executeUpdate(SQL);
String SQL = "INSERT INTO employee VALUES ('Sita', 'Singh', '20')";
stmt.executeUpdate(SQL);

//execute now
conn.commit();
}catch(SQLException se){
conn.rollback();
}
Retrieving Metadata
 JDBC provides the DatabaseMetaData interface for obtaining database-
wide information.
 It provides ResultSetMetaData interface for obtaining information on the
specific ResultSet.

A. DatabaseMetadata
 A connection provides access to database metadata information that
describes the capabilities of the database, supported SQL grammar, stored
procedures, and so on.
 The DatabaseMetaData interface provides information about the
database as a whole.
 There are many methods within the interface, such as whether or not
transactions are supported or whether or not JDBC 2.0-type result
sets are supported.
 These methods are very important for making a JDBC program as portable
as possible, since the use of advanced features is not supported in all
driver/database combinations.
Retrieving Metadata…
 The DatabaseMetaData interface also provides a means to
find out about the schema.
 There is a method to retrieve the names of the tables
available, as well as the name of the stored procedures
available .
 There are methods to return primary keys, foreign keys, and
user-defined types.
 To obtain an instance of DatabaseMetaData for a database, use
the getMetaData method on a connection object like this:
DatabaseMetaData dbMetaData = connection.getMetaData();
 Retrieving Metadata…
 DatabaseMetaData defines a number of methods that returns
different kind of information about the database.
Method description
String getURL() Returns the URL for this DBMS
String getUserName() Returns the user name as known to this database.
boolean isReadOnly() Returns whether this database is in read-only mode.
String getDatabaseProductName() Retrieves the name of this database product.
String getDatabaseProductVersion() Retrieves the version number of this database product.
String getDriverName() Retrieves the name of this JDBC driver.
String getDriverVersion() Retrieves the version number of this JDBC driver as a String.
Retrieves the maximum number of concurrent connections
int getMaxConnections()
to this database that are possible.
Retrieves the maximum number of columns this database
int getMaxColumnsInTable()
allows in a table.
Retrieves the maximum number of characters this database
getMaxTableNameLength()
allows in a table name.
 Example: using DatabaseMetaData to get information about the database
public class TestDatabaseMetaData {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost/university_record", "root", "vertrigo");
DatabaseMetaData dbMetaData = connection.getMetaData();
System.out.println("database URL: " + dbMetaData.getURL());
System.out.println("database username: " + dbMetaData.getUserName());
System.out.println("database product name: " + dbMetaData.getDatabaseProductName());
System.out.println("database product version: " + dbMetaData.getDatabaseProductVersion());
System.out.println("JDBC driver name: " + dbMetaData.getDriverName());
System.out.println("JDBC driver version: " + dbMetaData.getDriverVersion());
System.out.println("Max number of connections: " + dbMetaData.getMaxConnections());
System.out.println("MaxTableNameLength: " + dbMetaData.getMaxTableNameLength());
System.out.println("MaxColumnsInTable: " + dbMetaData.getMaxColumnsInTable());

connection.close();
}
}
Retrieving Metadata…
B. Result Set Metadata
 The ResultSetMetaData interface describes information pertaining to the
result set.
 A ResultSetMetaData object can be used to find the types and properties
of the columns in a ResultSet.
 To obtain an instance of ResultSetMetaData, use the getMetaData method
on a result set like this:
ResultSetMetaData rsMetaData = resultSet.getMetaData();
 ResultSetMetaData defines a number of methods that returns information
about the result set.
 For example, you can use the getColumnCount() method to find the number
of columns in the result and the getColumnName(int) method to get the
column names.
 Retrieving Metadata…
Method Description
int getColumnCount() This returns the number of columns in this ResultSet object.
Indicates whether the designated column is automatically
boolean isAutoIncrement(int col)
numbered.
Returns the designated column's name. The first column is 1, the
String getColumnName(int col)
second is 2, etc.

Returns the designated column's suggested title for use in

printouts and displays. The suggested title is usually specified by
String getColumnLabel(int col)
the SQL AS clause. If a SQL AS is not specified, returns the same as
getColumnName(int) method.
String getTableName(int col) Returns the designated column's table name.
Retrieves the designated column's SQL type. The value returned is
int getColumnType(int col)
SQL type from java.sql.Types.
Indicates whether it is possible for a write on the designated
boolean isWritable(int col)
column to succeed.