输入映射和输出映射
parameterType(输入类型)
1.传递简单类型
<select id="findUserById" parameterType="int"
resultType="cn.nanjin.po.User">
SELECT * FROM user WHERE id = #{NanJin}
</select>
2.传递pojo类型
<update id="updateUser" parameterType="cn.nanjin.po.User">
UPDATE user set username=#{username},address=#{address} WHERE id =#{id}
</update>
3.传递pojo包装类型
public class QueryVo{
private User u;
private Product p;
get /set
}
4.传递map类型
resultType类型
这里需要注意一点,比如查询 select username as u from user
1.简单类型
@Test
public void findcount(){
SqlSession session = sqlSessionFactory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
int findcount = mapper.findcount();
System.out.println(findcount);
}
<select id="findcount" resultType="int">
select count(1) from user
</select>
2.pojo类型
<select id="findUserByUserName" parameterType="string" resultType="cn.nanjin.po.User">
select * from user where username like '%${value}%'
</select>
3.resultMap
需求:映射下面的sql结果SELECT id id_,username username_,birthday birthday_ FROM user
mapper接口:
List<User> findUserByResultMap();
mapper.xml
<!-- 如果返回是map,那我们需要定义一个resultMap,将查询出来的列名和映射的pojo属性名对应
type:你要把查询结果映射到哪个pojo
id:唯一标识
-->
<resultMap type="user" id="remap">
<!-- id标签 主键列
column 查询sql的列名
property 映射到pojo的哪个属性名 -->
<id column="id_" property="id"/>
<!-- result 表示普通列 -->
<result column="username_" property="username"/>
<result column="birthday_" property="birthday"/>
</resultMap>
<select id="findUserByResultMap" resultMap="remap">
SELECT id id_,username username_,birthday birthday_ FROM user
</select>
测试类
@Test
public void findUserByResultMap(){
SqlSession session = sqlSessionFactory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> list = mapper.findUserByResultMap();
System.out.println(list);
}
总结
resultType和resultMap都可以进行结果映射
区别如下:
查询列名和属性名一致,则使用resultType更方便,
查询列名属性名不一致,使用resultMap,
如果使用延迟加载 必须使用resultMap。
动态sql
if标签
<select id="findusername" parameterType="user" resultType="user">
select * from user where 1=1
<if test="username!=null and username!=''">
and username like '%${username}%'
</if>
</select>
@Test
public void findUserByResultMap(){
SqlSession session = sqlSessionFactory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User u =new User();
u.setUsername("小明");
List<User> list = mapper.findusername(u);
System.out.println(list);
}
这里看到我们使用了if标签判断,test里面直接可以写表达式,如果符合条件的话会拼接里面的sql语句,因为if里面的语句会默认的加上and
where标签
<select id="findusername" parameterType="user" resultType="user">
select * from user
<where>
<if test="username!=null and username!=''">
and username like '%${username}%'
</if>
</where>
where会自动处理第一个and, 需要注意的是,如果肚子里if条件不满足,会直接执行select * from user ,where 会抛弃自己和肚子里的if
sql片段
<sql id="findids">
<if test="username!=null and username!=''">
and username like '%${username}%'
</if>
</sql>
引用此片段
<select id="findusername" parameterType="user" resultType="user">
select * from user
<where>
<include refid="findids"></include>
</where>
foreach标签
综合查询时,传入多个id查询用户信息,用下边两个sql实现:
SELECT * FROM USER WHEREusername LIKE '%小明%' AND(id =1 OR id =10 OR id=16)
SELECT * FROM USER WHEREusername LIKE '%小明%' AND id IN (1,10,16)
private List<Integer> ids;
get/set
<select id="findusername" parameterType="user" resultType="user">
select * from user
<where>
<include refid="findids"></include>
</where>
</select>
<sql id="findids">
<if test="username!=null and username!=''">
and username like '%${username}%'
</if>
<if test="ids!=null and ids.size()>0">
and id in
<foreach collection="ids" item="id" open="(" close=")" separator","=>
#{id}
</foreach>
</if>
</sql>
这里我们关注一下foreach的标签属性,第一个collection集合,这代表,你引用处 paramType里面的什么集合,我们这里是user里面的ids,所以就写ids ,item代表每次循环出来的变量,open代表执行循环第一次时先加个 “(” close代表循环结束加个“)” 注意是从循环开始到结束只执行一次!
separator为,
拼接语句 and id in (10,
第一次循环完毕,再次循环public void findUserByResultMap(){
SqlSession session = sqlSessionFactory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User u =new User();
List<Integer> ids =new ArrayList<>();
ids.add(10);
ids.add(24);
ids.add(25);
u.setIds(ids);
List<User> list = mapper.findusername(u);
System.out.println(list);
}
SELECT * FROM USER WHEREusername LIKE '%小明%' AND(id =1 OR id =10 OR id=16)
<sql id="findids">
<if test="username!=null and username!=''">
and username like '%${username}%'
</if>
<if test="ids!=null and ids.size()>0">
<foreach collection="ids " item="id" open="and (id=" close=")" separator="or id =">
#{id}
</foreach>
</if>
</sql>
分析,
循环一次结束执行separator
关联查询
注意:因为一个订单信息只会是一个人下的订单,所以从查询订单信息出发,关联查询用户信息为一对一查询。如果从用户信息出发,查询用户下的订单信息则为一对多查询,因为一个用户可以下多个订单。
一对一查询(订单为主)
public class OrdersExt extends Orders {
// 用户名称
private String username;
// 用户地址
private String address;
get/set
@Override
public String toString() {
return "OrdersExt [username=" + username + ", address=" + address + ", getId()=" + getId() + ", getUserId()="
+ getUserId() + ", getNumber()=" + getNumber() + ", getCreatetime()=" + getCreatetime() + ", getNote()="
+ getNote() + "]";
}
}
mapper.xml
<select id="findorderext" resultType="ordersext">
select orders.*,user.username,user.address from orders LEFT JOIN user on orders.user_id = `user`.id
</select>
terst
SqlSession session = sqlSessionFactory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List<OrdersExt> findorderext = mapper.findorderext();
System.out.println(findorderext);
小结:
1、一对一关联查询时,可以定义专门的扩展po类作为输出结果类型。
2、该扩展po类中定义了sql查询结果集所有的字段对应的属性。
3、此方法较为简单,企业中使用普遍。
resultMap
需要在扩展po加入 private User user; 因为是一对一,所以只需要单个。
查询结果
<resultMap type="ordersext" id="orders">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<association property="user" javaType="cn.nanjin.po.User">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="findorderext" resultMap="orders">
select orders.*,user.username,user.address from orders LEFT JOIN user on orders.user_id = `user`.id
</select>
我在这里先写的是select标签,然后在写resultMap,直接说下resultMap分析思路,
首先封装外层order数据,id主键和result其他字段,
<association>标签代表一对一关系映射
property,代表po类里面的哪个属性
javaType是指定该po属性到底是哪个po
里面id主键和result其他属性封装。
小结:
使用resultMap进行结果映射时,具体是使用association完成关联查询的映射,将关联查询信息映射到pojo对象中。
一对多查询(用户为主)
private List<Orders> orders;
sql语句: select user.*,orders.number from orders RIGHT JOIN user on orders.user_id = `user`.id
<resultMap type="user" id="myorders">
<id column="id" property="id"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<collection property="orders" ofType="orders">
<id property="id" column="oid"/>
<result property="userId" column="id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
</collection>
</resultMap>
<select id="findOrders" resultMap="myorders">
select user.*,orders.number from orders RIGHT JOIN user on orders.user_id = `user`.id
</select>
public void findUserByResultMap(){
SqlSession session = sqlSessionFactory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> findOrders = mapper.findOrders();
System.out.println(findOrders);
}
小结:
虽然resultMap和resultType都可以进行结果映射,但是使用resultType更加便捷,企业中建议使用resultType完成结果映射。
ResultMap只有特殊场景才会用,比如延迟加载。