MyBatis动态SQL与连接池详解

一、MyBatis连接池1.1 连接池概述连接池是存储数据库连接的容器。如果没有连接池每次执行SQL语句都会创建Connection连接浪费时间影响程序性能。1.2 连接池分类MyBatis内置了连接池技术dataSource 标签的 type 属性有3个取值取值说明POOLED使用连接池推荐UNPOOLED不使用连接池JNDI使用JNDI实现连接池dataSource typePOOLED !-- 连接配置 -- /dataSource二、动态SQL之if标签根据条件动态拼接SQL语句// UserMapper接口 public ListUser findByWhere(User user); select idfindByWhere parameterTypeUser resultTypeUser select * from user where 1 1 if testusername ! null and username ! and username like #{username} /if if testsex ! null and sex ! and sex #{sex} /if /select三、动态SQL之where标签where 标签可以自动处理 where 11 的拼接问题select idfindByWhere parameterTypeUser resultTypeUser select * from user where if testusername ! null and username ! and username like #{username} /if if testsex ! null and sex ! and sex #{sex} /if /where /select四、动态SQL之foreach标签4.1 需求一id 1 or id 2 or id 3在User类中添加List集合属性private ListInteger ids; select idfindByIds parameterTypeUser resultTypeUser select * from user where foreach collectionids openid separatoror id itemi #{i} /foreach /where /select4.2 需求二id in (1,2,3)select idfindByIds parameterTypeUser resultTypeUser select * from user where foreach collectionids openid in ( separator, close) itemi #{i} /foreach /where /selectforeach标签属性说明collection要遍历的集合属性名open遍历开始拼接的字符串separator元素之间的分隔符close遍历结束拼接的字符串item每次遍历的元素别名五、提取公共SQL片段使用 sql 标签提取公共SQL用 include 引用!-- 提取公共SQL -- sql idfindAllSql select * from user /sql !-- 引用公共SQL -- select idfindAll resultTypeUser include refidfindAllSql/ /select select idfindByWhere parameterTypeUser resultTypeUser include refidfindAllSql/ where if testusername ! null and username ! and username like #{username} /if /where /select