📖本篇内容：Mybatis入门到入坟一站式基础——囊括面试点与初学基础、框架分析
📆 最近更新：2022年1月11日 MySQL的最全常用SQL语句 —— 一文可以快速熟悉、回忆sql语句
🙊个人简介：一只二本院校在读的大三程序猿，本着注重基础，打卡算法，分享技术作为个人的经验总结性的博文博主，虽然可能有时会犯懒，但是还是会坚持下去的，如果你很喜欢博文的话，建议看下面一行~（疯狂暗示QwQ）
🌇 点赞 👍 收藏 ⭐留言 📝 一键三连 关爱程序猿，从你我做起
哇咔咔，小付又来了哦~今天给大家整理的是关于SSM中Mybatis框架的入门到入坟的基础夯实学习资料， 这次是小付二刷的经历过往，耗时也不算太多，更加坚固了基础知识的掌握的同时，也深刻理解了，Mybatis部分底层源码实现的工作流程，感谢各位支持，但我希望这篇文章能带给你们更多的是知识的查缺补漏，以及对框架的熟练掌握，加油xdm，冲冲冲~！
Mybatis介绍
Mybatis是一款优秀的持久化框架：
定制化 SQL、存储过程以及高级映射。避免了几乎所有的JDBC 代码和手动设置参数以及获取结果集。简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Ordinary Java Object,普通的 Java对象)映射成数据库中的记录。orm是什么？
ORM（Object Relational Mapping），对象关系映射，是一种为了解决关系型数据库数据与简单Java对象（POJO）的映射关系的技术。简单的说，ORM是通过使用描述对象和数据库之间映射的元数据，将程序中的对象自动持久化到关系型数据库中。
优点：
基于SQL语句进行编程，较为灵活，可以定制化SQL语句。xml文件当中，解除了数据持久化与代码程序的耦合性，便于管理不用手动关闭连接，从而浪费资源ORM字段关系映射；提供对象关系映射标签，支持对象关系组件维护缺点：
编写工作量较大，尤其当字段多、关联表多时，对开发人员编写SQL语句的功底有一定要求，你会体验到写高中作文的感受。依赖于数据库，导致数据库移植性差，不能随意更换数据库。共同点：
都是持久化层开发的框架，都用于DAO层的开发。不同点：
步骤1：搭建Mybatis的环境
添加驱动包（mybatis.jar与mysql.jar）
<dependencies><dependency><groupId&batis</groupId><artifactId>mybatis</artifactId><version>3.5.7</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.26</version></dependency></dependencies>
步骤2：快速创建一个数据库并且包含一张表作为测试
CREATE DATABASE `mybatis`;USE `mybatis`;CREATE TABLE `user`(`id` INT(64) NOT NULL PRIMARY KEY,`username` VARCHAR(30) DEFAULT NULL,`password` VARCHAR(30) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO user(`id`,`username`,`password`) VALUES(101,'Alascanfu','123456'),
(102,'root','root'),
(103,'admin','admin');步骤3：创建配置文件 l进行配置
配置文件需要配置：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis//DTD Config 3.0//EN"".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}"/><property name="password" value="${password}"/></dataSource></environment><environment id="otherDevelopment"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="${driver}"/><property name="url" value="${url}"/><property name="username" value="${username}"/><property name="password" value="${password}"/></dataSource></environment></environments>
<!-- 指定mapper文件的路径--><mappers><mapper resource="com.alascanfu.pojo.l"/></mappers></configuration>
步骤4：创建一个MybatisUtils工具类 获取SqlSession
/*** 功能描述* sqlSessionFactory ----> sqlSession* Mybatis的工具类 用来获取sqlSession* @author Alascanfu* @date 2022/1/9*/
public class MybatisUtils {private static InputStream inputStream;private static SqlSessionFactory sqlSessionFactory;//利用静态代码块直接在初始化时加载static {try {String resource = &#l";inputStream = ResourceAsStream(resource);sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);} catch (IOException e) {e.printStackTrace();}}/*** 功能描述* 获取sqlSession* @date 2022/1/9* @author Alascanfu*/public static SqlSession getSqlSession(){return sqlSessionFactory.openSession();}/*** 功能描述* 关闭流与断开SqlSession连接* @date 2022/1/9* @author Alascanfu*/public static void closeSqlSession(){try {inputStream.close();getSqlSession().close();} catch (IOException e) {e.printStackTrace();}}
}步骤5：创建实体类与接口类
User.java
public class User {private int id ;private String username;private String password;public User() {}public User(int id, String username, String password) {this.id = id;this.username = username;this.password = password;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + ''' +", password='" + password + ''' +'}';}
}
接口UserMapper.java
public interface UserMapper {List<User> getUserList();
}编写一个Mapper配置文件，来代替了之前JDBC中的UserDaoImpl的接口实现类
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis//DTD Mapper 3.0//EN"".dtd">
<!--namespace 绑定一个对应的Mapper接口-->
<mapper namespace="com.alascanfu.pojo.mapper.UserMapper"><select id="getUserList" resultType="com.alascanfu.pojo.User">select * from user</select>
</mapper>
步骤6：测试
UserMapperTest.java
public class UserMapperTest {@Testpublic void test(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);List<User> userList &#UserList();for (User user : userList) {System.out.println(user);}sqlSession.close();}
}
运行结果：
User{id=101, username='Alascanfu', password='123456'}
User{id=102, username='root', password='root'}
User{id=103, username='admin', password='admin'}Process finished with exit code 0
如果测试类进行测试的时候可能会报错
要先进行配置好maven的过滤环境
否则在测试的时候就会找不到咱们设置好的xml文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns=".0.0"xmlns:xsi=""xsi:schemaLocation=".0.0 .0.0.xsd"><parent><artifactId>mybatis-01-test</artifactId><groupId>com.alascanfu</groupId><version>1.0-SNAPSHOT</version></parent><modelVersion>4.0.0</modelVersion><artifactId>spring-02-HelloMybatis</artifactId><dependencies><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency></dependencies><!-- 配置好过滤Maven配置文件--><build><resources><resource><directory>src/main/resources</directory><includes><include>**/*.properties</include><include>**/*.xml</include></includes><filtering>true</filtering></resource><resource><directory>src/main/java</directory><includes><include>**/*.properties</include><include>**/*.xml</include></includes><filtering>true</filtering></resource></resources></build></project>
步骤1：需要合理设计好对应了映射类，属性命名最好=列名
步骤2：在Mapper接口中，定义对应的方法，增删改的返回值为int（代表的是操作修改的行），而查询则对应返回类型即可。
public interface UserMapper {//获取表中的所有UserList<User> getUserList();//插入一个用户int insertUser(User user);//根据id来删除一个用户int deleteUserById(int id);//根据id来修改用户int updateUserById(User user);//查询对应id的用户信息User queryUserById(int id);
}
步骤3：当在Mapper接口中，创建好方法后，就要去Mapper的配置文件当中设置对应方法的SQL语句了。
其中mapper中对应的标签名称 也就是翻译过来的 SQL映射语句
insert 插入
delete 删除
update 更新
select 查询
标签内的属性：
- parameterType：参数的类型
- resultType：返回值类型
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis//DTD Mapper 3.0//EN"".dtd">
<mapper namespace="com.alascanfu.pojo.mapper.UserMapper"><!-- 增加一个用户 这里的参数类型一定要写对应位置的返回类型 以免出错--><insert id="insertUser" parameterType="com.alascanfu.pojo.User">insert into user (id,username,password) values(#{id},#{username},#{password})</insert>
<!-- 删除一个用户 --><delete id="deleteUserById" parameterType="int">delete from USER where id = #{id}</delete><!-- 根据id更新用户信息 --> <update id="updateUserById" parameterType="com.alascanfu.pojo.User">update user set username = #{username},password=#{password} where id = #{id}</update><!-- 查询所有用户信息 --><select id="getUserList" resultType="com.alascanfu.pojo.User">SELECT * FROM USER</select>
<!-- 根据用户id查询用户信息 --> <select id="queryUserById" resultType="com.alascanfu.pojo.User">select * from user where id = #{id}</select></mapper>
注意内容：
#{属性值（参数名称）}
步骤4：连接数据库进行测试
public class Test {//用于测试查询所有的用户信息@org.junit.Testpublic void test(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);List<User> userList &#UserList();for (User user : userList) {System.out.println(user);}}//用于测试插入一个用户@org.junit.Testpublic void testInsert(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);mapper.insertUser(new User(104,"DBA","DBA"));List<User> userList = UserList();for (User user : userList) {System.out.println(user);}}//用于测试删除一个用户@org.junit.Testpublic void testDeleteById(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);mapper.deleteUserById(102);List<User> userList = UserList();for (User user : userList) {System.out.println(user);}}//用于测试更新用户信息@org.junit.Testpublic void testUpdateById(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);int row = mapper.updateUserById(new User(103, "root", "123456"));List<User> userList = UserList();for (User user : userList) {System.out.println(user);}}//用于测试根据id查询用户信息@org.junit.Testpublic void testQueryById(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);User user = mapper.queryUserById(101);System.out.println(user);}
}增删改的时候需要提交事务
sessionmit(); llback();
查询的时候要添加resultType属性
将多个参数封装到map集合当中，再将map集合传递给mapper文件。
获取map中对应的值
#{map的key值}
在Mapper接口中定义方法：
当需要传入多个参数的时候，使用map集合来进行传参
int insertUsers(Map map);
Mapper配置文件中设置参数为map类型
<insert id="insertUsers" parameterType="map">insert into user (id,username,password) values (#{uId},#{uUsername},#{uPassword});</insert>
Test.java
@org.junit.Testpublic void testInsertUser(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);Map map = new HashMap();//重点在下面这三行map.put("uId",105);map.put("uUsername","DBA");map.put("uPassword","123456");mapper.insertUsers(map);User user = mapper.queryUserById(105);System.out.println(user);}
ThreadLocal是什么？
我认为的ThreadLocal自如其名，就是代表的线程的局部变量，就好比一个线程正在运行当中，ThreadLocal的变量只可以被其自身线程调用、访问，别的线程均无法访问，是避免线程竞争的好东西，他不是解决冲突，而是从根本上避免了冲突的发生。使得线程之间独立运行，并发下安全问题的好帮手。
ThreadLocal的功能
为每一个使用该变量的线程都提供一个变量值的副本，是Java中一种较为特殊的线程绑定机制，是每一个线程都可以独立地改变自己的副本，而不会和其它线程的副本冲突。
代码测试理解
public class ThreadLocalTest {public static void main(String[] args) {final ThreadLocal<String> threadLocal = new ThreadLocal<String>();final List<String> list = new ArrayList<String>();new Thread(new Runnable() {public void run() {System.out.println("A线程开始存入值");threadLocal.set("A线程存入的threadLocal变量内容");list.add("A线程存入的list内容");System.out.println("A====>获取线程的局部变量内容"+ ());System.out.println("A====>获取线程的list变量内容"+ (0));}}).start();new Thread(new Runnable() {public void run() {try {TimeUnit.SECONDS.sleep(2);} catch (InterruptedException e) {e.printStackTrace();}System.out.println("B线程开始取出A线程存入的值");System.out.println("B====>获取线程A的局部变量内容"+ ());System.out.println("B====>获取线程A存入的list变量内容"+ (0));}}).start();}
}
执行结果：
A线程开始存入值
A====>获取线程的局部变量内容A线程存入的threadLocal变量内容
A====>获取线程的list变量内容A线程存入的list内容
B线程开始取出A线程存入的值
B====>获取线程A的局部变量内容null
B====>获取线程A存入的list变量内容A线程存入的list内容Process finished with exit code 0
你会发现B线程无法获取A线程设置的局部变量，而可以获得ArrayList中的变量，从侧面也就说明了ArrayList的线程不安全问题。
使用ThreadLocal优化SqlSession
**原理：**在之前已经提到过了SqlSession会在每次用户请求信息时，都会生成一个SqlSession对象实例，在处理数据的过程中也可能会出现多线程的问题。
ThreadLocal的set方法会使用一个map，将当前线程信息作为key，要set的值作为value存储
set()方法的源码：
public void set(T value) {Thread t = Thread.currentThread();ThreadLocalMap map = getMap(t);if (map != null)map.set(this, value);elsecreateMap(t, value);}
SqlSessionUtils工具类优化
/*** 功能描述* 通过ThreadLocal来优化的获取SqlSession的工具类* @author Alascanfu* @date 2022/1/10*/public class SqlSessionUtils {private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();private static SqlSessionFactory sqlSessionFactory;static {try {InputStream inputStream = ResourceAsStream(&#l");sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);} catch (IOException e) {e.printStackTrace();}}/***功能描述* 单例模式下安全的获得SqlSession* @date 2022/1/10* @author Alascanfu*/public static SqlSession getSqlSession(){SqlSession sqlSession = ();if (sqlSession == null){sqlSession = sqlSessionFactory.openSession();threadLocal.set(sqlSession);}return sqlSession;}/*** 功能描述* 关闭连接的工具类* @date 2022/1/10* @author Alascanfu*/public static void closeSqlSession(){SqlSession sqlSession = ();if (sqlSession != null){sqlSession.close();ve();}}
}
用于设置了自增主键的属性上
方便设置
<insert id="insertUserByIncrease" useGeneratedKeys="true" keyProperty="id"></insert>
测试用例
<insert id="insertUserByIncrease" parameterType="com.alascanfu.pojo.User" useGeneratedKeys="true" keyProperty="id">insert into user(username,password) values (#{username},#{password});</insert>
Test.java
@org.junit.Testpublic void testInsertUserByIncrese(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);User user1 = new User();user1.setUsername("HHXF");user1.setPassword("123");int i = mapper.insertUserByIncrease(user1);System.out.println(mapper.queryUserById(104));}
首先先导入log4j的jar包
<dependency><groupId>org.slf4j</groupId><artifactId>slf4j-api</artifactId><version>1.7.5</version></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-log4j12</artifactId><version>1.7.12</version></dependency><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency>
然后resources文件夹下创建log4j.properties进行配置数据库的日志
Logger=DEBUG, Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%nlog4j.logger.java.sql.ResultSet=INFO
log4j.logger.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
最后去测试 测试结果
2022-01-10 20:30:42,991 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.insertUsers] - ==> Preparing: insert into user (id,username,password) values (?,?,?);
2022-01-10 20:30:43,017 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.insertUsers] - ==> Parameters: 105(Integer), DBA(String), 123456(String)
2022-01-10 20:30:43,019 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.insertUsers] - <== Updates: 1
2022-01-10 20:30:43,020 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.queryUserById] - ==> Preparing: select * from user where id = ?
2022-01-10 20:30:43,020 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.queryUserById] - ==> Parameters: 105(Integer)
2022-01-10 20:30:43,043 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.queryUserById] - <== Total: 1
User{id=105, username='DBA', password='123456'}
Mybatis中 如何使用in查询
这里先用MySQL中来演示在SQL服务器中是如何使用in来进行查询的
in 一般用于 where 的表达式当中，其作用是用来查询某个范围之内的数据
示例：
mysql> show tables;
+-------------------+
| Tables_in_mybatis |
+-------------------+
| department |
| employee |
| employeeec |
| employeeremove |
| employeetrain |
| empsalary |
| hr |
| hr_role |
| mail_send_log |
| user |
+-------------------+
10 rows in set (0.01 sec)mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| username | varchar(30) | YES | | NULL | |
| password | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)mysql> select * from user;
+-----+-----------+----------+
| id | username | password |
+-----+-----------+----------+
| 101 | Alascanfu | 123456 |
| 102 | root | root |
| 103 | admin | admin |
| 108 | HHXF | 123 |
+-----+-----------+----------+
4 rows in set (0.04 sec)mysql> select * from user where id in (101,108);
+-----+-----------+----------+
| id | username | password |
+-----+-----------+----------+
| 101 | Alascanfu | 123456 |
| 108 | HHXF | 123 |
+-----+-----------+----------+
2 rows in set (0.00 sec)not in 与之相反
需要注意的是：在in之后依然可以跟函数哦~
咱们言归正传——来说说如何在Java中的配置文件中使用呢？
in 后面跟着的是 foreach 这个标签foreach标签中的属性说明： item 表示迭代过程中的每个元素的别名index 指定的是一个名字，用于表示在迭代过程中，迭代的位置open表示该语句 以什么开始separator 表示在每次进行迭代之间以什么符号作为分隔符close 表示以什么符号结束步骤1：在Mapper接口中创建对应的查询方法
public interface UserMapper {List<User> findInList(List list);List<User> findInArray(int[] arr);List<User> findInMap(Map map);
}步骤2：在Mapper的配置文件中书写复杂查询的SQL语句
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis//DTD Mapper 3.0//EN"".dtd">
<mapper namespace="com.alascanfu.pojo.mapper.UserMapper"><select id="findInList" resultType="com.alascanfu.pojo.User">select * from user where id in<foreach collection="list" item="uid" open="(" close=")" separator=",">#{uid}</foreach></select><select id="findInArray" resultType="com.alascanfu.pojo.User">select * from user where id in<foreach collection="array" open="(" close=")" separator="," item="uid">#{uid}</foreach></select><select id="findInMap" resultType="com.alascanfu.pojo.User">select * from user where id in<foreach collection="keyVal" open="(" close=")" separator="," item="uid">#{uid}</foreach></select></mapper>
步骤3：单元测试 是否满足条件
@org.junit.Testpublic void testInList(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);ArrayList<Integer> list = new ArrayList<Integer>();list.add(101);list.add(108);for (Integer i : list) {System.out.println(i);}List<User> userList = mapper.findInList(list);for (User user : userList) {System.out.println(user);}}@org.junit.Testpublic void testInArr(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);int [] arr = new int[2];arr[0] = 101;arr[1] = 108;List<User> userList = mapper.findInArray(arr);for (User user : userList) {System.out.println(user);}}@org.junit.Testpublic void testInMap(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);Map map = new HashMap();List<Integer> list = new ArrayList<Integer>();list.add(101);list.add(108);map.put("keyVal",list);List<User> userList = mapper.findInMap(map);for (User user : userList) {System.out.println(user);}}
执行结果：
101
108
2022-01-11 17:38:23,010 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.findInList] - ==> Preparing: select * from user where id in ( ? , ? )
2022-01-11 17:38:23,079 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.findInList] - ==> Parameters: 101(Integer), 108(Integer)
2022-01-11 17:38:23,112 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.findInList] - <== Total: 2
User{id=101, username='Alascanfu', password='123456'}
User{id=108, username='HHXF', password='123'}Process finished with exit code 0
和预期结果一致QwQ
但是需要注意的点在于你传入Map 作为参数的时候 collection 传入的是key值 也就是说明你需要先创建一个集合添加到map当中然后并给这个map赋予key，然后通过collection调用的时候就只能用key来获取。
使用模糊查询 首先要防止SQL注入问题咱们就需要用到动态的SQL语句
<if test="username!=null and username!=''">and username like "%"#{username}"%"
</if>
测试用例：
步骤1：我们需要现在Mapper接口中定义相关的模糊查询方法
public interface UserMapper {List<User> findLike(Map map);
}
步骤2：然后我们需要在对应的Mapper的配置文件进行书写SQL语句
<select id="findLike" resultType="com.alascanfu.pojo.User">select * from USER where 1=1<if test="username!=null and username!=''">and username like "%"#{username}"%"</if></select>
<if test="username!=null and username!=''">
这一段代码是为了防止SQL注入导致的恶意查询
步骤3：进行测试
public void testLike(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);Map map = new HashMap();map.put("username","a");List<User> userList = mapper.findLike(map);for (User user : userList) {System.out.println(user);}}
测试结果：
2022-01-11 18:28:44,935 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.findLike] - ==> Preparing: select * from USER where 1=1 and username like "%"?"%"
2022-01-11 18:28:44,974 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.findLike] - ==> Parameters: a(String)
2022-01-11 18:28:45,005 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.findLike] - <== Total: 2
User{id=101, username='Alascanfu', password='123456'}
User{id=103, username='admin', password='admin'}Process finished with exit code 0
答案如预期的一致~
注意点：
传递的参数如果是map类型的话，则test属性中写的就是key。
同时
#{} 相当于占位符
${} 表示拼接 可能会引起SQL注入问题
test属性中读取属性值时直接写属性名
模糊查询读取属性时使el 表达式， 标签调用的时候
${属性名}
除以上位置外，都使用#{属性名}
步骤1：在Mapper接口中书写对应的接口方法
List<User> findArea(Map map);
步骤2： 在Mapper配置文件中书写SQL语句
<select id="findArea" resultType="com.alascanfu.pojo.User">select * from user where 1=1 and id<if test="endId!=null and endId!=''">between #{startId} and #{endId}</if></select>
步骤3：测试
@org.junit.Testpublic void testBetween(){SqlSession sqlSession = SqlSession();UserMapper mapper = Mapper(UserMapper.class);Map map = new HashMap();map.put("startId",102);map.put("endId",105);List<User> userList = mapper.findArea(map);for (User user : userList) {System.out.println(user);}}
执行结果
2022-01-11 18:46:53,056 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.findArea] - ==> Preparing: select * from user where 1=1 and id between ? and ?
2022-01-11 18:46:53,102 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.findArea] - ==> Parameters: 102(Integer), 105(Integer)
2022-01-11 18:46:53,130 [main] DEBUG [com.alascanfu.pojo.mapper.UserMapper.findArea] - <== Total: 2
User{id=102, username='root', password='root'}
User{id=103, username='admin', password='admin'}
可以通过过resultMap 处理表的关系
为数据库表中的列起别名 = 属性名 同样可以映射数据
步骤1：在Mapper的配置文件中创建一个resultMap的映射关系
<resultMap id="userResultMap" type="com.alascanfu.pojo.User"><id property="id" column="id" jdbcType="INTEGER" javaType="int"/><id property="username" column="username" jdbcType="VARCHAR"/><id property="password" column="password" jdbcType="VARCHAR"/></resultMap>
注意：这里的properties对应的数据是你type中的属性值
而column对应的是数据库中的列头属性
步骤2：在对应的方法中添加返回resultMap类型
<select id="findArea" resultMap="userResultMap">select * from user where 1=1 and id<if test="endId!=null and endId!=''">between #{startId} and #{endId}</if></select>
两表联合查询：一对一和多对一
注意事项：
单表查询 selet中使用resultType 设置返回的类型即可。但是如果在多表查询，返回的就需要使用自定义的resultMap来对查询结果进行映射。
一表对多表查询
步骤1：初始化数据库表
create table `role`(`id` int not null primary key ,`name` varchar(32) default null,`nameZh` varchar(256) default null
)engine = innodb default charset = utf8;create table `user`(`id` int not null primary key ,`username` varchar(32) default null
)engine = innodb default charset = utf8;create table `user_role`(`id` int not null primary key ,`uid` int default null ,`rid` int default null
)engine = innodb default charset = utf8;
步骤2：向表中插入数据
mysql> select * from user;
+----+-----------+
| id | username |
+----+-----------+
| 1 | Alascanfu |
| 2 | HHXF |
+----+-----------+
2 rows in set (0.00 sec)mysql> select * from role;
+----+-------+--------------------+
| id | name | nameZh |
+----+-------+--------------------+
| 1 | admin | 系统管理员 |
| 2 | DBA | 数据库管理员 |
| 3 | user | 普通用户 |
+----+-------+--------------------+
3 rows in set (0.00 sec)mysql> select * from user_role;
+----+-----+-----+
| id | uid | rid |
+----+-----+-----+
| 1 | 1 | 3 |
| 2 | 1 | 1 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
+----+-----+-----+
4 rows in set (0.00 sec)初始化类与Mapper接口以及Mapper的配置文件 进行一对多连表查询
Role.java
public class Role {private int id ;private String name;private String nameZh;public Role() {}public Role(int id, String name, String nameZh) {this.id = id;this.name = name;this.nameZh = nameZh;}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 getNameZh() {return nameZh;}public void setNameZh(String nameZh) {this.nameZh = nameZh;}@Overridepublic String toString() {return "Role{" +"id=" + id +", name='" + name + ''' +", nameZh='" + nameZh + ''' +'}';}
}User.java
public class User1 {private int id ;private String username;private List<Role> roleList;public User1() {}public User1(int id, String username, List<Role> roleList) {this.id = id;this.username = leList = roleList;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public List<Role> getRoleList() {return roleList;}public void setRoleList(List<Role> roleList) {leList = roleList;}@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + ''' +", roleList=" + roleList +'}';}
}UserMapper.java
public interface UserMapper1 {List<User1> getUserById(int id);
}
