Web Programming Course

1
Web Programming Course

• Lecture 11 JDBC

2
Databases

• A database contains one or more tables
• Each table has a name
• A table consists of rows and columns
• Columns have names describing the data stored in
  that column (for example, Address)
• A row is a record it contains information about
  a single entity (such as a person)
• The data in a cell may be string, integer,
  floating point number, date, blank, etc.
• A value of null means the data for that cell is
  missing
• Two null values are not considered to be equal

3
Example table

• People is the name of the table
• Each row is a record
• Each cell in a column contains the same type of
  data

4
Primary Keys

• To look things up in a table, we need a way of
  choosing a particular row
• A primary key is a column, or group of columns,
  whose values uniquely identify each row
• Example In the previous table, no single column
  could be used as a primary key
• Multiple people can have the same first name,
  last name, gender, age, or telephone number
• However, no two people had the same first name
  and last name
• First_name and Last_name could be used as a
  primary key

5
Primary key and integrity

• In practical databases, it is more convenient to
  define a single column as a primary key
• Tables must follow certain integrity rules
• No two rows may be completely identical
• Any column that is a primary key, or part of a
  primary key, can not contain null values
• There are some other rules about arrays and
  repeating groups that need not concern us here

6
SQL

• SQL stands for Structured Query Language
• SQL language was designed for accessing and
  updating databases
• SQL is an ANSI (American National Standards
  Institute) standard
• Almost every relational database supports SQL
• MySQL, Oracle, MS-Access, DB2, etc
• Most also extend it in various incompatible ways

7
DDL and DML

• SQL consists of two types of statements
• DDL is the Data Definition Language it defines
  the structure of tables
• CREATE TABLE -- creates a new database table
• ALTER TABLE -- alters (changes) a database table
• DROP TABLE -- deletes a database table
• DML is the Data Manipulation Language it defines
  and manipulates the contents of tables
• INSERT -- puts new data into the database
• SELECT -- gets data from the database
• UPDATE -- updates (changes) data in the database
• DELETE -- removes data from the database

8
CREATE TABLE

• SyntaxCREATE TABLE table_name ( column_name
  data_type constraint, ,
  column_name data_type constraint )
• Names, such as the table_name and the
  column_names, are not quoted
• The data_types will be described shortly
• The constraints are optional
• Notice the commas

9
Example table creation

• CREATE TABLE People ( First_Name
  VARCHAR(12), Last_Name VARCHAR(25),
  Gender CHAR(1), Age NUMBER(3), Phone
  CHAR(6) )

10
Constraints

• When a table is created, constraints can be put
  on the columns
• Examples of constraints
• unique -- no repeated values in this column
• primary key -- unique and used to choose rows
• not null -- must have a value

11
ALTER TABLE

• ALTER TABLE table_name ADD column_name datatype
• Adds a column to the table
• ALTER TABLE table_name DROP COLUMN column_name
• Removes a column (and all its data) from the
  table
• DROP COLUMN is not supported by all the available
  SQL platforms

12
DROP TABLE

• SyntaxDROP TABLE table_name
• removes the contents of the table from the
  database
• deletes all the rows from a table leaves a
  blank table with column names and types only

13
SELECT

• SyntaxSELECT columns FROM table WHERE condition
  
• columns is
• a comma-separated list of column names, or to
  indicate all columns
• table is the name of the table
• condition is an optional condition to be
  satisfied
• Examples
• SELECT First_Name, Last_Name FROM People
• SELECT FROM People WHERE age

14
How SELECT works

• SELECT First_Name, Last_Name FROM People WHERE
  Age 30

Result
15
Conditions

• Equal
• Not equal to ( ! works on some databases)
• Greater than or equal
• Greater than
• LIKE String equality may be used as a
  wildcard
• WHERE First_Name LIKE 'Jo'matches Joe, John,
  Joanna, etc.

16
Operators

• AND, OR and NOT can be used with conditions
• Basic arithmetic operators are defined also in
  SQL
• add
• - subtract
• multiply
• / divide
• modulus (remainder)

17
Names and strings

• SQL keywords (e.g., SELECT) are usually
  case-insensitive, but are traditionally written
  in all uppercase letters
• Table names and column names may or may not be
  case sensitive
• Data values presumably are case sensitive
• String data must be enclosed in single quotes

18
INSERT INTO

• SyntaxINSERT INTO table_name (column, ,
  column) VALUES (value, , value)
• The columns are the names of columns you are
  putting data into, and the values are that data
• String data must be enclosed in single quotes
• Numbers are not quoted
• You can omit the column names if you supply a
  value for every column

19
INSERT INTO

• INSERT INTO PEOPLE VALUES (Sally, Jones, F,
  27, 3-1542)
• INSERT INTO PEOPLE (First_Name, Gender, Age)
  VALUES (John, M, 32)

20
UPDATE

• SyntaxUPDATE table_name SET column_name
  new_value WHERE column_name value
• Example
• UPDATE PersonSET age age 1WHERE First_Name
  'John' AND Last_Name 'Smith'

21
DELETE

• DELETE FROM table_name WHERE column_name
  some_value
• Examples
• DELETE FROM Person WHERE Last_Name 'Smith'
• DELETE FROM Person
• Deletes all records from the table!

22
Database transactions

• Transaction more than one statement which must
  all succeed (or all fail) together
• If one fails, the system must reverse all
  previous actions
• Can not leave DB in inconsistent state halfway
  through a transaction
• Two policies for handling transactions
• COMMIT complete transaction
• ROLLBACK abort

23
ODBC and JDBC

• ODBC - Open Database Connectivity
• Collection of drivers to commercial DBs such as
  Oracle, Sybase, Informix, MS SQLServer, Access
  and more
• Unified interface to applications independent of
  specific DB.
• ODBC API invokes the device driver according to
  the data-source.
• JDBC Java ODBC

24
JDBC rationale

• Java is very standardized, but there are many
  versions of SQL
• JDBC provides the means for accessing SQL
  databases from Java code
• On the one hand, JDBC is a standardized API for
  use by Java programs
• On the other hand, JDBC is a specification for
  how third-party vendors should write database
  drivers to access specific SQL versions

25
JDBC Architecture
Application
JDBC
Driver

• Java code calls JDBC library
• JDBC loads a driver
• Driver talks to a particular database
• Can have more than one driver and more than one
  database
• Ideal can change database engines without
  changing the application code

26
JDBC Architecture
27
Java SQL packages

• JDBC package is released with Java platform 2 in
  packages java.sql and javax.sql
• Every JDBC driver written by any DB vendor
  implements the JDBC interface
• In order to connect to the database from Java,
  import the following packages
• java.sql. (usually enough)
• javax.sql. (for advanced features)

28
Java JDBC Programming Steps

• Load Driver.
• Connect to the Database.
• Execute SQL.
• Process the Results.
• Close the Connection.

29
JDBC Object Classes

• DriverManager
• loads, chooses drivers
• Driver
• connects to actual database
• Connection
• a series of SQL statements to and from the DB
• Statement
• a single SQL statement
• ResultSet
• the records returned from a Statement

30
Loading a Driver

• When a driver class is first loaded, it registers
  with the DriverManager
• Thus, to register a driver, just load it
• Statically load driver
• Class.forName(foo.bar.MyDriver)
• Connection c DriverManager.getConnection(...)

31
JDBC URLs

• jdbcsubprotocolsource
• Each driver has its own sub-protocol
• Each sub-protocol has its own syntax for the
  source
• jdbcodbcDataSource
• e.g. jdbcodbcNorthwind
• jdbcmsql//hostport/database
• e.g. jdbcmsql//foo.nowhere.com4333/accounting

32
Connect to the Database

• Connection getConnection (String url, String
  user, String password)
• Connects to given JDBC URL with given user name
  and password
• Throws java.sql.SQLException
• Returns a Connection object

33
Connection

• A Connection represents a session with a
  database.
• Within the context of a Connection, SQL
  statements are executed and results are returned.
• Can have multiple connections to a database
• Most drivers support concurrent connections
• Also provides metadata -- information about the
  database, tables, and fields
• Provides an interface to deal with transactions

34
Obtaining a Connection

• String url "jdbcodbcNorthwind"
• try
• Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver")
• Connection con DriverManager.getConnection(url,
  user, password)
• catch (ClassNotFoundException e)
• e.printStackTrace()
• catch (SQLException e)
• e.printStackTrace()

35
AirlineScehdule table
36
Connecting to a Database

• import java.sql.
• class JDBCTest
• static String AS_URL "jdbcoracleASDatabas
  e"
• public static void main (String args )
  throws
• SQLException, ClassNotFoundException
  
• Class.forName ("oracle.jdbc.OracleDriver")
• Connection AS
• AS DriverManager.getConnection(AS_URL,
• "scott", "tiger")
• AS.close()

37
Statement

• A Statement object is used for executing an SQL
  statement and obtaining the results produced by
  the execution.
• Statement createStatement()
• Creates a Statement object for sending SQL
  statements to the database.
• SQL statements are executed using Statement
  objects.
• The Statement object may be reused for many
  statements

38
Issuing statements

• The following are methods on the Statement
  object
• int executeUpdate() -- for issuing queries that
  modify the database and return no result set
• Use for DROP TABLE, CREATE TABLE, and INSERT
• Returns the number of rows in the resultant table
• ResultSet executeQuery() -- for queries that do
  return a result set.
• Returns results as a ResultSet object.

39
ResultSet

• JDBC returns a ResultSet as a result of a query
• A ResultSet contains all the rows and columns
  that satisfy the SQL statement and provides
  access to the generated data.
• The table rows are retrieved in sequence.
• A ResultSet maintains an iterator pointing to its
  current row of data.
• The iterator is valid until the ResultSet object
  or its Statement object are closed
• Columns of the current row can be accessed by
  index or name

40
ResultSet Methods

• boolean next()
• activates the next row
• the first call to next() activates the first row
• returns false if there are no more rows
• void close()
• disposes of the ResultSet
• allows to re-use the Statement that created it
• automatically called by most Statement methods

41
Creating a table

• CREATE TABLE animal ( name CHAR(40),
  category CHAR(40) )
• Statement s conn.createStatement ()
  s.executeUpdate ("DROP TABLE IF EXISTS
  animal") s.executeUpdate (
  "CREATE TABLE animal ( "name CHAR(40), "
  "category CHAR(40))")

42
Populating the table

• int cntcnt s.executeUpdate ( "INSERT
  INTO animal (name, category)" "
  VALUES" "('snake', 'reptile'),"
  "('frog', 'amphibian')," "('tuna',
  'fish')," "('racoon', 'mammal')")
  s.close () System.out.println (cnt " rows
  inserted")

43
Querying the table

• Statement s conn.createStatement ()
• s.executeQuery ("SELECT name, category FROM
  animal")
• ResultSet rs s.getResultSet ()
• int count 0
• while (rs.next ()) String nameVal
  rs.getString ("name")String catVal
  rs.getString ("category")System.out.println("nam
  e"nameVal", category"catVal)count
• rs.close ()
• s.close ()
• System.out.println (count " rows were
  retrieved")

44
Issue Queries and Receive Results

• import java.sql.
• import java.io.
• class JdbcTest
• static String AS_URL "jdbcoracleASDatabase"
  
• public static void main (String args )
  throws
• SQLException, ClassNotFoundException,
  IOException
• Class.forName ("oracle.jdbc.OracleDriver")
• Connection AS
• AS DriverManager.getConnection(AS_URL,
• "scott", "tiger")

45
Issue Queries and Receive Results

• Statement schedule AS.createStatement ()
• String flightOut "SELECT FROM
  AirlineSchedule
• WHERE from SAN"
• ResultSet fromSAN schedule.executeQuery (
  flightOut)
• while ( fromSAN.next() )
• System.out.println (fromSAN.getString
  ("to"))
• AS.close()

46
ResultSet Methods

• Type getType(int columnIndex)
• returns the given field as the given type
• fields indexed starting at 1 (not 0)
• Type getType(String columnName)
• same, but uses name of field
• less efficient
• int findColumn(String columnName)
• looks up column index given column name

47
ResultSet Methods

• String getString(int columnIndex)
• boolean getBoolean(int columnIndex)
• byte getByte(int columnIndex)
• short getShort(int columnIndex)
• int getInt(int columnIndex)
• long getLong(int columnIndex)
• float getFloat(int columnIndex)
• double getDouble(int columnIndex)
• Date getDate(int columnIndex)
• Time getTime(int columnIndex)
• Timestamp getTimestamp(int columnIndex)

48
Sample Database

• Employee ID Last Name First Name
• 1 Davolio Nancy
• 2 Fuller Andrew
• 3 Leverling Janet
• 4 Peacock Margaret
• 5 Buchanan Steven

49
SELECT Example

• Connection con DriverManager.getConnection(url,
  "alex", "password")
• Statement st con.createStatement()
• ResultSet results st.executeQuery("SELECT
  EmployeeID, LastName, FirstName FROM Employees)
• while (results.next())
• int id results.getInt(1)
• String last results.getString(2)
• String first results.getString(3)
• System.out.println("" id " " first " "
  last)
• st.close()
• con.close()

50
Closing the connections

• Close the Connections, Statements, and ResultSets

Highly recommended
con.close() stmt.close() rs.close()
Recommended
Optional (Will otherwise be closed by its calling
statement)
51
PreparedStatement

• PreparedStatement objects contain SQL statements
  sent to the database to be prepared for execution
  
• The SQL statements contains variables (IN params)
  which are given values before statement is
  executed
• Rationale optimizes the performance
• IN parameters are indicated by a ?
• Values are set by position
• String flightOut "SELECT FROM AirlineSchedule
  WHERE from ?"

52
PreparedStatement

• PreparedStatement prepareStatement(String sql)
• Creates a PreparedStatement object for sending
  prepared SQL statements to the database.
• An SQL statement can be pre-compiled and stored
  in a PreparedStatement object.
• This object can be used to efficiently execute
  this statement multiple times.

53
PreparedStatement Example

• import java.sql.
• import java.io.
• class JdbcTest
• static String AS_URL "jdbcoracleDatabase"
• public static void main (String args )
  throws SQLException, ClassNotFoundException,
  IOException
• Class.forName ("oracle.jdbc.OracleDriver")
• Connection AS
• AS DriverManager.getConnection(AS_URL,
• "scott", "tiger")

54
PreparedStatement Example

• String flight "SELECT FROM
  AirlineSchedule WHERE from ?"
• PreparedStatement schedule
• schedule AS.prepareStatement (flight)
• schedule.setObject( 1, "SAN" )
• ResultSet fromSanDiego schedule.execute
  Query ()
• schedule. clearParameters()
• schedule.setObject( 1, "LAX" )
• ResultSet fromLA schedule.executeQuery
  ()

55
PreparedStatement Example

• PreparedStatement preStmt con.prepareStatement(
• INSERT INTO mytable VALUES (?, ?))
• preStmt.setInt(1, 1)
• preStmt.setString(2, "Amy")
• preStmt.executeUpdate()
• preStmt.setInt(1, 2)
• preStmt.setString(2, "Daisy")
• preStmt.executeUpdate()

56
PreparedStatement

• A statement object
• Does not contain a SQL statement while created
• Compiled while executed
• A PreparedStatement object
• Contains a SQL statement while created
• Compiled at creating time, does not need to
  compile while executed
• More efficient execution

57
Transaction Management

• Transactions consist of one or more statements
  that have been executed and completed
• A transaction ends when a commit or rollback is
  sent
• Connections have an AutoCommit variable
• The default value is true
• If AutoCommit is true, then every statement is
  automatically committed
• If AutoCommit is false, then every statement is
  added to an ongoing transaction
• The user explicitly commits or rolls back the
  transactions using Connection.commit() and
  Connection.rollback()

58
Transaction Example

• Suppose we want to transfer 100 from bank
  account 13 to account 72

PreparedStatement pstmt con.prepareStatement(U
PDATE BankAccount SET amountamount? WHERE
accountId ?) pstmt.setInt(1,-100)
pstmt.setInt(2, 13) pstmt.executeUpdate() pstmt
.setInt(1, 100) pstmt.setInt(2,
72) pstmt.executeUpdate()
What happens if this update fails?
59
Transaction Example

• con.setAutoCommit(false)
• try
• PreparedStatement pstmt con.prepareStatement(
  update BankAccount
• set amountamount? where accountId?)
• pstmt.setInt(1,-100) pstmt.setInt(2, 13)
• pstmt.executeUpdate()
• pstmt.setInt(1, 100) pstmt.setInt(2, 72)
• pstmt.executeUpdate()
• con.commit()
• catch (SQLException e)
• con.rollback()

60
JDBC Class Diagram
Whoa!