Database and Web Service Basics

1
Database and Web Service Basics

• ECE 601 Mobile Computing Laboratory, Fall 2008
• Prof. Mikko Lipasti
• Department of Electrical and Computer Engineering
• University of Wisconsin Madison
• Based on lecture developed by Prof. Murali
  Annavaram, USC

2
Motivation for Learning

• Mobile applications become far more interesting
  when they can interact with outside world in real
  time
• Look for a nearby park using your current GPS
  location
• Interacting with world implies accessing a web
  service
• Most web services in turn access a backend
  database server to provide useful information
• Connecting the three pieces, also called
  three-tier-applications, stretches the mobile
  application spaces to interesting new domains

3
Three-Tier Architectures
Back End Database Server
Location Based Web Service
http get/post (or) RPC
rpc
SQL
LocationServer
DataBase

• Mobile device becomes the client
• Web service and database service are logically
  separated but can physically reside on the same
  machine
• For this class we will setup one machine as both
  the web service provider and database server

4
JSR 172 RPC

• Clients can consume a web service using HTTP
  post/get or RPC
• JSR 172 simplifies RPC setup and invocation
• Simplification is achieved through RPC Stubs
• Application only has a simple stub interface
  integrated into the code
• The actual implementation, such as connecting to
  the network and making a function call, are
  handled by JSR 172 runtime running in the device
• Client must be completely agnostic to where/how
  the function call is invoked
• Good client app must also be aware of the long
  time delay in function returns

5
Pictorial View of JSR 172
http//developers.sun.com/mobility/apis/articles/w
sa/Image4.gif
6
3 Steps in Creating Mobile Web Service Application

• Server creates a web service and announces how to
  invoke the service (typically as a WSDL document)
• Read the WSDL document and generate a stub
• Stub contains the web service address
• It describes RPC invocation inputs and return
  values
• Application then instantiates a stub and invokes
  the methods of the stub i.e. the remote function
  call itself
• Note that as of now mobile devices are only web
  service consumers and not service providers

7
Step1 Creating a Web Service

• Use Netbeans for simplifying the process of web
  service creation and deployment
• Remember that the web service is created NOT ON
  THE MOBILE DEVICE but on a backend server
• For now, use lab PC to act as a backend server
• Later, will deploy course server for this (TBD)
• You will use Netbeans to create, edit and deploy
  the web service
• Click on the link below for a full video of the
  screen shots of how to create the web service and
  deploy it
• The sample web service invokes a function call
  HelloWorld with one string as input and returns a
  new string that has Hello World prepended to the
  original string

Creating Web Service Video 1
8
Step 23 Creating a Stub from WSDL, Create Client

• Again use Netbeans to accelerate the Mobile
  Client development
• Create a mobile app
• Create a stub from WSDL
• Instantiate the stub
• Invoke the remote methods using the instantiated
  stub
• Click below for the screen shot process

Creating stubs from WSDl Video 2
9
Databases

• The web server typically connects to a backend
  database server to provide useful services to the
  client
• The web service typically connects to the
  database server using the standardized JDBC API
  (Java Database Connectivity)
• Run SQL queries using the connection pointer to
  access database contents
• More about JDBC
• http//java.sun.com/docs/books/tutorial/jdbc

http//java.sun.com/docs/books/tutorial/jdbc/overv
iew/index.html
10
Databases

• A database is structured storage for information
• A relational database is a collection of tables
• Each table has columns that show the fields of
  the table and each row contains one record
• Tables store information in a way that makes it
  easier to retrieve them with simple SQL
  statements
• Select from Table1
• Select grade from Table1, Table2 where
  Table1.Student_IDTable2.Student_ID

EE579DB
Table 1
Table 2
11
Connecting to a Database

• First tell Java runtime which database driver to
  use
• Use Netbeans-gtServices-gtDatabases-gtDrivers to see
  the list of available drivers (see the screen
  shot video 1 below to find out the driver list)
• We will use Derby database that comes as a
  packaged database with Netbeans for our demo
  purposes
• org.apache.derby.jdbc.ClientDriver is the default
  driver
• Feel free to use any other database (recommend
  CAE MySQL projects)
• Use the simple Class.forName method to
  automatically create an instance of a driver
• Class.forName("org.apache.derby.jdbc.ClientDriver"
  )
• Second, get a connection pointer to the database
• Specify the connection URL for the database and
  use getConnection method
• conn DriverManager.getConnection(jdbcderby//l
  ocalhost1527/sample create true, 601Class,
  601Passwd)
• Use createtrue, if you like the database to be
  created if one does not exist as yet, otherwise
  set it to false.
• You can optionally specify the username and
  password in the getConnection method if your
  database requires protection (if you leave the
  last two fields empty then no username password
  is used)

Database Driver Selection Video 3
12
Creating Database and Tables

• Creating Database
• Using createtrue you can create the database
  from the JDBC directly
• Netbeans interface also offers a simple way to
  create the database first (For Derby database
  only)
• See the screen shot video below for details
• Creating Table
• You can use SQL statements within web service to
  create tables
• Statement s conn.createStatement()
• String createString "create table Table1
  (Student_Name varchar (50) Student_ID int PRIMARY
  KEY)
• s.execute(createString)
• You can also use Netbeans interface to create
  tables and view the contents of the database
  directly (useful for debugging purposes)

Database Creation From Netbeans Video 4
13
Accessing Database Contents

• Web service application uses the parameters sent
  during RPC invocation to determine what the
  Mobile client is requesting
• Based on the request the server will retrieve
  data from the appropriate table(s) using SQL
  statements
• String retrieveString "select from Table2
  where (Grade ?)"
• psRetrieve conn.prepareStatement(retrieveString
  )
• psRetrieve.setString(1,A)
• result psRetrieve.executeQuery()
• while (result.next()) int student_id
  result.getInt(1)

14
Accessing Database Contents

• Mastering SQL syntax is key to efficiently store
  and retrieve information in the tables
• Performance depends on the choice of table layout
  and what SQL statements are used
• Think carefully how you plan to use the table
  before you create the table
• Tables should be properly normalized (no
  redundant fields)
• E.g. addressbook should only have a field for zip
  code, which is key into zip table that maps zip
  code to ltcity,stategt
• Organize your table for efficient access
  (creating index keys, making certain columns
  unique to prevent accidental reinsertions etc.)
• Read many online and textbook references that are
  provided to gain more understanding of the issues
• Most of your projects will have a backend
  database component and so get started now..