public List<Question> queryQByUCIdAndTypeName(int courseId,String typeName,int userId){
List<Question> list = new ArrayList<Question>();
Question question =null;
try{
//获取连接
conn =DBConn.getConn();
//查询所有试卷信息的sql语句
String sql ="select question_id,question_title,choiceA,choiceB," +
"choiceC,choiceD,answer,,course_id,typename " +
"from question where typename=? " +
"and question_id in (select question_id from relation where user_id=? and course_id=?)";
pstm=conn.prepareStatement(sql);
pstm.setString(1,typeName);
pstm.setInt(2,userId);
pstm.setInt(3,courseId);
//获取rs结果集
rs=pstm.executeQuery();
System.out.println("sql cha");
while(rs.next()){
//实例化question对象
question = new Question();
//设置question的属性值
question.setQuestionId(rs.getInt(1));
question.setQuestionTitle(rs.getString(2));
question.setChoiceA(rs.getString(3));
question.setChoiceB(rs.getString(4));
question.setChoiceC(rs.getString(5));
question.setChoiceD(rs.getString(6));
question.setAnswer(rs.getString(7));
question.setMark(rs.getInt(8));
question.setCourseId(rs.getInt(9));
question.setTypeName(rs.getString(10));
list.add(question);
System.out.println("sql cha111");
}
}catch(Exception e){
System.out.println("查询所有试题根据考试参加课程的考试的题目类型查找题目失败!");
e.printStackTrace();
}finally{
this.close();
}
return list;
}SQLselect
List<Question> list = new ArrayList<Question>();
Question question =null;
try{
//获取连接
conn =DBConn.getConn();
//查询所有试卷信息的sql语句
String sql ="select question_id,question_title,choiceA,choiceB," +
"choiceC,choiceD,answer,,course_id,typename " +
"from question where typename=? " +
"and question_id in (select question_id from relation where user_id=? and course_id=?)";
pstm=conn.prepareStatement(sql);
pstm.setString(1,typeName);
pstm.setInt(2,userId);
pstm.setInt(3,courseId);
//获取rs结果集
rs=pstm.executeQuery();
System.out.println("sql cha");
while(rs.next()){
//实例化question对象
question = new Question();
//设置question的属性值
question.setQuestionId(rs.getInt(1));
question.setQuestionTitle(rs.getString(2));
question.setChoiceA(rs.getString(3));
question.setChoiceB(rs.getString(4));
question.setChoiceC(rs.getString(5));
question.setChoiceD(rs.getString(6));
question.setAnswer(rs.getString(7));
question.setMark(rs.getInt(8));
question.setCourseId(rs.getInt(9));
question.setTypeName(rs.getString(10));
list.add(question);
System.out.println("sql cha111");
}
}catch(Exception e){
System.out.println("查询所有试题根据考试参加课程的考试的题目类型查找题目失败!");
e.printStackTrace();
}finally{
this.close();
}
return list;
}SQLselect
"choiceC,choiceD,answer,,course_id,typename " +
"from question where typename='"+typeName+"'"+
"and question_id in(select question_id from relation where user_id="+userId+"+ and course_id="+courseId+")";
//pstm=conn.prepareStatement(sql1);
//pstm.setString(1,typeName);
//pstm.setInt(2,userId);
//pstm.setInt(3,courseId);
//获取rs结果集
Statement stmt =conn.createStatement();
rs=stmt.executeQuery(sql1);
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'and course_id=2)' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3170)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3099)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1169)
List<Question> list = new ArrayList<Question>();
Question question =null;
try{
//获取连接
conn =DBConn.getConn();
//查询所有试卷信息的sql语句
//String sql ="select question_id,question_title,choiceA,choiceB," +
// "choiceC,choiceD,answer,,course_id,typename " +
// "from question where typename=? " +
// "and question_id in (select question_id from relation where user_id=? and course_id=?)";
String sql1="select question_id,question_title,choiceA,choiceB," +
"choiceC,choiceD,answer,,course_id,typename " +
"from question where typename='"+typeName+"'"+
"and question_id in (select question_id from relation where user_id="+userId+" and course_id="+courseId+")";
//pstm=conn.prepareStatement(sql1);
//pstm.setString(1,typeName);
//pstm.setInt(2,userId);
//pstm.setInt(3,courseId);
//获取rs结果集
Statement stmt =conn.createStatement();
rs=stmt.executeQuery(sql1);
//rs=pstm.executeQuery();
System.out.println("sql cha");
while(rs.next()){
//实例化question对象
question = new Question();
//设置question的属性值
question.setQuestionId(rs.getInt(1));
question.setQuestionTitle(rs.getString(2));
question.setChoiceA(rs.getString(3));
question.setChoiceB(rs.getString(4));
question.setChoiceC(rs.getString(5));
question.setChoiceD(rs.getString(6));
question.setAnswer(rs.getString(7));
question.setMark(rs.getInt(8));
question.setCourseId(rs.getInt(9));
question.setTypeName(rs.getString(10));
list.add(question);
System.out.println("sql cha111");
}
}catch(Exception e){
System.out.println("查询所有试题根据考试参加课程的考试的题目类型查找题目失败!");
e.printStackTrace();
}finally{
this.close();
}
return list;
}