DATABASE PROGRAMMING

1
DATABASE PROGRAMMING

• 3 JDBC by the ASU Scholars

2
ADVANCED DATABASE CONCEPTS JDBC

• Susan D. Urban and Suzanne W. Dietrich
• Department of Computer Science and Engineering
• Arizona State University
• Tempe, AZ 85287-5406

3
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.

4
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.
• OBDCS 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.

5
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.

6
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
7
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
8
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.

9
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).

10
EXAMPLE TO CONNECT TO A DATABASE

• / dbName is the registered name of the ODBC data
  source /
• String url "jdbcodbc" dbName
• try
• / Load the jdbc-odbc driver /
• Class.forName("sun.jdbc.odbc.JdbcOdbcDrive
  r")
• / Open a connection to the odbc data
  source /
• con DriverManager.getConnection(url,"","")
  

11
STATEMENTS

• A Statement Object is used to send SQL queries to
  a database.
• A Statement object should be created using the
  connection method createStatement().

12
TYPES OF STATEMENTS

• There are three types of statement objects
• Simple statements Used to execute SQL statements
  without any parameters.
• Statement stmt connection.createStat
  ement()
• Prepared StatementsUsed when a statement will be
  called several times and is stored as a
  pre-compiled statement with IN parameters.
  PreparedStatement pstmt con.prepareStatemen
  t(update employee set salary? where ssn?)
• Callable StatementsUsed with calls to database
  stored procedures and SQL statements with OUT
  parameters.

13
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()

14
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))

15
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.

16
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)

17
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,ta
  bleName,null)
• while (rsColumns.next())