⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 jdbc.jsp

📁 jakarta-taglibs
💻 JSP
字号:
🔍 
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
   <title>Jakarta DBTAGS Taglib Example</title>
</head>
<body bgcolor="white">

<%@ taglib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql" %>

<P><B>Opening connection</B></P>

<sql:connection id="conn1">
  <sql:url><%=request.getParameter("dburl")%></sql:url>
  <sql:driver><%=request.getParameter("driver")%></sql:driver>
</sql:connection>

<P><B>Connection is closed?</B></P>

<jsp:getProperty name="conn1" property="closed"/>

<P><B>Deleteing the results of the last test</B></P>

<sql:statement id="stmt1" conn="conn1">
  <sql:query>delete from test_books where id > 2</sql:query>
  <sql:execute/>
</sql:statement>

<P><B>Showing current contents of the "test_books" table</B></P>

<!-- should be empty -->
<table>
<tr><th>id</th><th>name</th><th>description</th><th>rowCount</th></tr>
<sql:preparedStatement id="stmt2" conn="conn1">
  <sql:query>
    select id, name, description from test_books
    order by 1
  </sql:query>
  <sql:resultSet id="rset1">
    <tr>
      <td><sql:getColumn position="1"/></td>
      <td><sql:getColumn position="2"/></td>
      <td><sql:getColumn position="3"/><sql:wasNull>[no description]</sql:wasNull></td>
      <td><sql:rowCount/></td>
    </tr>
  </sql:resultSet>
  <tr>
    <td colspan="4">
    <sql:wasEmpty>No rows retrieved.</sql:wasEmpty>
    <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty>
    </td>
  </tr>
</sql:preparedStatement>
</table>

<P><B>Inseting a row using the "statement" tag</B></P>

<%-- added try block because InstantDB has trouble with SQL escaping --%>
<% try { %>
<sql:statement id="stmt3" conn="conn1">
  <sql:query>
    insert into test_books (id, name)
      values (3, '<sql:escapeSql>Gravity's Rainbow</sql:escapeSql>')
  </sql:query>
  <sql:execute/>
</sql:statement>
<% } catch (javax.servlet.jsp.JspTagException e) { %>
  <font color="red"<p>Possible SQL error:</p>
     <dl><dd><%= e.toString() %></dd></dl>
  <p>Your database (InstantDB?) may not support
  standard SQL escaping.  You will probably have to escape your
  SQL manually, rather than use the &lt;sql:escapeSql> tags.</p></font>
<% } %>

<P><B>Showing current contents of the "test_books" table</B></P>

<table>
<tr><th>id</th><th>name</th><th>description</th></tr>
<sql:preparedStatement id="stmt4" conn="conn1">
  <sql:query>
    select id, name, description from test_books
    order by 1
  </sql:query>
  <sql:resultSet id="rset2">
    <tr>
      <td><sql:getColumn position="1"/></td>
      <td><sql:getColumn position="2"/></td>
      <td><sql:getColumn position="3"/><sql:wasNull>[no description]</sql:wasNull></td>
    </tr>
  </sql:resultSet>
  <tr>
    <td colspan="3">
    <sql:wasEmpty>No rows retrieved.</sql:wasEmpty>
    <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty>
    </td>
  </tr>
</sql:preparedStatement>
</table>

<P><B>Select all book titles like "%ra%"</B></P>

<table>
<tr><th>id</th><th>name</th><th>description</th></tr>
<sql:statement id="likeStmt" conn="conn1">
  <sql:query>
    select id, name, description from test_books
    where name like '%ra%'
  </sql:query>
  <sql:resultSet id="likeRset">
    <tr>
      <td><sql:getColumn position="1"/></td>
      <td><sql:getColumn position="2"/></td>
      <td><sql:getColumn position="3"/><sql:wasNull>[no description]</sql:wasNull></td>
    </tr>
  </sql:resultSet>
  <tr>
    <td colspan="3">
    <sql:wasEmpty>No rows retrieved.</sql:wasEmpty>
    <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty>
    </td>
  </tr>
</sql:statement>
</table>


<P><B>Selecting back the title of book 3, assigning the value to an attribute</B></P>

<sql:preparedStatement id="stmt5" conn="conn1">
  <sql:query>
    select name from test_books
      where id = 3
      order by 1
  </sql:query>
  <sql:resultSet id="rset3">
    <sql:getColumn position="1" to="bookName"/>
    <!-- show that rowCount works for a single row -->
    retrieved row <sql:rowCount/>
  </sql:resultSet>
  <p>
    <sql:wasEmpty>No rows retrieved.</sql:wasEmpty>
    <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty>
  </p>
</sql:preparedStatement>

<P><B>Inserting that title into a new row with a "preparedStatement" tag, adding
a description</B></P>

<sql:preparedStatement id="stmt6" conn="conn1">
  <sql:query>
    insert into test_books (id, name, description)
      values (?, ?, ?)
  </sql:query>
  <sql:execute>
    The contents of an execute body are not included in the output
    <sql:setColumn position="1">4</sql:setColumn>
    <sql:setColumn position="2"><%=pageContext.getAttribute("bookName")%></sql:setColumn>
    <sql:setColumn position="3">book by Pynchon</sql:setColumn>
  </sql:execute>
</sql:preparedStatement>

<P><B>Nested Query example: Reselecting every row in the table</B></P>

<table>
<tr><th>id</th><th>name</th><th>description</th></tr>
<sql:preparedStatement id="stmt6" conn="conn1">
  <sql:query>
    select id, name, description from test_books
    order by 1
  </sql:query>
  <sql:resultSet id="rset4">
    <tr>
      <td><sql:getColumn position="1"/></td>
      <td><sql:getColumn position="2"/></td>
      <td><sql:getColumn position="3" to="description"/>
      <sql:wasNotNull>Description: <%= pageContext.getAttribute("description") %></sql:wasNotNull>
      <sql:wasNull>[no description]</sql:wasNull></td>
    </tr>

    <!-- okay, now perform the nested query -->
    <% int colId = rset4.getInt(1); %>
    <sql:statement id="stmt9" conn="conn1">
      <sql:query>
        select id, name, description from test_books
        where id = <%= colId %>
      </sql:query>
      <sql:resultSet id="rset7">
        <tr>
          <td><sql:getColumn position="1"/></td>
          <td><sql:getColumn position="2"/></td>
          <td><sql:getColumn position="3" to="description"/>
          <sql:wasNotNull>Description: <%= pageContext.getAttribute("description") %></sql:wasNotNull>
          <sql:wasNull>[no description]</sql:wasNull></td>
        </tr>        
      </sql:resultSet>
    </sql:statement>

  </sql:resultSet>
  <tr>
    <td colspan="3">
    <sql:wasEmpty>No rows retrieved.</sql:wasEmpty>
    <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty>
    </td>
  </tr>
</sql:preparedStatement>
</table>

<p><b>For fun, pretend that the ids are British money in the left column,
      and French decimals in the right</b></p>

<table>
<sql:preparedStatement id="stmt7" conn="conn1"> 
  <sql:query>
    select id from test_books
    order by 1
  </sql:query>
  <sql:resultSet id="rset5">
    <tr>
      <td><sql:getNumber colName="id" format="CURRENCY" locale="en_GB"/></td>
      <td><sql:getNumber position="1" format="0.00" locale="fr_FR"/></td>
    </tr>
  </sql:resultSet>
  <tr>
    <td>
    <sql:wasEmpty>No rows retrieved.</sql:wasEmpty>
    <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty>
    </td>
  </tr>
</sql:preparedStatement>
</table>

<P><B>Prove that an empty resultset executes the wasEmpty tag</B></P>

<sql:preparedStatement id="stmt8" conn="conn1">
  <!-- impossible statement -->
  <sql:query>
    select id, name, description from test_books
    where id > 1000
  </sql:query>
  <sql:resultSet id="rset6">
    <p>

      <!-- show that rowCount works for a no rows -->
      retrieved row <sql:rowCount/>

      <sql:getColumn position="1"/>
      <sql:getColumn position="2"/>
      <sql:getColumn position="3" to="description"/>
      <sql:wasNotNull>Description: <%= pageContext.getAttribute("description") %></sql:wasNotNull>
      <sql:wasNull>[no description]</sql:wasNull>
    </p>
  </sql:resultSet>
  <p>
    <sql:wasEmpty>No rows retrieved.</sql:wasEmpty>
    <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty>
  </p>
</sql:preparedStatement>

<P><B>Make sure the rowCount tag works with zero rows, outside a preparedStatement.</B></P>

<sql:rowCount/> rows retrieved.

<P><B>Closing the database connection</B></P>

<sql:closeConnection conn="conn1"/>

<P><B>Connection is closed?</B></P>

<jsp:getProperty name="conn1" property="closed"/>

<p><b>Success!</b></p>
</body>
</html>

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -