Q&A
ご回答有難う御座います。
ご回答通りにやってみたのですが変わらずでした。。。
###前提・実現したいこと
orde byをsqlで二回打っているためか
下記のエラーが出てしまいます。
改善の点を教えていただきたいです。
宜しくお願い致します。
###発生している問題・エラーメッセージ
HTTPステータス 500 - javax.servlet.ServletException: javax.servlet.jsp.JspException: type 例外レポート メッセージ javax.servlet.ServletException: javax.servlet.jsp.JspException: 説明 The server encountered an internal error that prevented it from fulfilling this request. 例外 org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: SELECT book.book_no,book.book_name,book.state,genre.genre_no,genre.genre_name,user.user_no,user.user_id, lend.lend_date,lend.return_date FROM book LEFT JOIN lend ON book.book_no = lend.book_no LEFT JOIN user ON user.user_no = lend.user_no LEFT JOIN genre ON book.genre_no = genre.genre_no where book.delete_flg = 0 order by book.book_no and book.genre_no = ? ORDER BY book.book_no asc; : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY book.book_no asc' at line 9 org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:548) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:454) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:396) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:340) javax.servlet.http.HttpServlet.service(HttpServlet.java:725) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 原因 javax.servlet.ServletException: javax.servlet.jsp.JspException: SELECT book.book_no,book.book_name,book.state,genre.genre_no,genre.genre_name,user.user_no,user.user_id, lend.lend_date,lend.return_date FROM book LEFT JOIN lend ON book.book_no = lend.book_no LEFT JOIN user ON user.user_no = lend.user_no LEFT JOIN genre ON book.genre_no = genre.genre_no where book.delete_flg = 0 order by book.book_no and book.genre_no = ? ORDER BY book.book_no asc; : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY book.book_no asc' at line 9 org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:904) org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:837) org.apache.jsp.book_005flend_jsp._jspService(book_005flend_jsp.java:369) org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) javax.servlet.http.HttpServlet.service(HttpServlet.java:725) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:431) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:396) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:340) javax.servlet.http.HttpServlet.service(HttpServlet.java:725) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 原因 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY book.book_no asc' at line 9 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) java.lang.reflect.Constructor.newInstance(Constructor.java:422) com.mysql.jdbc.Util.handleNewInstance(Util.java:404) com.mysql.jdbc.Util.getInstance(Util.java:387) com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902) com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526) com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673) com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962) org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:257) org.apache.jsp.book_005flend_jsp._jspx_meth_sql_005fquery_005f0(book_005flend_jsp.java:447) org.apache.jsp.book_005flend_jsp._jspService(book_005flend_jsp.java:137) org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) javax.servlet.http.HttpServlet.service(HttpServlet.java:725) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:431) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:396) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:340) javax.servlet.http.HttpServlet.service(HttpServlet.java:725) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 注意 原因のすべてのスタックトレースは、Apache Tomcat/8.0.20のログに記録されています Apache Tomcat/8.0.20
###JSP
<%@ page contentType="text/html; charset=UTF-8" import="java.sql.*,javax.naming.*,javax.sql.*,java.text.*,java.util.Date,java.util.Calendar"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%> <html> <sql:setDataSource var="db" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/Library" user="root" password="" /> <sql:query var="rs" dataSource="${db}"> SELECT book.book_no,book.book_name,book.state,genre.genre_no,genre.genre_name,user.user_no,user.user_id, lend.lend_date,lend.return_date FROM book LEFT JOIN lend ON book.book_no = lend.book_no LEFT JOIN user ON user.user_no = lend.user_no LEFT JOIN genre ON book.genre_no = genre.genre_no where book.delete_flg = 0 order by book.book_no <c:if test="${!empty param['genreNo']}"> and book.genre_no = ? <sql:param value="${param['genreNo']}" /> ORDER BY book.book_no asc; </c:if> </sql:query> <sql:query var="rs1" dataSource="${db}"> select * from genre </sql:query> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <link rel="stylesheet" type="text/css" href="css/Lending.css"> <title>書籍貸出画面</title> </head> <body> <% HttpSession a = request.getSession(); Boolean userResult = false; if (session.getAttribute("userResult") != null) { userResult = (boolean) session.getAttribute("userResult"); } else if (session.getAttribute("userResult") == null) { request.setAttribute("errorMessage", "ログインがされていません"); this.getServletContext().getRequestDispatcher("/Login.jsp") .forward(request, response); } %> <h2>書籍貸出画面</h2> <br> <form method="POST" action="book_lend.jsp"> <p> ジャンル <select name="genreNo"> <option value="">全て</option> <c:forEach var="row" items="${rs1.rows}"> <option value=${row.genre_no}>${row.genre_name}</option> </c:forEach> </select> </p> <INPUT style="color: white; background-color: D57200;" type="submit" value="絞り込む" /> </form> <br> <table border="1"> <tr> <th>ID</th> <th>書籍名</th> <th>ジャンル</th> <th>貸出日</th> <th>返却予定日</th> <th>状態</th> <th>貸出/返却</th> <th>貸りた人</th> </tr> <c:forEach var="row" items="${rs.rows}"> <tr> <td>${row.book_no}</td> <td>${row.book_name}</td> <td>${row.genre_name}</td> <td><c:if test="${empty row.lend_date}">-</c:if> ${row.lend_date }</td> <td><c:if test="${empty row.lend_date}">-</c:if> ${row.return_date }</td> <td><c:if test="${row.state == 2}"> <span class="fontRed">貸出不可</span> </c:if> <c:if test="${row.state == 1 }"> <c:if test="${empty row.lend_date}"> 貸出可能 </c:if> <c:if test="${!empty row.lend_date}"> <c:set var="today" value="<%=new Date()%>" /> <c:if test="${today > row.return_date}"> 返却遅延 </c:if> <c:if test="${today <= row.return_date}"> 貸出中 </c:if> </c:if> </c:if></td> <td><c:if test="${empty row.lend_date}"> <c:if test="${row.state == 2}">-</c:if> <c:if test="${row.state == 1}"> <form method="POST" action="book_lend"> <input type="hidden" value="${row.book_no}" name="book_no"> <input type="hidden" value="${row.genre_no}" name="genre_no"> <INPUT style="color: white; background-color: D57200; width: 60px; height: 40px;" type="submit" value="貸出" /> </form> </c:if> </c:if> <c:if test="${!empty row.lend_date}"> <form method="GET" action="book_lend"> <input type="hidden" value=${row.book_no } name="book_no" /> <INPUT style="color: white; background-color: AFD170; width: 60px; height: 40px;" type="submit" value="返却" /> </form> </c:if></td> <td><c:if test="${!empty row.lend_date}"> <option value=${row.user_no}>${row.user_id}</option> </c:if></td> </c:forEach> </table> <br> <form action="kanri_mypage.jsp"> <input style="color: white; background-color: D57200;" type="submit" name="mypage" value="TOPページ"> </form> </body> </html>
回答2件
0
sql
1where 2 book.delete_flg = 0 -- ソート１ 3order by 4 book.book_no 5 and book.genre_no = ? -- 文法がおかしい。これは Where句にあるべきなのでは？ 6ORDER BY 7 book.book_no asc; -- ソート１と同じことをしているため、どちらか不要
おかしいのはここですよね。
これを整形してみると
sql
1where 2 book.delete_flg = 0 3 and book.genre_no = ? 4order by 5 book.book_no
こうするためには tkturbo さんの回答ということになるのですが、いけませんか？
いけなかったとしても、同じエラーになるとは考えにくいのですが、
エラーログも全く同じなんでしょうか？
投稿2016/06/22 04:30
総合スコア4666
0
これでいいんじゃないすかね？
<sql:query var="rs" dataSource="${db}"> SELECT book.book_no,book.book_name,book.state,genre.genre_no,genre.genre_name,user.user_no,user.user_id, lend.lend_date,lend.return_date FROM book LEFT JOIN lend ON book.book_no = lend.book_no LEFT JOIN user ON user.user_no = lend.user_no LEFT JOIN genre ON book.genre_no = genre.genre_no where book.delete_flg = 0 <c:if test="${!empty param['genreNo']}"> and book.genre_no = ? </c:if> order by book.book_no </sql:query>
投稿2016/06/22 03:57
総合スコア5572
ifブロックをこの↓ように修正してもだめですか？
<c:if test="${!empty param['genreNo']}">
and book.genre_no = ?
<sql:param value="${param['genreNo']}" />
</c:if>
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/06/22 05:17
2016/06/22 06:13 編集
2016/06/22 09:12