报错信息
一直给我报MySQL的语法错误
十月 11, 2022 8:16:16 下午 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet [jsp] in context with path [/Mys] threw exception [javax.servlet.ServletException: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.stuno=t_score.stuno and t_score.courseno='001'' at line 1] with root cause
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.stuno=t_score.stuno and t_score.courseno='001'' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)
at Dao.ScoreDAO.getAllScoreByCourseno(ScoreDAO.java:27)
at org.apache.jsp.scoreForm_jsp._jspService(scoreForm_jsp.java:149)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:438)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:396)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:340)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
未修改源代码是这样的:
package Dao;
import java.sql.*;
import java.util.ArrayList;
import vo.Score;
public class ScoreDAO {
//连接数据库
private Connection conn=null;
public void initConnection()throws Exception{
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/school","root","");
}
//查询所有学生的成绩
public ArrayList<Score> getAllScoreByCourseno(String courseno) throws Exception{
ArrayList<Score> al=new ArrayList<Score>();
initConnection();
//这里就是报错的地方,看上去就很正常,但实际上在from后面的表名要加上 tab键 上面的符号,才不会报错
//--------------------------------------------------------------------------------------------------------------------------------------------
String sql="select t_student.stuno,t_student.stuname,t_score.type,t_score.score "+
"from t_student,t_score"+
"where t_student.stuno=t_score.stuno "+
"and t_score.courseno=? ";
//--------------------------------------------------------------------------------------------------------------------------------------------
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1,courseno);
ResultSet rs=ps.executeQuery();
while(rs.next()) {
Score score =new Score();
score.setStuno(rs.getString("stuno"));
score.setStuname(rs.getString("stuname"));
score.setType(rs.getString("Type"));
score.setScore(rs.getString("Score"));
al.add(score);
}
closeConnection();
return al;
}
//修改成绩
public void updateScores(String courseno,String[] type,String[] stuno,String[] score) throws Exception{
initConnection();
String sql="update t_score set score=? where stuno=? and type=? and courseno=?";
PreparedStatement ps=conn.prepareStatement(sql);
for(int i=0;i<stuno.length;i++) {
if(!score[i].equals("")) {
ps.setDouble(1,Double.parseDouble(score[i]));
ps.setString(2, stuno[i]);
ps.setString(3,type[i]);
ps.setString(4, courseno);
ps.executeUpdate();
}
}
ps.close();
closeConnection();
}
public void closeConnection() throws Exception {
// TODO Auto-generated method stub
conn.close();
}
}
修改后的效果:
String sql="select t_student.stuno,t_student.stuname,t_score.type,t_score.score "+
"from `t_student`,`t_score`"+
"where t_student.stuno=t_score.stuno "+
"and t_score.courseno=? ";
**然后就不报错了! **
就是说老掉坑里,老倒霉蛋了