JSP - 更改SQL数据库中的值
问题描述：
我有一个表显示来自数据库的项目。在第一个ResultSet中，我创建了一个下拉菜单，让您选择是否希望项目可用。但是，因为我已经在第一个ResultSet rs中创建了它，所以我可以在第二个ResultSet rs1中使用它。问题是，在这条线：JSP - 更改SQL数据库中的值
if (request.getParameter(rs1.getString("naziv") + "polje").equals("Nedostupno"))
这里是整个代码：
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<html>
<head>
<link rel="stylesheet" type="text/css" href="Stil/cssstil.css">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Prikaz oružja</title>
</head>
<body>
<h1>Prikaz oruzja</h1>
<%
out.print("<p align = \"center\">Administrator <a style=\"color:red;\">" + session.getAttribute("user") + "</a></p>");
%>
<table align = "center" bgcolor = "darkgrey" border = "1" cellpadding="3" cellspacing="1">
<tr>
<td>
<p style = "color: black;">Naziv</p>
</td>
<td>
<p style = "color: black;">Opis</p>
</td>
<td>
<p style = "color: black;">Cena</p>
</td>
<td>
<p style = "color: black;">Dostupnost</p>
</td>
<td>
</td>
</tr>
<%
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/CS230-Projekat", "root", "123");
Statement statement = connection.createStatement();
String sql = "SELECT * FROM oruzje";
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
%>
<tr bgcolor="grey">
<td><%=rs.getString("naziv")%></td>
<td><%=rs.getString("opis")%></td>
<td><%=rs.getString("cena")%></td>
<%
if (rs.getString("dostupnost").equals("1")) {
out.print("<td><p style = \"color: green; font-size: 20px\">Dostupno</p></td>");
} else {
out.print("<td><p style = \"color: red; font-size: 20px\">Nedostupno</p></td>");
}
%>
<%
int a = rs.getInt("dostupnost");
if (a == 1) {
out.print("<td><select name=\"" + rs.getString("naziv") + "polje\"><option value = \"Dostupno\">Dostupno</option><option value = \"Nedostupno\">Nedostupno</option></select></td>");
} else {
out.print("<td><select name=\"" + rs.getString("naziv") + "polje\"><option value = \"Dostupno\">Dostupno</option><option value = \"Nedostupno\">Nedostupno</option></select></td>");
}
}
%>
</tr>
<tr><td></td><td></td><td></td><td></td>
<td align=center>
<form method="post">
<% Statement statement1 = connection.createStatement();
int bre;
ResultSet rs1 = statement.executeQuery(sql);
while (rs1.next()) {
if (request.getParameter(rs1.getString("naziv") + "polje").equals("Nedostupno")) {
bre = statement1.executeUpdate("UPDATE oruzje SET dostupnost = 0 WHERE naziv='" + rs1.getString("naziv") + "'");
} else {
bre = statement1.executeUpdate("UPDATE oruzje SET dostupnost = 0 WHERE naziv='" + rs1.getString("naziv") + "'");
}
}
} catch (Exception e) {
e.printStackTrace();
}
%>
<input type="submit" value="Apply" name="Apply" />
</form>
</td>
</tr>
</table>
<p style=" position: absolute; bottom: 0; left: 0; width: 100%; text-align: center;"><a href ="index.jsp"><img src = "Slike/home.png" alt = "home"/></a></p>
</body>
答
首先，我会分裂成Servlet和JSP代码，并且告诉你为什么这是一个更好的方式去。
你的看法（假设被称为 “foo.jsp”）：
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!-- adding JSTL to your project -->
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" type="text/css" href="Stil/cssstil.css">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Prikaz oružja</title>
</head>
<body>
<h1>Prikaz oruzja</h1>
<p align="center">
Administrator <span style="color:red"><c:out value="${user}" /></span>
</p>
<!--
In this case, the form should wrap all your table
since you want to process all the input elements
inside it. Otherwise, you will update the entire data
rather than update per entity.
-->
<form method="post">
<table align="center" bgcolor="darkgrey" border="1" cellpadding="3" cellspacing="1">
<th>
<td>
<p style = "color: black;">Naziv</p>
</td>
<td>
<p style = "color: black;">Opis</p>
</td>
<td>
<p style = "color: black;">Cena</p>
</td>
<td>
<p style = "color: black;">Dostupnost</p>
</td>
<td>
</td>
</th>
<c:forEach items="${oruzjeList}" var="oruzje">
<tr bgcolor="grey">
<td>${oruzje.naziv}</td>
<td>${oruzje.opis}</td>
<td>${oruzje.cena}</td>
<td>
<p style="color: green; font-size: 20px">
<!-- printing the value dynamically in view -->
<c:out value="${oruzje.dostupnost == '1' ? 'Dostupno' : 'Nedostupno'}" />
</p>
</td>
<td>
<!--
oruzje.naziv should be an ID, not the "unique name"
because a name could contain empty spaces which
will break the name of the component
here I use id but you will replace it with your ID
-->
<select name="polje_${oruzje.id}">
<option value="1" ${oruzje.dostupnost == '1' ? 'selected' : ''}>Dostupno</option>
<option value="0" ${oruzje.dostupnost == '0' ? 'selected' : ''}>Nedostupno</option>
</select>
<!--
Additionally, we need to obtain the respective id for
the row to be updated.
We will use a hidden input field which uses the ID
-->
<input type="hidden" name="hidId_${oruzje.id}" value="${oruzje.id}" />
</td>
</tr>
</c:forEach>
<tr>
<td></td><td></td><td></td><td></td>
<td>
<input type="submit" value="Apply" />
</td>
</tr>
</table>
</form>
<p style="position: absolute; bottom: 0; left: 0; width: 100%; text-align: center;">
<a href="index.jsp"><img src="Slike/home.png" alt="home"/></a>
</p>
</body>
</html>
这里假设你有一个支持映射到数据库表中的实体：
public class Oruzje {
private int id;
private String naziv;
private String opis;
private String cena;
private String dostupnost;
//propers getters and setters for your fields
}
现在，你的servlet ：
//yes, a Servlet can map directly to your JSP
//there's no problem using this approach
@WebServlet("/foo.jsp")
public class OruzjeServlet extends HttpServlet {
//I won't go far with more classes
//nor with other improvements to the code
//I'll write the basic stuff here in Servlet
//this method should be in an utility class
//to provide reusability
private void closeResource(Closeable resource) {
try {
if (resource != null) {
resource.close();
}
} catch (IOException e) {
//silent exception
}
}
//this method should also be in an utility class
//and should recover the connection from a DataSource
//instead of creating the physical connection everytime
private Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/CS230-Projekat", "root", "123");
} catch (Exception e) {
//this should be splitted for better handling
//but I leave this up to you
//handle the exception
//very basic handling
e.printStacktrace();
}
}
//this method should be in a proper DAO class
//reusing a connection for execution of multiple statements
private List<Oruzje> getOruzjeList(Connection con) {
List<Oruzje> oruzjeList = new ArrayList<Oruzje>();
Statement statement = null;
ResultSet rs = null;
try {
statement = connection.createStatement();
String sql = "SELECT * FROM oruzje";
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
Oruzje oruzje = new Oruzje();
oruzje.setId(rs.getInt("id")); //use the real column
oruzje.setNaziv(rs.getString("naziv"));
oruzje.setOpis(rs.getString("opis"));
oruzje.setCena(rs.getString("cena"));
oruzje.setDostupnost(rs.getString("dostupnost"));
oruzjeList.add(oruzje);
}
} catch (SQLException e) {
//again, handle the exception
e.printStacktrace();
} finally {
closeResource(rs);
closeResource(statement);
}
return oruzjeList;
}
//this method will be executed when a client (browser)
//tries to enter to your foo.jsp page
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
Connection con = getConnection();
List<Oruzje> oruzjeList = getOruzjeList(con);
closeResource(con);
//setting the oruzjeList variable as attribute
//this will feed the ${oruzjeList} used in the
//<c:forEach>
request.setAttribute("oruzjeList", oruzjeList);
//now, forward the view to the right view (JSP)
//it is not a redirect
request.getRequestDispatcher("/foo.jsp").forward(request, response);
}
//this method will be executed when user selects "Apply"
//option in the view (the JSP)
//because you stated that the method to submit the <form>
//is POST
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//now, we just retrieve the necessary parameters and process the data
//sent from the form
Connection con = getConnection();
List<Oruzje> oruzjeList = getOruzjeList(con);
//each ? is a parameter in the query
//starting at index 1
String updateSql = "UPDATE oruzje SET dostupnost = ? WHERE id = ?";
PreparedStatement pstmt = con.prepareStatement();
for(Oruzje oruzje : oruzjeList) {
//this is where the static part of the name becomes handy
String theId = request.getParameter("hidId_" + oruzje.getId());
String theDostupnost = request.getParameter("polje_" + oruzje.getId());
//I'll avoid basic validation like
//if (theId == null || "".equals(theId))
//right to the update!
//setting the first parameter: dostupnost = ?
pstmt.setString(1, theDostupnost);
//setting the second parameter: ID = ?
//since it's an int, parsing the String to int
pstmt.setInt(2, Integer.parseInt(theId));
//perform the update for the current ID and Dostupnost
pstmt.execute();
}
//at the end, closing the resources
closeResource(pstmt);
//this is cumbersome but just to make sure
//the data was updated successfully
oruzjeList = getOruzjeList(con);
closeResource(con);
//similar to the doGet, we will forward to the view
request.getRequestDispatcher("/foo.jsp").forward(request, response);
}
}
更多信息：
- How to avoid Java code in JSP files?
- StackOverflow JSTL Wiki
- StackOverflow EL Wiki
- Difference between JSP forward and redirect
- Sending a variable from Servlet to JSP
- Retrieve values from JDBC and use JSTL tags to call the methods
- Is it a good idea to put jdbc connection code in servlet class?
- Difference between Statement and PreparedStatement
+0
非常感谢你，先生！ – 2014-09-05 03:31:36
+0
让我知道你是否需要别的东西。 – 2014-09-05 03:32:29
我建议不要为您的HTML组件使用* dynamic *名称。另外，请避免在代码中使用scriptlet。 – 2014-09-05 00:50:20
我知道我的代码不好，但这是学校作业，我必须尽快完成它，所以我没有太多时间做研究，正如你所看到的。没有完全改变代码是否可以解决这个问题？ – 2014-09-05 00:52:03
同样：**我会建议不要为您的HTML组件使用动态名称**。没有必要这样做。为你的'