Mybatis

 

 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">

<!-- 通过这个配置标签,完成mybatis与数据库的连接 -->
<configuration>
	<!-- 引入jdbc.properties文件 -->
	<properties resource="jdbc.properties"></properties>	
	
	<!-- 配置mybatis的log实现为LOG4J -->
	<settings>
		<setting name="logImpl" value="LOG4J"/>
		<!-- 设置全局缓存 -->
		<setting name="cacheEnabled" value="true"/>
	</settings>
	
	<!-- 引入与数据库表对应的javaBean的包 注意:别名为类名首字母大写 :User -->
	<typeAliases>
		<package name="com.pp.pojo"/>
	</typeAliases>
	
	
	<environments default="development">
		<environment id="development">
			<!-- 配置事务管理 采用jdbc -->
			<transactionManager type="JDBC"></transactionManager>
			<!-- POOLED是mybatis的数据源,jndi是基于tomcat的数据源 -->
			<dataSource type="POOLED">
				<property name="driver" value="${driver}"/>
				<property name="url" value="${url}?useUnicode=true&characterEncoding=utf8"/>
				<property name="username" value="${username}"/>
				<property name="password" value="${password}"/>
			</dataSource>
		</environment>
	</environments>
	<!-- 引入mapper.xml文件 -->
	<mappers>
		<mapper resource="com/pp/dao/UserMapper.xml"/>
		<mapper resource="com/pp/dao/RoleMapper.xml"/>
	</mappers>	
</configuration>

　　

连接数据库的配置文件: jdbc.properties(这个文件要引入mybatis配置文件中)

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/testdb
username=root
password=123

　　

Mapper配置文件:  UserMapper.xml(专门存储sql语句的配置文件)(这个文件要引入mybatis配置文件中)

<?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.pp.dao.UserMapper">


	<!--1. -->
	<!-- 因为返回一条数据 类型用int接收 -->
	<select id="count" resultType="int">
		select count(1) from user
	</select>

	
	<!-- 2. -->
	<!-- Bean中的Object属性用association标签表示 association标签中的result为role对象的属性 -->
	<!-- javaType为Role是因为别名   -->
	<resultMap type="User" id="userMap">
		<result property="id" column="id"/>
		<result property="userName" column="userName"/>
		<result property="userCode" column="userCode"/>
		<result property="userPassword" column="userPassword"/>
		<association property="role" javaType="Role">
			<result property="roleName" column="roleName"/>
		</association>
	</resultMap> -->
	

	<!--3. -->
	<!-- type为User 这个返回结果是User对象 property是属性 addressList是集合 ofType是集合中的对象,即泛型 -->
	<!-- 对象属性为集合 用collection标签表示 标签里面的是集合中对象(泛型对象)的属性 -->
	<resultMap type="User" id="getAddressByUserMap">
		<result property="id" column="userId"/>
		<collection property="addressList" ofType="Address">
			<id property="id" column="a_id"/>
			<result property="postCode" column="postCode"/>
			<result property="addressContent" column="addressContent"/>
		</collection>
		
	</resultMap>
	<!--  resultMap对应标签,标签 -->
	<!-- parameterType为调用方法时的参数类型  -->
	<select id="getAddressByUser" resultMap="getAddressByUserMap" parameterType="User">
		select *,a.id as a_id from user u,address a where u.id=a.userId and u.id=#{id} 
	</select>


</mapper>

　　

test类(junit)  增删改需要sqlSession.commit();提交事务

private Logger logger=Logger.getLogger(this.getClass());
	@Test
	public void conTest(){

		String resources="mybatis-config.xml";
		int count = 0;
		SqlSession sqlSession=null;
		InputStream is=null;
		
		try {
			//1.读取配置文件输入流
			is=Resources.getResourceAsStream(resources);

			//2.创建sqlSessionFactory对象,完成对配置文件的读取
			SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);

			//3.创建sqlSessin对象
			sqlSession=sqlSessionFactory.openSession();

			//4.调用mapper.xml文件进行数据操作
			count=sqlSession.selectOne("com.pp.dao.UserMapper.count");
			
			//测试输出
			logger.debug(count);

			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			sqlSession.close();
			try {
				is.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
		Assert.assertEquals(5, count);
		
	}
	
		

 

mybatis特性之一
动态sql

涉及标签:
if
choose when otherwise
trim where set
foreach

1.
<select id="getRoleList"  resultType="Role" parameterType="Role">
	select * from role	
	<where>
		<choose>
			<when test="roleCode!=null">
				and roleCode like CONCAT('%',#{roleCode},'%') 
			</when>
			<when test="roleName!=null">
				and roleName like CONCAT('%',#{roleName},'%')
			</when>
			<otherwise></otherwise>
		</choose>
	</where>
</select>


//where 标签相同sql语句where 智能省略第一个判断的and,就不用加 1=1 这样的语句
//choose 标签相同switch	 
//when 标签相同case   当满足一个条件时,追加sql语句后,跳出choose
//otherwise 标签相同switch中的default   当所有条件都不满足时,执行此标签内的语句


2.
<where>
	<if test="roleCode!=null">
		and roleCode like CONCAT('%',#{roleCode},'%') 
	</if>
	<if test="roleName!=null">
		and roleName like CONCAT('%',#{roleName},'%')
	</if>

</where>

//if标签 判断如满足条件则追加语句


3.
<trim prefix="where" prefixOverrides="and|or">
	<when test="roleCode!=null">
		and roleCode like CONCAT('%',#{roleCode},'%') 
	</when>
	<when test="roleName!=null">
		and roleName like CONCAT('%',(#roleName),'%')
	</when>
</trim>

//trim标签 追加where关键字 间隔符是and 或者 or   trim也会在满足第一个条件时,舍去其语句中and



4.
<update id="updateRole" parameterType="Role">
	update role
	<set>
		<if test="roleCode!=null and roleCode!=''">
				roleCode=#{roleCode},
		</if>
		<if test="roleName!=null">
			roleName=#{roleName}
		</if>
	</set>
	where id=#{id}
</update>

//update 也有set标签  添加set关键字 后面判断条件追加语句



5.
<update id="updateRole" parameterType="Role">
	update role
	<trim prefix="set" prefixOverrides=",">
		<if test="roleCode!=null and roleCode!=''">
			roleCode=#{roleCode},
		</if>
		<if test="roleName!=null">
			roleName=#{roleName}
		</if>
	</trim>
	where id=#{id}
</update>


//trim标签 前缀为set 意为追加set关键字 prefixOverrides值,  间隔符号为,



6.
<resultMap type="User" id="userMapBydepId">
	<result property="id" column="id"/>
	<result property="userCode" column="userCode"/>
	<result property="userName" column="userName"/>
</resultMap> 
<!-- 传入的参数如果是数组 key:array 集合 key:list-->
<select id="getUserBydepIds" resultMap="userMapBydepId" >
	select * from user where depId in
	<foreach collection="array" item="depIds" open="(" separator="," close=")">
		#{depIds}
	</foreach>
			
</select>
	
//select * from user where depId in(1,2)  查询表user字段depId为1和2的数据
//数组collection为array
//open,separator,close 定义in后面的格式 :(?,?)
//不需要写parameterType

<select id="getUserBydepIds" resultMap="userMapBydepId" >
	select * from user where depId in
	<foreach collection="list" item="depIds" open="(" separator="," close=")">
		#{depIds}
	</foreach>
			
</select>

//集合collection为list

　　

关于缓存
一级缓存 session作用域
二级缓存 全局作用域

全局缓存
mybatis配置文件:

<settings>
     <setting name="cacheEnabled" value="true"/>
</settings>    

　　

 

开一级缓存的话 一定要开全局缓存 不生效
在mapper.xml中设置缓存 默认不开启

<cache eviction="FIFO" flushInterVal="60000" size="512" readOnly="true"/>

　　

 

转载于:https://www.cnblogs.com/m97i/p/7599319.html