Java for Web学习笔记（三一）：JSTL（7）SQL Tag

来源：互联网发布：诅咒淘宝店网址编辑：程序博客网时间：2024/06/06 18:43

一般而言，尽可能不要在数据呈现（jsp）的位置来进行数据操作，但是在一些原型设计上，确也比较便捷，因此仍有必要了解。

准备

<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>

如果我们使用mysql，一样要在pom.xml中加入：

<dependency>    <groupId>mysql</groupId>    <artifactId>mysql-connector-java</artifactId>    <version>5.1.40</version></dependency>

因为是在非正式代码中使用，因此tomcat报的一些告警可以忽略：

信息: Server startup in 1543 ms Wed Nov 02 09:36:38 CST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.... ...警告: The web application [chapter07] registered the JDBC driver [com.mysql.jdbc.Driver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.十一月 02, 2016 9:38:30 上午 org.apache.catalina.loader.WebappClassLoaderBase clearReferencesThreads警告: The web application [chapter07] appears to have started a thread named [Abandoned connection cleanup thread] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:  java.lang.Object.wait(Native Method)  java.lang.ref.ReferenceQueue.remove(Unknown Source)com.mysql.jdbc.AbandonedConnectionCleanupThread.run(AbandonedConnectionCleanupThread.java:43)

定义DataSource

<sql:setDataSource var="testdb" driver="com.mysql.jdbc.Driver"     url="jdbc:mysql://191.8.1.101/test"     user="test"  password="test123"/>

全局的也可以在web.xml的javax.servlet.jsp.jstl.sql.dataSource中定义。

读操作：SELECT

例子1：无条件查询

<sql:query dataSource="${testdb}" var="result">SELECT * from Employees;</sql:query><table border="1" width="100%"><tr>  <th>Emp ID</th>   <th>First Name</th>   <th>Last Name</th>   <th>Age</th></tr><c:forEach var="row" items="${result.rows}"><tr><td><c:out value="${row.id}"/></td><td><c:out value="${row.first}"/></td><td><c:out value="${row.last}"/></td><td><c:out value="${row.age}"/></td></tr></c:forEach></table>

例子2：有条件查询

<sql:query dataSource="${testdb}" var="zara" sql="SELECT * from Employees WHERE first = ?"><sql:param value="Zara"/></sql:query><c:if test="${fn:length(zara.rows) >0 }">${zara.rows[0].first} is ${zara.rows[0].age} years old.</c:if>

写操作：UPDATE、DELETE、INSERT

例子1：增

<sql:update dataSource="${testdb}" var="result">INSERT INTO Employees VALUES (104, 2, 'Nuha', 'Ali');</sql:update>

例子2：删

<c:set var="empId" value="104"/><sql:update dataSource="${testdb}" var="count">DELETE FROM Employees WHERE id = ?<sql:param value="${empId}"/></sql:update><p>Delete ${count} entry/entries</p>

例子3：改

<c:set var="empId" value="102"/> <sql:update dataSource="${snapshot}" var="count">  UPDATE Employees SET last = 'Ali'  <sql:param value="${empId}" /></sql:update>

Transaction

事务是单个的工作单位。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，若果事务遇到错误，则必须取消或回滚，所有数据均被更改清除。[1]

BEGIN TRANSACTIONCOMMIT TRANSACTIONROLLBACK TRANSACTION

<sql:transaction dataSource="${someDataSource}" isolation="read_committed"><sql:update sql="...SQL语句...">    <sql:param value="..."/>    <!-- 给出一个datetime格式的例子-->    <fmt:parseDate var="transactionDate" value="${effectiveDate}" />    <sql:dateParam value="${transactionDate}" />    <sql:param value="..." />  </sql:update><sql:update>  ...SQL语句...  <sql:param value="..." />  <!-- 给出一个datetime格式的例子-->  <sql:dateParam value="${someLaterDate}" />  <sql:param value="..." /></sql:update></sql:transaction>

除了书外，还参考：https://www.tutorialspoint.com/jsp/jsp_database_access.htm

相关链接：我的Professional Java for Web Applications相关文章

0 0