這樣的執行問題請教高手

heyong2055發表於2005-04-14
在TOMCAT執行JSP的時候，生成了如下的錯誤。我猜測是歸檔DOC的時候生成的。可能與Arr欄位有關係但不知道怎麼修改，我很急，能告訴我一下方法嗎？
TOMCAT的出錯資訊：
java.sql.SQLException: Syntax error or access violation message from server: "Yo
u have an error in your SQL syntax; check the manual that corresponds to your My
SQL server version for the right syntax to use near '?' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2248)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2196)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1163)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(Dele
gatingPreparedStatement.java:195)
at com.csbook.documentsystem.DocMan.getDocInfo(DocMan.java:50)
at org.apache.jsp.defaultroot.docinfo_jsp._jspService(docinfo_jsp.java:1
06)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper
.java:311)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:3
01)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:284)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:204)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:257)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:567)
at org.apache.catalina.core.StandardContextValve.invokeInternal(Standard
ContextValve.java:245)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:199)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:567)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:184)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:151)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:164)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:149)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:567)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:156)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:567)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)

at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:20
6)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
:833)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce
ssConnection(Http11Protocol.java:732)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java
:619)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP
ool.java:688)
at java.lang.Thread.run(Thread.java:536)
頁面的出錯資訊：
HTTP Status 500 -
type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:358)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)


root cause

java.lang.NullPointerException
org.apache.jsp.defaultroot.docinfo_jsp._jspService(docinfo_jsp.java:194)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)


note The full stack trace of the root cause is available in the Tomcat logs.

JSP檔案的程式碼：
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*"%>
<jsp:useBean id="docMan" class="com.csbook.documentsystem.DocMan" scope="page"/>
<jsp:useBean id="deptMan" class="com.csbook.documentsystem.DepartmentMan" scope="page"/>
<jsp:useBean id="docBMan" class="com.csbook.documentsystem.DocBaseMan" scope="page"/>
<html>
<head>
<title>電子政務檔案管理系統</title>
<script language="JavaScript" src="tools.js"></script>
<script language="JavaScript">
function updateInfo(){
document.form1.opType.value="updateInfo";
document.form1.submit();
}
function storeIt(){
document.form1.opType.value="storeIt";
document.form1.submit();
}
</script>
</head>

<body>
<%
String id=request.getParameter("id");
if(id==null){
%>
<jsp:forward page="err.jsp?err=沒有該檔案"/>
<% }
int flag=docMan.getAttr(Integer.parseInt(id));
Hashtable docs=docMan.getDocInfo(Integer.parseInt(id));
ArrayList depts=deptMan.getDeptList();
Iterator it=depts.iterator();
%>
<form name="form1" method="post" action="action.jsp">
<table width="95%" border="1">
<tr>
<td width="22%"><div align="right">檔案名稱：</div></td>
<td width="78%"><div align="left">
<%=docs.get("name")%>
</div></td>
</tr>
<tr>
<td><div align="right">所屬機構：</div></td>
<td><div align="left">
<%
if(flag==0) {
%>
<select name="dept">
<%
while(it.hasNext()){
%>
<option><%=(String)it.next()%></option>
<%}%>
</select>
<%
}
else
{
%>
<%=docs.get("dept")%>
<%
}
%>
</div></td>
</tr>
<tr>
<td><div align="right">年度：</div></td>
<td><div align="left">
<%
if(flag==0){
%>
<select name="docyear">
<%
for(int i=1990;i<2100;i++)
{
%>
<option><%=i%></option>
<%
}
%>
</select>
<%
}
else{
%>
<%=docs.get("docyear")%>
</div></td>
</tr>
<%}%>
<tr>
<td><div align="right">儲存型別：</div></td>
<td>
<%
Object timeli=docs.get("timelimit");
int timelimit=Integer.parseInt(timeli.toString());
if (flag==0){
if (timelimit==0){
%>
<select name="timelimit">
<option value="0" selected>短期</option>
<option value="1">長期</option>
</select>
<%}
else{
%>
<select name="timelimit">
<option value="0">短期</option>
<option value="1" selected>長期</option>
</select>
<%
}
}
else{
if (timelimit==0){
%>
短期
<%
}
else{
%>
長期
<%
}
}
%>
</td>
</tr>
<tr>
<td height="120"><div align="right">備註說明：</div></td>
<td><div align="left">
<%
if (flag== 0){
%>
<textarea name="desc" cols="60" rows="8"><%=docs.get("desc")%></textarea>
<%}
else{
%>
<textarea name="desc" cols="60" rows="8" readonly><%=docs.get("desc")%></textarea>
</div></td>
</tr>
<%}
%>
<input name="id" type="hidden" value="<%=id%>"/>
<tr>
<td height="25"> </td>
<td>
<%
String operator=(String)session.getAttribute("userId");
boolean hasRight=docBMan.isWritableForDocBase((String)docs.get("docBase"),operator);
if(flag == 0){
if(hasRight){
%>
<input name="op" type="hidden" value="updateDoc">
<input name="opType" type="hidden">
<input type="submit" name="Submit" value="更新資訊" onClick="updateInfo()">
<input type="submit" name="Submit" value="歸檔" onClick="storeIt()">
<input type="button" name="golist" value="返回檔案庫" onClick="javascript:gotourl('doclist.jsp?docBase=<%=docs.get("docBase")%>')">
<%
}
}
else{
if(hasRight){
%>
<input name="op" type="hidden" value="setAttrOut"><br>
<input type="submit" name="Submit" value="移出檔案庫">
<%
}
%>
<input type="button" name="lend" value="借閱" onClick="javascript:gotourl('addlibinfo.jsp?docid=<%=id%>')">
<input type="button" name="back" value="返回" onClick="goback()">
<%
}
%>
</td>
</tr>
</table>
</form>
</body>
</html>
JAVABEAN的程式碼如下（可能引起錯誤的）：
package com.csbook.documentsystem;

import javax.naming.*;
import javax.sql.*;
import java.sql.*;
import java.util.*;

public class DocMan{
Context ctx=null;
DataSource ds=null;
SysLog log=null;
ChangeEncoding ce=null;
//建構函式
public DocMan()
{
//從連線池中獲取資料庫連線
try{
ctx=new InitialContext();
ds=(DataSource)ctx.lookup("documents");
}
catch(NamingException e){
e.printStackTrace();
}
log=new SysLog();
ce=new ChangeEncoding();
}

//獲得制定編號的檔案的資訊
//id為檔案的編號
public Hashtable getDocInfo(int id)
{
Connection con = null;
PreparedStatement ps = null;
ResultSet rs=null;
Hashtable ht=new Hashtable();
try{
String strQuery = "select * from documents where id=?";
con=ds.getConnection();
ps = con.prepareStatement(strQuery);
ps.setInt(1, id);
rs = ps.executeQuery(strQuery);
String temp;
if(rs.next()){
ht.put("name",ce.changeCharset(rs.getString("name")));
temp=rs.getString("dept");
if(temp!=null) ht.put("dept",ce.changeCharset(temp));
else ht.put("dept"," ");
ht.put("docyear",rs.getString("docyear"));
Integer timeli=new Integer(rs.getInt("timelimit"));
ht.put("timelimit",timeli.toString());
temp=rs.getString("description");
if(temp!=null)
ht.put("desc",ce.changeCharset(temp));
else ht.put("desc"," ");
temp=rs.getString("docBase");
if(temp!=null)
ht.put("docBase",ce.changeCharset(rs.getString("docBase")));
else
ht.put("docBase"," ");
}
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (rs != null) try {rs.close();}
catch (SQLException ignore) {}
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
return ht;
}

//從檔案編號獲得檔案的名稱
//id為檔案編號
public String getDocNameById(int id)
{
Connection con = null;
PreparedStatement ps = null;
ResultSet rs=null;
String doc="";
try {
String strQuery = "select name from documents where id=?";
con=ds.getConnection();
ps = con.prepareStatement(strQuery);
ps.setInt(1,id);
rs = ps.executeQuery();
if(rs.next())
doc=ce.changeCharset(rs.getString("name"));
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
return doc;
}

//根據輸入資訊檢索檔案資訊
public ArrayList getDocList(int attr,String name,String strDept,int intDocyear,String strBuilder,String docBase)
{
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList docList=new ArrayList();
try {
int i = 1;
String strQuery="select * from documents where attr=?";
if (!strDept.equals(""))
strQuery+=" and dept=?";
if (intDocyear != 0)
strQuery+=" and docyear=?";
if (!strBuilder.equals(""))
strQuery+=" and builder=?";
if (!docBase.equals(""))
strQuery+=" and docBase=?";
if (!name.equals(""))
strQuery+=" and name like ?";
con=ds.getConnection();
ps = con.prepareStatement(strQuery);
ps.setInt(i++, attr);
if (!strDept.equals(""))
ps.setString(i++, strDept);
if (intDocyear !=0)
ps.setInt(i++, intDocyear);
if (!strBuilder.equals(""))
ps.setString(i++, strBuilder);
if (!docBase.equals(""))
ps.setString(i++,ce.changeCharset(docBase));
if(!name.equals(""))
ps.setString(i++,'%'+ce.changeCharset(name)+'%');
rs = ps.executeQuery();
String temp;
while(rs.next()){
temp=rs.getInt("id")+" "+ce.changeCharset(rs.getString("name"))+" "+ce.changeCharset(rs.getString("docBase"))+" "+ce.changeCharset(rs.getString("dept"))+" "+rs.getInt("docyear");
docList.add(temp);
}
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (rs != null) try {rs.close();}
catch (SQLException ignore) {}
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
return docList;
}

//新增檔案
public void addDoc(String name,String dept,String docyear,int timelimit,String docBase,String description,String builder)
{
Connection con = null;
PreparedStatement ps = null;
try{
String strInsert = "insert into documents(name,dept,docyear,timelimit,attr,docBase,description,builder,buildtime) values(?,?,?,?,?,?,?,?,?)";
con=ds.getConnection();
ps = con.prepareStatement(strInsert);
ps.setString(1,ce.changeCharset(name));
ps.setString(2,ce.changeCharset(dept));
ps.setString(3,ce.changeCharset(docyear));
ps.setInt(4, timelimit);
ps.setInt(5,0);
ps.setString(6,ce.changeCharset(docBase));
if(description==null||description.equals(""))
ps.setString(7,description);
else
ps.setString(7,ce.changeCharset(description));
ps.setString(8,builder);
java.sql.Date currentTime=new java.sql.Date(System.currentTimeMillis());
ps.setDate(9,currentTime);
ps.executeUpdate();
log.addLog(builder,"add document:"+name,"document");
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
}

//更新檔案資訊
public void updateDoc(String operator,int intId,String dept,String docyear,int timelimit,String desc)
{
Connection con = null;
PreparedStatement ps = null;
try{
String strUpdate="update documents set dept=?,docyear=?, timelimit=?,description=? where id=?";
con=ds.getConnection();
ps = con.prepareStatement(strUpdate);
ps.setString(1, dept);
ps.setString(2, docyear);
ps.setInt(3, timelimit);
if(desc==null||desc.equals(""))
ps.setString(4, desc);
else
ps.setString(4,ce.changeCharset(desc));
ps.setInt(5,intId);
ps.executeUpdate();
log.addLog(operator,"update document:" +intId,"documents");
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
}

//刪除檔案
public void removeDoc(String operator,int id)
{
Connection con = null;
PreparedStatement ps = null;
try{
String strDelete = "delete from documents where id=?";
con=ds.getConnection();
ps = con.prepareStatement(strDelete);
ps.setInt(1, id);
ps.executeUpdate();
log.addLog(operator,"remove document:"+id,"documents");
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
}

//設定檔案儲存的檔案庫
public void setDocBase(String operator,int id,String docBase)
{
Connection con = null;
PreparedStatement ps = null;
try{
String strUpdate = "update documents set docBase=? where id=?";
con=ds.getConnection();
ps = con.prepareStatement(strUpdate);
ps.setString(1,ce.changeCharset(docBase));
ps.setInt(2,id);
ps.executeUpdate();
log.addLog(operator,"update document:" +id+" set docBase="+ce.changeCharset(docBase),"documents");
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
}

//設定檔案的歸檔屬性
public void setDocAttr(String operator,int id,int intAttr)
{
Connection con = null;
PreparedStatement ps = null;
try{
String strUpdate = "update documents set attr=? where id=?";
con=ds.getConnection();
ps = con.prepareStatement(strUpdate);
ps.setInt(1, intAttr);
ps.setInt(2, id);
ps.executeUpdate();
log.addLog(operator,"update document:" +id+" set attr="+intAttr,"documents");
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
}

//根據文件編號獲取文件的歸檔屬性
public int getAttr(int id)
{
Connection con = null;
PreparedStatement ps = null;
ResultSet rs=null;
int attr=0;
try{
String strUpdate = "select attr from documents where id=?";
con=ds.getConnection();
ps = con.prepareStatement(strUpdate);
ps.setInt(1,id);
rs=ps.executeQuery();
if(rs.next())
attr=rs.getInt("attr");
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (rs != null) try {rs.close();}
catch (SQLException ignore) {}
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
return attr;
}

//從檔案編號獲得檔案儲存的檔案庫
public String getDocBaseById(int id)
{
Connection con = null;
PreparedStatement ps = null;
ResultSet rs=null;
String docBase="";
try{
String strQuery = "select docBase from documents where id=?";
con=ds.getConnection();
ps = con.prepareStatement(strQuery);
ps.setInt(1,id);
rs=ps.executeQuery();
if(rs.next())
docBase=rs.getString("docBase");
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (rs != null) try {rs.close();}
catch (SQLException ignore) {}
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
return docBase;
}

//在刪除機構前,把該機構的所有檔案轉到其他機構
public void changeDocDept(String oldDept,String newDept)
{
Connection con = null;
PreparedStatement ps = null;
try{
String strUpdate = "update documents set dept=? where dept=?";
con=ds.getConnection();
ps = con.prepareStatement(strUpdate);
ps.setString(1,ce.changeCharset(newDept));
ps.setString(2,ce.changeCharset(oldDept));
ps.executeUpdate();
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
}

//在刪除機構前,把該機構的檔案移出檔案庫
public void setDeptDocOut(String dept)
{
Connection con = null;
PreparedStatement ps = null;
try{
String strUpdate = "update documents set attr=0,dept='deleted' where dept=?";
con=ds.getConnection();
ps = con.prepareStatement(strUpdate);
ps.setString(1,ce.changeCharset(dept));
ps.executeUpdate();
}
catch(SQLException e){
e.printStackTrace();
}
finally{
if (ps != null) try {ps.close();}
catch (SQLException ignore) {}
if (con != null) try {con.close();}
catch (SQLException ignore) {}
}
}
}

相關文章