mybatis详解(二)

一、动态SQL之if标签：

	<!-- 动态SQL语句 -->
	<select id="findUserByNameAndIdNumber" parameterType="com.milan.entity.User" resultType="com.milan.entity.User">
	select * from t_user
		<where>
			<if test="username != null and username != ''">
				and username like '%${username}%'
			</if>
			<if test="idNumber != null and idNumber != ''">
				and idNumber = #{idNumber}
			</if>
		</where>
	</select>

可以将where条件封装到sql标签中，以备其他select标签重用

	<!-- 动态SQL语句 -->
	<select id="findUserByNameAndIdNumber" parameterType="com.milan.entity.User" resultType="com.milan.entity.User">
	select * from t_user
		<include refid="user_where"></include>
	</select>
	
	<!-- 封装SQL条件，以备重用 -->
	<sql id="user_where">
		<where>
			<if test="username != null and username != ''">
				and username like '%${username}%'
			</if>
			<if test="idNumber != null and idNumber != ''">
				and idNumber = #{idNumber}
			</if>
		</where>
	</sql>

二、动态SQL之foreach标签：

	<!-- foreach标签：主要应用于sql中的in条件
		collection：对应parameterType中的参数
		item：字段名
		open：条件前缀
		close：条件后缀
		separator：每个参数的分隔符
	 -->
	<select id="findUserByIds" parameterType="com.milan.entity.Parameters" resultType="com.milan.entity.User">
		select * from t_user
		<where>
			<foreach collection="parameters" item="id" open="id in(" close=")" separator=",">
				#{id}
			</foreach>
		</where>
	</select>

对应的Parameters类：

/**
 * 参数类
 * @author Clark
 */
public class Parameters {
	
	private String parameter;
	private List<Integer> parameters;
	public String getParameter() {
		return parameter;
	}
	public void setParameter(String parameter) {
		this.parameter = parameter;
	}
	public List<Integer> getParameters() {
		return parameters;
	}
	public void setParameters(List<Integer> parameters) {
		this.parameters = parameters;
	}	
}

测试类：

	@Test
	public void getUserByIds() throws Exception{
		String resource ="SqlMapConfig.xml";  
        InputStream inputStream = Resources.getResourceAsStream(resource);  
        //创建SqlSessionFactory  
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);  
        //创建SqlSession  
        SqlSession sqlSession = factory.openSession();  
        //调用SqlSession的增删改查方法  
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		Parameters parameters = new Parameters();
		List<Integer> i = new ArrayList<>();
		i.add(1);
		i.add(3);
		parameters.setParameters(i);
		List<User> userlist = mapper.findUserByIds(parameters);
		System.out.println(userlist);
		sqlSession.close();
	}

三、单个对象映射关系：

	<resultMap type="com.milan.entity.UserOrder" id="orderAndUserResultMap">
		<id column="id" property="id" />
		<result column="userId" property="userId" />
		<result column="goodId" property="goodId" />
		<result column="goodNumber" property="goodNumber" />
		<result column="discount" property="discount" />
		<result column="createTime" property="createTime" />
		<result column="describe" property="describe" />
		<!-- association:表示单个对象的对应关系
			property:表示UserOrder中的User属性
			javaType：表示User的属性类型
		 -->
		<association property="user" javaType="com.milan.entity.User">
			<id column="uid" property="id" />
			<result column="username" property="username" />
			<result column="password" property="password" />
			<result column="idNumber" property="idNumber" />
			<result column="vip" property="vip" />
			<result column="score" property="score" />
			<result column="ucreateTime" property="createTime" />
			<result column="lastLoginTime" property="lastLoginTime" />
		</association>
	</resultMap>
	
	<!-- 手动映射 -->
	<select id="findOrderAndUser" resultMap="orderAndUserResultMap">
		SELECT o.*, u.id AS uid,username,password,idNumber,vip,score,u.createTime AS ucreateTime,lastLoginTime
			FROM t_userOrder o,t_user u WHERE o.userId = u.id;
	</select>

四：集合对象映射：

		<resultMap type="com.milan.entity.User" id="UserAndOrderResultMap">
		<id column="id" property="id" />
		<result column="username" property="username" />
		<result column="password" property="password" />
		<result column="idNumber" property="idNumber" />
		<result column="vip" property="vip" />
		<result column="score" property="score" />
		<result column="ucreateTime" property="createTime" />
		<result column="lastLoginTime" property="lastLoginTime" />
		<!-- 指定集合对象关系映射 -->
		<collection property="userOrder" ofType="com.milan.entity.UserOrder">
			<id column="oid" property="id" />
			<result column="userId" property="userId" />
			<result column="goodId" property="goodId" />
			<result column="goodNumber" property="goodNumber" />
			<result column="discount" property="discount" />
			<result column="ocreateTime" property="createTime" />
			<result column="describe" property="describe" />
		</collection>
	</resultMap>
	<select id="findUserAndOrder" resultMap="UserAndOrderResultMap">
		SELECT u.*,o.id as oid,userId,goodId,goodNumber,discount,o.createTime as ocreateTime,`describe` 
		from t_user as u,t_userOrder as o where u.id = o.userId
	</select>