MyBatis主键生成与动态SQL-优快云博客

本文链接：https://blog.youkuaiyun.com/bian1721/article/details/102599012

获取数据库生成的主键

使用场景

需求:我们很多时候有这种需求，向数据库插入一条记录后，希望能立即拿到这条记录在数据库中的主键值。

方式一:仅支持自动增长的数据库( useGeneratedKeys )

注意:Oracle不支持自动增长的，通过序列器产生的唯一的主键

1.建立新的模块,创建实体类和表结构

create table t_user(
	uid int primary key auto_increment,
	username varchar(100),
    gender varchar(20)
);
insert into t_user values(1,'aaa','男');
insert into t_user values(2,'bbb','女');

package com.sdfzzyxy.domain;

import java.io.Serializable;

public class User implements Serializable {
    private  Integer uid;
    private  String username;

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", username='" + username + '\'' +
                '}';
    }
}

2.编写dao接口

package com.sdfzzyxy.Dao;

import com.sdfzzyxy.domain.User;

public interface MapperDao {
    void  saveUser1(User user);

}

3.添加映射文件

<!--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="com.sdfzzyxy.Dao.MapperDao">
    <!--
    userGenerateKeys:true表示数据库生成的主键
    keyColumn:主键字段名称
    keyProperty:指定生成的主键封装到参数的哪个属性中

	注意:此方式 仅仅适用于底层支持主键自增的数据库!!!
    -->
    <insert id="saveUser1" useGeneratedKeys="true" 		keyColumn="uid" keyProperty="uid">
        insert  into  t_user (username) values(#{username})
    </insert>
</mapper>

4.注册映射文件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bNHdaTVH-1571275223681)(E:\学习文件\每日笔记\day47_mybtis.assets\1571141929003.png)]

5，测试代码

import com.sdfzzyxy.Dao.MapperDao;
import com.sdfzzyxy.domain.User;
import com.sdfzzyxy.util.BaseTester;
import org.junit.Test;

public class MapperDaoTest extends BaseTester {
    @Test
    public  void  testSaveUser1(){
        User user=new User();
        user.setUsername("小民");
        MapperDao mapperDao=sqlSession.getMapper(MapperDao.class);
        mapperDao.saveUser1(user);
        sqlSession.commit();
        System.out.println(user);
    }
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4O8n8Dnw-1571275223682)(E:\学习文件\每日笔记\day47_mybtis.assets\1571142052517.png)]

方式二:试用所有的数据库(selectKey)

通过调用数据库自身的获取生成的主键的语句实现

1.编写dao接口

//MapperDao.java
void  saveUser2(User user);

2.修改映射文件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CapWq6Om-1571275223683)(E:\学习文件\每日笔记\day47_mybtis.assets\1571143149161.png)]

补充：

<!--Oracle获取主键-->
<selectKey keyProperty="uid" resultType="int" order="Before">
    
          SELECT SEQ_T_OPERATION_LOG.nextval AS id FROM dual 
    
        </selectKey>
    </insert>

测试

import com.sdfzzyxy.Dao.MapperDao;
import com.sdfzzyxy.domain.User;
import com.sdfzzyxy.util.BaseTester;
import org.junit.Test;

public class MapperDaoTest extends BaseTester {

    @Test
     public  void  testSaveUser2(){
        User user=new User();
        user.setUsername("公交");
        MapperDao mapperDao = sqlSession.getMapper(MapperDao.class);
        mapperDao.saveUser2(user);
        sqlSession.commit();
        System.out.println(user);
    }
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EKMOFCvU-1571275223683)(E:\学习文件\每日笔记\day47_mybtis.assets\1571143366641.png)]

动态SQL

findByCondtion(User user):根据传入的user对象进行查询，将不为空的属性作为查询条件
update(User user);根据传入的user对象进行更新，将不为空的属性更新到数据库
save(User user):根据传入的user对象进行保存，将不为空的属性插入到数据库

根据程序运行时的不同条件产生不同的SQL，这就用到了动态SQL。

动态SQL标签：

if choose(when,otherwise) 用于条件判断(类似java的swtich功能)
trim(where,set) 用于去除分割符(格式化标签）
foreach 用户便利循环

条件判断

if

第一种情况: if 在where语句中的使用，实现动态条件判断

作用：用来做条件判断，接受一个ognl表达式，返回一个boolean值.，如果为true，则if标签的主体内容执行，否则不执行。

<if test="OGNL表达式">sql片段</if>

1.编写dao接口

 List<User>findUsers(User vo);

2.编写映射文件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5pW8SxqF-1571275223683)(E:\学习文件\每日笔记\day47_mybtis.assets\1571191909413.png)]

3.编写测试类

   @Test
     public  void  testFindUsers(){
        User user=new User();
       user.setUid(1);
        MapperDao mapperDao = sqlSession.getMapper(MapperDao.class);
        List<User> users = mapperDao.findUsers(user);
        for (User user1 : users) {
            System.out.println("---------");
            System.out.println(user1);

        }
    }

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iw1pVIGh-1571275223685)(E:\学习文件\每日笔记\day47_mybtis.assets\1571192115637.png)]

第二种情况: if 在set语句中的使用，实现动态条件更新

1.编写dao接口

void  updateUser(User  user);

2.映射文件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yCEamadm-1571275223685)(E:\学习文件\每日笔记\day47_mybtis.assets\1571192885662.png)]

3.测试类

   @Test
    public  void  testUpdateUser(){
        User user=new User();
        user.setUid(3);
        user.setGender("男");
        MapperDao mapper = sqlSession.getMapper(MapperDao.class);
        mapper.updateUser(user);
        sqlSession.commit();
    }

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ylC6AzPX-1571275223686)(E:\学习文件\每日笔记\day47_mybtis.assets\1571193183390.png)]

第三种情况: if 在insert语句中的使用，实现动态条件插入

1.编写dao

void insertUser(User user);

2.修改映射文件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OTHkCyUp-1571275223686)(E:\学习文件\每日笔记\day47_mybtis.assets\1571195993923.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bvcpOq6H-1571275223687)(E:\学习文件\每日笔记\day47_mybtis.assets\1571196025042.png)]

循环遍历foreach

作用：循环遍历输出主体内容

应用场景是SQL中的in语法中**，select * from user where uid in (1,2,3)** 在这样的语句中，传入的参数部分必须依靠 foreach遍历才能实现。

支持的参数类型：

集合(List):默认应用的占位符名称为：collection或list
数组：默认应用的占位符名称:array
Set: 默认应用的占位符名称 collection
Map:默认应用的占位符名称 _parameter

以上参数的默认占位符名称，都可以在dao方法中使用**@Param注解**指定自己的名称

foreach的选项 
collection：数据源【重点关注这一项，它的值会根据出入的参数类型不同而不同】 open:开始遍历之前的拼接字符串
close:结束遍历之后的拼接字符串 
separator:每次遍历之间的分隔符
item:每次遍历出的数据
index:遍历的次数，从0开始

1.编写dao接口

    List<User> findUser1(Integer[] uids);
    List<User> findUser2(List<Integer>uids);
    List<User> findUser3(Set<Integer> uids);
    List<User> findUser4(Map<String ,Integer> uids);

2.映射文件

 <select id="findUser1" resultType="com.sdfzzyxy.domain.User">
        select * from  t_user where uid in
        <foreach collection="array" item="uid" open="(" separator="," close=")">
            #{uid}
        </foreach>
    </select>
    <select id="findUser2" resultType="com.sdfzzyxy.domain.User">
        select  * from t_user where uid in
        <foreach collection="list" item="uid" open="("  separator="," close=")">
            #{uid}
        </foreach>
    </select>
    <select id="findUser3" resultType="com.sdfzzyxy.domain.User">
        select  * from t_user where uid in 
        <foreach collection="collection" item="uid" open="(" separator="," close=")">
            #{uid}
        </foreach>
    </select>
    
    <select id="findUser4" resultType="com.sdfzzyxy.domain.User">
        select * from t_user where uid in
        <foreach collection="_parameter" item="map" open="(" separator="," close=")">
            #{map}
        </foreach>
    </select>

3.测试文件

   @Test
    public  void  findUsers1(){
        Integer[] uids={1,2,3,4};
        MapperDao mapperDao = sqlSession.getMapper(MapperDao.class);
        List<User> user1 = mapperDao.findUser1(uids);
        for (User user : user1) {
            System.out.println("-------");
            System.out.println(user);
        }
    }
    @Test
    public  void  findUsers2(){
        List<Integer> uids=new ArrayList<>();
        uids.add(1);
        uids.add(2);
        uids.add(3);
        uids.add(4);
        MapperDao mapperDao = sqlSession.getMapper(MapperDao.class);
        List<User> user2 = mapperDao.findUser2(uids);
        for (User user : user2) {
            System.out.println("-------");
            System.out.println(user);
        }
    }
    @Test
    public  void  FindUser3(){
        Set<Integer> uids=new HashSet<>();
        uids.add(1);
        uids.add(2);
        uids.add(3);
        uids.add(4);
        MapperDao mapper = sqlSession.getMapper(MapperDao.class);
        List<User> user3 = mapper.findUser3(uids);
        for (User user : user3) {
            System.out.println("------");
            System.out.println(user);
        }
    }
    @Test 
    public  void  findUser4(){
        Map<String,Integer>uids=new HashMap<>();
        uids.put("a",1);
        uids.put("b",2);
        uids.put("c",3);
        uids.put("d", 4);
        MapperDao mapper = sqlSession.getMapper(MapperDao.class);
        List<User> user4 = mapper.findUser4(uids);
        for (User user : user4) {
            System.out.println("------");
            System.out.println(user);
        }
    }

choose when otherwise

实现类似java中的switch功能

1.编写dao接口

List<User> findUser5(User vo);

2.添加映射文件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YoJpHece-1571275223687)(E:\学习文件\每日笔记\day47_mybtis.assets\1571207834231.png)]

3.测试

    @Test
    public  void  finUser5(){
        User condition=new User();
        /*condition.setUid(1);*/
        condition.setUsername("aaa");
        MapperDao mapperDao = sqlSession.getMapper(MapperDao.class);
        List<User> user5 = mapperDao.findUser5(condition);
        for (User user : user5) {
            System.out.println("---");
            System.out.println(user);
        }
    }

格式化标签

where:

如果where包含的标签中有返回的true的语句,Mybatis会在语句****中插入一个’where’
如果标签的返回内容是以AND 或 OR 开头 ,Mybatis会将其剔除

 <!--
where 格式化标签:
1.没有主体内容,什么都不执行-->
<select id="findUsers" parameterType="com.sdfzzyxy.domain.User"
            resultType="com.sdfzzyxy.domain.User">
        select  * from t_user
        <where>
            <if test="uid!=null">
  <!--2:有主体内容,会在语句末尾自动加上where 关键字 并删除第一个and-->
                and  uid=#{uid}
            </if>
            <if test="username!=null">
                and  username=#{username}
            </if>
        </where>
    </select>

set

set标签的作用:去掉set语句最后的逗号(,)

注意:set标签中必须保证至少有一个语句

修改映射文件:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PGG7p8nh-1571275223688)(E:\学习文件\每日笔记\day47_mybtis.assets\1571210453477.png)]

多表的连接查询

多表设计及类之间的关联关系

一对多:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kWxGNFmr-1571275223688)(E:\学习文件\每日笔记\day47_mybtis.assets\1571214837797.png)]

多对多:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sHqeO16F-1571275223689)(E:\学习文件\每日笔记\day47_mybtis.assets\1571215320885.png)]

准备数据

create table t_user(
	uid int primary key auto_increment,
	username varchar(100),
    gender varchar(20)
);
insert into t_user values(1,'aaa','male');
insert into t_user values(2,'bbb','female');
create table t_account(
	aid int primary key auto_increment,
	number varchar(100),
	balance float(10,2),
	uid int,
	constraint fk_user_uid foreign key(uid) references t_user(uid)
);
insert into t_account values(1,'6666',10000,1);
insert into t_account values(2,'7777',10000,1);
insert into t_account values(3,'8888',10000,2);

create table t_role(
	rid int primary key auto_increment,
	name varchar(100),
	description varchar(255)
);
create table t_user_role(
	uid int,
	rid int,
	primary key(uid,rid),
	constraint fk_user_uid1 foreign key(uid) references t_user(uid),
	constraint fk_role_rid foreign key(rid) references t_role(rid)
);
insert into t_role values(1,'入库员','in'),(2,'出库员','out');
insert into t_user_role values(1,1),(1,2),(2,2);

package com.sdfzzyxy.domain;

import java.io.Serializable;

public class Account implements Serializable {
    private  Integer aid;
    private  String number;
    private  Float balance;
    private  User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Integer getAid() {
        return aid;
    }

    public void setAid(Integer aid) {
        this.aid = aid;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Float getBalance() {
        return balance;
    }

    public void setBalance(Float balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account{" +
                "aid=" + aid +
                ", number='" + number + '\'' +
                ", balance=" + balance +
                '}';
    }
}

2。映射文件

方式1： 采用外键+别名的形式进行映射

<select id="findAllAccountWithUser" resultType="com.sdfzzyxy.domain.Account">
    <!--自动映射，借助Sql别名-->
select  a.*,u.uid 'user.uid',u.username 'user.username' from t_account a left  join  t_user u on a.uid=u.uid;
</select>

方式2：采用resultMap形式进行映射

 <!--手动映射-->
    <resultMap id="accountMap" type="com.sdfzzyxy.domain.Account">
        <id column="aid" property="aid"/>
        <result column="number" property="number"/>
        <result column="balance" property="balance"/>
        <result column="uid" property="user.uid"/>
        <result column="username" property="user.username"/>
    </resultMap>
    <select id="findAllAccountWithUser2" resultMap="accountMap">
        select  * from t_account a  left join t_user u on a.uid=u.uid
    </select>

方式3: 采用resultMap + association 形式进行映射

  <!--方式三:通过association标签-->
    <resultMap id="accountsMap" type="com.sdfzzyxy.domain.Account">
        <id column="aid" property="aid"/>
        <result column="number" property="number"/>
        <result column="balance" property="balance"/>
        <association property="user" column="uid" javaType="com.sdfzzyxy.domain.User">
            <id column="uid" property="uid"/>
            <result column="username" property="username"/>
        </association>
    </resultMap>
    <select id="findAllAccountWithUser3" resultMap="accountsMap">
        select * from t_account a left join t_user u on a.uid=u.uid
    </select>

根据少的一方查询多的一方

1.建立用户和账户间的关联关系

package com.sdfzzyxy.domain;

import java.io.Serializable;
import java.util.List;

public class User implements Serializable {
    private  Integer uid;
    private  String username;
    private  String gender;
    private List<Account>accounts;

    public List<Account> getAccounts() {
        return accounts;
    }

    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", username='" + username + '\'' +
                '}';
    }
}

2。编写dao接口

    List<User> findAllUsersWithAccounts();

3.添加映射文件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n2k5e8D1-1571275223689)(E:\学习文件\每日笔记\day47_mybtis.assets\1571233862595.png)]

测试：

 @Test
    public  void  testFindAllUsersWithAccounts(){
        MapperDao mapperDao=sqlSession.getMapper(MapperDao.class);
        List<User> users = mapperDao.findAllUsersWithAccounts();
        for (User user : users) {
            System.out.println("--------");
            System.out.println("user:"+user);
            System.out.println("account:"+user.getAccounts());
        }
    }

多不多查询

查询用户用时查询对应的角色

1.设计角色实体类,并建立用户的角色关联关系

package com.sdfzzyxy.domain;

import java.io.Serializable;

public class Role implements Serializable {
    private  Integer rid;
    private  String name;
    private  String description;

    public Integer getRid() {
        return rid;
    }

    public void setRid(Integer rid) {
        this.rid = rid;
    }

    public String getName() {
        return name;
    }

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

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7PdGipA5-1571275223689)(E:\学习文件\每日笔记\day47_mybtis.assets\1571234307333.png)]

2.添加接口

    List<User>findAllUsersWithRoles();

3.修改映射文件

    <resultMap id="userRoleMap" extends="userMap" type="com.sdfzzyxy.domain.User">
        <collection property="roles" column="uid" ofType="com.sdfzzyxy.domain.Role">
            <id column="rid" property="rid"/>
            <result column="name" property="name"/>
            <result column="description" property="description"/>

        </collection>
    </resultMap>
    <select id="findAllUsersWithRoles" resultMap="userRoleMap">
        select  u.*,r.* from t_user u left join  t_user_role ur on u.uid=ur.uid left join  t_role r on ur.rid=r.rid
    </select>

4.测试

    @Test
    public void testFindAllUsersWithRoles(){
        MapperDao mapper = sqlSession.getMapper(MapperDao.class);
        List<User> users = mapper.findAllUsersWithRoles();
        for (User user : users) {
            System.out.println("---------");
            System.out.println("user:"+user);
            System.out.println("role:"+user.getRoles());
        }
    }

     <result column="name" property="name"/>
        <result column="description" property="description"/>

    </collection>
</resultMap>
<select id="findAllUsersWithRoles" resultMap="userRoleMap">
    select  u.*,r.* from t_user u left join  t_user_role ur on u.uid=ur.uid left join  t_role r on ur.rid=r.rid
</select>


4.测试

```java
    @Test
    public void testFindAllUsersWithRoles(){
        MapperDao mapper = sqlSession.getMapper(MapperDao.class);
        List<User> users = mapper.findAllUsersWithRoles();
        for (User user : users) {
            System.out.println("---------");
            System.out.println("user:"+user);
            System.out.println("role:"+user.getRoles());
        }
    }