JSP

1
JSPDB Programming

• By Mr. PHUPHA PUNYAPOTASAKUL
• (???? ?????????????)

2
Architecture
3
JDBC

• Connect to a data source, like a database
• Send queries and update statements to the
  database
• Retrieve and process the results received from
  the database in answer to your query

4
Establish connection

• Initialize driverClass.forName(driver)
• Create ConnectionConnection connDriverManager.ge
  tConnection(url,user,password)
• Each JDBC Vendor may use different driver class
  name and url

5
Driver class name and URL example

• Oracle 10i
• driveroracle.jdbc.driver.OracleDriver
• urljdbcoraclethin_at_//DBSERVER1521/DBNAME
• MySQL 4.x, 5.x
• drivercom.mysql.jdbc.Driver
• urljdbcmysql//DBSERVER/DBNAME
• MS SQLServer 2000, 2005
• drivercom.microsoft.sqlserver.jdbc.SQLServerDrive
  r
• urljdbcsqlserver//DBSERVER1433databaseName
  DBNAMEintegratedSecurityfalse

6
Create statement and execute SQL command

• Create statementStatement stmt
  con.createStatement(ResultSet.TYPE_SCROLL_INSENSI
  TIVE,ResultSet.CONCUR_READ_ONLY)
• Execute Updatestmt.executeUpdate(sql)
• Execute QueryResultSet rs stmt.executeQuery(sql
  )

7
ResultSet type

• TYPE_FORWARD_ONLY The result set is not
  scrollable its cursor moves forward only, from
  before the first row to after the last row. The
  rows contained in the result set depend on how
  the underlying database materializes the results.
  That is, it contains the rows that satisfy the
  query at either the time the query is executed or
  as the rows are retrieved.
• TYPE_SCROLL_INSENSITIVE The result set is
  scrollable its cursor can move both forward and
  backward relative to the current position, and it
  can move to an absolute position.
• TYPE_SCROLL_SENSITIVE The result set is
  scrollable its cursor can move both forward and
  backward relative to the current position, and it
  can move to an absolute position.

8
Concurent Type

• CONCUR_READ_ONLY
• CONCUR_UPDATABLE

9
ResultSet movement

• next() - moves the cursor forward one row.
  Returns true if the cursor is now positioned on a
  row and false if the cursor is positioned after
  the last row.
• previous() - moves the cursor backwards one row.
  Returns true if the cursor is now positioned on a
  row and false if the cursor is positioned before
  the first row.
• first() - moves the cursor to the first row in
  the ResultSet object. Returns true if the cursor
  is now positioned on the first row and false if
  the ResultSet object does not contain any rows.
• last() - moves the cursor to the last row in the
  ResultSet object. Returns true if the cursor is
  now positioned on the last row and false if the
  ResultSet object does not contain any rows.

10
ResultSet movement

• beforeFirst() - positions the cursor at the start
  of the ResultSet object, before the first row. If
  the ResultSet object does not contain any rows,
  this method has no effect.
• afterLast() - positions the cursor at the end of
  the ResultSet object, after the last row. If the
  ResultSet object does not contain any rows, this
  method has no effect.
• relative(int rows) - moves the cursor relative to
  its current position.
• absolute(int row) - positions the cursor on the
  row-th row of the ResultSet object.

11
Retrieving data

• Data type conversion
• getString()
• getInt()
• getFloat()
• getDate()
• etc.
• 2 ways to retrieve datae.g. select product_name
  from product
• By column namegetString("product_name")
• By column index (start from 1)getString(1)

12
Updating data

• Only if using CONCUR_UPDATABLE
• Not all JDBC Driver support this mode
• Data type conversion
• upateString()
• updateInt()
• updateFloat()
• updateDate()
• etc.
• Be able to use both column name and index

13
Updating data

• To insert a new recordrs.moveToInsertRow()rs.upd
  ateString(1,"XXX")rs.insertRow()
• To update a record//move to expected
  recordrs.updateString(1,"XXX")rs.updateRow()
• To delete a record//move to expected
  recordrs.deleteRow()

14
Exception handling

• Connection connnull
• Statement stmtnull
• ResultSet rsnull
• try
• ...
• catch(SQLException ex)
• String sqlErr""
• while (ex ! null)
• sqlErrsqlErr "\nMessage "
  ex.getMessage()
• sqlErrsqlErr "\nSQLState "
  ex.getSQLState()
• sqlErrsqlErr "\nErrorCode "
  ex.getErrorCode()
• sqlErrsqlErr "\n-----------------------------
  ------------------------------"
• ex ex.getNextException()
• System.out.println(sqlErr)
• catch(Exception ex)
• ex.printStackTrace()
• finally
• try if(rs!null) rs.close() catch(Exception
  e)

15
Prepared Statement

• Create StatementPreparedStatement pstmt
  conn.prepareStatement(sql,rtype,concur)
• SQL with parameter using "?"E.g. select from
  product where product_id? and is_enable?
• Set parameter value
• setString()
• setFloat()
• setInt()
• setDate()

16
Prepared Statement

• Use parameter index only - can't use column name
  (start from 1)pstmt.setString(1,"XXX")
• Execute query or update normallyResultSet
  rspstmt.executeQuery()pstmt.executeUpdate()
• Re-usable

17
Question Answer