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;}}

测试类：

@Testpublic 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,lastLoginTimeFROM 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>