JDBCODBC Overview

1
JDBC/ODBC Overview

• Introduction to JDBC/ODBC

2
ODBC

• ODBC (Open Database Connectivity) is an API
  introduced by Microsoft that allows applications
  to access databases by using SQL.
• By using ODBC, a single application can access
  remote databases under different DBMSs (i.e.,
  Informix, Oracle, Sybase, etc.)
• The idea behind ODBC is to have a single API to
  interact with a database
• However, there is a lot of work to be done in
  order to get all operating systems to use ODBC,
  and that may not be possible or even desirable
  given that there is JDBC.

3
Open ODBC Standards

• The X/Open Group and ISO have made ODBC a
  standard, though there are differences from this
  standard and the Microsoft implementation.
• ODBC can be used on UNIX/Linux systems using
  products provided by third party software vendors
  and the Free Software Foundation.
• OpenAccess, from http//www.atinet.com provides
  UNIX clients ODBC connectivity to Windows based
  databases.
• unixODBC, from http//www.unixodbc.org is a
  freeware implementation of ODBC available for
  Linux/FreeBSD systems.
• ODBC relies on data drivers to convert the ODBC
  calls to different database formats.
• At development time, the application developer
  only needs to know the ODBC calls to connect to a
  database, execute SQL statements, and retrieve
  results.

4
n-Tier Architectures
Database server(someone elsesC program)

• 1-tier
• 2-tier
• 3-tier

5
Overview

• Setup a database for Web access
• Load a driver
• Connect to database
• Create Statement
• Execute Statement
• Process the results
• Remember to catch errors

6
Web access to a Database

• Basic steps
• Create a Database (e.g., MS Access or FoxPro)
• Create the data source using the ODBC
  administrator
• In Windoze 98, etc. Open Start?Control
  Panel?ODBC Data Sources (32bit)
• OR in WinXP, etc. Open Start?Control
  Panel?Administrative Tools?ODBC Data Source
  Administrator
• Click System DSN tab then Add button

7

• Click System DSN tab then Add button

8

• Choose the ODBC Driver and press Finish button

9
Give new Data Source Name, Description and path
of the Database then press OK
10
To Configure/Re-Configure a Connection press File
DSN tab, Add, choose Driver and give same data
source as System DSN
11
Give the path of the database
12
JDBC

• Java DB connectivity API
• Similar to ODBC
• Why do you need it
• Pure Java
• Simple API
• Well.Multi-platform

13
JDBC Architecture

• Supports n-Tier architectures
• Tabular data handling
• SQL but not only SQL

14
JDBC Driver Types

• JDBC database drivers are classified into four
  types based on the nature of the driver provided
• Bridges. Convert from JDBC to another standard
  for example ODBC. That standard then converts
  into the vendor specific API.
• Part Java part Native. Driver contains some Java
  code and some Native code
• Intermediate Server. JDBC talks directly to a
  separate server which then converts from JDBC to
  native API (great for intranets)
• Pure Java. JDBC driver converts directly to
  native API and sends through vendor specific
  network protocol (great for intranets)

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
15
JDBC-ODBC Bridge

• Microsoft has developed the standard ODBC Open
  DataBase Connectivity as a windows specific
  standard. It converts from ODBC to the vendors
  native API
• The JDBC driver converts from JDBC to ODBC.
• This is the only way to access Microsoft Access
  because MS Access does not provide direct access
  to its own API.
• Requires you have ODBC and JDBC drivers installed
• This makes accessing Microsoft Access via Java
  slow and less reliable because two sets of
  conversion and divers are required

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
16
The Driver manager

• It is possible that a Java application may need
  to talk to multiple databases, possibly from
  different vendors (especially if it is a
  distributed system)
• The JDBC driver manager provides the ability to
  communicate with multiple databases and keep
  track of which driver is needed for which
  database.
• Even if you only talk to one database you need to
  connect to it via the driver manager

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
17
Java JDBC Programming Model

• DriverManager allows you to connect to one or
  more to databases
• Connection represents the connection to a single
  database
• Statement contains an SQL statement to be sent
  down the connection to be executed by the
  database
• ResultSet contains the results of an SQL query
  such as SELECT

Driver Manager
Database
ResultSet
Connection
Statement
SQL
Database
ResultSet
Connection
Statement
SQL
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
18
Java JDBC Programming Steps
Class forName
Class.forName() loads the DriverManager
DriverManager getConnection
DriverManager.getconnection() connects to a
database and returns a Connection object
Connection createStatement
Connection.createStatement() provides a Statement
object you can insert SQL into
Statement
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
19
The JDBC Steps

• 1. Importing Packages
• 2. Registering the JDBC Drivers
• 3. Opening a Connection to a Database
• 4. Creating a Statement Object
• 5. Executing a Query and Returning a Result Set
  Object
• 6. Processing the Result Set
• 7. Closing the Result Set and Statement Objects
• 8. Closing the Connection

20
1 Importing Packages

• //
• // Program name LecExample_1a.java
• // Purpose Basic selection using prepared
  statement
• ////Import packagesimport java.sql. //JDBC
  packages import java.math. import
  java.io.import oracle.jdbc.driver.

21
2 Registering JDBC Drivers

• class LecExample_1a
• public static void main (String args ) throws
  SQLException
• // Load Oracle driver
• DriverManager.registerDriver (new
  oracle.jdbc.driver.OracleDriver())

22
JDBC Universal Resource Locators (URLs)

• Provides a way of identifying a database
• Allow different drivers to use different schemes
  for naming database
• Allow driver writers to encode all necessary
  connection information within them
• Allow a level of indirection

23
Step 3 Connecting to a Database, part I

• The getConnection method of DriverManager
  requires the name of the database to connect to,
  the name of the user connecting and their
  password it returns a Connection Object.
• The syntax for the name of the database is a
  little messy and is unfortunately Driver specific
• A JDBC URL represents a driver and has following
  three-part syntax
• jdbcltsubprotocolgtltsubnamegt

"jdbcmysql//www3.unl.ac.uk3306/kingj1
24
Step 3 Connecting to a Database, part II

• The DriverManager allows you to connect to a
  database using the specified JDBC driver,
  database location, database name, username and
  password.
• It returns a Connection object which can then be
  used to communicate with the database.
• The password is passed to the database in plain
  text and therefore anyone who can see the .java
  or .class file can find out the password!
• Connection connection DriverManager.getConnectio
  n("jdbcmysql//www3.unl.ac.uk3306/kingj1","kingj
  1","secret")

Password in plain text!!!!!!!!!!!!!!!
Vendor of database, Location of database server
and name of database
Username
25
3 Opening connection to a Database

• //Prompt user for username and password
• String user
• String password
• user readEntry("username ")
• password readEntry("password ")
• // Connect to the local database
• Connection conn DriverManager.getConnection
  ("jdbcoraclethin_at_aardvark1526teach", user,
  password)

26
4. Creating a Statement Object

• // Query the hotels table for resort 'palma
  nova
• // Please notice the essential trim
• PreparedStatement pstmt conn.prepareStatement
  ("SELECT hotelname, rating FROM hotels WHERE
  trim(resort) ?")
• pstmt.setString(1, "palma nova")

27
Step 5 Executing SQL (Querying the Database)

• SQL which queries the database uses the
  executeQuery method of Statement
• ExecuteQuery returns a ResultSet which we can
  then look at to see what records matched the
  query
• Lets assume the database has a table called test
  with a single column called value
• We will list the entire table in SQL this would
  be SELECT FROM test
• ResultSet rsstatement.executeQuery("SELECT
  FROM test")

SQL statement surrounded by " "
Contains the any matched records
28
Step 6 Processing the Returned Data, part I

• When executeQuery has finished it returns a
  ResultSet.
• ResultSet has an internal pointer which can be
  moved forward and backwards. You can only
  retrieve the values of the record it points at.
• Next(), Previous(), First() and Last() move the
  internal pointer between records they return true
  if the record is valid
• So if the pointer is already on the first record
  and you use previous it will return false etc.
• isLast() and isFirst() return true if the pointer
  is at the last or first record respectively

ResultSet
Internal Pointer
Record 1
Record 2
Record 3
Record 4
29
Step 6 Processing the Returned Data, part II

• The pointer points at the record to retrieve.
• However, we still have to get the value of each
  of its columns
• GetString("name") returns the value of the column
  called name as a String
• GetInt("name") returns the value of the column
  name as a int etc...
• while (rs.next())
• int valuers.getInt("value")

Name of the field inside the record to fetch
Moves to the next record and returns true if the
record is valid. If the record is not valid the
loop exits
NOTE this code will not skip the first record
because the pointer starts one before the first
record!
Do something with value
30
5. Executing a Query, Returning a Result Set
Object 6. Processing the Result Set

• ResultSet rset pstmt.executeQuery ()
• // Print query results
• while (rset.next ()) System.out.println
  (rset.getString (1)" " rset.getString(2))

31
7. Closing the Result Set and Statement Objects
8. Closing the Connection

• // close the result set, statement, and the
  connection rset.close() pstmt.close()
  conn.close()

32
(No Transcript)
33
AppendixODBC JDBC Example using Microsoft Access

• From Core Java 2
• by Cay Horstmann
• Screen displays by Dongchan Choo

34
Example Make DB

• Create a MS Access file called mssql1
• Create DSN for mssql1
• Go to Start (in MS window)
• Go to control panel
• Go to Data Source ODBC

35
Example Make DB(cont)

• Select System DSN
• Select Add

36
Example Make DB(cont)

• Select MS Access Driver

37
Example Make DB(cont)

• Input Data Source Name mssql1
• Select source

38
Example Make DB(cont)

• Select Database Name by directory

39
Example Make DB

• Create Books1.dat file using Note Pad

40
Example Make DB

• Create MakeDB1.java file
• import java.net.
• import java.sql.
• import java.io.
• import java.util.
• class MakeDB1
• public static void main (String args)
• try
• Class.forName("sun.jdbc.odbc.JdbcOdbcDri
  ver")
• Connection con DriverManager.getConnec
  tion(
• "jdbcodbcmssql1", "", "")

41
Example Make DB

• Statement stmt con.createStatement()
• String tableName ""
• if (args.length gt 0)
• tableName args0
• else
• System.out.println("Usage MakeDB
  TableName")
• System.exit(0)
• BufferedReader in new
  BufferedReader(new
• FileReader(tableName ".dat"))

42
Example Make DB

• createTable(tableName, in, stmt)
• showTable(tableName, stmt)
• in.close()
• stmt.close()
• con.close()

43
Example Make DB

• catch (IOException ex)
• System.out.println("Exception " ex)
• ex.printStackTrace ()
• catch (Exception e)
• System.out.println("Error " e.toString()
• e.getMessage())

44
Example Make DB

• public static void createTable(String tableName,
• BufferedReader in, Statement stmt)
• throws SQLException, IOException
• String line in.readLine()
• String command "CREATE TABLE "
  tableName
• "(" line ")"
• stmt.executeUpdate(command)
• while ((line in.readLine()) ! null)
• command "INSERT INTO " tableName
• " VALUES (" line ")"
• stmt.executeUpdate(command)

45
Example Make DB

• public static void showTable(String tableName,
• Statement stmt) throws SQLException
• String query "SELECT FROM "
  tableName
• ResultSet rs stmt.executeQuery(query)
• ResultSetMetaData rsmd rs.getMetaData()
• int columnCount rsmd.getColumnCount()
• while (rs.next())
• for (int i 1 i lt columnCount i)
• if (i gt 1) System.out.print(", ")
• System.out.print(rs.getString(i))
  
• System.out.println()
• rs.close()

46
(No Transcript)
47
Standard Query Language (SQL)

• Composed of two categories
• Data Manipulation Language (DML)
• used to manipulate the data
• select
• delete
• update
• Data Definition Language (DDL)
• create database
• create table
• drop database

48
Data Manipulation Language

• SELECT - query the database
• select from customer where id gt 1001
• INSERT - adds new rows to a table.
• Insert into customer values (1009, John Doe)
• DELTE - removes a specified row
• delete
• UPDATE - modifies an existing row
• update customers set amount 10 where id gt 1003

49
Data Definition Language

• CREATE DATABASE - allows you to create a database
• CREATE TABLE - allows you to create a table
  definition in a database
• DROP TABLE - removes a table from a database
• ALTER TABLE - modifies the definition of a table
  in a database

50
Transactions

• A transaction consists of one or more statements
  that have been executed, completed, and then
  either committed or rolled back (commit or
  rollback).
• A new connection is in auto-commit mode by
  default.
• If auto-commit mode is disabled, the transaction
  will not terminate until the method commit or
  rollback is invoked
• JDBC-compliant drivers must support transactions
• DatabaseMetaData gives information on the level
  of transaction support

51
Stored Procedures

• A Stored Procedure is written in a metalanguage
  defined by the DBMS vendor
• Used to batch or group multiple SQL statements
  that are stored in executable form at the
  database
• Written in some internal programming language of
  the DBMS
• Oracles PL/SQL
• Sybases Transact-SQL
• THESE LANGUAGES ARE NON-PORTABLE from one DBMS to
  another (with the exception of the SQLJ standard,
  which allows you to write SQL in standard Java
  and have that understood by any DBMS that
  supports the SQLJ standard).

52
Why Use Stored Procedures?

• Faster Execution of SQL (compiled and in-memory
  stored query plan)
• Reduced Network Traffic
• Modular Programming
• Automation of complex or sensitive transactions
• Syntax checking at time of creation of SP
• Syntax supports if, else, while loops, goto,
  local variables, etc., all of which dynamic SQL
  doesnt have

53
SQLJ vs JDBC comparison
54
Use SQLJ to write your program when

• you want to be able to check your program for
  errors at translation-time rather than at
  run-time.
• you want to write an application that you can
  deploy to another database. Using SQLJ, you can
  customize the static SQL for that database at
  deployment-time.
• you are working with a database that contains
  compiled SQL. You will want to use SQLJ because
  you cannot compile SQL statements in a JDBC
  program.

55
Use JDBC to write your program when

• your program uses dynamic SQL. For example, you
  have a program that builds queries on-the-fly or
  has an interactive component.
• you do not want to have a SQLJ layer during
  deployment or development. For example, you might
  want to download only the JDBC Thin driver and
  not the SQLJ runtime libraries to minimize
  download time over a slow link.

56
SQLJ static and non-static SQL

• The standard covers only static SQL operations
• those that are predefined and do not change in
  real-time as a user runs the application
• of course the data values that are transmitted
  can change dynamically!
• Oracle SQLJ offers extensions to support dynamic
  SQL operations
• those that are not predefined, where the
  operations themselves can change in real-time.
• It is possible to use dynamic SQL operations
  through JDBC code or PL/SQL code within a SQLJ
  application.
• Typical applications contain much more static SQL
  than dynamic SQL.

57
Java and SQLJ versus PL/SQL I

• Java and PL/SQL are complementary.
• Suited for different kinds of applications.
• PL/SQL is better for SQL-intensive applications.
• Optimized for SQL, and so SQL operations are
  faster in PL/SQL than in Java.
• Uses SQL datatypes directly, while Java
  applications must convert between SQL datatypes
  and Java types.
• Java, is better for logic-intensive applications.
  
• Superior programming model.
• Java's more general type system is better suited
  than PL/SQL for component-oriented applications.

58
Interoperability SQLJ and PL/SQL

• PL/SQL programs
• transparently call Java stored procedures,
  enabling you to build component-based Enterprise
  JavaBeans and CORBA applications.
• have transparent access to a wide variety of
  existing Java class libraries through trivial
  PL/SQL call specifications.
• Java programs
• call PL/SQL stored procedures and anonymous
  blocks through JDBC or SQLJ.
• SQLJ provides syntax for calling stored
  procedures and functions from within a SQLJ
  statement, and also supports embedded PL/SQL
  anonymous blocks within a SQLJ statement.

59
ReadEntry method (for completeness)

• // Method readEntry // Purpose to read
  a string from the user and return it // Input
  The prompt string // Output User entry
  static String readEntry (String prompt)
  try StringBuffer buffer new StringBuffer
  () System.out.print (prompt)
  System.out.flush () int c System.in.read
  () while (c ! '\n' c !
  -1) buffer.append ((char)c) c
  System.in.read () return
  buffer.toString ().trim () catch
  (IOException e) return ""

60
(No Transcript)
61
Java Servlets
Core Servlets JSP book www.coreservlets.com Mor
e Servlets JSP book www.moreservlets.com Servle
t and JSP Training Courses courses.coreservlets.c
om
62
Outline

• Java servlets
• Advantages of servlets
• Servlet structure
• Servlet examples
• Handling the client request
• Form Data
• HTTP request headers

63
A Servlets Job

• Read explicit data sent by client (form data)
• Read implicit data sent by client (request
  headers)
• Generate the results
• Send the explicit data back to client (HTML)
• Send the implicit data to client(status codes
  and response headers)

64
Why Build Web Pages Dynamically?

• The Web page is based on data submitted by the
  user
• E.g., results page from search engines and
  order-confirmation pages at on-line stores
• The Web page is derived from data that changes
  frequently
• E.g., a weather report or news headlines page
• The Web page uses information from databases or
  other server-side sources
• E.g., an e-commerce site could use a servlet to
  build a Web page that lists the current price and
  availability of each item that is for sale.

65
The Advantages of Servlets Over Traditional CGI

• Efficient
• Threads instead of OS processes, one servlet
  copy, persistence
• Convenient
• Lots of high-level utilities
• Powerful
• Sharing data, pooling, persistence
• Portable
• Run on virtually all operating systems and
  servers
• Secure
• No shell escapes, no buffer overflows
• Inexpensive
• There are plenty of free and low-cost servers.

66
Simple Servlet Template

• import java.io.
• import javax.servlet.
• import javax.servlet.http.
• public class ServletTemplate extends HttpServlet
  
• public void doGet(HttpServletRequest request,
• HttpServletResponse response)
• throws ServletException, IOException
• // Use "request" to read incoming HTTP
  headers
• // (e.g. cookies) and HTML form data (query
  data)
• // Use "response" to specify the HTTP
  response status
• // code and headers (e.g. the content type,
  cookies).
• PrintWriter out response.getWriter()
• // Use "out" to send content to browser

67
A Simple Servlet That Generates Plain Text

• import java.io.
• import javax.servlet.
• import javax.servlet.http.
• public class HelloWorld extends HttpServlet
• public void doGet(HttpServletRequest request,
• HttpServletResponse response)
• throws ServletException, IOException
• PrintWriter out response.getWriter()
• out.println("Hello World")

68
A Servlet That Generates HTML

• public class HelloWWW extends HttpServlet
• public void doGet(HttpServletRequest request,
• HttpServletResponse response)
• throws ServletException, IOException
• response.setContentType("text/html")
• PrintWriter out response.getWriter()
• String docType
• "lt!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML
  4.0 "
• "Transitional//EN\"gt\n"
• out.println(docType
• "ltHTMLgt\n"
• "ltHEADgtltTITLEgtHello
  WWWlt/TITLEgtlt/HEADgt\n"
• "ltBODYgt\n"
• "ltH1gtHello WWWlt/H1gt\n"
• "lt/BODYgtlt/HTMLgt")

69
The Servlet Life Cycle

• init
• Executed once when the servlet is first loaded.
  Not called for each request.
• service
• Called in a new thread by server for each
  request. Dispatches to doGet, doPost, etc. Do
  not override this method!
• doGet, doPost, doXxx
• Handles GET, POST, etc. requests.
• Override these to provide desired behavior.
• destroy
• Called when server deletes servlet instance. Not
  called after each request.

70
Handling the Client Request Form Data

• Form data
• Processing form data
• Reading request parameters
• Filtering HTML-specific characters

71
The Role of Form Data

• Example URL at online travel agent
• http//host/path?userMartyHalloriginbwidestl
  ax
• Names come from HTML author values usually come
  from end user
• Parsing form (query) data in traditional CGI
• Read the data one way (QUERY_STRING) for GET
  requests, another way (standard input) for POST
  requests
• Chop pairs at ampersands, then separate parameter
  names (left of the equal signs) from parameter
  values (right of the equal signs)
• URL decode values (e.g., "7E" becomes "")
• Need special cases for omitted values(param1val1
  param2param3val3) and repeated parameters
  (param1val1param2val2param1val3)

72
Creating Form Data HTML Forms

• lt!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
  Transitional//EN"gt
• ltHTMLgt
• ltHEADgtltTITLEgtA Sample Form Using
  GETlt/TITLEgtlt/HEADgt
• ltBODY BGCOLOR"FDF5E6"gt
• ltH2 ALIGN"CENTER"gtA Sample Form Using GETlt/H2gt
• ltFORM ACTION"http//localhost8088/SomeProgram"gt
• ltCENTERgt
• First name
• ltINPUT TYPE"TEXT" NAME"firstName"
  VALUE"Joe"gtltBRgt
• Last name
• ltINPUT TYPE"TEXT" NAME"lastName"
  VALUE"Hacker"gtltPgt
• ltINPUT TYPE"SUBMIT"gt lt!-- Press this to submit
  form --gt
• lt/CENTERgt
• lt/FORMgt
• lt/BODYgtlt/HTMLgt
• See CSAJSP Chapter 16 for details on forms

73
HTML Form Initial Result
74
Reading Form Data In Servlets

• request.getParameter("name")
• Returns URL-decoded value of first occurrence of
  name in query string
• Works identically for GET and POST requests
• Returns null if no such parameter is in query
• request.getParameterValues("name")
• Returns an array of the URL-decoded values of all
  occurrences of name in query string
• Returns a one-element array if param not repeated
• Returns null if no such parameter is in query
• request.getParameterNames()
• Returns Enumeration of request params

75
An HTML Form With Three Parameters

• ltFORM ACTION"/servlet/coreservlets.ThreeParams"gt
• First Parameter ltINPUT TYPE"TEXT"
  NAME"param1"gtltBRgt
• Second Parameter ltINPUT TYPE"TEXT"
  NAME"param2"gtltBRgt
• Third Parameter ltINPUT TYPE"TEXT"
  NAME"param3"gtltBRgt
• ltCENTERgtltINPUT TYPE"SUBMIT"gtlt/CENTERgt
• lt/FORMgt

76
Reading the Three Parameters

• public class ThreeParams extends HttpServlet
• public void doGet(HttpServletRequest request,
• HttpServletResponse response)
• throws ServletException, IOException
• response.setContentType("text/html")
• PrintWriter out response.getWriter()
• String title "Reading Three Request
  Parameters"
• out.println(ServletUtilities.headWithTitle(tit
  le)
• "ltBODY BGCOLOR\"FDF5E6\"gt\n"
• "ltH1 ALIGNCENTERgt" title
  "lt/H1gt\n"
• "ltULgt\n"
• " ltLIgtltBgtparam1lt/Bgt "
• request.getParameter("param1")
  "\n"
• " ltLIgtltBgtparam2lt/Bgt "
• request.getParameter("param2")
  "\n"
• " ltLIgtltBgtparam3lt/Bgt "
• request.getParameter("param3")
  "\n"
• "lt/ULgt\n"
• "lt/BODYgtlt/HTMLgt")

77
Reading Three ParametersResult
78
Filtering Strings for HTML-Specific Characters

• You cannot safely insert arbitrary strings into
  servlet output
• lt and gt can cause problems anywhere
• and " can cause problems inside of HTML
  attributes
• You sometimes cannot manually translate
• The string is derived from a program excerpt or
  another source where it is already in some
  standard format
• The string is derived from HTML form data
• Failing to filter special characters from form
  data makes you vulnerable to cross-site scripting
  attack
• http//www.cert.org/advisories/CA-2000-02.html
• http//www.microsoft.com/technet/security/crssite.
  asp