数据库:questions.sql
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50549
Source Host : localhost:3306
Source Schema : questions
Target Server Type : MySQL
Target Server Version : 50549
File Encoding : 65001
Date: 14/09/2020 16:22:34
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for answers
-- ----------------------------
DROP TABLE IF EXISTS `answers`;
CREATE TABLE `answers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ansContent` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`ansDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`qid` int(11) NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 59 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of answers
-- ----------------------------
INSERT INTO `answers` VALUES (1, '1', '2020-06-29 15:38:53', 1);
INSERT INTO `answers` VALUES (2, '1', '2020-09-04 15:59:32', 2);
INSERT INTO `answers` VALUES (3, '1', '2020-09-04 15:59:37', 1);
INSERT INTO `answers` VALUES (4, '1', '2020-09-04 15:59:40', 1);
INSERT INTO `answers` VALUES (6, '1111', '2020-09-04 16:03:23', 2);
INSERT INTO `answers` VALUES (7, '驱蚊器无无', '2020-09-04 16:03:28', 3);
INSERT INTO `answers` VALUES (8, '武切维奇无群', '2020-09-04 16:03:34', 4);
INSERT INTO `answers` VALUES (9, '哈哈哈哈', '2020-09-04 16:24:20', 3);
INSERT INTO `answers` VALUES (10, '挨打的', '2020-09-04 16:24:26', 3);
INSERT INTO `answers` VALUES (11, '其味无穷无群', '2020-09-04 16:26:19', 3);
INSERT INTO `answers` VALUES (12, '武切维奇', '2020-09-04 16:26:26', 3);
INSERT INTO `answers` VALUES (13, '1', '2020-09-04 16:29:06', 3);
INSERT INTO `answers` VALUES (14, '2', '2020-09-04 16:29:10', 2);
INSERT INTO `answers` VALUES (15, '1', '2020-09-04 16:29:56', 4);
INSERT INTO `answers` VALUES (16, '2', '2020-09-04 16:30:02', 4);
INSERT INTO `answers` VALUES (17, '2', '2020-09-04 16:30:05', 4);
INSERT INTO `answers` VALUES (18, '2', '2020-09-04 16:30:10', 4);
INSERT INTO `answers` VALUES (19, '1', '2020-09-04 16:30:41', 3);
INSERT INTO `answers` VALUES (20, '2', '2020-09-04 16:30:45', 3);
INSERT INTO `answers` VALUES (21, '11', '2020-09-04 16:32:06', 2);
INSERT INTO `answers` VALUES (22, '1', '2020-09-04 16:39:32', 1);
INSERT INTO `answers` VALUES (23, '2', '2020-09-04 16:39:35', 1);
INSERT INTO `answers` VALUES (24, '1', '2020-09-04 16:39:45', 1);
INSERT INTO `answers` VALUES (25, '2', '2020-09-04 16:41:20', 4);
INSERT INTO `answers` VALUES (26, '12', '2020-09-16 16:48:11', 1);
INSERT INTO `answers` VALUES (27, 'assasa', '2020-09-04 16:49:06', 3);
INSERT INTO `answers` VALUES (28, '122', '2020-09-04 16:54:13', 1);
INSERT INTO `answers` VALUES (29, '111', '2020-09-04 17:06:08', 3);
INSERT INTO `answers` VALUES (30, '按时撒撒', '2020-09-04 17:07:50', 3);
INSERT INTO `answers` VALUES (31, '122', '2020-09-04 17:09:31', 4);
INSERT INTO `answers` VALUES (32, '1', '2020-09-04 17:12:08', 3);
INSERT INTO `answers` VALUES (33, '11', '2020-09-04 17:12:23', 3);
INSERT INTO `answers` VALUES (34, '11', '2020-09-04 17:24:44', 1);
INSERT INTO `answers` VALUES (35, '111', '2020-09-04 17:24:49', 6);
INSERT INTO `answers` VALUES (36, '2121', '2020-09-05 14:16:53', 6);
INSERT INTO `answers` VALUES (37, '1212', '2020-09-05 14:17:41', 9);
INSERT INTO `answers` VALUES (38, '', '2020-09-06 15:37:28', 9);
INSERT INTO `answers` VALUES (39, '', '2020-09-06 15:37:46', 9);
INSERT INTO `answers` VALUES (40, '1', '2020-09-06 15:41:42', 9);
INSERT INTO `answers` VALUES (41, '1', '2020-09-06 15:55:58', 9);
INSERT INTO `answers` VALUES (42, '21212121', '2020-09-06 15:58:34', 5);
INSERT INTO `answers` VALUES (43, '11', '2020-09-06 15:59:06', 4);
INSERT INTO `answers` VALUES (44, '11111', '2020-09-06 15:59:20', 4);
INSERT INTO `answers` VALUES (45, 'kkk', '2020-09-06 19:52:56', 4);
INSERT INTO `answers` VALUES (46, 'kkk', '2020-09-06 19:53:53', 4);
INSERT INTO `answers` VALUES (47, '2121', '2020-09-06 19:59:15', 6);
INSERT INTO `answers` VALUES (48, '2121', '2020-09-06 19:59:15', 6);
INSERT INTO `answers` VALUES (49, '111', '2020-09-06 20:02:47', 9);
INSERT INTO `answers` VALUES (50, '111', '2020-09-06 20:12:43', 9);
INSERT INTO `answers` VALUES (51, '111', '2020-09-06 20:12:43', 9);
INSERT INTO `answers` VALUES (52, '111', '2020-09-06 20:16:28', 9);
INSERT INTO `answers` VALUES (53, '111', '2020-09-06 20:18:00', 9);
INSERT INTO `answers` VALUES (54, '11', '2020-09-06 20:18:52', 6);
INSERT INTO `answers` VALUES (55, '11', '2020-09-06 20:22:12', 6);
INSERT INTO `answers` VALUES (56, '11', '2020-09-06 20:22:12', 6);
INSERT INTO `answers` VALUES (57, '11', '2020-09-06 20:23:03', 6);
INSERT INTO `answers` VALUES (58, '11', '2020-09-06 20:28:03', 6);
-- ----------------------------
-- Table structure for questions
-- ----------------------------
DROP TABLE IF EXISTS `questions`;
CREATE TABLE `questions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`detailDesc` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`answerCount` int(100) NOT NULL,
`lastModified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of questions
-- ----------------------------
INSERT INTO `questions` VALUES (1, '1', '1', 7, '2020-09-04 17:24:44');
INSERT INTO `questions` VALUES (2, '2', '2', 8, '2020-09-04 16:32:06');
INSERT INTO `questions` VALUES (3, '55555555', NULL, 16, '2020-09-04 17:12:23');
INSERT INTO `questions` VALUES (4, '111', '222', 13, '2020-09-06 19:53:53');
INSERT INTO `questions` VALUES (5, '222', '3333', 4, '2020-09-06 15:58:34');
INSERT INTO `questions` VALUES (6, '333333333333333', '12', 12, '2020-09-06 20:28:03');
INSERT INTO `questions` VALUES (9, '11', '11', 10, '2020-09-06 20:18:00');
-- ----------------------------
-- Triggers structure for table answers
-- ----------------------------
DROP TRIGGER IF EXISTS `ins`;
delimiter ;;
CREATE TRIGGER `ins` AFTER INSERT ON `answers` FOR EACH ROW update questions set answerCount = answerCount +1 where id = new.qid
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
相关依赖:pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.xxx</groupId>
<artifactId>questions</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<Spring.version>5.1.15.RELEASE</Spring.version>
</properties>
<packaging>war</packaging>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${Spring.version}</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.9.8</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${Spring.version}</version>
</dependency>
<!-- aspectJ AOP 织入器 -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.9</version>
</dependency>
<!--mybatis-Spring适配器 -->
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.15</version>
</dependency>
<!--Spring java数据库访问包,在本例中主要用于提供数据源 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${Spring.version}</version>
</dependency>
<!--MySQL数据库驱动 -->
<dependency>
<groupId>MySQL</groupId>
<artifactId>MySQL-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<!--log4j日志包 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- mybatis ORM框架 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- JUnit单元测试工具 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.3</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>redis.clients</groupId>
<artifactId>jedis</artifactId>
<version>2.9.0</version>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-redis</artifactId>
<version>1.8.11.RELEASE</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-simple</artifactId>
<version>1.7.5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
</dependencies>
<build>
<plugins>
<!-- tomcat插件控制 -->
<plugin>
<groupId>org.apache.tomcat.maven</groupId>
<artifactId>tomcat7-maven-plugin</artifactId>
<version>2.2</version>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.1</version>
<configuration>
<source>1.7</source>
<target>1.7</target>
<encoding>utf-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
</project>
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<!-- 监听器 -->
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!-- 当容器启动时,加载spring配置文件,springioc容器启动,mybatis启动 -->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:applicationContext.xml</param-value>
</context-param>
<!-- 字符编码过滤器 -->
<filter>
<filter-name>CharacterEncodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<!-- 指定字符编码 -->
<init-param>
<param-name>encoding</param-name>
<param-value>utf-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>CharacterEncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- 配置前端控制器 DIsPatcherServlet -->
<servlet>
<servlet-name>springmvc</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<!-- 指定springmvc.xml配置文件的位置 -->
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:springmvc.xml</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>springmvc</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
</web-app>
QuestionsController.java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
import java.util.List;
@Controller
public class QuestionsController {
@Autowired
private QuestionService questionService;
@Autowired
private AnswersService answersService;
@RequestMapping("find")
public ModelAndView find(ModelAndView modelAndView){
List<Questions> list = questionService.find();
modelAndView.addObject("list",list);
modelAndView.setViewName("index");
return modelAndView;
}
@RequestMapping("addQuestions")
public String addQuestions(Questions questions){
boolean r = questionService.addQuestions(questions);
return "redirect:/find";
}
@RequestMapping("findById")
public ModelAndView findById(int id,ModelAndView modelAndView){
Questions questions = questionService.findById(id);
modelAndView.addObject("questions",questions);
modelAndView.setViewName("detail");
return modelAndView;
}
@RequestMapping("addAnswers")
public String addAnswers(Answers answers,int qid){
boolean r = answersService.addAnsers(answers);
return "redirect:findById?id="+qid;
}
}
Answers.java
import java.io.Serializable;
import java.util.Date;
public class Answers implements Serializable {
private Integer id;
private String ansContent;
private Date ansDate;
private Integer qid;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getAnsContent() {
return ansContent;
}
public void setAnsContent(String ansContent) {
this.ansContent = ansContent == null ? null : ansContent.trim();
}
public Date getAnsDate() {
return ansDate;
}
public void setAnsDate(Date ansDate) {
this.ansDate = ansDate;
}
public Integer getQid() {
return qid;
}
public void setQid(Integer qid) {
this.qid = qid;
}
}
Questions.java
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class Questions implements Serializable {
private Integer id;
private String title;
private String detailDesc;
private Integer answerCount;
private Date lastModified;
private List<Answers> answersList;
public List<Answers> getAnswersList() {
return answersList;
}
public void setAnswersList(List<Answers> answersList) {
this.answersList = answersList;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title == null ? null : title.trim();
}
public String getDetailDesc() {
return detailDesc;
}
public void setDetailDesc(String detailDesc) {
this.detailDesc = detailDesc == null ? null : detailDesc.trim();
}
public Integer getAnswerCount() {
return answerCount;
}
public void setAnswerCount(Integer answerCount) {
this.answerCount = answerCount;
}
public Date getLastModified() {
return lastModified;
}
public void setLastModified(Date lastModified) {
this.lastModified = lastModified;
}
AnswersMapper.java
public interface AnswersMapper {
int deleteByPrimaryKey(Integer id);
int insert(Answers record);
int insertSelective(Answers record);
Answers selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(Answers record);
int updateByPrimaryKey(Answers record);
}
QuestionsMapper.java
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface QuestionsMapper {
int deleteByPrimaryKey(Integer id);
int insert(Questions record);
int insertSelective(Questions record);
Questions selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(Questions record);
int updateByPrimaryKey(Questions record);
@Select("select * from questions")
List<Questions> find();
}
AnswersService.java
public interface AnswersService {
public boolean addAnsers(Answers answers);
}
AnswersServiceImpl.java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Caching;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
@Transactional
public class AnswersServiceImpl implements AnswersService {
@Autowired
private AnswersMapper answersMapper;
@Override
@Caching(evict = {
@CacheEvict(value = "findById",key = "'findById'+#answers.qid"),
@CacheEvict(value = "findQuestions",key = "'findQuestions'")
})
public boolean addAnsers(Answers answers) {
return answersMapper.insertSelective(answers)>0;
}
}
QuestionService.java
import java.util.List;
public interface QuestionService {
List<Questions> find();
boolean addQuestions(Questions questions);
Questions findById(int id);
}
QuestionServiceImpl.java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
@Transactional
public class QuestionServiceImpl implements QuestionService {
@Autowired
private QuestionsMapper questionsMapper;
@Override
@Cacheable(value = "findQuestions",key = "'findQuestions'")
public List<Questions> find() {
return questionsMapper.find();
}
@Override
@CacheEvict(value = "findQuestions",key = "'findQuestions'")
public boolean addQuestions(Questions questions) {
return questionsMapper.insertSelective(questions)>0;
}
@Override
@Cacheable(value = "findById",key = "'findById'+#id")
public Questions findById(int id) {
return questionsMapper.selectByPrimaryKey(id);
}
}
AnswersMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xx.mapper.AnswersMapper" >
<resultMap id="BaseResultMap" type="com.xx.domain.Answers" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="ansContent" property="ansContent" jdbcType="VARCHAR" />
<result column="ansDate" property="ansDate" jdbcType="TIMESTAMP" />
<result column="qid" property="qid" jdbcType="INTEGER" />
</resultMap>
<sql id="Base_Column_List" >
id, ansContent, ansDate, qid
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from answers
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from answers
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.xx.domain.Answers" >
insert into answers (id, ansContent, ansDate,
qid)
values (#{id,jdbcType=INTEGER}, #{ansContent,jdbcType=VARCHAR}, #{ansDate,jdbcType=TIMESTAMP},
#{qid,jdbcType=INTEGER})
</insert>
<insert id="insertSelective" parameterType="com.xx.domain.Answers" >
insert into answers
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="ansContent != null" >
ansContent,
</if>
ansDate,
<if test="qid != null" >
qid,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="ansContent != null" >
#{ansContent,jdbcType=VARCHAR},
</if>
now(),
<if test="qid != null" >
#{qid,jdbcType=INTEGER},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.xx.domain.Answers" >
update answers
<set >
<if test="ansContent != null" >
ansContent = #{ansContent,jdbcType=VARCHAR},
</if>
<if test="ansDate != null" >
ansDate = #{ansDate,jdbcType=TIMESTAMP},
</if>
<if test="qid != null" >
qid = #{qid,jdbcType=INTEGER},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.xx.domain.Answers" >
update answers
set ansContent = #{ansContent,jdbcType=VARCHAR},
ansDate = #{ansDate,jdbcType=TIMESTAMP},
qid = #{qid,jdbcType=INTEGER}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
QuestionsMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xx.mapper.QuestionsMapper" >
<resultMap id="BaseResultMap" type="com.xx.domain.Questions" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="title" property="title" jdbcType="VARCHAR" />
<result column="detailDesc" property="detailDesc" jdbcType="VARCHAR" />
<result column="answerCount" property="answerCount" jdbcType="INTEGER" />
<result column="lastModified" property="lastModified" jdbcType="TIMESTAMP" />
<collection property="answersList" ofType="answers">
<result column="ansContent" property="ansContent" />
<result column="ansDate" property="ansDate" />
</collection>
</resultMap>
<sql id="Base_Column_List" >
id, title, detailDesc, answerCount, lastModified
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select q.*,ansContent,ansDate
from questions q LEFT JOIN answers a
on q.id = a.qid
where q.id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from questions
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.xx.domain.Questions" >
insert into questions (id, title, detailDesc,
answerCount, lastModified)
values (#{id,jdbcType=INTEGER}, #{title,jdbcType=VARCHAR}, #{detailDesc,jdbcType=VARCHAR},
#{answerCount,jdbcType=INTEGER}, #{lastModified,jdbcType=TIMESTAMP})
</insert>
<insert id="insertSelective" parameterType="com.xx.domain.Questions" >
insert into questions
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="title != null" >
title,
</if>
<if test="detailDesc != null" >
detailDesc,
</if>
<if test="answerCount != null" >
answerCount,
</if>
lastModified
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="title != null" >
#{title,jdbcType=VARCHAR},
</if>
<if test="detailDesc != null" >
#{detailDesc,jdbcType=VARCHAR},
</if>
<if test="answerCount != null" >
#{answerCount,jdbcType=INTEGER},
</if>
now()
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.xx.domain.Questions" >
update questions
<set >
<if test="title != null" >
title = #{title,jdbcType=VARCHAR},
</if>
<if test="detailDesc != null" >
detailDesc = #{detailDesc,jdbcType=VARCHAR},
</if>
<if test="answerCount != null" >
answerCount = #{answerCount,jdbcType=INTEGER},
</if>
<if test="lastModified != null" >
lastModified = #{lastModified,jdbcType=TIMESTAMP},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.xx.domain.Questions" >
update questions
set title = #{title,jdbcType=VARCHAR},
detailDesc = #{detailDesc,jdbcType=VARCHAR},
answerCount = #{answerCount,jdbcType=INTEGER},
lastModified = #{lastModified,jdbcType=TIMESTAMP}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:util="http://www.springframework.org/schema/util"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xmlns:cache="http://www.springframework.org/schema/cache"
xsi:schemaLocation="
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/jdbc
http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd
http://www.springframework.org/schema/cache
http://www.springframework.org/schema/cache/spring-cache.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 注册cache缓存
开启spring使用注解管理redis
-->
<cache:annotation-driven />
<!-- 配置 JedisPoolConfig 实例 -->
<bean id="poolConfig" class="redis.clients.jedis.JedisPoolConfig">
<property name="maxIdle" value="2"/>
<property name="maxTotal" value="10"/>
<property name="maxWaitMillis" value="3000"/>
<property name="testOnBorrow" value="true"/>
</bean>
<!-- 配置JedisConnectionFactory -->
<bean id="jedisConnectionFactory" class="org.springframework.data.redis.connection.jedis.JedisConnectionFactory">
<property name="hostName" value="192.168.130.128"/>
<property name="port" value="6379"/>
<property name="password" value="123456"/>
<property name="database" value="0"/>
<property name="poolConfig" ref="poolConfig"/>
</bean>
<!-- 配置RedisTemplate -->
<bean id="redisTemplate" class="org.springframework.data.redis.core.RedisTemplate">
<property name="connectionFactory" ref="jedisConnectionFactory"/>
</bean>
<!-- 配置RedisCacheManager
把redis 理解成一个作业
这个作业每个时机需要做什么,实现什么功能都交给这个管家进行调度
-->
<bean id="cacheManager" class="org.springframework.data.redis.cache.RedisCacheManager">
<constructor-arg name="redisOperations" ref="redisTemplate"/>
<!-- key 统一的存活时间
单位是秒
-->
<property name="defaultExpiration" value="3000"/>
</bean>
<!-- 扫包-->
<context:component-scan base-package="com.xx">
<!-- 在spring扫描注解时,不扫描@Controller -->
<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"></context:exclude-filter>
</context:component-scan>
<!-- 加载读取jdbc.properties -->
<context:property-placeholder location="classpath:jdbc.properties" />
<!-- 配置数据源 druid-->
<bean class="com.alibaba.druid.pool.DruidDataSource" id="dataSource">
<property name="driverClassName" value="${jdbc.driver}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<!-- 整合mybatis 配置sqlsessionFacotrybean -->
<bean class="org.mybatis.spring.SqlSessionFactoryBean" id="sessionFactory">
<property name="dataSource" ref="dataSource"></property>
<!-- 别名 -->
<property name="typeAliasesPackage" value="com.xx.domain"></property>
<!-- 加载mybatis-cfg.xml -->
<property name="configLocation" value="classpath:mybatis-cfg.xml"></property>
<!-- 配置分页插件 -->
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<!-- 配置数据库方言 在遇到每个数据库特有的语法时,参考指定的数据库语法 比如mysql 特有limit -->
<value>helperDialect=MySQL</value>
</property>
</bean>
</array>
</property>
</bean>
<!-- 配置mapper接口扫描-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- mapper接口的位置 -->
<property name="basePackage" value="com.xx.mapper"></property>
<!-- 如果只配置了一个sqlSessionFactoryBean 以下配置可以不写 -->
<property name="sqlSessionFactoryBeanName" value="sessionFactory"></property>
</bean>
<!-- 配置事务管理平台 -->
<bean class="org.springframework.jdbc.datasource.DataSourceTransactionManager" id="transactionManager">
<!-- 注入数据源 -->
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 开启事务注解 -->
<tx:annotation-driven transaction-manager="transactionManager" />
</beans>
jdbc.properties
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql:///questions?useUnicode=true&characterEncoding=utf-8
jdbc.username = root
jdbc.password = 1234
log4j.properties
# Global logging configuration \u5F00\u53D1\u65F6\u5019\u5EFA\u8BAE\u4F7F\u7528 debug
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
mybatis-cfg.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!-- 设置mybatis使用Log4j作为日志工具 -->
<setting name="logImpl" value="LOG4J"/>
<!-- 开启mybatis二级缓存 -->
<setting name="cacheEnabled" value="true"/>
</settings>
</configuration>
springmvc.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd">
<context:component-scan base-package="com.xx.controller" />
<!-- 处理器映射器、处理器适配器、转换器jackson、拦截器、静态处理等功能 -->
<mvc:annotation-driven/>
<!-- 视图解析器 -->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/"></property>
<property name="suffix" value=".jsp"></property>
</bean>
<mvc:default-servlet-handler />
</beans>
add.jsp
<%--
Created by IntelliJ IDEA.
User: xiaohuihui
Date: 2020/9/7
Time: 11:21
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<form action="addQuestions" method="post">
<table border="1" cellpadding="0" cellspacing="0" width="500" align="center">
<input type="hidden" name="answerCount" value="0" />
<tr>
<th colspan="2"><h1>我要提问</h1></th>
</tr>
<tr>
<th colspan="2"><a href="find">返回首页</a></th>
</tr>
<tr>
<td>问题:</td>
<td><input name="title" type="text" /></td>
</tr>
<tr>
<td>问题描述:</td>
<td><textarea name="detailDesc"></textarea></td>
</tr>
<tr>
<td colspan="2"><input type="submit" /></td>
</tr>
</table>
</form>
</body>
</html>
detail.jsp
<%--
Created by IntelliJ IDEA.
User: xiaohuihui
Date: 2020/9/7
Time: 11:21
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<html>
<head>
<meta charset="UTF-8">
<title></title>
<style>
td:nth-of-type(2){
background-color: darkseagreen;
}
</style>
</head>
<body>
<form action="${pageContext.request.contextPath}/addAnswers" method="post">
<input type="hidden" name="qid" value="${questions.id}" />
<table border="1" cellpadding="0" cellspacing="0" width="300px" align="center">
<tr><th colspan="2"><a href="find">返回首页</a></th></tr>
<tr>
<td>问题:</td>
<td>${questions.title}</td>
</tr>
<tr>
<td>问题描述:</td>
<td>${questions.detailDesc}</td>
</tr>
<tr>
<td>网友回答:</td>
<td>
<c:forEach items="${questions.answersList}" var="answers">
<fmt:formatDate value="${answers.ansDate}" pattern="yyyy-MM-dd hh:mm:ss" /><br>
${answers.ansContent}<br>
</c:forEach>
</td>
</tr>
<tr>
<td>我来回答:</td>
<td><textarea name="ansContent"></textarea></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="回答" /></td>
</tr>
</table>
</form>
</body>
</html>
index.jsp
<%--
Created by IntelliJ IDEA.
User: xiaohuihui
Date: 2020/9/7
Time: 11:21
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<html>
<head>
<meta charset="utf-8" />
<title></title>
<style>
*{text-align: center;}
a{color: blue; text-decoration: none;}
</style>
</head>
<body>
<table width="800px" cellpadding="0" cellspacing="0" align="center" border="1">
<tr>
<th colspan="4"><h1>在线问答</h1></th>
</tr>
<tr>
<th colspan="4"><a href="add.jsp">我要提问</a></th>
</tr>
<tr>
<th>序号</th>
<th>问题</th>
<th>回答次数</th>
<th>最后修改</th>
</tr>
<c:forEach var="questions" items="${list}">
<tr>
<td>${questions.id}</td>
<td><a href="findById?id=${questions.id}">${questions.title}</a></td>
<td>${questions.answerCount}</td>
<td><fmt:formatDate value="${questions.lastModified}" pattern="yyyy-MM-dd hh:mm:ss" /></td>
</tr>
</c:forEach>
</table>
</body>
</html>