Connecting Database in JSP

1
Connecting Database in JSP

• CMT3332
• Lecture 4b

2
Saving Data to Database

• In order to work with a Database using JSP and
  Servlet, you need to use JDBC
• JDBC (Java Database Connectivity) is an API that
  lets you access any tabular data source from a
  Java application
• These files include databases, data in
  spreadsheets and flat files
• To use JDBC, you need to have the JDBC drivers
• Different databases have their corresponding JDBC
  driver.
• You need to put your JDBC driver (usually a jar
  file) in WEB-INF/lib directory of your web
  application in order to use the driver
• If you are using JDBC-ODBC driver, the driver is
  in the J2SDK and you do not to include the driver
  in your web application
• Different JDBC drivers present the same interface
  to programmer

3
JDBC Basic

• To know more about JDBC and how to use the code,
  please refer to http//java.sun.com/j2se/1.4.2/doc
  s/guide/jdbc/
• The following will give you a brief introduction
  on using JDBC
• Throughout the example, you will also revise some
  commonly use SQL statements
• We will NOT talk about how to write SQL
  statements explicitly as you should have learnt
  it before

4
JDBC Getting Started

• The first thing you need to do is check that you
  are set up properly
• JDBC API has been included in your J2SDK
• You need to install a JDBC driver if you are not
  using JDBC-ODBC bridge
• MySQL JDBC driver (Connector/J 3.1) downloading
  pagehttp//dev.mysql.com/downloads/connector/j/3
  .1.html
• You can find the dirver in the downloaded zip
  file mysql-connector-java-version-bin.jar
• Place it in the WEB-INF\lib folder

5
Setting Up a Database

• We will assume that the database COFFEEBREAK
  already exists.
• It should be a System DSN if you are using ODBC
  (e.g. Access)
• To know how to setup a System DSN, please refer
  to Lab 4
• Refer to MySQLs manual to see how to setup the
  database

6
Loading the Driver

• To establish a connection with the database will
  involve two steps
• Loading the driver
• Making the connection
• To load a driver, we will use the following
  statement Class.forName(ltdriver_namegt)
• Class.forName will create an instance of your
  driver and register it with the DriverManager
  automatically
• For JDBC-ODBC bridge, the driver name is
  "sun.jdbc.odbc.JdbcOdbcDriver"
• For MySQL, substitute the name by
  "com.mysql.jdbc.Driver"

7
Making the Connection

• The following code is used to establish the
  connection
• Connection con DriverManager.getConnection( u
  rl, "myLogin", "myPassword")
• The URL is generally your data source name or
  database system.
• For JDBC-ODBC bridge, the url will be
  jdbcodbcDSN where DSN is the data source name
  of your database
• For MySQL, the url (in general) will be
  jdbcmysql//hostport/database
• The second and third parameters represent the
  username and password used to connect the database

8
Creating a Table

• Table COFFEES contains the essential information
  about the coffees sold at The Coffee Break
• Type of COF_NAME is VARCHAR(32) (primary key)
• Type of SUP_ID is INTEGER
• Type of PRICE (price per pound) is FLOAT
• Type of SALES (monthly sales in lbs) is INTEGER
• Type of TOTAL (total sales in lbs) is INTEGER

9
Creating a Table

• Table SUPPLIERS gives information about each of
  the suppliers
• SUP_ID is the primary key and has the type
  INTEGER
• You can set SUP_NAME and STREET to type
  VARCHAR(40), CITY to type VARCHAR(20), STATE to
  type CHAR(2), ZIP to CHAR(5)

10
Entering Data into a Table

• A Statement object is what sends your SQL
  statement to the DBMS.
• It takes an instance of an active connection to
  create a Statement object.
• Statement stmt con.createStatement()
• SQLs are then executed using the appropriate
  execute method
• For a SELECT statement, the method to use is
  executeQuery .
• For statements that create or modify tables, the
  method to use is executeUpdate
• Example stmt.executeUpdate("INSERT INTO COFFEES
  " "VALUES ('Colombian', 101, 7.99, 0, 0)")

11
Getting Data from a Table

• JDBC returns results of excecuteQuery in a
  ResultSet object
• ResultSet rs stmt.executeQuery( "SELECT
  COF_NAME, PRICE FROM COFFEES")
• The above ResultSet rs contains rows of coffee
  names and prices
• Initially, the cursor in rs is pointing to just
  above the first row
• Using next method in ResultSet can move the
  current cursor to the next row and make the row
  becomes available for operations
• There are many other ways in moving your cursor
  around and will be talked about in later slide

12
Using the getXXX Methods in ResultSet

• We use the getXXX method of the appropriate type
  to retrieve the value in each column
• 1st column is of type VARCHAR is to be
  retrieved using getString()
• 2nd column is of type FLOAT is to be retrieved
  using getFloat()
• So, we can use a loop to retrieve the results in
  rs until there are no more rows in rs
• while (rs.next())
• String s rs.getString("COF_NAME")
• float n rs.getFloat("PRICE")
• System.out.println(s " " n)
• rs.next() will return false if no next row can be
  got

13
Using the getXXX Methods in ResultSet (contd)

• Instead of giving the column name to retrieve the
  data, you can also use the column index as the
  parameter in getXXX method
• String s rs.getString(1)float n
  rs.getFloat(2)
• Column index starts from 1, but not zero
• The column index refers to the index of your
  result set, but not your database table
• To know which getXXX method should be used for
  your database data, you can check this table
• http//java.sun.com/docs/books/tutorial/jdbc/basi
  cs/retrieving.html
• (under the topic Use of ResultSet.getXXX
  Methods to Retrieve JDBC Types )

14
Updating Tables

• The SQL statement to update one row might look
  like this
• String updateString "UPDATE COFFEES "
  "SET SALES 75 " "WHERE COF_NAME LIKE
  'Colombian'"
• Using the Statement Object stmt, JDBC can execute
  the update in the following way
• stmt.executeUpdate(updateString)
• To update multiple columns, you can set the SQL
  string to the following
• "UPDATE COFFEES SET SALES75, TOTAL75 "
  "WHERE COF_NAME LIKE 'Colombian'"

15
Using Prepared Statements

• Sometimes it is more convenient efficient to use
  a PreparedStatement object for sending a SQL
  statement to the database many times
• A SQL statement is required to create a
  PreparedStatement
• The SQL statement can take parameters that can
  use different values each time you execute it
• A PreparedStatement is created using an opened
  Connection object
• PreparedStatement updateSales
  con.prepareStatement("UPDATE COFFEES " "SET
  SALES ? WHERE COF_NAME LIKE ?")
• The above prepared statement takes two input
  parameters

16
Supplying Values for PreparedStatement Parameters

• You will need to supply values to be used in
  place of the question mark placeholders before
  you can execute a PreparedStatement object
• You do this by calling the setXXX methods in
  PreparedStatement class
• The first parameter of the setXXX methods is the
  placeholder index (count from 1)
• The second parameter is the value given to the
  parameter
• ExampleupdateSales.setInt(1, 75)
  updateSales.setString(2, "Colombian")
  updateSales.executeUpdate()
• Once a parameter has been set with a value, it
  will retain that value until it is reset to
  another value or the method clearParameters is
  called.

17
Using a Loop to Set Values

• You can often make coding easier by using a for
  loop or a while loop to set values for input
  parameters
• PreparedStatement updateSales
• String updateString "update COFFEES "
• "set SALES ? where COF_NAME like ?"
• updateSales con.prepareStatement(updateString)
• int salesForWeek 175, 150, 60, 155, 90
• String coffees "Colombian", "French_Roast",
  "Espresso", "Colombian_Decaf", "French_Roast_Decaf
  "
• int len coffees.length
• for(int i 0 i lt len i)
• updateSales.setInt(1, salesForWeeki)
• updateSales.setString(2, coffeesi)
• updateSales.executeUpdate()

18
Moving the Cursor in Scrollable Result Sets

• One of the new features in JDBC 2.0 is the
  ability to move a result set's cursor backward as
  well as forward
• Before you can take advantage of these features,
  however, you need to create a scrollable
  ResultSet object. Statement stmt
  con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV
  E, ResultSet.CONCUR_READ_ONLY)
• 1st parameter represents the cursor type, which
  can be TYPE_FORWARD_ONLY, TYPE_SCROLL_SENSITIVE,
  and TYPE_SCROLL_INSENSITIVE
• 2nd parameter represents the concurrency type,
  which can be CONCUR_READ_ONLY or CONCUR_UPDATABLE

19
Moving the Cursor Methods in ResultSet

• void beforeFirst()
• Moves the cursor to the front of this ResultSet
  object, just before the first row.
• void afterLast()
• Moves the cursor to the end of this ResultSet
  object, just after the last row.
• boolean first()
• Moves the cursor to the first row in this
  ResultSet object.
• next()
• Moves the cursor down one row from its current
  position.
• boolean next()
• Moves the cursor down one row from its current
  position.
• boolean previous()
• Moves the cursor to the previous row in this
  ResultSet object.

20
Moving the Cursor More Methods in ResultSet

• boolean absolute(int row)
• Moves the cursor to the given row number in this
  ResultSet object.
• If the row number is negative, the cursor moves
  the given number from the end
• boolean relative(int rows)
• Moves the cursor a relative number of rows,
  either positive or negative.
• int getRow()
• Retrieves the current row number.
• Four additional methods let you verify whether
  the cursor is at a particular position isFirst,
  isLast, isBeforeFirst, isAfterLast

21
Update the ResultSet to Affect the Database

• We can now use the new JDBC 2.0 methods in the
  ResultSet interface to insert/delete a row and
  modify a column value
• To delete a row,
• Move to the row you want to delete
• Call deleteRow method in ResultSet
• To modify a row,
• Move to the row you want to modify
• Call updateXXX methods in ResultSet
• The 1st parameter represents the column name /
  index
• The 2nd parameter represents the new value for
  the column
• Call updateRow method to update the current row
• You can call cancelRowUpdates method to cancel
  the updateXXX actions after the last updateRow
  call

22
Inserting a Row to Database Using ResultSet

• After you have create a scrollable and updatable
  ResultSet object uprs, you can do your insertion
  to your database table according to the following
  steps
• Move the cursor to the insert row
• uprs.moveToInsertRow()
• Set the five column values
• uprs.updateString("COF_NAME", "Kona")
• uprs.updateInt("SUP_ID", 150)
• uprs.updateFloat("PRICE", 10.99)
• uprs.updateInt("SALES", 0)
• uprs.updateInt("TOTAL", 0)
• Insert the new row into uprs and the respective
  table
• uprs.insertRow()

23
Moving Cursor after Insertion

• After inserting one row, you can continue to
  insert another without using moveToInsertRow()
  method
• If you want to continue your browsing in the
  ResultSet, you can use moveToCurrentRow() to go
  back to the origin row, or other cursor moving
  method (based on current row)