互联网持久框架——MyBatis
提纲
一、认识Mybatis的核心组件
-
主要功能:
- 不屏蔽SQL
- 提供动态SQL
- 提供了Mapper的接口编程,简化工作
-
构建SqlSessionFactory:
-
使用XML文件来构建:
//实体类 public class Role { } //RoleMapper.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="role"> </mapper> //jdbc.properties配置文件 jdbc.url=jdbc:mysql://localhost:3306/worktest?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8 jdbc.driverClass=com.mysql.cj.jdbc.Driver jdbc.username=root jdbc.password=root //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> <!-- mybatis配置文件标签匹配顺序: 元素类型为 "configuration" 的内容必须匹配 "(properties?,settings?, typeAliases?,typeHandlers?,objectFactory?, objectWrapperFactory?,reflectorFactory?, plugins?,environments?,databaseIdProvider?,mappers?) --> <!-- 加载配置文件 --> <properties resource="jdbc.properties"/> <typeAliases> <!-- 单个别名定义 --> <typeAlias alias="role" type="com.zking.pojo.Role"/> <!-- 批量别名定义,扫描整个包下的类,别名为类名(首字母大小写都可以) --> <package name="com.zking.pojo"/> <!-- <package name="其它包"/> --> </typeAliases> <!-- 描述数据库 --> <environments default="development"> <environment id="development"> <!-- 配置事务管理器,这里是JDBC管理器方式 --> <transactionManager type="JDBC"/> <!-- 配置事务管理器,这里是MANAGED管理器方式,自动提交和回滚 --> <!-- <transactionManager type="MANAGED"> <!-- 连接会默认关闭,所以这里需要设置不关闭连接 --> <property name="closeConnection" value="false" /> </transactionManager> --> <!-- POOLED表示采用Mybatis内部提供的连接池方式 --> <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> <mappers> <mapper resource="com/zking/mappers/RoleMapper.xml"/> <!-- <package name="com.zking.mappers"/> --> </mappers> </configuration> //测试类 public class Test { public static void main(String[] args) { SqlSessionFactory sqlSessionFactory = null; String resource = "/mybatis-config.xml"; try { InputStream inputStream = Test.class.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); System.out.println(sqlSessionFactory); } catch (Exception e) { e.printStackTrace(); } } }
-
使用代码创建SqlSessionFactory:
public static SqlSessionFactory getSqlSessionFactory(){ //数据库连接池信息 PooledDataSource dataSource = new PooledDataSource(); dataSource.setDriver("com.mysql.cj.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/worktest?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8"); dataSource.setUsername("root"); dataSource.setPassword("root"); dataSource.setDefaultAutoCommit(false); //采用Mybatis的JDBC事务方式 TransactionFactory transactionFactory = new JdbcTransactionFactory(); Environment environment = new Environment("development", transactionFactory, dataSource); //创建configuration对象 Configuration configuration = new Configuration(environment); //注册一个mybatis上下文别名 configuration.getTypeAliasRegistry().registerAlias(Role.class); //加入一个映射器 configuration.addMapper(RoleMapper.class); //使用SqlSessionFactoryBuilder创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration); return sqlSessionFactory; }
-
-
Mybatis映射器:
-
使用xml文件实现映射器:
//映射器文件RoleMapper.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.zking.pojo.RoleMapper"> <select id="getRole" parameterType="long" resultType="role"> select id,roleName,note from role where id=#{id} </select> </mapper> //映射器接口RoleMapper public interface RoleMapper { public Role getRole(Long id); }
-
使用注解实现映射器:
public interface RoleMapper { @Select("select id,roleName,note from role where id=#{id}") public Role getRole(Long id); }
-
-
SqlSession发送SQL:
public static void main(String[] args) { SqlSessionFactory sqlSessionFactory = null; String resource = "/mybatis-config.xml"; SqlSession sqlSession = null; try { InputStream inputStream = Test.class.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = sqlSessionFactory.openSession(); Role role = sqlSession.selectOne("com.zking.pojo.RoleMapper.getRole", 1L); //如果命名空间只有一个id为getRole的,可以简写为getRole Role role2 = sqlSession.selectOne("getRole", 1L); System.out.println("role:"+role); System.out.println("role2:"+role2); } catch (Exception e) { e.printStackTrace(); } finally { if (sqlSession != null) { sqlSession.close(); } } }
-
用Mapper接口发送SQL:
public static void main(String[] args) { SqlSessionFactory sqlSessionFactory = null; String resource = "/mybatis-config.xml"; SqlSession sqlSession = null; try { InputStream inputStream = Test.class.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = sqlSessionFactory.openSession(); Role role = sqlSession.selectOne("com.zking.pojo.RoleMapper.getRole", 1L); //如果命名空间只有一个id为getRole的,可以简写为getRole Role role2 = sqlSession.selectOne("getRole", 1L); //使用Mapper接口发送sql(直接匹配<mapper namespace="com.zking.pojo.RoleMapper">) RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); Role role3 = roleMapper.getRole(1L); System.out.println("role:"+role); System.out.println("role2:"+role2); System.out.println("role3:"+role3); } catch (Exception e) { e.printStackTrace(); } finally { if (sqlSession != null) { sqlSession.close(); } } }
-
Mybatis各个类的生命周期:
- SqlSessionFactoryBuilder:创建SqlSessionFactory中存在,之后失效。
- SqlSessionFactory:SqlSessionFactory可以被认为是一个数据库连接池,它的作用是创建SqlSession接口对象。一旦创建,一直存在,直到mybatis是使用完毕。所以,它一般是一个单例。
- SqlSession:存在一个事务中,commit或者rollback后结束。
- Mapper:与SqlSession同周期。
-
实例:
-
日志文件log4j.properties
log4j.rootLogger=DEBUG,SYS_CONSOLE,ROLLING_FILE log4j.logger.com.swzer=DEBUG #log4j.logger.com.ibatis=DEBUG #log4j.logger.com.mysql=DEBUG #log4j.logger.java.sql=DEBUG #log4j.logger.org.apache=DEBUG #log4j.logger.com.opensymphony.oscache=DEBUG log4j.logger.com.opensymphony.xwork2.interceptor=DEBUG log4j.logger.com.opensymphony.xwork2.ognl.OgnlValueStack=ERROR #************************* # System Console Appender #************************* log4j.appender.SYS_CONSOLE=org.apache.log4j.ConsoleAppender log4j.appender.SYS_CONSOLE.Target=System.out log4j.appender.SYS_CONSOLE.layout=org.apache.log4j.PatternLayout log4j.appender.SYS_CONSOLE.layout.ConversionPattern=(SYS)[%d] -%-4r [%t] %-5p %c(%F:%L) %x: %m%n log4j.appender.ROLLING_FILE=org.apache.log4j.RollingFileAppender log4j.appender.ROLLING_FILE.Threshold=DEBUG log4j.appender.ROLLING_FILE.File=${catalina.base}/logs/swzer.log #log4j.appender.ROLLING_FILE.Append=true log4j.appender.ROLLING_FILE.MaxFileSize=10240KB log4j.appender.ROLLING_FILE.MaxBackupIndex=20 log4j.appender.ROLLING_FILE.layout=org.apache.log4j.PatternLayout log4j.appender.ROLLING_FILE.layout.ConversionPattern=[%-5p] %d{yyyy-MM-dd HH:mm:ss} :%l: %m%n
-
数据库配置文件jdbc.properties
jdbc.url=jdbc:mysql://localhost:3306/worktest?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8 jdbc.driverClass=com.mysql.cj.jdbc.Driver jdbc.username=root jdbc.password=root
-
Mapper映射器接口:com.zking.mapper.RoleMapper
package com.zking.mapper; import java.util.List; import com.zking.pojo.Role; public interface RoleMapper { // @Select("select id,roleName,note from role where id=#{id}") public Role getRole(Long id); public int insertRole(Role role); public int deleteRole(Long id); public int updateRole(Role role); public List<Role> findRoles(); }
-
实体类com.zking.pojo.Role
package com.zking.pojo; public class Role { private Long id;//编号 private String roleName;//角色名称 private String note;//备注 =====省略getter、setter、toString()、无参和有参构造 }
-
获取SqlSession工具类:com.zking.utils.SqlSessionFactoryUtils
package com.zking.utils; import java.io.InputStream; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class SqlSessionFactoryUtils { private final static Class<SqlSessionFactoryUtils> LOCK = SqlSessionFactoryUtils.class; private SqlSessionFactoryUtils(){} private static SqlSessionFactory sqlSessionFactory = null; /** * 获取一个SqlSessionFactory单实例 */ public static SqlSessionFactory getSqlSessionFactory(){ synchronized (LOCK) { if (sqlSessionFactory != null) { return sqlSessionFactory; } String resource = "/mybatis-config.xml"; InputStream inputStream = null; try { inputStream = SqlSessionFactoryUtils.class.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (Exception e) { e.printStackTrace(); return null; } return sqlSessionFactory; } } /** * 获取一个SqlSession */ public static SqlSession openSqlSession(){ if (sqlSessionFactory == null) { getSqlSessionFactory(); } return sqlSessionFactory.openSession(); } /** * 关闭sqlSession * @param sqlSession */ public static void closeSqlSession(SqlSession sqlSession){ if (sqlSession != null) { sqlSession.close(); } } }
-
mybatis的配置文件:
<?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> <!-- mybatis配置文件标签匹配顺序: 元素类型为 "configuration" 的内容必须匹配 "(properties?,settings?, typeAliases?,typeHandlers?,objectFactory?, objectWrapperFactory?,reflectorFactory?, plugins?,environments?,databaseIdProvider?,mappers?) --> <!-- 加载配置文件 --> <properties resource="jdbc.properties"/> <typeAliases> <!-- 单个别名定义 --> <typeAlias alias="role" type="com.zking.pojo.Role"/> <!-- 批量别名定义,扫描整个包下的类,别名为类名(首字母大小写都可以) --> <package name="com.zking.pojo"/> <!-- <package name="其它包"/> --> </typeAliases> <!-- 描述数据库 --> <environments default="development"> <environment id="development"> <!-- 配置事务管理器,这里是JDBC管理器方式 --> <transactionManager type="JDBC"/> <!-- 配置事务管理器,这里是MANAGED管理器方式,自动提交和回滚 --> <!-- <transactionManager type="MANAGED"> <!-- 连接会默认关闭,所以这里需要设置不关闭连接 --> <property name="closeConnection" value="false" /> </transactionManager> --> <!-- POOLED表示采用Mybatis内部提供的连接池方式 --> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driverClass}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/zking/mapper/RoleMapper.xml"/> <!-- <package name="com.zking.mappers"/> --> <!-- <mapper class="com.zking.pojo.RoleMapper"/> --> </mappers> </configuration>
-
Mapper映射器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.zking.mapper.RoleMapper"> <select id="getRole" parameterType="long" resultType="role"> select id,roleName,note from role where id=#{id} </select> <insert id="insertRole" parameterType="role"> insert into role(roleName,note) values(#{roleName},#{note}) </insert> <delete id="deleteRole" parameterType="role"> delete from role where id=#{id} </delete> <update id="updateRole" parameterType="role"> update role set roleName=#{roleName},note=#{note} where id=#{id} </update> <select id="findRoles" resultType="role"> select id,roleName,note from role </select> </mapper>
-
测试类:com.zking.main.CharpterMain
package com.zking.main; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.apache.log4j.Logger; import com.zking.mapper.RoleMapper; import com.zking.pojo.Role; import com.zking.utils.SqlSessionFactoryUtils; public class CharpterMain { public static void main(String[] args) { Logger logger = Logger.getLogger(CharpterMain.class); SqlSession sqlSession = null; try { sqlSession = SqlSessionFactoryUtils.openSqlSession(); RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); //查询 // Role role = roleMapper.getRole(1L); // logger.info(role); //增加 // Role role2 = new Role(null, "lpm", "帅"); // int i = roleMapper.insertRole(role2); // sqlSession.commit(); // logger.info(i); //修改 // Role role3 = roleMapper.getRole(1L); // role3.setRoleName("admin666"); // role3.setNote("丑"); // int i2 = roleMapper.updateRole(role3); // sqlSession.commit(); // logger.info(i2); //删除 // int i3 = roleMapper.deleteRole(3L); // sqlSession.commit(); // logger.info(i3); //查询所有 List<Role> roles = roleMapper.findRoles(); logger.info(roles); } catch (Exception e) { sqlSession.rollback(); e.printStackTrace(); } finally { SqlSessionFactoryUtils.closeSqlSession(sqlSession); } } }
-
-
二、Mybatis配置
-
Mybatis配置文件所有元素标签如下:Mybatis标签顺序不能颠倒,颠倒则会倒置初始化失败。常用标签:properties,settings,typeAliases,typeHandlers,plugins,environments,mappers。
<?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> <!-- 配置 --> <!-- mybatis配置文件标签匹配顺序: 元素类型为 "configuration" 的内容必须匹配 "(properties?,settings?, typeAliases?,typeHandlers?,objectFactory?, objectWrapperFactory?,reflectorFactory?, plugins?,environments?,databaseIdProvider?,mappers?) --> <properties /> <!-- 属性 --> <settings /> <!-- 设置 --> <typeAliases /> <!-- 类型命名 --> <typeHandlers /> <!-- 类型处理器 --> <objectFactory /> <!-- 对象工厂 --> <reflectorFactory /> <!-- 反射工厂 --> <plugins /> <!-- 插件 --> <environments><!-- 配置环境 --> <environment><!-- 环境变量 --> <transactionManager/><!-- 数据库事务 --> <dataSource><!-- 数据源 --> </environment> </environments> <databaseIdProvider /> <!-- 数据库厂商标识 --> <mappers /> <!-- 映射器 --> </configuration>
-
properties:properties属性可以给系统配置一些运行参数,这样就方便修改,而不用总是去修改代码。
-
property子元素:直接将数据库配置编写在这里,方便调用。
<?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> <properties> <property name="jdbc.url" value="jdbc:mysql://localhost:3306/worktest?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8"/> <property name="jdbc.driverClass" value="com.mysql.cj.jdbc.Driver"/> <property name="jdbc.username" value="root"/> <property name="jdbc.password" value="root"/> </properties> <environments default="development"> <environment id="development"> <!-- 配置事务管理器,这里是JDBC管理器方式 --> <transactionManager type="JDBC"/> <!-- 配置事务管理器,这里是MANAGED管理器方式,自动提交和回滚 --> <!-- <transactionManager type="MANAGED"> <!-- 连接会默认关闭,所以这里需要设置不关闭连接 --> <property name="closeConnection" value="false" /> </transactionManager> --> <!-- POOLED表示采用Mybatis内部提供的连接池方式 --> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driverClass}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> </configuration>
-
使用properties文件:
<properties resource="jdbc.properties"/>
-
使用程序传递方式传递参数:
//获取properties配置文件信息省略 //将拿出来的内容调用某个写好的方法进行解密,然后重新设置到properties配置文件中 //最后创建sqlsessionfactory //SqlSessionFactory sqlSessionFactory = // new SqlSessionFactoryBuilder().build(inputStream, properties);
-
-
settings设置:配置文件中最复杂的配置,虽然内容很多,但是它都是有默认值的,在默认情况下都可以运行。我们只需要修改其中常用的几个参数就可以,比如:自动映射、驼峰命名映射、级联规则、是否启动缓存、执行器类型等。一下是配置说明:
//常用设置 <setting name="cacheEnabled" value="true" /> <setting name="defaultStatementTimeout" value="3000" /> <setting name="mapUnderscoreToCamelCase" value="true" /> <setting name="proxyFactory" value="CGLIB" /> <setting name="lazyLoadingEnabled" value="true" />
-
typeAliases别名:使用一个别名代替类所在很长的一个路径,别名不区分大小写。
- 在Mybatis中,自动初始化的一些别名: 如果要用到数组,则可直接写比如:_int[]
- Mybatis如何使用代码初始化别名:
-
Configration配置的别名:
-
自定义别名:
<typeAliases> <!-- 单个别名定义 --> <typeAlias alias="role" type="com.zking.pojo.Role"/> <!-- 可以写多个typeAlias..... --> <!-- 批量别名定义,扫描整个包下的类,别名为类名首字母小写(不区分大小写,首字母大小写都可以) --> <package name="com.zking.pojo"/> <!-- <package name="其它包"/> --> </typeAliases> //注意:如果两个包出现了相同的类名,则会出现别名重复,则需要在类中用注解进行区分。 import org.apache.ibatis.type.Alias; @Alias(value = "role2") public class Role { }
- 在Mybatis中,自动初始化的一些别名: 如果要用到数组,则可直接写比如:_int[]
-
typeHandlers类型转换器:就是将数据库中的数据类型与java中的数据类型进行匹配,一般数数据类型Mybatis会默认进行转换。
6. environment数据源环境:
<dataSource type="POOLED"/>中type有三种选项:
1.UNPOOLED:不使用连接池。每次请求会重新建立一个新的连接。速度较慢,在一些对性能要求没有很高的场合可以使用。
2.POOLED:使用连接池。在初始化时就建立很多个空的连接,这样在达到连接池上限时,新的的连接则不会再重新去创建,因为创建的过程是比较慢的。
3.JNDI
4.我们也可以自定义一个数据源,比如DBCP。
- databaseIdProvider数据库厂商标识:当需要用到多种数据库时,则用到此选项。
-
系统默认:
<databaseIdProvider type="DB_VENDOR"> <property name="Oracle" value="oracle"/> <property name="MySQL" value="mysql"/> <property name="DB2" value="db2"/> </databaseIdProvider>
-
三、映射器
-
映射器的配置元素:
- select:查询语句,最常用、最复杂的元素之一。可以自定义参数,返回结果集等。
- insert:插入语句。执行后返回一个整数,代表插入的条数。
- update:更新语句。执行后返回一个整数,代表更新的条数。
- delete:删除语句。执行后返回一个整数,代表删除的条数。
- parameterMap:定义参数映射关系。即将被删除的元素,不建议使用。
- sql:运行定义一部分SQL,然后在各个地方引用它。例如,一张表列名,一次定义,可以在多个SQL语句中使用。
- resultMap:用来描述从数据库结果集张来加载对象,它是最复杂,最强大的元素。它将提供映射规则。
- cache:给定命名空间的缓存配置。
- cache-ref:其他命名空间缓存配置的引用。
-
select元素——查询语句
-
概述:在映射器中select元素代表SQL的select语句,用于查询。在SQL中,select语句是用的最多的语句,在Mybatis中select元素也是用的最多的元素,使用的多就意味着强大和复杂。先来看看元素的配置。在实际工作中用的最多的是id、parameterType、resultType、resultMap,如果要设置缓存,还会使用到flushCache、userCache,其他都是不常用的功能。
[外链图片转存失败(img-q0Fs4GJz-1569121641392)(课上图片/select元素的配置1.png)]
[外链图片转存失败(img-WnATutdv-1569121641393)(课上图片/select元素的配置2.png)] -
简单的select元素的应用:
<select id="getRole" parameterType="long" resultType="role"> select id,roleName,note from role where id=#{id} </select> 其中: id:配合Mapper的全限定名,联合成为一个唯一标识(在不考虑数据库厂商标识的前提下),用于标识这条sql。 parameterType:sql可接受参数类型。可以是系统定义或者自定义的别名。 resultType:sql返回的类型。 #{id}:被传递进去的参数。 //还需要定义一个接口 public Role getRole(Long id);
-
自动映射和驼峰映射:在settings元素中有两个可以配置的选项autoMappingBehavior和mapUnderscoreToCameCase,他们是控制自动映射和驼峰映射的开关。自定映射一般用的多,驼峰映射要求严苛,用的较少。
-
配置自动映射autoMappingBehavior的取值范围:
- NONE:不进行自动映射。
- PARTIAL:默认值,只对没有嵌套结果集进行自动映射。
- FULL:对所有的结果集进行自动映射,包括嵌套结果集。
-
举例:
package com.zking.pojo; public class Role { private Long id;//编号 private String roleName;//角色名称 private String note;//备注 =====省略getter、setter、toString()、无参和有参构造 } //这是一个十分简单的POJO,它定义了3个属性及getter和setter方法,如果编写sql列名和属性名保持一致,那么它就会形成自动映射。 <select id="getRole" parameterType="long" resultType="role"> select id,roleName,note from role where id=#{id} </select>
-
-
传递多个参数:
-
使用map接口传递多个参数:
<select id="findRolesByMap" parameterType="map" resultType="role"> select id,roleName,note from role where roleName like concat('%',#{roleName},'%') and note like concat('%',#{note},'%') </select> //接口中就要写成如下所示: public List<Role> findRolesByMap(Map<String, Object> paramMap); //获取数据 Map<String, Object> paramMap = new HashMap<String, Object>(); paramMap.put("roleName", "a"); paramMap.put("note", "丑"); List<Role> roles = roleMapper.findRolesByMap(paramMap);
-
使用注解传递多个参数:
<select id="findRolesByAnnotation" resultType="role"> select id,roleName,note from role where roleName like concat('%',#{roleName},'%') and note like concat('%',#{note},'%') </select> public List<Role> findRolesByAnnotation(@Param("roleName") String roleName, @Param("note") String note); List<Role> roles = roleMapper.findRolesByAnnotation("a", "丑");
-
使用JavaBean传递多个参数:
<insert id="insertRole" parameterType="role"> insert into role(roleName,note) values(#{roleName},#{note}) </insert> public int insertRole(Role role); Role role2 = new Role(null, "lpm", "帅"); int i = roleMapper.insertRole(role2); sqlSession.commit(); logger.info(i);
-
混合使用:
public class PageParams{ private int start; private int limit; =========省略getter和setter======== } public List<Role> findByMix(@Param("role") Role role,@Param("page") PageParams page); <select id="findByMix" resultType="role"> select id,roleName,note from role where roleName like concat('%',#{role.roleName},'%') and note like concat('%',#{role.note},'%') limit #{page.start},#{page.limit} </select>
-
-
使用resultMap映射结果集:
<resultMap type="role" id="roleMap"> <id property="id" column="id"/> <result property="roleName" column="roleName"/> <result property="note" column="note"/> </resultMap> <select id="getRoleUserResultMap" parameterType="long" resultMap="roleMap"> select id,roleName,note from role where id=#{id} </select>
-
分页参数RoleBounds:
public List<Role> findRolesByRowBounds(@Param("roleName") String roleName, @Param("note") String note, RowBounds rowBounds); <select id="findRolesByRowBounds" resultType="role"> select id,roleName,note from role where roleName like concat('%',#{roleName},'%') and note like concat('%',#{note},'%') </select> RowBounds rowBounds = new RowBounds(0, 2); List<Role> roles = roleMapper.findRolesByRowBounds("a", "丑", rowBounds); logger.info(roles);
-
-
insert元素——插入语句:
-
insert语句的配置:
[外链图片转存失败(img-lLDdmp5Y-1569121641394)(课上图片/insert语句的配置.png)] -
简单的insert语句的应用:
<insert id="insertRole" parameterType="role"> insert into role(roleName,note) values(#{roleName},#{note}) </insert>
-
主键回填:当主键自动生成后,再将主键的值返回给这个对象。
<insert id="insertRole" parameterType="role" useGeneratedKeys="true" keyProperty="id"> insert into role(roleName,note) values(#{roleName},#{note}) </insert> Role role2 = new Role(null, "lpm", "帅"); int i = roleMapper.insertRole(role2); sqlSession.commit(); logger.info(i);
-
自定义主键:
<insert id="insertRole" parameterType="role" useGeneratedKeys="true" keyProperty="id"> <selectKey keyProperty="id" resultType="long" order="BEFORE"> select if(max(id) = null,max(id)+1) from role </selectKey> insert into role(id,roleName,note) values(#{id},#{roleName},#{note}) </insert>
-
-
update和delete元素:
<delete id="deleteRole" parameterType="long"> delete from role where id=#{id} </delete> <update id="updateRole" parameterType="role"> update role set roleName=#{roleName},note=#{note} where id=#{id} </update>
-
sql元素:sql元素可以定义一条sql的一部分,方便后面的sql引用它,比如最典型的列名。通常情况下要在select、insert等语句中反复使用他们,特别是字段多的表。
<sql id="roleCols"> id,roleName,note </sql> <select id="findRolesByRowBounds" resultType="role"> select <include refid="roleCols" /> from role where roleName like concat('%',#{roleName},'%') and note like concat('%',#{note},'%') </select> //给sql元素传递参数。将别名r传递给sql元素 <sql id="roleCols"> ${alias}.id,${alias}.roleName,${alias}.note </sql> <select id="findRolesByRowBounds" resultType="role"> select <include refid="roleCols"> <property name="alias" value="r"/> </include> from role r where roleName like concat('%',#{roleName},'%') and note like concat('%',#{note},'%') </select>
-
参数:
-
存储过程参数支持:
#{id,mode=IN}//输入参数 #{RoleName,mode=OUT}//输出参数 #{id,mode=INOUT}//输入输出参数
-
特殊字符串的替换和处理:如果使用动态列的时候可以使用美元符号。
//在下面的语句中,如果 username 的值为 zhangsan,则两种方式无任何区别: select * from user where name = #{name}; select * from user where name = ${name}; //其解析之后的结果均为: select * from user where name = 'zhangsan'; //但是 #{} 和 ${} 在预编译中的处理是不一样的。#{} 在预处理时,会把参数部分用一个占位符 ? 代替,变成如下的 sql 语句: select * from user where name = ?; //而 ${} 则只是简单的字符串替换,在动态解析阶段,该 sql 语句会被解析成 select * from user where name = 'zhangsan';
-
-
resultMap元素:
-
使用map存储结果集:
<select id="getRole" parameterType="long" resultType="map"> select id,roleName,note from role where id=#{id} </select>
-
使用POJO存储结果集:
<resultMap type="role" id="roleMap"> <id property="id" column="id" /> <result property="roleName" column="roleName"/> <result property="note" column="note" /> </resultMap>
-
-
级联:当两个表或两个表以上之间存在有关联关系时,就要用到级联操作。
- mybatis中的级联:
- 鉴别器(discriminator):它时一个根据某些条件决定采用具体实现类级联的方案,比如体检表要根据性别去区分。
- 一对一(association):比如学生证和学生就是一种一对一的级联。
- 一对多(collection):比如班主任和学生就是一种一对多的级联。注意:mybatis中没有多对多的级联,因为多对多太过复杂,且可以通过两个一对多的级联进行替换。
- 级联举例:
-
数据库表关系与项目结构图(具体sql文件见:雇员级联模型.sql):
-
雇员级联模型:
-
用户与角色模型:
-
项目结构图:
-
-
数据库配置文件jdbc.properties:
jdbc.url=jdbc:mysql://localhost:3306/worktest? useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8 jdbc.driverClass=com.mysql.cj.jdbc.Driver jdbc.username=root jdbc.password=root
-
日志配置文件log4j.properties:
log4j.rootLogger=DEBUG,SYS_CONSOLE,ROLLING_FILE log4j.logger.com.swzer=DEBUG #log4j.logger.com.ibatis=DEBUG #log4j.logger.com.mysql=DEBUG #log4j.logger.java.sql=DEBUG #log4j.logger.org.apache=DEBUG #log4j.logger.com.opensymphony.oscache=DEBUG log4j.logger.com.opensymphony.xwork2.interceptor=DEBUG log4j.logger.com.opensymphony.xwork2.ognl.OgnlValueStack=ERROR #************************* # System Console Appender #************************* log4j.appender.SYS_CONSOLE=org.apache.log4j.ConsoleAppender log4j.appender.SYS_CONSOLE.Target=System.out log4j.appender.SYS_CONSOLE.layout=org.apache.log4j.PatternLayout log4j.appender.SYS_CONSOLE.layout.ConversionPattern=(SYS)[%d] -%-4r [%t] %-5p %c(%F:%L) %x: %m%n log4j.appender.ROLLING_FILE=org.apache.log4j.RollingFileAppender log4j.appender.ROLLING_FILE.Threshold=DEBUG log4j.appender.ROLLING_FILE.File=${catalina.base}/logs/swzer.log #log4j.appender.ROLLING_FILE.Append=true log4j.appender.ROLLING_FILE.MaxFileSize=10240KB log4j.appender.ROLLING_FILE.MaxBackupIndex=20 log4j.appender.ROLLING_FILE.layout=org.apache.log4j.PatternLayout log4j.appender.ROLLING_FILE.layout.ConversionPattern=[%-5p] %d{yyyy-MM-dd HH:mm:ss} :%l: %m%n
-
mybatis全局配置文件:
<?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> <!-- mybatis配置文件标签匹配顺序: 元素类型为 "configuration" 的内容必须匹配 "(properties?,settings?, typeAliases?,typeHandlers?,objectFactory?, objectWrapperFactory?,reflectorFactory?, plugins?,environments?,databaseIdProvider?,mappers?) --> <!-- 加载配置文件 --> <properties resource="jdbc.properties"/> <!-- 更改默认全局设置 --> <settings> <!-- 设置mybats是否缓存 --> <setting name="cacheEnabled" value="true" /> <!-- 是否适用驼峰命名法 --> <setting name="mapUnderscoreToCamelCase" value="true" /> <!-- 是否延迟加载:当查询的级联关系未使用到时,不会发送sql语句 --> <setting name="lazyLoadingEnabled" value="true"></setting> <!-- 是否使用层级加载: 层级加载即与查询的表直接相关的属于同一层,且会自动加载。 比如:与雇员直接相关的有雇员任务、工牌等,在查询雇员时,如果配置为true,这些都会直接加载 --> <setting name="aggressiveLazyLoading" value="false"></setting> </settings> <!-- 别名定义 --> <typeAliases> <!-- 单个别名定义 --> <!-- <typeAlias alias="role" type="com.zking.pojo.Role"/> --> <!-- 批量别名定义,扫描整个包下的类,别名为类名(首字母大小写都可以) --> <package name="com.zking.pojo"/> <!-- <package name="其它包"/> --> </typeAliases> <!-- 描述数据库 --> <environments default="development"> <environment id="development"> <!-- 配置事务管理器,这里是JDBC管理器方式 --> <transactionManager type="JDBC"/> <!-- 配置事务管理器,这里是MANAGED管理器方式,自动提交和回滚 --> <!-- <transactionManager type="MANAGED"> <!-- 连接会默认关闭,所以这里需要设置不关闭连接 --> <property name="closeConnection" value="false" /> </transactionManager> --> <!-- POOLED表示采用Mybatis内部提供的连接池方式 --> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driverClass}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <!-- 数据库厂商标识 --> <databaseIdProvider type="DB_VENDOR"> <property name="Oracle" value="oracle"/> <property name="MySQL" value="mysql"/> </databaseIdProvider> <!-- 加载映射器文件,如果直接加载一个包,则必须映射器与映射器接口在同一个包下 --> <mappers> <!-- <mapper resource="com/zking/mapper/RoleMapper.xml"/> --> <package name="com.zking.mapper"/> </mappers> </configuration>
-
获取session的配置文件SqlSessionFactoryUtils.java:
package com.zking.utils; import java.io.InputStream; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; /** * 获取session工厂工具类 */ public class SqlSessionFactoryUtils { //得到改类的一个反射 private final static Class<SqlSessionFactoryUtils> LOCK = SqlSessionFactoryUtils.class; //私有方法,禁止外部创建该类的实例 private SqlSessionFactoryUtils(){} private static SqlSessionFactory sqlSessionFactory = null; /** * 获取一个SqlSessionFactory单实例 */ public static SqlSessionFactory getSqlSessionFactory(){ synchronized (LOCK) { if (sqlSessionFactory != null) { return sqlSessionFactory; } String resource = "/mybatis-config.xml"; InputStream inputStream = null; try { inputStream = SqlSessionFactoryUtils.class.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (Exception e) { e.printStackTrace(); return null; } return sqlSessionFactory; } } /** * 获取一个SqlSession */ public static SqlSession openSqlSession(){ if (sqlSessionFactory == null) { getSqlSessionFactory(); } return sqlSessionFactory.openSession(); } /** * 关闭sqlSession * @param sqlSession */ public static void closeSqlSession(SqlSession sqlSession){ if (sqlSession != null) { sqlSession.close(); } } }
-
所有pojo实体类:
//雇员类Employee.java /** * 雇员类 */ public class Employee { private Long id;//编号 private String realName;//姓名 private String sex;//性别 private Date birthday;//生日 private String mobile;//手机号 private String email;//邮箱 private String position;//职位 private String note;//备注 //工牌按一对一级联 private WorkCard workCard; //雇员任务,一对多级联 private List<EmployeeTask> employeeTaskList = null; =============getter、setter、构造方法、toString()=============== } //雇员任务类EmployeeTask.java /** * 雇员任务类 */ public class EmployeeTask { private Long id;//编号 private Long empId;//雇员编号 private Long taskId;//任务编号 private String taskName;//任务名称 private String note;//备注 private Task task = null; =============getter、setter、构造方法、toString()=============== } //体检表父类HealthForm.java /** * 体检表父类 */ public class HealthForm { private Long id;//编号 private Long empId;//雇员编号 private String heart;//心 private String liver;//肝 private String spleen;//脾 private String lung;//肺 private String kidney;//肾 private String note;//备注 =============getter、setter、构造方法、toString()=============== } //女雇员类FemaleEmployee.java /** * 女雇员类 */ public class FemaleEmployee extends Employee{ private FemaleHealthForm femaleHealthForm = null; =============getter、setter、构造方法、toString()=============== } /** * 女性体检表类 */ public class FemaleHealthForm extends HealthForm{ private String prostate;//子宫 =============getter、setter、构造方法、toString()=============== } //男雇员类MaleEmployee.java /** * 男雇员类 */ public class MaleEmployee extends Employee{ //男体检表类 private MaleHealthForm maleHealthForm = null; =============getter、setter、构造方法、toString()=============== } //男性体检表类FemaleHealthForm.java /** * 男性体检表类 */ public class MaleHealthForm extends HealthForm { private String uterus;//前列腺 =============getter、setter、构造方法、toString()=============== } //任务类Task.java /** * 任务类 */ public class Task { private Long id;// private String title;//任务标题 private String context;//任务内容 private String note;//备注 =============getter、setter、构造方法、toString()=============== } //工牌类WorkCard.java /** * 工牌类 */ public class WorkCard { private Long id;//编号 private Long empId;//雇员编号 private String realName;//姓名 private String department;//手机 private String mobile;//邮件 private String position;//职位 private String note;//备注 =============getter、setter、构造方法、toString()=============== } //角色表Role.java /** * 角色表 */ public class Role implements Serializable{ private static final long serialVersionUID = -2095890302779679742L; private Long id;//编号 private String roleName;//角色名 private String note;//备注 //单独一对多的关系 private List<User> users; =============getter、setter、构造方法、toString()=============== } //用户表User.java: /** * 用户 */ public class User implements Serializable{ private static final long serialVersionUID = -6743318673622528797L; private int id;//编号 private String username;//用户名 private String password;//密码 //单独一对多关系 private List<Role> roles; =============getter、setter、构造方法、toString()=============== }
-
映射器接口:由于映射器接口中的方法都是大同小异,这里就只给出一个例子和所有名称:
/** * 雇员映射器接口 */ public interface EmployeeMapper { public Employee getEmployee(Long id); } EmployeeTaskMapper.java 雇员任务 FemaleEmployeeMapper.java 女雇员 FemaleHealthFormMapper.java 女雇员体检 HealthFormMapper.java 体检父类 MaleEmployeeMapper.java 男雇员 MaleHealthFormMapper.java 男雇员体检 TaskMapper.java 任务 WorkCardMapper.java 工牌 RoleMapper.java 角色 UserMapper.java 用户
-
映射器配置文件:只是测试了个别级联,所以这里没有写内容的映射文件不展示,只写名称。
//雇员映射器:EmployeeMapper.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.zking.mapper.EmployeeMapper"> <!-- 雇员 --> <!-- 设置返回结果集类型 --> <resultMap type="employee" id="employee"> <id column="id" property="id" /> <result column="real_name" property="realName" /> <result column="sex" property="sex" /> <result column="birthday" property="birthday" /> <result column="mobile" property="mobile" /> <result column="email" property="email" /> <result column="position" property="position" /> <result column="note" property="note" /> <!-- 一对一。根据Employee表中的id传递给工牌表,然后查询内容 --> <association property="workCard" column="id" select="com.zking.mapper.WorkCardMapper.getWorkCardByEmpId" /> <!-- 一对多。根据雇员编号查询对应任务表信息 --> <collection property="employeeTaskList" column="id" select="com.zking.mapper.EmployeeTaskMapper.getemployeeTaskByEmpId" fetchType="lazy" /><!-- fetchType属性会忽略全局属性 --> </resultMap> <!-- 将常用列名编写成sql语句以便引用 --> <sql id="employeeColnums"> id,real_name realName,sex,birthday,mobile,email,position,note </sql> <select id="getEmployee" parameterType="long" resultMap="employee"> select <include refid="employeeColnums" /> from t_employee where id = #{id} </select> </mapper> //雇员任务EmployeeTaskMapper.xml <mapper namespace="com.zking.mapper.EmployeeTaskMapper"> <!-- 雇员任务 --> <!-- 将常用列名编写成sql语句以便引用 --> <sql id="employeeTaskColnums"> id,emp_id,task_name,note </sql> <!-- 设置返回结果集类型 --> <resultMap type="employeeTask" id="EmployeeTaskMap"> <id column="id" property="id" /> <result column="emp_id" property="empId" /> <result column="task_name" property="taskName" /> <result column="note" property="note" /> <association property="task" column="task_id" select="com.zking.mapper.TaskMapper.getTask" /> </resultMap> <!-- 根绝雇员编号查询雇员任务信息 --> <select id="getemployeeTaskByEmpId" parameterType="long" resultMap="EmployeeTaskMap"> select <include refid="employeeTaskColnums" /> from t_employee_task where emp_id=#{empId} </select> </mapper> //女雇员体检FemaleHealthFormMapper.xml <mapper namespace="com.zking.mapper.FemaleHealthFormMapper"> <!-- 女性体检 --> <sql id="femaleHealthFormColnums"> id,emp_id empId,heart,liver,spleen,lung,kidney,prostate,note </sql> <!-- 根据雇员编号查询体检表信息 --> <select id="getFemaleHealthForm" parameterType="long" resultType="femaleHealthForm"> select <include refid="femaleHealthFormColnums" /> from t_female_health_form where emp_id = #{empId} </select> </mapper> //男雇员体检MaleHealthFormMapper.xml <mapper namespace="com.zking.mapper.MaleHealthFormMapper"> <!-- 男性体检 --> <sql id="maleHealthFormColnums"> id,emp_id empId,heart,liver,spleen,lung,kidney,uterus,note </sql> <!-- 根据雇员编号查询体检表信息 --> <select id="getMaleHealthForm" parameterType="long" resultType="maleHealthForm"> select <include refid="maleHealthFormColnums" /> from t_male_health_form where emp_id = #{empId} </select> </mapper> //任务TaskMapper.xml <mapper namespace="com.zking.mapper.TaskMapper"> <!-- 任务 --> <!-- 将常用列名编写成sql语句以便引用 --> <sql id="taskColnums"> id,title,context,note </sql> <!-- 根据id获取单个任务信息 --> <select id="getTask" parameterType="long" resultType="task"> select <include refid="taskColnums"/> from t_task where id = #{id} </select> </mapper> //工牌表WorkCardMapper.xml <mapper namespace="com.zking.mapper.WorkCardMapper"> <!-- 工牌 --> <!-- 将常用列名编写成sql语句以便引用 --> <sql id="workCardColnums"> id,emp_id as empId,real_name as realName,department mobile,position,note </sql> <!-- 根据雇员编号查询雇员工牌表信息 --> <select id="getWorkCardByEmpId" parameterType="long" resultType="workCard"> select <include refid="workCardColnums" /> from t_work_card where emp_id=#{empId} </select> </mapper> //角色RoleMapper.xml <mapper namespace="com.zking.mapper.RoleMapper"> <!-- 配置使用mybatis二级缓存 注意:二级缓存的pojo类必须实现Serializable序列化接口,否则报错 --> <cache /> <resultMap type="role" id="roleMap"> <id column="id" property="id" /> <result column="roleName" property="roleName" /> <result column="note" property="note" /> <collection property="users" column="id" select="com.zking.mapper.UserMapper.getUserByRoleId" fetchType="lazy" /> </resultMap> <sql id="roleColunms"> ${alias}.id,${alias}.roleName,${alias}.note </sql> <!-- useCache="false":默认为true,直接让这条语句禁用二级缓存 flushCache="true":是否刷新缓存 --> <select id="getRole" parameterType="long" resultMap="roleMap" flushCache="true" useCache="false"> select <include refid="roleColunms"> <property name="alias" value="r" /> </include> from role r where r.id = #{id} </select> <select id="getRoleByUserId" parameterType="long" resultMap="roleMap"> select <include refid="roleColunms"> <property name="alias" value="r" /> </include> from role r,role_user ru where r.id = ru.roleid and ru.userid = #{userid} </select> </mapper> //用户UserMapper.xml <mapper namespace="com.zking.mapper.UserMapper"> <!-- 引用别的映射器中的二级缓存 --> <cache-ref namespace="com.zking.mapper.RoleMapper"/> <resultMap type="user" id="userMap"> <id column="id" property="id"/> <result column="username" property="username"/> <result column="password" property="password"/> <collection property="roles" column="id" select="com.zking.mapper.RoleMapper.getRoleByUserId" fetchType="lazy"/> </resultMap> <sql id="userColunms"> ${alias}.id,${alias}.username,${alias}.password </sql> <select id="getUser" parameterType="long" resultMap="userMap"> select <include refid="userColunms"> <property name="alias" value="u"/> </include> from user u where id = #{id} </select> <select id="getUserByRoleId" parameterType="long" resultMap="userMap"> select <include refid="userColunms"> <property name="alias" value="u"/> </include> from user u,role_user ru where u.id = ru.userid and ru.roleid = #{roleid} </select> </mapper>
-
测试类:
import org.apache.ibatis.session.SqlSession; import org.apache.log4j.Logger; import com.zking.mapper.RoleMapper; import com.zking.pojo.Role; import com.zking.utils.SqlSessionFactoryUtils; public class Test { public static void main(String[] args) { SqlSession sqlSession = null; try { Logger logger = Logger.getLogger(Test.class); sqlSession = SqlSessionFactoryUtils.openSqlSession(); //级联一对多与一对一关系 // EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class); // Employee employee = employeeMapper.getEmployee(1L); // logger.info(employee.getBirthday()); // logger.info(employee.getWorkCard()); // logger.info(employee.getEmployeeTaskList()); //多对多关系 // RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); // Role role = roleMapper.getRole(1L); // logger.info(role.getRoleName()); // role.getUsers(); // UserMapper userMapper = sqlSession.getMapper(UserMapper.class); // User user = userMapper.getUser(1L); // logger.info(user.getUsername()); // user.getRoles(); //一级缓存 // TaskMapper taskMapper = sqlSession.getMapper(TaskMapper.class); // Task task1 = taskMapper.getTask(1L); // logger.info(task1); // System.out.println("==============一级缓存==============="); // Task task2 = taskMapper.getTask(1L); // logger.info(task2); //二级缓存 // SqlSession sqlSession1 = SqlSessionFactoryUtils.openSqlSession(); // TaskMapper taskMapper1 = sqlSession1.getMapper(TaskMapper.class); // Task task1 = taskMapper1.getTask(1L); // logger.info(task1); // sqlSession1.commit(); // sqlSession1.close(); // System.out.println("==============二级缓存==============="); // SqlSession sqlSession2 = SqlSessionFactoryUtils.openSqlSession(); // TaskMapper taskMapper2 = sqlSession2.getMapper(TaskMapper.class); // Task task2 = taskMapper2.getTask(1L); // logger.info(task2); // sqlSession2.commit(); // sqlSession2.close(); } catch (Exception e) { e.printStackTrace(); } finally { SqlSessionFactoryUtils.closeSqlSession(sqlSession); } } }
-
二级缓存配置项:
-
- mybatis中的级联:
-
存储过程:
- 调用简单的存储过程:
-
首先,编写MySQL与Oracle中的存储过程:(注意:Oracle对存储过程有较好的支持,MySQL略显不足)
-- MySQL编写存储过程 -- 注意://代表将两个//之间的代码看作是存储过程,而不会把;直接看成一句话执行。 -- 最后DELIMITER ;代表将;号还原。 DROP PROCEDURE IF EXISTS count_role; DELIMITER // CREATE PROCEDURE count_role(IN p_role_name VARCHAR(20),OUT count_total INT,OUT exec_date DATE) BEGIN SELECT COUNT(*) INTO count_total FROM role WHERE roleName LIKE CONCAT('%',p_role_name,'%'); SELECT SYSDATE() INTO exec_date; END; // DELIMITER ; -- 调用存储过程 SET @p_role_name = 'i'; SET @count_total=0; SET @exec_date=NULL; CALL count_role(@p_role_name,@count_total,@exec_date); SELECT @p_role_name,@count_total,@exec_date; -- Oracle编写存储过程: --创建存储过程 create or replace procedure count_role( p_role_name in varchar2, count_total out number, exec_date out date ) is begin select count(*) into count_total from role where roleName like '%'||p_role_name||'%'; select sysdate into exec_date from dual; end; --调用存储过程 declare p_role_name varchar2(20):='i'; count_total number; exec_date date; begin count_role(p_role_name,count_total,exec_date); dbms_output.put_line(p_role_name||','||count_total||','||exec_date); end;
-
创建一个类用来传递和存储参数:
public class PdCountRoleParams { private String roleName; private int total; private Date execDate; =============getter、setter、构造方法、toString()=============== }
-
创建映射器语句:
<!-- statementType="CALLABLE":声明着是一个存储过程,否则报错 --> <select id="countRole" parameterType="pdCountRoleParams" statementType="CALLABLE"> <!-- mode:有三个参数,IN,OUT,INOUT --> {call count_role( #{roleName,mode=IN,jdbcType=VARCHAR}, #{total,mode=OUT,jdbcType=INTEGER}, #{execDate,mode=OUT,jdbcType=DATE} )} </select>
-
调用:
Logger logger = Logger.getLogger(CharpterMain.class); SqlSession sqlSession = null; try { sqlSession = SqlSessionFactoryUtils.openSqlSession(); RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); PdCountRoleParams params = new PdCountRoleParams(); params.setRoleName("i"); roleMapper.countRole(params); logger.info(params); } catch (Exception e) { sqlSession.rollback(); e.printStackTrace(); } finally { SqlSessionFactoryUtils.closeSqlSession(sqlSession); }
-
- 游标的使用:
-
创建存储过程:
--创建存储过程 create or replace procedure find_role( p_role_name in varchar2, p_start in number, p_end in number, r_count out number, ref_cur out sys_refcursor ) as begin select count(*) into r_count from role where roleName like '%'||p_role_name||'%'; open ref_cur for select id,roleName,note from( select id,roleName,note,rownum rn from role r where r.rolename like '%'||p_role_name||'%' and rownum <= p_end) where rn>p_start; end find_role; --调用存储过程 declare p_role_name varchar2(20):='i'; p_start number:=0; p_end number:=2; r_count number; ref_cur sys_refcursor; r role%rowtype; begin find_role(p_role_name,p_start,p_end,r_count,ref_cur); dbms_output.put_line(p_role_name||','||p_start||','||p_end||','||r_count); loop fetch ref_cur into r; exit when ref_cur%notfound; dbms_output.put_line(r.id||','||r.rolename||','||r.note); end loop; close ref_cur; end;
-
创建一个类来传递和接收参数:
public class PdFindRoleParams { private String roleName; private int start;//起始数据 private int end;//结束数据 private int total;//总数据数 //将游标数据放入到list集合中 private List<Role> roles; =============getter、setter、构造方法、toString()=============== }
-
创建映射器:
<mapper namespace="com.zking.mapper.RoleMapper"> <resultMap type="role" id="roleMap"> <id property="id" column="id" /> <result property="roleName" column="roleName" /> <result property="note" column="note" /> </resultMap> <select id="findRole" parameterType="pdFindRoleParams" statementType="CALLABLE"> {call find_role( #{roleName,mode=IN,jdbcType=VARCHAR}, #{start,mode=IN,jdbcType=INTEGER}, #{end,mode=IN,jdbcType=INTEGER}, #{total,mode=OUT,jdbcType=INTEGER}, #{roles,mode=OUT,jdbcType=CURSOR,javaType=ResultSet, resultMap=roleMap} )} </select> </mapper>
-
测试类:
Logger logger = Logger.getLogger(CharpterMain.class); SqlSession sqlSession = null; try { sqlSession = SqlSessionFactoryUtils.openSqlSession(); RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); PdFindRoleParams params = new PdFindRoleParams(); params.setRoleName("i"); params.setStart(0); params.setEnd(2); roleMapper.findRole(params); logger.info(params.getRoles()); } catch (Exception e) { sqlSession.rollback(); e.printStackTrace(); } finally { SqlSessionFactoryUtils.closeSqlSession(sqlSession); }
-
- 调用简单的存储过程:
四、动态SQL
-
概述:Mybaitis的动态SQL包括以下几种元素:
- if:判断语句。单条件分支判断。
- choose(when,otherwise):相当于Java中的switch和case语句。多条件分支判断。
- trim(where,set):辅助元素,用于处理特定的SQL拼装问题,比如去掉多余的and、or等。
- foreach:循环语句。在in语句等列举条件常用。
-
if元素:if元素是最常用的判断语句,相当于Java中的if语句,它常常与test属性联合使用。举例如下:
<select id="findRolesByCondition" parameterType="role" resultType="role"> select id,roleName,note from role where 1=1 <if test="roleName != null and roleName != ''"> and roleName like concat('%',#{roleName},'%') </if> </select> //测试: Logger logger = Logger.getLogger(CharpterMain.class); SqlSession sqlSession = null; try { sqlSession = SqlSessionFactoryUtils.openSqlSession(); RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); //当RoleName字段位空时,if后面的内容不执行。 Role role = new Role(); role.setRoleName("i"); List<Role> roles = roleMapper.findRolesByCondition(role); logger.info(roles); } catch (Exception e) { sqlSession.rollback(); e.printStackTrace(); } finally { SqlSessionFactoryUtils.closeSqlSession(sqlSession); }
-
choose、when、otherwise元素,举例如下:
<select id="findRolesByCondition" parameterType="role" resultType="role"> select id,roleName,note from role where 1=1 <choose> <when test="id != null and id != ''"> and id = #{id} </when> <when test="roleName != null and roleName != ''"> and roleName like concat('%',#{roleName},'%') </when> <otherwise> and note is not null </otherwise> </choose> </select> //测试: Logger logger = Logger.getLogger(CharpterMain.class); SqlSession sqlSession = null; try { sqlSession = SqlSessionFactoryUtils.openSqlSession(); RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); Role role = new Role(); role.setId(1L); role.setRoleName("i"); List<Role> roles = roleMapper.findRolesByCondition(role); } catch (Exception e) { sqlSession.rollback(); e.printStackTrace(); } finally { SqlSessionFactoryUtils.closeSqlSession(sqlSession); }
-
trim、where、set元素:上面的语句都有一个共同点,就是加入了一条where 1=1的语句,不加sql就会报错,这样看着会有点别扭。所以我们可以用到trim、where、set元素来处理。举例如下:
-
使用where:where元素会自动判断,如果where中的语句都不执行则不加where关键字,如果后面可以执行,where会自动把第一个条件的and去掉并加上where关键字。
<select id="findRolesByCondition" parameterType="role" resultType="role"> select id,roleName,note from role <where> <if test="roleName != null and roleName != ''"> and roleName like concat('%',#{roleName},'%') </if> <if test="note != null and note != ''"> and note like concat('%',#{note},'%') </if> </where> </select> //测试 Logger logger = Logger.getLogger(CharpterMain.class); SqlSession sqlSession = null; try { sqlSession = SqlSessionFactoryUtils.openSqlSession(); RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); Role role = new Role(); role.setRoleName("i"); role.setNote("高"); List<Role> roles = roleMapper.findRolesByCondition(role); logger.info(roles); } catch (Exception e) { sqlSession.rollback(); e.printStackTrace(); } finally { SqlSessionFactoryUtils.closeSqlSession(sqlSession); }
-
使用trim:与where不同,trim是去掉一些特殊的字符串,prefix代表的是语句的前缀。而prefixOverrides代表的是需要去掉的是哪种字符串。里面还有其他元素,suffix代表后缀。suffixOverrides代表去掉那种后缀字符串。这里的语句与where语句是等效的,这里就不列出测试了。
<select id="findRolesByCondition" parameterType="role" resultType="role"> select id,roleName,note from role <trim prefix="where" prefixOverrides="and"> <if test="roleName != null and roleName != ''"> and roleName like concat('%',#{roleName},'%') </if> <if test="note != null and note != ''"> and note like concat('%',#{note},'%') </if> </trim> </select>
-
使用set:hibernate中,如果要更新某个字段,则需要将所有的字段都更新一遍,这往往是比较消耗资源的。所以我们可以用set元素来完成。也可以使用trim元素代替。
//这里加上id=#{id},的原因是因为if后面都为空,这个sql语句就会报错。 <update id="updateRole" parameterType="role"> update role <set> id=#{id}, <if test="roleName != null and roleName != ''"> roleName=#{roleName}, </if> <if test="note != null and note != ''"> note=#{note}, </if> </set> where id=#{id} </update> //测试: Logger logger = Logger.getLogger(CharpterMain.class); SqlSession sqlSession = null; try { sqlSession = SqlSessionFactoryUtils.openSqlSession(); RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); Role role = new Role(); role.setId(1L); //role.setRoleName("admin"); role.setNote("超帅"); int i = roleMapper.updateRole(role); sqlSession.commit(); logger.info(i); } catch (Exception e) { sqlSession.rollback(); e.printStackTrace(); } finally { SqlSessionFactoryUtils.closeSqlSession(sqlSession); }
-
-
foreach元素:foreach元素是一个循环语句,它的作用是遍历集合,它能够很好的支持数组和List、Set接口的集合,对此提供遍历功能。它往往用于in关键字。
<select id="findRolesByIds" parameterType="role" resultType="role"> select id,roleName,note from role where id in <!-- 其中: item:循环遍历中的当前元素 index:当前元素所在集合位置的下标 open和close:使用什么符号将这些元素包装起来 separator:元素之间的分隔符 --> <foreach item="id" index="index" open="(" separator="," close=")" collection="list"> #{id} </foreach> </select> //测试: Logger logger = Logger.getLogger(CharpterMain.class); SqlSession sqlSession = null; try { sqlSession = SqlSessionFactoryUtils.openSqlSession(); RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); List<Long> list = new ArrayList<Long>(); list.add(1L); list.add(3L); List<Role> roles = roleMapper.findRolesByIds(list); logger.info(roles); logger.info(i); } catch (Exception e) { sqlSession.rollback(); e.printStackTrace(); } finally { SqlSessionFactoryUtils.closeSqlSession(sqlSession); }
-
test条件判断:如果要判断字符串是否相等,可以使用toString()方法:
<if test="roleName == 'Y'.toString()"> where 1=1 </if>
-
bind元素:如果使用到不同的数据库,那么sql语句就有可能不一样,比如说MySQL中的字符串拼接和Oracle就不一样,这样我们就可以先把字符串拼接好,再传入值,举例如下:
<select id="findRolesByCondition" parameterType="role" resultType="role"> <bind name="roleNamePattren" value="'%'+ roleName +'%'"/> <bind name="notePattren" value="'%'+ note +'%'"/> select id,roleName,note from role <where> <if test="roleName != null and roleName != ''"> and roleName like #{roleNamePattren} </if> <if test="note != null and note != ''"> and note like #{notePattren} </if> </where> </select>