mybatis是一个半orm框架,内部封装了JDBC,将java对象与sql的动态参数形成最终的sql进行执行,将结果映射成java对象返回
mybatis的编程步骤
1、 创建 SqlSessionFactory
2、 通过 SqlSessionFactory 创建 SqlSession
3、 通过 sqlsession 执行数据库操作
4、 调用 session.commit()提交事务
5、 调用 session.close()关闭会话
mybatis的工作原理
1.读取 Mybatis 配置文件mybatis-config.xml 为 Mybatis 的全局配置文件，配置了 Mybatis 的运行环境等信息，例如数据库连接信息
2.构造会话工厂SqlSessionFactory
3.创建会话对象SqlSession
4.在SqlSession对象上调用方法来执行SQL语句
5.MyBatis将查询结果映射回Java对象
String resource = "mybatis-config.xml";
InputStream inputStream = MyBatisExample.class.getClassLoader().getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
List<User> users = sqlSession.selectList("com.example.mapper.UserMapper.selectAll");
for (User user : users) {
System.out.println(user);
}
} finally {
sqlSession.close();
}
#{}和${}的区别
#{}是预编译处理，${}是字符串替换
Mybatis 在处理#{}时，会将 sql 中的#{}替换为?号，调用 PreparedStatement 的set 方法来赋值
Mybatis 在处理{}替换成变量的值
使用#{}可以有效的防止 SQL 注入，提高系统安全性
mybatis 中resultType 和 ResultMap 的区别是什么?
如果数据库结果集中的列名和要封装实体的属性名完全一致的话用 resultType 属性
如果数据库结果集中的列名和要封装实体的属性名有不一致的情况用 resultMap 属
性，通过 resultMap 手动建立对象关系映射，resultMap 要配置一下表和类的一一对应关
系，所以说就算你的字段名和你的实体类的属性名不一样也没关系，都会给你映射出来。
mybaits的常用操作
模糊查询
SELECT * from sys_user where user_name like CONCAT('%','sha','%') #使用concat函数拼接字符串
SELECT * from sys_user where user_name like '%#{username}%'
批量添加
<insert id="batchInsertDeviceLease" parameterType="java.util.List">
<foreach collection="list" item="item" separator=";">
insert into t_device_lease
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="item.deviceId!=null">device_id,</if>
<if test="item.deviceNo!=null and item.deviceNo!=''">device_no,</if>
<if test="item.tenantId!=null">tenant_id,</if>
<if test="item.tenantName!=null and item.tenantName!=''">tenant_name,</if>
<if test="item.tenantNo!=null and item.tenantNo!=''">tenant_no,</if>
<if test="item.leaseCompanyId!=null">lease_company_id,</if>
<if test="item.leaseCompanyName!=null and item.leaseCompanyName!=''">lease_company_name,</if>
<if test="item.firstPartyId!=null">first_party_id,</if>
<if test="item.firstPartyName!=null and item.firstPartyName!=''">first_party_name,</if>
<if test="item.leaseTime!=null">lease_time,</if>
</trim>
<trim prefix="values(" suffix=")" suffixOverrides=",">
<if test="item.deviceId!=null">#{item.deviceId},</if>
<if test="item.deviceNo!=null and item.deviceNo!=''">#{item.deviceNo},</if>
<if test="item.tenantId!=null">#{item.tenantId},</if>
<if test="item.tenantName!=null and item.tenantName!=''">#{item.tenantName},</if>
<if test="item.tenantNo!=null and item.tenantNo!=''">#{item.tenantNo},</if>
<if test="item.leaseCompanyId!=null">#{item.leaseCompanyId},</if>
<if test="item.leaseCompanyName!=null and item.leaseCompanyName!=''">#{item.leaseCompanyName},</if>
<if test="item.firstPartyId!=null">#{item.firstPartyId},</if>
<if test="item.firstPartyName!=null and item.firstPartyName!=''">#{item.firstPartyName},</if>
<if test="item.leaseTime!=null">#{item.leaseTime},</if>
</trim>
</foreach>
</insert>
批量修改
<update id="batchUpdateDevice" parameterType="java.util.List">
<foreach collection="list" separator=";" item="item">
update t_device
<set>
<if test="item.itemId!=null">item_id=#{item.itemId},</if>
<if test="item.floorId!=null">floor_id=#{item.floorId},</if>
<if test="item.tenantId!=null">tenant_id=#{item.tenantId},</if>
<if test="item.tenantNo != null and item.tenantNo != ''">tenant_no = #{item.tenantNo},</if>
<if test="item.tenantName != null and item.tenantName != ''">tenant_name = #{item.tenantName},</if>
<if test="item.leaseCompanyId!=null">lease_company_id=#{item.leaseCompanyId},</if>
<if test="item.leaseCompanyName != null and item.leaseCompanyName != ''">lease_company_name = #{item.leaseCompanyName},</if>
<if test="item.firstPartyId!=null">first_party_id=#{item.firstPartyId},</if>
<if test="item.firstPartyName != null and item.firstPartyName != ''">first_party_Name= #{item.firstPartyName}</if>
</set>
where id=#{item.id}
</foreach>
</update>
批量查询
<select id="getTsSpecialTaskByIds" resultMap="specialTaskResult" parameterType="java.util.List">
select * from ts_special_task
where id in
<foreach collection="list" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</select>
批量删除
<delete id="deleteUserByIds" parameterType="Long">
delete from sys_user where sys_user.user_id in
<foreach collection="array" item="userId" open="(" separator="," close=")">
#{userId}
</foreach>
</delete>
获取自增的主键
#useGeneratedKeys表示当前使用自增功能的主键,keyProperty表示将要添加的实体类主键
<insert id="insertGroup" parameterType="com.xxmfl.iot.domain.Group" useGeneratedKeys="true" keyProperty="groupId">
insert into iot_group
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="groupName != null and groupName != ''">group_name,</if>
<if test="groupOrder != null">group_order,</if>
<if test="userId != null">user_id,</if>
<if test="userName != null and userName != ''">user_name,</if>
<if test="delFlag != null">del_flag,</if>
<if test="createBy != null">create_by,</if>
<if test="createTime != null">create_time,</if>
<if test="updateBy != null">update_by,</if>
<if test="updateTime != null">update_time,</if>
<if test="remark != null">remark,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="groupName != null and groupName != ''">#{groupName},</if>
<if test="groupOrder != null">#{groupOrder},</if>
<if test="userId != null">#{userId},</if>
<if test="userName != null and userName != ''">#{userName},</if>
<if test="delFlag != null">#{delFlag},</if>
<if test="createBy != null">#{createBy},</if>
<if test="createTime != null">#{createTime},</if>
<if test="updateBy != null">#{updateBy},</if>
<if test="updateTime != null">#{updateTime},</if>
<if test="remark != null">#{remark},</if>
</trim>
</insert>
mybatis分页插件的分页原理
在插件的拦截方法内拦截待执行的 sql，然后重写 sql，根据 dialect 方言，添加对应的物理分页语句和物理分页参数
mybatis的一对多和多对多
association适合一对一和多对一,collection适合多对多和一对多
<resultMap id="BaseUserMap" type="com.lxy.stock.domain.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<association property="dept" javaType="com.lxy.stock.domain.Dept">
<id column="dept_id" property="deptId"/>
<result column="dept_name" property="deptName"/>
</association>
<collection property="roleList" ofType="com.lxy.stock.domain.Role">
<id column="role_id" property="roleId"/>
<result column="role_name" property="roleName"/>
</collection>
</resultMap>
<select id="selectAllUserByRole" resultMap="BaseUserMap">
SELECT
*
FROM
t_user u
LEFT JOIN t_user_role ur ON u.id = ur.user_id
LEFT JOIN t_role r ON r.role_id = ur.role_id
</select>
<select id="selectAllUserByDept" resultMap="BaseUserMap">
select * from
t_user u left join t_dept d on u.dept_id=d.dept_id
</select>
