Mybatis框架 -- 连接池动态SQL 多表关联查询

清疚 2021-12-22 06:37 166阅读 0赞

#### 一、Mybatis 连接池 ####

**1. Mybatis中数据源分类**  
![Mybatis dataSource分类.png][Mybatis dataSource_.png]  
Mybatis将它自己的数据源分为三类：

*  `UNPOOLED`，不使用连接池的数据源
 *  `POOLED`，使用连接池的数据源
 *  `JNDI`，使用 JNDI 实现的数据源

**2. Mybatis中数据源的配置**

MyBatis在初始化时，解析此文件，根据`<dataSource>`的 type 属性来创建相应类型的的数据源DataSource，即：

*  `type="POOLED"`： MyBatis 会创建 Pooled DataSource 实例；
 *  `type="UNPOOLED"` ： MyBatis 会创建 Unpooled DataSource 实例；
 *  `type="JNDI"`： MyBatis 会从 JNDI 服务上查找 DataSource 实例，然后返回使用。

#### 二、Mybatis动态SQL ####

**1. `<if>`**

<select id="findByCondition" parameterType="queryvo" resultType="com.zz.entity.User">
        select * from user where 1=1
        
        <if test="user!= null and user.id != 0">
            and id=#{user.id}
        </if>
        <if test="user!= null and user.username != null">
            and username=#{user.username}
        </if>
    </select>

**2. `<where>`**

<select id="findByCondition" parameterType="queryvo" resultType="com.zz.entity.User">
        select * from user
        <where>
            
            <if test="user!= null and user.id != 0">
                and id=#{user.id}
            </if>
            <if test="user!= null and user.username != null">
                and username=#{user.username}
            </if>
        </where>
    </select>

**3. `<foreach>`**

<select id="findByCondition" parameterType="queryvo" resultType="com.itheima.entity.User">
        
        select * from user
        <where>
            
            <if test="user!= null and user.id != 0">
                and id=#{user.id}
            </if>
            <if test="user!= null and user.username != null">
                and username=#{user.username}
            </if>
            
            
            <if test="ids!=null and ids.size()>0">
                <foreach item="num" collection="ids" open="and id IN (" separator="," close=")">
                    #{num}
                </foreach>
            </if>
        </where>
    </select>

**4. `<include>`**

<sql id="selectUser">
        select * from user
    </sql>
    
    
    <select id="findByCondition" parameterType="queryvo" resultType="user">
    
        
        <include refid="selectUser"></include>
        <where>
            
            <if test="user!= null and user.id != 0">
                and id=#{user.id}
            </if>
            <if test="user!= null and user.username != null">
                and username=#{user.username}
            </if>
        </where>
    </select>

注意：如果引用其它mapper.xml的sql片段，则在引用时需要加上namespace，如：`<include refid="namespace.sql 片段"/>`

#### 三、Mybatis的多表关联查询 ####

**1. 一对一**

实体类：

public class Account { 
    
        private int accountId;
        private double money;
        //账户与用户一对一
        private User user;
    	
    	...
    }
    
    public class User { 
    
        private int id;
        private String username;
        private String sex;
        private String address;
        private Date birthday;
        //一个用户有多个账户
        private List<Account> accounts;
    
    	...
    }

`AccountDao`接口：

public interface AccountDao { 
    
        List<Account> findAccountByUserId(int userId);
    }

`AccountDao.xml`：

<mapper namespace="com.zz.dao.AccountDao">
    
        <resultMap id="userResultMap" type="account">
            
            <id property="accountId" column="accountId" />
            <result property="money" column="money"/>
    
            
            
            <association property="user" javaType="com.zz.entity.User" column="uid">
                
                <id property="id" column="id"/>
                <result property="username" column="username"/>
                <result property="sex" column="sex"/>
                <result property="address" column="address"/>
                <result property="birthday" column="birthday"/>
            </association>
        </resultMap>
        
        
        <select id="findAccountByUserId" parameterType="int" resultMap="userResultMap">
            SELECT a.*,u.* FROM account a INNER JOIN USER u ON a.uid=u.id where a.uid=#{userId}
        </select>
    </mapper>

**2. 一对多**

`UserDao`接口：

public interface UserDao { 
    
        //查询全部用户，同时还要查询出用户对应的全部账户信息)
        List<User> findAll();
    }

`UserDao.xml`：

<mapper namespace="com.zz.dao.UserDao">
    
        
        <resultMap id="accountResultMap" type="user">
            
            <id property="id" column="id"/>
            <result property="username" column="username"/>
            <result property="sex" column="sex"/>
            <result property="address" column="address"/>
            <result property="birthday" column="birthday"/>
            
            
            <collection property="accounts" ofType="com.zz.entity.Account" >
                <id property="accountId" column="accountId" />
                <result property="money" column="money"/>
            </collection>
        </resultMap>
    
        
        <select id="findAll" resultMap="accountResultMap">
            SELECT u.*,a.* FROM USER u LEFT JOIN account a  ON a.uid=u.id
        </select>
    </mapper>

**3. 多对多**

多对多可以理解为双向一对多

*  [MyBatis 多对多 中间表插入数据][MyBatis _]

#### 四、常见SQL ####

**1. 注解中判断某个字符串参数是否为null或者空字符串**

<when>:
    @Select({
       "<script>",
            "select * from user_info where 1=1",
            "<when test='name!=null and name!=\"\"'>",
            "and name = #{name}",
            "</when>",
            "<when test='password!=null and password!=\"\"'>",
            "and password= #{password}",
            "</when>",
            "</script>"})
    public User selectByNamePassword(String name,String password);
    
    <if>:
    @Select({
       "<script>",
            "select * from user_info where 1=1",
            "<if test='name!=null and name!=\"\"'>",
            "and name = #{name}",
            "</if>",
            "<if test='password!=null and password!=\"\"'>",
            "and password= #{password}",
            "</if>",
            "</script>"})
    public User selectByNamePassword(String name,String password);

**2. Mybatis插入数据时获取自增的主键Id**

*  **ServiceImpl**

@Override
    public void insertProject(String areaName, String project, String status) { 
    
        try { 
        	// 查找areaId,未找到则插入area表中
            Integer areaId = areaMapper.findAreaId(areaName);
            if (areaId == null) { 
                Area area = new Area();
                area.setAreaName(areaName);
                area.setStatus(0);
                areaMapper.insertArea(area);
                // 获取主键
                areaId = area.getAreaId();
            }
            projectMapper.insertProject(project, areaId, status);
        } catch (Exception e) { 
            throw new RuntimeException(e);
        }
    }

/** * 表AREA_T插入数据,获取自增主键 AreaId * keyProperty: 主键实体类属性名 keyColumn: 主键表列名 */
    @Insert("insert into AREA_T(area_name,del_flag) values(#{areaName},#{status});")
    @Options(useGeneratedKeys = true, keyProperty = "areaName", keyColumn = "area_name")
    void insertArea(Area area);

注：现在不用注解即可，MyBatis插入area后，area的主键会被MyBatis返回并赋值，直接用get方法即可获取到主键ID，即使不是自增主键也可获取。

[Mybatis dataSource_.png]: /images/20211220/e2b69fa5bae54f8c84e414f24c12c42b.png
[MyBatis _]: https://blog.csdn.net/u010857795/article/details/71512044