个人中心
gpt4 book ai didi

mysql - 在jsp页面显示mysql代码输出

转载 作者：行者123 更新时间：2023-11-28 23:47:58 27 4
gpt4 key购买 nike

我想计算给定天数的总时间。从 retrieve.jsp 页面插入值，并在 cal.jsp 页面上显示结果。我知道将 SQL 代码或任何与数据库相关的代码放在 JSP 中是个坏主意。但我想尝试这种方式请帮助。

检索.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>

<%@page import="com.eis.bean.Provider"%>
<%@page import="com.eis.bean.ConnectionProvider"%>
<%@page import="java.sql.*" %>
<%@page import="com.eis.servlet.RetrieveServlet"%>

<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>JSP Page</title>
</head>
<body>
    <h4>Enter Employee ID and the dates</h4>
    <form name="retrieve form" action="cal.jsp" method="POST">
        <table border="0">

            <tbody>
                <tr>
                    <td>Employee ID</td>
                    <td><input type="text" name="Emp_id" required="required"/></td>
                </tr>
                <tr>
                    <td>From Date:</td>
                    <td><input type="date" name="From" value="yyyy/MM/dd" required="required"/></td>
                </tr>
                <tr>
                    <td>To Date:</td>
                    <td><input type="date" name="To" value="yyyy/MM/dd" required="required"/></td>
                </tr>
            </tbody>
        </table>
        <input type="reset" value="Clear" name="clear" />
        <input type="submit" value="Submit" name="submit" />
    </form>
</body>
</html>

调用.jsp

<!DOCTYPE html>
<%@page import="com.eis.bean.Provider"%>
<%@page import="com.eis.bean.ConnectionProvider"%>
<%@page import="java.sql.*" %>
<%@page import="com.eis.servlet.RetrieveServlet"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<jsp:include page="retrieve.jsp"/>
<%
String empid = request.getParameter("Emp_id");
String from = request.getParameter("From");
String to = request.getParameter("To");
Connection conn= null;
PreparedStatement ps1= null; 
ResultSet rs1= null; 

     conn = ConnectionProvider.getConn(); 

    ps1 = conn.prepareStatement("SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`total`))) AS Total FROM timsheetdb.logintable WHERE Emp_id=? AND LoginDate BETWEEN ? AND ?;");

    ps1.setString(1, empid); 
    ps1.setString(2, from);
    ps1.setString(3, to);

    rs1=ps1.executeQuery();
      String Total=null;
        while (rs1.next())  
        {
           Total = rs1.getString("Total");
           out.println("Total is : " + Total + "<br>");
        }

 %>

我得到的错误:

 org.apache.jasper.JasperException: An exception occurred processing JSP page /cal.jsp at line 28

25:           String Total=null;
26:             while (rs1.next())  
27:             {
28:                Total = rs1.getString("Total");
29:                out.println("Total is : " + Total + "<br>");
30:             }
31:              


Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:574)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:461)
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:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

root cause

javax.servlet.ServletException: java.sql.SQLException: Illegal hour value '27' for java.sql.Time type in value '27:00:00.
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:909)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:838)
org.apache.jsp.cal_jsp._jspService(cal_jsp.java:143)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:438)
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:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

root cause

java.sql.SQLException: Illegal hour value '27' for java.sql.Time type in value '27:00:00.
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
com.mysql.jdbc.TimeUtil.fastTimeCreate(TimeUtil.java:286)
com.mysql.jdbc.ResultSetImpl.fastTimeCreate(ResultSetImpl.java:973)
com.mysql.jdbc.ResultSetImpl.getTimeFromString(ResultSetImpl.java:5558)
com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5319)
com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5173)
com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5212)
org.apache.jsp.cal_jsp._jspService(cal_jsp.java:125)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:438)
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:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

我认为它导致此错误是因为 java.sql.SQLException: Illegal hour value '27' for java.sql.Time type in value '27:00:00 。我在我的数据库中将总计列设置为时间戳类型。

我在 sql 中试过它工作正常下面是图像 enter image description here

最佳答案

请尝试使用转换函数将时间戳转换为字符串。例如；

SELECT CAST((SEC_TO_TIME(SUM(TIME_TO_SEC(`total`))) ) AS char) AS Total FROM timeornek.logintable  WHERE user=50 AND total BETWEEN '2015-10-13 00:00:00' AND '2015-10-25 00:00:00'

关于mysql - 在jsp页面显示mysql代码输出，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/33232078/

27 4 0
文章推荐： mysql - NATURAL JOIN 无法正常工作
文章推荐： tomcat - 在 Lucene 中索引 500,000 个用户时频繁的 GC (ParNew)
文章推荐： tomcat - 从 servlet 应用程序访问 server.xml 中的主机信息
文章推荐： ios - 列出附加到 SCNNode 的所有 CAAnimation
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com