MyBatis
1、CRUD(增删改查)
1.namespace
namespace中的包名要与Dao/Mapper 接口的包名一致!
2.select
选择,查询语句;
- id:就是对应的namespace中的方法名;
- resultType:Sql语句执行的返回值!
- parameterType:参数类型!
1.编写接口
//根据id查询用户
User getUserById(int id);
2.编写对应的mapper中的sql语句
<select id="getUserById" parameterType="int" resultType="com.zhu.pojo.User">
select * from student.user where id = #{id}
</select>
3.测试
@Test
public void getUserById(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
System.out.println(user);
sqlSession.close();
}
3.Insert
<insert id="addUser" parameterType="com.zhu.pojo.User">
insert into student.user(id, name, age) VALUES (#{id},#{name},#{age});
</insert>
4.Updata
<update id="updataUser" parameterType="com.zhu.pojo.User">
update student.user set name=#{name},age=#{age} where id=#{id};
</update>
@Test
public void updataUser(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.updataUser(new User(2,"朱静",21));
sqlSession.commit();
sqlSession.close();
}
5.Delete
<delete id="deleteUser" parameterType="int">
delete from student.user where id=#{id};
</delete>
注意点:
- 增删改需要提交事务
6.分析错误
- 标签不要匹配错
- resource绑定mapper,需要使用路径
- 程序配置文件必须要规范!
- NullPointerException ,没有注册到资源!
- maven资源导出问题
7.Map
假设,我们的实体类,或者数据库中的表,字段或者参数过多,我们应当考虑使用Map!
//Map
User getUserById2(Map<String,Object> map);
<select id="getUserById2" parameterType="map" resultType="com.zhu.pojo.User">
select * from student.user where id = #{helloid}
</select>
@Test
public void getUserById2(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String,Object> map = new HashMap<String, Object>();
map.put("helloid",1);
mapper.getUserById2(map);
sqlSession.close();
}
Map传递参数,直接在sql中取出key就可!【parameterType=“map”】
对象传递参数,需要在sql中取对象的属性即可!【parameterType=“Object”】
只有一个基本类型参数的情况下,可以直接在sql中取到!
多个参数用Map,或者注解!
8.模糊查询
List<User> getUserLike(String value);
1.java代码执行到时候,传递通配符:
select * from student.user where name like #{value}
2.在sql拼接中使用通配符:
<select id="getUserLike" resultType="com.zhu.pojo.User">
select * from student.user where name like "%"#{value}"%"//防止sql注入
</select>
@Test
public void getUserLike(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> list = userMapper.getUserLike("%张%");
for (User user : list) {
System.out.println(user);
}
sqlSession.close();
}
2、配置环境
1.外部引入数据库
<properties resource="db.properties"/>
diver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/student?useSSL=true&useUnicode=true&characterEncoding=UTF-8
username=root
password=111458
2.外部另起实体类别名(两种方法)
<typeAliases>
<typeAlias type="com.zhu.pojo.User" alias="User"/>
</typeAliases>
<typeAliases>
<package name="com.zhu.pojo"/>
</typeAliases>
3.映射器(mappers)
一般使用resource方式
使用class,或者是包名来进行文件引入,接口名和Mapper名必须要一致,且必须要在同一个包下!
3、生命周期和作用域
SqlSessionFactoryBuilder:
- 一旦创建SqlSessionFactory,就不再需要他
- 局部变量
SqlSessionFactory:
- 相当于数据库的连接池
- SqlSessionFactory一旦被创建就应该在应用的运行期间一直存在,没有任何理由丢弃或者重建另一个实例
SqlSession:
- 连接到连接池的一个请求
- SqlSession的实例不是线程安全的,因此不能被共享,所以他的最佳的作用域是请求或者方法作用域
- 用完之后要关闭,否则资源被占用
4、resultMap 结果集映射
<resultMap id="UserMap" type="User">
<result column="pwd" property="password"/>
</resultMap>
<select id="getUserList" resultMap="UserMap">
...
</select>
5、日志工厂
1.STDOUT_LOGGING
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
2.LOG4J
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
添加依赖
<dependencies>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
</dependencies>
log4j.properties
# priority :debug<info<warn<error
#you cannot specify every priority with different file for log4j
log4j.rootLogger=debug,stdout,info,debug,warn,error
#console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern= [%d{yyyy-MM-dd HH:mm:ss a}]:%p %l%m%n
#info log
log4j.logger.info=info
log4j.appender.info=org.apache.log4j.DailyRollingFileAppender
log4j.appender.info.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.info.File=./src/com/hp/log/info.log
log4j.appender.info.Append=true
log4j.appender.info.Threshold=INFO
log4j.appender.info.layout=org.apache.log4j.PatternLayout
log4j.appender.info.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#debug log
log4j.logger.debug=debug
log4j.appender.debug=org.apache.log4j.DailyRollingFileAppender
log4j.appender.debug.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.debug.File=./src/com/hp/log/debug.log
log4j.appender.debug.Append=true
log4j.appender.debug.Threshold=DEBUG
log4j.appender.debug.layout=org.apache.log4j.PatternLayout
log4j.appender.debug.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#warn log
log4j.logger.warn=warn
log4j.appender.warn=org.apache.log4j.DailyRollingFileAppender
log4j.appender.warn.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.warn.File=./src/com/hp/log/warn.log
log4j.appender.warn.Append=true
log4j.appender.warn.Threshold=WARN
log4j.appender.warn.layout=org.apache.log4j.PatternLayout
log4j.appender.warn.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#error
log4j.logger.error=error
log4j.appender.error = org.apache.log4j.DailyRollingFileAppender
log4j.appender.error.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.error.File = ./src/com/hp/log/error.log
log4j.appender.error.Append = true
log4j.appender.error.Threshold = ERROR
log4j.appender.error.layout = org.apache.log4j.PatternLayout
log4j.appender.error.layout.ConversionPattern = %d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
6、分页
用于减少数据的处理量
1.使用limit分页
select * from 表名 limit startIndex,pageSize;
2.使用MyBatis进行分页(推荐)
1.接口
//分页
List<User> getUserByLimit(Map<String,Integer> map);
2.Mapper.xml
<select id="getUserByLimit" resultType="com.zhu.pojo.User" parameterType="map">
select * from student.user limit #{startIndex},#{pageSize}
</select>
3.测试
@Test
public void getUserByLimit(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
HashMap<String,Integer> map = new HashMap<String,Integer>();
map.put("startIndex",0);
map.put("pageSize",2);
List<User> userList = userMapper.getUserByLimit(map);
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
3.分页插件MyBatisPageHelper
7、注解开发
1.面向接口编程
关于接口的理解
-定义和实现分离
主要有两类:
第一类是对一个个体的抽象,可对应为一个抽象体(abstract class);
第二类是对一个个体某一方面的抽象,即形成一个抽象面(interface)。
2.使用注解开发
1.接口
@Select("select * from student.user")
List<User> getUser();
2.核心配置文件配置(重点)
//绑定接口
<mappers>
<mapper class="zhu.dao.UserMapper"/>
</mappers>
3.测试
@Test
public void getUser(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.getUser();
for (User user : users) {
System.out.println(user);
}
sqlSession.close();
}
3.MyBatis执行流程
resources获取加载全部配置文件->实例化SqlSessionFactoryBuilder构造器->解析配置文件流XMLConfigBuilder
->Configuration所有的配置信息->SqlSessionFactory实例化->transaction事务管理->创建executor执行器->创建
sqlSession->实现CRUD(回滚事务)->查看是否执行成功(否:回滚事务)->提交事务->关闭
4.CRUD
注解可实现CRUD是自动提交事务(需要手动改写)
//根据id查询
@Select("select * from student.user where id = #{id}")
User getUserByID(@Param("id")int id);
//插入
@Insert("insert into user(id,name,age) values(#{id},#{name},#{age})")
int addUser(User user);
//修改
@Update("update user set name = #{name},age = #{age} where id = #{id}")
int updateUser(@Param("id")int id);
//删除
@Delete("delete from user where id = #{id}")
int deleteUser(@Param("id")int id);
@Param()注释:一般的基本类型都建议使用Param()注释,设定成我们MySql设定的属性名,可以成大程度防止sql注入
8、Lombok插件(一般不建议使用)
1.在idea下载Lombok
2.导入Lombok的jar包
3.使用
利用注解进行使用
@Getter and @Setter
@FieldNameConstants
@ToString
@EqualsAndHashCode
@AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor
@Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog
@Data
9、多对一处理(对象)
环境
1.创建两个sql表,进行关联
2.新建实体类Teacher,Student
3.创建Mapper接口
4.配置Mapper.xml文件
5.在核心配置文件中绑定我们创建的Mapper接口或者文件
6.测试
查询嵌套处理(子查询)
<select id="getStudent" resultMap="StudentTeacher">
select * from student.student
</select>
<resultMap id="StudentTeacher" type="com.zhu.pojo.Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--association:对象 collection:集合-->
<association property="teacher" column="tid" javaType="com.zhu.pojo.Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="com.zhu.pojo.Teacher">
select * from student.teacher where id = #{id}
</select>
结果嵌套处理(连表查询)
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid,s.name sname,t.name tname
from student.student s ,student.teacher t
where s.tid = t.id;
</select>
<resultMap id="StudentTeacher2" type="com.zhu.pojo.Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="com.zhu.pojo.Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
10、一对多处理(集合)
结果嵌套处理
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.name tname,t.id tid
from student.student s,student.teacher t
where s.tid = t.id and t.id = #{id};
</select>
<resultMap id="TeacherStudent" type="com.zhu.pojo.Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="com.zhu.pojo.Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
查询嵌套处理
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from student.teacher where id = #{id};
</select>
<resultMap id="TeacherStudent2" type="com.zhu.pojo.Teacher">
<collection property="students" column="id" javaType="ArrayList" ofType="com.zhu.pojo.Student" select="getStudentByTeacherID"/>
</resultMap>
<select id="getStudentByTeacherID" resultType="com.zhu.pojo.Student">
select * from student.student where tid = #{id};
</select>
javaType:用于指定实体类中属性的类型
ofType:用于指定映射到List或者集合中的pojo类型,泛型中的约束类型
11、动态SQL
if语句
//if查询语句
List<Blog> queryBlogByIF(Map map);
<select id="queryBlogByIF" parameterType="map" resultType="com.zhu.pojo.Blog">
select * from student.blog where 1 = 1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
public void queryBlogByIF(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","Java");
map.put("author","KIN");
List<Blog> blogs = blogMapper.queryBlogByIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
trim、where、set
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
where语句:
<select id="queryBlogByIF" parameterType="map" resultType="com.zhu.pojo.Blog">
select * from student.blog
<where>
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
set语句:
<update id="updateBlog" parameterType="map">
update student.blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author">
author = #{author}
</if>
</set>
where id = #{id}
</update>
public void updateBlog(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","C语言");
map.put("id","222");
blogMapper.updateBlog(map);
sqlSession.commit();
sqlSession.close();
}
choose、when、otherwise
choose嵌套在where语句中
<select id="queryBlogByChoose" parameterType="map" resultType="com.zhu.pojo.Blog">
select * from student.blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
SQL片段
用于提高相同sql语句的复用性
使用sql标签提取公共部分
建议基于单表使用sql标签,不要存在where标签
<sql id="getting">
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
<select id="queryBlogByIF" parameterType="map" resultType="com.zhu.pojo.Blog">
select * from student.blog
<where>
<include refid="getting"></include>
</where>
</select>
foreach
# select * from student.blog where 1=1 and (id = 1 or id = 2)
<select id="queryBlogForeach" parameterType="map" resultType="com.zhu.pojo.Blog">
select * from student.blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
public void queryBlogForeach(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
ArrayList<Integer> ids = new ArrayList<>();
ids.add(222);
map.put("ids",ids);
blogMapper.queryBlogForeach(map);
sqlSession.close();
}
动态SQL其实就是sql语句的拼接,我们只要保证sql语句的正确性,按照sql语句的格式进行排练组合即可
12、MyBatis缓存
一级缓存
(默认开启,本地会话缓存,只在一次sqlsession中有效,就是连接到关闭连接过程)
缓存失效情况:
1.查询不同东西
2.增删改操作,必定会刷新缓存
3.查询不同得Mapper.xml
4.手动清理缓存(sqlsession.clearCache())
二级缓存
(全局缓存,需要手动开启和配置,基于namespace级别的缓存,一个名称空间对应一个二级缓存)
MyBatis定义了Cache接口,通过实现Cache接口定义二级缓存
- 一个会话查询一条数据,这个数据就会被放在当前会话的一级缓存中;
- 如果当前会话关闭,这个会话对应的一级缓存清空,此时一级缓存的数据被保存到二级缓存中,此前都是存储在一级缓存中
- 新的会话查询信息可以从二级缓存中获取内容
- 不同的mapper查出的数据会放在自己对应的缓存中
1.开启全局缓存:
<setting name="cacheEnabled" value="true"/>
2.在要使用二级缓存的Mapper.xml文件中开启
<cache/>
或者:
<cache
eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"/>
使用多个sqlsession和mapper对象的时候,要注意实体类的序列化问题
implements Serializable
只要开启了一个缓存在一个mapper中都有效
自定义缓存Ehcache
EhCache 是一个纯Java的进程内缓存框架,具有快速、精干等特点
以上属于个人学习积累,希望对你有帮助!