数据库脚本:
/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.27-log
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `question_copy` (
`questionId` int (32),
`question` varchar (300),
`optionA` varchar (300),
`optionB` varchar (300),
`optionC` varchar (300),
`optionD` varchar (300),
`subject` int (11),
`answer` varchar (300)
);
insert into `question_copy` (`questionId`, `question`, `optionA`, `optionB`, `optionC`, `optionD`, `subject`, `answer`) values('1','下列选项中,对于JSP动态网页特性说法错误的是','交互性','自动更新','高效性','随机性','1','D');
insert into `question_copy` (`questionId`, `question`, `optionA`, `optionB`, `optionC`, `optionD`, `subject`, `answer`) values('2','在Web项目中,用来存放微博。xml的目录是','Src','META-INf','WEB-INF','WEB-INF/lib','1','C');
insert into `question_copy` (`questionId`, `question`, `optionA`, `optionB`, `optionC`, `optionD`, `subject`, `answer`) values('3','在JavaScript中,对字符串‘12.2+3.5’进行parseInt转换,得到的结果是','16','NAN','12','15','3','C');
insert into `question_copy` (`questionId`, `question`, `optionA`, `optionB`, `optionC`, `optionD`, `subject`, `answer`) values('4','京京 有多帅','一般帅','挺帅的','很帅','非常帅','3','D');
实体类:
private int questionId;
private String question;
private String optionA;
private String optionB;
private String optionC;
private String optionD;
private int subject;
private String answer;
-+++get/set方法
Dao:
QuestionDao:
public interface QuestionDao {
//查询全部
List<Question> getAll();
//根据科目查询
List<Question> getAllBySubject(int subject);
//增加
int insertQuestion(Question question);
}
impl--》
QuestionDaoImpl:
public class QuestionDaoImpl extends BaseDao implements QuestionDao {
@Override
public List<Question> getAll() {
String sql="select * from question";
ResultSet rs = this.executeQuery(sql, null);
List<Question> questionList=new ArrayList<>();
try {
while(rs.next()){
Question question=new Question();
question.setQuestionId(rs.getInt("questionId"));
question.setQuestion(rs.getString("question"));
question.setOptionA(rs.getString("optionA"));
question.setOptionB(rs.getString("optionB"));
question.setOptionC(rs.getString("optionC"));
question.setOptionD(rs.getString("optionD"));
question.setSubject(rs.getInt("subject"));
question.setAnswer(rs.getString("answer"));
questionList.add(question);
}
}catch (Exception ex){
ex.printStackTrace();
}
return questionList;
}
@Override
public List<Question> getAllBySubject(int subject) {
String sql="select * from question where subject=?";
Object [] objects={subject};
ResultSet rs = this.executeQuery(sql, objects);
List<Question> questionList=new ArrayList<>();
try {
while(rs.next()){
Question question=new Question();
question.setQuestionId(rs.getInt("questionId"));
question.setQuestion(rs.getString("question"));
question.setOptionA(rs.getString("optionA"));
question.setOptionB(rs.getString("optionB"));
question.setOptionC(rs.getString("optionC"));
question.setOptionD(rs.getString("optionD"));
question.setSubject(rs.getInt("subject"));
question.setAnswer(rs.getString("answer"));
questionList.add(question);
}
}catch (Exception ex){
ex.printStackTrace();
}
return questionList;
}
@Override
public int insertQuestion(Question question) {
String sql="INSERT INTO question(question,optionA,optionB,optionC,optionD,`subject`,answer)VALUES(?,?,?,?,?,?,?)";
Object [] objects={question.getQuestion(),question.getOptionA(),question.getOptionB(),question.getOptionC(),question.getOptionD(),question.getSubject(),question.getAnswer()};
return this.executeUpdate(sql,objects);
}
}
Service:
QuestionService:
public interface QuestionService {
//查询全部
List<Question> getAll();
//根据科目查询
List<Question> getAllBySubject(int subject);
//增加
boolean insertQuestion(Question question);
}
impl--》
QuestionServiceImpl:
public class QuestionServiceImpl implements QuestionService {
//创建数据访问层对象
QuestionDao qd=new QuestionDaoImpl();
@Override
public List<Question> getAll() {
return qd.getAll();
}
@Override
public List<Question> getAllBySubject(int subject) {
return qd.getAllBySubject(subject);
}
@Override
public boolean insertQuestion(Question question) {
if(qd.insertQuestion(question)>0){
return true;
}
return false;
}
}
Servlet:
@WebServlet("/QuestionServlet")
public class QuestionServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req,resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=UTF-8");
String path = req.getParameter("path");
HttpSession session = req.getSession();
QuestionService qs=new QuestionServiceImpl();
if(path.equals("showAll")){
List<Question> questionList = qs.getAll();
//拼接json格式
String questionListJSON="";
StringBuffer sb=new StringBuffer("[");
Question question=null;
for(int i=0;;){
sb.append("{");
question = questionList.get(i);
sb.append("\"question\":\""+question.getQuestion()+"\",");
sb.append("\"optionA\":\""+question.getOptionA()+"\",");
sb.append("\"optionB\":\""+question.getOptionB()+"\",");
sb.append("\"optionC\":\""+question.getOptionC()+"\",");
sb.append("\"optionD\":\""+question.getOptionD()+"\",");
sb.append("\"answer\":\""+question.getAnswer()+"\"");
sb.append("}");
//判断是否是最后一次循环
if((++i)==questionList.size()){
break;
}else{
sb.append(",");
}
}
sb.append("]");
questionListJSON= sb.toString();
PrintWriter pw=resp.getWriter();
pw.print(questionListJSON);
pw.flush();
pw.close();
}else if(path.equals("bySubject")){
//条件查询
String subject = req.getParameter("subject");
List<Question> questionList = qs.getAllBySubject(Integer.valueOf(subject));
//拼接json格式
String questionListJSON="";
StringBuffer sb=new StringBuffer("[");
Question question=null;
for(int i=0;;){
sb.append("{");
question = questionList.get(i);
sb.append("\"question\":\""+question.getQuestion()+"\",");
sb.append("\"optionA\":\""+question.getOptionA()+"\",");
sb.append("\"optionB\":\""+question.getOptionB()+"\",");
sb.append("\"optionC\":\""+question.getOptionC()+"\",");
sb.append("\"optionD\":\""+question.getOptionD()+"\",");
sb.append("\"answer\":\""+question.getAnswer()+"\"");
sb.append("}");
//判断是否是最后一次循环
if((++i)==questionList.size()){
break;
}else{
sb.append(",");
}
}
sb.append("]");
questionListJSON= sb.toString();
PrintWriter pw=resp.getWriter();
System.out.println(questionListJSON);
pw.print(questionListJSON);
pw.flush();
pw.close();
}else if(path.equals("add")){
String qt = req.getParameter("question");
String a=req.getParameter("optionA");
String b=req.getParameter("optionB");
String c=req.getParameter("optionC");
String d=req.getParameter("optionD");
String subject=req.getParameter("subject");
String answer=req.getParameter("answer");
Question question=new Question();
question.setQuestion(qt);
question.setOptionA(a);
question.setOptionB(b);
question.setOptionC(c);
question.setOptionD(d);
question.setSubject(Integer.valueOf(subject));
question.setAnswer(answer);
boolean flag = qs.insertQuestion(question);
if(flag){
resp.getWriter().print("<script>alert('添加成功');window.location='index.jsp'</script>");
}
}
}
}
Index.jsp:
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>$Title$</title>
<script type="text/javascript" src="js/jquery-1.8.2.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
function init() {
$.getJSON("QuestionServlet","path=showAll", callBack);
function callBack(data) {
for (var i = 0; i < data.length; i++) {
$("#tab1").append("<tr class='tt'><td>" + data[i].question + "</td><td>" + data[i].optionA + "</td><td>" + data[i].optionB + "</td><td>" + data[i].optionC + "</td><td>" + data[i].optionD + "</td><td>" + data[i].answer + "</td></tr>");
}
}
}
init();
})
function bySubject() {
$("[class=tt]").remove();
var subject = document.getElementById("subject");
$.getJSON("QuestionServlet?subject=" + subject.value, "path=bySubject", callBack);
function callBack(data) {
for (var i = 0; i < data.length; i++) {
$("#tab1").append("<tr class='tt'><td>" + data[i].question + "</td><td>" + data[i].optionA + "</td><td>" + data[i].optionB + "</td><td>" + data[i].optionC + "</td><td>" + data[i].optionD + "</td><td>" + data[i].answer + "</td></tr>");
}
}
}
</script>
</head>
<body>
<p align="center">科目 <select name="" id="subject">
<option value="1">JSP</option>
<option value="2">OOP</option>
<option value="3">JavaScript</option>
</select>
<input type="button" onclick="bySubject()" value="搜索"> <a href="add.jsp">添加试题</a></p>
<table id="tab1" border="1" align="center">
<tr>
<td>题干</td>
<td>选项A</td>
<td>选项B</td>
<td>选项C</td>
<td>选项D</td>
<td>答案</td>
</tr>
</table>
</body>
</html>
add.jsp:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<form action="QuestionServlet?path=add" method="post">
<table align="center" border="1">
<tr>
<td>题目</td>
<td><input type="text" name="question"></td>
</tr>
<tr>
<td>选项A</td>
<td><input type="text" name="optionA"></td>
</tr>
<tr>
<td>选项B</td>
<td><input type="text" name="optionB"></td>
</tr>
<tr>
<td>选项C</td>
<td><input type="text" name="optionC"></td>
</tr>
<tr>
<td>选项D</td>
<td><input type="text" name="optionD"></td>
</tr>
<tr>
<td>科目</td>
<td><select name="subject">
<option value="1">JSP</option>
<option value="2">OOP</option>
<option value="3">JavaSCript</option>
</select></td>
</tr>
<tr>
<td>答案</td>
<td><select name="answer">
<option value="A">A</option>
<option value="B">B</option>
<option value="C">C</option>
<option value="D">D</option>
</select></td>
</tr>
<tr>
<td align="center" colspan="2"><input type="submit" value="提交"></td>
</tr>
</table>
</form>
</body>
</html>