Java/JSTL/Database
Содержание
JSTL SQL Query
<%@ taglib uri="http://java.sun.ru/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.ru/jstl/sql" prefix="sql" %>
<sql:setDataSource var="dataSource" driver="org.gjt.mm.mysql.Driver"
url="jdbc:mysql://localhost/forum?user=forumuser"
scope="session" />
<html>
<head>
<title>Query Example</title>
</head>
<body>
<sql:query var = "users" dataSource="${dataSource}">
select column_uid,column_pwd,column_accesses,column_first,column_last,column_bad,column_posted,column_type from t_users
</sql:query>
<table border=1>
<c:forEach var="row" items="${users.rows}">
<tr>
<td><c:out value="${row.column_uid}"/></td>
<td><c:out value="${row.column_pwd}"/></td>
<td><c:out value="${row.column_accesses}"/></td>
<td><c:out value="${row.column_first}"/></td>
<td><c:out value="${row.column_last}"/></td>
<td><c:out value="${row.column_bad}"/></td>
<td><c:out value="${row.column_posted}"/></td>
<td><c:out value="${row.column_type}"/></td>
</tr>
</c:forEach>
</table>
</body>
</html>
JSTL SQL Update
<%@ taglib uri="http://java.sun.ru/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.ru/jstl/core-rt" prefix="c-rt" %>
<%@ taglib uri="http://java.sun.ru/jstl/sql" prefix="sql" %>
<sql:setDataSource var="dataSource" driver="org.gjt.mm.mysql.Driver"
url="jdbc:mysql://localhost/forum?user=forumuser"
scope="session" />
<html>
<head>
<title>General Query</title>
</head>
<body>
<c:choose>
<c:when test="${param.cmd!=null}">
<c:set var="str" value="${param.cmd}" />
</c:when>
<c:otherwise>
<c:set var="str"
value="select * from tableName" />
</c:otherwise>
</c:choose>
Please enter a query:
<br />
<form method="post">
<textarea name="cmd" cols="40" rows="5">
<c:out value="${str}" />
</textarea>
<br />
<input type="submit" />
</form>
<c:if test="${pageContext.request.method=="POST"}">
<c:catch var="e">
<sql:query var="users" dataSource="${dataSource}"
sql="${param.cmd}" />
<table border="1">
<c:forEach var="row" items="${users.rows}"
varStatus="status">
<jsp:useBean id="status"
type="javax.servlet.jsp.jstl.core.LoopTagStatus" />
<c-rt:if test="<%=status.getCount()==1%>">
<tr>
<c:forEach var="col" items="${row}">
<th>
<c:out value="${col.key}" />
</th>
</c:forEach>
</tr>
</c-rt:if>
<tr>
<c:forEach var="col" items="${row}">
<td>
<c:out value="${col.value}" />
</td>
</c:forEach>
</tr>
</c:forEach>
</table>
</c:catch>
<c:if test="${e!=null}">
<h3>Error</h3>
<c:out value="${e}" />
</c:if>
</c:if>
</body>
</html>
JSTL: Transaction with a JSP
<%@ taglib uri="http://java.sun.ru/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.ru/jstl/sql" prefix="sql" %>
<html>
<HEAD>
<TITLE>Using a Transaction with a JSP</TITLE>
</HEAD>
<body bgcolor="white">
<h2>View table Data</h2>
<sql:transaction>
<sql:update>
insert into atable values(2, "Joe","Id","Feb-24-1996","F")
</sql:update>
<sql:query var="resultObj">
select * from atable
</sql:query>
</sql:transaction>
<table>
<c:forEach items="${resultObj.rows}" var="row">
<c:forEach items="${row}" var="column">
<tr>
<td align="right">
<b><c:out value="${column.key}" /></b>
</td>
<td>
<c:out value="${column.value}" />
</td></tr>
</c:forEach>
</c:forEach>
</table>
</body>
</html>
Presenting database content using tags
//web.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.ru/dtd/web-app_2_3.dtd">
<web-app>
<resource-ref>
<res-ref-name>jdbc/address</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
<%@ taglib prefix="sql" uri="http://java.sun.ru/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.ru/jstl/core" %>
<html>
<head>
<title>Presenting database content using tags</title>
<sql:setDataSource
dataSource="jdbc/address"
var="conn"
/>
</head>
<body>
<h1>Address List</h1>
<sql:query dataSource="${conn}" var="addresses">
SELECT * FROM AddressList
</sql:query>
<table width="90%" border="1">
<tr>
<!-- add the table column headings -->
<c:forEach var="columnName" items="${addresses.columnNames}">
<th> <c:out value="${columnName}"/> </th>
</c:forEach>
</tr>
<!-- add the table rows from the result set -->
<c:forEach var="row" items="${addresses.rowsByIndex}">
<tr>
<c:forEach var="column" items="${row}">
<td><c:out value="${column}"/></td>
</c:forEach>
</tr>
</c:forEach>
</table>
</body>
</html>
SQL Tag Out Examples
<%@ taglib uri="http://java.sun.ru/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.ru/jstl/sql" prefix="sql" %>
<sql:setDataSource var="dataSource" driver="sun.jdbc.odbc.JdbcOdbcDriver" url="jdbc:odbc:forum"/>
<html>
<head>
<title>SQL Tag Out Examples</title>
</head>
<body>
<sql:query var = "users" dataSource="${dataSource}">
select column_uid,column_pwd,column_accesses,column_first,column_last,column_bad,column_posted,column_type from t_users
</sql:query>
<table border=1>
<c:forEach var="row" items="${users.rows}">
<tr>
<td><c:out value="${row.column_uid}"/></td>
<td><c:out value="${row.column_pwd}"/></td>
<td><c:out value="${row.column_accesses}"/></td>
<td><c:out value="${row.column_first}"/></td>
<td><c:out value="${row.column_last}"/></td>
<td><c:out value="${row.column_bad}"/></td>
<td><c:out value="${row.column_posted}"/></td>
<td><c:out value="${row.column_type}"/></td>
</tr>
</c:forEach>
</table>
</body>
</html>
Updating a database using the sql:update tag
//web.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.ru/dtd/web-app_2_3.dtd">
<web-app>
<resource-ref>
<res-ref-name>jdbc/address</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
<%@ taglib prefix="sql" uri="http://java.sun.ru/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.ru/jstl/core" %>
<html>
<head>
<title>Updating a database using the sql:update tag</title>
<sql:setDataSource
var="conn"
dataSource="jdbc/address"
/>
</head>
<body>
<h1>Modify Address List</h1>
<sql:update dataSource="${conn}" var="addresses">
INSERT INTO AddressList (name, street, city, country, telephone) VALUES (?, ?, ?, ?, ?)
<sql:param value="${param["name"]}"/>
<sql:param value="${param["street"]}"/>
<sql:param value="${param["city"]}"/>
<sql:param value="${param["country"]}"/>
<sql:param value="${param["tel"]}"/>
</sql:update>
</body>
</html>
