【愚公系列】2023年05月 Java面面刷题系统-002.数据库设计和（学科、目录、题目、题目选项）

一、数据库设计

1.数据库设计范式

数据库设计范式是关系型数据库中的一种规范化设计方法，目的是提高数据的一致性、完整性和准确性。常见的数据库设计范式有以下几种：

• 第一范式（1NF）：每个列都是原子性的，不可再分。

• 第二范式（2NF）：在满足1NF的基础上，非主键列必须完全依赖于主键，而不能依赖于主键的一部分。

• 第三范式（3NF）：在满足2NF的基础上，任何非主键列之间都不能有传递依赖关系，即不能存在A->B->C这样的依赖关系。

• 巴斯-科德范式（BCNF）：在满足3NF的基础上，任何非主键列都不能依赖于非主键列，即不存在A->B、B->C这样的依赖关系。

• 第四范式（4NF）：在满足BCNF的基础上，消除多值依赖关系，即一个表中的每个非主键列都与主键有函数依赖关系。

• 第五范式（5NF）：在满足4NF的基础上，消除联合依赖关系，即一个表中的每个非主键列都和主键有独立的函数依赖关系，不会受到其他非主键列的影响。

通过遵循数据库设计范式，可以有效地减少数据冗余，提高数据存储和查询的效率，保障数据的一致性和完整性。

1.2 数据库设计工具

PowerDesigner是一款数据建模和设计工具，可以帮助企业进行数据架构设计、业务流程设计、应用程序设计等工作。它能够支持多种数据模型，包括关系型、面向对象、XML等，同时也能够与多种数据库管理系统进行集成，如Oracle、SQL Server、MySQL等。PowerDesigner的主要功能包括数据建模、业务流程建模、应用程序设计、数据仓库设计、版本管理等。它可以帮助企业在数据管理方面提高效率和准确性，减少错误和风险。

PowerDesigner下载地址：https://www.sap.com/cmp/td/sap-powerdesigner-trial.html

二、学科模块

下面我们进行学科模块的快速开发：

1、创建学科实体：Course

public class Course {
private String id;
private String name;
private String remark;
private String state;
private Date createTime;

// getter/setter略
}

2、创建dao接口：CourseDao

public interface CourseDao {
int save(Course course);

int delete(Course course);

int update(Course course);

Course findById(String id);

List<Course> findAll();
}

3、创建业务层接口：CourseService

public interface CourseService {
/**
* 添加
* @param course
* @return
*/
void save(Course course);

/**
* 删除
* @param course
* @return
*/
void delete(Course course);

/**
* 修改
* @param course
* @return
*/
void update(Course course);

/**
* 查询单个
* @param id 查询的条件（id）
* @return 查询的结果，单个对象
*/
Course findById(String id);

/**
* 查询全部的数据
* @return 全部数据的列表对象
*/
List<Course> findAll();

/**
* 分页查询数据
* @param page 页码
* @param size 每页显示的数据总量
* @return
*/
PageInfo findAll(int page, int size);
}

4、创建业务层实现类：CourseServiceImpl

public class CourseServiceImpl implements CourseService {
@Override
public void save(Course course) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CourseDao courseDao = MapperFactory.getMapper(sqlSession,CourseDao.class);
//id使用UUID的生成策略来获取
String id = UUID.randomUUID().toString();
course.setId(id);
//3.调用Dao层操作
courseDao.save(course);
//4.提交事务
TransactionUtil.commit(sqlSession);
}catch (Exception e){
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public void delete(Course course) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CourseDao courseDao = MapperFactory.getMapper(sqlSession,CourseDao.class);
//3.调用Dao层操作
courseDao.delete(course);
//4.提交事务
TransactionUtil.commit(sqlSession);
}catch (Exception e){
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public void update(Course course) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CourseDao courseDao = MapperFactory.getMapper(sqlSession,CourseDao.class);
//3.调用Dao层操作
courseDao.update(course);
//4.提交事务
TransactionUtil.commit(sqlSession);
}catch (Exception e){
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public Course findById(String id) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CourseDao courseDao = MapperFactory.getMapper(sqlSession,CourseDao.class);
//3.调用Dao层操作
return courseDao.findById(id);
}catch (Exception e){
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public List<Course> findAll() {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CourseDao courseDao = MapperFactory.getMapper(sqlSession,CourseDao.class);
//3.调用Dao层操作
return courseDao.findAll();
}catch (Exception e){
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public PageInfo findAll(int page, int size) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CourseDao courseDao = MapperFactory.getMapper(sqlSession,CourseDao.class);
//3.调用Dao层操作
PageHelper.startPage(page,size);
List<Course> all = courseDao.findAll();
PageInfo pageInfo = new PageInfo(all);
return pageInfo;
}catch (Exception e){
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}
}

5、创建servlet：CourseServlet

// uri:/store/course?operation=list
@WebServlet("/store/course")
public class CourseServlet extends BaseServlet {

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String operation = request.getParameter("operation");
if("list".equals(operation)){
this.list(request,response);
}else if("toAdd".equals(operation)){
this.toAdd(request,response);
}else if("save".equals(operation)){
this.save(request, response);
}else if("toEdit".equals(operation)){
this.toEdit(request,response);
}else if("edit".equals(operation)){
this.edit(request,response);
}else if("delete".equals(operation)){
this.delete(request,response);
}
}

private void list(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
//进入列表页
//获取数据
int page = 1;
int size = 5;
if(StringUtils.isNotBlank(request.getParameter("page"))){
page = Integer.parseInt(request.getParameter("page"));
}
if(StringUtils.isNotBlank(request.getParameter("size"))){
size = Integer.parseInt(request.getParameter("size"));
}
PageInfo all = courseService.findAll(page, size);
//将数据保存到指定的位置
request.setAttribute("page",all);
//跳转页面
request.getRequestDispatcher("/WEB-INF/pages/store/course/list.jsp").forward(request,response);
}

private void toAdd(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
//跳转页面
request.getRequestDispatcher("/WEB-INF/pages/store/course/add.jsp").forward(request,response);
}

private void save(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
//将数据获取到，封装成一个对象
Course course = BeanUtil.fillBean(request,Course.class,"yyyy-MM-dd");
//调用业务层接口save
courseService.save(course);
//跳转回到页面list
response.sendRedirect(request.getContextPath()+"/store/course?operation=list");
}

private void toEdit(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//查询要修改的数据findById
String id = request.getParameter("id");
Course course = courseService.findById(id);
//将数据加载到指定区域，供页面获取
request.setAttribute("course",course);
//跳转页面
request.getRequestDispatcher("/WEB-INF/pages/store/course/update.jsp").forward(request,response);
}

private void edit(HttpServletRequest request, HttpServletResponse response) throws IOException {
//将数据获取到，封装成一个对象
Course course = BeanUtil.fillBean(request,Course.class,"yyyy-MM-dd");
//调用业务层接口save
courseService.update(course);
//跳转回到页面list
response.sendRedirect(request.getContextPath()+"/store/course?operation=list");
}

private void delete(HttpServletRequest request, HttpServletResponse response) throws IOException {
//将数据获取到，封装成一个对象
Course course = BeanUtil.fillBean(request,Course.class);
//调用业务层接口save
courseService.delete(course);
//跳转回到页面list
response.sendRedirect(request.getContextPath()+"/store/course?operation=list");
}

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request,response);
}
}

6、修改BaseServlet，添加CourseService

public class BaseServlet extends HttpServlet {
protected CompanyService companyService;
protected DeptService deptService;
protected UserService userService;
protected CourseService courseService;

@Override
public void init() throws ServletException {
companyService = new CompanyServiceImpl();
deptService = new DeptServiceImpl();
userService = new UserServiceImpl();
courseService = new CourseServiceImpl();
}
}

三、目录模块

1、创建目录实体：Catalog

public class Catalog {
private String id;
private String name;
private String remark;
private String state;
private Date createTime;
private String courseId;

private Course course;

// getter/setter略
}

2、创建dao接口：CatalogDao

public interface CatalogDao {
int save(Catalog catalog);

int delete(Catalog catalog);

int update(Catalog catalog);

Catalog findById(String id);

List<Catalog> findAll();
}

3、创建业务层接口：CatalogService

public interface CatalogService {
/**
* 添加
* @param catalog
* @return
*/
void save(Catalog catalog);

/**
* 删除
* @param catalog
* @return
*/
void delete(Catalog catalog);

/**
* 修改
* @param catalog
* @return
*/
void update(Catalog catalog);

/**
* 查询单个
* @param id 查询的条件（id）
* @return 查询的结果，单个对象
*/
Catalog findById(String id);

/**
* 查询全部的数据
* @return 全部数据的列表对象
*/
List<Catalog> findAll();

/**
* 分页查询数据
* @param page 页码
* @param size 每页显示的数据总量
* @return
*/
PageInfo findAll(int page, int size);
}

4、创建业务层实现类：CatalogServiceImpl

public class CatalogServiceImpl implements CatalogService {
@Override
public void save(Catalog catalog) {
SqlSession sqlSession = null;
try {
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CatalogDao catalogDao = MapperFactory.getMapper(sqlSession, CatalogDao.class);
//id使用UUID的生成策略来获取
String id = UUID.randomUUID().toString();
catalog.setId(id);

//3.调用Dao层操作
catalogDao.save(catalog);
//4.提交事务
TransactionUtil.commit(sqlSession);
} catch (Exception e) {
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
} finally {
try {
TransactionUtil.close(sqlSession);
} catch (Exception e) {
e.printStackTrace();
}
}
}

@Override
public void delete(Catalog catalog) {
SqlSession sqlSession = null;
try {
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CatalogDao catalogDao = MapperFactory.getMapper(sqlSession, CatalogDao.class);
//3.调用Dao层操作
catalogDao.delete(catalog);
//4.提交事务
TransactionUtil.commit(sqlSession);
} catch (Exception e) {
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
} finally {
try {
TransactionUtil.close(sqlSession);
} catch (Exception e) {
e.printStackTrace();
}
}
}

@Override
public void update(Catalog catalog) {
SqlSession sqlSession = null;
try {
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CatalogDao catalogDao = MapperFactory.getMapper(sqlSession, CatalogDao.class);
//3.调用Dao层操作
catalogDao.update(catalog);
//4.提交事务
TransactionUtil.commit(sqlSession);
} catch (Exception e) {
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
} finally {
try {
TransactionUtil.close(sqlSession);
} catch (Exception e) {
e.printStackTrace();
}
}
}

@Override
public Catalog findById(String id) {
SqlSession sqlSession = null;
try {
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CatalogDao catalogDao = MapperFactory.getMapper(sqlSession, CatalogDao.class);
//3.调用Dao层操作
return catalogDao.findById(id);
} catch (Exception e) {
throw new RuntimeException(e);
//记录日志
} finally {
try {
TransactionUtil.close(sqlSession);
} catch (Exception e) {
e.printStackTrace();
}
}
}

@Override
public List<Catalog> findAll() {
SqlSession sqlSession = null;
try {
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CatalogDao catalogDao = MapperFactory.getMapper(sqlSession, CatalogDao.class);
//3.调用Dao层操作
return catalogDao.findAll();
} catch (Exception e) {
throw new RuntimeException(e);
//记录日志
} finally {
try {
TransactionUtil.close(sqlSession);
} catch (Exception e) {
e.printStackTrace();
}
}
}

@Override
public PageInfo findAll(int page, int size) {
SqlSession sqlSession = null;
try {
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
CatalogDao catalogDao = MapperFactory.getMapper(sqlSession, CatalogDao.class);
//3.调用Dao层操作
PageHelper.startPage(page, size);
List<Catalog> all = catalogDao.findAll();
PageInfo pageInfo = new PageInfo(all);
return pageInfo;
} catch (Exception e) {
throw new RuntimeException(e);
//记录日志
} finally {
try {
TransactionUtil.close(sqlSession);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}

5、创建servlet：CatalogServlet

// uri:/store/catalog?operation=list
@WebServlet("/store/catalog")
public class CatalogServlet extends BaseServlet {

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String operation = request.getParameter("operation");
if("list".equals(operation)){
this.list(request,response);
}else if("toAdd".equals(operation)){
this.toAdd(request,response);
}else if("save".equals(operation)){
this.save(request, response);
}else if("toEdit".equals(operation)){
this.toEdit(request,response);
}else if("edit".equals(operation)){
this.edit(request,response);
}else if("delete".equals(operation)){
this.delete(request,response);
}
}

private void list(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
//进入列表页
//获取数据
int page = 1;
int size = 5;
if(StringUtils.isNotBlank(request.getParameter("page"))){
page = Integer.parseInt(request.getParameter("page"));
}
if(StringUtils.isNotBlank(request.getParameter("size"))){
size = Integer.parseInt(request.getParameter("size"));
}
PageInfo all = catalogService.findAll(page, size);
//将数据保存到指定的位置
request.setAttribute("page",all);
//跳转页面
request.getRequestDispatcher("/WEB-INF/pages/store/catalog/list.jsp").forward(request,response);
}

private void toAdd(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
//跳转页面
request.getRequestDispatcher("/WEB-INF/pages/store/catalog/add.jsp").forward(request,response);
}

private void save(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
//将数据获取到，封装成一个对象
Catalog catalog = BeanUtil.fillBean(request,Catalog.class,"yyyy-MM-dd");
//调用业务层接口save
catalogService.save(catalog);
//跳转回到页面list
response.sendRedirect(request.getContextPath()+"/store/catalog?operation=list");
}

private void toEdit(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//查询要修改的数据findById
String id = request.getParameter("id");
Catalog catalog = catalogService.findById(id);
//将数据加载到指定区域，供页面获取
request.setAttribute("catalog",catalog);

//跳转页面
request.getRequestDispatcher("/WEB-INF/pages/store/catalog/update.jsp").forward(request,response);
}

private void edit(HttpServletRequest request, HttpServletResponse response) throws IOException {
//将数据获取到，封装成一个对象
Catalog catalog = BeanUtil.fillBean(request,Catalog.class,"yyyy-MM-dd");
//调用业务层接口save
catalogService.update(catalog);
//跳转回到页面list
response.sendRedirect(request.getContextPath()+"/store/catalog?operation=list");
}

private void delete(HttpServletRequest request, HttpServletResponse response) throws IOException {
//将数据获取到，封装成一个对象
Catalog catalog = BeanUtil.fillBean(request,Catalog.class);
//调用业务层接口save
catalogService.delete(catalog);
//跳转回到页面list
response.sendRedirect(request.getContextPath()+"/store/catalog?operation=list");
}

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request,response);
}
}

6、修改BaseServlet，添加CatalogService

public class BaseServlet extends HttpServlet {
protected CompanyService companyService;
protected DeptService deptService;
protected UserService userService;
protected CourseService courseService;
protected CatalogService catalogService;

@Override
public void init() throws ServletException {
companyService = new CompanyServiceImpl();
deptService = new DeptServiceImpl();
userService = new UserServiceImpl();
courseService = new CourseServiceImpl();
catalogService = new CatalogServiceImpl();
}
}

四、题目模块

1、创建题目实体：Question

public class Question {
private String id; //题目ID

private String companyId; //所属企业
private String catalogId; //题目所属目录ID

private String remark; //题目简介

private String subject; //题干

private String analysis; //题目分析

private String type; //题目类型 1:单选，2：多选，3：简答
private String difficulty; //难易程度： 1极易 2容易 3普通 4困难 5极难

private String isClassic; //是否经典面试题 0：否 1：是
private String state; //题目状态 0：不可用 1：可用（只有审核通过的题目才可以设置）

private String reviewStatus;//审核状态 -1 审核不通过 0 审核中 1 审核通过
private Date createTime;

private Company company;
private Catalog catalog;

// getter/setter 略
}

2、创建dao接口：QuestionDao

public interface QuestionDao {

int save(Question question);

int delete(Question question);

int update(Question question);

Question findById(String id);

List<Question> findAll();
}

3、创建业务层接口：QuestionService

public interface QuestionService {
/**
* 添加
* @param question
* @return
*/
void save(Question question);

/**
* 删除
* @param question
* @return
*/
void delete(Question question);

/**
* 修改
* @param question
* @return
*/
void update(Question question);

/**
* 查询单个
* @param id 查询的条件（id）
* @return 查询的结果，单个对象
*/
Question findById(String id);

/**
* 查询全部的数据
* @return 全部数据的列表对象
*/
List<Question> findAll();

/**
* 分页查询数据
* @param page 页码
* @param size 每页显示的数据总量
* @return
*/
PageInfo findAll(int page, int size);
}

4、创建业务层实现类：QuestionServiceImpl

public class QuestionServiceImpl implements QuestionService {
@Override
public void save(Question question) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionDao questionDao = MapperFactory.getMapper(sqlSession,QuestionDao.class);
//id使用UUID的生成策略来获取
String id = UUID.randomUUID().toString();
question.setId(id);

//3.调用Dao层操作
questionDao.save(question);
//4.提交事务
TransactionUtil.commit(sqlSession);
}catch (Exception e){
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public void delete(Question question) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionDao questionDao = MapperFactory.getMapper(sqlSession,QuestionDao.class);
//3.调用Dao层操作
questionDao.delete(question);
//4.提交事务
TransactionUtil.commit(sqlSession);
}catch (Exception e){
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public void update(Question question) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionDao questionDao = MapperFactory.getMapper(sqlSession,QuestionDao.class);
//3.调用Dao层操作
questionDao.update(question);
//4.提交事务
TransactionUtil.commit(sqlSession);
}catch (Exception e){
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public Question findById(String id) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionDao questionDao = MapperFactory.getMapper(sqlSession,QuestionDao.class);
//3.调用Dao层操作
return questionDao.findById(id);
}catch (Exception e){
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public List<Question> findAll() {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionDao questionDao = MapperFactory.getMapper(sqlSession,QuestionDao.class);
//3.调用Dao层操作
return questionDao.findAll();
}catch (Exception e){
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public PageInfo findAll(int page, int size) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionDao questionDao = MapperFactory.getMapper(sqlSession,QuestionDao.class);
//3.调用Dao层操作
PageHelper.startPage(page,size);
List<Question> all = questionDao.findAll();
PageInfo pageInfo = new PageInfo(all);
return pageInfo;
}catch (Exception e){
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}
}

5、创建servlet：QuestionServlet

// uri:/store/question?operation=list
@WebServlet("/store/question")
public class QuestionServlet extends BaseServlet {

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String operation = request.getParameter("operation");
if("list".equals(operation)){
this.list(request,response);
}else if("toAdd".equals(operation)){
this.toAdd(request,response);
}else if("save".equals(operation)){
this.save(request, response);
}else if("toEdit".equals(operation)){
this.toEdit(request,response);
}else if("edit".equals(operation)){
this.edit(request,response);
}else if("delete".equals(operation)){
this.delete(request,response);
}
}

private void list(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
//进入列表页
//获取数据
int page = 1;
int size = 5;
if(StringUtils.isNotBlank(request.getParameter("page"))){
page = Integer.parseInt(request.getParameter("page"));
}
if(StringUtils.isNotBlank(request.getParameter("size"))){
size = Integer.parseInt(request.getParameter("size"));
}
PageInfo all = questionService.findAll(page, size);
//将数据保存到指定的位置
request.setAttribute("page",all);
//跳转页面
request.getRequestDispatcher("/WEB-INF/pages/store/question/list.jsp").forward(request,response);
}

private void toAdd(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
//跳转页面
request.getRequestDispatcher("/WEB-INF/pages/store/question/add.jsp").forward(request,response);
}

private void save(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
//将数据获取到，封装成一个对象
Question question = BeanUtil.fillBean(request,Question.class,"yyyy-MM-dd");
//调用业务层接口save
questionService.save(question);
//跳转回到页面list
response.sendRedirect(request.getContextPath()+"/store/question?operation=list");
}

private void toEdit(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//查询要修改的数据findById
String id = request.getParameter("id");
Question question = questionService.findById(id);
//将数据加载到指定区域，供页面获取
request.setAttribute("question",question);
//跳转页面
request.getRequestDispatcher("/WEB-INF/pages/store/question/update.jsp").forward(request,response);
}

private void edit(HttpServletRequest request, HttpServletResponse response) throws IOException {
//将数据获取到，封装成一个对象
Question question = BeanUtil.fillBean(request,Question.class,"yyyy-MM-dd");
//调用业务层接口save
questionService.update(question);
//跳转回到页面list
//list(request,response);
response.sendRedirect(request.getContextPath()+"/store/question?operation=list");
}

private void delete(HttpServletRequest request, HttpServletResponse response) throws IOException {
//将数据获取到，封装成一个对象
Question question = BeanUtil.fillBean(request,Question.class);
//调用业务层接口save
questionService.delete(question);
//跳转回到页面list
response.sendRedirect(request.getContextPath()+"/store/question?operation=list");
}

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request,response);
}
}

6、修改BaserServlet，添加QuestionService

public class BaseServlet extends HttpServlet {
protected CompanyService companyService;
protected DeptService deptService;
protected UserService userService;
protected CourseService courseService;
protected CatalogService catalogService;
protected QuestionService questionService;

@Override
public void init() throws ServletException {
companyService = new CompanyServiceImpl();
deptService = new DeptServiceImpl();
userService = new UserServiceImpl();
courseService = new CourseServiceImpl();
catalogService = new CatalogServiceImpl();
questionService = new QuestionServiceImpl();
}
}

五、文件上传

1.测试文件上传

1、html上传代码

<form id="editForm" action="${ctx}/store/question?operation=testUpload" method="post" enctype="multipart/form-data">
<div class="tab-pane active" id="tab-form">
<div class="row data-type">

<div class="col-md-2 title">题干图片</div>
<div class="col-md-10 data ">
<input type="file" class="form-control" placeholder="题干图片" name="picture">
</div>

</div>
<!--工具栏-->
<div class="box-tools text-center">
<button type="button" onclick='document.getElementById("editForm").submit()' class="btn bg-maroon">保存</button>
<button type="button" class="btn bg-default" onclick="history.back(-1);">返回</button>
</div>
</div>
</form>

2、在QuestionServlet中添加toTestUpload方法，跳转到文件上传页面

@WebServlet("/store/question")
public class QuestionServlet extends BaseServlet {

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String operation = request.getParameter("operation");
if("list".equals(operation)){
this.list(request,response);
}else if("toAdd".equals(operation)){
this.toAdd(request,response);
}else if("save".equals(operation)){
this.save(request, response);
}else if("toEdit".equals(operation)){
this.toEdit(request,response);
}else if("edit".equals(operation)){
this.edit(request,response);
}else if("delete".equals(operation)){
this.delete(request,response);
}else if("toTestUpload".equals(operation)){
this.toTestUpload(request,response);
}else if("testUpload".equals(operation)){
try {
this.testUpload(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}
}
private void toTestUpload(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
request.getRequestDispatcher("/WEB-INF/pages/store/question/testFileUpload.jsp").forward(request,response);
}

private void testUpload(HttpServletRequest request,HttpServletResponse response) throws Exception {

}
}

3、完成文件上传的后台代码

private void testUpload(HttpServletRequest request,HttpServletResponse response) throws Exception {
//1.确认该操作是否支持文件上传操作，enctype="multipart/form-data"
if(ServletFileUpload.isMultipartContent(request)){
//2.创建磁盘工厂对象
DiskFileItemFactory factory = new DiskFileItemFactory();
//3.Servlet文件上传核心对象
ServletFileUpload fileUpload = new ServletFileUpload(factory);
//4.从request中读取数据
List<FileItem> fileItems = fileUpload.parseRequest(request);

for(FileItem item : fileItems){
//5.当前表单是否是文件表单
if(!item.isFormField()){
//6.从临时存储文件的地方将内容写入到指定位置
item.write(new File(this.getServletContext().getRealPath("upload"),item.getName()));
}
}
}
}

2.添加题目时加入文件上传

（1）在题目实体中添加图片的属性

public class Question {
// 其他属性略
private String picture;

public String getPicture() {
return picture;
}
public void setPicture(String picture) {
this.picture = picture;
}
}

（2）在对应的dao映射配置文件中添加图片字段的配置，在resultMap，查询的sql片段，保存，更新几个位置添加映射配置即可，更新的时候是不需要更改图片的名称，因此是去掉对图片名称的更新

<!--配置实体类属性和数据库表中列的对应关系-->
<resultMap id="BaseResultMap" type="com.itheima.domain.store.Question">
<id column="id" jdbcType="VARCHAR" property="id"/>
<result column="company_id" jdbcType="VARCHAR" property="companyId"/>
<result column="catalog_id" jdbcType="VARCHAR" property="catalogId"/>
<result column="remark" jdbcType="VARCHAR" property="remark"/>
<result column="subject" jdbcType="VARCHAR" property="subject"/>
<result column="analysis" jdbcType="VARCHAR" property="analysis"/>
<result column="type" jdbcType="VARCHAR" property="type"/>
<result column="difficulty" jdbcType="VARCHAR" property="difficulty"/>
<result column="is_classic" jdbcType="VARCHAR" property="isClassic"/>
<result column="state" jdbcType="VARCHAR" property="state"/>
<result column="review_status" jdbcType="VARCHAR" property="reviewStatus"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="picture" jdbcType="VARCHAR" property="picture"/>
<association
property="company"
column="company_id"
javaType="com.itheima.domain.store.Course"
select="com.itheima.dao.store.CompanyDao.findById"/>
<association
property="catalog"
column="catalog_id"
javaType="com.itheima.domain.store.Course"
select="com.itheima.dao.store.CatalogDao.findById"
/>
</resultMap>
<!--配置查询的列名公共SQL语句-->
<sql id="Base_Column_List">
id, catalog_id, company_id, remark,subject,analysis,type, difficulty, is_classic,
state, review_status, create_time, picture
</sql>
<!--配置全字段插入，当某个字段没有值时，插入null-->
<insert id="save" parameterType="com.itheima.domain.store.Question">
insert into st_question(id, company_id, catalog_id, remark, subject, analysis, type,
difficulty, is_classic, state, review_status, create_time ,picture )
values (#{id,jdbcType=VARCHAR}, #{companyId,jdbcType=VARCHAR}, #{catalogId,jdbcType=VARCHAR},
#{remark,jdbcType=VARCHAR}, #{subject,jdbcType=VARCHAR}, #{analysis,jdbcType=VARCHAR},
#{type,jdbcType=VARCHAR}, #{difficulty,jdbcType=VARCHAR}, #{isClassic,jdbcType=VARCHAR},
#{state,jdbcType=VARCHAR}, #{reviewStatus,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP},
#{picture,jdbcType=VARCHAR} )
</insert>
<!--配置全字段更新，当提供的数据为null时，数据库数据会被更新为null-->
<update id="update" parameterType="com.itheima.domain.store.Question">
update
st_question
set
company_id = #{companyId,jdbcType=VARCHAR},
catalog_id = #{catalogId,jdbcType=VARCHAR},
remark = #{remark,jdbcType=VARCHAR},
subject = #{subject,jdbcType=VARCHAR},
analysis = #{analysis,jdbcType=VARCHAR},
difficulty = #{difficulty,jdbcType=VARCHAR},
is_classic = #{isClassic,jdbcType=VARCHAR},
state = #{state,jdbcType=VARCHAR}
where
id = #{id,jdbcType=VARCHAR}
</update>

（3）在question模块的添加页面add.jsp中加入图片上传的表单项

<form id="editForm" action="${ctx}/store/question?operation=save" method="post" enctype="multipart/form-data">
<!--其他元素略-->

<div class="col-md-2 title">题干图片</div><!--放到题干后面-->
<div class="col-md-10 data ">
<input type="file" class="form-control" placeholder="题干图片" name="picture">
</div>

</form>

（4）在servlet中修改保存题目的方法save，首先要更改的就是接收数据的方式，我们要按照文件上传的形式来接收

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String operation = request.getParameter("operation");
if("list".equals(operation)){
this.list(request,response);
}else if("toAdd".equals(operation)){
this.toAdd(request,response);
}else if("save".equals(operation)){
try {
this.save(request, response);
} catch (Exception e) {
e.printStackTrace();
}
}else if("toEdit".equals(operation)){
this.toEdit(request,response);
}else if("edit".equals(operation)){
this.edit(request,response);
}else if("delete".equals(operation)){
this.delete(request,response);
}else if("toTestUpload".equals(operation)){
this.toTestUpload(request,response);
}else if("testUpload".equals(operation)){
try {
this.testUpload(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}
}

private void save(HttpServletRequest request,HttpServletResponse response) throws Exception {
//1.确认该操作是否支持文件上传操作，enctype="multipart/form-data"
if(ServletFileUpload.isMultipartContent(request)){
//2.创建磁盘工厂对象
DiskFileItemFactory factory = new DiskFileItemFactory();
//3.Servlet文件上传核心对象
ServletFileUpload fileUpload = new ServletFileUpload(factory);
//4.从request中读取数据
List<FileItem> fileItems = fileUpload.parseRequest(request);
// --处理form表单提交过来的普通数据
//将数据获取到，封装成一个对象
Question question = BeanUtil.fillBean(fileItems,Question.class);
//调用业务层接口save
questionService.save(question);

// --处理form表单提交过来的文件数据
for(FileItem item : fileItems){
//5.当前表单是否是文件表单
if(!item.isFormField()){
//6.从临时存储文件的地方将内容写入到指定位置
item.write(new File(this.getServletContext().getRealPath("upload"),item.getName()));
}
}
}
//跳转回到页面list
response.sendRedirect(request.getContextPath()+"/store/question?operation=list");
}

六、题目选项模块

1、创建题目选项实体：QuestionItem

public class QuestionItem {
private String id; //ID
private String questionId; //题目ID
private String content; //选项内容
private String picture; //选项图片
private String isRight; //是否正确答案

@Override
public String toString() {
return "QuestionItem{" +
"id='" + id + '\'' +
", questionId='" + questionId + '\'' +
", content='" + content + '\'' +
", picture='" + picture + '\'' +
", isRight='" + isRight + '\'' +
'}';
}

// getter/setter方法略
}

2、创建dao接口：QuestionItemDao

public interface QuestionItemDao {
int save(QuestionItem questionItem);

int delete(QuestionItem questionItem);

int update(QuestionItem questionItem);

QuestionItem findById(String id);

List<QuestionItem> findAll();
}

3、创建业务层接口：QuestionItemService

public interface QuestionItemService {

/**
* 添加
* @param questionItem
* @return
*/
void save(QuestionItem questionItem);

/**
* 删除
* @param questionItem
* @return
*/
void delete(QuestionItem questionItem);

/**
* 修改
* @param questionItem
* @return
*/
void update(QuestionItem questionItem);

/**
* 查询单个
* @param id 查询的条件（id）
* @return 查询的结果，单个对象
*/
QuestionItem findById(String id);

/**
* 查询全部的数据
* @return 全部数据的列表对象
*/
List<QuestionItem> findAll();

/**
* 分页查询数据
* @param page 页码
* @param size 每页显示的数据总量
* @return
*/
PageInfo findAll(int page, int size);
}

4、创建业务层实现类：QuestionItemServiceImpl

我们基于CompanyServiceImpl拷贝然后改造

public class QuestionItemServiceImpl implements QuestionItemService {
@Override
public void save(QuestionItem questionItem) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionItemDao questionItemDao = MapperFactory.getMapper(sqlSession,QuestionItemDao.class);
//id使用UUID的生成策略来获取
String id = UUID.randomUUID().toString();
questionItem.setId(id);
//3.调用Dao层操作
questionItemDao.save(questionItem);
//4.提交事务
TransactionUtil.commit(sqlSession);
}catch (Exception e){
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public void delete(QuestionItem questionItem) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionItemDao questionItemDao = MapperFactory.getMapper(sqlSession,QuestionItemDao.class);
//3.调用Dao层操作
questionItemDao.delete(questionItem);
//4.提交事务
TransactionUtil.commit(sqlSession);
}catch (Exception e){
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public void update(QuestionItem questionItem) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionItemDao questionItemDao = MapperFactory.getMapper(sqlSession,QuestionItemDao.class);
//3.调用Dao层操作
questionItemDao.update(questionItem);
//4.提交事务
TransactionUtil.commit(sqlSession);
}catch (Exception e){
TransactionUtil.rollback(sqlSession);
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public QuestionItem findById(String id) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionItemDao questionItemDao = MapperFactory.getMapper(sqlSession,QuestionItemDao.class);
//3.调用Dao层操作
return questionItemDao.findById(id);
}catch (Exception e){
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public List<QuestionItem> findAll() {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionItemDao questionItemDao = MapperFactory.getMapper(sqlSession,QuestionItemDao.class);
//3.调用Dao层操作
return questionItemDao.findAll();
}catch (Exception e){
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}

@Override
public PageInfo findAll(int page, int size) {
SqlSession sqlSession = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionItemDao questionItemDao = MapperFactory.getMapper(sqlSession,QuestionItemDao.class);
//3.调用Dao层操作
PageHelper.startPage(page,size);
List<QuestionItem> all = questionItemDao.findAll();
PageInfo pageInfo = new PageInfo(all);
return pageInfo;
}catch (Exception e){
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
}
}