ObjectOriented Software Engineering

1
Object-OrientedSoftware Engineering

• Databases and Connection Strings

2
Introduction

• Why use a database to store data?
• What kinds of databases are there?
• How do you connect to a database?
• Simple SELECT example.
• Simple INSERT example.
• Simple UPDATE example.
• Simple DELETE example.
• Connection strings.
• What issues are there for web applications?

3
Persistence

• Its is common in computing to want to store data
  after an application has closed down.
• In the early days of computing, the only option
  was to store data in files (either in
  ASCII-styled or Binary formats).
• This is cumbersome for any appreciably sized set
  of data.
• Difficult to query easily
• Performance issues
• Potentially many files

4
Databases

• Although still technically file based, databases
  solve many of these problems by storing
  structured information.
• This structuring enables easy querying and
  retrieval of data.
• Also, DBMS (Database Management Systems) provide
  additional functionality
• Rollback
• Data locking
• Querying language (e.g. SQL).
• Backup

5
Database Types

• It can be easy to believe that there is only one
  type of database, the relational type.
• This is the simple table structure type.
• Each row in the table is a record and a record
  can have multiple fields (with one piece of data
  in each field).
• The benefits are that it is easy to understand
  and build.
• Problems exist however because not all data is
  easy to structure inside a relational table.
• Many-Many relationships have to be broken across
  multiple tables.
• The need for keys for identification of records.
• Duplicate data tends to be stored.

6
Database Types

• In object-oriented programs, we are dealing with
  objects, essentially containers of attributes.
• These objects in turn are related to other
  objects in an object hierarchy.
• Since we are dealing with objects it makes sense
  to store them in an object-oriented database.
• These exist (Jasmine etc.) but we wont be
  looking into them

7
Database Type

• There is another database type you may use in the
  future
• the XML-native Database
• XML is (basically) an ASCII file that contains
  structured information (looks a bit like HTML).
• To store data held in this form, we could parse
  the document and strip out the data, then send it
  to a database
• However, it makes more sense to store this kind
  of data in a database that understands what an
  XML document is (see Xindice on apache.org)

8
Connecting to a Database

• The Java method follows the ASP method quite
  closely.
• Load a database driver to connect to a specific
  database (Access, MySQL etc.)
• You then have to create a statement that will
  hold the SQL (for relational databases).
• Form your SQL syntax with any data you need to
  store then execute it.

9
Simple SELECT SQL

• lt_at_ page import"java.sql." gt
• lt
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").ne
  wInstance()
• Connection conn DriverManager.getConnection("j
  dbcodbcGuestBook")
• Statement statement conn.createStatement()
• ResultSet columns statement.executeQuery("SELEC
  T FROM GuestBook")
• while(columns.next())
• String email columns.getString("Email")
• String firstname columns.getString("FirstNam
  e")
• String lastname columns.getString("LastName"
  )
• gtlt email gtlt firstname gtlt lastname
  gtlt
• gt

10
Simple INSERT SQL

• lt_at_ page import"java.sql." gt
• lt_at_ page import"java.util." gt
• lt
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").ne
  wInstance()
• Connection conn DriverManager.getConnection("j
  dbcodbcGuestBook")
• Statement statement conn.createStatement()
• Enumeration parameters request.getParameterName
  s()
• if(parameters.hasMoreElements())
• String emailParam request.getParameter("ema
  il")
• String firstNameParam request.getParameter("
  firstName")
• String lastNameParam request.getParameter("l
  astName")
• statement.executeUpdate("INSERT INTO
  GuestBook (Email, FirstName, LastName) VALUES
  ('"emailParam"','"firstNameParam"','"lastName
  Param"')")
• gt

11
Simple UPDATE SQL

• lt_at_ page import"java.sql." gt
• lt_at_ page import"java.util." gt
• lt
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").ne
  wInstance()
• Connection conn DriverManager.getConnection("j
  dbcodbcGuestBook")
• Statement statement conn.createStatement()
• Enumeration parameters request.getParameterName
  s()
• if(parameters.hasMoreElements())
• String emailParam request.getParameter("ema
  il")
• String firstNameParam request.getParameter("
  firstName")
• String lastNameParam request.getParameter("l
  astName")
• statement.executeUpdate(UPDATE GuestBook SET
  Email emailParam , FirstName
  firstNameParam , LastName"lastNameParam"'"
  )
• gt

12
Simple DELETE SQL

• lt_at_ page import"java.sql." gt
• lt_at_ page import"java.util." gt
• lt
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").ne
  wInstance()
• Connection conn DriverManager.getConnection("j
  dbcodbcGuestBook")
• Statement statement conn.createStatement()
• Enumeration parameters request.getParameterName
  s()
• if(parameters.hasMoreElements())
• String emailParam request.getParameter("ema
  il")
• String firstNameParam request.getParameter("
  firstName")
• String lastNameParam request.getParameter("l
  astName")
• statement.executeUpdate(DELETE FROM
  GuestBook WHERE Email emailParam )
• gt

13
Connection Strings

• There are several ways to connect to a database
• Data Source Name
• Server Path
• Local file
• In the previous slides, we used a Data Source
  Name called GuestBook
• Connection conn DriverManager.getConnection("jdb
  codbcGuestBook")

14
Data Source Names

• In order to use a Data Source Name, we must be
  able to set one up.
• If it is our own machine we can use the Control
  Panel
• In Windows Start-gtRun-gtodbcad32
• This will bring up a dialog box (demo).
• You need to add a new DSN, select the appropriate
  database driver, give it a name to use in your
  code and point it at the database you wish to use.

15
Server Path

• Instead of using DSNs (which can be difficult if
  working with ISPs), you can use a server path
• Connection conn DriverManager.getConnection(//t
  rentdev/wwwdatajsp/OOSE/studentid/mydatabase.mdb")
  
• OR (better version!)
• Connection conn DriverManager.getConnection(
• jdbcodbcDRIVERMicrosoft Access Driver
  (.mdb) dbq//trentdev/wwwdatajsp/OOSE/studenti
  d/mydatabase.mdb)
• This is hard-coding the path into your source
  code (which may cause problems later) but it is
  direct and easy to use.

16
Local Path

• If you are working on a standalone machine (or
  you have full access to the server) and you dont
  want to use a DSN or server path, then you can
  use a local path
• Connection conn DriverManager.getConnection(C/
  tomcat/webapps/ROOT/mydatabase.mdb")
• (NB try the improved pattern from the previous
  slide if you need to explicitly state the driver
  to use.)
• The choice is (largely) yours!

17
Issues for Web Applications

• Where should the database code go?
• In the JSP? not a good separation of logic.
• In the Java application? ditto unless in a
  class set aside for that purpose which really
  makes it a
• Java Bean! good separation of logic usable by
  either a JSP page (via usetag) or a Java
  application (standard object).
• We can create objects of our Beans from either
  our JSP pages or from the web-enabled Java
  application.
• If we need to move or update our database
  connectivity code then we can just change the
  bean. Simple!
• Where should the database reside? If we have to
  lock tables to insert, do we end up crashing the
  whole system if too many users are trying to use
  it (U2 ticket fiasco anyone?)
• Should the database reside on a different server
  to the web server?

18
Issues for Web Applications

• Since we are using a web application, we have the
  issue of server resources.
• How many users can the server cope with?
• Should we initiate some kind of connection
  pooling?
• Are our database beans granular enough (i.e.
  does each database bean do more than one task?)
  if so this may needlessly drain resources.
• Are we only passing required data or are our SQL
  statements including unnecessary information?