DT228/3 Web Development

1. DT228/3 Web Development Databases

2. Database • Almost all web application on the net access a database • e.g. shopping sites, message boards, search engines • Relational databases (uses tables) are the most common type used • Small application may use just one table, larger applications may have have hundreds of tables • In JSP, database access can be done using scriplets or using JSTL • JSTL supplies the SQL library to enable database access

3. Relational DBs Data is stored in tables. Rows and columns in tables can berelated to rows and columns in other tables in the dB Each table usually has a primary key Structured Query Language (SQL) is used to query the database Common SQL Statements: SELECT, INSERT, UPDATE, DELETE customer IDNamePhone 1 John 879687 2 Liz 975645 3 Rory 321544 SELECT * from customers? SELECT name, phone from customers where customer_ID = 2 ? Column Row

4. Relational DBs INSERT INSERT INTO CUSTOMERS (customer_ID, name, phone) VALUES (5, “JOHN”, “875895”) UPDATE UPDATE CUSTOMERS SET NAME = “Robert” WHERE CUSTOMER_ID = 1 DELETE DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 2

5. Some Database concepts • To access a database from a web application, need a path or connection to the database • To support multiple connections to the same database- have connection pooling. Works like a group of connections that are used by clients as needed and “put back” in the pool as needed. • Each database engine (e.g. Oracle, SQLServer, Access) needs its own database driver (The driver is just a piece of software that translate SQL calls between the web application to the database). • Web applications have to “load” the appropriate driver in order to connect to the database

6. Some Database concepts • Java provides the JDBC API (Java Database ConnectivityAPI) to enable a uniform interface to differentdatabase engines • JDBC enables java code that accesses a databases to beportable from one database to another (provided the correct driver is used). • Many databases have an ODBC (Open DatabaseConnectivity) interface (e.g. access) • Sun provides a JDBC-ODBCdriver for development purposes

7. Some Database concepts • JDBC-ODBC driver enables java (and JSP) applications to connect to ODBC databases, using the JDBC API • Note: In production environment, should use a production quality driver from the database vendor

8. Databases and web application • Variety of databases available for use by web applications • typically will use relational database with support for Structured Query Language • Examples of common databases used: SQL server, MySQL, Oracle, Access

9. Datasource names (DSNs) • To use a database in a web application need to say: - What the database is called - Where is resides - What driver is required by the database • ODBC databases allow a Data source name to be used as a quick way to specify the above within the applicaton • e.g. , rather than saying “I want to connect to an Access2000 database named Exams.mdb in d:\samples\database directory in every JSP page that uses the particular database, will rather create a DSN named Exams • Note: not all databases will support DSNs.

10. Datasource names (DSNs) • To SET UP a data source name in Windows: • Go to Windows control panel / administrative tools /system DSN (NOT user DSN) • Add a new database source, selecting appropriate driver • Note: Will use microsoft access for development purposes – it’s an ODBC database and supports DSNs

11. Accessing a database from JSP • Need to identify and connect to the database to be used with the JSP page: • 1) Global datasource: Can specifying a default datasource in a Tomcat configuration file for the application called the web.xml file. The datasource will automatically be made available to the JSP if done this way – Good approach for larger applications. • OR • 2) Direct from JSP: by specifying the database details directly within the JSP page. Use instead of (1) all the time OR just to override the default data source specified in (1)

12. Accessing a database directly from JSP page • Will cover 1) on next course. Good for larger applications. • Will use 2) for development purposes. (Useful for smaller applications - but does not support connection pooling) • Using option 2): • Can use java code (via scriptlets) OR JSTL <SQL> tags to access databases. We’ll use JSTL here. • Full description of <SQL> tags in JSTL documentation • Note: To use JSTL <sql> tags, need to include the appropriate <taglib> directive into the JSP page

13. Accessing a database directly from JSP page • Use the <SQL:setDataSource> action from the JSTL SQL library and specify the attributes it needs • The <SQL:setDataSource> action creates a database connection in the background • Note: Can use the datasource name in the action, if available – as shown on next page. Otherwise, have to specify direct database and driver locations.

14. Accessing a database from JSPwith a Data source name setDataSource tag has seven attributes: • <sql:setDataSource • var=“productsdb" driver=“sun.jdbc.odbc.JdbcOdbcDriver" url="jdbc:odbc:products” • user = “cindy” • password = “DIT” • scope = “session” • datasource = .. • /> Will use this name in any other SQL statements To access the Db DSN Can specify username/password - optional

15. Accessing a database from JSP(without a DSN) • Note: IF issue in setting up DSN (e.g. permissions problem in Labs).. Have to specify the database more specifically within the <SQL:setDataSource> tag <sql:setDataSource var ="shopDb" scope = "session" driver = "sun.jdbc.odbc.JdbcOdbcDriver" url ="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\Assignment\Shopping.mdb" /> Instead of the DSN, have to enter in exact URL required for that database.. Including full path to the database

16. Querying a database • To query a database… just use the sql: query tag • Example Variable to hold query result • <sql:query var = “productResult” • dataSource = “${productsdb}” • SELECT * FROM Products • WHERE ProductID = ? • <sql:param value = “${param.productNAME}”>/ • <sql:query> DSN as defined in the setDAtasource Holds the value of to be transferred into the ? placeholder. • Will return all rows from Products table with a product ID name same as that supplied in the product name parameter

17. Querying a Database • Querying, where parameter value is already known (i.e. not passed in…) • <sql:query var = “productResult” • dataSource = “${productsdb}” • SELECT * FROM Products • WHERE ProductType = ‘grocery’ • <sql:query> Note: DOESN’T use “ “. !

18. Querying a database: <sql:query> • <sql:query> tag has 6 attributes • dataSource • sql • maxRows • startRows • var • scope • (p 151 of specification) • Name of datasource. Optional. Needed if setDataSource tag used • SQL statement. Mandatory. unless specified as the body. • Optional. Maximum # of rows to show in result. Default is all rows • Optional. • Mandatory. The name of the variable to store the result. • Optional. The scope for the query results - page (default), request, session or application. Set this carefully if you want to carry query results through to another page

19. Scope of a query… A JSP page itemsearch.jsp queries a database for a list of items that can be added to a shopping cart The results of the query search will be displayed on another JSP page called results.jsp The scope of the query that is executed in itemsearch.jsp needs to be ‘request’. Otherwise, queryresults will have ‘disappeared’ when itemsearch.jsp haspassed control to results.jsp (because default scope is‘page’ for queries).

20. Scope of a query… • <sql:query var = “productResult” scope = “request” • dataSource = “${productsdb}”> • SELECT * FROM Products • WHERE ProductID = ? • <sql:param value = “${param.productNAME}”> • </sql:query> Specify the scope of the query results if the results need to be available to the request, session or application. The default scope is page.