Pagina 1/6
E' assegnato il database books contenente le tabelle authors, publishers, titles,
authorisbn
•
•
•
•
trovare la lista dei libri pubblicati da un certo editore (immesso in una casella
testuale di un form)
◦ SELECT * FROM books.authorisbn INNER JOIN books.authors ON
authorisbn.authorid = authors.authorid INNER JOIN books.titles ON authorisbn.isbn
= titles.isbn INNER JOIN books.publishers ON titles.publisherid =
publishers.publisherid WHERE publishers.publishername like '%${param.editoreTxt}
%'
i libri/il libro che costano di più
◦ SELECT * FROM titles, publishers WHERE titles.publisherID =
publishers.publisherID AND price = (SELECT MAX(price) FROM titles)
gli autori dei libri
◦ SELECT CONCAT(firstname, " ", lastname) AS authorname FROM authors
il libro con isbn assegnato (gli isbn sono caricati in una casella a scorrimento)
◦ SELECT * FROM titles, publishers WHERE titles.publisherID =
publishers.publisherID AND isbn = '${param.isbnCbo}'
NB Tutte le query sono già assegnate
Attività preliminari
Il progetto NetBeans include nel file web.xml le informazioni sulla connessione al
database
<context-param>
<param-name>
javax.servlet.jsp.jstl.sql.dataSource
</param-name>
<param-value>
jdbc:mysql://localhost:3306/books,com.mysql.jdbc.Driver,root,
</param-value>
</context-param>
Pagina 2/6
Ricordarsi inoltre di
• aggiungere le librerie JSTL (si cerca tra le proprietà del Progetto la voce Libraries)
• aggiungere i driver del database MySQL
◦ Netbeans 5.5 e 6.1: prelevare il file mysql-connector-java-5.1.6.tar.gz all'indirizzo
http://dev.mysql.com/downloads/connector/j/5.1.html. Dal file tar.gz estrarre il file
mysql-connector-java-5.1.6-bin.jar e copiarlo (o spostarlo) nella cartella lib (da
creare) di WEB-INF
◦ Netbeans 6.5: i driver sono già inclusi nel pacchetto
• aggiungere nel codice le direttive taglib (librerie core ed sql almeno)
Soluzione primo punto
Codice
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Libri pubblicati da un certo editore</title>
<style type="text/css">
body,td,th {
font-family: Verdana;
Pagina 3/6
font-size: 16px;
}
</style>
</head>
<body>
<h3>Libri pubblicati da un certo editore</h3>
<p>
<form name="provaFrm" action="${request.requestURI}" method="GET">
Nome editore <input type="text" name="editoreTxt"/>
<input type="submit" name="inviaBtn" value="Invia"/>
</form>
</p>
<p>
<c:if test="${!empty param.inviaBtn}">
<sql:query var="result">
SELECT * FROM books.authorisbn INNER JOIN books.authors ON
authorisbn.authorid = authors.authorid INNER JOIN books.titles ON authorisbn.isbn
= titles.isbn INNER JOIN books.publishers ON titles.publisherid =
publishers.publisherid WHERE publishers.publishername like '%${param.editoreTxt}
%'
</sql:query>
<c:choose>
<c:when test="${result.rowCount>0}">
<c:forEach var="row" items="${result.rows}">
<c:out value="${row.isbn}"/> | <c:out value="${row.title}"/> | <c:out
value="${row.publishername}"/> | <c:out value="${row.editionNumber}"/> <br/>
</c:forEach>
</c:when>
<c:otherwise>
Non ci sono libri pubblicati da <c:out value="${param.editoreTxt}" />
</c:otherwise>
</c:choose>
</c:if>
</p>
</body>
</html>
Soluzione secondo punto
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
Pagina 4/6
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Libri che costano di più</title>
<style type="text/css">
body,td,th {
font-family: Verdana;
font-size: 16px;
}
</style>
</head>
<body>
<h3>Libri che costano di più</h3>
<sql:query var="result">
SELECT * FROM titles, publishers WHERE titles.publisherID =
publishers.publisherID AND price = (SELECT MAX(price) FROM titles)
</sql:query>
<c:forEach var="row" items="${result.rows}">
<c:out value="${row.isbn}"/> | <c:out value="${row.title}"/> | <c:out value="$
{row.publishername}"/> | <c:out value="${row.price}"/><br/>
</c:forEach>
</body>
</html>
Soluzione terzo punto
Codice
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Autori</title>
<style type="text/css">
body,td,th {
font-family: Verdana;
font-size: 16px;
Pagina 5/6
}
</style>
</head>
<body>
<h3>Autori</h3>
<sql:query var="result">
SELECT CONCAT(firstname, " ", lastname) AS authorname FROM authors
</sql:query>
<c:forEach var="row" items="${result.rows}">
<c:out value="${row.authorname}"/> <br/>
</c:forEach>
</body>
</html>
Soluzione quarto punto
Codice
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Libro con ISBN assegnato</title>
<style type="text/css">
Pagina 6/6
body,td,th {
font-family: Verdana;
font-size: 16px;
}
</style>
</head>
<body>
<h3>Libro con ISBN assegnato</h3>
<sql:query var="result">
SELECT isbn FROM titles
</sql:query>
<p>
<form name="provaFrm" action="${request.requestURI}" method="GET">
ISBN <select name="isbnCbo">
<c:forEach var="row" items="${result.rows}">
<option><c:out value="${row.isbn}"/></option>
</c:forEach>
</select>
<input type="submit" name="inviaBtn" value="Invia"/>
</form>
</p>
<p>
<c:if test="${!empty param.inviaBtn}">
<sql:query var="result">
SELECT * FROM titles, publishers WHERE titles.publisherID =
publishers.publisherID AND isbn = '${param.isbnCbo}'
</sql:query>
<c:choose>
<c:when test="${result.rowCount>0}">
<c:set var="row" value="${result.rows[0]}"/>
<c:out value="${row.isbn}"/> | <c:out value="${row.title}"/> | <c:out value="$
{row.publishername}"/> | <c:out value="${row.editionNumber}"/><br/>
</c:when>
<c:otherwise>
Non ci sono libri con isbn <c:out value="${param.isbnCbo}" />
</c:otherwise>
</c:choose>
</c:if>
</p>
</body>
</html>
