<?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.ws.dao.UserDao">
<select id="getAllUser" resultType="com.ws.pojo.User">
select * from user
</select>
</mapper>
Mybatis
简介
-
maven仓库:
<dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.2</version> </dependency>
第一个mybatis程序
导入进pom.xml文件中
- 导入mysql驱动
- 导入myBatis
- 导入junit
<!-- 导入依赖-->
<dependencies>
<!-- mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<!-- junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
在resource中新建一个mybatis-config.xml文件
- 核心设置(mybatis-config 【mybatis 官网给出模板】 【链接数据库】):
<?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.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
//类似于dao层的impl
<mappers>
<mapper resource="com/ws/dao/UserMapper.xml"/>
</mappers>
</configuration>
- 编写核心工具类(mybatis官方 给出)
- sqlSessionFactory -> sqlSession
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
String resource = "mybatis-config.xml";
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(resource);
} catch (IOException e) {
e.printStackTrace();
}
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
}
}
-
写接口(UserDao)
public interface UserDao { public List<User> getAllUser(); }
-
写Dao层的实现(UserMapper.xml 相当于以前的UserDaoimpl)
<?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"> <!-- id方法名 resultType返回数据类型--> <mapper namespace="com.ws.dao.UserDao"> <select id="getAllUser" resultType="com.ws.pojo.User"> select * from user </select> </mapper>
-
测试
@org.junit.Test public void test(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserDao userDao = sqlSession.getMapper(UserDao.class); List<User> allUser = userDao.getAllUser(); for (User user : allUser) { System.out.println(user.toString()); } sqlSession.close(); }
-
如果Mapper.xml 没有放在resource里面 在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>
CURD(增删改查)
namespace
namespace中的包名要和接口相同
select
<select id="getAllUser" resultType="com.ws.pojo.User"
parameterType="">
select * from user
</select>
<!-- 语句中接受参数#{}-->
- id: 对于namespace中方法名
- resultType: sql语句返回值
- parameterType: 参数类型
测试:
@org.junit.Test
public void test(){
SqlSession sqlSession =MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> allUser = userDao.getAllUser();
for (User user : allUser) {
System.out.println(user.toString());
}
//在增删改的时候需要执行
//sqlSession.commit();
sqlSession.close();
}
insert
<insert id="addUser" parameterType="com.ws.pojo.User">
insert into mybatis.user (id,name,pwd) values(#{id},#{name},#{pwd})
</insert>
delete
<delete id="delUser" parameterType="int">
delete from mybatis.user where id = #{id}
</delete>
update
<update id="updateUser" parameterType="com.ws.pojo.User">
update mybatis.user set name = #{name},pwd=#{pwd} where id=#{id} ;
</update>
增删改 在最后要提交事务!!!
【如果在MybatisUtils中 return sqlSessionFactory.openSession(true); 则不需要使用sqlSession.commit()】
sqlSession.commit();
使用map
当对象属性很多的时候,new一个对象过于繁琐 可以使用map获得值
使用类 则需要与属性名对应
UserMapper:
<update id="updateUser2" parameterType="map">
update user set name = #{myName} ,pwd = #{myPwd} where id = #{myId}
</update>
Test类:
@org.junit.Test
public void update2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
Map<String,Object> map = new HashMap<>();
map.put("myName","我的名字");
map.put("myPwd","516");
map.put("myId",1);
mapper.updateUser2(map);
sqlSession.commit();
sqlSession.close();
}
配置解析
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
</mappers>
</configuration>
环境配置(environment)
- 可以拥有多套环境 但是只有一套环境可用
<configuration>
<environments default="development">
<environment id="development">
</environment>
<environment id="test">
</environment>
</environments>
<mappers>
</mappers>
</configuration>
在default里面通过id选择使用的环境
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver"value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
</mappers>
</configuration>
- tansactionManager 中的type【JDBC|MANAGER】
- dataSource 中的type【POOLED|UNPOOLED|JNDI】POOLED用完不会关闭
属性(properties)
db.properties:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username=root
password=root
在配置文件mybatis-config.xml文件中引入
<properties resource="db.properties"/>
- 必须放在最开始
- 在mybatis-config.xml中 中的 中的value属性通过${}获取值
可在内部填写值
<properties resource="db.properties">
<property name="" value=""/>
</properties>
先获取外部文件.properties中的值 再获取内部值
类型别名(typeAliases)
解决在mapper中com.ws.pojo.User多次使用
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4LWyf5pm-1602592319223)(C:\Users\ws666\AppData\Roaming\Typora\typora-user-images\image-20201002190149790.png)]
-
在mybatis-config.xml文件中 typeAlias
<typeAliases> <typeAlias type="com.ws.pojo.User" alias="user"/> </typeAliases>
在Mapper中使用:
<select id="getAllUser" resultType="user"> select * from user </select>
User|user 均可
-
在mybatis-config.xml文件中 package
<typeAliases> <package name="com.ws.pojo"/> </typeAliases>
在mapper中
<select id="getAllUser" resultType="user"> select * from user </select>
默认使用user这个名字
如果使用其他名字 在类上使用@Alias(“XXX”) resultType中的值为XXX
设置
映射器(mappers)
在mybatis-config.xml中
方式一:
<mappers>
<mapper resource="com/ws/dao/UserMapper.xml"/>
</mappers>
方式二:
<mappers>
<mapper class="com.ws.dao.UserMapper"/>
</mappers>
- 要放在与接口同一个包下!!
- 要与接口的名字相同!!
方式三:
<mappers>
<mapper package="com/ws/dao/UserMapper.xml"/>
</mappers>
- 要放在与接口同一个包下!!
- 要与接口的名字相同!!
解决对象属性名与字段不一致的情况
<resultMap id="ttt" type="User">
<result property="password" column="pwd"/>
</resultMap>
<select id="findUser" parameterType="int" resultMap="ttt">
select * from user where id=#{id}
</select>
- 将resultType 换成resultMap
- 使用resultMap标签
- property对应属性名 column对应数据库中的名字
日志
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lhYdqqsf-1602592319224)(C:\Users\ws666\AppData\Roaming\Typora\typora-user-images\image-20201003153813453.png)]
配置文件mybatis-config.xml中
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
log4j
导入log4j的包
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
log4j的配置文件 log4j.property在网上找
例 :
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/kuang.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
测试类:
public class MyTest {
static Logger logger = Logger.getLogger(MyTest.class);
@Test
public void log4jTest(){
logger.info("hello");//正常的
logger.error("error");//出错
logger.debug("debug");//debug
}
}
分页
使用limit分页
语法:select *from user limit 0,2
用map传参
使用RowBounds
(面向对象的方式 基本上不怎么用)
使用new RowBounds() sqlSession.selectList()
注解开发
- 不在Mapper.xml中书写
- 直接使用注解 例:@Select(“select * from user”)
UserDao:
public interface UserDao {
@Select("select * from user")
public List<User> getAllUser();
}
测试类:
@org.junit.Test
public void annotationTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
List<User> allUser = mapper.getAllUser();
for (User user : allUser) {
System.out.println(user);
}
sqlSession.close();
}
CURD
接口必须绑定在核心配置文件中
public interface UserDao {
@Select("select * from user")
public List<User> getAllUser();
@Insert("insert into user (name,pwd,id) values (#{name},#{pwd},#{id})")
public void addUser(User user);
@Delete("delete from user where id = #{uid}")
public void delUser(@Param("uid") int id);
@Update("update user set name = #{name},pwd=#{pwd} where id=#{id}")
public void updateUser(User user);
@Select("select * from user where id = #{id}")
public User findUserById(int id);
}
- @param(“XXX”) 在参数上使用
- 如果参数是引用类型不用使用@param("")
- #{}相比较${}更加安全 放在sql注入
多对一处理(关联)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yVqcZ6V5-1602592319226)(C:\Users\ws666\AppData\Roaming\Typora\typora-user-images\image-20201009194617135.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uAHt3juI-1602592319227)(C:\Users\ws666\AppData\Roaming\Typora\typora-user-images\image-20201009194628377.png)]
按照查询嵌套处理
类似子查询
<select id="getStuTea" resultMap="stuTea">
select * from student
</select>
<!-- type 为最终映射到哪儿类上-->
<resultMap id="stuTea" type="student" >
<result column="id" property="id"/>
<result column="name" property="name"/>
<association column="tid" property="teacher" javaType="teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{id}
</select>
思路:
- 查询整个学生
- 使用 查询里面的Teacher类 javaType 是指明类型
- 查询老师的详细sql
按照结果嵌套处理
类似连表查询
<select id="getAllStudent" resultMap="get2">
select s.id sid,s.name sname,t.id,t.name tname
from student s,teacher t
where s.tid = t.id
</select>
<resultMap id="get2" type="student">
<result property="name" column="sname"/>
<result property="id" column="sid"/>
<association property="teacher" javaType="Teacher" >
<result property="name" column="tname"/>
<result property="id" column="id"/>
</association>
</resultMap>
思路:
- 书写整个查询sql
- 在里面书写 将字段对应起来
一对多处理(集合)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Gtfge8LT-1602592319229)(C:\Users\ws666\AppData\Roaming\Typora\typora-user-images\image-20201009195757317.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9UzItEMg-1602592319229)(C:\Users\ws666\AppData\Roaming\Typora\typora-user-images\image-20201009195803721.png)]
按照查询嵌套处理
<select id="getAllStudent" resultMap="teacherStudent" parameterType="_int">
select * from teacher where id = #{tid}
</select>
<resultMap id="teacherStudent" type="Teacher">
<result column="id" property="id"/>
<collection property="students" javaType="ArrayList" ofType="Student" column="id" select="getStudent"/>
</resultMap>
<select id="getStudent" resultType="Student" >
select * from student where tid = #{tid}
</select>
按照结果嵌套处理
<select id="getAllStudent2" resultMap="st" parameterType="_int">
select t.id tid,t.name tname, s.id sid, s.name sname
from student s,teacher t
where s.tid = t.id and t.id = #{tid}
</select>
<resultMap id="st" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<result property="name" column="sname"/>
<result property="id" column="sid"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
动态SQL
- if
- choose(when otherwise)
- trim(where set)
- foreach
在只有1个子元素的时候插入where 当语句中存在AND OR的时候不插入
IF语句
blogMapper.xml
<select id="selectBlogIf" parameterType="map" resultType="Blog">
select * from blog where 1=1
<if test="title != null">
and title like #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
test:
@org.junit.Test
public void selectBlogIf(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
map.put("author","ws");
map.put("title","%1%");
List<Blog> blogs = mapper.selectBlogIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
choose(when otherwise)
类似于 switch — case
如果存在两个或者多个when 两个条件时 满足第一个when后不会退出 会继续判断接下来的when
trim(where set)
sql片段
类似与方法
<sql id="iff">
<if test="title != null">
and title like #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
引用:
<include refid="iff"/>
- 不要再里面写标签等
- 尽量增强复用
Foreach
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
open:以什么开头
close:以什么结尾
separator:分隔符
缓存
一级缓存
sqlsession
(相当于一个map)
- 默认开启,只在一次sqlsession中有效(拿到链接-关闭链接)
- 更新表的时候会清理缓存
- 不同的mapper会清理缓存
- 手动清理后
二级缓存
开启二级缓存步骤:
- setting 中 显示设置 cacheEnabled
在**Mapper.xml中添加
<cache/>
- 一个namespace对应一个二级缓存
- 所有数据会先放到一级缓存中
- 当sqlsession提交或关闭的时候会提交到二级缓存
mybatis缓存原理
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1WG5U13j-1602592319230)(C:\Users\ws666\AppData\Roaming\Typora\typora-user-images\image-20201013114241375.png)]
- 查看二级缓存
- 查看一级缓存
自定义缓存
ehcache
- 导包
- 写cache.xml
r != null">
and author = #{author}
引用:
```xml
<include refid="iff"/>
- 不要再里面写标签等
- 尽量增强复用
Foreach
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
open:以什么开头
close:以什么结尾
separator:分隔符
缓存
一级缓存
sqlsession
(相当于一个map)
- 默认开启,只在一次sqlsession中有效(拿到链接-关闭链接)
- 更新表的时候会清理缓存
- 不同的mapper会清理缓存
- 手动清理后
二级缓存
开启二级缓存步骤:
- setting 中 显示设置 cacheEnabled
在**Mapper.xml中添加
<cache/>
- 一个namespace对应一个二级缓存
- 所有数据会先放到一级缓存中
- 当sqlsession提交或关闭的时候会提交到二级缓存
mybatis缓存原理
[外链图片转存中…(img-1WG5U13j-1602592319230)]
- 查看二级缓存
- 查看一级缓存
自定义缓存
ehcache
- 导包
- 写cache.xml
redius 数据库做缓存