EBIZ 5535 Lecture 6

1
EBIZ 5535Lecture 6

• More on JDBC

2
Put it together

• zipcode.java

3
Using Prepared Statements

• As with the Statement object, you create
  PreparedStatement objects with a Connection
  method.
• PreparedStatement updateSales
  conn.prepareStatement("UPDATE COFFEES SET SALES
  ? WHERE COF_NAME LIKE ?")
• This is a template that can have values inserted
  for the '?'s

4
Using Prepared Statements

• Set the first ? to an integer with
• UpdateSales.setInt(1, 75)
• Set the second ? to a string with
• UpdateSales.setString(2, "Colombian")
• Then execute the prepared statement
• updateSales.executeUpdate()
• And its reusable

5
Connecting to a Database from a JSP

• lthtmlgtltheadgt
• lttitlegt Zipcode lookup lt/titlegtlt/headgt
• ltbodygt
• lth1gt Zipcode lookup lt/h1gt ltbrgt
• lt_at_ page import'java.sql., java.util.' gt
• lt! private static Connection c
• static Statement s null gt
• lt try
• Class.forName("com.caucho.jdbc.mysql.Driver"
  )

6
Cont'd

• c DriverManager.getConnection("jdbcmysql-caucho
  //jvb.cs.uwyo.edu3306/zipcodes","jvb",
  "")
• s c.createStatement()
• catch(Exception s)
• out.println("Error Couldn't open database "
  s)
• int start request.getQueryString().indexOf("")

7

• String zip request.getQueryString().substring(st
  art1,start6)
• try
• ResultSet rs s.executeQuery("select from zips
  where zip " zip)
• if (rs.next()) gt
• The zipcode lt zip gt is for lt
  rs.getString(1) gt, ltrs.getString(2) gt ltbrgt
• lt

8
Cont'd

• else out.println("The zipcode " zip "
  was not found")
• catch(SQLException s)
• out.println("Zip not found")
• gt
• lt/bodygt
• lt/htmlgt

9
Database Meta Data

• Comprehensive information about the database as a
  whole.
• Implemented by driver vendors to let users know
  the capabilities of a Database Management System
  (DBMS) in combination with the driver based on
  JDBC technology

10
Information Available in Database Meta Data

• DatabaseMetaData dbMetaData connection.getMetaDa
  ta()
• String productName
• dbMetaData.getDatabaseProductName()
• String productVersion
• dbMetaData.getDatabaseProductVersion()

11
Example of Other Useful Information

• ResultSet getAttributes(String catalog,
  String schemaPattern, String typeNamePattern,
  String attributeNamePattern)           Retrieves
  a description of the given attribute of the
  given type for a user-defined type (UDT) that is
  available in the given schema and catalog.

12
ResultSet Meta Data

• ResultSetMetaData can be used to get information
  about the types and properties of the columns in
  a ResultSet object.
• ResultSet rs stmt.executeQuery("SELECT a, b, c
  FROM TABLE2")
• ResultSetMetaData rsmd rs.getMetaData()
• int numberOfColumns rsmd.getColumnCount()

13
Some Useful Methods of ResultSetMetaData

• String getColumnName(int column)
• int getColumnType(int column) Retrieves the
  designated column's SQL type.
• int getColumnDisplaySize(int column)
•  Indicates the designated column's normal
  maximum width in characters.

14
A Detailed Example Including Use of Meta Data

• QueryViewer

15
Connection Pooling

• Opening Database connections can be time
  consuming
• Short queries often take much less time than
  establishing a connection
• In an application that makes many queries on the
  same database, use connection pooling
• Recycle connections

16
What a Connection Pooler Should do

• A connection pool class should do the following
• Preallocate connections
• Manage open connections
• Allocate new connections
• Wait for a connection to become available
• Close connections

17
Servlets that Use Connection Pooler

• init() method creates a new connection pool
• destroy() method closes all connections
• doGet() method
• Gets a connection from the connection pool
• Does database operations
• Frees the connection