Mybatis学习笔记——狂神说
1.常见错误
1.1 配置文件没有注册
org.apache.ibatis.binding.BindingException: Type interface com.candy.dao.UserDao is not known to the MapperRegistry.
解决方法:
Mybatis-config.xml文件中没有配置mapper文件
2.常用配置
2.1导入资源(pom.xml)
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
</build>
2.2增删改查
2.2.1一般流程
1.编写接口方法
public interface UserMapper {
//查询所有用户
List<User> getUserList();
//根据ID查询用户
User getUserById(int id);
//添加一个用户
int addUser(User user);
//修改用户信息
int updateUser(User user);
//删除一个用户
int delUser(int id);
}
2.编写对应mapper中的sql语句
<mapper namespace="com.candy.dao.UserMapper">
<select id="getUserList" resultType="com.candy.pojo.User">
select * from mybts.user
</select>
<select id="getUserById" resultType="com.candy.pojo.User" parameterType="int">
select * from mybts.user where id = #{id}
</select>
<insert id="addUser" parameterType="com.candy.pojo.User" >
insert into mybts.user (id,name,pwd) values (#{id},#{name},#{pwd})
</insert>
<update id="updateUser" parameterType="com.candy.pojo.User">
update mybts.user
set name = #{name},pwd = #{pwd}
where id = #{id};
</update>
<delete id="delUser" parameterType="int">
delete from mybts.user
where id = #{id}
</delete>
</mapper>
3.测试
@Test
public void test4(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.updateUser(new User(4,"翔霖","123456"));
sqlSession.commit();
sqlSession.close();
}
2.2.2一定要记得提交事务(增、删、改)
sqlSession.commit();
2.2.3模糊查询
List<User> getUserLike(String value);
<select id="getUserLike" resultType="com.candy.pojo.User">
select * from mybts.user where name like #{value}
或者(下面的Java语句中就不需要加%)
select * from mybts.user where name like “%”#{value}“%”
</select>
@Test
public void test7(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUserLike("%文%");
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
2.2.4万能map
如果实体类参数过多,我们应该考虑使用map
int addUser2(Map<String,Object> map);
<insert id="addUser2" parameterType="map" >
insert into mybts.user (id,name,pwd) values (#{userId},#{userName},#{passWord})
</insert>
@Test
public void test6(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String,Object> map = new HashMap<String,Object>();
map.put("userId",6);
map.put("userName","文轩");
map.put("passWord","4399");
mapper.addUser2(map);
System.out.println("添加成功!");
sqlSession.commit();
sqlSession.close();
}
2.3parameterType(mapper.xml)
2.3.1parameterType=“map”
map传递参数,直接在sql语句中取出key值
<insert id="addUser2" parameterType="map" >
insert into mybts.user (id,name,pwd) values (#{userId},#{userName},#{passWord})
</insert>
2.3.2parameterType=“实体类”
对象传递参数,直接在sql语句中取对象属性
<update id="updateUser" parameterType="com.candy.pojo.User">
update mybts.user
set name = #{name},pwd = #{pwd}
where id = #{id};
</update>
2.3.3不写parameterType
如果只有一个基本类型的参数,可以不写parameterType
<delete id="delUser">
delete from mybts.user
where id = #{id}
</delete>
多个参数用map或者@注解
2.4核心配置文件
2.4.1xml配置
在XML中标签顺序是有规定的,如下图展示
1.属性(properties)
这些属性可以在外部进行配置,并可以进行动态替换。既可以在典型的 Java 属性文件【db.properties】中配置这些属性,也可以在 properties 元素的子元素中设置。
S1:编写配置文件(db.properties)
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/mybts?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT
username = root
password = root12345
S2:在XML文件中引入
可以直接引用外部文件,也可以增加属性,如果有同一字段,优先使用外部文件
<properties resource="db.properties">
<property name="username" value="root"/>
<property name="password" value="root12345"/>
</properties>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
2.设置(settings)
这是 MyBatis 中极为重要的调整设置,它们会改变 MyBatis 的运行时行为。
设置名 | 描述 | 有效值 | 默认值 |
---|---|---|---|
cacheEnabled | 全局性地开启或关闭所有映射器配置文件中已配置的任何缓存。 | true | false | true |
lazyLoadingEnabled | 延迟加载的全局开关。当开启时,所有关联对象都会延迟加载。 特定关联关系中可通过设置 fetchType 属性来覆盖该项的开关状态。 | true | false | false |
mapUnderscoreToCamelCase | 是否开启驼峰命名自动映射,即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn。 | true | false | False |
logImpl | 指定 MyBatis 所用日志的具体实现,未指定时将自动查找。 | SLF4J | LOG4J(deprecated since 3.5.9) | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | STDOUT_LOGGING | NO_LOGGING | 未设置 |
<settings>
<setting name="cacheEnabled" value="true"/>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="mapUnderscoreToCamelCase" value="false"/>
</settings>
3.类型别名(typeAliases)
实体类多建议用第二种
类型别名可为 Java 类型设置一个缩写名字。 它仅用于 XML 配置,意在降低冗余的全限定类名书写。
方式1:直接给一个Java类起别名
<typeAliases>
<typeAlias type="com.candy.pojo.User" alias="User"></typeAlias>
</typeAliases>
<select id="getUserList" resultType="User">
select * from mybts.user
</select>
方法2:扫描实体类包(里面的Java类默认用首字母小写作为别名)
<typeAliases>
<package name="com.candy.pojo"/>
</typeAliases>
<select id="getUserList" resultType="user">
select * from mybts.user
</select>
还可以用@注解给实体类起别名
4.环境配置(environments)
MyBatis 可以配置成适应多种环境,默认事务管理器:JDBC,连接池:POOLED。
尽管可以配置多个环境,但每个 SqlSessionFactory 实例只能选择一种环境。
<environments default="development">
<environment id="development">
<transactionManager type="JDBC">
<property name="..." value="..."/>
</transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
注意一些关键点:
- 默认使用的环境 ID(比如:default=“development”)。
- 每个 environment 元素定义的环境 ID(比如:id=“development”)。
- 事务管理器的配置(比如:type=“JDBC”)。还有一种managed但是基本用不到。
- 数据源的配置(比如:type=“POOLED”)。
5.映射器(mappers)
mapperRegistry:注册绑定mapper文件
<mappers>
<mapper resource="com/candy/dao/UserMapper.xml"/>
</mappers>
2.5RusultMap结果集映射
2.5.1解决属性名与字段名不一致的问题
解决方案:
1.起别名
2.resultMap—结果集映射
User:
字段名 实体类
id id
name name
pwd password
2.6日志
2.6.1日志工厂
解决:由于数据库操作出现异常,排除错误。日志是最好的助手!
-
SLF4J
-
Apache Commons Logging
-
Log4j 2
-
Log4j (deprecated since 3.5.9)。【掌握】
– 通过配置文件来修改配置不需要修改代码
S1:先导包(pom.xml)
<dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>
S2:创建log4j.properties
#将等级为DEBUG的日志信息输出到console和file这两个目的地,console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file
#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n
#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/candy.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n
#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
S3:在设置中设置LOG4j
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
初步使用:
-
在需要使用的LOG4J的类中,导包
import org.apache.log4j.Logger;
-
生成日志对象,参数为当前类.class
static Logger logger = Logger.getLogger(UserMapperTest.class);
-
日志级别
logger.info("info:进入testlog4j"); logger.debug("debug:进入debug"); logger.error("error:进入error");
- STDOUT_LOGGING【掌握】【标志日志输出】
3.分页
3.1 Limit分页
SELECT * from user limit startIndex,pageSize;//startIndex:从第几个开始查 pageSize:每页显示几个
SELECT * from user limit 0,3;
pageSize=—1 从startIndex开始一直到最后一个(算是以前的bug,现在不能这么用了)
如果只有一个参数n,输出类似于[0,n]
3.2 Mybatis分页(核心SQL)
3.2.1 步骤
- 接口
//分页
List<User> getUserByLimit(Map<String,Integer> map);
- Mapper.xml
<select id="getUserByLimit" parameterType="map" resultMap="UserMap">
select * from mybts.user limit #{startIndex},#{pageSize}
</select>
- 测试类
@Test
public void test8(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
HashMap<String, Integer> map = new HashMap<String, Integer>();
map.put("startIndex",1);
map.put("pageSize",2);
List<User> userList = mapper.getUserByLimit(map);
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
4.注解
4.1 使用注解开发
-
在接口上实现
@Select("select * from user") List<User> getUsers();
-
在核心配置文件中绑定接口
<mappers> <mapper class="com.candy.dao.UserMapper"></mapper> </mappers>
-
测试类(和之前一样)
本质:反射机制实现
底层:动态代理
4.2增删改查的注解
方法存在多个参数时,所有参数前面必须加上@Param(“xxx”)
@Param(“xxx”):
- 基本类型的参数或者String类型需要
- 引用类型不需要加
- 如果只有一个基本类型,可以忽略,但是建议加上
- @Param(“xxx”)与#{xxx}对应
@Select("select * from user where id = #{id}")
User getUserById(@Param("id") int id);
@Insert("insert into user(id,name,pwd) values (#{id},#{name},#{password})")
int addUser(User user);
5.复杂查询
5.1多对一
举个例子:
上网课,多个学生上一个老师的课程
-
对于学生而言,多个学生关联一个老师【多对一】
-
对于老师而言,一个老师集合有很多学生【一对多】
实体类:
public class Student { private int id ; private String name; private Teacher teacher; }
public class Teacher { private int id ; private String name; }
javaType:指定属性的类型 !! list泛型信息用ofType获取!!
查询所有学生以及其对应的老师:
1.按照查询嵌套处理【子查询】:
思路:1.查询所有学生
2.根据学生的tid,查询到对应的老师
sql:select * from student s,teacher t where s.id = t.id
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"></result>
<result property="name" column="name"></result>
<!--复杂属性: 对象:association 集合:collection-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"></association>
</resultMap>
<select id="getStudent" resultType="StudentTeacher">
select * from student
</select>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{tid}
</select>
2.按照结果嵌套处理【联表查询】:
<resultMap id="StudentTeacher2" type="student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid,s.name sname,t.name tname
from student s,teacher t
where s.tid = t.id
</select>
字段名起别名之后 column要写别名
5.2一对多
实体类:
public class Student {
private int id ;
private String name;
private int tid;
}
public class Teacher {
private int id ;
private String name;
private List<Student> students;
}
查询指定老师下的所有学生以及老师的信息:
1.按照结果嵌套查询:
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.name tname,t.id tid
from student s,teacher t
where s.id = t.id and t.id = #{tid}
</select>
2.按照查询嵌套处理:
<resultMap id="TeacherStudent2" type="Teacher">
<collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id" >
</collection>
</resultMap>
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from tracher where id = #{tid}
</select>
<select id="getStudentByTeacherId" resultType="Student">
select *
from student
where tid = #{tid}
</select>
6.动态SQL
6.1定义
动态SQL就是指根据不同的条件生成不同的SQL语句
6.2标签
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
6.2.1 if
<select id="queryIf" parameterType="map" resultType="blog">
select *
from mybts.blog
where
<if test="title != null">
title = #{title}
</if>
</select>
@Test
public void test1() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","男儿歌");
List<Blog> blogs = mapper.queryIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
6.2.2 choose(when, otherwise)
<select id="queryChoose" parameterType="map" resultType="blog">
select *
from mybts.blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
author = #{author}
</when>
<otherwise>
views = #{views}
</otherwise>
</choose>
</where>
</select>
@Test
public void test2() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
// map.put("title","男儿歌");
map.put("views",2);
List<Blog> blogs = mapper.queryChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
6.2.3 trim(where、set)
<update id="updateBlog" parameterType="map">
update mybts.blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
@Test
public void test3() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","相遇");
map.put("author","时代少年团官博");
map.put("id","74ea322de7db4b6b9c098d6158f480a3");
mapper.updateBlog(map);
System.out.println("-----");
session.commit();
session.close();
}
set标签:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
//可以自定义
prefix 前缀 suffixOverrides语句中有就去掉,没有加上
6.2.4 foreach
<select id="queryForeach" parameterType="map" resultType="blog">
select * from mybts.blog
<where>
<foreach collection="ids" item="id" open="(" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
open开头拼接 close结尾拼接 separate分割符
@Test
public void test4() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
ArrayList<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
map.put("ids",ids);
//map.put("ids","1");
//map.put("id","74ea322de7db4b6b9c098d6158f480a3");
mapper.queryForeach(map);
System.out.println("-----");
session.commit();
session.close();
}
6.2.5 sql语句
使用标签抽取公共部分,引用的时候添加
<sql id="if-t">
<if test="title != null">
title = #{title}
</if>
</sql>
<select id="queryIf" parameterType="map" resultType="blog">
select *
from mybts.blog
<where>
<include refid="if-t"></include>
</where>
</select>
里面最好不要有标签