Mybatis框架（四）

向右看齐 2022-03-20 02:44 141阅读 0赞

上一次写到Mybatis的动态sql，这次写多表查询和Spring整合Mybatis。

--------------------

##### 多表查询 #####

在我们生活中的多表查询是处处都有的，有一对一的查询，也有一对多的查询，还有多对多的查询。  
比如在生活中一次购物，一个人对应一个订单（一对一），多个订单项对应一个订单（一对多），多个人对应多个订单（多对多）。  
下边就用实例实现多表查询

###### 映射文件（一对一查询） ######

<resultMap id="GetOrderUser" type="order">
            <id property="id" column="id"/>
            <result property="userId" column="user_id"/>
            <result property="number" column="number"/>
            <result property="createtime" column="createtime"/>
            <result property="note" column="note"/>
            <association property="user" javaType="user">
                <id property="id" column="id"/>
                <result property="username" column="username"/>
            </association>
        </resultMap>
        <select id="GetOrderUser" resultMap="GetOrderUser">
            SELECT o.*,u.id,u.username
            FROM mybatis.order o
                      left join mybatis.user u on u.id = o.user_id;
        </select>

pojo，给订单项增加user及其setter，并重写toString

package pojo;
    
    import java.util.Date;
    
    public class Order {
    	private Integer id;
    
    	private Integer userId;
    
    	private String number;
    
    	private Date createtime;
    
    	private String note;
    
    	private User user;
    
    	public Integer getId() {
    		return id;
    	}
    
    	public void setId(Integer id) {
    		this.id = id;
    	}
    
    	public Integer getUserId() {
    		return userId;
    	}
    
    	public void setUserId(Integer userId) {
    		this.userId = userId;
    	}
    
    	public String getNumber() {
    		return number;
    	}
    
    	public void setNumber(String number) {
    		this.number = number == null ? null : number.trim();
    	}
    
    	public Date getCreatetime() {
    		return createtime;
    	}
    
    	public void setCreatetime(Date createtime) {
    		this.createtime = createtime;
    	}
    
    	public String getNote() {
    		return note;
    	}
    
    	public void setNote(String note) {
    		this.note = note == null ? null : note.trim();
    	}
    
    	@Override
    	public String toString() {
    		return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
    				+ ", note=" + note + ",username="+user.getUsername()+"]";
    	}
    
    	public User getUser() {
    		return user;
    	}
    
    	public void setUser(User user) {
    		this.user = user;
    	}
    }

测试

@Test
        public void testOrderUser() throws IOException {
            SqlSession sqlSession = SqlSessionUtils.getSqlSession("SqlMapCofig.xml");
            OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
            List<Order> list = orderMapper.GetOrderUser();
            for (Order order : list
            ) {
                System.out.println(order.toString());
            }
    
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70]  
上边的是用的resultMap，也可以用resultType  
用法：新建一个连接后的pojo项，然后连接查询，这个很简单就不演示了。

###### 映射文件（一对多），注意一定要在order的id起别名和user的id分开，否则会混乱。 ######

###### 多对多和一对多大致相似，就是把一对多的集合遍历一遍 ######

使用collection标签

<resultMap id="getUserOrderMap" type="user">
            <id property="id" column="id"/>
            <result property="username" column="username"/>
            <result property="birthday" column="birthday"/>
            <result property="address" column="address"/>
            <result property="sex" column="sex"/>
            <collection property="orders" ofType="order">
                <id property="id" column="oid"/>
                <result property="userId" column="user_id"/>
                <result property="number" column="number"/>
                <result property="createtime" column="createtime"/>
                <result property="note" column="note"/>
            </collection>
            
        </resultMap>
        <select id="getUserOrder" resultMap="getUserOrderMap">
            select u.id,
                   u.username,
                   u.sex,
                   u.address,
                   u.birthday,
                   o.id oid,
                   user_id,
                   number,
                   createtime,
                   note
            from mybatis.user u
                     left join mybatis.order o on o.user_id = u.id;
        </select>

给user的pojo加上order

package pojo;
    import java.util.Date;
    
    public class User {
    
    	private Integer id;
    	private String username;// 用户姓名
    	private String sex;// 性别
    	private Date birthday;// 生日
    	private String address;// 地址
    	private String uuid1;
    	private Order order;
    
    	public Integer getId() {
    		return id;
    	}
    
    	public void setId(Integer id) {
    		this.id = id;
    	}
    
    	public String getUsername() {
    		return username;
    	}
    
    	public void setUsername(String username) {
    		this.username = username;
    	}
    
    	public String getSex() {
    		return sex;
    	}
    
    	public void setSex(String sex) {
    		this.sex = sex;
    	}
    
    	public Date getBirthday() {
    		return birthday;
    	}
    
    	public void setBirthday(Date birthday) {
    		this.birthday = birthday;
    	}
    
    	public String getAddress() {
    		return address;
    	}
    
    	public void setAddress(String address) {
    		this.address = address;
    	}
    
    	@Override
    	public String toString() {
    		return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address="
    				+ address +", uuid1="+uuid1+"]";
    	}
    
    	public String getUuid1() {
    		return uuid1;
    	}
    
    	public void setUuid1(String uuid1) {
    		this.uuid1 = uuid1;
    	}
    
    	public Order getOrder() {
    		return order;
    	}
    	
    	public void setOrder(Order order) {
    		this.order = order;
    	}
    }

测试类

@Test
        public void testUserOrders() throws IOException {
            SqlSession sqlSession = SqlSessionUtils.getSqlSession("SqlMapCofig.xml");
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<User> list = userMapper.getUserOrder();
            for (User user : list) {
                System.out.println(user.toString());
                for (Order o : user.getOrders()) {
                    System.out.println("订单：" + o.toString());
                }
            }
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70 1]

--------------------

### Spring整合mybatis ###

大致学完了mybatis，接下来就用前边学过的Spring整合mybatis。（会变的更简单）  
依我们前边所学的Spring，它可以帮助我们管理我们的类，使得我们使用起来更加便捷。那Spring整合mybatis到底能帮我们代理那些东西呢？  
1、SqlSessionFactory对象应该放到spring容器中作为单例存在。（Mybatis的单例对象）  
2、传统dao的开发方式中，应该从spring容器中获得sqlsession对象。  
3、Mapper代理形式中，应该从spring容器中直接获得mapper的代理对象。  
4、数据库的连接以及数据库连接池事务管理都交给spring容器来完成。  
可以说用到的类它基本都可以帮我们管理，下边就以一个实例来说明。  
1.新建一个工程：SpringMybatis  
2.导入相关的jar包  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70 2]  
上边的jar导入主要的就可以了，会有多余的包  
3.按照前边代码创建如下结构  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70 3]  
applicationContext.xml 创建Spring的配置文件

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:context="http://www.springframework.org/schema/context"
           xmlns:aop="http://www.springframework.org/schema/aop"
           xmlns:tx="http://www.springframework.org/schema/tx"
           xsi:schemaLocation="http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context.xsd
           http://www.springframework.org/schema/aop
           http://www.springframework.org/schema/aop/spring-aop.xsd
           http://www.springframework.org/schema/tx
           http://www.springframework.org/schema/tx/spring-tx.xsd">
        
        
        
        
        
        
        
        
        
    
        
        <context:property-placeholder location="classpath:db.properties"/>
        <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
            <property name="driverClassName" value="${jdbc.driverClass}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="username" value="${jdbc.user}"/>
            <property name="password" value="${jdbc.password}"/>
        </bean>
        
         <bean  id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
            
            <property name="dataSource" ref="dataSource"/>
            
            <property name="configLocation" value="Config/SqlMapCofig.xml"/>
            
            <property name="typeAliasesPackage" value="pojo"/>
        </bean>
       
       
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer" >
            <property name="basePackage" value="Mapper"/>
        </bean>
    </beans>

db.properties

jdbc.driverClass = com.mysql.cj.jdbc.Driver
    jdbc.url = jdbc:mysql://localhost:3306/spring?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
    jdbc.user = root
    jdbc.password = wrial.qq.com

log4j

# Global logging configuration
    log4j.rootLogger=DEBUG, stdout
    # Console output...
    log4j.appender.stdout=org.apache.log4j.ConsoleAppender
    log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
    log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

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>
    </configuration>

userMapper

package Mapper;
    
    import pojo.User;
    import java.util.List;
    
    public interface UserMapper {
    
        User getUserById(Integer id);
    
        List<User> getUserByLikeIt(String word);
    }

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="Mapper.UserMapper">
        <select id="getUserById" parameterType="Integer" resultType="pojo.User">
            select *
            from mybatis.user
            where id = #{id}
        </select>
        
        
        <select id="getUserByLikeIt" parameterType="String" resultType="User">
            select username
            from user
            where username like '%${value}%'
        </select>
    </mapper>

pojo/user

package pojo;
    
    import java.util.Date;
    import java.util.List;
    
    public class User {
    
        private Integer id;
        private String username;// 用户姓名
        private String sex;// 性别
        private Date birthday;// 生日
        private String address;// 地址
        private String uuid1;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
        public Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    
        @Override
        public String toString() {
            return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address="
                    + address + ", uuid1=" + uuid1 + "]";
        }
    
        public String getUuid1() {
            return uuid1;
        }
    
        public void setUuid1(String uuid1) {
            this.uuid1 = uuid1;
        }
    }

test   **必须先加载工厂实例，所以用before**

package test;
    
    import Mapper.UserMapper;
    import org.junit.Before;
    import org.junit.Test;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    import pojo.User;
    
    public class test01 {
        private ApplicationContext applicationContext;
        @Before
        public void init() {
          applicationContext = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
        }
        @Test
        public void test01(){
            UserMapper userMapper = applicationContext.getBean(UserMapper.class);
            User u = userMapper.getUserById(1);
            System.out.println(u);
    
        }
    
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70 4]  
总结：用Spring管理类，在Spring整合Mybatis中极其重要的包就是Spring-Mybatisxxx.jar可以查看里边源码查看需要注入什么值。Spring管理可以大大减少代码量，可以更清晰的展现代码的逻辑结构。

Mybatis框架就到此为止了，想要深入了解Mybatis框架可以去看一些更深入的东西，下一次就开始进入SpringMVC，ssm的最后一部分，学完之后会有一个项目实战可以来整合一下以前学的知识！

--------------------

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70]: /images/20220320/d66587913a274150936da8cea2d4d8d8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70 1]: /images/20220320/f44a9181f68a4a5c855b9d6fd3e907fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70 2]: /images/20220320/1e1122a1edc741a0a6ba8da9b83fd17c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70 3]: /images/20220320/02e059d04f9e4be2b20124e863e8d1a9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70 4]: /images/20220320/e5a0c2437dd44eafba17164ed91d9499.png