<select id="xcxGetAnnounce" resultMap="policyVo"> SELECT zhengce.id,zhengce.title, zhengce.dispatch_date,zhengce.declare_date, zhengce.fw_department <if test="sysType != null and sysType == 3 and keyWord!=null and keyWord!=''"> ,MATCH(zhengce.title) AGAINST(REPLACE(#{keyWord},' ','')) AS score </if> FROM oa_zhengce zhengce WHERE type=2 AND zhijian_status=5 AND status=1 AND ( zhengce.zc_jibie=1 OR <choose> <when test="sysType != null and sysType == 3"> zhengce.area='440604' OR ( zhengce.zc_jibie=2 and zhengce.prov='440000') OR (zhengce.zc_jibie=3 and zhengce.city='440600') </when> <when test="sysType != null and sysType == 2"> zhengce.street='440604010' OR ( zhengce.zc_jibie=2 and zhengce.prov='440000') OR (zhengce.zc_jibie=3 and zhengce.city='440600') OR (zhengce.zc_jibie=4 and zhengce.city='440604') </when> <otherwise> zhengce.city=#{city} OR ( zhengce.zc_jibie=2 and zhengce.prov=#{prov}) </otherwise> </choose> ) <choose> <when test="sysType != null and sysType == 2 and keyWord!=null and keyWord!=''"> AND REPLACE(zhengce.title,' ','') like CONCAT('%',REPLACE(#{keyWord},' ',''),'%') </when> <when test="sysType != null and sysType == 3 and keyWord!=null and keyWord!=''"> AND MATCH(zhengce.title) AGAINST(REPLACE(#{keyWord},' ','')) </when> </choose> <choose> <when test="keyWord==null or keyWord==''"> ORDER BY zhengce.dispatch_date desc,zhengce.id </when> <otherwise> ORDER BY score desc ,zhengce.dispatch_date desc, zhengce.id desc </otherwise> </choose> </select>
时间: 2024-02-15 09:28:54 浏览: 113
这是一个 MyBatis 的 XML 映射文件,用于查询 oa_zhengce 表中的政策数据。它包含一个 SELECT 语句,其中包含多个 SQL 语句片段,用于根据不同的输入参数动态构建查询条件。
具体来说,该 SELECT 语句将返回 oa_zhengce 表中类型为 2、审核状态为 5、发布状态为 1,且符合特定级别和地区要求的政策数据。其中,如果输入参数 sysType 为 3,且输入参数 keyWord 不为 null 或空字符串,则会使用 MATCH AGAINST 函数对政策标题进行全文检索,并按照检索得分从高到低排序;否则,将按照发布日期和政策 id 从新到旧排序。
该 SELECT 语句还包含多个 SQL 片段,可根据不同的输入参数和条件动态构建查询条件。例如,如果输入参数 sysType 为 2,则政策所在的地区必须为指定的街道或城市,否则政策所在的地区必须为指定的城市或省份。如果输入参数 keyWord 不为 null 或空字符串,则还需要对政策标题进行模糊匹配或全文检索。
相关问题
根据下面的配置信息,写出Classes,Teacher和Student这三个POJO的全部代码。<mapper namespace="com.lcb.mapping.userMapper"> <l--association—对—关联查询--> <select id="getClass" parameterType="int" resultMap="ClassesResultMap">select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id} <select> <resultMap type="com.lcb.user.Classes" id="ClassesResultMap"><l--实体类的字段名和数据表的字段名映射--> <id property="id" column="c_id"/> <result property="name" column="c_name"/><association property="teacher" javaType="com.Icb.user.Teacher"><id property="id" column="t_id" l> <result property="name" column="t_name"/></association> </resultMap> <l--collection—对多关联查询--> <select id="getClass2" parameterType="int"resultMap="ClassesResultMap2"> select * from class c,teacher t,student s where c.teacher_id=t.t_idand c.c_id=s.class_id and c.c_id=#{ id} <select> <resultMap type="com.lcb.user.Classes" id="ClassesResultMap2"><id property=""id" column="c_id"/> <result property="name" column="c_name"/><association property="teacher" javaType="com.Icb.user.Teacher"><id property="id" column="t_id"l> <result property="name" column="t_name"/></association> <collection property="student" ofType="com.Icb.user.Student"><id property="id" column="s_id"/> cresult property="name" column="s_name"/></collection> </resultMap></mapper>
1. Classes POJO代码:
public class Classes {
private int id;
private String name;
private int teacherId;
private Teacher teacher;
// 省略getter和setter方法
}
2. Teacher POJO代码:
public class Teacher {
private int id;
private String name;
// 省略getter和setter方法
}
3. Student POJO代码:
public class Student {
private int id;
private String name;
private int age;
private int classId;
private Classes classes;
// 省略getter和setter方法
}
<?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.phonemarket.mapper.BannerMapper"> <resultMap type="Banner" id="BaseResultMap"> <id property="bannerId" column="banner_id" /> <result property="bannerName" column="banner_name" /> <result property="bannerUrl" column="banner_url" /> <result property="bannerImg" column="banner_img" /> <result property="bannerState" column="banner_state" /> </resultMap> <insert id="addBanner" parameterType="Banner"> insert into banner(banner_name,banner_url,banner_img) values(#{bannerName},#{bannerUrl},#{bannerImg}) </insert> <update id="deleteBanner" parameterType="java.lang.Integer"> update banner set banner_state=-1 where banner_id=#{id} </update> <update id="changeBannerState" parameterType="java.lang.Integer"> update banner set banner_state=#{0} where banner_id=#{1} </update> <update id="updateBanner" parameterType="Banner"> update banner <set> <if test="bannerName!=null"> banner_name=#{bannerName}, </if> <if test="bannerUrl!=null"> banner_url=#{bannerUrl}, </if> <if test="bannerImg!=null"> banner_img=#{bannerImg} </if> </set> where banner_id=#{bannerId} </update> <select id="findBannerById" parameterType="java.lang.Integer" resultMap="BaseResultMap"> select * from banner where banner_id=#{id} </select> <select id="findAllBanner" resultMap="BaseResultMap"> select * from banner where banner_state > 0 </select> <select id="findAllBannerByLikeName" parameterType="java.lang.String" resultMap="BaseResultMap"> select * from banner where banner_state > 0 and banner_name like '%${value}%' </select> <select id="findAllShowBanner" resultMap="BaseResultMap"> select * from banner where banner_state=1 </select> </mapper>
这是一个 MyBatis 的 XML 配置文件,用于映射 Banner 对象和 banner 数据库表之间的关系。文件中定义了一个 namespace 为 com.phonemarket.mapper.BannerMapper 的 Mapper,并包含了一些 SQL 语句,如插入、删除、更新和查询等。括号中的参数类型表示 SQL 语句中的参数类型,resultMap 定义了查询结果与 Banner 对象之间的映射关系。其中BaseResultMap是一个标识符,表示基本的结果映射,对应了Banner类中的属性名称。在 SQL 语句中,#{param} 表示参数占位符,${value} 表示变量占位符。
阅读全文