在jsp页面中显示mysql代码输出 [英] display mysql code output in jsp page
本文介绍了在jsp页面中显示mysql代码输出的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想计算出给定天数的总时间.值从retrieve.jsp页面插入，并在cal.jsp页面上显示结果.我知道将SQL代码或任何与数据库相关的代码放在JSP中是一个坏主意.但我想尝试这种方式，请帮忙.
I want to calculate total time in the give days. values are inserted from the retrieve.jsp page and it shows the result on cal.jsp page. I know that it is a bad idea to put SQL code or any database related code in JSP. But I want to try this way please help.
retrieve.jsp
retrieve.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>
cal.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导致此错误的原因:java.sql.Time的小时小时值'27'键入值'27:00:00. 我在数据库中将总计"列设置为时间戳记"类型.
I think its causing this error beacause of java.sql.SQLException: Illegal hour value '27' for java.sql.Time type in value '27:00:00 . I my database i set the Total column to Timestamp type.
我在sql中尝试过，下面的工作正常是图像
I have tried in sql its working fine below is the image
推荐答案
请尝试使用强制转换功能将时间戳转换为字符串.例如；
try please convert timestamp to string with cast function. For example;
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'
这篇关于在jsp页面中显示mysql代码输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
