1、项目搭建 1.1、引入依赖 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 <dependencies > <dependency > <groupId > org.mybatis</groupId > <artifactId > mybatis</artifactId > <version > 3.5.10</version > </dependency > <dependency > <groupId > junit</groupId > <artifactId > junit</artifactId > <version > 4.13.2</version > <scope > test</scope > </dependency > <dependency > <groupId > mysql</groupId > <artifactId > mysql-connector-java</artifactId > <version > 8.0.29</version > </dependency > <dependency > <groupId > org.projectlombok</groupId > <artifactId > lombok</artifactId > <version > 1.18.24</version > <scope > provided</scope > </dependency > </dependencies >
1.2、创建表
创建mysql表user
1 2 3 4 5 6 7 8 9 CREATE TABLE `user ` ( `id` int NOT NULL AUTO_INCREMENT, `username` varchar (20 ) DEFAULT NULL , `password` varchar (20 ) DEFAULT NULL , `age` int DEFAULT NULL , `gender` char (1 ) DEFAULT NULL , `email` varchar (50 ) DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE= InnoDB DEFAULT CHARSET= utf8;
1.3、实体类
在entity包下创建对应的实体类User.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data @NoArgsConstructor @AllArgsConstructor public class User { private Integer id; private String username; private String password; private Integer age; private String gender; private String email; }
1.4、配置文件
在src/main/resources下创建mybatis配置文件mybatis-config.xml(习惯上命名,不强制要求)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd" > <configuration > <environments default ="development" > <environment id ="development" > <transactionManager type ="JDBC" /> <dataSource type ="POOLED" > <property name ="driver" value ="com.mysql.cj.jdbc.Driver" /> <property name ="url" value ="jdbc:mysql://localhost:3306/ssm?serverTimezone=UTC" /> <property name ="username" value ="root" /> <property name ="password" value ="123456" /> </dataSource > </environment > </environments > <mappers > <mapper resource ="mapper/UserMapper.xml" /> </mappers > </configuration >
1.4、mapper接口
在mapper包下创建对应的mapper接口UserMapper.java Mybatis中的mapper接口相当于以前的dao。但是我们不用需要提供对应的实现类。
1 2 3 4 5 6 public interface UserMapper { int insertUser () ; }
1.5、映射文件
在src/main/resources下新建文件夹mapper,并在该文件夹下创建映射文件UserMapper.xml。 映射文件命名规则:对应的实体类的类名+Mapper.xml。 namespace命名空间对应着mapper接口的全类名。
1 2 3 4 5 6 7 8 9 10 <?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.dyz.mapper.UserMapper" > <insert id ="insertUser" > insert into user values (null, 'admin', '123456', 22, '男', '12345@qq.com') </insert > </mapper >
1.6、测试 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 @Test public void testInsert () throws IOException { InputStream is = Resources.getResourceAsStream("mybatis-config.xml" ); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder (); SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is); SqlSession sqlSession = sqlSessionFactory.openSession(true ); UserMapper userMapper = sqlSession.getMapper(UserMapper.class); int result = userMapper.insertUser(); System.out.println("运行结果:" + result); }
SqlSession:代表Java程序和数据库之间的会话。(HttpSession是Java程序和浏览器之间的会话) SqlSessionFactory:是“生产”SqlSession的“工厂”。 工厂模式:如果创建某一个对象,使用的过程基本固定,那么我们就可以把创建这个对象的相关代码封装到一个“工厂类”中,以后都使用这个工厂类来“生产”我们需要的对象。
1.7、加入日志功能
1.引入依赖
1 2 3 4 5 6 <dependency > <groupId > log4j</groupId > <artifactId > log4j</artifactId > <version > 1.2.17</version > </dependency >
2.在src/main/resources目录下加入log4j配置log4j.properties。
日志的级别: FATAL(致命)>ERROR(错误)>WARN(警告)>INFO(信息)>DEBUG(调试) 从左到右打印的内容越来越详细
1 2 3 4 5 log4j.rootLogger=ERROR,Console log4j.logger.com.dyz=DEBUG log4j.appender.Console=org.apache.log4j.ConsoleAppender log4j.appender.Console.layout=org.apache.log4j.PatternLayout log4j.appender.Console.layout.ConversionPattern=%5p [%t] %m%n
控制台输出(便于学习观察内部所调用的sql): DEBUG [main] ==> Preparing: insert into user values (null, ‘admin’, ‘123456’, 22, ‘男’, ‘12345@qq.com ‘) DEBUG [main] ==> Parameters: DEBUG [main] <== Updates: 1 运行结果:1
2、封装SqlSessionUtil
若每次测试都要写上面一大段相同代码,过于冗余,因此将创建SqlSession部分相同代码提取出来,封装成SqlSessionUtil
1 2 3 4 5 6 7 8 9 10 11 12 13 14 public class SqlSessionUtil { public static SqlSession getSqlSession () { SqlSession sqlSession = null ; try { InputStream is = Resources.getResourceAsStream("mybatis-config.xml" ); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder (); SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is); sqlSession = sqlSessionFactory.openSession(true ); } catch (IOException e) { e.printStackTrace(); } return sqlSession; } }
直接调用SqlSessionUtil.getSqlSession()来创建SqlSession对象
1 2 3 4 5 6 7 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); mapper.insertUser(); sqlSession.close(); }
3、基本增删改查
mapper接口
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 public interface UserMapper { int insertUser () ; int updateUser () ; int deleteUser () ; User selectUser () ; List<User> selectUserList () ; }
对应的xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 <mapper namespace ="com.dyz.mapper.UserMapper" > <insert id ="insertUser" > insert into user values (null, 'admin', '123456', 22, '男', '12345@qq.com') </insert > <update id ="updateUser" > update user set username='root', password='123' where id = 1 </update > <delete id ="deleteUser" > delete from user where id = 1 </delete > <select id ="selectUser" resultType ="com.dyz.entity.User" > select * from user where id = 1 </select > <select id ="selectUserList" resultType ="com.dyz.entity.User" > select * from user </select > </mapper >
测试类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); System.out.println("---------增加---------" ); mapper.insertUser(); System.out.println("---------修改---------" ); mapper.updateUser(); System.out.println("---------查询一条---------" ); User user = mapper.selectUser(); System.out.println(user); System.out.println("---------查询多条---------" ); List<User> users = mapper.selectUserList(); users.forEach(System.out::println); System.out.println("---------删除---------" ); mapper.deleteUser(); sqlSession.close(); }
输出结果: ———增加——— DEBUG [main] ==> Preparing: insert into user values (null, ‘admin’, ‘123456’, 22, ‘男’, ‘12345@qq.com ‘) DEBUG [main] ==> Parameters: DEBUG [main] <== Updates: 1 ———修改——— DEBUG [main] ==> Preparing: update user set username=’root’, password=’123’ where id = 1 DEBUG [main] ==> Parameters: DEBUG [main] <== Updates: 1 ———查询一条——— DEBUG [main] ==> Preparing: select * from user where id = 1 DEBUG [main] ==> Parameters: DEBUG [main] <== Total: 1 User(id=1, username=root, password=123, age=22, gender=男, email=12345@qq.com ) ———查询多条——— DEBUG [main] ==> Preparing: select * from user DEBUG [main] ==> Parameters: DEBUG [main] <== Total: 3 User(id=1, username=root, password=123, age=22, gender=男, email=12345@qq.com ) User(id=2, username=admin, password=123456, age=22, gender=男, email=12345@qq.com ) User(id=3, username=admin, password=123456, age=22, gender=男, email=12345@qq.com ) ———删除——— DEBUG [main] ==> Preparing: delete from user where id = 1 DEBUG [main] ==> Parameters: DEBUG [main] <== Updates: 1
4、核心配置详解
核心配置文件中的标签必须按照如下固定的顺序: properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?
4.1、environments标签
environments:配置数据库环境 属性: default:设置默认使用环境的id 子标签: 1、environment:设置一个具体的数据库连接环境 属性: id:设置环境唯一标识,不能重复 子标签: 1.1、transactionManager:设置事务管理器 属性:type:设置事务管理方式 type=”JDBC”(JDBC原生的事务管理方式) type=”MANAGED”(被管理,例如Spring) 1.2、dataSource:设置数据源 属性:type:设置数据源类型 type=”POOLED”(表示使用数据库连接池) type=”UNPOOLED”(表示不使用数据库连接池) type=”JNDI”(表示使用上下文中数据源)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 <environments default ="development" > <environment id ="development" > <transactionManager type ="JDBC" /> <dataSource type ="POOLED" > <property name ="driver" value ="com.mysql.cj.jdbc.Driver" /> <property name ="url" value ="jdbc:mysql://localhost:3306/ssm?serverTimezone=UTC" /> <property name ="username" value ="root" /> <property name ="password" value ="123456" /> </dataSource > </environment > <environment id ="test" > <transactionManager type ="JDBC" /> <dataSource type ="POOLED" > <property name ="driver" value ="com.mysql.cj.jdbc.Driver" /> <property name ="url" value ="jdbc:mysql://localhost:3306/ssm2?serverTimezone=UTC" /> <property name ="username" value ="root" /> <property name ="password" value ="123456" /> </dataSource > </environment > </environments >
4.2、properties标签
用于引入properties文件,此后就可以在当前文件中使用${key}的方式访问value。
在resources目录下创建jdbc.properties
1 2 3 4 jdbc.driver =com.mysql.cj.jdbc.Driver jdbc.url =jdbc:mysql://localhost:3306/ssm?serverTimezone=UTC jdbc.username =root jdbc.password =123456
使用properties标签引入外部文件
1 <properties resource ="jdbc.properties" />
environment标签的数据源就可以改为:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 <environments default ="development" > <environment id ="development" > <transactionManager type ="JDBC" /> <dataSource type ="POOLED" > <property name ="driver" value ="${jdbc.driver}" /> <property name ="url" value ="${jdbc.url}" /> <property name ="username" value ="${jdbc.username}" /> <property name ="password" value ="${jdbc.password}" /> </dataSource > </environment > </environments >
4.3、typeAliases标签
类型别名标签,用于设置某个java类型的别名。
typeAlias:设置某个类型的别名 属性:type:设置需要设置别名的类型 alias:设置某个类的别名,该属性可以省略,省略后默认的别名即类名,且不区分大小写
package:给包下所有的类起默认的别名 属性:name:设置需要起别名的类所在的包
1 2 3 4 5 6 <typeAliases > <package name ="com.dyz.entity" /> </typeAliases >
然后再UserMapper.xml就可以直接使用该别名
1 2 3 <select id ="selectUserList" resultType ="user" > select * from user </select >
在MyBatis中,对于Java中常用的类型都设置了类型别名 例如: java.lang.Integer–>int|integer 例如: int–>_int|_integer 例如: Map–>map,List–>list
4.4、mappers标签
用于引入映射文件的标签
mapper:引入单个映射文件 属性:resource:映射文件路径
package:引入包下所有映射文件 属性:name:映射文件所在的包名 要求:1、mapper接口所在的包要和映射文件所在的包一致 2、mapper接口要和映射文件的名字一致
1 2 3 4 5 <mappers > <mapper resource ="mapper/UserMapper.xml" /> </mappers >
5、参数的获取
MyBatis获取参数的两种方式:${} 和 #{}。
5.1、${}和#{}区别 举个例子,使用username查询用户信息:
UserMapper.java接口里面添加selectUserByUsername(String username)
方法
1 User selectUserByUsername (String username) ;
使用${}获取参数
1 2 3 4 5 6 <select id ="selectUserByUsername" resultType ="com.dyz.entity.User" > select * from user where username = '${username}' </select >
使用#{}获取参数
1 2 3 4 5 <select id ="selectUserByUsername" resultType ="com.dyz.entity.User" > select * from user where username = #{username} </select >
测试
1 2 3 4 5 6 7 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.selectUserByUsername("admin" ); System.out.println(user); }
使用 ${} 打印的sql语句:select * from user where username = ‘admin’ DEBUG [main] ==> Preparing: select * from user where username = ‘admin’ DEBUG [main] ==> Parameters: DEBUG [main] <== Total: 1 User(id=2, username=admin, password=123456, age=22, gender=男, email=12345@qq.com )
使用 #{} 打印的sql语句:select * from user where username = ? DEBUG [main] ==> Preparing: select * from user where username = ? DEBUG [main] ==> Parameters: admin(String) DEBUG [main] <== Total: 1 User(id=2, username=admin, password=123456, age=22, gender=男, email=12345@qq.com )
因此: ${}的本质就是字符串拼接,#{}的本质就是占位符赋值 。 ${}使用字符串拼接的方式拼接sql,若为字符串类型或日期类型的字段进行赋值时,需要手动加单引号 ; #{}使用占位符赋值的方式拼接sql,此时为字符串类型或日期类型的字段进行赋值时,可以自动添加单引号
注意:在实际项目中大多使用#{},#{}是预编译处理,可以有效的防止sql注入问题。
5.2、多参数获取 当Mapper接口中方法参数为一个时,在对应的xml映射文件中#{}填写任意值,Mybatis都可正确的识别参数 例如: 在User selectUserByUsername(String username);
方法中只有一个username参数, 因此在映射文件中的sql语句可以写成:select * from user where username = #{username}
也可以写成:select * from user where username = #{abc}
,都能正常运行。
那么,如果方法中有多个参数呢? 比如:
1 2 3 public interface UserMapper { User selectByUsernameAndPassword (String username, String password) ; }
1 2 3 4 5 6 <select id ="selectByUsernameAndPassword" resultType ="com.dyz.entity.User" > select * from user where username = #{username} and password = #{password} </select >
显然,这种写法会报错: org.apache.ibatis.exceptions.PersistenceException: Error querying database. Cause: org.apache.ibatis.binding.BindingException: Parameter ‘username’ not found. Available parameters are [arg1, arg0, param1, param2] Cause: org.apache.ibatis.binding.BindingException: Parameter ‘username’ not found. Available parameters are [arg1, arg0, param1, param2]
那么如何让Mybatis知道多个参数所对应的是sql语句中的哪一个占位符呢? 我们注意到,在上面的报错提示中,框架提示我们可以使用#{arg0},#{arg1}或#{param1},#{param2}来对应username和password,这是可行的,因为Mybatis实际上是将这些参数封装在了Map集合中,arg0、param1这些就是默认的key,参数值就是对应的value。因此,我们可以将sql改为select * from user where username = #{param1} and password = #{param2}
。
当然我们还可以自定义一个Map来存储这些变量,并且使用Map来进行查询数据。
1 2 3 public interface UserMapper { User selectByUsernameAndPassword (Map<String, Object> map) ; }
1 2 3 4 5 6 7 8 9 10 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); HashMap<String, Object> map = new HashMap <>(); map.put("username" ,"admin" ); map.put("password" ,"123456" ); User user = mapper.selectByUsernameAndPassword(map); System.out.println(user); }
但是在实际开发中 ,使用#{param}无法做到见名知义,而自定义Map集合又过于繁琐,所以,Mybatis提供了一个**@Param注解**来解决这个问题。下面是它的使用方式:
在mapper接口方法的参数中给每个参数使用**@Param(“value”)**来标识参数
1 2 3 4 public interface UserMapper { User selectByUsernameAndPassword (@Param("username") String username, @Param("password") String password) ;}
在映射文件中使用对应写上**#{value}**,这样就可以对应上多个参数值了
1 2 3 4 5 6 <select id ="selectByUsernameAndPassword" resultType ="com.dyz.entity.User" > select * from user where username = #{username} and password = #{password} </select >
5.3、对象参数获取
当mapper接口方法中的参数为一个实体类对象,那么该如何写sql语句呢?
1 2 3 public interface UserMapper { int insertUser (User user) ; }
此时只需要在#{}或者${}中填写实体类中的成员变量即可,Mybatis可以将实体类中的属性通过get,set方法自动转化为这里的参数
1 2 3 4 <insert id ="insertUser" > insert into user values (null, #{username}, #{password}, #{age}, #{gender}, #{email}) </insert >
正常运行: DEBUG [main] ==> Preparing: insert into user values (null, ?, ?, ?, ?, ?) DEBUG [main] ==> Parameters: abc(String), 123(String), 20(Integer), 女(String), 123@qq.com (String) DEBUG [main] <== Updates: 1 1
1 2 3 4 5 6 7 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int res = mapper.insertUser(new User (null , "abc" , "123" , 20 , "女" , "123@qq.com" )); System.out.println(res); }
6、各种查询 6.1、查询实体类对象 1 User selectUserById (@Param("id") int id) ;
1 2 3 <select id ="selectUserById" resultType ="User" > select * from user where id = #{id} </select >
6.2、查询List集合 1 List<User> selectUserList () ;
1 2 3 <select id ="selectUserList" resultType ="User" > select * from user </select >
6.3、查询单个数据
1 2 3 <select id ="getCount" resultType ="int" > select count(*) from user </select >
6.4、查询一条Map集合 1 Map<String, Object> getUserToMap (@Param("id") int id) ;
1 2 3 <select id ="getUserToMap" resultType ="map" > select * from user where id = #{id} </select >
查询结果:{password=123456, gender=男, id=2, age=22, email=12345@qq.com , username=admin} Map集合的key就是属性名,value就是查询出来的值,若数据库某个字段的值为null,那么查询出来的结果就不会存放在map中。
6.5、查询多条Map集合 ①方式一:
将查询出来的每条结果都封装为一个Map,查询出来几条数据就有几个Map,然后将这些Map存放在List集合中统一返回。
1 List<Map<String, Object>> getAllUserToMap () ;
1 2 3 <select id ="getAllUserToMap" resultType ="map" > select * from user </select >
查询结果: [ {password=123456, gender=男, id=2, age=22, email=12345@qq.com , username=admin}, {password=123, gender=女, id=4, age=20, email=123@qq.com , username=abc} ]
②方式二:
将查询出来的每条结果都封装为一个Map,查询出来几条数据就有几个Map,然后将这些Map再封装成一个大的Map统一返回。
1 2 3 4 @MapKey("id") Map<String, Object> getAllUserToMap () ;
1 2 3 <select id ="getAllUserToMap" resultType ="map" > select * from user </select >
查询结果: { 2={password=123456, gender=男, id=2, age=22, email=12345@qq.com , username=admin}, 4={password=123, gender=女, id=4, age=20, email=123@qq.com , username=abc} }
6.6、模糊查询 1 List<User> searchUserByUsername (@Param("searchInfo") String searchInfo) ;
1 2 3 4 5 <select id ="searchUserByUsername" resultType ="user" > select * from user where username like "%"#{searchInfo}"%" </select >
7、自定义映射resultMap 7.1、准备工作
新建两张mysql表:员工表(emp)和部门表(dept)。员工表中包含了部门id
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 CREATE TABLE `emp` ( `emp_id` int NOT NULL AUTO_INCREMENT, `emp_name` varchar (20 ) DEFAULT NULL , `age` int DEFAULT NULL , `gender` char (1 ) DEFAULT NULL , `dept_id` int DEFAULT NULL , PRIMARY KEY (`emp_id`) ) ENGINE= InnoDB AUTO_INCREMENT= 5 DEFAULT CHARSET= utf8; INSERT INTO `emp` VALUES (1 , '张三' , 20 , '男' , 1 );INSERT INTO `emp` VALUES (2 , '李四' , 22 , '男' , 2 );INSERT INTO `emp` VALUES (3 , '王五' , 21 , '男' , 3 );INSERT INTO `emp` VALUES (4 , '赵六' , 22 , '男' , 1 );CREATE TABLE `dept` ( `dept_id` int NOT NULL AUTO_INCREMENT, `dept_name` varchar (20 ) DEFAULT NULL , PRIMARY KEY (`dept_id`) ) ENGINE= InnoDB AUTO_INCREMENT= 4 DEFAULT CHARSET= utf8; INSERT INTO `dept` VALUES (1 , '部门A' );INSERT INTO `dept` VALUES (2 , '部门B' );INSERT INTO `dept` VALUES (3 , '部门C' );
创建实体类:Emp.java、Dept.java
1 2 3 4 5 6 7 8 9 @Data @AllArgsConstructor @NoArgsConstructor public class Emp { private Integer empId; private String empName; private Integer age; private String gender; }
1 2 3 4 5 6 7 @Data @AllArgsConstructor @NoArgsConstructor public class Dept { private Integer deptId; private String deptName; }
因为mysql中的字段名跟实体类中的属性名不一致(例如:mysql字段名为emp_id,而实体类属性名为empId),因此在使用Mybatis时候会出现属性无法赋值的情况,这时候需要在mybatis配置文件中加上将下划线映射为驼峰命名的配置。
1 2 3 4 <settings > <setting name ="mapUnderscoreToCamelCase" value ="true" /> </settings >
7.2、最基础的使用
虽然开启下划线自动映射为驼峰命名的配置可以应对绝大部分字段名和属性名不一致的情况,但是在一些复杂场景下,驼峰命名显然也无法解决字段名和属性名不一致的问题,这时候就需要用到resultMap来自定义映射关系了。
下面是resultMap最简单的使用示例:
EmpMapper.java
1 2 3 public interface EmpMapper { Emp getEmpById (@Param("empId") Integer empId) ; }
EmpMapper.xml
resultMap:设置自定义映射关系
id:唯一标识
type:处理映射关系的实体类类型
常用子标签:
id:处理主键和实体类中属性的映射关系
result:处理普通字段和实体类中属性的映射关系
column:设置映射关系中的字段名,必须是sql查询出的某个字段
property:设置映射关系中的属性的属性名,必须是处理的实体类类型中的属性名
1 2 3 4 5 6 7 8 9 10 11 <resultMap id ="empResultMap" type ="Emp" > <id column ="emp_id" property ="empId" /> <result column ="emp_name" property ="empName" /> <result column ="age" property ="age" /> <result column ="gender" property ="gender" /> </resultMap > <select id ="getEmpById" resultType ="emp" > select * from emp where emp_id = #{empId} </select >
测试方法
1 2 3 4 5 6 7 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); Emp emp = mapper.getEmpById(1 ); System.out.println(emp); }
即使去掉 也是可以正常运行的。 运行结果: DEBUG [main] ==> Preparing: select * from emp where emp_id = ? DEBUG [main] ==> Parameters: 1(Integer) DEBUG [main] <== Total: 1 Emp(empId=1, empName=张三, age=20, gender=男)
7.3、多对一映射
首先在Emp中加上部门属性,员工和部门是多对一的关系,因此每个员工应该都对应一个部门
1 2 3 4 5 6 7 8 9 10 11 @Data @AllArgsConstructor @NoArgsConstructor public class Emp { private Integer empId; private String empName; private Integer age; private String gender; private Dept dept; }
先来看不使用resultMap的结果:
mapper接口:
1 Emp getEmpAndDeptByEmpId (@Param("empId") Integer empId) ;
xml映射文件:
1 2 3 4 5 6 7 <select id ="getEmpAndDeptByEmpId" resultType ="com.dyz.entity.Emp" > select * from emp left join dept on emp.emp_id = dept.dept_id where emp.emp_id = #{empId} </select >
测试:
1 2 3 4 5 6 7 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); Emp emp = mapper.getEmpAndDeptByEmpId(1 ); System.out.println(emp); }
运行结果: DEBUG [main] ==> Preparing: select * from emp left join dept on emp.emp_id = dept.dept_id where emp.emp_id = ? DEBUG [main] ==> Parameters: 1(Integer) DEBUG [main] <== Total: 1 Emp(empId=1, empName=张三, age=20, gender=男, dept=null)
我们可以发现,dept无法自动映射,因为mysql最后查出来的并不是dept,而是dept里面的字段(dept_id和dept_name),因此Mybatis并不能自动将dept和这两个字段联系起来,因此就需要使用resultMap来进行处理。
①方式一:级联方式 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 <resultMap id ="empAndDeptResultMap" type ="Emp" > <id column ="emp_id" property ="empId" /> <result column ="emp_name" property ="empName" /> <result column ="age" property ="age" /> <result column ="gender" property ="gender" /> <result column ="dept_id" property ="dept.deptId" /> <result column ="dept_name" property ="dept.deptName" /> </resultMap > <select id ="getEmpAndDeptByEmpId" resultMap ="empAndDeptResultMap" > select * from emp left join dept on emp.emp_id = dept.dept_id where emp.emp_id = #{empId} </select >
输出结果: DEBUG [main] ==> Preparing: select * from emp left join dept on emp.emp_id = dept.dept_id where emp.emp_id = ? DEBUG [main] ==> Parameters: 1(Integer) DEBUG [main] <== Total: 1 Emp(empId=1, empName=张三, age=20, gender=男, dept=Dept(deptId=1, deptName=部门A))
②方式二:association标签
association:处理多对一的映射关系(处理实体类类型的属性)
property:设置需要处理映射关系的属性名
javaType:设置需要处理的属性类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 <resultMap id ="empAndDeptResultMap" type ="Emp" > <id column ="emp_id" property ="empId" /> <result column ="emp_name" property ="empName" /> <result column ="age" property ="age" /> <result column ="gender" property ="gender" /> <association property ="dept" javaType ="Dept" > <id column ="dept_id" property ="deptId" /> <result column ="dept_name" property ="deptName" /> </association > </resultMap > <select id ="getEmpAndDeptByEmpId" resultMap ="empAndDeptResultMap" > select * from emp left join dept on emp.emp_id = dept.dept_id where emp.emp_id = #{empId} </select >
输出结果: DEBUG [main] ==> Preparing: select * from emp left join dept on emp.emp_id = dept.dept_id where emp.emp_id = ? DEBUG [main] ==> Parameters: 1(Integer) DEBUG [main] <== Total: 1 Emp(empId=1, empName=张三, age=20, gender=男, dept=Dept(deptId=1, deptName=部门A))
③方式三:分步查询
EmpMapper.java接口:
1 Emp getEmpAndDeptByStepOne (@Param("empId") Integer empId) ;
EmpMapper.xml映射文件:
select:设置分步查询,查询某个属性的值的sql的唯一标识(namespace.sqlId) column:将sql以及查询结果中的某个字段设置为分步查询的条件
1 2 3 4 5 6 7 8 9 10 11 12 13 <resultMap id ="empAndDeptByStepResultMap" type ="Emp" > <id column ="emp_id" property ="empId" /> <result column ="emp_name" property ="empName" /> <result column ="age" property ="age" /> <result column ="gender" property ="gender" /> <association property ="dept" select ="com.dyz.mapper.DeptMapper.getEmpAndDeptByStepTwo" column ="dept_id" /> </resultMap > <select id ="getEmpAndDeptByStepOne" resultMap ="empAndDeptByStepResultMap" > select * from emp where emp_id = #{empId} </select >
DeptMapper.java接口:
1 Dept getEmpAndDeptByStepTwo (@Param("deptId") Integer deptId) ;
DeptMapper.xml映射文件:
1 2 3 <select id ="getEmpAndDeptByStepTwo" resultType ="Dept" > select * from dept where dept_id = #{deptId} </select >
输出结果: DEBUG [main] ==> Preparing: select * from emp where emp_id = ? DEBUG [main] ==> Parameters: 1(Integer) DEBUG [main] ====> Preparing: select * from dept where dept_id = ? DEBUG [main] ====> Parameters: 1(Integer) DEBUG [main] <==== Total: 1 DEBUG [main] <== Total: 1 Emp(empId=1, empName=张三, age=20, gender=男, dept=Dept(deptId=1, deptName=部门A))
分步查询的优点:可以实现延迟加载
前提:必须在配置文件中配置:
lazyLoadingEnabled:延迟加载的全局开关。当开启时,所有关联对象都会延迟加载
aggressiveLazyLoading:当开启时,任何方法的调用都会加载该对象的所有属性。否则,每个属性会按需加载
1 2 3 4 5 6 7 8 <settings > <setting name ="mapUnderscoreToCamelCase" value ="true" /> <setting name ="lazyLoadingEnabled" value ="true" /> <setting name ="aggressiveLazyLoading" value ="false" /> </settings >
此时就可以实现全局按需加载,获取的数据是什么,就只会执行相应的sql。
测试:当只使用到emp.getEmpName(),而没用到dept相关属性,那么只会查询一步
1 2 3 4 5 6 7 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); Emp emp = mapper.getEmpAndDeptByStepOne(1 ); System.out.println(emp.getEmpName()); }
只会输出一条sql语句: DEBUG [main] ==> Preparing: select * from emp where emp_id = ? DEBUG [main] ==> Parameters: 1(Integer) DEBUG [main] <== Total: 1 张三
若有个别的分步查询不希望延时加载 ,那么可通过association和collection中的fetchType属性 设置当前单个的分步查询是否使用延迟加载 , fetchType=”lazy(延迟加载)|eager(立即加载)”
7.4、一对多映射
首先在Dept中加上员工属性,部门和员工是一对多的关系,因此每个部门有多个员工
1 2 3 4 5 6 7 8 9 @Data @AllArgsConstructor @NoArgsConstructor public class Dept { private Integer deptId; private String deptName; private List<Emp> emps; }
①方式一:collection标签
DeptMapper.java:
1 Dept getDeptAndEmpByDeptId (@Param("deptId") Integer deptId) ;
DeptMapper.xml:
collection:处理一对多的映射关系(处理集合类型的属性)
property:设置需要处理映射关系的属性名
ofType:设置需要处理的集合里面的元素类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 <resultMap id ="deptAndEmpResultMap" type ="Dept" > <id column ="dept_id" property ="deptId" /> <result column ="dept_name" property ="deptName" /> <collection property ="emps" ofType ="Emp" > <id column ="emp_id" property ="empId" /> <result column ="emp_name" property ="empName" /> <result column ="age" property ="age" /> <result column ="gender" property ="gender" /> </collection > </resultMap > <select id ="getDeptAndEmpByDeptId" resultMap ="deptAndEmpResultMap" > select * from dept left join emp on dept.dept_id = emp.emp_id where dept.dept_id = #{deptId} </select >
测试:
1 2 3 4 5 6 7 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); DeptMapper mapper = sqlSession.getMapper(DeptMapper.class); Dept dept = mapper.getDeptAndEmpByDeptId(1 ); System.out.println(dept); }
运行结果: DEBUG [main] ==> Preparing: select * from dept left join emp on dept.dept_id = emp.dept_id where dept.dept_id = ? DEBUG [main] ==> Parameters: 1(Integer) DEBUG [main] <== Total: 2 Dept(deptId=1, deptName=部门A, emps=[Emp(empId=1, empName=张三, age=20, gender=男, dept=null), Emp(empId=4, empName=赵六, age=22, gender=男, dept=null)])
②方式二:分步查询
DeptMapper.java
1 Dept getDeptAndEmpByStepOne (@Param("deptId") Integer deptId) ;
DeptMapper.xml
1 2 3 4 5 6 7 8 9 10 11 <resultMap id ="deptAndEmpByStepResultMap" type ="Dept" > <id column ="dept_id" property ="deptId" /> <result column ="dept_name" property ="deptName" /> <collection property ="emps" select ="com.dyz.mapper.EmpMapper.getDeptAndEmpByStepTwo" column ="dept_id" /> </resultMap > <select id ="getDeptAndEmpByStepOne" resultMap ="deptAndEmpByStepResultMap" > select * from dept where dept_id = #{deptId} </select >
EmpMapper.java
1 List<Emp> getDeptAndEmpByStepTwo (@Param("deptId") Integer deptId) ;
EmpMapper.xml
1 2 3 <select id ="getDeptAndEmpByStepTwo" resultType ="Emp" > select * from emp where dept_id = #{deptId} </select >
测试:
1 2 3 4 5 6 7 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); DeptMapper mapper = sqlSession.getMapper(DeptMapper.class); Dept dept = mapper.getDeptAndEmpByStepOne(1 ); System.out.println(dept); }
输出: DEBUG [main] ==> Preparing: select * from dept where dept_id = ? DEBUG [main] ==> Parameters: 1(Integer) DEBUG [main] <== Total: 1 DEBUG [main] ==> Preparing: select * from emp where dept_id = ? DEBUG [main] ==> Parameters: 1(Integer) DEBUG [main] <== Total: 2 Dept(deptId=1, deptName=部门A, emps=[Emp(empId=1, empName=张三, age=20, gender=男, dept=null), Emp(empId=4, empName=赵六, age=22, gender=男, dept=null)])
8、动态SQL
Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能,它存在的意义是为了解决拼接SQL语句字符串时的痛点问题。
9.1、if标签
if标签可通过test属性的表达式进行判断,若表达式的结果为true,则标签中的内容会执行;反之标签中的内容不会执行。
EmpMapper.java:根据条件查询员工
1 List<Emp> getEmpByCondition (Emp emp) ;
对应的EmpMapper.xml
1 2 3 4 5 6 7 8 9 10 11 12 <select id ="getEmpByCondition" resultType ="Emp" > select * from emp where 1 = 1 <if test ="empName != null and empName != ''" > and emp_name = #{empName} </if > <if test ="age != null and age != ''" > and age = #{age} </if > <if test ="gender != null and gender != ''" > and gender = #{gender} </if > </select >
测试:根据不同条件查询结果
1 2 3 4 5 6 7 8 9 10 11 12 13 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); List<Emp> empList = mapper.getEmpByCondition(new Emp (null , "张三" , 20 , "男" )); empList.forEach(System.out::println); System.out.println("----------------------------------------------------" ); List<Emp> empList2 = mapper.getEmpByCondition(new Emp (null , null , 22 , "男" )); empList2.forEach(System.out::println); System.out.println("----------------------------------------------------" ); List<Emp> empList3 = mapper.getEmpByCondition(new Emp ()); empList3.forEach(System.out::println); }
输出结果:注意观察SQL语句 DEBUG [main] ==> Preparing: select * from emp where 1 = 1 and emp_name = ? and age = ? and gender = ? DEBUG [main] ==> Parameters: 张三(String), 20(Integer), 男(String) DEBUG [main] <== Total: 1 Emp(empId=1, empName=张三, age=20, gender=男) -————————————————— DEBUG [main] ==> Preparing: select * from emp where 1 = 1 and age = ? and gender = ? DEBUG [main] ==> Parameters: 22(Integer), 男(String) DEBUG [main] <== Total: 2 Emp(empId=2, empName=李四, age=22, gender=男) Emp(empId=4, empName=赵六, age=22, gender=男) -————————————————— DEBUG [main] ==> Preparing: select * from emp where 1 = 1 DEBUG [main] ==> Parameters: DEBUG [main] <== Total: 4 Emp(empId=1, empName=张三, age=20, gender=男) Emp(empId=2, empName=李四, age=22, gender=男) Emp(empId=3, empName=王五, age=21, gender=男) Emp(empId=4, empName=赵六, age=22, gender=男)
9.2、where标签
where标签一般和if结合使用: 1、若where标签中的if条件都不满足,则where标签没有任何功能。 2、若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方的and删除。 注意:where不能删除条件最后面的and。
所以上面的if案例可以优化为:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 <select id ="getEmpByCondition" resultType ="Emp" > select * from emp <where > <if test ="empName != null and empName != ''" > emp_name = #{empName} </if > <if test ="age != null and age != ''" > and age = #{age} </if > <if test ="gender != null and gender != ''" > and gender = #{gender} </if > </where > </select >
9.3、trim标签
trim用于去掉或添加标签中的内容。
常用属性:
prefix:在trim标签中的内容前面添加指定内容。
prefixOverrides:在trim标签中的内容前面去掉指定内容
prefix:在trim标签中的内容后面添加指定内容。
prefixOverrides:在trim标签中的内容后面去掉指定内容
1 2 3 4 5 6 7 8 9 10 11 12 13 14 <select id ="getEmpByCondition" resultType ="Emp" > select * from emp <trim prefix ="where" prefixOverrides ="and" > <if test ="empName != null and empName != ''" > emp_name = #{empName} </if > <if test ="age != null and age != ''" > and age = #{age} </if > <if test ="gender != null and gender != ''" > and gender = #{gender} </if > </trim > </select >
9.4、choose、when、otherwise标签
这三个标签相当于if…else if…else或者是switch结构,只会执行其中的某一个分支,当某个分支第一次符合条件,剩下的条件无论符不符合,都不会被拼接到Sql中。 when标签至少有一个,otherwise标签可以没有,有也最多有一个。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 <select id ="getEmpByCondition" resultType ="Emp" > select * from emp <where > <choose > <when test ="empName != null and empName != ''" > emp_name = #{empName} </when > <when test ="age != null and age != ''" > age = #{age} </when > <when test ="gender != null and gender != ''" > gender = #{gender} </when > </choose > </where > </select >
1 2 3 4 5 6 7 8 9 10 11 12 13 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); List<Emp> empList = mapper.getEmpByCondition(new Emp (null , "张三" , 20 , "男" )); empList.forEach(System.out::println); System.out.println("----------------------------------------------------" ); List<Emp> empList2 = mapper.getEmpByCondition(new Emp (null , null , 22 , "男" )); empList2.forEach(System.out::println); System.out.println("----------------------------------------------------" ); List<Emp> empList3 = mapper.getEmpByCondition(new Emp ()); empList3.forEach(System.out::println); }
执行结果: DEBUG [main] ==> Preparing: select * from emp WHERE emp_name = ? DEBUG [main] ==> Parameters: 张三(String) DEBUG [main] <== Total: 1 Emp(empId=1, empName=张三, age=20, gender=男) -————————————————— DEBUG [main] ==> Preparing: select * from emp WHERE age = ? DEBUG [main] ==> Parameters: 22(Integer) DEBUG [main] <== Total: 2 Emp(empId=2, empName=李四, age=22, gender=男) Emp(empId=4, empName=赵六, age=22, gender=男) -————————————————— DEBUG [main] ==> Preparing: select * from emp DEBUG [main] ==> Parameters: DEBUG [main] <== Total: 4 Emp(empId=1, empName=张三, age=20, gender=男) Emp(empId=2, empName=李四, age=22, gender=男) Emp(empId=3, empName=王五, age=21, gender=男) Emp(empId=4, empName=赵六, age=22, gender=男)
9.5、foreach标签
该标签常用于批量操作。
EmpMapper.java:批量添加员工信息,根据id批量删除员工信息
1 2 3 4 int insertEmps (@Param("emps") List<Emp> emps) ;int deleteEmps (@Param("ids") List<Integer> ids) ;
对应的EmpMapper.xml
foreach标签属性:
collection:要循环的数组或者集合。
item:集合中的每个元素。
separator:设置每次循环之间的分隔符
open:在开始循环之前添加指定内容。
close:在循环结束之后添加指定内容。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 <insert id ="insertEmps" > insert into emp (emp_name, age, gender) values <foreach collection ="emps" item ="emp" separator ="," > (#{emp.empName}, #{emp.age}, #{emp.gender}) </foreach > </insert > <delete id ="deleteEmps" > delete from emp where emp_id in <foreach collection ="ids" item ="id" open ="(" separator ="," close =")" > #{id} </foreach > </delete >
测试类:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); System.out.println("------------批量添加---------------" ); ArrayList<Emp> emps = new ArrayList <>(); emps.add(new Emp (null , "姓名1" , 20 , "男" )); emps.add(new Emp (null , "姓名2" , 21 , "男" )); mapper.insertEmps(emps); System.out.println("-------------批量删除--------------" ); ArrayList<Integer> ids = new ArrayList <>(); ids.add(5 ); ids.add(6 ); mapper.deleteEmps(ids); }
输出结果: ————批量添加————— DEBUG [main] ==> Preparing: insert into emp (emp_name, age, gender) values (?,?,?) , (?,?,?) DEBUG [main] ==> Parameters: 姓名1(String), 20(Integer), 男(String), 姓名2(String), 21(Integer), 男(String) DEBUG [main] <== Updates: 2 ————-批量删除————– DEBUG [main] ==> Preparing: delete from emp where emp_id in ( ? , ? ) DEBUG [main] ==> Parameters: 5(Integer), 6(Integer) DEBUG [main] <== Updates: 2
9.6、sql、include标签
sql标签:用于指定一段SQL语句的片段,方便SQL语句的复用。 id属性:sql标签的唯一标识。
include标签:用于引用sql标签所定义的SQL片段,拼接到当前SQL语句中。 refid属性:声明所引用的sql标签的唯一标识。
1 2 3 4 5 6 7 8 9 <sql id ="empColumns" > emp_id,emp_name,age,gender,dept_id </sql > <select id ="getEmpByCondition" resultType ="Emp" > select <include refid ="empColumns" /> from emp </select >
9、Mybatis缓存 9.1、一级缓存 一级缓存是SqlSession级别的 ,通过同一个SqlSession查询的数据会被缓存,下次查询相同的数据,就会从缓存中直接获取,不会从数据库重新访问。一级缓存是默认开启的 。
例如:查询两次相同数据,只执行了一次Sql语句,第二次直接是从缓存里面取的
1 2 3 4 5 6 7 8 9 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); Emp emp1 = mapper.getEmpById(1 ); System.out.println(emp1); Emp emp2 = mapper.getEmpById(1 ); System.out.println(emp2); }
输出: DEBUG [main] ==> Preparing: select * from emp where emp_id = ? DEBUG [main] ==> Parameters: 1(Integer) DEBUG [main] <== Total: 1 Emp(empId=1, empName=张三, age=20, gender=男) Emp(empId=1, empName=张三, age=20, gender=男)
一级缓存失效的四种情况:
不同的SqlSession对应不同的一级缓存
同一个SqlSession但是查询条件不同
同一个SqlSession两次查询期间执行了任何一次增删改操作
同一个SqlSession两次查询期间手动清空了缓存,即调用sqlSession.clearCache();
9.2、二级缓存 二级缓存是SqlSessionFactory级别 ,通过同一个SqlSessionFactory创建的SqlSession查询的结果会被缓存;此后若再次执行相同的查询语句,结果就会从缓存中获取。二级缓存默认关闭 ,需要手动开启。
二级缓存开启的条件:
在核心配置文件中,设置全局配置属性cacheEnabled=”true”(默认为true,可以省略)
在映射文件中设置cache标签:<cache/>
二级缓存必须在SqlSession关闭或提交之后有效
查询的数据所转换的实体类类型必须实现序列化的接口(Serializable接口)
二级缓存失效的情况:
两次查询之间执行了任意的增删改,会使一级和二级缓存同时失效。
相关配置 在mapper配置文件中 添加的cache标签 可以设置一些属性 :
type属性 :设置二级缓存类型,用于整合第三方缓存。
eviction属性 :缓存回收策略,默认的是 LRU。
LRU(Least Recently Used): 最近最少使用的(移除最长时间不被使用的对象)。
FIFO(First in First out):先进先出(按对象进入缓存的顺序来移除它们)。
SOFT:软引用(移除基于垃圾回收器状态和软引用规则的对象)。
WEAK:弱引用(更积极地移除基于垃圾收集器状态和弱引用规则的对象)。
flushInterval属性 :刷新间隔,单位毫秒。默认情况是不设置,也就是没有刷新间隔,缓存仅仅调用语句时刷新
size属性 :引用数目,正整数。代表缓存最多可以存储多少个对象,太大容易导致内存溢出
readOnly属性 :只读,默认false
true:只读缓存;会给所有调用者返回缓存对象的相同实例。因此这些对象不能被修改。这提供了很重要的性能优势。
false:读写缓存;会返回缓存对象的拷贝(通过序列化)。这会慢一些,但是安全,因此默认是false。
缓存查询顺序
先查询二级缓存,因为二级缓存中可能会有其他程序已经查出来的数据,可以拿来直接使用。
如果二级缓存没有命中,再查询一级缓存。
如果一级缓存也没有命中,则查询数据库。
SqlSession关闭之后,一级缓存中的数据会写入二级缓存。
10、逆向工程
正向工程:先创建Java实体类,由框架负责根据实体类生成数据库表。 Hibernate是支持正向工程的。
逆向工程:先创建数据库表,由框架负责根据数据库表,反向生成如下资源:
Java实体类
Mapper接口
Mapper映射文件
首先引入pom.xml依赖文件:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 <dependencies > <dependency > <groupId > org.mybatis</groupId > <artifactId > mybatis</artifactId > <version > 3.5.10</version > </dependency > <dependency > <groupId > junit</groupId > <artifactId > junit</artifactId > <version > 4.13.2</version > <scope > test</scope > </dependency > <dependency > <groupId > mysql</groupId > <artifactId > mysql-connector-java</artifactId > <version > 8.0.29</version > </dependency > <dependency > <groupId > org.projectlombok</groupId > <artifactId > lombok</artifactId > <version > 1.18.24</version > <scope > provided</scope > </dependency > <dependency > <groupId > log4j</groupId > <artifactId > log4j</artifactId > <version > 1.2.17</version > </dependency > </dependencies > <build > <plugins > <plugin > <groupId > org.mybatis.generator</groupId > <artifactId > mybatis-generator-maven-plugin</artifactId > <version > 1.4.1</version > <dependencies > <dependency > <groupId > org.mybatis.generator</groupId > <artifactId > mybatis-generator-core</artifactId > <version > 1.4.1</version > </dependency > <dependency > <groupId > mysql</groupId > <artifactId > mysql-connector-java</artifactId > <version > 8.0.29</version > </dependency > </dependencies > </plugin > </plugins > </build >
再在resources目录下创建generatorConfig.xml(必须这个名字)逆向工程配置文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd" > <generatorConfiguration > <context id ="DB2Tables" targetRuntime ="MyBatis3" > <jdbcConnection driverClass ="com.mysql.cj.jdbc.Driver" connectionURL ="jdbc:mysql://localhost:3306/ssm?serverTimezone=UTC" userId ="root" password ="123456" > </jdbcConnection > <javaModelGenerator targetPackage ="com.dyz.entity" targetProject =".\src\main\java" > <property name ="enableSubPackages" value ="true" /> <property name ="trimStrings" value ="true" /> </javaModelGenerator > <sqlMapGenerator targetPackage ="com.dyz.mapper" targetProject =".\src\main\resources" > <property name ="enableSubPackages" value ="true" /> </sqlMapGenerator > <javaClientGenerator type ="XMLMAPPER" targetPackage ="com.dyz.mapper" targetProject =".\src\main\java" > <property name ="enableSubPackages" value ="true" /> </javaClientGenerator > <table tableName ="emp" domainObjectName ="Emp" /> <table tableName ="dept" domainObjectName ="Dept" /> </context > </generatorConfiguration >
最后双击该命令即可自动生成逆向工程代码文件。
测试多条件查询:
1 2 3 4 5 6 7 8 9 10 11 12 13 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); EmpExample empExample = new EmpExample (); empExample.createCriteria() .andEmpNameEqualTo("张三" ) .andAgeBetween(20 ,25 ); empExample.or().andEmpNameEqualTo("李四" ) .andGenderEqualTo("男" ); List<Emp> emps = mapper.selectByExample(empExample); emps.forEach(System.out::println); }
DEBUG [main] ==> Preparing: select emp_id, emp_name, age, gender, dept_id from emp WHERE ( emp_name = ? and age between ? and ? ) or( emp_name = ? and gender = ? ) DEBUG [main] ==> Parameters: 张三(String), 20(Integer), 25(Integer), 李四(String), 男(String) DEBUG [main] <== Total: 2 Emp(empId=1, empName=张三, age=20, gender=男, deptId=1) Emp(empId=2, empName=李四, age=22, gender=男, deptId=2)
11、分页插件 ①:引入依赖
1 2 3 4 5 6 <dependency > <groupId > com.github.pagehelper</groupId > <artifactId > pagehelper</artifactId > <version > 5.3.0</version > </dependency >
②:在mybatis配置文件中配置
1 2 3 4 <plugins > <plugin interceptor ="com.github.pagehelper.PageInterceptor" /> </plugins >
③:最基本的使用
只需要在查询语句之前添加PageHelper.startPage(int pageNum, int pageSize);
就可以开启分页,分页插件会自动在要执行的查询语句后面加上limit。
pageNum:当前页页码 pageSize:每页显示的条数
1 2 3 4 5 6 7 8 9 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); PageHelper.startPage(1 ,2 ); List<Emp> emps = mapper.selectAll(); emps.forEach(System.out::println); }
输出: DEBUG [main] ==> Preparing: SELECT count(0) FROM emp DEBUG [main] ==> Parameters: DEBUG [main] <== Total: 1 DEBUG [main] ==> Preparing: select emp_id, emp_name, age, gender, dept_id from emp LIMIT ? DEBUG [main] ==> Parameters: 2(Integer) DEBUG [main] <== Total: 2 Emp(empId=1, empName=张三, age=20, gender=男, deptId=1) Emp(empId=2, empName=李四, age=22, gender=男, deptId=2)
在查询获取list集合之后,使用PageInfo<T> pageInfo = new PageInfo<>(List<T> list, int navigatePages)
获取分页相关的分页数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 @Test public void testMybatis () { SqlSession sqlSession = SqlSessionUtil.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); PageHelper.startPage(1 , 2 ); List<Emp> emps = mapper.selectAll(); emps.forEach(System.out::println); PageInfo<Emp> empPageInfo = new PageInfo <>(emps,3 ); System.out.println("总条数:" + empPageInfo.getTotal()); System.out.println("总页数:" + empPageInfo.getPages()); System.out.println("分页页码:" + Arrays.toString(empPageInfo.getNavigatepageNums())); }
DEBUG [main] ==> Preparing: SELECT count(0) FROM emp DEBUG [main] ==> Parameters: DEBUG [main] <== Total: 1 DEBUG [main] ==> Preparing: select emp_id, emp_name, age, gender, dept_id from emp LIMIT ? DEBUG [main] ==> Parameters: 2(Integer) DEBUG [main] <== Total: 2 Emp(empId=1, empName=张三, age=20, gender=男, deptId=1) Emp(empId=2, empName=李四, age=22, gender=男, deptId=2) 总条数:6 总页数:3 分页页码:[1, 2, 3]
PageInfo包含的分页常用信息:
pageNum:当前页的页码
pageSize:每页显示的条数
size:当前页显示的真实条数
total:总记录数
pages:总页数
prePage:上一页的页码
nextPage:下一页的页码
isFirstPage/isLastPage:是否为第一页/最后一页
hasPreviousPage/hasNextPage:是否存在上一页/下一页
navigatePages:导航分页的页码数
navigatepageNums:导航分页的页码,[1,2,3,4,5]