Transcript jdbc - Villanova University

DATABASE PROGRAMMING
3
WEB/DB
JDBC by the ASU Scholars
1
ADVANCED DATABASE CONCEPTS
JDBC
Susan D. Urban and Suzanne W. Dietrich
Department of Computer Science and Engineering
Arizona State University
Tempe, AZ 85287-5406
WEB/DB
2
OPEN DATABASE CONNECTIVITY (ODBC)




Standard application programming interface (API) for
accessing a database.
A separate module or driver is required for each
database to be accessed.
Based on the standard Call Level Interface (CLI) of the
SQL Access Group (part of the X/Open Standard).
Can use the API to execute SQL statements, update
tables, and retrieve metadata.
WEB/DB
3
ODBC ISSUES IN A JAVA ENVIRONMENT



ODBC uses C to access the data source. This poses
issues with implementation. Hence, it cannot be used
in conjunction with Java.
OBDC’S API cannot be used by translating the API to
Java since there is no pointer concept in Java.
ODBC requires the driver manager to be installed on
every client installation.
WEB/DB
4
JAVA DATABASE CONNECTIVITY (JDBC)






Java API for connecting programs written in Java to
databases.
Based on ODBC.
Allows Java programs to send SQL statements to any
relational database.
Platform independent.
JDBC drivers written in Java can be accessed from any
computer in a heterogeneous network .
A JDBC-ODBC bridge can be used to access databases
using the ODBC interface.
WEB/DB
5
TWO-TIER JDBC ARCHITECTURES



Java application or applet talks directly to the data source.
Client sends requests to the server through user interfaces.
JDBC Driver communicates with the data source to access
the data.
Sun Microsystems Inc. 1999
WEB/DB
6
THREE-TIER JDBC ARCHITECTURES



Uses a third tier between the client and the server.
Controls updates that are made to the database.
Secure and robust.
Sun Microsystems Inc. 1999
WEB/DB
7
DEVELOPING JDBC APPLICATIONS





Import JDBC classes (java.sql.*)
Load the JDBC Driver.
Connect to the database.
Use the JDBC API to access the database.
Disconnect from the database.
WEB/DB
8
ESTABLISHING A CONNECTION
TO A DATABASE





The first step in accessing data from any relational
database using JDBC is to establish a connection with the
data source.
The Connection object is used to get meta data and execute
SQL statements.
The getConnection method returns a Connection object
that represents a session with a specific database.
The parameters in the getConnection method are URL,
username and password. Username and password are
optional.
The URL consists of the protocol “jdbc”, sub-protocol
“odbc”, and the Data Source Name (DSN).
WEB/DB
9
EXAMPLE TO CONNECT TO A DATABASE
/* dbName is the registered name of the ODBC data source */
String url = "jdbc:odbc:" + dbName ;
try {
/* Load the jdbc-odbc driver */
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/* Open a connection to the odbc data source */
con =DriverManager.getConnection(url,"","");
}
WEB/DB
10
STATEMENTS


A Statement Object is used to send SQL queries to a
database.
A Statement object should be created using the
connection method createStatement().
WEB/DB
11
TYPES OF STATEMENTS
There are three types of statement objects:
 Simple statements
Used to execute SQL statements without any parameters.
Statement stmt = connection.createStatement();

Prepared Statements
Used when a statement will be called several times and is stored
as a pre-compiled statement with IN parameters.
PreparedStatement pstmt =
con.prepareStatement(“update employee set salary=? where ssn=?”);

Callable Statements
Used with calls to database stored procedures and SQL statements
with OUT parameters.
WEB/DB
12
EXECUTING SIMPLE STATEMENTS

The execution of a statement returns results into a
ResultSet object. The ResultSet object is then used to
access query results.
ResultSet rs = null;

The executeQuery() method is used to execute an SQL
statement through the statement object.
rs = stmt.executeQuery("SELECT a, b, c FROM Table1");

The close() method is used to close the ResultSet.
rs.close();
WEB/DB
13
GETTING DATA FROM A ResultSet



The next() method is used to traverse through tuples in
the ResultSet object.
The data stored in a ResultSet object is retrieved
through a set of get methods that allows access to the
various columns of the current row.
The results are printed out on a screen using the
Servlet output stream.
while(rs.next())
{
out.println(rs.getString(“SSN”));
}
WEB/DB
14
DATABASE METADATA

Metadata is the information in the database that is
associated with the database schema:





Table names
Column names
Column types
The metadata associated with a database can be
queried using JDBC.
The metadata associated with the result set object of a
statement execution can also be queried.
WEB/DB
15
DATABASE METADATA RETRIEVAL

Create a metadata object.
DatabaseMetaData dbmd ;

Retrieve metadata from the database through the
connection established.
dbmd = con.getMetaData();

The getTables() method of the metadata object is used
to retrieve information about the tables in a database.
The information is stored in a result set object.
ResultSet rsTables = dbmd.getTables(null, null, null, null);
WEB/DB
16
GETTING THE METADATA

The getString() method of the ResultSet object is used
to locate a specific table.
String tableName = rsTables.getString("TABLE_NAME");

The getColumns() method is used to retrieve
information about the columns and column types in a
table, with the results stored in a ResultSet object.
ResultSet rsColumns =
dbmd.getColumns(null,null,tableName,null);
while (rsColumns.next())
{
…
}
WEB/DB
17