String sql = "select cno,cname,tname from dbo.Course as c,dbo.teacher as t" +
"where CNO in (select cno from dbo.sc sc where sno = ? ) and t.tno = c.tno";红色的地方有错 ‘cno’ 附近语法错误,如果改成c.cno Junit测试报 ‘c’ 附近语法错误。我想问下,是不是sql语句写的不对,该怎么改?程序:public ArrayList<CourseTemp> showCourse(String sno){
Connection conn = new DB_util().getConnection();
ResultSet rs = null;
String sql = "select cno,cname,tname from dbo.Course as c,dbo.teacher as t" +
"where CNO in (select cno from dbo.sc sc where sno = ? ) and t.tno = c.tno";
ArrayList<CourseTemp> list = new ArrayList<CourseTemp>();
try {
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, sno);
stmt.execute();
rs = stmt.getResultSet();
while(rs.next())
{
CourseTemp ct = new CourseTemp();
ct.setCNO(rs.getString(1));
ct.setCNAME(rs.getString(2));
ct.setCTEACHER(rs.getString(3));
list.add(ct);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
"where CNO in (select cno from dbo.sc sc where sno = ? ) and t.tno = c.tno";红色的地方有错 ‘cno’ 附近语法错误,如果改成c.cno Junit测试报 ‘c’ 附近语法错误。我想问下,是不是sql语句写的不对,该怎么改?程序:public ArrayList<CourseTemp> showCourse(String sno){
Connection conn = new DB_util().getConnection();
ResultSet rs = null;
String sql = "select cno,cname,tname from dbo.Course as c,dbo.teacher as t" +
"where CNO in (select cno from dbo.sc sc where sno = ? ) and t.tno = c.tno";
ArrayList<CourseTemp> list = new ArrayList<CourseTemp>();
try {
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, sno);
stmt.execute();
rs = stmt.getResultSet();
while(rs.next())
{
CourseTemp ct = new CourseTemp();
ct.setCNO(rs.getString(1));
ct.setCNAME(rs.getString(2));
ct.setCTEACHER(rs.getString(3));
list.add(ct);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
"where cno in (select cno from dbo.sc sc where sno = ? ) and t.tno = c.tno";
还有,你可以先在数据库里面执行一下正确与否,这是排除法.....(排除错误)
where c.cno in (select cno from dbo.sc sc where sno = '95002' ) and t.tno = c.tno在数据库查询成功。但是在程序里调用还是不行,我已照上面兄弟说的修改了。String sql = "select cno,cname,tname from dbo.Course c,dbo.teacher t" +
"where c.cno in (select cno from dbo.sc sc where sno = ? ) and t.tno = c.tno";我把报错信息放上,希望能有用。com.microsoft.sqlserver.jdbc.SQLServerException: 'c' 附近有语法错误。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(Unknown Source)
at com.msx.DAO.CourseDAO.showCourse(CourseDAO.java:166)
at com.msx.DAO.CourseDAO.test(CourseDAO.java:191)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:66)
at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
where c.cno in (select cno from dbo.sc sc where sno = '95002' ) and t.tno = c.tno
,那您在具体的调用中也检查一下SNO带入具体的值啊!一开始不要用?如果SNO带入具体的值可以的话,再带入?。如果不可以的话那说明SQL语句错误.