mybatis

Mybatis 单表CURD细节

1. ${} 与#{} 区别(面试题)

   1. ${} 拼接sql 造成sql注入 #{} 使用?占位

   如果作为值, 推荐使用#{}

   1. ${} 实现一些动态排序,使用

#{column}
select * from tb_userinfo order by ? desc
column: id 赋值
sql: select * from tb_userinfo order by 'id' desc
根据 'id'常数列排序
结果: 
[User{name='张三丰', gender='男', age=108, address='湖北', email='zhangsanfeng@163.com', qq='123456'}, User{name='李思琪', gender='女', age=21, address='上海', email='lisiqi@163.com', qq='12312321'}, User{name='李思思', gender='女', age=21, address='北京', email='lisisi@126.com', qq='3323422'}, User{name='李大海', gender='男', age=22, address='上海', email='lidahai@ss.com', qq='12212121'}, User{name='倪大红', gender='女', age=23, address='上海', email='asdsa@ss.com', qq='12312321'}, User{name='fsdsf', gender='男', age=43, address='北京', email='aadda@ww.com', qq='544554'}, User{name='wangwu', gender='女', age=18, address='上海', email='wangwu@163.com', qq='21321321'}, User{name='李四', gender='男', age=21, address='湖南', email='lisi@163.com', qq='222222'}, User{name='zhangsansan', gender='男', age=21, address='湖南', email='zhangsansan@163.com', qq='12321312'}, User{name='莫问归期', gender='女', age=21, address='北京', email='1234567@qq.com', qq='1234567'}, User{name='一个人挺好', gender='男', age=21, address='北京', email='sadsad@163.com', qq='2313128'}, User{name='张三', gender='男', age=21, address='长沙', email='zhangsan@qq.com', qq='1234678'}, User{name='张三', gender='男', age=21, address='长沙', email='zhangsan@qq.com', qq='1234678'}]
​

1. resultType: 不是表示方法的返回值类型, 查询到结果集中记录映射到java那个实体类, 写的实体类

too many paramNumber selectone所带所导致的结果

先后顺序：(先执行插入操作，再执行这个查询生成id的命令)

1. selectOne() 与selectList() 区别

   selectOne() 查询结果集最多有一行记录, 超出一行记录, 抛异常 TooManyResultsException

   

   selectList(): 查询结果集可以0 ,1 , 多条 返回的List<T>, 可以使用selectList() 替换selectOne()

1. 插入操作,

mysql提供: select LAST_INSERT_ID(); 查找生成id值

只能获取当前会话执行插入操作的产生的id

Mybatis的Dao层开发模式

1. 传统的Dao模式

编写一个Dao接口, 提供Dao实现类

package com.fs.dao;
​
import com.fs.entity.User;
​
import java.util.List;
​
public interface UserDao {/*** 根据主键查询* @param id* @return*/User selectById(Integer id);
​/*** 查询所有* @return*/List<User> selectAll();
​/*** 添加* @param user* @return*/int insert(User user);
​/*** 根据主键删除* @param id* @return*/int deleteById(Integer id);
​/*** 根据主键修改* @param user* @return*/int updateById(User user);
}
​

package com.fs.dao.impl;
​
import com.fs.dao.UserDao;
import com.fs.entity.User;
import com.fs.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
​
import java.util.List;
​
public class UserDaoImpl  implements UserDao {private SqlSession sqlSession;
​public UserDaoImpl() {this.sqlSession = MybatisUtil.getSqlSession();}
​@Overridepublic User selectById(Integer id) {User user = sqlSession.selectOne("UserDao.selectById",id);sqlSession.close();return user;}
​@Overridepublic List<User> selectAll() {List<User> users = sqlSession.selectList("UserDao.selectAll");sqlSession.close();return users;}
​@Overridepublic int insert(User user) {int row  = sqlSession.insert("UserDao.addUser",user);//提交事务sqlSession.commit();sqlSession.close();return row;}
​@Overridepublic int deleteById(Integer id) {int row = sqlSession.delete("UserDao.deleteById", id);sqlSession.commit();sqlSession.close();return row;}
​@Overridepublic int updateById(User user) {return 0;}
}
​

传统Dao方式问题:

1. 重复代码重复写

2. StatementId硬编码方式,

产生原因: Dao的实现类

2. Mapper代理模式 重点

Mybatis推荐,

编写Dao接口,不需要编写实现类, 由Mybatis动态代理自动生成的实现类对象

为了区分传统Dao方式, 把Dao 取名为Mapper 层: Mapper 接口 UserMapper

使用Mapper代理模式前提条件:

Sql映射文件的要求:

1. namespace必须写成Mapper接口全限定名

2. statement的id写成方法名

package com.fs.mapper;
​
import com.fs.entity.User;
​
import java.util.List;
​
public interface UserMapper {/*** 根据主键查询* @param id* @return*/User selectById(Integer id);
​/*** 查询所有* @return*/List<User> selectAll();
​/*** 添加* @param user* @return*/int insert(User user);
​/*** 根据主键删除* @param id* @return*/int deleteById(Integer id);
​/*** 根据主键修改* @param user* @return*/int updateById(User user);
}
​

sql映射文件:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fs.mapper.UserMapper">
​
​<select id="selectById" parameterType="int" resultType="com.fs.entity.User">select * from tb_userinfo where id = #{id}</select>
​<delete id="deleteById" parameterType="int">delete from tb_userinfo where id = #{id}</delete>
​<select id="selectAll" resultType="com.fs.entity.User">select * from tb_userinfo</select>
​<insert id="insert" parameterType="com.fs.entity.User">
​<selectKey resultType="int" order="AFTER"  keyProperty="id">select LAST_INSERT_ID()</selectKey>insert into tb_userinfo(name,gender,age,address,email,qq,photo) values(#{name},#{gender},#{age},#{address},#{email},#{qq},#{photo})</insert>
</mapper>