Search for notes by fellow students, in your own course and all over the country.
Browse our notes for titles which look like what you need, you can preview any of the notes via a sample of the contents. After you're happy these are the notes you're after simply pop them into your shopping cart.
Title: software engineering
Description: all units are covered in the pdf and very useful .each and every topic is mentioned with side headings
Description: all units are covered in the pdf and very useful .each and every topic is mentioned with side headings
Document Preview
Extracts from the notes are below, to see the PDF you'll receive please use the links above
About the Tutorial
JDBC API is a Java API that can access any kind of tabular data, especially data
stored in a Relational Database
...
Audience
This tutorial is designed for Java programmers who would like to understand the
JDBC framework in detail along with its architecture and actual usage
...
As you are going to deal with RDBMS, you should
have prior exposure to SQL and Database concepts
...
Ltd
...
Ltd
...
We strive to update the contents of our website and tutorials as timely and as
precisely as possible, however, the contents may contain inaccuracies or errors
...
Ltd
...
If you discover any errors on our website or in this tutorial, please notify us at
contact@tutorialspoint
...
i
Audience
...
i
Copyright & Disclaimer
...
ii
1
...
1
What is JDBC?
...
1
JDBC Architecture
...
2
The JDBC 4
...
3
2
...
4
Create Database
...
4
Create Table
...
5
INSERT Data
...
6
UPDATE Data
...
7
3
...
8
Install Java
...
8
Install Database Drivers
...
9
Create Database
...
10
Create Data Records
...
SAMPLE CODE
...
13
Sample Code
...
DRIVER TYPES
...
17
JDBC Drivers Types
...
20
6
...
21
Import JDBC Packages
...
21
Database URL Formulation
...
24
Closing JDBC Connections
...
STATEMENTS
...
27
The PreparedStatement Objects
...
34
The CallableStatement Objects
...
RESULT SETS
...
44
Concurrency of ResultSet
...
46
Navigate - Example Code
...
51
Viewing - Example Code
...
56
Updating - Example Code
...
DATA TYPES
...
67
Handling NULL Values
...
TRANSACTIONS
...
69
Commit - Example Code
...
74
Savepoints - Example Code
...
EXCEPTIONS
...
81
12
...
86
Batching with Statement Object
...
88
Batching with PrepareStatement Object
...
94
13
...
99
Creating CallableStatement Object
...
101
JDBC SQL Escape Syntax
...
102
escape Keyword
...
103
call Keyword
...
103
14
...
105
15
...
110
Required Steps
...
110
16
...
113
Required Steps
...
113
17
...
116
Required Steps
...
116
18
...
119
Required Steps
...
119
19
...
122
Required Steps
...
122
20
...
125
Required Steps
...
125
v
21
...
129
Required Steps
...
129
22
...
133
Required Steps
...
133
23
...
137
Required Steps
...
137
24
...
141
Required Steps
...
141
25
...
146
Required Steps
...
146
26
...
151
Required Steps
...
151
vi
1
...
The JDBC library includes APIs for each of the tasks mentioned below that are
commonly associated with database usage
...
Creating SQL or MySQL statements
...
Viewing & Modifying the resulting records
...
Java can be used to
write different types of executables, such as:
Java Applications
Java Applets
Java Servlets
Java ServerPages (JSPs)
Enterprise JavaBeans (EJBs)
...
JDBC provides the same capabilities as ODBC, allowing Java programs to contain
database-independent code
...
JDBC Driver API: This supports the JDBC Manager-to-Driver Connection
...
The JDBC driver manager ensures that the correct driver is used to access each
data source
...
Following is the architectural diagram, which shows the location of the driver
manager with respect to the JDBC drivers and the Java application:
Common JDBC Components
The JDBC API provides the following interfaces and classes:
DriverManager: This class manages a list of database drivers
...
The first driver that recognizes a
certain subprotocol under JDBC will be used to establish a database
Connection
...
You will interact directly with Driver objects very rarely
...
It
also abstracts the details associated with working with Driver objects
...
The connection object represents communication context, i
...
, all
communication with database is through connection object only
...
Some derived interfaces accept parameters
in addition to executing stored procedures
...
It acts as an iterator to
allow you to move through its data
...
The JDBC 4
...
sql and javax
...
0
...
It offers the main classes
for interacting with your data sources
...
Exception handling improvements
...
Connection and statement interface enhancements
...
SQL ROWID access
...
Annotations
...
SQL SYNTAX
JDBC
Structured Query Language (SQL) is a standardized language that allows you to
perform operations on a database, such as creating entries, reading content,
updating content, and deleting entries
...
This chapter gives an overview of SQL, which is a prerequisite to understand
JDBC concepts
...
For a detailed understanding on SQL, you can read our MySQL Tutorial
...
The
syntax is:
SQL> CREATE DATABASE DATABASE_NAME;
Example
The following SQL statement creates a Database named EMP:
SQL> CREATE DATABASE EMP;
Drop Database
The DROP DATABASE statement is used for deleting an existing database
...
Be careful, deleting a database would loss all the data
stored in the database
...
The syntax is:
4
JDBC
SQL> CREATE TABLE table_name
(
column_name column_data_type,
column_name column_data_type,
column_name column_data_type
...
The syntax is:
SQL> DROP TABLE table_name;
Example
The following SQL statement deletes a table named Employees:
SQL> DROP TABLE Employees;
INSERT Data
The syntax for INSERT, looks similar to the following, where column1, column2,
and so on represents the new data to appear in the respective columns:
SQL> INSERT INTO table_name VALUES (column1, column2,
...
The syntax for
SELECT is:
SQL> SELECT column_name, column_name,
...
Example
The following SQL statement selects the age, first and last columns from the
Employees table, where id column is 100:
SQL> SELECT first, last, age
FROM Employees
WHERE id = 100;
The following SQL statement selects the age, first and last columns from the
Employees table, where first column contains Zara:
SQL> SELECT first, last, age
FROM Employees
WHERE first LIKE '%Zara%';
UPDATE Data
The UPDATE statement is used to update data
...
WHERE conditions;
6
JDBC
The WHERE clause can use the comparison operators such as =, !=, <, >,
<=,and >=, as well as the BETWEEN and LIKE operators
...
The syntax for DELETE
is:
SQL> DELETE FROM table_name WHERE conditions;
The WHERE clause can use the comparison operators such as =, !=, <, >,
<=,and >=, as well as the BETWEEN and LIKE operators
...
ENVIRONMENT
JDBC
To start developing with JDBC, you should setup your JDBC environment by
following the steps shown below
...
Install Java
Install J2SE Development Kit 5
...
0) from Java Official Site
...
g
...
5
...
CLASSPATH: This environment variable should have appropriate paths
set, e
...
C:\Program Files\Java\jdk1
...
0_20\jre\lib
...
g
...
5
...
It is possible you have these variable set already, but just to make sure here's
how to check
...
If you are a Windows
XP user, it is possible you have to open Performance and Maintenance,
before you will see the System icon
...
Now check if all the above mentioned variables are set properly
...
sql and javax
...
0 (JDK 5
...
Install Database
The most important thing you will need, of course is an actual running database
with a table that you can query and modify
...
You can have plenty of choices
and most common are:
MySQL DB: MySQL is an open source database
...
We recommend downloading the full Windows
installation
...
These are GUI based tools that will make
your development much easier
...
For the purpose of this tutorial, we will
assume that you have
installed
the driver at C:\Program
Files\MySQL\mysql-connector-java-5
...
8
...
1
...
1
...
jar
...
PostgreSQL DB: PostgreSQL is an open source database
...
The Postgres installation contains a GUI based administrative tool called
pgAdmin III
...
Oracle DB: Oracle DB is a commercial database sold by Oracle
...
Oracle installation includes a GUI based administrative tool called
Enterprise Manager
...
Install Database Drivers
The latest JDK includes a JDBC-ODBC Bridge driver that makes most Open
Database Connectivity (ODBC) drivers available to programmers using the JDBC
API
...
So, you should not worry about this
part
...
When you install any of
the above database, its administrator ID is set to root and gives provision to set
a password of your choice
...
There are various database operations like database creation and deletion, which
would need administrator ID and password
...
9
JDBC
If you do not have sufficient privilege to create new users, then you can ask
your Database Administrator (DBA) to create a user ID and password for you
...
exe may vary depending on the install location of
MySQL on your system
...
Step 2
Start the database server by executing the following command, if it is already
not running
...
08 sec)
mysql>
Create Data Records
Finally you create few records in Employee table as follows:
mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0
...
00 sec)
11
JDBC
mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
Query OK, 1 row affected (0
...
00 sec)
mysql>
For a complete understanding on MySQL database, study the MySQL Tutorial
...
Next chapter gives you a
sample example on JDBC Programming
...
SAMPLE CODE
JDBC
This chapter provides an example of how to create a simple JDBC application
...
All the steps mentioned in this template example, would be explained in
subsequent chapters of this tutorial
...
Most
often, using import java
...
* will suffice
...
Open a connection: Requires using
the DriverManager
...
Execute a query: Requires using an object of type Statement for building
and submitting an SQL statement to the database
...
getXXX() method to retrieve the data from the result
set
...
Sample Code
This sample example can serve as a template when you need to create your
own JDBC application in the future
...
Copy and past the following example in FirstExample
...
Import required packages
13
JDBC
import java
...
*;
public class FirstExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
out
...
");
stmt = conn
...
executeQuery(sql);
//STEP 5: Extract data from result set
while(rs
...
getInt("id");
14
JDBC
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
close();
stmt
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
15
JDBC
}//end try
System
...
println("Goodbye!");
}//end main
}//end FirstExample
Now let us compile the above example as follows:
C:\>javac FirstExample
...
Creating statement
...
DRIVER TYPES
JDBC
What is JDBC Driver?
JDBC drivers implement the defined interfaces in the JDBC API, for interacting
with your database server
...
The Java
...
Third party vendors implements the java
...
Driver interface in their
database driver
...
Sun has divided the
implementation types into four categories, Types 1, 2, 3, and 4, which is
explained below:
Type 1: JDBC-ODBC Bridge Driver
In a Type 1 driver, a JDBC bridge is used to access ODBC drivers installed on
each client machine
...
When Java first came out, this was a useful driver because most databases only
supported ODBC access but now this type of driver is recommended only for
experimental use or when no other alternative is available
...
2 is a good example of this kind
of driver
...
These drivers are typically provided by the
database vendors and used in the same manner as the JDBC-ODBC Bridge
...
If we change the Database, we have to change the native API, as it is specific to
a database and they are mostly obsolete now, but you may realize some speed
increase with a Type 2 driver, because it eliminates ODBC's overhead
...
18
JDBC
Type 3: JDBC-Net Pure Java
In a Type 3 driver, a three-tier approach is used to access databases
...
The socket information is then translated by the middleware
application server into the call format required by the DBMS, and forwarded to
the database server
...
You can think of the application server as a JDBC "proxy," meaning that it makes
calls for the client application
...
Your application server might use a Type 1, 2, or 4 driver to communicate with
the database, understanding the nuances will prove helpful
...
This is the highest performance
driver available for the database and is usually provided by the vendor itself
...
Further, these drivers can be downloaded dynamically
...
Because of the proprietary nature
of their network protocols, database vendors usually supply type 4 drivers
...
If your Java application is accessing multiple types of databases at the same
time, type 3 is the preferred driver
...
The type 1 driver is not considered a deployment-level driver, and is typically
used for development and testing purposes only
...
CONNECTIONS
JDBC
After you've installed the appropriate driver, it is time to establish a database
connection using JDBC
...
Here
are these simple four steps:
Import JDBC Packages: Add import statements to your Java program
to import required classes in your Java code
...
Database URL Formulation: This is to create a properly formatted
address that points to the database to which you wish to connect
...
Import JDBC Packages
The Import statements tell the Java compiler where to find the classes you
reference in your code and are placed at the very beginning of your source code
...
sql
...
math
...
Registering the
driver is the process by which the Oracle driver's class file is loaded into the
memory, so it can be utilized as an implementation of the JDBC interfaces
...
You can register a
driver in one of two ways
...
forName()
The
most
common
approach
to
register
a
driver
is
to
use
Java's Class
...
This method is preferable because it
allows you to make the driver registration configurable and portable
...
forName( ) to register the Oracle driver:
try {
Class
...
jdbc
...
OracleDriver");
}
catch(ClassNotFoundException ex) {
System
...
println("Error: unable to load driver class!");
System
...
forName("oracle
...
driver
...
newInstance();
}
catch(ClassNotFoundException ex) {
System
...
println("Error: unable to load driver class!");
System
...
out
...
exit(2);
catch(InstantiationException ex) {
System
...
println("Error: unable to instantiate driver!");
System
...
registerDriver()
The second approach you can use to register a driver, is to use the static
DriverManager
...
You should use the registerDriver() method if you are using a non-JDK compliant
JVM, such as the one provided by Microsoft
...
jdbc
...
OracleDriver();
DriverManager
...
out
...
exit(1);
}
Database URL Formulation
After you've loaded the driver, you can establish a connection using the
DriverManager
...
For easy reference, let me list the
three overloaded DriverManager
...
A database URL is an address that
points to your database
...
Following table lists down the popular JDBC driver names and database URL
...
mysql
...
Driver
jdbc:mysql://hostname/
databaseName
ORACLE
oracle
...
driver
...
ibm
...
jdbc
...
DB2Driver
jdbc:db2:hostname:port
Number/databaseName
Sybase
com
...
jdbc
...
Create Connection Object
We have listed down three forms of DriverManager
...
Using a Database URL with a username and password
The most commonly used form of getConnection() requires you to pass a
database URL, a username, and a password:
Assuming
you
are
using
Oracle's thin driver,
you'll
specify
host:port:databaseName value for the database portion of the URL
...
0
...
1 with a host name of amrood, and
your Oracle listener is configured to listen on port 1521, and your database
name is EMP, then complete database URL would be:
jdbc:oracle:thin:@amrood:1521:EMP
Now you have to call getConnection() method with appropriate username and
password to get a Connection object as follows:
String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
String USER = "username";
String PASS = "password"
Connection conn = DriverManager
...
getConnection( ) method requires only a
database URL:
DriverManager
...
getConnection(URL);
Using a Database URL and a Properties Object
A third form of the DriverManager
...
getConnection(String url, Properties info);
A Properties object holds a set of keyword-value pairs
...
To make the same connection made by the previous examples, use the following
code:
import java
...
*;
String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
Properties info = new Properties( );
info
...
put( "password", "password" );
Connection conn = DriverManager
...
However, if you
forget, Java's garbage collector will close the connection when it cleans up stale
objects
...
You should make a habit of always closing the
connection with the close() method associated with connection object
...
A finally block always executes, regardless of an exception occurs or not
...
close();
Explicitly closing a connection conserves DBMS resources, which will make your
database administrator happy
...
26
7
...
The
JDBC Statement, CallableStatement, and PreparedStatement interfaces define
the methods and properties that enable you to send SQL or PL/SQL commands
and receive data from your database
...
The following table provides a summary of each interface's purpose to decide on
the interface to use
...
Useful when you are using static SQL statements at
runtime
...
PreparedStatement
Use this when you plan to use the SQL statements many
times
...
CallableStatement
Use this when you want to access the database stored
procedures
...
The Statement Objects
Creating Statement Object
Before you can use a Statement object to execute a SQL statement, you need to
create one using the Connection object's createStatement( ) method, as in the
following example:
Statement stmt = null;
try {
stmt = conn
...
27
JDBC
}
catch (SQLException e) {
...
}
Once you've created a Statement object, you can then use it to execute an SQL
statement with one of its three execute methods
...
Use this
method to execute SQL DDL statements or when you need to use truly
dynamic SQL
...
Use this method to execute SQL
statements for which you expect to get a number of rows affected - for
example, an INSERT, UPDATE, or DELETE statement
...
Use
this method when you expect to get a result set, as you would with a
SELECT statement
...
A simple call to the close() method will do the job
...
However, you should
always explicitly close the Statement object to ensure proper cleanup
...
createStatement( );
...
}
finally {
28
JDBC
stmt
...
Statement Object Example
Following is the example, which makes use of the following three queries along
with the opening and closing statment:
boolean execute(String SQL): Returns a boolean value of true if a
ResultSet object can be retrieved; otherwise, it returns false
...
int executeUpdate(String SQL): Returns the number of rows affected
by the execution of the SQL statement
...
ResultSet executeQuery(String SQL): Returns a ResultSet object
...
This sample code has been written based on the environment and database
setup done in the previous chapters
...
java, compile and run as
follows:
//STEP 1
...
sql
...
mysql
...
Driver";
static final String DB_URL = "jdbc:mysql://localhost/EMP";
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
29
JDBC
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class
...
mysql
...
Driver");
//STEP 3: Open a connection
System
...
println("Connecting to database
...
getConnection(DB_URL,USER,PASS);
//STEP 4: Execute a query
System
...
println("Creating statement
...
createStatement();
String sql = "UPDATE Employees set age=30 WHERE id=103";
// Let us check if it returns a true Result Set or not
...
execute(sql);
System
...
println("Return value is : " + ret
...
executeUpdate(sql);
System
...
println("Rows impacted : " + rows );
// Let us select all the records and display them
...
executeQuery(sql);
//STEP 5: Extract data from result set
while(rs
...
getInt("id");
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
close();
stmt
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
31
JDBC
}//end JDBCExample
Now let us compile the above example as follows:
C:\>javac JDBCExample
...
Creating statement
...
This statement gives you the flexibility of supplying arguments dynamically
...
prepareStatement(SQL);
...
32
JDBC
}
finally {
...
You must supply values for every parameter before
executing the SQL statement
...
If you
forget to supply the values, you will receive an SQLException
...
The first marker
represents position 1, the next position 2, and so forth
...
All of the Statement object's methods for interacting with the database (a)
execute(), (b) executeQuery(), and (c) executeUpdate() also work with the
PreparedStatement object
...
Closing PreparedStatement Object
Just as you close a Statement object, for the same reason you should also close
the PreparedStatement object
...
If you close the Connection
object first, it will close the PreparedStatement object as well
...
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn
...
}
catch (SQLException e) {
...
close();
33
JDBC
}
For a better understanding, let us study Prepare - Example Code discussed
below
...
This sample code has been written based on the environment and database
setup done in the previous chapters
...
java, compile and run as
follows:
//STEP 1
...
sql
...
mysql
...
Driver";
static final String DB_URL = "jdbc:mysql://localhost/EMP";
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
PreparedStatement stmt = null;
try{
//STEP 2: Register JDBC driver
Class
...
mysql
...
Driver");
//STEP 3: Open a connection
System
...
println("Connecting to database
...
getConnection(DB_URL,USER,PASS);
34
JDBC
//STEP 4: Execute a query
System
...
println("Creating statement
...
prepareStatement(sql);
//Bind values into the parameters
...
setInt(1, 35);
// This would set age
stmt
...
executeUpdate();
System
...
println("Rows impacted : " + rows );
// Let us select all the records and display them
...
executeQuery(sql);
//STEP 5: Extract data from result set
while(rs
...
getInt("id");
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
close();
stmt
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
Now let us compile the above example as follows:
C:\>javac JDBCExample
...
Creating statement
...
Creating CallableStatement Object
Suppose, you need to execute the following Oracle stored procedure:
CREATE OR REPLACE PROCEDURE getEmpName
(EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS
BEGIN
SELECT first INTO EMP_FIRST
FROM Employees
WHERE ID = EMP_ID;
END;
NOTE: Above stored procedure has been written for Oracle, but we are working
with MySQL database so, let us write same stored procedure for MySQL as
follows to create it in EMP database:
DELIMITER $$
DROP PROCEDURE IF EXISTS `EMP`
...
`getEmpName`
(IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
SELECT first INTO EMP_FIRST
FROM Employees
WHERE ID = EMP_ID;
37
JDBC
END $$
DELIMITER ;
Three types of parameters exist: IN, OUT, and INOUT
...
The CallableStatement object can use all the
three
...
You bind values to IN parameters with
the setXXX() methods
...
You retrieve values from the OUT parameters with
the getXXX() methods
...
You
bind variables with the setXXX() methods and retrieve values
with the getXXX() methods
...
prepareCall() method
to
instantiate
a CallableStatement object based on the preceding stored procedure:
CallableStatement cstmt = null;
try {
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn
...
}
catch (SQLException e) {
...
38
JDBC
}
The String variable SQL, represents the stored procedure, with parameter
placeholders
...
You must bind values to all the parameters before executing the
statement, or you will receive an SQLException
...
When you use OUT and INOUT parameters you must employ an additional
CallableStatement method, registerOutParameter()
...
Once you call your stored procedure, you retrieve the value from the OUT
parameter with the appropriate getXXX() method
...
Closing CallableStatement Object
Just as you close other Statement object, for the same reason you should also
close the CallableStatement object
...
If you close the Connection
object first, it will close the CallableStatement object as well
...
CallableStatement cstmt = null;
try {
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn
...
}
catch (SQLException e) {
...
close();
}
39
JDBC
For a better understanding, I would suggest to study Callable - Example Code
...
You
can use MySQL Query Browser to get it done
...
`getEmpName` $$
CREATE PROCEDURE `EMP`
...
Copy and past the following example in JDBCExample
...
Import required packages
import java
...
*;
public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
out
...
");
String sql = "{call getEmpName (?, ?)}";
stmt = conn
...
setInt(1, empID); // This would set ID as 102
// Because second parameter is OUT so register it
stmt
...
sql
...
VARCHAR);
//Use execute method to run stored procedure
...
out
...
" );
stmt
...
getString(2);
System
...
println("Emp Name with ID:" +
empID + " is " + empName);
stmt
...
close();
}catch(SQLException se){
41
JDBC
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
Now let us compile the above example as follows:
C:\>javac JDBCExample
...
Creating statement
...
Emp Name with ID:102 is Zaid
Goodbye!
42
JDBC
C:\>
43
8
...
The SELECT statement is the standard way to select rows from a
database and view them in a result set
...
sql
...
A ResultSet object maintains a cursor that points to the current row in the result
set
...
The methods of the ResultSet interface can be broken down into three
categories:
Navigational methods: Used to move the cursor around
...
Update methods: Used to update the data in the columns of the current
row
...
The cursor is movable based on the properties of the ResultSet
...
JDBC provides the following connection methods to create statements with
desired ResultSet:
createStatement(int RSType, int RSConcurrency);
prepareStatement(String SQL, int RSType, int RSConcurrency);
prepareCall(String sql, int RSType, int RSConcurrency);
The first argument indicates the type of a ResultSet object and the second
argument is one of two ResultSet constants for specifying whether a result set is
read-only or updatable
...
If you do not specify any ResultSet type,
you will automatically get one that is TYPE_FORWARD_ONLY
...
TYPE_FORWARD_ONLY
Description
The cursor can only move forward in
44
JDBC
the result set
...
TYPE_SCROLL_INSENSITIVE
The cursor can scroll forward and
backward, and the result set is not
sensitive to changes made by others to
the database that occur after the result
set was created
...
TYPE_SCROLL_SENSITIVE
...
Concurrency of ResultSet
The possible RSConcurrency are given below
...
Concurrency
Description
ResultSet
...
This is the
default
ResultSet
...
All our examples written so far can be written as follows, which initializes a
Statement object to create a forward-only, read only ResultSet object:
try {
Statement stmt = conn
...
TYPE_FORWARD_ONLY,
ResultSet
...
}
finally {
...
N
...
2
public void afterLast() throws SQLException
Moves the cursor just after the last row
...
4
public void last() throws SQLException
Moves the cursor to the last row
...
6
public boolean relative(int row) throws SQLException
Moves the cursor the given number of rows forward or backward, from
where it is currently pointing
...
This method returns false if the
previous row is off the result set
...
This method returns false if there are
no more rows in the result set
...
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
...
Navigate - Example Code
Following is the example, which makes use of few navigation methods described
in the Result Set tutorial
...
Copy and past the following example in JDBCExample
...
Import required packages
import java
...
*;
public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
System
...
println("Creating statement
...
createStatement(
ResultSet
...
CONCUR_READ_ONLY);
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt
...
System
...
println("Moving cursor to the last
...
last();
//STEP 5: Extract data from result set
System
...
println("Displaying record
...
getInt("id");
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
System
...
println("Moving cursor to the first row
...
first();
//STEP 6: Extract data from result set
System
...
println("Displaying record
...
getInt("id");
age = rs
...
getString("first");
last = rs
...
out
...
out
...
out
...
out
...
System
...
println("Moving cursor to the next row
...
next();
//STEP 7: Extract data from result set
System
...
println("Displaying record
...
getInt("id");
age = rs
...
getString("first");
last = rs
...
out
...
out
...
out
...
out
...
close();
stmt
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
Now let us compile the above example as follows:
50
JDBC
C:\>javac JDBCExample
...
Creating statement
...
Displaying record
...
Displaying record
...
Displaying record
...
There is a get method for each of the possible data types, and each get method
has two versions:
One that takes in a column name
...
For example, if the column you are interested in viewing contains an int, you
need to use one of the getInt() methods of ResultSet:
S
...
1
Methods & Description
public int getInt(String columnName) throws SQLException
Returns the int in the current row in the column named columnName
...
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
...
lang
...
lang
...
net
...
There are also methods for getting SQL data types java
...
Date, java
...
Time,
java
...
TimeStamp, java
...
Clob, and java
...
Blob
...
For a better understanding, let us study the Viewing - Example Code as
discussed below
...
This
example is very similar to previous example explained in the Navigation Result
Set Section
...
Copy and past the following example in JDBCExample
...
Import required packages
import java
...
*;
public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
System
...
println("Creating statement
...
createStatement(
ResultSet
...
CONCUR_READ_ONLY);
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt
...
System
...
println("Moving cursor to the last
...
last();
//STEP 5: Extract data from result set
System
...
println("Displaying record
...
getInt("id");
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
System
...
println("Moving cursor to the first row
...
first();
//STEP 6: Extract data from result set
System
...
println("Displaying record
...
getInt("id");
age = rs
...
getString("first");
last = rs
...
out
...
out
...
out
...
out
...
System
...
println("Moving cursor to the next row
...
next();
//STEP 7: Extract data from result set
System
...
println("Displaying record
...
getInt("id");
age = rs
...
getString("first");
last = rs
...
out
...
out
...
out
...
out
...
close();
stmt
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
55
JDBC
Now let us compile the above example as follows:
C:\>javac JDBCExample
...
Creating statement
...
Displaying record
...
Displaying record
...
Displaying record
...
As with the get methods, there are two update methods for each data type:
One that takes in a column name
...
For example, to update a String column of the current row of a result set, you
would use one of the following updateString() methods:
S
...
1
Methods & Description
public void updateString(int columnIndex, String s) throws
SQLException
56
JDBC
Changes the String in the specified column to the value of s
...
There are update methods for the eight primitive data types, as well as String,
Object, URL, and the SQL data types in the java
...
Updating a row in the result set changes the columns of the current row in the
ResultSet object, but not in the underlying database
...
S
...
1
Methods & Description
public void updateRow()
Updates the current row by updating the corresponding row in the
database
...
3
public void refreshRow()
Refreshes the data in the result set to reflect any recent changes in the
database
...
5
public void insertRow()
Inserts a row into the database
...
For a better understanding, let us study the Updating - Example Code as
discussed below
...
CONCUR_UPDATABLE and
ResultSet
...
This example would explain INSERT, UPDATE and DELETE operation on a table
...
This sample code has been written based on the environment and database
setup done in the previous chapters
...
java, compile and run as
follows:
//STEP 1
...
sql
...
mysql
...
Driver";
static final String DB_URL = "jdbc:mysql://localhost/EMP";
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
try{
//STEP 2: Register JDBC driver
Class
...
mysql
...
Driver");
//STEP 3: Open a connection
System
...
println("Connecting to database
...
getConnection(DB_URL,USER,PASS);
//STEP 4: Execute a query to create statment with
58
JDBC
// required arguments for RS example
...
out
...
");
Statement stmt = conn
...
TYPE_SCROLL_INSENSITIVE,
ResultSet
...
executeQuery(sql);
System
...
println("List result set for reference
...
beforeFirst();
//STEP 7: Extract data from result set
while(rs
...
getInt("age") + 5;
rs
...
updateRow();
}
System
...
println("List result set showing new ages
...
//Move to insert row and add column data with updateXXX()
System
...
println("Inserting a new record
...
moveToInsertRow();
rs
...
updateString("first","John");
rs
...
updateInt("age",40);
//Commit row
rs
...
out
...
");
printRs(rs);
// Delete second record from the table
...
absolute( 2 );
System
...
println("List the record before deleting
...
getInt("id");
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
deleteRow();
System
...
println("List result set after \
deleting one records
...
close();
stmt
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se){
se
...
out
...
beforeFirst();
while(rs
...
getInt("id");
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
out
...
java
C:\>
When you run JDBCExample, it produces the following result:
C:\>java JDBCExample
Connecting to database
...
List result set for reference
...
ID: 100, Age: 38, First: Zara, Last: Ali
ID: 101, Age: 45, First: Mahnaz, Last: Fatma
ID: 102, Age: 55, First: Zaid, Last: Khan
ID: 103, Age: 50, First: Sumit, Last: Mittal
Inserting a new record
...
ID: 100, Age: 38, First: Zara, Last: Ali
ID: 101, Age: 45, First: Mahnaz, Last: Fatma
ID: 102, Age: 55, First: Zaid, Last: Khan
ID: 103, Age: 50, First: Sumit, Last: Mittal
ID: 104, Age: 40, First: John, Last: Paul
List the record before deleting
...
ID: 100, Age: 38, First: Zara, Last: Ali
ID: 102, Age: 55, First: Zaid, Last: Khan
ID: 103, Age: 50, First: Sumit, Last: Mittal
62
JDBC
ID: 104, Age: 40, First: John, Last: Paul
Goodbye!
C:\>
63
JDBC
9
...
It uses a default mapping for most data types
...
Default mappings were
created to provide consistency between drivers
...
updateXXX()
method
...
lang
...
lang
...
lang
...
math
...
sql
...
sql
...
sql
...
sql
...
sql
...
sql
...
sql
...
sql
...
0 has enhanced support for BLOB, CLOB, ARRAY, and REF data types
...
The setXXX() and updateXXX() methods enable you to convert specific Java
types to specific JDBC data types
...
ResultSet object provides corresponding getXXX() method for each data type to
retrieve column value
...
SQL
JDBC/Java
setXXX
getXXX
VARCHAR
java
...
String
setString
getString
CHAR
java
...
String
setString
getString
LONGVARCHAR
java
...
String
setString
getString
65
JDBC
BIT
boolean
setBoolean
getBoolean
NUMERIC
java
...
BigDecimal
setBigDecimal
getBigDecimal
TINYINT
byte
setByte
getByte
SMALLINT
short
setShort
getShort
INTEGER
int
setInt
getInt
BIGINT
long
setLong
getLong
REAL
float
setFloat
getFloat
FLOAT
float
setFloat
getFloat
DOUBLE
double
setDouble
getDouble
VARBINARY
byte[ ]
setBytes
getBytes
BINARY
byte[ ]
setBytes
getBytes
DATE
java
...
Date
setDate
getDate
TIME
java
...
Time
setTime
getTime
TIMESTAMP
java
...
Timestamp
setTimestamp
getTimestamp
CLOB
java
...
Clob
setClob
getClob
BLOB
java
...
Blob
setBlob
getBlob
ARRAY
java
...
Array
setARRAY
getARRAY
REF
java
...
Ref
SetRef
getRef
STRUCT
java
...
Struct
SetStruct
getStruct
66
JDBC
Date & Time Data Types
The java
...
Date class maps to the SQL DATE type, and the java
...
Time and
java
...
Timestamp classes map to the SQL TIME and SQL TIMESTAMP data
types, respectively
...
import java
...
Date;
import java
...
Time;
import java
...
Timestamp;
import java
...
*;
public class SqlDateTime {
public static void main(String[] args) {
//Get standard date and time
java
...
Date javaDate = new java
...
Date();
long javaTime = javaDate
...
out
...
toString());
//Get and display SQL DATE
java
...
Date sqlDate = new java
...
Date(javaTime);
System
...
println("The SQL DATE is: " +
sqlDate
...
sql
...
sql
...
out
...
toString());
//Get and display SQL TIMESTAMP
java
...
Timestamp sqlTimestamp =
new java
...
Timestamp(javaTime);
System
...
println("The SQL TIMESTAMP is: " +
sqlTimestamp
...
java
C:\>
When you run JDBCExample, it produces the following result:
C:\>java SqlDateTime
The Java Date is:Tue Aug 18 13:46:02 GMT+04:00 2009
The SQL DATE is: 2009-08-18
The SQL TIME is: 13:46:02
The SQL TIMESTAMP is: 2009-08-18 13:46:02
...
So, to
handle SQL NULL values in Java, there are three tactics you can use:
Avoid using getXXX( ) methods that return primitive data types
...
Use primitive data types and the ResultSet object's wasNull( ) method to
test whether the primitive variable that received the value returned by the
getXXX( ) method should be set to an acceptable value that you've
chosen to represent a NULL
...
createStatement( );
String sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt
...
getInt(1);
if( rs
...
TRANSACTIONS
JDBC
If your JDBC Connection is in auto-commit mode, which it is by default, then
every SQL statement is committed to the database upon its completion
...
Transactions enable you to control if, and when, changes are applied to the
database
...
To enable manual- transaction support instead of the auto-commit mode that the
JDBC
driver
uses
by
default,
use
the
Connection
object's setAutoCommit() method
...
You can pass a boolean true to
turn it back on again
...
setAutoCommit(false);
Commit & Rollback
Once you are done with your changes and you want to commit the changes then
call commit() method on connection object as follows:
conn
...
rollback( );
The following example illustrates the use of a commit and rollback object:
try{
//Assume a valid connection object conn
conn
...
createStatement();
String SQL = "INSERT INTO Employees
" +
"VALUES (106, 20, 'Rita', 'Tez')";
stmt
...
executeUpdate(SQL);
// If there is no error
...
commit();
}catch(SQLException se){
// If there is any error
...
rollback();
}
In this case, none of the above INSERT statement would success and everything
would be rolled back
...
Commit - Example Code
Following is the example, which makes use of commit and rollback described
in the Transaction tutorial
...
Copy and past the following example in JDBCExample
...
Import required packages
import java
...
*;
public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
conn
...
System
...
println("Creating statement
...
createStatement(
ResultSet
...
CONCUR_UPDATABLE);
//STEP 6: INSERT a row into Employees table
System
...
println("Inserting one row
...
executeUpdate(SQL);
//STEP 7: INSERT one more row into Employees table
SQL = "INSERT INTO Employees " +
71
JDBC
"VALUES (107, 22, 'Sita', 'Singh')";
stmt
...
System
...
println("Commiting data here
...
commit();
//STEP 9: Now list all the available records
...
executeQuery(sql);
System
...
println("List result set for reference
...
close();
stmt
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
System
...
println("Rolling back data here
...
rollback();
}catch(SQLException se2){
se2
...
forName
e
...
close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
public static void printRs(ResultSet rs) throws SQLException{
//Ensure we start with first row
rs
...
next()){
//Retrieve by column name
int id
= rs
...
getInt("age");
String first = rs
...
getString("last");
//Display values
System
...
print("ID: " + id);
System
...
print(", Age: " + age);
System
...
print(", First: " + first);
System
...
println(", Last: " + last);
}
System
...
println();
}//end printRs()
73
JDBC
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Creating statement
...
Commiting data here
...
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
ID: 106, Age: 20, First: Rita, Last: Tez
ID: 107, Age: 22, First: Sita, Last: Singh
Goodbye!
C:\>
Using Savepoints
The new JDBC 3
...
Most modern DBMS, support savepoints within their environments such
as Oracle's PL/SQL
...
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
...
It also
returns a Savepoint object
...
Notice that it requires a Savepoint object as a parameter
...
74
JDBC
There is one rollback (String savepointName) method, which rolls back work
to the specified savepoint
...
setAutoCommit(false);
Statement stmt = conn
...
setSavepoint("Savepoint1");
String SQL = "INSERT INTO Employees " +
"VALUES (106, 20, 'Rita', 'Tez')";
stmt
...
executeUpdate(SQL);
// If there is no error, commit the changes
...
commit();
}catch(SQLException se){
// If there is any error
...
rollback(savepoint1);
}
In this case, none of the above INSERT statement would success and everything
would be rolled back
...
Savepoints - Example Code
Following is the example, which makes use of setSavepoint and rollback
described in the Transaction tutorial
...
75
JDBC
Copy and past the following example in JDBCExample
...
Import required packages
import java
...
*;
public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
conn
...
System
...
println("Creating statement
...
createStatement();
//STEP 6: Now list all the available records
...
executeQuery(sql);
System
...
println("List result set for reference
...
Savepoint savepoint1 = conn
...
out
...
");
String SQL = "DELETE FROM Employees " +
"WHERE ID = 110";
stmt
...
we deleted too wrong employees!
//STEP 8: Rollback the changes afetr save point 2
...
rollback(savepoint1);
// STEP 9: delete rows having ID grater than 104
// But save point before doing so
...
setSavepoint("ROWS_DELETED_2");
System
...
println("Deleting row
...
executeUpdate(SQL);
//STEP 10: Now list all the available records
...
executeQuery(sql);
System
...
println("List result set for reference
...
close();
stmt
...
close();
77
JDBC
}catch(SQLException se){
//Handle errors for JDBC
se
...
System
...
println("Rolling back data here
...
rollback();
}catch(SQLException se2){
se2
...
forName
e
...
close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
public static void printRs(ResultSet rs) throws SQLException{
//Ensure we start with first row
78
JDBC
rs
...
next()){
//Retrieve by column name
int id
= rs
...
getInt("age");
String first = rs
...
getString("last");
//Display values
System
...
print("ID: " + id);
System
...
print(", Age: " + age);
System
...
print(", First: " + first);
System
...
println(", Last: " + last);
}
System
...
println();
}//end printRs()
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Creating statement
...
ID: 95, Age: 20, First: Sima, Last: Chug
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 30, First: Sumit, Last: Mittal
ID: 110, Age: 20, First: Sima, Last: Chug
79
JDBC
Deleting row
...
List result set for reference
...
EXCEPTIONS
JDBC
Exception handling allows you to handle exceptional conditions such as programdefined errors in a controlled fashion
...
The term thrown
means that current program execution stops, and the control is redirected to the
nearest applicable catch clause
...
JDBC Exception handling is very similar to the Java Excpetion handling but for
JDBC, the most common exception you'll deal with is java
...
SQLException
...
When such an
exception occurs, an object of type SQLException will be passed to the catch
clause
...
getMessage( )
Gets the JDBC driver's error message for an
error, handled by the driver or gets the Oracle
error number and message for a database
error
...
For a JDBC
driver error, no useful information is returned
from this method
...
This method can return null
...
81
JDBC
printStackTrace( )
Prints the current exception, or throwable, and
its backtrace to a standard error stream
...
printStackTrace(PrintWriter
w)
Prints this throwable and it’s backtrace to the
print writer you specify
...
Here is the general form
of a try block:
try {
// Your risky code goes between these curly braces!!!
}
catch(Exception ex) {
// Your exception handling code goes between these
// curly braces, similar to the exception clause
// in a PL/SQL block
...
Like closing database connection
...
catch
...
code
to
understand
the
usage
//STEP 1
...
sql
...
mysql
...
Driver";
static final String DB_URL = "jdbc:mysql://localhost/EMP";
82
JDBC
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
try{
//STEP 2: Register JDBC driver
Class
...
mysql
...
Driver");
//STEP 3: Open a connection
System
...
println("Connecting to database
...
getConnection(DB_URL,USER,PASS);
//STEP 4: Execute a query
System
...
println("Creating statement
...
createStatement();
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt
...
next()){
//Retrieve by column name
int id
= rs
...
getInt("age");
String first = rs
...
getString("last");
//Display values
System
...
print("ID: " + id);
System
...
print(", Age: " + age);
System
...
print(", First: " + first);
System
...
println(", Last: " + last);
83
JDBC
}
//STEP 6: Clean-up environment
rs
...
close();
conn
...
printStackTrace();
}catch(Exception e){
//Handle errors for Class
...
printStackTrace();
}finally{
//finally block used to close resources
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Creating statement
...
85
12
...
When you send several SQL statements to the database at once, you reduce the
amount of communication overhead, thereby improving performance
...
You should use the
DatabaseMetaData
...
The method returns
true if your JDBC driver supports this feature
...
The executeBatch() is used to start the
execution of all the statements grouped together
...
Just as you can add statements to a batch for processing, you can remove
them with the clearBatch() method
...
However, you cannot
selectively choose which statement to remove
...
Set auto-commit to false using setAutoCommit()
...
Execute all the SQL statements using executeBatch() method on created
statement object
...
into
batch
Example
The following code snippet provides an example of a batch update using
Statement object:
86
JDBC
// Create statement object
Statement stmt = conn
...
setAutoCommit(false);
// Create SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
"VALUES(200,'Zia', 'Ali', 30)";
// Add above SQL statement in the batch
...
addBatch(SQL);
// Create one more SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
"VALUES(201,'Raj', 'Kumar', 35)";
// Add above SQL statement in the batch
...
addBatch(SQL);
// Create one more SQL statement
String SQL = "UPDATE Employees SET age = 35 " +
"WHERE id = 100";
// Add above SQL statement in the batch
...
addBatch(SQL);
// Create an int[] to hold returned values
int[] count = stmt
...
commit();
For a better understanding, let us study the Batching - Example Code as
discussed below
...
Create a Statement object using either createStatement() methods
...
Set auto-commit to false using setAutoCommit()
...
Add
as
many
as
SQL
statements
you
like
using addBatch() method on created statement object
...
Execute all the SQL statements using executeBatch() method on created
statement object
...
Finally, commit all the changes using commit() method
...
Copy and past the following example in JDBCExample
...
sql
...
mysql
...
Driver";
static final String DB_URL = "jdbc:mysql://localhost/EMP";
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
// Register JDBC driver
Class
...
mysql
...
Driver");
// Open a connection
88
JDBC
System
...
println("Connecting to database
...
getConnection(DB_URL,USER,PASS);
// Create statement
System
...
println("Creating statement
...
createStatement();
// Set auto-commit to false
conn
...
printRows( stmt );
// Create SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
"VALUES(200,'Zia', 'Ali', 30)";
// Add above SQL statement in the batch
...
addBatch(SQL);
// Create one more SQL statement
SQL = "INSERT INTO Employees (id, first, last, age) " +
"VALUES(201,'Raj', 'Kumar', 35)";
// Add above SQL statement in the batch
...
addBatch(SQL);
// Create one more SQL statement
SQL = "UPDATE Employees SET age = 35 " +
"WHERE id = 100";
// Add above SQL statement in the batch
...
addBatch(SQL);
// Create an int[] to hold returned values
int[] count = stmt
...
commit();
// Again, let us select all the records and display them
...
close();
conn
...
printStackTrace();
}catch(Exception e){
//Handle errors for Class
...
printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt
...
close();
}catch(SQLException se){
se
...
out
...
out
...
");
90
JDBC
// Let us select all the records and display them
...
executeQuery(sql);
while(rs
...
getInt("id");
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
out
...
close();
}//end printRows()
}//end JDBCExample
Now let us compile the above example as follows:
C:\>javac JDBCExample
...
Creating statement
...
ID: 95, Age: 20, First: Sima, Last: Chug
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
91
JDBC
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 30, First: Sumit, Last: Mittal
ID: 110, Age: 20, First: Sima, Last: Chug
Displaying available rows
...
Create SQL statements with placeholders
...
Create PrepareStatement
methods
...
Set auto-commit to false using setAutoCommit()
...
Add
as
many
as
SQL
statements
you
like
using addBatch() method on created statement object
...
Execute all the SQL statements using executeBatch() method on created
statement object
...
Finally, commit all the changes using commit() method
...
prepareStatement(SQL);
//Set auto-commit to false
conn
...
setInt( 1, 400 );
pstmt
...
setString( 3, "Singh" );
pstmt
...
addBatch();
// Set the variables
pstmt
...
setString( 2, "Pawan" );
pstmt
...
setInt( 4, 31 );
// Add it to the batch
pstmt
...
...
...
executeBatch();
//Explicitly commit statements to apply changes
conn
...
Batching - Example Code
Here is a typical sequence
PrepareStatement Object:
of
steps
to
use
Batch
Processing
with
Create SQL statements with placeholders
...
Set auto-commit to false using setAutoCommit()
...
Execute all the SQL statements using executeBatch() method on created
statement object
...
object
using
either
prepareStatement()
into
batch
This sample code has been written based on the environment and database
setup done in the previous chapters
...
java, compile and run as
follows:
// Import required packages
import java
...
*;
public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
out
...
");
stmt = conn
...
setAutoCommit(false);
// First, let us select all the records and display them
...
setInt( 1, 400 );
stmt
...
setString( 3, "Singh" );
stmt
...
addBatch();
// Set the variables
stmt
...
setString( 2, "Pawan" );
stmt
...
setInt( 4, 31 );
// Add it to the batch
stmt
...
executeBatch();
//Explicitly commit statements to apply changes
conn
...
printRows( stmt );
// Clean-up environment
stmt
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn
...
printStackTrace();
96
JDBC
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
public static void printRows(Statement stmt) throws SQLException{
System
...
println("Displaying available rows
...
String sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt
...
next()){
//Retrieve by column name
int id
= rs
...
getInt("age");
String first = rs
...
getString("last");
//Display values
System
...
print("ID: " + id);
System
...
print(", Age: " + age);
System
...
print(", First: " + first);
System
...
println(", Last: " + last);
}
System
...
println();
rs
...
java
C:\>
When you run JDBCExample, it produces following result:
97
JDBC
C:\>java JDBCExample
Connecting to database
...
Displaying available rows
...
ID: 95, Age: 20, First: Sima, Last: Chug
ID: 100, Age: 35, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 30, First: Sumit, Last: Mittal
ID: 110, Age: 20, First: Sima, Last: Chug
ID: 200, Age: 30, First: Zia, Last: Ali
ID: 201, Age: 35, First: Raj, Last: Kumar
ID: 400, Age: 33, First: Pappu, Last: Singh
ID: 401, Age: 31, First: Pawan, Last: Singh
Goodbye!
C:\>
98
13
...
This chapter is similar to that section, but it
would give you additional information about JDBC SQL escape syntax
...
Creating CallableStatement Object
Suppose, you need to execute the following Oracle stored procedure:
CREATE OR REPLACE PROCEDURE getEmpName
(EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS
BEGIN
SELECT first INTO EMP_FIRST
FROM Employees
WHERE ID = EMP_ID;
END;
NOTE: Above stored procedure has been written for Oracle, but we are working
with MySQL database so, let us write same stored procedure for MySQL as
follows to create it in EMP database:
DELIMITER $$
DROP PROCEDURE IF EXISTS `EMP`
...
`getEmpName`
(IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
SELECT first INTO EMP_FIRST
FROM Employees
WHERE ID = EMP_ID;
END $$
99
JDBC
DELIMITER ;
Three types of parameters exist: IN, OUT, and INOUT
...
The CallableStatement object can use all the
three
...
You bind values to IN parameters with
the setXXX() methods
...
You retrieve values from the OUT parameters with
the getXXX() methods
...
You
bind variables with the setXXX() methods and retrieve values
with the getXXX() methods
...
prepareCall() method to instantiate a CallableStatement object
based on the preceding stored procedure:
CallableStatement cstmt = null;
try {
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn
...
}
catch (SQLException e) {
...
}
100
JDBC
The String variable SQL represents the stored procedure, with parameter
placeholders
...
You must bind values to all the parameters before executing the statement, or
you will receive an SQLException
...
When you use OUT and INOUT parameters, you must employ an additional
CallableStatement method, registerOutParameter()
...
Once you call your stored procedure, you retrieve the value from the OUT
parameter with the appropriate getXXX() method
...
Closing CallableStatement Object:
Just as you close other Statement object, for the same reason you should also
close the CallableStatement object
...
If you close the Connection
object first, it will close the CallableStatement object as well
...
CallableStatement cstmt = null;
try {
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn
...
}
catch (SQLException e) {
...
close();
}
We have studied more details in the Callable - Example Code section earlier
...
The general SQL escape syntax format is as follows:
{keyword 'parameters'}
Here are the following escape sequences, which you would find very useful while
performing the JDBC programming:
d, t, ts Keywords
They help identify date, time, and timestamp literals
...
This escape syntax tells the
driver to render the date or time in the target database's format
...
Using this syntax {d '2009-0903'} is March 9, 2009
...
createStatement();
//Insert data ==> ID, First Name, Last Name, DOB
String sql="INSERT INTO STUDENTS VALUES" +
"(100,'Zara','Ali', {d '2001-12-16'})";
stmt
...
Using this syntax {t '13:30:29'}
is 1:30:29 PM
...
102
JDBC
escape Keyword
This keyword identifies the escape character used in LIKE clauses
...
For example:
String sql = "SELECT symbol FROM MathSymbols
WHERE symbol LIKE '\%' {escape '\'}";
stmt
...
fn Keyword
This keyword represents scalar functions used in a DBMS
...
call Keyword
This keywork is used to call the stored procedures
...
The syntax is as follows:
{oj outer-join}
Where outer-join = table {LEFT|RIGHT|FULL} OUTERJOIN {table | outer-join}
on search-condition
...
execute(sql);
104
14
...
This enables you to place entire files into database
columns that can hold large values, such as CLOB and BLOB data types
...
setCharacterStream(): This method is used to supply large UNICODE
values
...
The setXXXStream() method requires an extra parameter, the file size, besides
the parameter placeholder
...
Example
Consider we want to upload an XML file XML_Data
...
Here is the content of this XML file:
...
This example would create a database table
XML_Data
...
XML_Data
and
then
file
Copy and past the following example in JDBCExample
...
sql
...
io
...
util
...
mysql
...
Driver";
static final String DB_URL = "jdbc:mysql://localhost/EMP";
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
Statement stmt = null;
ResultSet rs = null;
try{
// Register JDBC driver
Class
...
mysql
...
Driver");
// Open a connection
System
...
println("Connecting to database
...
getConnection(DB_URL,USER,PASS);
//Create a Statement object and build table
stmt = conn
...
xml");
long fileLength = f
...
prepareStatement(SQL);
pstmt
...
setAsciiStream(2,fis,(int)fileLength);
pstmt
...
close();
// Do a query to get the row
SQL = "SELECT Data FROM XML_Data WHERE id=100";
rs = stmt
...
next ()){
//Retrieve data from input stream
InputStream xmlInputStream = rs
...
read ()) != -1)
bos
...
out
...
toString());
}
// Clean-up environment
rs
...
close();
pstmt
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se2){
}// nothing we can do
try{
if(pstmt!=null)
pstmt
...
close();
}catch(SQLException se){
se
...
out
...
out
...
" );
//Create SQL Statement
String streamingDataSql = "CREATE TABLE XML_Data " +
"(id INTEGER, Data LONG)";
//Drop table first if it exists
...
executeUpdate("DROP TABLE XML_Data");
}catch(SQLException se){
}// do nothing
//Build table
...
executeUpdate(streamingDataSql);
}//end createXMLTable
}//end JDBCExample
Now let us compile the above example as follows:
C:\>javac JDBCExample
...
Creating XML_Data table
...
0"?>
Goodbye!
C:\>
109
15
...
Before executing the following example, make sure you have the
following in place:
You should have admin privilege to create a database in the given
schema
...
Your MySQL or whatever database you are using, is up and running
...
Most
often, using import java
...
* will suffice
...
Open a connection: Requires using
the DriverManager
...
To create a new database, you need not give any database name while
preparing database URL as mentioned in the below example
...
Clean up the environment: Requires explicitly closing all database
resources versus relying on the JVM's garbage collection
...
java, compile and run as
follows:
//STEP 1
...
sql
...
mysql
...
Driver";
static final String DB_URL = "jdbc:mysql://localhost/";
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class
...
mysql
...
Driver");
//STEP 3: Open a connection
System
...
println("Connecting to database
...
getConnection(DB_URL, USER, PASS);
//STEP 4: Execute a query
System
...
println("Creating database
...
createStatement();
String sql = "CREATE DATABASE STUDENTS";
stmt
...
out
...
");
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Creating database
...
Goodbye!
C:\>
112
16
...
Before executing the following example, make sure you have the
following in place:
To
execute
the
following
example
you
need
to
replace
the username and password with your actual user name and password
...
Required Steps
The following steps are required to create a new Database using JDBC
application:
Import the packages: Requires that you include the packages
containing the JDBC classes needed for the database programming
...
sql
...
Register the JDBC driver: Requires that you initialize a driver so you
can open a communications channel with the database
...
getConnection() method to create a Connection object,
which represents a physical connection with a selected database
...
Following
example would make connection with STUDENTS database
...
Sample Code
Copy and past the following example in JDBCExample
...
Import required packages
import java
...
*;
public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
out
...
");
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se){
se
...
out
...
java
C:\>
When you run JDBCExample, it produces the following result:
C:\>java JDBCExample
Connecting to a selected database
...
Goodbye!
C:\>
115
17
...
Before executing the following example, make sure you have
the following in place:
To
execute
the
following
example
you
need
to
replace
the username and password with your actual user name and password
...
NOTE: This is a serious operation and you have to make a firm decision before
proceeding to delete a database because everything you have in your database
would be lost
...
Most
often, using import java
...
* will suffice
...
Open a connection: Requires using
the DriverManager
...
Deleting a database does not require database name to be in your
database URL
...
Execute a query: Requires using an object of type Statement for building
and submitting an SQL statement to delete the database
...
Sample Code
Copy and paste the following example in JDBCExample
...
Import required packages
import java
...
*;
116
JDBC
public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
out
...
");
//STEP 4: Execute a query
System
...
println("Deleting database
...
createStatement();
String sql = "DROP DATABASE STUDENTS";
stmt
...
out
...
");
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
117
JDBC
e
...
close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Connected database successfully
...
Database deleted successfully
...
CREATE TABLES
JDBC
This chapter provides an example on how to create a table using JDBC
application
...
Your MySQL or whatever database you are using, is up and running
...
Most
often, using import java
...
* will suffice
...
Open a connection: Requires using
the DriverManager
...
Execute a query: Requires using an object of type Statement for building
and submitting an SQL statement to create a table in a seleted database
...
Sample Code
Copy and paste the following example in JDBCExample
...
Import required packages
import java
...
*;
public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
out
...
");
//STEP 4: Execute a query
System
...
println("Creating table in given database
...
createStatement();
String sql = "CREATE TABLE REGISTRATION " +
"(id INTEGER not NULL, " +
" first VARCHAR(255), " +
" last VARCHAR(255), " +
" age INTEGER, " +
" PRIMARY KEY ( id ))";
stmt
...
out
...
");
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Connected database successfully
...
Created table in given database
...
DROP TABLES
JDBC
This chapter provides an example on how to delete a table using JDBC
application
...
Your MySQL or whatever database you are using, is up and running
...
Required Steps
The following steps are required to create a new Database using JDBC
application:
Import the packages: Requires that you include the packages
containing the JDBC classes needed for database programming
...
sql
...
Register the JDBC driver: Requires that you initialize a driver so, you
can open a communications channel with the database
...
getConnection() method to create a Connection object,
which represents a physical connection with a database server
...
Clean up the environment: Requires explicitly closing all database
resources versus relying on the JVM's garbage collection
...
java, compile and run as
follows:
//STEP 1
...
sql
...
mysql
...
Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class
...
mysql
...
Driver");
//STEP 3: Open a connection
System
...
println("Connecting to a selected database
...
getConnection(DB_URL, USER, PASS);
System
...
println("Connected database successfully
...
out
...
");
stmt = conn
...
executeUpdate(sql);
System
...
println("Table
deleted in given database
...
printStackTrace();
}catch(Exception e){
//Handle errors for Class
...
printStackTrace();
123
JDBC
}finally{
//finally block used to close resources
try{
if(stmt!=null)
conn
...
close();
}catch(SQLException se){
se
...
out
...
java
C:\>
When you run JDBCExample, it produces the following result:
C:\>java JDBCExample
Connecting to a selected database
...
Deleting table in given database
...
Goodbye!
C:\>
124
20
...
Before executing following example, make sure you have the
following in place:
To
execute
the
following
example
you
can
replace
the username and password with your actual user name and password
...
Required Steps
The following steps are required to create a new Database using JDBC
application:
Import the packages: Requires that you include the packages
containing the JDBC classes needed for database programming
...
sql
...
Register the JDBC driver: Requires that you initialize a driver so you
can open a communications channel with the database
...
getConnection() method to create a Connection object,
which represents a physical connection with a database server
...
Clean up the environment: Requires explicitly closing all database
resources versus relying on the JVM's garbage collection
...
java, compile and run as
follows:
//STEP 1
...
sql
...
mysql
...
Driver";
125
JDBC
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class
...
mysql
...
Driver");
//STEP 3: Open a connection
System
...
println("Connecting to a selected database
...
getConnection(DB_URL, USER, PASS);
System
...
println("Connected database successfully
...
out
...
");
stmt = conn
...
executeUpdate(sql);
sql = "INSERT INTO Registration " +
"VALUES (101, 'Mahnaz', 'Fatma', 25)";
stmt
...
executeUpdate(sql);
sql = "INSERT INTO Registration " +
"VALUES(103, 'Sumit', 'Mittal', 28)";
stmt
...
out
...
");
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Connected database successfully
...
Inserted records into the table
...
SELECT RECORDS
JDBC
This chapter provides an example on how to select/fetch records from a table
using JDBC application
...
Your MySQL or whatever database you are using, is up and running
...
Most
often, using import java
...
* will suffice
...
Open a connection: Requires using
the DriverManager
...
Execute a query: Requires using an object of type Statement for building
and submitting an SQL statement to select (i
...
fetch) records from a
table
...
Clean up the environment: Requires explicitly closing all database
resources versus relying on the JVM's garbage collection
...
java, compile and run as
follows:
//STEP 1
...
sql
...
mysql
...
Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class
...
mysql
...
Driver");
//STEP 3: Open a connection
System
...
println("Connecting to a selected database
...
getConnection(DB_URL, USER, PASS);
System
...
println("Connected database successfully
...
out
...
");
stmt = conn
...
executeQuery(sql);
//STEP 5: Extract data from result set
while(rs
...
getInt("id");
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Connected database successfully
...
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Goodbye!
C:\>
132
22
...
Before executing the following example, make sure you have
the following in place:
To
execute
the
following
example
you
can
replace
the username and password with your actual user name and password
...
Required Steps
The following steps are required to create a new Database using JDBC
application:
Import the packages: Requires that you include the packages
containing the JDBC classes needed for database programming
...
sql
...
Register the JDBC driver: Requires that you initialize a driver so you
can open a communications channel with the database
...
getConnection() method to create a Connection object,
which represents a physical connection with a database server
...
This Query
makes use of IN and WHERE clause to update conditional records
...
Sample Code
Copy and paste the following example in JDBCExample
...
Import required packages
import java
...
*;
public class JDBCExample {
// JDBC driver name and database URL
133
JDBC
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
out
...
");
//STEP 4: Execute a query
System
...
println("Creating statement
...
createStatement();
String sql = "UPDATE Registration " +
"SET age = 30 WHERE id in (100, 101)";
stmt
...
executeQuery(sql);
while(rs
...
getInt("id");
134
JDBC
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
135
JDBC
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Connected database successfully
...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 101, Age: 30, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Goodbye!
C:\>
136
23
...
Before executing following example, make sure you have the
following in place:
To
execute
the
following
example
you
can
replace
the username and password with your actual user name and password
...
Required Steps
The following steps are required to create a new Database using JDBC
application:
Import the packages: Requires that you include the packages
containing the JDBC classes needed for database programming
...
sql
...
Register the JDBC driver: Requires that you initialize a driver so you
can open a communications channel with the database
...
getConnection() method to create a Connection object,
which represents a physical connection with a database server
...
This
Query makes use of the WHERE clause to delete conditional records
...
Sample Code
Copy and paste the following example in JDBCExample
...
Import required packages
import java
...
*;
public class JDBCExample {
// JDBC driver name and database URL
137
JDBC
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
out
...
");
//STEP 4: Execute a query
System
...
println("Creating statement
...
createStatement();
String sql = "DELETE FROM Registration " +
"WHERE id = 101";
stmt
...
executeQuery(sql);
while(rs
...
getInt("id");
138
JDBC
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
139
JDBC
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Connected database successfully
...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Goodbye!
C:\>
140
24
...
This would add additional conditions using WHERE clause while
selecting records from the table
...
Your MySQL or whatever database you are using, is up and running
...
Most
often, using import java
...
* will suffice
...
Open a connection: Requires using
the DriverManager
...
Execute a query: Requires using an object of type Statement for building
and submitting an SQL statement to fetch records from a table, which
meet the given condition
...
Clean up the environment: Requires explicitly closing all database
resources versus relying on the JVM's garbage collection
...
java, compile and run as
follows:
//STEP 1
...
sql
...
mysql
...
Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class
...
mysql
...
Driver");
//STEP 3: Open a connection
System
...
println("Connecting to a selected database
...
getConnection(DB_URL, USER, PASS);
System
...
println("Connected database successfully
...
out
...
");
stmt = conn
...
System
...
println("Fetching records without condition
...
executeQuery(sql);
while(rs
...
getInt("id");
int age = rs
...
getString("first");
142
JDBC
String last = rs
...
out
...
out
...
out
...
out
...
out
...
");
sql = "SELECT id, first, last, age FROM Registration" +
" WHERE id >= 101 ";
rs = stmt
...
next()){
//Retrieve by column name
int id
= rs
...
getInt("age");
String first = rs
...
getString("last");
//Display values
System
...
print("ID: " + id);
System
...
print(", Age: " + age);
System
...
print(", First: " + first);
System
...
println(", Last: " + last);
}
rs
...
printStackTrace();
}catch(Exception e){
//Handle errors for Class
...
printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
conn
...
close();
}catch(SQLException se){
se
...
out
...
java
C:\>
When you run JDBCExample, it produces the following result:
C:\>java JDBCExample
Connecting to a selected database
...
Creating statement
...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Fetching records with condition
...
LIKE CLAUSE
JDBC
This chapter provides an example on how to select records from a table using
JDBC application
...
Before executing the following example, make
sure you have the following in place:
To
execute
the
following
example
you
can
replace
the username and password with your actual user name and password
...
Required Steps
The following steps are required to create a new Database using JDBC
application:
Import the packages: Requires that you include the packages
containing the JDBC classes needed for database programming
...
sql
...
Register the JDBC driver: Requires that you initialize a driver so you
can open a communications channel with the database
...
getConnection() method to create a Connection object,
which represents a physical connection with a database server
...
This Query makes use of LIKE clause to select
records to select all the students whose first name starts with "za"
...
Sample Code
Copy and paste the following example in JDBCExample
...
Import required packages
import java
...
*;
public class JDBCExample {
146
JDBC
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com
...
jdbc
...
forName("com
...
jdbc
...
out
...
");
conn = DriverManager
...
out
...
");
//STEP 4: Execute a query
System
...
println("Creating statement
...
createStatement();
// Extract records without any condition
...
out
...
");
String sql = "SELECT id, first, last, age FROM Registration";
ResultSet rs = stmt
...
next()){
//Retrieve by column name
int id
= rs
...
getInt("age");
String first = rs
...
getString("last");
//Display values
System
...
print("ID: " + id);
System
...
print(", Age: " + age);
System
...
print(", First: " + first);
System
...
println(", Last: " + last);
}
// Select all records having ID equal or greater than 101
System
...
println("Fetching records with condition
...
executeQuery(sql);
while(rs
...
getInt("id");
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
148
JDBC
e
...
close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Connected database successfully
...
Fetching records without condition
...
ID: 100, Age: 30, First: Zara, Last: Ali
149
JDBC
ID: 102, Age: 30, First: Zaid, Last: Khan
Goodbye!
C:\>
150
26
...
This would use asc and desc keywords to sort records in
ascending or descending order
...
Your MySQL or whatever database you are using, is up and running
...
Most
often, using import java
...
* will suffice
...
Open
a
connection: Requires
using
the DriverManager
...
Execute a query: Requires using an object of type Statement for building
and submitting an SQL statement to sort records from a table
...
Clean up the environment: Requires explicitly closing all database
resources versus relying on the JVM's garbage collection
...
java, compile and run as
follows:
//STEP 1
...
sql
...
mysql
...
Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
//
Database credentials
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class
...
mysql
...
Driver");
//STEP 3: Open a connection
System
...
println("Connecting to a selected database
...
getConnection(DB_URL, USER, PASS);
System
...
println("Connected database successfully
...
out
...
");
stmt = conn
...
System
...
println("Fetching records in ascending order
...
executeQuery(sql);
while(rs
...
getInt("id");
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
System
...
println("Fetching records in descending order
...
executeQuery(sql);
while(rs
...
getInt("id");
int age = rs
...
getString("first");
String last = rs
...
out
...
out
...
out
...
out
...
close();
}catch(SQLException se){
//Handle errors for JDBC
se
...
forName
e
...
close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn
...
printStackTrace();
}//end finally try
}//end try
System
...
println("Goodbye!");
}//end main
}//end JDBCExample
Now, let us compile the above example as follows:
C:\>javac JDBCExample
...
Connected database successfully
...
Fetching records in ascending order
...
154
JDBC
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Goodbye!
C:\>
155
Title: software engineering
Description: all units are covered in the pdf and very useful .each and every topic is mentioned with side headings
Description: all units are covered in the pdf and very useful .each and every topic is mentioned with side headings