代理模式下spring整合mybatis的常见SQL语句

代理模式下spring整合mybatis的常见SQL语句

项目结构：

1.实体类 User.java

package com.hao.pojo;

public class User {

    private Integer id;
    private String name;
    private String upwd;
    private String phone;
    private String email;
    private Integer age;
    private Integer status;
    private Integer classNo;

    public User() {
    }

    public User(Integer id, String name, String upwd, String phone,
                String email, Integer age, Integer status, Integer classNo) {
        this.id = id;
        this.name = name;
        this.upwd = upwd;
        this.phone = phone;
        this.email = email;
        this.age = age;
        this.status = status;
        this.classNo = classNo;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", upwd='" + upwd + '\'' +
                ", phone='" + phone + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                ", status=" + status +
                ", classNo=" + classNo +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public String getUpwd() {
        return upwd;
    }

    public void setUpwd(String upwd) {
        this.upwd = upwd;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    public Integer getClassNo() {
        return classNo;
    }

    public void setClassNo(Integer classNo) {
        this.classNo = classNo;
    }
}

2.接口类 UserMapp.java

package com.hao.mapper;

import com.hao.pojo.User;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;


public interface UserMapper {

    List<User> findAllUser(); //查询全部用户
    User findUserById(Integer id);  //根据ID查找

    void addUser(User user); //增加用户  增删改返回的都是受影响的行数

    void deleteUserByid(int id); //根据删除用户

    void updataUser(User user);  //更改用户

    void updataUser2(int id,String name);  //通过id 更改姓名

    List<User>findByName(String name); //一个参数的模糊查询

    User findBylike(int id,String name); //   两个参数的模糊查询

    List<User>findBylikeList(HashMap<String, Object> map);  //三个参数的模糊查询

    List<User>findSimple(HashMap  map);//不确定参数个数的查询  (多条件查询)

    List<User>findByParam(HashMap map); //分页查询

    void updateUserByCond(User user);//动态sql语句的修改

    List<User>findUserByCond(HashMap map); //trim操作查询

    List<User>findUserByCond2(ArrayList ids);//查询id在集合中的user对象

    List<User>findUserByCond3(HashMap map);//choose  when  跟switch case 很像
}

3.接口的Mapper映射对象 UserMapper.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">

<!-- namespace 对应dao接口全称：包名+类名 -->
<mapper namespace="com.hao.mapper.UserMapper">


    <!-- 属性名与列名不一致的解决办法 column 数据库列名 properrty 类里的属性名 -->
    <resultMap type="User" id="user">
        <result property="name" column="uname" javaType="String"></result>
    </resultMap>

    <!--查询全部用户-->
    <select id="findAllUser"  resultMap="user">
        select *
        from t_user;
    </select>


    <!-- 根据ID查找 -->
    <select id="findUserById"  resultMap="user">
        select *
        from t_user
        where id = #{id};
    </select>

    <!-- 增加用户 -->
    <insert id="addUser" parameterType="User">
        insert into t_user
        values (#{id}, #{name}, #{upwd}, #{phone}, #{email}, #{age}, #{status}, #{classNo})
    </insert>

    <!-- 删除用户 -->
    <delete id="deleteUserByid" parameterType="User">
        delete
        from t_user
        where id = #{id};
    </delete>

    <!-- 更新用户 -->
    <update id="updataUser" parameterType="User">
        update t_user
        set uname=#{name},
            upwd=#{upwd},
            phone=#{phone},
            email=#{email},
            age=#{age},
            status=#{status},
            classNo=#{classNo}
        where id = #{id};
    </update>

    <!--    通过id 更改姓名-->
    <update id="updataUser2" parameterType="User">
        update t_user
        set uname =
                #{arg1}
        where id = #{arg0};
    </update>


    <!-- 模糊查询 -->
    <select id="findByName"  resultMap="user">
        select *
        from t_user
        where uname like concat('%', #{name}, '%');
    </select>

    <!-- 两个参数的查询 -->
    <select id="findBylike" resultMap="user">
        select *
        from t_user
        where id = #{arg0}
          and uname like #{arg1};
    </select>

    <!-- 三个参数的模糊查询 -->
    <select id="findBylikeList" resultMap="user">
        select *
        from t_user
        where uname like #{name}
          and age = #{age}
          and classNo = #{classNo};
    </select>

    <!-- 多条件查询（不确定参数个数的查询） -->
    <select id="findSimple" resultMap="user">
        select * from t_user where 1=1
        <if test="name!=null and name!=''">
            and uname like #{name}
        </if>
        <if test="age!=null">
            and age &lt;= #{MaxAge}
        </if>
        <if test="age!=null">
            and age &gt;= #{MinAge}
        </if>
    </select>

    <!-- 分页查询 -->
    <select id="findByParam" resultMap="user">
        select * from t_user where 1=1
        <if test="uname!=null and uname!=''">
            and uname = #{uname}
        </if>
        <if test="uage!=null">
            and age = #{uage}
        </if>
        limit #{start},#{pageSize}  <!-- limit 10,20   查询第二页的数据  strat=(pageIndex-1)*pageSize -->
    </select>

    <!-- 动态更新 -->
    <update id="updateUserByCond" parameterType="User">
        update t_user
        <set>
            <if test="uname!=null and uname!=''">
                uname=#{uname},
            </if>
            <if test="age!=null">
                age = #{age},
            </if>
            <if test="phone!=null and phone!=''">
                phone = #{phone},
            </if>
        </set>
        where id = #{id}
    </update>


    <!-- trim查询 -->
    <select id="findUserByCond" resultMap="user">
        select * from t_user
        <trim prefix="where" prefixOverrides="and">
            <if test="name!=null and name!=''">
                and uname like #{name}
            </if>

            <if test="MinAge!=null">
                and age&gt;=#{MinAge}
            </if>
            <if test="MaxAge!=null">
                and age&lt;=#{MaxAge}
            </if>

        </trim>
    </select>


    <!-- 查询id在集合中的user对象   foreach运用场景： 参数是数组或集合的 -->
    <select id="findUserByCond2" resultMap="user"
            parameterType="ArrayList">
        select * from t_user where id in
        <foreach collection="list" item="ids" open="(" close=")"
                 separator=",">
            #{ids}
        </foreach>
    </select>



    <!-- choose when 跟switch case 很像 -->
    <select id="findUserByCond3" resultMap="user">
        select * from t_user
        <choose>
            <when test="a==1">
                where uname like #{name}
            </when>

            <when test="a==2">
                where age&gt;=#{MinAge}
            </when>

            <when test="a==3">
                where age&lt;=#{MaxAge}
            </when>

            <otherwise>
                where status=#{status}
            </otherwise>
        </choose>
    </select>

</mapper>

4.测试类 MyTest.java

package com.hao.test;


import com.hao.mapper.UserMapper;
import com.hao.pojo.User;
import lombok.extern.log4j.Log4j;
import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;


@Log4j
public class MyTest {


    @Test
    public  void test1(){
        ApplicationContext context = new ClassPathXmlApplicationContext("spring-mybatis.xml");
        UserMapper um = context.getBean( UserMapper.class);

        log.debug("开始执行SQL语句！");
        //查询全部用户
        List<User> list = um.findAllUser();
        for (User user : list) {
            System.out.println(user);
        }

        //根据ID查询用户
//        User byId = um.findUserById(3);
//        System.out.println(byId);

        //增加用户
//        User user = new User(50,"王丹","234234","234332","345654",25,3,3);
//        um.addUser(user);

        //通过id删除用户
//        um.deleteUserByid(49);

        //更改用户
//        User user = new User(48,"王琼","23354234","23324332","3454654",26,3,7);
//        um.updataUser(user);

        //通过id 更改用户姓名
//        um.updataUser2(47,"张词");
//        System.out.println(um.findUserById(47));


        //模糊查询
//        List<User> users = um.findByName("%大%");
//        for(User user : users) {
//            System.out.println(user);
//        }

        //两个参数的查询
//        User user = um.findBylike(34,"大%");
//        System.out.println(user);

        //三个参数的查询
//        HashMap<String, Object> map =new HashMap<String, Object>();
//        map.put("name","大%");
//        map.put("age",23);
//        map.put("classNo",6);
//        List<User> user = um.findBylikeList(map);
//        for (User user1 : user) {
//            System.out.println(user1);
//        }


        //多条件查询
//        HashMap<Object, Object>map = new HashMap<Object, Object>();
//        map.put("name","王%");
//        map.put("MixAge",40);
//        map.put("MinAge",20);
//        List<User> list = um.findSimple(map);
//        for (User user : list) {
//            System.out.println(user);
//        }


        //分页查询
//        HashMap<String, Object>map = new HashMap<String, Object>();
//
//        map.put("age", 23);
//
//        Integer pageIndex = 1;//第n页
//        Integer pageSize = 5; // 每页n条
//        Integer start = (pageIndex-1)*pageSize;
//
//        map.put("start", start);
//        map.put("pageSize",pageSize);
//        List<User>users = um.findByParam(map);
//        for (User user : users) {
//            System.out.println(user);
//        }

        //动态sql更新
//        User user = new User();
//        user.setId(10);
//        user.setName("王胁");
//        user.setAge(24);
//        user.setPhone("167238828828");
//        System.out.println(um.findUserById(10));

        //trim操作
//        HashMap<String,Object> map = new HashMap<String,Object>();
//        map.put("name","王%");
//        map.put("MinAge",12);
//        map.put("MaxAge",38);
//        List<User>list = um.findUserByCond(map);
//        for (User user : list) {
//            System.out.println(user);
//        }


        //查询id在集合中的user对象
//        ArrayList<Integer> ids = new ArrayList<Integer>();
//		ids.add(2);
//		ids.add(3);
//		ids.add(4);
//		ids.add(5);
//		List<User>users = um.findUserByCond2(ids);
//		for (User user : users) {
//			System.out.println(user);
//		}


        //choose  when  跟switch case 很像
//		HashMap<String, Object>map = new HashMap<String, Object>();
//        map.put("a",3);  //一个choose
//       // map.put("name", "王%");  //当a==1
//       // map.put("MinAge", 13);   //当a==2
//		map.put("MaxAge", 25);   //当a==3
//		List<User>users = um.findUserByCond3(map);
//		for (User user : users) {
//			System.out.println(user);
//		}
    }

}