9.6 Updating a Database
| Besides supporting database queries with <sql:query>, JSTL also supports database updates with an <sql:update> action. The <sql:update> action supports both Data Manipulation Language (DML) commands and Data Definition Language (DDL) commands. The more common DML commands are:
The INSERT command lets you insert a row in a table, UPDATE lets you update information in a row, and DELETE lets you delete rows. The more common DDL commands are:
Both DML and DDL commands are executed with an SQL statement for the <sql:update> action. That action has the following syntax: [14]
<sql:update sql [var] [scope] [dataSource]/> The sql attribute specifies the SQL statement. Like <sql:query>, that statement can optionally be specified in the body of <sql:update>, like this: <sql:update [var] [scope] [dataSource]> SQL Statement </sql:update> Also, like <sql:query>, <sql:update> lets you specify a data source with the dataSource attribute. The rest of this section shows you how to use <sql:update> to perform DML commands. DDL commands are executed in exactly the same manner, except for the SQL statement, so those are left as an exercise for the reader. Database InsertsInserting rows in a database is easy with the <sql:update> action, as illustrated by the simple Web application shown in Figure 9-7 and Figure 9-8. That application consists of two JSP pages, one that collects information and another that performs the database insert. Figure 9-7. A JSP Page That Collects Customer Information Figure 9-8. Inserting a Row in a Database The JSP page shown in Figure 9-7 collects information about a customer that is subsequently inserted into the database; that insertion is performed by the JSP page shown in Figure 9-8. The JSP page shown in Figure 9-7 is listed in Listing 9.14. The preceding JSP page is unremarkable ”it merely provides a form that lets users enter information for a new customer. The action for that form is insert_customer.jsp , which is shown in Figure 9-8 and listed in Listing 9.15. The preceding JSP page performs two database queries that select all of the customers in the database. The first query is executed solely to ascertain how many customers are stored in the database so that the proper customer ID can be specified for the new customer. After the initial query, the preceding JSP page uses <sql:update> to perform the insert. Because all of the customer data is dynamically generated in a form, <sql:param> actions are used to specify customer parameters. The <sql:update> action lets you specify an optional var attribute that stores the number of rows affected by the operation in a scoped variable. The preceding JSP page uses that attribute to show how many customers were inserted in the database. Subsequently, the preceding JSP page performs a second query and displays the results of that query in an HTML table. Database UpdatesUpdating information in a database is also performed with the <sql:update> action. Figure 9-9 shows a Web application that lets you update information for a single customer in the database. That Web application consists of three JSP pages. The first JSP page, shown in the top picture in Figure 9-9, lets you select a customer to update. The second JSP page, shown in the two middle pictures, lets you modify the selected user 's information. The third JSP page, shown in the bottom picture, shows the result of the update. Figure 9-9. Updating a Row in a Database. The top picture shows a JSP page that lets users select a customer to update. The two middle pictures show the original data (middle-top) and the modified data (middle-bottom). The bottom picture shows the result of the update. The JSP page shown in the top picture in Figure 9-9 is listed in Listing 9.16. The preceding JSP page uses a database query to populate an HTML select element with the names of all the customers in the database. When the form's submit button is activated, collect_update_info.jsp is loaded. That JSP page is listed in Listing 9.17. The preceding JSP page performs a query against the selected customer to populate an HTML form. Because customer names are unique in our database, that query always contains a single row of data. To make that single row of data more accessible, the JSP page stores that data in a page scope variable named row as a convenient shorthand for ${customers.rows[0]} . Listing 9.14 index.jsp (Inserting a Row)<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Updating a Database: INSERT</title> </head> <body> <font size='5'>Inserting a Row in a Database</font> <form action='insert_customer.jsp'> Add a Customer to the Database:<p> <table> <tr><td>Name:</td> <td><input type='text' name='name'/></td></tr> <tr><td>Phone Number:</td> <td><input type='text' name='phone'/></td></tr> <tr><td>Street Address:</td> <td><input type='text' name='address'/></td></tr> <tr><td>City:</td> <td><input type='text' name='city'/></td></tr> <tr><td>State:</td> <td><input type='text' name='state'/></td></tr> </table> <p><input type='submit' value='Add Customer to Database'/> </form> </body> </html> Listing 9.15 insert_customer.jsp<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c'%> <%@ taglib uri='http://java.sun.com/jstl/sql' prefix='sql'%> <title>Updating a Database: INSERT</title> </head> <body> <%-- This query is made to find out how many customers are currently in the database --%> <sql:query var='customers'> SELECT * FROM CUSTOMERS </sql:query> <%-- Perform the database update --%> <sql:update var=' updateResult '> INSERT INTO CUSTOMERS VALUES (?, ?, ?, ?, ?, ?) <sql:param value='${customers.rowCount+1}'/> <sql:param value='${param.name}'/> <sql:param value='${param.phone}'/> <sql:param value='${param.address}'/> <sql:param value='${param.city}'/> <sql:param value='${param.state}'/> </sql:update> <%-- Show the result of the update --%> <font size='5'> <c:out value=' ${updateResult} Customer was Inserted:'/> <c:out value='${param.name}'/> </font> <%-- After adding a customer, perform another customer query --%> <sql:query var='customers'> SELECT * FROM CUSTOMERS </sql:query> <%-- Access the rowCount property of the query --%> <p><c:out value='There are ${customers.rowCount} rows'/> in the customer query. Here they are:</p> <%-- Create a table with column names and row data --%> <p><table border='1'> <tr> <c:forEach var='columnName' items='${customers.columnNames}'> <th><c:out value='${columnName}'/></th> </c:forEach> </tr> <c:forEach var='row' items='${customers.rowsByIndex}'> <tr> <c:forEach var='rowData' items='${row}'> <td><c:out value='${rowData}'/></td> </c:forEach> </tr> </c:forEach> </table> </body> </html> Listing 9.16 index.jsp (Updating a Row) <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Updating a Database: UPDATE</title> </head> <body> <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c' %> <%@ taglib uri='http://java.sun.com/jstl/sql' prefix='sql'%> <%-- This query is used to populate the customers option--%> <sql:query var='customers'> SELECT * FROM CUSTOMERS </sql:query> <form action='collect_update_info.jsp'> Select a Customer to Update: <select name='customer'> <c:forEach var='row' items='${customers.rows}'> <option value="<c:out value='${row.name}'/>"> <c:out value='${row.name}'/> </option> </c:forEach> </select> <p><input type='submit' value='Update Selected Customer'/> </form> </body> </html> Listing 9.17 collect_update_info.jsp<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> </html> </head> <title>Updating a Database: UPDATE</title> </head> <body> <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c' %> <%@ taglib uri='http://java.sun.com/jstl/sql' prefix='sql'%> <%-- Perform a query against the selected customer --%> <sql:query var='customers'> SELECT * FROM CUSTOMERS WHERE NAME = ? <sql:param value='${param.customer}'/> </sql:query> <%-- Set a variable for the row as a shorthand to be used below --%> <c:set var='row' value='${customers.rows[0]}'/> <%-- Populate a form with the query performed above --%> <form action='update_customer.jsp'> <table> <tr><td>Name:</td> <td><c:out value=' ${row.NAME} '/></td></tr> <tr><td>Phone:</td> <td><input type='text' value='<c:out value=" ${row.PHONE_NUMBER} "/>' name='phone'/></td></tr> <tr><td>Address:</td> <td><input type='text' value='<c:out value=" ${row.STREET_ADDRESS} "/>' name='address' size='35'/></td></tr> <tr><td>City:</td> <td><input type='text' value='<c:out value=" ${row.CITY} "/>' name='city'/></td></tr> <tr><td>State:</td> <td><input type='text' value='<c:out value=" ${row.STATE} "/>' name='state'/></td></tr> </table> <p> <input type='submit' value="Update <c:out value=' ${row.NAME} '/>"> <input type='hidden' name='name' value='<c:out value="${row.NAME}"/>'/> </form> </body> </html> The form in the preceding JSP page displays the user's name, but does not allow the name to be updated. Because of that restriction, the user's name will not be encoded as a request parameter when a user activates the form's submit button, so the form stores that name in a hidden field. When a user activates the submit button, the JSP page update_customer.jsp is loaded in the browser. That JSP page is listed in Listing 9.18. Listing 9.18 update_customer.jsp<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Updating a Database: UPDATE</title> </head> <body> <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c' %> <%@ taglib uri='http://java.sun.com/jstl/sql' prefix='sql'%> <%-- Perform the database update --%> <sql:update var='updateResult'> UPDATE CUSTOMERS SET PHONE_NUMBER = ?, STREET_ADDRESS = ?, CITY = ?, STATE = ? WHERE NAME = ? <sql:param value='${param.phone}'/> <sql:param value='${param.address}'/> <sql:param value='${param.city}'/> <sql:param value='${param.state}'/> <sql:param value='${param.name}'/> </sql:update> <%-- Perform a query against the updated customer --%> <sql:query var='customers'> SELECT * FROM CUSTOMERS WHERE NAME = ? <sql:param value='${param.name}'/> </sql:query> <%-- Show how many rows were updated --%> <p><font size='4'> <c:out value=' ${updateResult} row updated '/> <c:out value='for ${param.name}:'/> </font> <%-- Display updated data with the query performed above--%> <c:forEach var='row' items='${customers.rows}'> <p><table border='1'> <tr><td>Phone:</td> <td><c:out value='${row.PHONE_NUMBER}'/></td> </tr> <tr><td>Address:</td> <td><c:out value='${row.STREET_ADDRESS}'/></td> </tr> <tr><td>City:</td> <td><c:out value='${row.CITY}'/></td> </tr> <tr><td>State:</td> <td><c:out value='${row.STATE}'/></td> </tr> </table> </c:forEach> </body> </html> The preceding JSP page updates the selected user's information in the database with <sql:update> and <sql:param>, and executes a subsequent query against the user. That query is then used to populate an HTML table. Database DeletesDeleting rows from a database is also performed with the <sql:update> action. Figure 9-10 shows a Web application, consisting of two JSP pages, that deletes a selected customer from the database. Figure 9-10. Deleting a Row from a Database. Row 18 was deleted from the Customers table, which originally had 20 customers. The top picture in Figure 9-10 shows a JSP page that lets you select a customer to delete from the database. That JSP page is listed in Listing 9.19. When the submit button in the preceding JSP page's form is activated, delete_customer.jsp is loaded in the browser. That JSP page is listed in Listing 9.20. The preceding JSP page uses <sql:update> and <sql:param> to delete the selected customer from the database. Subsequently, a query against all customers in the database is executed, and the result of that query is displayed in an HTML table. Listing 9.19 index.jsp (Deleting a Row) <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Updating a Database: DELETE</title> </head> <body> <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c' %> <%@ taglib uri='http://java.sun.com/jstl/sql' prefix='sql'%> <%-- This query is used to populate the customers option--%> <sql:query var='customers'> SELECT * FROM CUSTOMERS </sql:query> <form action='delete_customer.jsp'> Select a Customer to Delete: <select name='customer'> <c:forEach var='row' items='${customers.rows}'> <option value="<c:out value='${row.NAME}'/>"> <c:out value='${row.NAME}'/> </option> </c:forEach> </select> <p><input type='submit' value='Delete Selected Customer'> </form> </body> </html> Listing 9.20 delete_customer.jsp<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Updating a Database: DELETE</title> </head> <body> <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c' %> <%@ taglib uri='http://java.sun.com/jstl/sql' prefix='sql'%> <%-- Perform the database delete --%> <sql:update var='updateResult'> DELETE FROM CUSTOMERS WHERE NAME = ? <sql:param value='${param.customer}'/> </sql:update> <%-- Show how many rows were deleted --%> <font size='5'> <c:out value='${updateResult} Customer was Deleted:'/> <c:out value='${param.customer}'/> </font> <%-- Make a query of all customers in the database --%> <sql:query var='customers'> SELECT * FROM CUSTOMERS </sql:query> <%-- Access the rowsCount property of the query --%> <p><c:out value='There are ${customers.rowCount} rows'/> in the customer query. Here they are: <%-- Create a table with column and row data --%> <p><table border='1'> <tr> <c:forEach var='columnName' items='${customers.columnNames}'> <th><c:out value='${columnName}'/></th> </c:forEach> </tr> <c:forEach var='row' items='${customers.rowsByIndex}'> <tr> <c:forEach var='rowData' items='${row}'> <td><c:out value='${rowData}'/></td> </c:forEach> </tr> </c:forEach> </table> </body> </html>  |
![Core JSTL[c] Mastering the JSP Standard Tag Library](/icons/blank_book.jpg "Core JSTL[c] Mastering the JSP Standard Tag Library"){kind=icon}
EAN: N/A
Pages: 124