ORM 框架
object relationship mapping
java对象 关系型数据库 映射
定义java对象与数据库表之前的映射关系,让增删改查操作变得简洁
1)在pom中加入mybatis
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>x.x.x</version>
</dependency>
2)编写mybatis配置文件
mybatis-config.xml
连接数据库 url , driver , username , password
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/eshop?serverTimezone=GMT%2B8&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&rewriteBatchedStatements=true&useCursorFetch=true&defaultFetchSize=100&allowPublicKeyRetrieval=true"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
</mappers>
</configuration>
3) 编写实体类和数据库表
create table user(
username varchar(20) primary key ,
passowrd varchar(20) not null
);
4) 编写映射文件
主要管理sql语句
习惯是 实体类名Mapper.xml
例如 :UserMapper.xml
ProductMapper.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">
<!-- namespace="mapper的包名.mapper的文件名" 主要是防止sql名字冲突 -->
<mapper namespace="com.westos.mapper.UserMapper">
<!-- id="SQL语句的唯一标识"
parameterType="sql语句需要的参数类型"
-->
<!-- User(username,password) -->
<insert id="insert" parameterType="com.westos.entity.User">
insert into user (username, password) values (#{username},#{password})
</insert>
<update id="update" parameterType="实体类型"> 更新sql, #{实体属性值} </update>
<delete id="delete" parameterType="基本类型"> 删除sql, #{任意字符串} </delete>
<select id="查询单个" parameterType="查询条件类型" resultType="实体类型"> 查询sql </select>
<select id="查询多个" parameterType="查询条件类型" resultType="实体类型"> 查询sql </select>
</mapper>
把映射文件的位置加入mybatis-config.xml中
<mapper resource="包名/XXMapper.xml" />
5) java 代码
public class TestStudent {
static SqlSessionFactory factory;
static {
InputStream is = null;
try {
is = Resources.getResourceAsStream("mybatis-config.xml");
factory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void insert() {
SqlSession sqlSession = factory.openSession();
User user =new User();
user.setUsername("root");
user.setPassword("root");
sqlSession.insert("com.westos.mapper.UserMapper.insert", user);
sqlSession.commit();
sqlSession.close();
}
}
常见错误
Mapped Statements collection does not contain value for com.westos.mapper.UserMapper.selectAll
可能原因1: <mapper>
没有加入到 mybatis-config.xml配置文件中
原因2: namespace 或id 写错了
Cause: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. Cause: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias 'com.westos.aaa.User'. Cause: java.lang.ClassNotFoundException: Cannot find class: com.westos.aaa.User
检查类型名称是否写对了
调试技巧
1.debug调试
2.单元测试工具 junit
1) 添加 junit的jar包
2) 在无返回值,无参数的方法前添加 "@Test "
3.通过日志包
让mybatis中的sql语句显示出来
日志包(将程序运行过程中的重要信息记录为日志)
1) 添加日志jar包
<dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.3</version> </dependency>
2) 日志配置文件
logback.xml 把它放入 src/main/resources目录
mybatis 进阶
1 . 新增操作(自增)
1) 数据库
create table student(
id int primary key auto_increment,
name varchar(20),
age int
);
2) StudentMapper.xml
<!--
将数据库插入后生成的id值,同步到java对象上
useGeneratedKeys="是否使用由数据库生成的主键"
keyColumn="主键列的名称"
keyProperty="主键要存入哪个属性"
-->
<insert id="insert" parameterType="com.westos.entity.Student"
useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into student (id, name) values (null, #{name})
</insert>
2 . 动态sql (foreach)
删除操作:一次删除多条记录
delete from student where id in(1);
delete from student where id in(1, 2);
delete from student where id in(1, 2, 3);
// java.util.List -> 简写为 list
<!-- list (1,2,3)
collection="要遍历的集合"
item="临时变量名称"
open="循环之前的符号"
close="循环之后的符号"
separator="每个元素的分隔符"
delete from student where id in (1, 2, 3)
-->
<delete id="delete" parameterType="list">
delete from student where id in
<foreach collection="list" item="i" open="(" close=")" separator=",">
#{i}
</foreach>
</delete>
3. 动态sql (if)
Map map = ...
map.put("name", "张%");
map.put("minAge", 10);
map.put("maxAge", 20);
select * from student where name=#{name}
select * from student where age between #{minAge} and #{maxAge}
select * from student where name=#{name} and age between #{minAge} and #{maxAge}
select * from student
// java.util.Map 简写为 map
<select id="selectByCondition" parameterType="map" resultType="com.westos.entity.Student">
select * from student
<where>
<if test="name != null">
and name=#{name}
</if>
<if test="minAge != null && maxAge != null">
and age between #{minAge} and #{maxAge}
</if>
</where>
</select>
动态更新
update student set name=#{}, age=#{} where id=#{}
希望实现修改哪列就在update中出现响应的set语句,而不是出现所有的列
update student set name=#{} wehre id=#{}
update student set age=#{} wehre id=#{}
<update id="update" parameterType="com.westos.entity.Student">
update student
<set>
<if test="name != null">
name = #{name},
</if>
<if test="age != 0">
age = #{age},
</if>
</set>
where id = #{id}
</update>
4 . 分页查询
1 ) 物理分页 (使用sql语句实现分页)
limit 下标,数量
缺点 : 不通用,数据库不同sql语法有差异:
mysql, limit
sqlserver, top, row_number()
oracle, rownum
<!-- map
.put("m", 下标);
.put("n", 数量);
-->
<select id="selectByPage" parameterType="map" resultType="com.westos.entity.Student">
select * from student limit #{m}, #{n}
</select>
```
HashMap<String, Integer> map = new HashMap<String, Integer>();
map.put("m",5);
map.put("n",5);
List<Student> list = sqlSession.selectList("com.westos.mapper.StudentMapper.selectByPage", map);
for (Student stu : list) {
System.out.println(stu);
}
2 ) 逻辑分页(把所有记录都查出来,用jdbc代码实现分页)
优点 : 通用,sql代码都是查询所有
缺点 : 效率低, 适合数据较少的情况
<!-- 逻辑分页 -->
<select id="selectLogical" resultType="com.westos.entity.Student">
select * from student
</select>
// rowBounds一定要作为第三个参数
List<Student> list = sqlSession.selectList("com.westos.mapper.StudentMapper.selectLogical", null,
new RowBounds(5, 5));
for (Student student : list) {
System.out.println(student);
}
5 . 表和实体类不匹配
1 ) sql语句
create table teacher (
id int primary key auto_increment,
first_name varchar(20),
last_name varchar(20)
);
2) TeacherMapper.xml
<!-- 方法1: 可以使用列别名来解决不一致问题 -->
<select id="selectOne" parameterType="int" resultType="com.westos.entity.Teacher">
select id,first_name firstName,last_name lastName from teacher where id = #{id}
</select>
<!-- 方法2: 使用 resultMap 代替 resultType完成映射 -->
<select id="selectOne" parameterType="int" resultMap="aaa">
select id, first_name, last_name from teacher where id = #{id}
</select>
<!-- type="实体对象的类型"
id 标签用来映射主键
result 标签用来映射其它列
-->
<resultMap id="aaa" type="com.westos.entity.Teacher">
<!-- column="列名" property="属性名" -->
<id column="id" property="id"></id>
<result column="first_name" property="firstName"></result>
<result column="last_name" property="lastName"></result>
</resultMap>
6 . 连接查询的映射
商品和类别
select * from product p inner join category c on p.category_id = c.id where p.id=1;
<!-- 把连接查询映射到两个有关系的实体类上 -->
<select id="selectById" parameterType="int" resultMap="bbb">
select p.id, p.name, p.price, c.id cid, c.name cname
from product p inner join category c on p.category_id = c.id where p.id=#{id}
</select>
<!-- association 关联 -->
<resultMap id="bbb" type="com.westos.entity.Product">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="price" property="price"></result>
<!-- property="关系属性名" -->
<association property="category" javaType="com.westos.entity.Category">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
</association>
</resultMap>
```
7. mybatis 中的缓存
1) 一级缓存
每个sqlsession都有一个一级缓存,只要sql语句和参数相同,只有第一次查询数据库,并且会把查询结果放入一级缓存
之后的查询,就直接从一级缓存中获取结果
一级缓存的作用范围,只限于一个sqlsession
2) 二级缓存
所有sqlSession共享的缓存
一级缓存无需配置,而二级缓存需要配置
<!-- 开启 ProductMapper的二级缓存, 会让本mapper内的所有select利用二级缓存-->
<cache/>
二级缓存的意义是减少与数据库的交互,从而提升程序的性能
3) 缓存失效
只要是执行了增,删,改的操作,缓存就应该失效,仍然从数据库查询得到最新结果
4) 二级缓存适用场景
当数据的查询远远多于修改时, 才有启用二级缓存的必要
8 . `#{}` 与 `${}` 的区别
区别1:
`#{}` 生成的sql语句是用?占位符的方式, 可以防止sql注入攻击
`${}` 生成的sql语句是直接把值进行了字符串的拼接, 有注入攻击漏洞
区别2:
`${}` 可以进行运算 `#{}` 不能运算
区别3:
`#{}` 在sql语句中只能替换值, 不能是表名,列名,关键字
`${}` 可以替换sql语句的任意部分