关于Springboot+Mybatis的动态查询

前端页面

 <form class="bs-example bs-example-form" method="get" role="form" style="width: 250px" th:action="@{/testGetUserByConditionIf}">
            <div class="input-group">
                <span class="input-group-addon">姓名</span>
                <input type="text" class="form-control" name="name" placeholder="请输入姓名">
            </div>
            <br>
            <div class="input-group">
                <span class="input-group-addon">身份证号</span>
                <input type="text" class="form-control" name="identityCode" placeholder="请输入身份证号">
            </div>
            <br>
            <div class="input-group">
                <span class="input-group-addon">报考科目 </span>
                <input type="text" class="form-control" name="examTitle" placeholder="请输入报考科目">
            </div>
            <br>
            <div class="input-group">
                <span class="input-group-addon">院系</span>
                <input type="text" class="form-control" name="subordinateDepartments" placeholder="请输入院系">
            </div>
            <br>
            <div class="input-group">
                <span class="input-group-addon">班级</span>
                <input type="text" class="form-control" name="classNumber" placeholder="请输入班级">
            </div>
            <br>
            <div class="input-group">
                <span class="input-group-addon">入学年份</span>
                <input type="text" class="form-control" name="enrollmentYear" placeholder="请输入入学年份">
            </div>
            <br>
            <button type="submit" class="btn btn-info">查询</button>

        </form>

bean

package edu.gdlgxy.newexaminationsystem.bean;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import java.math.BigDecimal;

@AllArgsConstructor
@NoArgsConstructor
@ToString
@Data
public class inputExcel{

    private BigDecimal id;//序号
    private String name;//名称
    private String sex;//性别
    private String identityCode;//身份证号码
    private BigDecimal stuNumber;//学号
    private String eduBackground;//学历
    private String enrollmentYear;//入学年份
    private String subjectCode;//科目代码
    private String examTitle;//报考科目
    private String examLevel;//等级
    private BigDecimal telNumber;//手机号码
    private String subordinateDepartments;//所属院系
    private String major;//所属专业
    private String classNumber;//班级号码
    private String password;//密码
    private int totalHours;//总学时，数据库默认为0

    public inputExcel(String name,String identityCode, String examTitle, String subordinateDepartments,String classNumber,String enrollmentYear){
        this.name=name;
        this.identityCode=identityCode;
        this.classNumber=classNumber;
        this.examTitle=examTitle;
        this.subordinateDepartments=subordinateDepartments;
        this.classNumber=classNumber;
        this.enrollmentYear=enrollmentYear;
    }

}

 

controller

/**
     * 按需要动态查询
     *
     * @return
     * @throws IOException
     */
    @GetMapping(value ={"/testGetUserByConditionIf","/testGetUserByConditionIf/{pageNum}"} )
    public String testGetUserByConditionIf(@Param("name") String name,
                                           @Param("identityCode") String identityCode,
                                           @Param("examTitle") String examTitle,
                                           @Param("subordinateDepartments") String subordinateDepartments,
                                           @Param("classNumber") String classNumber,
                                           @Param("enrollmentYear")String enrollmentYear,
                                           @RequestParam(defaultValue = "1", value = "pageNum") int pageNum,
                                           Model model) throws IOException {
        SqlSessionFactory ssf = Tools.getSqlSessionFactory("mybatis\\mybatis-config.xml");
        SqlSession session = ssf.openSession();

        try {
            inputExcelMapper dsm = session.getMapper(inputExcelMapper.class);
            log.info("dsm.toString():"+dsm.toString());
            log.info("准备获取iE...........");
            inputExcel iE = new inputExcel(name,identityCode,examTitle,subordinateDepartments,classNumber,enrollmentYear);
            log.info("成功获取iE...........");
            log.info("IE:"+iE.toString());
            log.info("准备获取userByConditionIf...........");
            List<inputExcel> userByConditionIf = dsm.getUserByConditionIf(iE);
            log.info("成功获取userByConditionIf...........");
            log.info("userByConditionIf:"+userByConditionIf.toString());
            //发送json数据到前端，用于导出excel表
            ObjectMapper mapper = new ObjectMapper();
            String json = mapper.writeValueAsString(userByConditionIf);
            model.addAttribute("json",json);
            //处理分页
            pageUtil.page(pageNum,model,userByConditionIf.size(),userByConditionIf);
            session.commit();
        } finally {
            session.close();
        }
            return "adminMainPage";
        }

Tool工具类

package edu.gdlgxy.newexaminationsystem.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

/**
 * 获取SqlSessionFactory的工具类
 */
public class Tools {

    public static SqlSessionFactory getSqlSessionFactory(String resource) throws IOException {
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

}

inputExcelMapper

package edu.gdlgxy.newexaminationsystem.mapper;

import edu.gdlgxy.newexaminationsystem.bean.inputExcel;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface inputExcelMapper {
    //携带了哪个字段查询条件就带上这个字段的值
    public List<inputExcel> getUserByConditionIf(inputExcel inputexcel);
}

inputExcelMapper.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">
<!--sql映射文件绑定mapper接口-->
<mapper namespace="edu.gdlgxy.newexaminationsystem.mapper.inputExcelMapper">

    <!-- 查询员工，要求，携带了哪个字段查询条件就带上这个字段的值 -->
    <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
    <select id="getUserByConditionIf" resultType="edu.gdlgxy.newexaminationsystem.bean.inputExcel">
        select * from inputExcel
        <!-- 自定义字符串的截取规则 -->
            <trim prefix="where" suffixOverrides="and">
                <if test="name!=null &amp;&amp; name!=&quot;&quot;">
                    name=#{name} and
                </if>
                <if test="identityCode!=null &amp;&amp; identityCode!=&quot;&quot;">
                    identityCode=#{identityCode} and
                </if>
                <if test="examTitle!=null &amp;&amp; examTitle!=&quot;&quot;">
                    examTitle=#{examTitle} and
                </if>
            <if test="subordinateDepartments!=null &amp;&amp; subordinateDepartments!=&quot;&quot;">
                subordinateDepartments=#{subordinateDepartments} and
            </if>
            <if test="classNumber!=null &amp;&amp; classNumber!=&quot;&quot;">
                classNumber=#{classNumber} and
            </if>
            <if test="enrollmentYear!=null &amp;&amp; enrollmentYear!=&quot;&quot;">
                enrollmentYear=#{enrollmentYear}
            </if>
 </trim>
    </select>

</mapper>

mybatis-config.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>


<!--    也可以直接在yaml配置文件直接配置-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/examinationSysInfo?serverTimezone=GMT%2B8&amp;useUnicode=true&amp;characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="7589" />
            </dataSource>
        </environment>
    </environments>


    <mappers>
        <mapper resource="mybatis\mapper\inputExcelMapper.xml" />
    </mappers>


</configuration>