JSTL and JDBC
<%-- ex1.jsp --%>
<%-- First example of
using sql taglib --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html;
charset=windows-1252"/>
<title>ex1</title>
</head>
<body>
<%-- set data source; other attributes
are password and user
--%>
<sql:setDataSource driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:registrar" />
<%--Execute query; result stored in var --%>
<sql:query
sql="select
count(*) from OFFERINGS where INSTRUCTOR = 'Avitabile'"
var="results"
/>
<%-- Assign first field of first row to
variable --%>
<c:set
value="${results.rowsByIndex[0][0]}" var="count"/>
<%-- Print variable --%>
<c:out
value="${count}"/> courses offered by Avitabile
</body>
</html>
<%-- ex2.jsp --%>
<%-- Same problem - but
uses Oracle instead of Access --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=windows-1252"/>
<title>ex2</title>
</head>
<body>
<%-- set data source; other attributes
are password and user
--%>
<sql:setDataSource
dataSource="jdbc:oracle:thin:@srv12.nysed.gov:1526:test,oracle.jdbc.driver.OracleDriver,javauser9/javauser9"/>
<sql:query
sql="select
count(*) from OFFERINGS where INSTRUCTOR = 'Avitabile'"
var="results"
/>
<c:set
value="${results.rowsByIndex[0][0]}" var="count"/>
<c:out
value="${count}"/> courses offered by Avitabile
</body>
</html>
<%-- ex3.jsp --%>
<%-- Information on all
courses taught by Avitabile --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex3</title>
</head>
<body>
<sql:setDataSource driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:registrar" />
<sql:query
sql="select
CRN, COURSE, SECTION, DATES, TIMES, ROOM from OFFERINGS where INSTRUCTOR = 'Avitabile'"
var="results"
/>
<TABLE BORDER="1">
<TR>
<TH>CRN</TH>
<TH>Course</TH>
<TH>Section</TH>
<TH>Date</TH>
<TH>Time</TH>
<TH>Room</TH>
</TR>
<%-- loop over each row --%>
<c:forEach
var="row" items="${results.rowsByIndex}"
>
<tr>
<%-- loop over each field --%>
<c:forEach var="field"
items="${row}">
<td>
<c:out value="${field}" />
</td>
</c:forEach>
</tr>
</c:forEach>
</table>
</body>
</html>
<%-- ex4.jsp --%>
<%-- Information on all
courses taught by Avitabile --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex4</title>
</head>
<body>
<sql:setDataSource driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:registrar" />
<sql:query
sql="select
CRN, COURSE, SECTION, DATES, TIMES, ROOM from OFFERINGS where INSTRUCTOR = 'Avitabile'"
var="results"
/>
<TABLE BORDER="1">
<TR>
<TH>CRN</TH>
<TH>Course</TH>
<TH>Section</TH>
<TH>Date</TH>
<TH>Time</TH>
<TH>Room</TH>
</TR>
<%-- loop over each row --%>
<c:forEach
var="row" items="${results.rowsByIndex}"
>
<%-- print each field of row --%>
<tr>
<td>
<c:out value="${row[0]}" />
</td>
<td>
<c:out value="${row[1]}" />
</td>
<td>
<c:out value="${row[2]}" />
</td>
<td>
<c:out value="${row[3]}" />
</td>
<td>
<c:out value="${row[4]}" />
</td>
<td>
<c:out value="${row[5]}" />
</td>
</tr>
</c:forEach>
</table>
</body>
</html>
<%-- ex5.jsp --%>
<%-- Information on all
courses taught by Avitabile --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex5</title>
</head>
<body>
<sql:setDataSource driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:registrar" />
<sql:query
sql="select
CRN, COURSE, SECTION, DATES, TIMES, ROOM from OFFERINGS where INSTRUCTOR = 'Avitabile'"
var="results"
/>
<TABLE BORDER="1">
<TR>
<TH>CRN</TH>
<TH>Course</TH>
<TH>Section</TH>
<TH>Date</TH>
<TH>Time</TH>
<TH>Room</TH>
</TR>
<%-- use rows attribute of results --%>
<c:forEach var="row"
items="${results.rows}" >
<%-- print each field of row
using fieldname --%>
<tr>
<td>
<c:out value="${row.CRN}"
/> </td>
<td>
<c:out value="${row.COURSE}"
/> </td>
<td>
<c:out value="${row.SECTION}"
/> </td>
<td>
<c:out value="${row.DATES}"
/> </td>
<td>
<c:out value="${row.TIMES}"
/> </td>
<td>
<c:out value="${row.ROOM}"
/> </td>
</tr>
</c:forEach>
</table>
</body>
</html>
ex6.htm
<HTML>
<HEAD>
<TITLE>Courses for an
instructor</TITLE>
</HEAD>
<BODY>
<FORM
ACTION="ex6.jsp"
METHOD="POST">
Enter the instructor: <INPUT TYPE="text"
NAME="instructor">
<BR><INPUT TYPE="submit"
VALUE="Enter">
</FORM>
</HTML>
<%-- ex6.jsp --%>
<%-- Information on all
courses taught by input instructor --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex6</title>
</head>
<body>
<sql:setDataSource driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:registrar" />
<%-- Note prepared statement --%>
<sql:query
sql="select
CRN, COURSE, SECTION, DATES, TIMES, ROOM from OFFERINGS where INSTRUCTOR =
?"
var="results">
<%-- use param
tag to fill in ? --%>
<sql:param value="${param.instructor}"
/>
</sql:query>
<%-- rowCount
attribute gives rows returned --%>
<c:if
test="${ results.rowCount >= 1 }">
<TABLE BORDER="1">
<TR>
<TH>CRN</TH>
<TH>Course</TH>
<TH>Section</TH>
<TH>Date</TH>
<TH>Time</TH>
<TH>Room</TH>
</TR>
<c:forEach var="row"
items="${results.rows}" >
<tr>
<td>
<c:out value="${row.CRN}"
/> </td>
<td>
<c:out value="${row.COURSE}"
/> </td>
<td>
<c:out value="${row.SECTION}"
/> </td>
<td>
<c:out value="${row.DATES}"
/> </td>
<td>
<c:out value="${row.TIMES}"
/> </td>
<td>
<c:out value="${row.ROOM}"
/> </td>
</tr>
</c:forEach>
</table>
</c:if>
<c:if
test="${ results.rowCount == 0 }">
No courses taught by <c:out value="${param.instructor}"/>!
</c:if>
</body>
</html>
ex7.htm
<HTML>
<HEAD>
<TITLE>Change title of
course</TITLE>
</HEAD>
<BODY>
<FORM
ACTION="ex7.jsp"
METHOD="POST">
Enter the course number: <INPUT TYPE="text" NAME="coursenumber">
<P>
Enter new title: <INPUT TYPE="text"
NAME="title" size="60">
<BR><INPUT TYPE="submit"
VALUE="Enter">
</FORM>
</HTML>
<%-- ex7.jsp --%>
<%-- Do an update --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex7</title>
</head>
<body>
<sql:setDataSource driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:registrar" />
<%-- Note that the sql
tag of sql:query is not used
--%>
<sql:query var="results">
select *
from COURSES
where COURSE = ?
<sql:param value="${param.coursenumber}"
/>
</sql:query>
<c:if
test="${ results.rowCount == 1 }">
<%-- update tag --%>
<sql:update>
update
COURSES
set
DESCRIPTION = ?
where
COURSE = ?
<sql:param value="${param.title}"
/>
<sql:param value="${param.coursenumber}"
/>
</sql:update>
Update made!
</c:if>
<c:if
test="${ results.rowCount == 0 }">
No course named <c:out value="${param.coursenumber}"/>!
</c:if>
</body>
</html>
ex8.htm
<HTML>
<HEAD>
<TITLE>Registration</TITLE>
</HEAD>
<BODY>
<FORM
ACTION="ex8.jsp"
METHOD="POST">
Enter the username:
<input type=text name="username">
<P>
Enter the password:
<input type=password name="password">
<P>
<input type = submit
value="Submit">
</form>
</body>
</HTML>
<%-- ex8.jsp --%>
<%-- Registration with
hidden fields --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex8</title>
</head>
<body>
<sql:setDataSource driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:registrar" />
<sql:query var="result1">
select COUNT(*)
as MATCHES
from USERS
where USERID
like ? and PASSWORD like ?
<sql:param value="${param.username}"
/>
<sql:param value="${param.password}"
/>
</sql:query>
<sql:query var="result2">
select
COUNT(*)
from USERROLES
where ROLEID =
1 and USERID like ?
<sql:param value="${param.username}"
/>
</sql:query>
<c:choose>
<c:when test="${result1.rowsByIndex[0][0] >
1}">
<P>Too many matches!
</c:when>
<c:when test="${result1.rowsByIndex[0][0]==
0}">
<P>Not a legal user
name/password
</c:when>
<c:when test="${result2.rowsByIndex[0][0]==
0}">
<P>Not a student
</c:when>
<c:otherwise>
<sql:query var="result3">
select
FNAME, LNAME
from
USERS
where
USERID like ?
<sql:param value="${param.username}"
/>
</sql:query>
<P>Pick a course, <c:out value="${result3.rowsByIndex[0][0]}"/>
<c:out
value="${result3.rowsByIndex[0][1]}"/>
<FORM ACTION="ex9.jsp"
METHOD="POST">
<sql:query var="result4">
select
distinct OFFERINGS.COURSE, DESCRIPTION
from OFFERINGS
inner join COURSES on
OFFERINGS.COURSE =
COURSES.COURSE
</sql:query>
<select name='course'>
<c:forEach var="row"
items="${result4.rowsByIndex}" >
<option value='<c:out
value="${row[0]}"/>'>
<c:out value="${row[0]}"/>
<c:out value="${row[1]}"/>
</option>
</c:forEach>
</select>
<INPUT type='hidden' name='username'
value='<c:out value="${param.username}"/>'>
<input type = submit
value="Submit">
</FORM>
</c:otherwise>
</c:choose>
</body>
</html>
<%-- ex9.jsp --%>
<%-- Registration with
hidden fields --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex9</title>
</head>
<body>
<sql:setDataSource driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:registrar" />
<sql:query var="result1">
select CRN,
SECTION, DATES, TIMES, INSTRUCTOR, ROOM
from OFFERINGS
where COURSE
like ?
<sql:param value="${param.course}"
/>
</sql:query>
<FORM ACTION="ex10.jsp"
METHOD="POST">
<TABLE BORDER='1'>
<TR>
<TH>Choice</TH>
<TH>CRN</TH>
<TH>Section</TH>
<TH>Date</TH>
<TH>Time</TH>
<TH>Instructor</TH>
<TH>Room</TH>
</TR>
<c:forEach var="row"
items="${result1.rowsByIndex}" >
<TR>
<TD>
<input type='radio'
name='crn'
value='<c:out
value="${row[0]}"/>'>
</TD>
<c:forEach var="field"
items="${row}" >
<TD>
<c:out value="${field}"
/>
</TD>
</c:forEach>
</c:forEach>
</TABLE>
<INPUT type='hidden' name='username'
value='<c:out value="${param.username}"/>'>
<input type = submit
value="Submit">
</FORM>
</body>
</html>
<%-- ex10.jsp --%>
<%-- Registration with
hidden fields --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<%@ taglib
uri="http://java.sun.com/jstl/fmt"
prefix="fmt"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex10</title>
</head>
<body>
<sql:setDataSource driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:registrar" />
<c:choose>
<c:when
test="${param.crn == null}">
<P>No section
chosen. Hit back button to return.
</c:when>
<c:otherwise>
<sql:query var="result1">
select
COUNT(*)
from
ENROLLMENTS
where CRN
= ? and USERID = ?
<sql:param value="${param.crn}"
/>
<sql:param value="${param.username}"
/>
</sql:query>
<sql:query var="result2">
select
COUNT(*)
from
OFFERINGS
where CRN
= ?
<sql:param value="${param.crn}"
/>
</sql:query>
<sql:query var="result3">
select
ENROLLMAX - ENROLLCURRENT
from
OFFERINGS
where CRN
= ?
<sql:param value="${param.crn}"
/>
</sql:query>
<c:choose>
<c:when test="${result1.rowsByIndex[0][0] >
0}">
<P>Already registered for
this section!</P>
</c:when>
<c:when test="${result2.rowsByIndex[0][0] ==
0}">
<P>No such CRN!</P>
</c:when>
<c:when test="${result3.rowsByIndex[0][0] <=
0}">
<P>Class is full!</P>
</c:when>
<c:otherwise>
<jsp:useBean id="today" class="java.util.Date" />
<c:set var="now"
value="${today}" />
<fmt:formatDate value="${now}"
var = "day"
pattern="M/d/yyyy" />
<sql:transaction>
<sql:update>
INSERT INTO ENROLLMENTS(CRN, USERID, ENROLLDATE)
VALUES(?,?,?)
<sql:param value="${param.crn}"
/>
<sql:param value="${param.username}"
/>
<sql:param value="${day}" />
<%--- use sql:dateParam for Date ---%>
</sql:update>
<sql:update>
update
OFFERINGS
set
ENROLLCURRENT = ENROLLCURRENT + 1
WHERE CRN = ?
<sql:param value="${param.crn}"
/>
</sql:update>
</sql:transaction>
Registration Made!
</c:otherwise>
</c:choose>
</c:otherwise>
</c:choose>
</body>
</html>
ex11.htm
<HTML>
<HEAD>
<TITLE>Registration</TITLE>
</HEAD>
<BODY>
<FORM
ACTION="ex11.jsp"
METHOD="POST">
Enter the username:
<input type=text name="username">
<P>
Enter the password:
<input type=password name="password">
<P>
<input type = submit
value="Submit">
</form>
</body>
</HTML>
<%-- ex11.jsp --%>
<%-- Registration with
session variables --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex11</title>
</head>
<body>
<sql:setDataSource driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:registrar"
scope="session" />
<%-- dataSource
has session scope; possibilities are
application,
page, request, scope --%>
<sql:query var="result1">
select
COUNT(*) as MATCHES
from USERS
where USERID
like ? and PASSWORD like ?
<sql:param value="${param.username}"
/>
<sql:param value="${param.password}"
/>
</sql:query>
<sql:query var="result2">
select
COUNT(*)
from USERROLES
where ROLEID =
1 and USERID like ?
<sql:param value="${param.username}"
/>
</sql:query>
<c:choose>
<c:when test="${result1.rowsByIndex[0][0] >
1}">
<P>Too many matches!
</c:when>
<c:when test="${result1.rowsByIndex[0][0]==
0}">
<P>Not a legal user name/password
</c:when>
<c:when test="${result2.rowsByIndex[0][0]==
0}">
<P>Not a student
</c:when>
<c:otherwise>
<jsp:forward page="ex12.jsp"/>
</c:otherwise>
</c:choose>
</body>
</html>
<%-- ex12.jsp --%>
<%-- Registration with
session variables --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex12</title>
</head>
<body>
<sql:query var="result3">
select FNAME,
LNAME
from USERS
where USERID
like ?
<sql:param value="${param.username}"
/>
</sql:query>
<P>Pick a course, <c:out
value="${result3.rowsByIndex[0][0]}"/>
<c:out
value="${result3.rowsByIndex[0][1]}"/>
<FORM ACTION="ex13.jsp"
METHOD="POST">
<sql:query var="result4">
select
distinct OFFERINGS.COURSE, DESCRIPTION
from OFFERINGS
inner join COURSES on
OFFERINGS.COURSE =
COURSES.COURSE
</sql:query>
<select name='course'>
<c:forEach var="row"
items="${result4.rowsByIndex}" >
<option value='<c:out
value="${row[0]}"/>'>
<c:out value="${row[0]}"/>
<c:out value="${row[1]}"/>
</option>
</c:forEach>
</select>
<input type = submit
value="Submit">
</FORM>
<c:set
var="username" scope="session"
value="${param.username}" />
</body>
</html>
<%-- ex13.jsp --%>
<%-- No hidden fields --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core" prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex13</title>
</head>
<body>
<sql:query var="result1">
select CRN,
SECTION, DATES, TIMES, INSTRUCTOR, ROOM
from OFFERINGS
where COURSE
like ?
<sql:param value="${param.course}"
/>
</sql:query>
<FORM ACTION="ex14.jsp"
METHOD="POST">
<TABLE BORDER='1'>
<TR>
<TH>Choice</TH>
<TH>CRN</TH>
<TH>Section</TH>
<TH>Date</TH>
<TH>Time</TH>
<TH>Instructor</TH>
<TH>Room</TH>
</TR>
<c:forEach var="row"
items="${result1.rowsByIndex}" >
<TR>
<TD>
<input type='radio'
name='crn'
value='<c:out
value="${row[0]}"/>'>
</TD>
<c:forEach var="field"
items="${row}" >
<TD>
<c:out value="${field}"
/>
</TD>
</c:forEach>
</c:forEach>
</TABLE>
<input type = submit
value="Submit">
</FORM>
</body>
</html>
<%-- ex14.jsp --%>
<%-- Registration with
session variables --%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<%@ taglib
uri="http://java.sun.com/jstl/fmt"
prefix="fmt"%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex14</title>
</head>
<body>
<c:choose>
<c:when
test="${param.crn == null}">
<P>No section
chosen. Hit back button to return.
</c:when>
<c:otherwise>
<sql:query var="result1">
select
COUNT(*)
from
ENROLLMENTS
where CRN
= ? and USERID = ?
<sql:param value="${param.crn}"
/>
<sql:param value="${username}" />
</sql:query>
<sql:query var="result2">
select
COUNT(*)
from
OFFERINGS
where CRN
= ?
<sql:param value="${param.crn}"
/>
</sql:query>
<sql:query var="result3">
select
ENROLLMAX - ENROLLCURRENT
from
OFFERINGS
where CRN
= ?
<sql:param value="${param.crn}"
/>
</sql:query>
<c:choose>
<c:when test="${result1.rowsByIndex[0][0] >
0}">
<P>Already registered for
this section!</P>
</c:when>
<c:when test="${result2.rowsByIndex[0][0] ==
0}">
<P>No such CRN!</P>
</c:when>
<c:when test="${result3.rowsByIndex[0][0] <=
0}">
<P>Class is full!</P>
</c:when>
<c:otherwise>
<jsp:useBean id="today" class="java.util.Date" />
<c:set var="now"
value="${today}" />
<fmt:formatDate value="${now}"
var = "day"
pattern="M/d/yyyy" />
<sql:transaction>
<sql:update>
INSERT INTO ENROLLMENTS(CRN,
USERID, ENROLLDATE)
VALUES(?,?,?)
<sql:param value="${param.crn}"
/>
<sql:param value="${username}" />
<sql:param value="${day}" />
<%--- use sql:dateParam for Date ---%>
</sql:update>
<sql:update>
update
OFFERINGS
set
ENROLLCURRENT = ENROLLCURRENT + 1
WHERE CRN =
?
<sql:param value="${param.crn}"
/>
</sql:update>
</sql:transaction>
Registration Made!
</c:otherwise>
</c:choose>
</c:otherwise>
</c:choose>
</body>
</html>
ex15.jsp
<HTML>
<HEAD>
<TITLE>Registration</TITLE>
</HEAD>
<BODY>
<FORM
ACTION="ex15.jsp"
METHOD="POST">
Enter the username:
<input type=text name="username">
<P>
Enter the password:
<input type=password name="password">
<P>
<input type = submit
value="Submit">
</form>
</body>
</HTML>
<%-- ex15.jsp --%>
<%-- Registration example
--%>
<%@ include file="ex_initial.jsp" %>
<%-- Static content --%>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex15</title>
</head>
<body>
<sql:setDataSource driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:registrar"
scope="session" />
<%-- dataSource
has session scope; possibilities are
application,
page, request, scope --%>
<c:set
var="username" scope="session"
value="${param.username}" />
<sql:query var="result1">
select
COUNT(*) as MATCHES
from USERS
where USERID
like ? and PASSWORD like ?
<sql:param value="${param.username}"
/>
<sql:param value="${param.password}"
/>
</sql:query>
<sql:query var="result2">
select
COUNT(*)
from USERROLES
where ROLEID =
1 and USERID like ?
<sql:param value="${param.username}"
/>
</sql:query>
<c:choose>
<c:when test="${result1.rowsByIndex[0][0] >
1}">
<P>Too many matches!
</c:when>
<c:when test="${result1.rowsByIndex[0][0]==
0}">
<P>Not a legal user
name/password
</c:when>
<c:when test="${result2.rowsByIndex[0][0]==
0}">
<P>Not a student
</c:when>
<c:otherwise>
<jsp:forward page="ex16.jsp"/>
</c:otherwise>
</c:choose>
</body>
</html>
<%-- ex_initial.jsp
--%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib
uri="http://java.sun.com/jstl/core"
prefix="c"%>
<%@ taglib
uri="http://java.sun.com/jstl/sql"
prefix="sql"%>
<%@ taglib
uri="http://java.sun.com/jstl/fmt"
prefix="fmt"%>
<%-- ex_error.jsp
--%>
<%@ include file="ex_initial.jsp" %>
<P>You
made an error!
<P>
<c:out value="${param.error}"
/>
<P>Hit back button to
try again!
<%-- ex16.jsp --%>
<%-- Registration example
--%>
<%@ include file="ex_initial.jsp" %>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>ex16</title>
</head>
<body>
<sql:query var="result3">
select FNAME,
LNAME
from USERS
where USERID
like ?
<sql:param value="${param.username}"
/>
</sql:query>
<P>Pick sections, <c:out
value="${result3.rowsByIndex[0][0]}"/>
<c:out
value="${result3.rowsByIndex[0][1]}"/>
<sql:query var="result4">
select CRN, COURSE,
SECTION, DATES, TIMES, INSTRUCTOR, ROOM
from OFFERINGS
</sql:query>
<FORM ACTION="ex17.jsp"
METHOD="POST">
<TABLE BORDER='1'>
<TR>
<TH>Choice</TH>
<TH>CRN</TH>
<TH>Course</TH>
<TH>Section</TH>
<TH>Date</TH>
<TH>Time</TH>
<TH>Instructor</TH>
<TH>Room</TH>
</TR>
<c:forEach
var="row"
items="${result4.rowsByIndex}" >
<TR>
<TD>
<input type='checkbox'
name='crn<c:out
value="${row[0]}"/>'
value='<c:out value="${row[0]}"/>'>
</TD>
<c:forEach var="field"
items="${row}" >
<TD>
<c:choose>
<c:when test="${field == null}">
</c:when>
<c:otherwise>
<c:out value="${field}"
/>
</c:otherwise>
</c:choose>
</TD>
</c:forEach>
</TR>
</c:forEach>
</TABLE>
<input type = submit
value="Submit">
</FORM>
</body>
</html>
<%-- ex17.jsp --%>
<%-- Registration example
--%>
<%@ include file="ex_initial.jsp" %>
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=windows-1252"/>
<title>ex17</title>
</head>
<body>
<jsp:useBean id="today" class="java.util.Date" />
<c:set
var="now" value="${today}" />
<fmt:formatDate value="${now}"
var
= "day"
pattern="M/d/yyyy" />
<c:forEach
var="myparam"
items="${paramValues}">
<sql:query var="result1">
select
COUNT(*) from ENROLLMENTS
where
CRN = ? and USERID = ?
<sql:param value="${param[myparam.key]}"/>
<sql:param value="${username}" />
</sql:query>
<sql:query var="result2">
select
COUNT(*)
from
OFFERINGS
where
CRN = ?
<sql:param value="${param[myparam.key]}" />
</sql:query>
<sql:query var="result3">
select
ENROLLMAX - ENROLLCURRENT
from
OFFERINGS
where
CRN = ?
<sql:param value="${param[myparam.key]}" />
</sql:query>
<c:choose>
<c:when test="${result1.rowsByIndex[0][0] >
0}">
<P> CRN <c:out value="${param[myparam.key]}" />
<jsp:include page="ex_error.jsp">
<jsp:param name="error"
value="Already registered for section" />
</jsp:include>
</c:when>
<c:when test="${result2.rowsByIndex[0][0] ==
0}">
<P> CRN <c:out value="${param[myparam.key]}" />
<jsp:include page="ex_error.jsp">
<jsp:param name="error" value="No such
CRN" />
</jsp:include>
</c:when>
<c:when test="${result3.rowsByIndex[0][0] <=
0}">
<P> CRN <c:out value="${param[myparam.key]}" />
<jsp:include page="ex_error.jsp">
<jsp:param name="error" value="Full
CRN" />
</jsp:include>
</c:when>
<c:otherwise>
<sql:transaction>
<sql:update>
INSERT INTO ENROLLMENTS(CRN, USERID, ENROLLDATE)
VALUES(?,?,?)
<sql:param value="${param[myparam.key]}" />
<sql:param value="${username}" />
<sql:param value="${day}" />
<%--- use sql:dateParam for Date ---%>
</sql:update>
<sql:update>
update
OFFERINGS
set
ENROLLCURRENT = ENROLLCURRENT + 1
WHERE CRN = ?
<sql:param value="${param[myparam.key]}" />
</sql:update>
</sql:transaction>
<P>Registration Made for
<c:out value="${param[myparam.key]}" />
</c:otherwise>
</c:choose>
</c:forEach>
</body>
</html>