MyBatis的高级查询与映射

桃扇骨 2023-07-04 11:10 7阅读 0赞

#### 1，导入MyBatis的依赖 ####

<dependencies>
    
                
                <dependency>
                    <groupId>org.mybatis</groupId>
                    <artifactId>mybatis</artifactId>
                    <version>3.4.6</version>
                </dependency>
    
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>5.1.46</version>
                    <scope>runtime</scope>
                </dependency>
                <dependency>
                    <groupId>junit</groupId>
                    <artifactId>junit</artifactId>
                    <version>4.12</version>
                    <scope>test</scope>
                </dependency>
            </dependencies>

#### 2，需要映射的实体类，User.class,Order.class ####

public class User {
        
        private  int id;
        private String username;
        private String password;
        private String email;
        private String gender;
        private int flag;
        private int role;
        private String code;
    
        private List<Order> orders;
    
    
        public List<Order> getOrders() {
        
            return orders;
        }
    
        public void setOrders(List<Order> orders) {
        
            this.orders = orders;
        }
    
        public int getId() {
        
            return id;
        }
    
        public void setId(int id) {
        
            this.id = id;
        }
    
        public String getUsername() {
        
            return username;
        }
    
        public void setUsername(String username) {
        
            this.username = username;
        }
    
        public String getPassword() {
        
            return password;
        }
    
        public void setPassword(String password) {
        
            this.password = password;
        }
    
        public String getEmail() {
        
            return email;
        }
    
        public void setEmail(String email) {
        
            this.email = email;
        }
    
        public String getGender() {
        
            return gender;
        }
    
        public void setGender(String gender) {
        
            this.gender = gender;
        }
    
        public int getFlag() {
        
            return flag;
        }
    
        public void setFlag(int flag) {
        
            this.flag = flag;
        }
    
        public int getRole() {
        
            return role;
        }
    
        public void setRole(int role) {
        
            this.role = role;
        }
    
        public String getCode() {
        
            return code;
        }
    
        public void setCode(String code) {
        
            this.code = code;
        }
    
    
        @Override
        public String toString() {
        
            return "User{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    ", email='" + email + '\'' +
                    ", gender='" + gender + '\'' +
                    ", flag=" + flag +
                    ", role=" + role +
                    ", code='" + code + '\'' +
                    ", orders=" + orders +
                    '}';
        }
    }

public class Order {
        
        private  String id;
        private  int money;
    
        private String status;
    
    
        private List<Orderdetail> orderdetailList;
    
        public List<Orderdetail> getOrderdetailList() {
        
            return orderdetailList;
        }
    
        public void setOrderdetailList(List<Orderdetail> orderdetailList) {
        
            this.orderdetailList = orderdetailList;
        }
    
        public String getStatus() {
        
            return status;
        }
    
        public void setStatus(String status) {
        
            this.status = status;
        }
    
        public String getId() {
        
            return id;
        }
    
        public void setId(String id) {
        
            this.id = id;
        }
    
        public int getMoney() {
        
            return money;
        }
    
        public void setMoney(int money) {
        
            this.money = money;
        }
    
        @Override
        public String toString() {
        
            return "Order{" +
                    "id='" + id + '\'' +
                    ", money=" + money +
                    ", status='" + status + '\'' +
                    ", orderdetailList=" + orderdetailList +
                    '}';
        }
    
    }

#### 3，MyBatis的接口mapper类UserMapper.class ####

/**
     * Created by davidshion 2/9/20/6:27 PM
     * 相当于以前的dao接口
     *
     * @Author davidshi
     */
    public interface UserMapper {
        
         User findUserById(int id);
    
         List<User> findUserByMult(User user);
         List<User> findUserByMult1(User user);
    
         List<User> findUserByIds(List<Integer> ids);
         List<User> findUserByIds1(QueryVo queryVo);
    
         User findUserAndOrderByUserId(int id);
    
         User findUserAndOrderAndOrderDetailByUserId(int id);
    }

#### 4，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" >
    
    <mapper namespace="person.david.mybatis.mapper.UserMapper">
        
        <select id="findUserById" parameterType="int" resultType="person.david.mybatis.pojo.User">
            select * from tb_user where  id =#{id}
        </select>
    
    
        
            
    
        
        
        
        
            
        
    
        
           
            
            
        
    
    
        
    
        <sql id="getall">
            SELECT  id, username, password, email, gender, flag, role, code from tb_user
    
        </sql>
    
    
    <select id="findUserByMult" resultType="person.david.mybatis.pojo.User">
    
    
        SELECT  id, username, password, email, gender, flag, role, code from tb_user
    
      <where>
        <if test="username!=null and username !=''">
    
            and username=#{username}
    
        </if>
    
        <if test="email!=null and email !=''">
    
            and email=#{email}
    
        </if>
      </where>
    </select>
    
    
        <select id="findUserByMult1" resultType="person.david.mybatis.pojo.User">
            <include refid="getall"/>
            <where>
                <if test="username!=null and username !=''">
                    and username=#{username}
                </if>
                <if test="email!=null and email !=''">
                    and email=#{email}
                </if>
            </where>
        </select>
    
    
        <select id="findUserByIds" parameterType="arraylist" resultType="person.david.mybatis.pojo.User">
            select * from tb_user
          <where>
              <if test="list !=null">
                <foreach collection="list" item="id" open="id in (" close=")" separator=",">
                    #{id}
                </foreach>
              </if>
          </where>
        </select>
    
    
        <select id="findUserByIds1" parameterType="person.david.mybatis.vo.QueryVo" resultType="person.david.mybatis.pojo.User">
    
            select * from tb_user
            <where>
                <if test="ids !=null">
                    <foreach collection="ids" item="id" open="id in (" close=")" separator=",">
                        #{id}
                    </foreach>
                </if>
            </where>
    
        </select>
    
        <resultMap id="basemap" type="person.david.mybatis.pojo.User">
    
            <id column="uid" property="id"></id>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
        </resultMap>
    
    
    
    
        <resultMap id="userorderresultmap" type="person.david.mybatis.pojo.User" extends="basemap">
            
            
            
    
                <collection property="orders" ofType="person.david.mybatis.pojo.Order">
                    <id column="id" property="id"></id>
                    <result  column="money" property="money"/>
                    <result column="status" property="status"/>
                </collection>
        </resultMap>
    
    
        <select id="findUserAndOrderByUserId" resultMap="userorderresultmap">
            select  u.id uid , u.username, u.password,o.id,o.money,o.status from tb_user u ,tb_order o where  u.id=o.uid and u.id =#{id}
    
        </select>
    
    
    
    
    
        <resultMap id="userorderdetailresultmap" type="person.david.mybatis.pojo.User" extends="basemap">
            
            
            
    
            <collection property="orders" ofType="person.david.mybatis.pojo.Order">
                <id column="id" property="id"></id>
                <result  column="money" property="money"/>
                <result column="status" property="status"/>
    
                <collection property="orderdetailList" ofType="person.david.mybatis.pojo.Orderdetail">
                    <id column="odid" property="id"></id>
                    <result column="num" property="num"></result>
                    <result column="money" property="money"></result>
                </collection>
    
            </collection>
    
        </resultMap>
    
    
        <select id="findUserAndOrderAndOrderDetailByUserId" resultMap="userorderdetailresultmap">
            select  u.id uid , u.username, u.password,o.id,o.money,o.status ,od.id odid, od.num,od.money from tb_user u ,tb_order o ,tb_orderdetail od where  u.id=o.uid and o.id=od.Oid and u.id =#{id}
    
        </select>
    </mapper>

#### 5，MyBatis的配置类SqlMapConfig.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>
    
        <settings>
            
            <setting name="logImpl" value="STDOUT_LOGGING" />
        </settings>
       
        
        <environments default="abc">
    
            <environment id="abc">
                
                <transactionManager type="JDBC"></transactionManager>
    
                
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql:///db_shopping?useUnicode=true&characterEncoding=UTF8"/>
                    <property name="username" value="root"/>
                    <property name="password" value="qishimeiyoumima"/>
                </dataSource>
            </environment>
    
        </environments>
    
     <mappers>
    
         
         
            
         
            <package name="person.david.mybatis.mapper"/>
    
     </mappers>
    
    </configuration>

#### 6，测试类TestMain.class ####

public class TestMain {
        
        private SqlSessionFactory sqlSessionFactory;
    
        @Before
        public void before() throws IOException {
        
            InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        }
    
        @Test
        public void test1() {
        
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);//通过动态代理的方式获取到Usermapper的一个代理对象
            User user = userMapper.findUserById(10);//执行流程 1.当你调用这个方法的时候,因为是一个动态代理对象,所以mubatis内部会拦截你调用的方法,然后可以获取到方法的权限定名称,而这个名称其实就是mapper中你要执行的sql的唯一标记,参考我们普通的编写方式,2 在内部又转换成我们原始的调用方式
            System.out.println(user);
            // "com.qianfeng.mybatis.mapper.UserMapper.findUserById"
            //反射只提供最基本的操作,就相当于我们盖楼的时候基础部件,比如砖,水泥,钢筋
            //
    
            sqlSession.close();
        }
    
        @Test
        public void test2() {
        
            StackTraceElement[] stackTrace = Thread.currentThread().getStackTrace();
            String methodName = stackTrace[1].getMethodName();
            String name = this.getClass().getName();
            System.out.println(name);
            System.out.println(methodName);
        }
    
        @Test
        public void test3(){
        
            User user = new User();
            user.setUsername("王三十");
            //user.setEmail("wangsanshi1.com");
    
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            List<User> result = mapper.findUserByMult(user);
            System.out.println(result);
        }
    
        @Test
        public void test4(){
        
            User user = new User();
            user.setUsername("王三十");
            //user.setEmail("wangsanshi1.com");
    
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            List<User> result = mapper.findUserByMult1(user);
            System.out.println(result);
        }
    
    
        @Test
        public void test5(){
        
            List list = new ArrayList();
            list.add(2);
            list.add(3);
            list.add(4);
            list.add(5);
            list.add(6);
            list.add(7);
            list.add(8);
            list.add(9);
            list.add(10);
            list.add(11);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            List<User> result = mapper.findUserByIds(list);
            System.out.println(result);
        }
    
        @Test
        public void test6(){
        
            QueryVo queryVo = new QueryVo();
            List list = new ArrayList();
            list.add(2);
            list.add(3);
            list.add(4);
            list.add(5);
            list.add(6);
            list.add(7);
            list.add(8);
            list.add(9);
            list.add(10);
            list.add(11);
            queryVo.setIds(list);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            List<User> result = mapper.findUserByIds1(queryVo);
            System.out.println(result);
        }
    
        @Test
        public void test7() {
        
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            User user = mapper.findUserAndOrderByUserId(9);
            System.err.println(user+"========>"+user.getOrders().size());
        }
    
        @Test
        public void test8() {
        
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            User user = mapper.findUserAndOrderAndOrderDetailByUserId(9);
            System.err.println(user+"========>"+user.getOrders().size());
        }
    }