[PHP知识库]PHP和JAVA 动态SQL

Laravel动态SQL

• 第一个：查询属性值

        $stay_at_school_id = request()->stay_at_school_id == null || request()->stay_at_school_id == "null" || request()->stay_at_school_id == "" ? null : (int)request()->stay_at_school_id;
        $check_state = request()->check_state == null || request()->check_state == "null" || request()->check_state == "" ? null : (int)request()->check_state;

 		//方便方便条件查询导出
        $result = StayAtSchoolDetails::with('stayAtSchool')
            ->with('myBed')->with('applyBed')
            ->when($stay_at_school_id, function ($query) use ($stay_at_school_id) {
            //whereHas 对象中对象属性可以用这个
                return $query->whereHas('stayAtSchool', function ($query) use ($stay_at_school_id) {
                    return $query->where('id', '=', $stay_at_school_id);
                });
            })
            //when(判断条件，回调函数)，对象的属性值
            ->when($check_state==null, function ($query) use ($check_state) {
                return $query->where('check_state', '=', $check_state);
            })
            ->get();

• 第二个：查询关联的属性值

查询关联的属性值
->whereHas('bed',function ($query) use ($bed_id){ $query->where('id','=',$bed_id); })

    function exportStudentData($request){

        //获取参数
        $name = $request['name']!="null"?substr($request['name'],1):null;
        $bed_id = $request['bed_id']!="null"?$request['bed_id']:null;
        $number = $request['number']!="null"?substr($request['number'],1):null;
        $nationality = $request['nationality']!="null"?substr($request['nationality'],1):null;
        $politics = $request['politics']!="null"?substr($request['politics'],1):null;
        $college = $request['college']!="null"?substr($request['college'],1):null;
        $major = $request['major']!="null"?substr($request['major'],1):null;
        $class = $request['class']!="null"?substr($request['class'],1):null;
        $grade = $request['grade']!="null"?substr($request['grade'],1):null;
        $system = $request['system']!="null"?substr($request['system'],1):null;
        $level = $request['level']!="null"?$request['level']:null;

        //获取数据
        $queryUserStudent =UserStudent::query()
            ->with('bed')
// 	业务不需要这个，用上一行代替。
//            ->whereHas('bed',function ($query) use ($bed_id){
//                $query->where('id','=',$bed_id);
//            })
            ->when($name,function ($query) use ($name){
                 $query->where('name','like',$name);
            })
            ->when($number,function ($query) use ($number){
                $query->where('number','like',$number);
            })
            ->when($nationality,function ($query) use ($nationality){
                $query->where('nationality','like',$nationality);
            })
            ->when($politics,function ($query) use ($politics){
                $query->where('politics','like',$politics);
            })
            ->when($college,function ($query) use ($college){
                $query->where('college','like',$college);
            })
            ->when($major,function ($query) use ($major){
                $query->where('major','like',$major);
            })
            ->when($class,function ($query) use ($class){
                $query->where('class','like',$class);
            })
            ->when($grade,function ($query) use ($grade){
                $query->where('grade','like',$grade);
            })
            ->when($system,function ($query) use ($system){
                $query->where('system','like',$system);
            })
            ->when($level,function ($query) use ($level){
                $query->where('level','=',$level);
            })
            ->get();

        return  $queryUserStudent;
    }
    

java 动态SQL

    <select id="selectAnswerList" parameterType="com.ruoyi.course.domain.Answer" resultMap="AnswerResult">
        select id, exam_id, student_number, content, score, create_time, update_time, delete_time,comment,state from answer
        <where>  
            <if test="examId != null "> and exam_id = #{examId}</if>
            <if test="studentNumber != null  and studentNumber != ''"> and student_number = #{studentNumber}</if>
            <if test="content != null  and content != ''"> and content = #{content}</if>
            <if test="score != null "> and score = #{score}</if>
            <if test="deleteTime != null "> and delete_time = #{deleteTime}</if>
            <if test="comment != null "> and comment = #{comment}</if>
            <if test="state != null "> and state = #{state}</if>
        </where>
    </select>

    <insert id="insertAnswer" parameterType="Answer" useGeneratedKeys="true" keyProperty="id">
        insert into answer
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="examId != null ">exam_id,</if>
            <if test="studentNumber != null  and studentNumber != ''">student_number,</if>
            <if test="content != null ">content,</if>
            <if test="score != null ">score,</if>
            <if test="createTime != null ">create_time,</if>
            <if test="updateTime != null ">update_time,</if>
            <if test="deleteTime != null ">delete_time,</if>
            <if test="comment != null ">comment,</if>
            <if test="state != null ">state,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="examId != null ">#{examId},</if>
            <if test="studentNumber != null  and studentNumber != ''">#{studentNumber},</if>
            <if test="content != null ">#{content},</if>
            <if test="score != null ">#{score},</if>
            <if test="createTime != null ">#{createTime},</if>
            <if test="updateTime != null ">#{updateTime},</if>
            <if test="deleteTime != null ">#{deleteTime},</if>
            <if test="comment != null ">#{comment},</if>
            <if test="state != null ">#{state},</if>
         </trim>
    </insert>


    <update id="updateAnswer" parameterType="com.ruoyi.course.domain.Answer">
        update answer
        <trim prefix="SET" suffixOverrides=",">
            <if test="examId != null ">exam_id = #{examId},</if>
            <if test="studentNumber != null  and studentNumber != ''">student_number = #{studentNumber},</if>
            <if test="content != null ">content = #{content},</if>
            <if test="score != null ">score = #{score},</if>
            <if test="createTime != null ">create_time = #{createTime},</if>
            <if test="updateTime != null ">update_time = #{updateTime},</if>
            <if test="deleteTime != null ">delete_time = #{deleteTime},</if>
            <if test="comment != null ">comment = #{comment},</if>
            <if test="state != null ">state = #{state},</if>
        </trim>
        where id = #{id}
    </update>

select * from answer
where student_number  in 
(select   student_number from   answer group by  student_number,exam_id  having count(student_number) > 1) 
and  exam_id in (SELECT exam_id from   answer group by  exam_id, student_number  having count(exam_id) > 1)
;