English

Vorlesung Datenbanken
7
Wintersemester 2013/14
Web Databases
Access to Web Databases:
• Servlets, Applets
• Java Server Pages
• P HP, P EAR
Languages: Java, P HP, Python, . . .
Prof. Dr. Dietmar Seipel
837
Vorlesung Datenbanken
Wintersemester 2013/14
7.1 Access to Web Databases by Servlets
Java Servlets can be called using a parametrised URL (Uniform Resource
Locator).
To guarantee the scalability of the architecture even for very large numbers of
users, the servlet engine executes the requests in parallel in several
independent threads (lightweight processes).
Sofware systems which are extended in that way – often in combination with
a Web server – are called application servers.
Prof. Dr. Dietmar Seipel
838
Vorlesung Datenbanken
Wintersemester 2013/14
Formular Interface
Prof. Dr. Dietmar Seipel
839
Vorlesung Datenbanken
Wintersemester 2013/14
Query Page in H TML
<html>
<head><title>Employees over a given Income</title></head>
<body>
<h1>Employees over a given Income</h1>
<blockquote>
<form action="https:/www.xyz.de/servlets/EmployeeSalary"
method="GET">
Please enter the minimum salary: <br>
<input type="TEXT" name="salary"/> <br>
<input type="SUBMIT" value="Submit Query"/>
</form>
</blockquote>
</body>
</html>
Prof. Dr. Dietmar Seipel
840
Vorlesung Datenbanken
Wintersemester 2013/14
The servlet implements a method doGet (or doPost):
• start of the database connection using J DBC
• execution of the query
• generation of the H TML page which is send to the browser
Parameters: The parameters which are necessary for formulating the query
are computed on the client side using a formular interface.
They are transferred to the servlet as an HttpServletRequest object
request. The servlet accesses a paramenter Par by calling
request.getParameter("Par").
Output: The generated H TML document is transferred to the
HttpServletResponse object using a PrintWriter object.
Prof. Dr. Dietmar Seipel
841
Vorlesung Datenbanken
Wintersemester 2013/14
GET Method
When using the HTTP GET method, the parameters are transferred from the
Web browser as part of the URL to the servlet, e.g.:
https:/www.xyz.de/servlets/EmployeeSalary?salary=30000
The parameter part (following the character “?”) of such a URL is often
called query string. Several parameter/value pairs can be transmitted
separated by “&”.
POST Method
When using the alternative HTTP POST method, the parameters are
transferred invisbly from the Web browser to the servlet;
they are not part of the URL.
Prof. Dr. Dietmar Seipel
842
Vorlesung Datenbanken
Wintersemester 2013/14
Java Servlet
import javax.servlet.*; import javax.servlet.http.*;
import java.io.*; import java.sql.*; import java.text.*;
public class EmployeeSalary extends HttpServlet {
public void doGet (
HttpServletRequest request, HttpServletResponse response )
throws ServletException, IOException {
protected Connection con = null;
initConnection();
response.setContentType("text/html");
PrintWriter out = response.getWriter();
String Salary = request.getParameter("salary");
generateHtmlTable(Salary);
}
}
Prof. Dr. Dietmar Seipel
843
Vorlesung Datenbanken
Wintersemester 2013/14
private void generateHtmlTable(String Salary)
{
printHtmlHeader(Salary);
try {
String query = "SELECT * FROM employee " +
"WHERE salary >= ’" + Salary + "’";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
out.println( "<tr><td>" + rs.getString("Fname") +
"</td><td>" + rs.getString("Minit") +
"</td><td>" + rs.getString("Lname") +
"</td><td>" + rs.getString("Salary") + "</td></tr>" );
}
}
catch (Exception mye){ out.println(mye.toString()); }
printHtmlBottom();
}
Prof. Dr. Dietmar Seipel
844
Vorlesung Datenbanken
Wintersemester 2013/14
Result of the Query (H TML, in Browser)
<html>
<head><title>Employees earning at least 30000 USD</title></head>
<body>
<table border="1" cellpadding="5">
<tr><th>Fname</th><th>Minit</th><th>Lname</th><th>Salary</th></tr>
<tr><td>John</td><td>B</td><td>Smith</td><td>30000</td></tr>
<tr><td>Franklin</td><td>T</td><td>Wong</td><td>40000</td></tr>
<tr><td>Jennifer</td><td>S</td><td>Wallace</td><td>43000</td></tr>
<tr><td>Ramesh</td><td>K</td><td>Narayan</td><td>38000</td></tr>
<tr><td>James</td><td>E</td><td>Borg</td><td>55000</td></tr>
</table>
</body>
</html>
Prof. Dr. Dietmar Seipel
845
Vorlesung Datenbanken
Wintersemester 2013/14
A servlet should check the parameters before using them in S QL queries.
Database Injection Problem
If we build the query as
String query = "SELECT * FROM employee " +
"WHERE salary >= " + Salary;
then a user could enter “30000; DELETE * FROM employee” in the
field for salary of the H TML formular. Then the query
SELECT * FROM employee
WHERE salary >= 30000; DELETE * FROM employee
would be constructed; its execution would delete all tuples of the relation
employee.
Prof. Dr. Dietmar Seipel
846
Vorlesung Datenbanken
Wintersemester 2013/14
H TML Page with Servlet Calls
<html>
<head><title>Employees over a given Income</title></head>
<body>
<h1>Employees over a given Income</h1>
<ul>
<li> <a href=".../servlets/EmployeeSalary?salary=0">
list all employess </a> </li>
<li> <a href=".../servlets/EmployeeSalary?salary=30000">
list employess over 30000 USD </a> </li>
<li> <a href=".../servlets/EmployeeSalary?salary=60000">
list employess over 60000 USD </a> </li>
</ul>
</body>
</html>
Prof. Dr. Dietmar Seipel
847
Vorlesung Datenbanken
Wintersemester 2013/14
Prof. Dr. Dietmar Seipel
848
Vorlesung Datenbanken
Wintersemester 2013/14
Access to Web Databases by Java Applets
• Applets are Java programs which are executed at the client side.
• Applets are mobile code, which is transferred from the Web server to the
client, and which is executed at the client side.
Prof. Dr. Dietmar Seipel
849
Vorlesung Datenbanken
Wintersemester 2013/14
7.2 Java Server Pages
• H TML pages which consist of both static and dynamically generated
parts
• Java Server Pages (JSPs) allow for embedding Java code fragments in
an H TML page. The Web server initiates their execution before the page
is send to the client.
• Advantage: The visualisation, i.e., the H TML code, is statically part of
the document.
• The Java code fragments can be components (Java beans) that are part
of separate files. This encreases readability and reuse.
• Microsoft alternatively offers Active Server Pages (ASPs).
Prof. Dr. Dietmar Seipel
850
Vorlesung Datenbanken
Wintersemester 2013/14
5 additional tags:
• <%@page attributes of the directive%>
guiding the translation of the JSP by various attributes
• <%!declaration%>
e.g., a Java operation which later is executed in the page several times
• <%=expression%>
corresponds to <% out.print(expression) %>
• <%java code fragment%>
• <%--comment--%>
is not transferred to the generated H TML document
Prof. Dr. Dietmar Seipel
851
Vorlesung Datenbanken
Wintersemester 2013/14
Java Server Page with Java Code
<%@page import="java.sql.*"%>
<%!Connection con = null;%>
<%initConnection();%>
<%!String generateHtmlTable(String Salary) ...%>
<html>
<head><title>Employees over a given Income</title></head>
<body>
<ul>
<li>
0 <%=generateHtmlTable("0")%>
</li>
<li> 30000 <%=generateHtmlTable("30000")%> </li>
<li> 60000 <%=generateHtmlTable("60000")%> </li>
</ul>
</body>
</html>
Prof. Dr. Dietmar Seipel
852
Vorlesung Datenbanken
Wintersemester 2013/14
String generateHtmlTable(String Salary)
{
StringBuffer result = new StringBuffer();
result.append("<table>");
try {
String query = "SELECT * FROM employee " +
"WHERE salary >= ’" + Salary + "’";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
result.append( "<tr><td>" + rs.getString("Fname") +
"</td><td>" + rs.getString("Minit") +
"</td><td>" + rs.getString("Lname") +
"</td><td>" + rs.getString("Salary") + "</td></tr>" );
}
}
catch (Exception mye){ result.append(mye.toString()); }
result.append("</table>");
return result.toString();
}
Prof. Dr. Dietmar Seipel
853
Vorlesung Datenbanken
Wintersemester 2013/14
Computation in P ROLOG / F N Query
The relational database is accessed using an O DBC query. The data types of
the returned rows of the table employee are given by Types.
generate_html_table(Salary, table:Rows) :concat(’SELECT fname, minit, lname, salary \
FROM employee WHERE salary >= ’, Salary, Query),
Types = [types([atom,atom,atom,integer])],
findall( Row,
( odbc_query(mysql, Query, row(F,M,L,S), Types),
Row = tr:[td:[F], td:[M], td:[L], td:[S]] ),
Rows ).
For a every result row(F,M,L,S), a P ROLOG term tr:[td:[F],
td:[M], td:[L], td:[S]] is constructed, which represents a table
row in H TML.
Prof. Dr. Dietmar Seipel
854
Vorlesung Datenbanken
Wintersemester 2013/14
Java Server Page with a Call to a Java Bean
<%@page import="jspdemo.EmployeeBean"%>
<jsp:useBean id="mybean" class="jspdemo.EmployeeBean"
scope="application"/>
<html>
<head><title>Employees over a given Income</title></head>
<body>
<ul>
<li>
0 <%=mybean.generateHtmlTable("0")%>
</li>
<li> 30000 <%=mybean.generateHtmlTable("30000")%> </li>
<li> 60000 <%=mybean.generateHtmlTable("60000")%> </li>
</ul>
</body>
</html>
Prof. Dr. Dietmar Seipel
855
Vorlesung Datenbanken
Wintersemester 2013/14
Java Bean
package jspdemo;
import java.sql.*;
public class EmployeeBean
{
Connection con = null;
public EmployeeBean() {
initConnection();
}
public String generateHtmlTable(String Salary)
...
}
There must exist a constructor without parameters.
Prof. Dr. Dietmar Seipel
856
Vorlesung Datenbanken
Wintersemester 2013/14
Access to a JSP
• The JSP is translated into a servlet.
• The browser requests the JSP.
• The Web server initiates the execution of the generated servlet.
• During the execution, the Java code fragments (including the tags for
the expressions) are eliminated, and they are replaced by the results that
are generated during the execution.
• The resulting pure H TML page is send to the client.
• The Web browser displays this page.
Prof. Dr. Dietmar Seipel
857
Vorlesung Datenbanken
Wintersemester 2013/14
A Template Implementation in P ROLOG (cf. Server Faces)
<html>
<head><title>Employees earning at least 30000 USD</title></head>
<body>
<table border="1" cellpadding="5">
<tr>
<th BGCOLOR="#dfdfff">Fname</th>
<th BGCOLOR="#dfdfff">Minit</th>
<th BGCOLOR="#dfdfff">Lname</th>
<th BGCOLOR="#dfdfff">Salary</th>
</tr>
<sequence database="company" sql="where salary >= 30000 order by salary">
<tr>
<td><select table="employee" attribute="fname"/></td>
<td><select table="employee" attribute="minit"/></td>
<td><select table="employee" attribute="lname"/></td>
<td><select table="employee" attribute="salary"/></td>
</tr>
</table>
</body>
</html>
Prof. Dr. Dietmar Seipel
858
Vorlesung Datenbanken
Wintersemester 2013/14
7.3 Web Database Connections in P HP
• originally, P HP was designed as a scripting language,
for generating H TML
• functional language, since P HP Version 4 also object–oriented
• very broad support of the providers
(classical environment: Apache / P HP 4 or P HP 5 / MySQL)
• lower costs for development and maintenance than for servlet
applications
Prof. Dr. Dietmar Seipel
859
Vorlesung Datenbanken
Wintersemester 2013/14
databases can be accessed from P HP in two ways:
• directly over the P HP interface implementation of the database
(e.g., php–mysql)
• over the P EAR database interface
(P HP Extension and Application Repository)
P EAR serves as a layer of abstraction in P HP and supports (almost) all
common database types, including
MyS QL, PostgreS QL, Oracle 7/8/8i, Interbase,
O DBC, Microsoft S QL, . . .
Prof. Dr. Dietmar Seipel
860
Vorlesung Datenbanken
Wintersemester 2013/14
P EAR Database Model as an Abstraction Layer:
• Advantage: independent of database specific extensions (cf., O DBC),
thus easily portable (e.g., from Mysql to Postgres)
• Disadvantage: no database specific features
(e.g., view mechanisms under Postgres)
Example (P EAR)
• Start of a connection over DSN (data source names):
mysql://user:very_secret@localhost/company
• Query
• Shutdown of the connection
Prof. Dr. Dietmar Seipel
861
Vorlesung Datenbanken
Wintersemester 2013/14
<?php
require_once(’DB.php’);
//
$dsn = array(
’phptype’ => ’mysql’, ’username’ => ’user’,
’password’ => ’very_secret’, ’hostspec’ => ’localhost’,
’database’ => ’company’
);
$db =& DB::connect($dsn);
if(PEAR::isError($db)){
die($db->getMessage());
}
$sql = "SELECT * FROM employee";
$result =& $db->query($sql);
if(PEAR::isError($result)){
die($result->getMessage());
}
while($result->fetchInto($row)){
// $row: Array of result data
}
$db->disconnect();
?>
Prof. Dr. Dietmar Seipel
862
Vorlesung Datenbanken
Wintersemester 2013/14
same example (Java like):
<?php ...
$db = new DB();
$connection = $db->connect($dsn);
if(PEAR::isError($connection)){
die($connection->getMessage());
} ...
$result = $connection->query($sql); ...
while($row = $result->fetchRow()){
// ...
}
$connection->disconnect();
?>
Prof. Dr. Dietmar Seipel
863
Vorlesung Datenbanken
Wintersemester 2013/14
prepare and execute Statements for Select Queries
standard query:
$result = $db->query(
"SELECT * FROM employee WHERE SALARY >= 30000");
prepared query with scalar:
$prepared = $db->prepare(
"SELECT * FROM employee WHERE SALARY >= ?");
$result = $db->execute($prepared, 30000);
prepared query with arrays:
$prepared = $db->prepare(
"SELECT * FROM employee WHERE SALARY >= ? AND SEX = ’?’");
$result = $db->execute($prepared, array(30000, ’F’));
prepared statement for several queries:
$prepared = $db->prepare(
"SELECT * FROM employee WHERE SALARY >= ? AND SEX = ’?’");
$data = array(array(30000, ’F’), array(30000, ’M’));
$results = $db->executeMultiple($prepared, $data);
foreach ($results as $result){
// ... }
Prof. Dr. Dietmar Seipel
864
Vorlesung Datenbanken
Wintersemester 2013/14
autoPrepare and autoExecute for Insert/Update Queries
• autoPrepare generates complete Update or Insert Statements,
which are executed using execute later
• autoExecute generates and executes Update or Insert Statements
• the query mode is set by
– DB_AUTOQUERY_INSERT and
– DB_AUTOQUERY_UPDATE,
respectively
Prof. Dr. Dietmar Seipel
865
Vorlesung Datenbanken
Wintersemester 2013/14
Example (S QL, P HP): John Smith gets a raise in salary
UPDATE employee
SET SALARY=40000
WHERE SSN = ’888888888’’’;
<?php
$field_values = array(’SALARY’ => 40000);
$result = $db->autoExecute(
’employee’,
// table
$field_values,
// Werte
DB_AUTOQUERY_UPDATE, // Mode
"SSN=’888888888’"
// WHERE--Bedingung
);
// or:
$field_names = array(’SALARY’);
$field_values = array(’40000’);
$prepared = $db->autoPrepare(
’employee’, $field_names, DB_AUTOQUERY_UPDATE, "SSN = ’888888888’");
$result =& $db->execute($prepared, $field_values);
?>
Prof. Dr. Dietmar Seipel
866
Vorlesung Datenbanken
Wintersemester 2013/14
Result Sets of Queries: Arrays
The result sets can be stored as
• indexed arrays:
<?php
while($row = $result->fetchRow()){
// $row indexed
}
?>
• associative arrays (the array index uses the DB columns):
<?php
$db->setFetchMode(DB_FETCHMODE_ASSOC);
while($row = $result->fetchRow()){
echo $row[’FNAME’];
echo $row[’LNAME’];
echo $row[’SEX’];
echo $row[’SALARY’];
}
?>
Prof. Dr. Dietmar Seipel
867
Vorlesung Datenbanken
Wintersemester 2013/14
Literature
• Rasmus Lerdorf, Kevin Tatroe:
Programming P HP,
O’Reilly–Verlag, 2002.
• P EAR Online Manual:
http://pear.php.net/manual/en/
Prof. Dr. Dietmar Seipel
868