sql语法错误(使用jstl标签)

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (114)

我正在服用usernamecolumn namenew entry在jsp中从用户更新。我正在尝试使用jstl标记更新我的SQL数据库表,并显示更新的表。jsp页面没有打开它在我的更新sql查询中给出了语法错误的消息。

我的jsp页面: -

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%> 
<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Found page</title>
    <style>
        a:link, a:visited {
            background-color:darkolivegreen;
            color: white;
            padding: 10px 25px;
            text-align: center;
            width:100px; 
            text-decoration: none;
            display: inline-block;
            border-radius:20px; 
        }


        a:hover, a:active {
            background-color: lightgreen;
        }
        header {
            background-color:teal;
            color:white;
            text-align:center;
            padding:5px;
        }
         #file {
            font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
            border-collapse: collapse;
            width: 100%;

        }

        #file td, #file th {
            border: 2px solid black;
            text-align: left;
            padding: 8px;                
        }            

        #file tr:hover {background-color: #ddd;} 

        #file th {
            padding-top: 12px;
            padding-bottom: 12px;
            background-color: lightslategray;
            color: white;
        }
        section {
            height:270px; 
            width:1050px;
            float:right;
            padding:87px;
        }
        footer {
            background-color:black;
            float:bottom;
            color:white;
            clear:both;
            text-align:center;
            padding:5px;
        }
    </style>
</head>
<body style="background-color:lightsteelblue;">
    <%
        String userName = null;
        String sessionID = null;
        Cookie[] cookies = request.getCookies();
        if (cookies != null) {
            for (Cookie cookie : cookies) {
                if (cookie.getName().equals("user")) {
                    userName = cookie.getValue();
                }
            }
        }
    %>
    <header>
        <h1>File Tracking System</h1>
        <div><span style="float:right">Hi <%=userName%></span></div> 
        <br>
        <form  style="float:right" action=" LogoutServlet" method="post">
            <input type="submit" value="Logout" >
        </form>
        <br>
    </header>
    <br>
    <a href="file.jsp">1.Insert New File</a>        
    <a href="fileStatus.jsp">2.Change File Status</a>
    <a href="found.jsp">3.Search your File</a>
    <a href="checkstatus.jsp">4.Check File Status</a> 
    <br>
    <br>
    <form method="POST">
        User ID:<br><input type="text" name="user" value="" size="20" />

下拉列表中的这些选项拼写与userdetails表中的列名相同

        Alter:<br><select name="use">
            <option>userName</option>
            <option>password</option>
            <option>role</option>
            <option>firstname</option>
            <option>lastname</option>
            <option>departmentname</option>
            <option>email</option>
            <option>mobile</option>
        </select>
        New Entry:<br><input type="text" name="enter" value="" size="20" />
        <input type="submit" value="submit" name="submit" />
    </form>
    <br>
    <section>
        <sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
                           url="jdbc:mysql://localhost/login"
                           user="root"  password="root"/>

udate sql查询 -

它的形式是(update table_name set table_column =“xyz”,其中column_name =“abc”;)

        <sql:update dataSource="${snapshot}" var="result">
            update usertable set "${param.use}"="${param.enter}" where username="${param.user}";
        </sql:update>
        <sql:query dataSource="${snapshot}" var="result">
            select * from usertable where username="${param.user}";
        </sql:query>

        <table id="file">
            <tr>  
                <th>UserName</th>
                <th>Password</th>
                <th>Role</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Department Name</th>
                <th>Email</th>
                <th>Mobile Number</th>
            </tr>
            <c:forEach var="row" items="${result.rows}">
                <tr>
                    <td><c:out value="${row.username}"/></td>
                    <td><c:out value="${row.password}"/></td>
                    <td><c:out value="${row.role}"/></td>
                    <td><c:out value="${row.firstname}"/></td>
                    <td><c:out value="${row.lasttname}"/></td>
                    <td><c:out value="${row.departmentname}"/></td>
                    <td><c:out value="${row.email}"/></td>
                    <td><c:out value="${row.mobile}"/></td>
                </tr>
            </c:forEach>
        </table>
    </section>
    <footer>
        Copyright 2016 NSIC. All right reserved.                             
    </footer>
</body>
</html>

什么应该是sql的正确语法?还有其他方法可以做到这一点吗?我使用了另一种方式,我提交值alter.jsp并在servlet中获取它们,changeServlet.java但它没有显示任何错误,并且没有被重定向到它只是打开http:// localhost:8080 / test9 / changeServlet的任何地方并且卡在这里显示一个白色空白页。

package bean;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

public class changeServlet extends HttpServlet {

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    //request.getRequestDispatcher("link.html").include(request, response);
    Cookie[] cookies = request.getCookies();
    if(cookies != null){
    for(Cookie cookie : cookies){
        if(cookie.getName().equals("JSESSIONID")){
            System.out.println("JSESSIONID="+cookie.getValue());
            break;
        }
    }
    }       
    HttpSession session = request.getSession(false);
    System.out.println("admin="+session.getAttribute("admin"));
   if(session!=null && session.getAttribute("admin") != null){
               String admin=(String)session.getAttribute("admin"); 
                boolean status=false;
    try{
        String user=request.getParameter("user");
        String column=request.getParameter("column");
        String data=request.getParameter("data");            
        boolean checkc=checkchar.value(column);
        boolean checkp=checkchar.value(data);

if语句在提交错误的条目时检查是否有错误的条目或空值,它重定向到entry.jsp。这意味着它工作到这里,但在此之后不起作用。

        if(checkc==true&&checkp==true) {
        Connection con=ConnectionProvider.getCon();
        String sql="update usertable set '"+column+"'='"+data+"' where username='"+user+"'";
        PreparedStatement pstmt =con.prepareStatement(sql);

        int rs=pstmt.executeUpdate();
        if(rs>0){status=true;}

            if(status){
                  PrintWriter out= response.getWriter();
                  out.print("data updated,"+admin);
                  response.sendRedirect("updated.jsp");
                        }
              else 
              {   
                  PrintWriter out= response.getWriter();
                  out.print("failed to update");
                  response.sendRedirect("notinsert.jsp");
              }
            }
        else{response.sendRedirect("entry.jsp");}
            }catch(SQLException e){}  
              }else{
    RequestDispatcher rd = getServletContext().getRequestDispatcher("/index.html");
    PrintWriter out= response.getWriter();
    out.println("<font color=red>Either user name or password is wrong.</font>");
    rd.include(request, response);
    }
}
}
提问于
用户回答回答于

在修改这些内容之前,您的查询将无效:

  1. 你的选项标签
<option value="userName">userName</option>
<option value="password">password</option>
<option value="role">role</option>
<option value="firstname">firstname</option>
<option value="lastname">lastname</option>
<option value="department">departmentname</option>
<option value="email">email</option>
<option value="mobile">mobile</option>
  1. 您正在同一页面上直接触发查询,即您已显示该框,并在其后立即查询您的更新。

这会导致问题,因为JSTL总是首先在服务器端运行,因此无论你在哪里写它,它都会先执行,现在执行那个查询时,jstl发现参数为null,所以现在查询就像这样。 : -

UPDATE TABLE `table-name` SET `null`=null where `user-name`=null;`

你可以在null=nullcolumn-name中看到它本身是null,可以考虑null值,但你的列名也是null,并且在数据库或表中不存在。

所以请在两个jsp页面中创建一个将收集信息并将它们作为参数传递给另一个jsp页面,现在这个页面必须包含查询,现在它可以正确获取查询中的参数值。

扫码关注云+社区

领取腾讯云代金券