期末考试题解2

表二Report

点击查看代码

CREATE TABLE daily_report (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    publishdate DATE ,
    stuno VARCHAR(8) ,
    name VARCHAR(10) ,
    spenttime INT ,
    codeamount INT ,
    blogsnumber INT ,
    knowledgepoints VARCHAR(100),
    State VARCHAR(5),
    dailyscore INT,
    Statereason VARCHAR(100)
);

Report的pojo

点击查看代码

package com.QixunQiu.pojo;

public class Report {
    int ID ;
    String publishdate ;
    String stuno ;
    String name ;
    int spenttime ;
    int codeamount ;
    int blogsnumber ;
    String knowledgepoints ;
    String State ;
    int dailyscore ;
    String Statereason ;

    @Override
    public String toString() {
        return "Report{" +
                "ID=" + ID +
                ", publishdate='" + publishdate + '\'' +
                ", stuno='" + stuno + '\'' +
                ", name='" + name + '\'' +
                ", spenttime=" + spenttime +
                ", codeamount=" + codeamount +
                ", blogsnumber=" + blogsnumber +
                ", knowledgepoints='" + knowledgepoints + '\'' +
                ", State='" + State + '\'' +
                ", dailyscore=" + dailyscore +
                ", Statereason='" + Statereason + '\'' +
                '}';
    }

    public int getID() {
        return ID;
    }

    public void setID(int ID) {
        this.ID = ID;
    }

    public String getPublishdate() {
        return publishdate;
    }

    public void setPublishdate(String publishdate) {
        this.publishdate = publishdate;
    }

    public String getStuno() {
        return stuno;
    }

    public void setStuno(String stuno) {
        this.stuno = stuno;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getSpenttime() {
        return spenttime;
    }

    public void setSpenttime(int spenttime) {
        this.spenttime = spenttime;
    }

    public int getCodeamount() {
        return codeamount;
    }

    public void setCodeamount(int codeamount) {
        this.codeamount = codeamount;
    }

    public String getKnowledgepoints() {
        return knowledgepoints;
    }

    public void setKnowledgepoints(String knowledgepoints) {
        this.knowledgepoints = knowledgepoints;
    }

    public int getBlogsnumber() {
        return blogsnumber;
    }

    public void setBlogsnumber(int blogsnumber) {
        this.blogsnumber = blogsnumber;
    }

    public int getDailyscore() {
        return dailyscore;
    }

    public void setDailyscore(int dailyscore) {
        this.dailyscore = dailyscore;
    }

    public String getState() {
        return State;
    }

    public void setState(String state) {
        State = state;
    }

    public String getStatereason() {
        return Statereason;
    }

    public void setStatereason(String statereason) {
        Statereason = statereason;
    }
}

ReportMapper

点击查看代码

package com.QixunQiu.mapper;

import com.QixunQiu.pojo.Report;
import com.QixunQiu.pojo.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ReportMapper {
    void addReport(Report report);

    List<Report> selectAll();

    @Select("select * from daily_report where stuno = #{stuno} ")
    List<Report>  selectBystuno(@Param("stuno") String stuno);

    @Select("select * from daily_report where ID = #{ID} ")
    Report  selectByID(@Param("ID") int ID);

    @Select("select * from daily_report where ID = #{ID} ")
    List<Report>  selectByIDList(@Param("ID") int ID);

    int updateReport(Report report);

    int Fen(Report report);

    int checkReport(Report report);

    @Select("select * from daily_report where publishdate = #{publishdate} ")
    List<Report>  selectByDate(@Param("publishdate") String publishdate);

    void deleteReport(int ID);

    List<Report> selectByThree(Report report);
}

ReportMapper.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.QixunQiu.mapper.ReportMapper">

    <insert id="addReport">
        insert into daily_report (publishdate,
                                  stuno,
                                  name,
                                  spenttime,
                                  codeamount,
                                  blogsnumber,
                                  knowledgepoints,
                                  State)
        values (#{publishdate}, #{stuno}, #{name}, #{spenttime},#{codeamount},
                #{blogsnumber},#{knowledgepoints},#{State});
    </insert>
    <update id="updateReport">
        update daily_report
        <set>
            <if test="spenttime != null ">
                spenttime = #{spenttime},
            </if>
            <if test="codeamount != null ">
                codeamount = #{codeamount},
            </if>
            <if test="blogsnumber != null ">
                blogsnumber = #{blogsnumber},
            </if>
            <if test="knowledgepoints != null ">
                knowledgepoints = #{knowledgepoints},
            </if>
        </set>
        where ID=#{ID};
    </update>
    <update id="checkReport">
        update daily_report
        <set>
            <if test="State != null ">
                State = #{State},
            </if>
        </set>
        where ID=#{ID};
    </update>
    <update id="Fen">
        update daily_report
        <set>
            <if test="dailyscore != null ">
                dailyscore = #{dailyscore},
            </if>
            <if test="Statereason != null ">
                Statereason= #{Statereason},
            </if>
        </set>
        where ID=#{ID};
    </update>
    <delete id="deleteReport">
        delete from daily_report where ID=#{ID};
    </delete>
    <select id="selectAll" resultType="com.QixunQiu.pojo.Report">
        select * from daily_report;
    </select>

    <select id="selectByThree" resultType="com.QixunQiu.pojo.Report">
        select *
        from daily_report
        <where>
            <choose>
                <when test="stuno != null and stuno != ''">
                    stuno = #{stuno}
                </when>
                <when test="publishdate != null and publishdate != '' ">
                    publishdate like #{publishdate}
                </when>
                <when test="knowledgepoints != null and knowledgepoints != '' ">
                    knowledgepoints like #{knowledgepoints}
                </when>
            </choose>
        </where>
    </select>
</mapper>

ReportService

点击查看代码

package com.QixunQiu.service;

import com.QixunQiu.mapper.ReportMapper;
import com.QixunQiu.pojo.Report;
import com.QixunQiu.util.SqlSessionFactoryUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import java.util.List;

public class ReportService {
    SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtils.getSqlSessionFactory();
    public void add(Report report) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ReportMapper reportMapper = sqlSession.getMapper(ReportMapper.class);
        reportMapper.addReport(report);
        sqlSession.commit();
        sqlSession.close();
    }

    public void Fen(Report report) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ReportMapper reportMapper = sqlSession.getMapper(ReportMapper.class);
        reportMapper.Fen(report);
        sqlSession.commit();
        sqlSession.close();

    }

    public List<Report> selectAll() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ReportMapper reportMapper = sqlSession.getMapper(ReportMapper.class);
        List<Report> reports = reportMapper.selectAll();
        sqlSession.close();
        return reports;
    }

    public List<Report> selectBystuno(String stuno) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ReportMapper reportMapper = sqlSession.getMapper(ReportMapper.class);
        List<Report> reports = reportMapper.selectBystuno(stuno);
        sqlSession.close();
        return reports;
    }

    public Report selectID(int id) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ReportMapper reportMapper = sqlSession.getMapper(ReportMapper.class);
        Report reports = reportMapper.selectByID(id);
        sqlSession.close();
        return reports;
    }

    public List<Report> selectIDlist(int id) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ReportMapper reportMapper = sqlSession.getMapper(ReportMapper.class);
        List<Report> report= reportMapper.selectByIDList(id);
        sqlSession.close();
        return report;
    }

    public void update(Report report) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ReportMapper reportMapper = sqlSession.getMapper(ReportMapper.class);
        reportMapper.updateReport(report);
        sqlSession.commit();
        sqlSession.close();
    }

    public void check(Report report) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ReportMapper reportMapper = sqlSession.getMapper(ReportMapper.class);
        reportMapper.checkReport(report);
        sqlSession.commit();
        sqlSession.close();

    }

    public void delete(int id) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ReportMapper reportMapper = sqlSession.getMapper(ReportMapper.class);
        reportMapper.deleteReport(id);
        sqlSession.commit();
        sqlSession.close();
    }

    public List<Report> selectByThree(Report report) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ReportMapper reportMapper = sqlSession.getMapper(ReportMapper.class);
        List<Report> reports = reportMapper.selectByThree(report);
        sqlSession.close();
        return reports;
    }

    public List<Report> selectByDate(String date) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ReportMapper reportMapper = sqlSession.getMapper(ReportMapper.class);
        List<Report> reports = reportMapper.selectByDate(date);
        sqlSession.close();
        return reports;
    }
}