Mybatis
环境
- JDBC
- java基础
- JDK 1.8
- Mysql5.7
- maven 3.6.1
- 开发工具 idea
- Junit
SSM框架:配置文件的最好方式:看官网文档
1 简介
1.1 什么是Mybatis?
- MyBatis是一款优秀的持久层框架
- 支持定制SQL、存储过程以及高级映射
- Mybatis避免了几乎所有的JDBC代码和手动设置参树,以及获取结果集
- Mybatis可以使用简单的XML或注解来配置和映射原生类型、接口和Java的POJO(Plain Old Java Objects,普通老式Java对象)为数据库中的记录
- MyBatis本是apache的一个开源项目iBatis
- 2010年这个项目由apache software foundation迁移到了google code,并且改名为MyBatis
- 2013年11月迁移到Github
如何获得Mybatis?
- Maven仓库:下载地址
- <dependency>
- <groupId>org.mybatis</groupId>
- <artifactId>mybatis</artifactId>
- <version>3.5.2</version>
- </dependency>
复制代码 - Github:Mybatis地址
- 中文文档地址
1.2 持久化
数据持久化
- 持久化就是将程序的数据保存在硬盘中
- 内存特性:断电会失去信息
- 数据库(jdbc),io文件持久化
- 生活中的持久化:冷藏,写……
为什么需要持久化?
- 我们需要将一些数据保存下来,方便以后使用
- 内存价格过高(制造成本高)
1.3 持久层
复习学习过的层:Dao,Service,Controller
持久层的工作
1.4 为什么需要Mybatis?
- 帮助程序员将数据存入到数据库中
- 传统的JDBC代码太复杂了
- 简化、框架、自动化
- 不用Mybatis也能实现,Mybatis可以使程序员在不知道底层原理的情况下,完成网站后台搭建
- 优点:
- 简单易学
- 灵活
- 解除sql与程序代码的耦合
- 提供映射标签,支持对象与数据库的orm字段关系映射
- 提供对象关系映射标签,支持对象关系组建维护
- 提供xml标签,支持编写动态sql。
2 第一个MyBatis程序
思路:搭建环境-->导入MyBatis-->编写代码-->测试
2.1 搭建环境
- 搭建数据库
- -- 创建数据库
- create database `mybatis`;
-
- use mybatis;
-
- -- 创建表
- create table `user`(
- `id` int(20) not null,
- `name` varchar(30) default null,
- `pwd` varchar(30) default null,
- primary key(`id`)
- )engine=InnoDB default charset=utf8mb4;
-
- -- 插入数据
- insert into `user`(`id`,`name`,`pwd`) values
- (1,'千树','123'),
- (2,'张三','123'),
- (3,'李飞','123');
复制代码 - mybatis官方文档:文档地址
- 新建普通maven项目作为父项目,导入sql驱动,mybatis,junit组件
- <dependencies>
-
- <dependency>
- <groupId>mysql</groupId>
- <artifactId>mysql-connector-java</artifactId>
- <version>5.1.46</version>
- </dependency>
-
- <dependency>
- <groupId>org.mybatis</groupId>
- <artifactId>mybatis</artifactId>
- <version>3.5.2</version>
- </dependency>
-
- <dependency>
- <groupId>junit</groupId>
- <artifactId>junit</artifactId>
- <version>4.12</version>
- </dependency>
- </dependencies>
复制代码 - 新建新组件作为子级项目,普通maven的module
- 添加配置文件:
在src->main->resources目录下新建mybatis-config.xml文件,把官方的配置代码复制粘贴(不能在配置文件中写中文注释)- <?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="${driver}"/>//数据库驱动,不同驱动可连接不同数据库服务器
- <property name="url" value="${url}"/>//连接数据库的目录
- <property name="username" value="${username}"/>//数据库名字,默认root
- <property name="password" value="${password}"/>//数据库密码,自己的数据库密码,一般为root
- </dataSource>
- </environment>
- </environments>
- </configuration>
复制代码 配置文件的作用就是连接数据库
2.2 创建模块
- 编写mybatis工具类
- //SqlSessionFactory --生产--> SqlSession
- public class MybatisUtils {
- private static SqlSessionFactory sqlSessionFactory; //提升作用域
- //获取工厂,固定代码
- static {
- try {
- String resource="mybatis-config.xml";
- InputStream inputStream = Resources.getResourceAsStream(resource);
- sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- //获取sqlSession
- //SqlSession完全包含了面向对象数据库执行SQL命令所需的方法
- public static SqlSession getSqlSession(){ return sqlSessionFactory.openSession();}
- }
复制代码 2.3 编写代码
- 实体类
- public class User {
- private int id;
- private String name;
- private String pwd;
- public User() { }
- public User(int id, String name, String pwd) {
- this.id = id;
- this.name = name;
- this.pwd = pwd;
- }
- public int getId() {
- return id;
- }
- public void setId(int id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public String getPwd() {
- return pwd;
- }
- public void setPwd(String pwd) {
- this.pwd = pwd;
- }
- @Override
- public String toString() {
- return "User{" +
- "id=" + id +
- ", name='" + name + '\'' +
- ", pwd='" + pwd + '\'' +
- '}';
- }
- }
复制代码 - Dao接口
- public interface UserDao {
- List<User> getUserList();
- }
复制代码 - 接口实现类改为以xxxMapper.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">
- <mapper namespace="com.qian.dao.UserDao">
- <select id="getUserList" resultType="com.qian.pojo.User">
- select * from mybatis.user where id = #{id}
- </select>
- </mapper>
复制代码 2.4 测试
- 测试类
- public class UserDaoTest {
- @Test
- public void test(){
- //获取SqlSession对象
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- //获取mapper
- UserDao mapper = sqlSession.getMapper(UserDao.class);
- List<User> list = mapper.getUserList();
- for (User u:list){
- System.out.println(u);
- }
- //不推荐使用
- /*
- 这种方式能够正常工作,对使用旧版本 MyBatis 的用户来说也比较熟悉。但现在有了一种更简洁的方式——使用和指定语句的参数和返回值相匹配的接口(比如 BlogMapper.class),现在你的代码不仅更清晰,更加类型安全,还不用担心可能出错的字符串字面值以及强制类型转换。
- */
- // List<User> list = sqlSession.selectList("com.qian.dao.UserDao.getUserList");
- // for (User user : list) {
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); System.out.println(user);
- // }
- //关闭SqlSession
- sqlSession.close();
- }
- }
复制代码 异常1:org.apache.ibatis.binding.BindingException: Type interface com.qian.dao.UserDao is not known to the MapperRegistry.
解决方法:每一个Mapper.xml文件都需要在src->main->resources目录下新建mybatis-config.xml的核心配置文件中 注册
xml//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
@Select("select * from mybatis.user where id=#{id}")
User selectUserById(@Param("id") int id);
@Select("select * from mybatis.user")
List<User> selectAll();
@Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
boolean insertUser(User u);
@Update("update user set name=#{name},pwd=#{password} where id = #{id}")
boolean updateUser(User u);
@Delete("delete from mybatis.user where id=#{id}")
boolean deleteUser(@Param("id") int id); //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
@Select("select * from mybatis.user where id=#{id}")
User selectUserById(@Param("id") int id);
@Select("select * from mybatis.user")
List<User> selectAll();
@Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
boolean insertUser(User u);
@Update("update user set name=#{name},pwd=#{password} where id = #{id}")
boolean updateUser(User u);
@Delete("delete from mybatis.user where id=#{id}")
boolean deleteUser(@Param("id") int id);
异常2:
Error building SqlSession.
The error may exist in com/qian/dao/UserMapper.xml
Cause: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration.
Cause: java.io.IOException: Could not find resource com/qian/dao/UserMapper.xml
解决方法:
xml//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
@Select("select * from mybatis.user where id=#{id}")
User selectUserById(@Param("id") int id);
@Select("select * from mybatis.user")
List<User> selectAll();
@Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
boolean insertUser(User u);
@Update("update user set name=#{name},pwd=#{password} where id = #{id}")
boolean updateUser(User u);
@Delete("delete from mybatis.user where id=#{id}")
boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
@Select("select * from mybatis.user where id=#{id}")
User selectUserById(@Param("id") int id);
@Select("select * from mybatis.user")
List<User> selectAll();
@Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
boolean insertUser(User u);
@Update("update user set name=#{name},pwd=#{password} where id = #{id}")
boolean updateUser(User u);
@Delete("delete from mybatis.user where id=#{id}")
boolean deleteUser(@Param("id") int id); <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases>src/main/resources <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases> <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases> **/*.properties <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases> **/*.xml <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases> <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases>true <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases>src/main/java <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases> <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases> **/*.properties <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases> **/*.xml <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases> <typeAliases>
<typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
<package name="com.yu.pojo"/>
</typeAliases>true //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
@Select("select * from mybatis.user where id=#{id}")
User selectUserById(@Param("id") int id);
@Select("select * from mybatis.user")
List<User> selectAll();
@Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
boolean insertUser(User u);
@Update("update user set name=#{name},pwd=#{password} where id = #{id}")
boolean updateUser(User u);
@Delete("delete from mybatis.user where id=#{id}")
boolean deleteUser(@Param("id") int id);
异常3:
Error building SqlSession.
The error may exist in com/qian/dao/UserMapper.xml
Cause: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration.
Cause: org.apache.ibatis.builder.BuilderException: Error creating document instance. Cause: org.xml.sax.SAXParseException; lineNumber: 6;
解决方法:
xml //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
@Select("select * from mybatis.user where id=#{id}")
User selectUserById(@Param("id") int id);
@Select("select * from mybatis.user")
List<User> selectAll();
@Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
boolean insertUser(User u);
@Update("update user set name=#{name},pwd=#{password} where id = #{id}")
boolean updateUser(User u);
@Delete("delete from mybatis.user where id=#{id}")
boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
@Select("select * from mybatis.user where id=#{id}")
User selectUserById(@Param("id") int id);
@Select("select * from mybatis.user")
List<User> selectAll();
@Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
boolean insertUser(User u);
@Update("update user set name=#{name},pwd=#{password} where id = #{id}")
boolean updateUser(User u);
@Delete("delete from mybatis.user where id=#{id}")
boolean deleteUser(@Param("id") int id);
另一种解决方法:删除掉xxxMapper.xml文件中所有的中文注释
异常4:
Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
解决方法:
useSSL=true改为false(true也可以,需要在mysql中启用SSL)
xml//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
@Select("select * from mybatis.user where id=#{id}")
User selectUserById(@Param("id") int id);
@Select("select * from mybatis.user")
List<User> selectAll();
@Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
boolean insertUser(User u);
@Update("update user set name=#{name},pwd=#{password} where id = #{id}")
boolean updateUser(User u);
@Delete("delete from mybatis.user where id=#{id}")
boolean deleteUser(@Param("id") int id);
2.5 总结
- public class UserDaoTest {
- @Test
- public void test(){
- //获取SqlSession对象
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- try{
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); //获取mapper
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); UserDao mapper = sqlSession.getMapper(UserDao.class);
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); List<User> list = mapper.getUserList();
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); for (User u:list){
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); System.out.println(u);
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); }
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); //不推荐使用
- // List<User> list = sqlSession.selectList("com.qian.dao.UserDao.getUserList");
- // for (User user : list) {
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); System.out.println(user);
- // }
- }finally {
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); //关闭SqlSession
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); sqlSession.close();
- }
- }
- }
复制代码 3 增删改查实现
3.1 Mapper接口
- public interface UserMapper {
- //查询全部用户
- List<User> getUserList();
- //根据id查询用户
- <select id="queryBlogIf" parameterType="map" resultType="Blog">
- select * from mybatis.blog where 1=1
- <if test="title!=null">
- and title = #{title}
- </if>
- <if test="author!=null">
- and author = #{author}
- </if>
- </select>
- //增加新的用户
- boolean insertNewUser(User u);
- //删除用户
- boolean deleteUserById(int id);
- boolean deleteUserByName(String name);
- //修改用户
- boolean updateUserById(User u);
- }
复制代码 3.2 xxxMapper.xml文件
- <?xml version="1.0" encoding="utf8" ?>
- <!DOCTYPE mapper
- PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
- "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
- <mapper namespace="com.qian.dao.UserMapper">
- <select id="getUserList" resultType="com.qian.pojo.User">
- select * from mybatis.user
- </select>
- <select id="getUserById" parameterType="int" resultType="com.qian.pojo.User">
- @Test
- public void queryBlogIf(){
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
- Map<String,Object> map=new HashMap<String,Object>();
- mapper.queryBlogIf(map);
- System.out.println("==================");
- map.put("title",(String)"MyBatis如此简单");
- mapper.queryBlogIf(map);
- System.out.println("==================");
- map.put("author",(String)"狂神说");
- mapper.queryBlogIf(map);
- System.out.println("==================");
- sqlSession.close();
- }
- </select>
-
- <insert id="insertNewUser" parameterType="com.qian.pojo.User">
- insert into mybatis.user (id, name, pwd) VALUES (#{id},#{name},#{pwd})
- </insert>
- <delete id="deleteUserById" parameterType="int">
- delete from mybatis.user where id=#{id}
- </delete>
- <delete id="deleteUserByName" parameterType="String">
- delete from mybatis.user where name=#{name}
- </delete>
- <update id="updateUserById" parameterType="com.qian.pojo.User">
- update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id}
- </update>
- </mapper>
复制代码 3.3 Test类
注意:增删改要提交事务!!!- public class UserDaoTest {
- @Test
- public void test(){
- //获取SqlSession对象
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- try{
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); //获取mapper
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); UserMapper mapper = sqlSession.getMapper(UserMapper.class);
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); 查询全表
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); List<User> list = mapper.getUserList();
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); for (User u:list){
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); System.out.println(u);
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); }
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); //根据id查询
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); User user = mapper.getUserById(1);
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); System.out.println(user);
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); //插入新用户,注意:更新,插入,删除都需要提交事务
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); User user1 = new User(4,"李四","25615");
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); boolean isInserted = mapper.insertNewUser(user1);
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); sqlSession.commit();
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); //代码优化
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); if (mapper.insertNewUser(new User(4,"李四","25615"))) sqlSession.commit();
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); //删除用户
- ////多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); if (mapper.deleteUserById(4))sqlSession.commit();
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); if (mapper.deleteUserByName("李四"))sqlSession.commit();
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); //修改用户
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); if (mapper.updateUserById(new User(4,"王五","6849816")))sqlSession.commit();
- }finally {
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); //关闭SqlSession
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); sqlSession.close();
- }
- }
- }
复制代码 3.4 常见错误
- 标签要匹配
- resource绑定Mapper,需要使用路径
- 配置文件中不要写注释
- useSSL=true有时候无法使用,改为false
3.5 万能Map
- UserMapper.java
- User getUser(Map<String,Object> map);
- boolean addUser(Map<String,Object> map);
复制代码 - UserMapper.xml
- <select id="getUser" parameterType="map" resultType="com.qian.pojo.User">
- select * from mybatis.user where id=#{userId}
- </select>
- <insert id="addUser" parameterType="map">
- insert into mybatis.user (id, name, pwd) VALUES (#{userId},#{userName},#{password})
- </insert>
复制代码 - Test.java
- @Test
- public void test(){
- //获取SqlSession对象
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- UserMapper mapper = sqlSession.getMapper(UserMapper.class);
-
- Map<String, Object> map = new HashMap<String, Object>();
- map.put("userId",5);
- User user = mapper.getUser(map);
- System.out.println(user);
- map.put("userId",5);
- map.put("userName","孙悟空");
- map.put("password","565464");
- if (mapper.addUser(map)) sqlSession.commit();
-
- sqlSession.close();
- }
复制代码 3.6 模糊查询
- java执行的时候,传递通配符% %
- UserMapper.java
- //模糊查询
- List<User> getUsersLike(String value);
复制代码 - UserMapper.xml
- <select id="getUsersLike" resultType="com.qian.pojo.User">
- select * from mybatis.user where name like #{value};
- </select>
复制代码 - Test.java
- @Test
- public void getUsersLike(){
- UserMapper mapper = getUserMapper();
- List<User> userList = mapper.getUsersLike("%千%");
- System.out.println(userList);
- }
- public UserMapper getUserMapper(){
- return MybatisUtils.getSqlSession().getMapper(UserMapper.class);
- }
复制代码 - 在sql中使用拼接符
- <select id="getUsersLike" resultType="com.qian.pojo.User">
- select * from mybatis.user where name like "%"#{value}"%"
- </select>
复制代码 4 配置解析
4.1 核心配合文件
- mybatis-config.xml
- MyBatis 的配置文件包含了会深深影响 MyBatis 行为的设置和属性信息。 配置文档的顶层结构如下:
- configuration(配置)
- properties(属性)
- settings(设置)
- typeAliases(类型别名)
- environments(环境配置)
- environment(环境变量)
- transactionManager(事务管理器)
- dataSource(数据源)你
- mappers(映射器)
复制代码 4.2 环境配置(environments)
MyBatis 可以配置成适应多种环境,尽管可以配置多个环境,但每个 SqlSessionFactory 实例只能选择一种环境。
MyBatis默认事务连接器就是JDBC,连接池POOLED
- 数据源(dataSource)
作用:连接数据库
c3p0 druid dbcp
大多数 MyBatis 应用程序会按示例中的例子来配置数据源。虽然数据源配置是可选的,但如果要启用延迟加载特性,就必须配置数据源。
有三种内建的数据源类型(也就是 type="[UNPOOLED|POOLED|JNDI]"):
4.3 属性(properties)
这些属性可以在外部进行配置,并可以进行动态替换。你既可以在典型的 Java 属性文件中配置这些属性,也可以在 properties 元素的子元素中设置。
- 配置db.properties
- driver=com.mysql.jdbc.Driver
- url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&;useUnicode=true&;
- characterEncoding=UTF8&;serverTimezone=GMT%2B8&;autoConnect=true
- username=root
- password=root
复制代码 - 在核心配置文件中引入
注意:在xml中,所有的标签都可以规定顺序- (properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?)".
复制代码 - 核心文件配置
- <configuration>
- <properties resource="db.properties"/>
- <environments default="development">
- <environment id="development">
- <transactionManager type="JDBC"></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>
- </configuration>
复制代码 - 总结
- 可以直接引入外部文件
- 也可以在里面配置
- 外部引入的文件(db.properties)的优先级要比在要高
- 错误提示
4.4 类型别名(typeAliases)
类型别名可为 Java 类型设置一个缩写名字。 它仅用于 XML 配置,意在降低冗余的全限定类名书写。
- 在mybatis-config.xml -> 配置
- <typeAliases>
- <typeAlias type="com.yu.pojo.User" alias="user"></typeAlias>
-
-
- <package name="com.yu.pojo"/>
- </typeAliases>
复制代码 - 然后xxxMapper.xml的返回值(resultType)就可以替换为resultType user
- 实体类较少的时候使用第一种,较多就直接扫描包目录
- 第二种也可以用注解@Alias("xxx")给类起别名
4.5 设置(settings)
这是 MyBatis 中极为重要的调整设置,它们会改变 MyBatis 的运行时行为。
- 需要掌握的设置
4.6 映射器(mappers)
MapperRegistry:注册绑定我们的Mapper文件
- 几种绑定方式
- class与扫描包(packaaage)绑定的注意点
- 接口和Mapper配置文件必须同名
- 接口和他的Mapper配置文件必须在同一包下
4.7 其他配置
- typeHandlers(类型处理器)
- objectFactory(对象工厂)
- plugins(插件)
4.8 生命周期和作用域(Scope)
理解我们之前讨论过的不同作用域和生命周期类别是至关重要的,因为错误的使用会导致非常严重的并发问题。
SqlSessionFactoryBuilder:
- 局部变量:一旦创建了 SqlSessionFactory,就不再需要它了。
SqlSessionFactory:
- 全局变量:一旦被创建就应该在应用的运行期间一直存在,可以想象为数据库连接池,没有任何理由丢弃它或重新创建另一个实例。
- 因此 SqlSessionFactory 的最佳作用域是应用作用域,最简单的就是使用单例模式或者静态单例模式。
SqlSession:
- 连接到连接池的一个请求
- SqlSession 的实例不是线程安全的,因此是不能被共享的,所以它的最佳的作用域是请求或方法作用域。
- 用完之后需要关闭
- 每一个Mapper代表一个具体的业务
5 解决属性名和字段名不一致
5.1 新建项目mybatis-03
- 复制原文件到对应目录下,修改属性(ctrl+r)pwd->password
- 使用测试类测试
- UserMapper.xml
- <mapper namespace="com.yu.dao.UserMapper">
- <select id="getUserById" parameterType="int" resultType="user">
- select * from mybatis.user where id=#{id}
-
- </select>
- </mapper>
复制代码 5.2 解决方法
- 起别名
- select id,name,pwd as password from mybatis.user where id=#{id}
复制代码 - 使用resultMap
resultMap:结果集映射- <resultMap id="UserMap" type="user">
- /*<result column="id" property="id"/>
- <result column="name" property="name"/> */
- <result column="pwd" property="password"/>
- </resultMap>
- <select id="getUserById" parameterType="int" resultMap="UserMap">
- @Test
- public void queryBlogIf(){
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
- Map<String,Object> map=new HashMap<String,Object>();
- mapper.queryBlogIf(map);
- System.out.println("==================");
- map.put("title",(String)"MyBatis如此简单");
- mapper.queryBlogIf(map);
- System.out.println("==================");
- map.put("author",(String)"狂神说");
- mapper.queryBlogIf(map);
- System.out.println("==================");
- sqlSession.close();
- }
- </select>
复制代码 ResultMap 的设计思想是,对简单的语句做到零配置,对于复杂一点的语句,只需要描述语句之间的关系就行了。
ResultMap 的优秀之处——你完全可以不用显式地配置它们。 虽然上面的例子不用显式配置 ResultMap。 但为了讲解,我们来看看如果在刚刚的示例中,显式使用外部的 resultMap 会怎样,这也是解决列名不匹配的另外一种方式。
6 日志
6.1 日志工厂
如果数据库操作出现异常,需要通过日志获取sql语句,方便纠错。
- logImpl:指定 MyBatis 所用日志的具体实现,未指定时将自动查找。
- SLF4J +
- LOG4J 【掌握】
- | LOG4J2
- | JDK_LOGGING
- | COMMONS_LOGGING
- | STDOUT_LOGGING 【掌握】
- | NO_LOGGING
- 在mybatis-config.xml中配置设置
STDOUT_LOGGING 标准日志输出- <configuration>
- <properties resource="db.properties"/>
- <settings>
- <setting name="logImpl" value="STDOUT_LOGGING"/>
- </settings>
- ... ...
- <configuration>
复制代码 6.2 测试输出
<ul>STDOUT_LOGGING- Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
- /*
- ...
- */
- Created connection 471579726.
- Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1c1bbc4e]
- ==> Preparing: select * from mybatis.user where id=?
- ==> Parameters: 1(Integer)
- <== Columns: id, name, pwd
- <== Row: 1, 千树, 123
- <== Total: 1
- User{id=1, name='千树', password='123'}
- Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1c1bbc4e]
- Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1c1bbc4e]
- Returned connection 471579726 to pool.
复制代码- ......
- Caused by: org.apache.ibatis.exceptions.PersistenceException:
- ### Error building SqlSession.
- ### The error may exist in SQL Mapper Configuration
- ### Cause: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.logging.LogException: Error setting Log implementation.
- ......
- Caused by: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.logging.LogException: Error setting Log implementation. Cause: java.lang.NoClassDefFoundError: org/apache/log4j/Priority
- ...
复制代码- <dependency>
- <groupId>log4j</groupId>
- <artifactId>log4j</artifactId>
- <version>1.2.17</version>
- </dependency>
复制代码- log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
- log4j:WARN Please initialize the log4j system properly.
- log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
- User{id=1, name='千树', password='123'}
复制代码- #newhappy log4j.properties start
- log4j.rootLogger=DEBUG,console,file
- #控制台输出 console appender
- 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
- #文件输出 rolling file appender
- log4j.appender.file=org.apache.log4j.RollingFileAppender
- log4j.appender.file.File=./log/yu.log
- log4j.appender.file.MaxFileSize=10mB
- log4j.appender.file.MaxBackupIndex=2
- log4j.appender.file.layout=org.apache.log4j.PatternLayout
- log4j.appender.file.layout.ConversionPattern=%d{mmm d,yyyy hh:mm:ss a} : %p [%t] %m%n
- log4j.appender.file.threshold=DEBUG
- #日志输出级别 logger
- 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
- #newhappy log4j.properties end
复制代码 11.3按照结果嵌套处理
- 代码演示
- public class UserDaoTest {
- static Logger logger = Logger.getLogger(UserDaoTest.class);
- @Test
- public void loggerTest(){
- logger.info("info:进入了TestLog4j");
- logger.debug("debug:调试");
- logger.error("error:错误");
- logger.fatal("fatal:致命错误");
- }
- }
复制代码- -- 语法:select * from xxx limit startIndex,pageSize
- select * from user limit 3;
复制代码- List<User> selectLimit(Map<String,Integer> map);
复制代码 11.4 回顾Mysql多对一查询方式
11.5 一对多的处理
- 代码演示
- <select id="selectLimit" parameterType="map" resultMap="UserMap">
- select * from mybatis.user limit #{startIndex},#{pageSize}
- </select>
复制代码- public class UserDaoTest {
- @Test
- public void limitTest(){
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- UserMapper mapper = sqlSession.getMapper(UserMapper.class);
- Map<String, Integer> map = new HashMap<String, Integer>();
- map.put("startIndex",0);
- map.put("pageSize",2);
- List<User> list=mapper.selectLimit(map);
- for (User u:
- list) {
- System.out.println(u);
- }
- sqlSession.close();
- }
- }
复制代码- public interface TeacherMapper { <dependency>
- <groupId>log4j</groupId>
- <artifactId>log4j</artifactId>
- <version>1.2.17</version>
- </dependency> //获取指定老师下的所有学生 Teacher getTeacher(@Param("tid")int id); Teacher getTeacher2(@Param("tid")int id); List getStudents(@Param("tid")int id);}
复制代码- <select id="selectRowBounds" resultMap="UserMap">
- select * from mybatis.user
- </select>
复制代码- @Test
- public void selectRowBounds(){
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- RowBounds rowBounds = new RowBounds(0,2);
- List<User> list = sqlSession.selectList("com.yu.dao.UserMapper.selectRowBounds"
- ,null,rowBounds);
- for (User user : list) {
- System.out.println(user);
- }
- sqlSession.close();
- }
复制代码 11.6 小结
- 关联 association 多对一
- 集合 collection 一对多
- javaType 指定实体类中属性的类型
- ofType 用来指定映射到List或者集合中的pojo类型,泛型中的约束类型
注意点:
- 保证sql语句的可读性
- 注意一对多和多对一属性和字段的问题
- 面试要点
- Mysql引擎
- InnoDB底层原理
- 索引
- 索引优化
12 动态SQL
什么是动态SQL?
动态SQL是指根据不同的条件生成不同的SQL语句
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
12.1 搭建环境
- 搭建数据库
- @Select("select * from mybatis.user")
- List<User> selectAll();
复制代码 创建基础工程
- 导包
- 编写配置文件
- 编写实体类日
- <mappers>
- <mapper />
- </mappers>
复制代码 - 编写实体类对应Mapper接口和Mapper.xml文件
- @Test
- public void selectAll(){
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- //底层主要应用反射
- UserMapper mapper = sqlSession.getMapper(UserMapper.class);
- List<User> list=mapper.selectAll();
- for (User user : list) {
- System.out.println(user);
- }
- sqlSession.close();
- }
复制代码- [/code]
- [*]解决数据库字段名和实体类属性名不一致的问题[code]//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);
复制代码 12.2 if
- 使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。比如:
- @Test
- public void selectAll(){
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- //底层主要应用反射
- UserMapper mapper = sqlSession.getMapper(UserMapper.class);
- // List<User> list=mapper.selectAll();
- // for (User user : list) {
- // System.out.println(user);
- // }
- /**
- User u=mapper.selectUserById(1);
- System.out.println(u);
- */
- // boolean isInserted=mapper.insertUser(new User(4,"图拉真","dgsdgs"));
- // if (mapper.updateUser(new User(6,"寒江雪",null)))
- if (mapper.deleteUser(6))
- for (User user : mapper.selectAll()) {
- System.out.println(user);
- }
- sqlSession.close();
- }
复制代码 - 代码演示
- <dependency>
- <groupId>org.projectlombok</groupId>
- <artifactId>lombok</artifactId>
- <version>1.18.10</version>
- </dependency>
复制代码- select * from mybatis.blog where 1=1//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); and title = #{title}//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); and author = #{author}
复制代码- @Data支持: 无参构造,getter&setter,toString,hashCode,equals
- @AllArgsConstructor: 有参构造
- @NoArgsConstructor: 无参构造
复制代码 12.3 choose、when、otherwise
- 有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
- 还是上面的例子,但是策略变为:传入了 “title” 就按 “title” 查找,传入了 “author” 就按 “author” 查找的情形。若两者都没有传入,就返回标记为 featured 的 BLOG(这可能是管理员认为,与其返回大量的无意义随机 Blog,还不如返回一些由管理员挑选的 Blog)。
- create table `teacher`(
- `id` int not null,
- `name` varchar(30) default null,
- primary key(`id`)
- ) engine=InnoDB default charset=utf8;
- insert into teacher values (1,'王老师');
- create table `student`(
- `id` int not null,
- `name` varchar(30) default null,
- `tid` int not null,
- primary key(`id`),
- key `FK_tid` (`tid`),
- constraint `FK_tid` foreign key(`tid`) references `teacher`(`id`)
- ) engine=InnoDB default charset=utf8;
复制代码 - 代码演示
- @Data
- @AllArgsConstructor
- @NoArgsConstructor
- public class Student {
- private int id;
- private String name;
- private Teacher teacher;
- }
复制代码- select * from mybatis.blog//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); title=#{title}//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); and author=#{author}//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); and views=#{views}//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);
复制代码- List<Student> selectAll();
复制代码 12.4 trim、where、set
- 前面几个例子已经合宜地解决了一个臭名昭著的动态 SQL 问题。现在回到之前的 “if” 示例,这次我们将 “state = ‘ACTIVE’” 设置成动态条件,看看会发生什么。
- <resultMap id="student_teacher" type="Student">
- <result property="id" column="id"/>
- <result property="name" column="name"/>
-
- <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
- </resultMap>
- <select id="selectAll" resultMap="student_teacher">
- select * from mybatis.student
- </select>
- <select id="getTeacher" resultType="Teacher">
- select * from mybatis.teacher where id=#{tid}
- </select>
复制代码 - 如果没有匹配的条件会怎么样?最终这条 SQL 会变成这样:
- @Test
- public void selectAll(){
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
- List<Student> studentList = mapper.selectAll();
- for (Student s:
- studentList) {
- System.out.println(s);
- }
- sqlSession.close();
- }
复制代码 - 这会导致查询失败。如果匹配的只是第二个条件又会怎样?这条 SQL 会是这样:
- @Test
- public void selectAll(){
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
- List<Student> studentList = mapper.selectAll();
- for (Student s:
- studentList) {
- System.out.println(s);
- }
- sqlSession.close();
- }AND title like ‘someTitle’
复制代码 - 这个查询也会失败。这个问题不能简单地用条件元素来解决。这个问题是如此的难以解决,以至于解决过的人不会再想碰到这种问题。
- MyBatis 有一个简单且适合大多数场景的解决办法。而在其他场景中,可以对其进行自定义以符合需求。而这,只需要一处简单的改动:
- <select id="selectAll2" resultMap="S_T">
- select s.id sid,s.name sname,t.name tname
- from mybatis.student s,mybatis.teacher t
- where s.tid=t.id
- </select>
- <resultMap id="S_T" type="Student">
- <result property="id" column="sid"/>
- <result property="name" column="sname"/>
- <association property="teacher" javaType="Teacher">
- <result property="name" column="tname"/>
- </association>
- </resultMap>
复制代码 - where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
- 如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:
- prefixOverrides 属性会忽略通过管道符分隔的文本序列(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。
- 用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列,忽略其它不更新的列。比如:
- @Data
- @AllArgsConstructor
- @NoArgsConstructor
- public class Teacher {
- private int id;
- private String name;
- //老师拥有多个学生
- private List<Student> students;
- }
复制代码 - 如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:
- update Author//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); username=#{username}, password=#{password}, email=#{email}, bio=#{bio} where id=#{id}
复制代码 - 这个例子中,set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
- 来看看与 set 元素等价的自定义 trim 元素吧:
- 代码演示
- <select id="selectAll" resultType="Teacher">
- select * from mybatis.teacher
- </select>
- <select id="getTeacher" resultMap="S_T">
- select t.id tid, t.name tname,s.name sname
- from mybatis.teacher t,mybatis.student s
- where s.tid=tid and tid=#{tid}
- </select>
- <resultMap id="S_T" type="Teacher">
- <result property="id" column="tid"/>
- <result property="name" column="tname"/>
-
- <collection property="students" ofType="Student">
- <result property="name" column="sname"/>
- <result property="tid" column="tid"/>
- </collection>
- </resultMap>
- <select id="getTeacher2" resultMap="student_teacher">
- select * from mybatis.teacher where id=#{tid}
- </select>
- <resultMap id="student_teacher" type="Teacher">
- <result property="id" column="id"/>
- <result property="name" column="name"/>
- <collection property="students" column="id" ofType="Student" select="getStudents"/>
- </resultMap>
- <select id="getStudents" resultType="Student">
- select * from mybatis.student where tid=#{tid}
- </select>
复制代码- update mybatis.blog//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); title=#{title}, author=#{author} where id=#{id}
复制代码- create table blog(
- id varchar(50) not null comment '博客id',
- title varchar(100) not null comment '博客标题',
- author varchar(30) not null comment '博客作者',
- ctreate_time datetime not null comment '创建时间',
- views int not null comment '浏览量'
- )engine=InnoDB default charset=utf8;
复制代码 - 所谓动态sql,本质还是SQL语句,只是我们可以在SQL层面,执行逻辑代码
12.5 SQL片段
- 有的时候,我们可以将一些功能的部分抽取出来(类似函数的封装),方便复用
- 使用sql标签封装,在需要的地方使用include标签引入
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); title = #{title}//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); and author = #{author} select * from mybatis.blog//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);
复制代码 - 注意事项:
- 最好基于单表定义SQL片段
- 不要存在where标签
12.6 foreach
- 动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。比如:
- public interface BlogMapper {}
复制代码 - foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符,看它多智能!
- 提示 你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
- 至此,我们已经完成了与 XML 配置及映射文件相关的讨论。下一章将详细探讨 Java API,以便你能充分利用已经创建的映射配置。
- foreach的作用,就是为了替代下面这种复杂的语句
- <?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.yu.dao.BlogMapper">
- </mapper>
复制代码 - 代码演示(改一下数据库id)
- <settings>
- <setting name="logImpl" value="STDOUT_LOGGING"/>
- <setting name="mapUnderscoreToCamelCase" value="true"/>
- </settings>
复制代码- select * from mybatis.blog//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id); id=#{id}//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);
复制代码- //查询博客
- List<Blog> queryBlogIf(Map<String,Object> map);
复制代码 动态SQL就是在拼接SQL语句,我们只要保证SQL的正确性,按照SQL的格式去排列组合
13 缓存
13.1 简介
- 为什么要使用缓存
每次查询都要连接数据库,比较耗资源,我们把查询到的数据暂存到内存里面,下次查询的时候,从内存读取, 这个地方就叫缓存。
- 什么样的数据适用于缓存?
经常查询且不经常改变的数据
13.2 Mybatis缓存
- Mybatis系统默认定义了两级缓存
- 默认情况下,只有一级缓存开启(SqlSession缓存,也称为本地缓存)
- 二级缓存需要手动配置,它是基于namespace级别的缓存
- Mybatis定义了缓存接口Cache,可以通过实现Cache接口来自定义二级缓存
13.3 一级缓存
- 测试步骤
- 开启日志
- 测试在一个Session中查询两次的计量局
- 查看日志输出
- 代码演示
- <select id="queryBlogIf" parameterType="map" resultType="Blog">
- select * from mybatis.blog where 1=1
- <if test="title!=null">
- and title = #{title}
- </if>
- <if test="author!=null">
- and author = #{author}
- </if>
- </select>
复制代码- @Test
- public void queryBlogIf(){
- SqlSession sqlSession = MybatisUtils.getSqlSession();
- BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
- Map<String,Object> map=new HashMap<String,Object>();
- mapper.queryBlogIf(map);
- System.out.println("==================");
- map.put("title",(String)"MyBatis如此简单");
- mapper.queryBlogIf(map);
- System.out.println("==================");
- map.put("author",(String)"狂神说");
- mapper.queryBlogIf(map);
- System.out.println("==================");
- sqlSession.close();
- }
复制代码- <select id="findActiveBlogLike"
- resultType="Blog">
- SELECT * FROM BLOG WHERE state = ‘ACTIVE’
- <choose>
- <when test="title != null">
- AND title like #{title}
- </when>
- <when test="author != null and author.name != null">
- AND author_name like #{author.name}
- </when>
- <otherwise>
- AND featured = 1
- </otherwise>
- </choose>
- </select>
复制代码
- 缓存失效的情况
- 查询不同的Mapper.xml
- 增删改
- 查询不同的东西
- 手动清理缓存(sqlSession.clearCache())
13.4 二级缓存
- MyBatis 内置了一个强大的事务性查询缓存机制,它可以非常方便地配置和定制。 为了使它更加强大而且易于配置,我们对 MyBatis 3 中的缓存实现进行了许多改进。
- 默认情况下,只启用了本地的会话缓存,它仅仅对一个会话中的数据进行缓存。 要启用全局的二级缓存,只需要在你的 SQL 映射文件中添加一行:
- [/code]
- [*]基本上就是这样。这个简单语句的效果如下:
- [code]<select id="queryBlogChoose" parameterType="map" resultType="Blog">
- select * from mybatis.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>
复制代码 - 这些属性可以通过 cache 元素的属性来修改。比如:
- [/code]
- [*]这个更高级的配置创建了一个 FIFO 缓存,每隔 60 秒刷新,最多可以存储结果对象或列表的 512 个引用,而且返回的对象被认为是只读的,因此对它们进行修改可能会在不同线程中的调用者产生冲突。
- [*]使用二级缓存
- [list=1]
- [*]开启全局缓存
- [/list][code]
复制代码- [/code][list=1]
- [*]测试使用
- [/list][code]SELECT * FROM BLOG
- WHERE
- AND title like ‘someTitle’
复制代码
- 问题
- 小结
- 二级缓存在同一个Mapper下有效
- 所有的数据都会先放在一级缓存中
- 当会话提交或者关闭,数据会被转存到二级缓存中
13.5 缓存原理
- 图片演示
13.6 自定义缓存ehcache
- 简介
EhCache 是一个纯Java的进程内缓存框架,具有快速、精干等特点,是Hibernate中默认CacheProvider。Ehcache是一种广泛使用的开源Java分布式缓存。主要面向通用缓存,Java EE和轻量级容器。
- 使用
- <select id="findActiveBlogLike"
- resultType="Blog">
- SELECT * FROM BLOG
- <where>
- <if test="state != null">
- state = #{state}
- </if>
- <if test="title != null">
- AND title like #{title}
- </if>
- <if test="author != null and author.name != null">
- AND author_name like #{author.name}
- </if>
- </where>
- </select>
复制代码
- 写入配置文件(resources->ehcache.xml)
- //多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);//多个参数情况下,有两种解决方式,一个map封装,另一种是注解Param
- @Select("select * from mybatis.user where id=#{id}")
- User selectUserById(@Param("id") int id);
- @Select("select * from mybatis.user")
- List<User> selectAll();
- @Insert("insert into mybatis.user() values(#{id},#{name},#{password}) ")
- boolean insertUser(User u);
- @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
- boolean updateUser(User u);
- @Delete("delete from mybatis.user where id=#{id}")
- boolean deleteUser(@Param("id") int id);
复制代码- [/code][list=1]
- [*]测试使用(用之前的代码即可)
- [/list]
- [*]自定义缓存
- 只要实现了org.apache.ibatis.cache.Cache接口,就能定义自己的缓存,但是实现比较复杂,只需要会使用就行,ehcache是继承了AbstractEhcacheCache,该类已经实现了Cache接口。
- [code]<update id="updateAuthorIfNecessary">
- update Author
- <set>
- <if test="username != null">username=#{username},</if>
- <if test="password != null">password=#{password},</if>
- <if test="email != null">email=#{email},</if>
- <if test="bio != null">bio=#{bio}</if>
- </set>
- where id=#{id}
- </update>
复制代码 - 实际开发中使用的缓存
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作! |