mybatis+spring+Druid连接池+Vue+ElementUI简单实现学生系统的增删改查+模糊查询+分页

mybatis+spring+Druid连接池+Vue+ElementUI简单实现学生系统的增删改查+模糊查询+分页

1-resources配置目录

1.1 mapper目录

1.1.1.1 StudentMapper.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.zelin.mapper.StudentMapper">
    <!--1-查询所有学生-->
    <select id="findAll" resultType="student">
    select st.*,cname from student st,classes c where c.cid = st.cid
  </select>
    <!--2-自动插入学生-->
    <insert id="insert" parameterType="student" useGeneratedKeys="true" keyProperty="sid">
        insert into student values (null,#{sname},#{sex},#{age},#{addr},#{cid})
    </insert>
    <!--3-查询指定Id的学生-->
    <select id="findById" resultType="student">
    select st.*,cname from student st,classes c where c.cid = st.cid and sid=#{value}
  </select>
    <!--4-修改学生-->
    <update id="update" parameterType="student">
        update student set sname=#{sname},sex=#{sex},age=#{age},addr=#{addr},cid=#{cid} where sid=#{sid}
    </update>
    <!--5-根据Id删除指定的学生-->
    <delete id="deleteById" parameterType="int">
        delete from student where sid=#{value}
    </delete>
    <!--6-条件查询(方式一：使用#方式进行条件查询)-->
    <select id="findByStu" parameterType="student" resultType="student">
        select st.*,cname from student st,classes c where c.cid = st.cid and sname like #{sname} and addr like #{addr}
    </select>
    <!--7-条件查询(方式二：使用$方式进行条件查询)-->
    <select id="findByStu2" parameterType="student" resultType="student">
        select st.*,cname from student st,classes c where c.cid = st.cid and sname like "%${sname}%" and addr like "%${addr}%"
    </select>

    <!--8.动态SQL查询：关键字查询学生-->
    <select id="searchByWords" resultType="student" parameterType="student">
        select st.*,cname from student st,classes c
        <where>
            st.cid = c.cid
            <if test="sname !=null and sname!=''">
                and sname like '%${sname}%'
            </if>
            <if test="addr !=null and addr!=''">
                and addr like '%${addr}%'
            </if>
        </where>
    </select>

    <!--9-分页查询：查询所有的学生总数-->
    <select id="findByCount" resultType="long">
        select count(*) from student st,classes c where st.cid = c.cid
    </select>
    <!--10-分页查询：查询指定页 指定每页大小下的学生集合-->
    <select id ="findByPage" parameterType="pageVo" resultType="student">
        select st.*,cname from student st,classes c where c.cid=st.cid limit #{page},#{pageSize}
    </select>

</mapper>

1.1.1.2 ClassesMapper.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.zelin.mapper.ClassesMapper">
    <!--1-查询所有班级-->
    <select id="findAll" resultType="classes">
    select * from classes
  </select>

</mapper>

1.2 spring目录

1.2.1 applicationContext.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"

       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       https://www.springframework.org/schema/context/spring-context.xsd">
    <!--1-导入属性文件-->
    <context:property-placeholder location="classpath*:properties/db.properties"/>
    <!--2-配置扫描包-->
    <context:component-scan base-package="com.zelin"/>

    <!--3-配置数据源-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.user}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    <!--4-配置sqlSessionBean对象-->
    <bean class="org.mybatis.spring.SqlSessionFactoryBean" id="sqlSessionFactoryBean">
        <!--4.1)配置数据源-->
        <property name="dataSource" ref="dataSource"/>
        <!--4.2)配置别名包-->
        <property name="typeAliasesPackage" value="com.zelin.pojo"/>
        <!--4.3)引入mapper映射文件所在的位置-->
        <property name="mapperLocations" value="classpath*:mapper/*.xml"/>
    </bean>
    <!--5.使用包扫描创建代理对象-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer" >
        <property name="basePackage" value="com.zelin.mapper"/>
    </bean>

</beans>

1.3 properties属性目录下

1.3.1 db.properties属性文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///chrisfeng
jdbc.user=root
jdbc.password=123456

2-test目录 方便测试 之后再去servlet目录下进行修改

package com.zelin.test;


import com.zelin.pojo.Classes;
import com.zelin.pojo.PageResult;
import com.zelin.pojo.PageVo;
import com.zelin.pojo.Student;
import com.zelin.service.ClassesService;
import com.zelin.service.StudentService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

/**
 * @author wf
 * @date 2020-10-22 17:04
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath*:spring/applicationContext.xml")
public class TestSpringMybatis {
   
    @Autowired
    private StudentService studentService;
    @Autowired
    private ClassesService classesService;
    //1.查询所有学生
    @Test
    public void test01(){
   
        //1.1)查询学生列表
        List<Student> students = studentService.findAll();
        //1.2)遍历学生
        for (Student student : students) {
   
            System.out.println(student);
        }
    }
    //2.增加学生
    @Test
    public void test02(){
   
        Student student = new Student("梅兰芳","女",56,"香港",2);
        studentService.insert(student);
        //打印插入后的学生是否有sid这个属性
        System.out.println(student);
    }
    //3.查询指定ID的学生
    @Test
    public void test03(){
   
        Student student = studentService.findById(30);
        System.out.println(student);
    }
    //4.修改指定Id的学生
    @Test
    public void test04(){
   
        //4.1)先查询指定Id的学生
        Student student = studentService.findById(31);
        //4.2)修改学生属性
        student.setSname("王力宏有点帅");
        //4.3)修改学生
        studentService.update(student);
    }
    //5.根据指定Id删除学生
    @Test
    public void test05(){
   
        studentService.deleteById(22);
    }
    //6.查询所有班级
    @Test
    public void test06(){
   
        List<Classes> classes = classesService.findAll();
        for (Classes aClass : classes) {
   
            System.out.println(classes);
        }
    }
    //7.关键字条件查询(方式一：使用#+%进行查询 需要传入%)
    @Test
    public void test07(){
   
        //7.1)定义学生对象
        Student student = new Student();
        //7.2)设置查询的参数
        student.setSname("%"+"刘"+"%");
        student.setAddr("%"+"海"+"%");
        //7.3)条件查询学生
        List<Student> students = studentService.findByStu(student);
        //7.4)遍历学生
        for (Student stud : students) {
   
            System.out.println(stud);
        }
    }
    //8.关键字条件查询(方式二：使用$进行查询 不需要传入% 直接在sql拼凑%)
    @Test
    public void test08(){
   
        //8.1)定义学生对象
        Student student = new Student();
        //8.2)设置查询的参数
        student.setSname("刘");
        student.setAddr("海");
        //8.3)条件查询学生
        List<Student> students = studentService.findByStu2(student);
        //8.4)遍历学生
        for (Student stud : students) {
   
            System.out.println(stud);
        }
    }
    //9.查询学生总数
    @Test
    public void test09(){
   
        Long count = studentService.findCount