学习内容:学习MyBatis框架(Day56)
1、高级查询
2、分页插件
3、Mybatis使用注解
1、高级查询
(1)Mybatis作为一个ORM框架,也对SQL的高级查询做了支持,即Mybatis下的一对一、一对多、多对多的查询。
一对一查询:查询订单,并且查询出下单人的信息,同时要把两个信息放在一块去。
方法1.需要扩展Order对象,来映射结果集。(把两个信息放到一块去)创建第三个实体类,即订单信息也有用户信息。
OrderUser.java
java">@Data
@AllArgsConstructor
@NoArgsConstructor
public class OrderUser extends Order{
private String name;
@Override
public String toString() {
return "OrderUser{" +
"userId=" + getUserId() +
",name=" + name +
",orderId=" + getId() +
",order_number=" + getOrderNumber() +
'}';
}
}
mapper接口
java">public interface UserMapper {
List<OrderUser> queryOrderUserByOrderNumber(@Param("orderNumber") String orderNumber);
}
mapper.xml文件
<select id="queryOrderUserByOrderNumber" resultType="OrderUser">
SELECT
*
FROM
tb_order
LEFT JOIN tb_user ON tb_order.user_id = tb_user.id
WHERE
tb_order.order_number = #{orderNumber}
</select>
测试
java">@Test
public void queryOrderUserByOrderNumber(){
List<OrderUser> orderUsers = userMapper.queryOrderUserByOrderNumber("20140921002");
orderUsers.forEach(System.out::println);
}
方法2.面向对象的思想,在Order对象中添加User对象。
Order.java
java">@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Order {
private Integer id;
private Long userId;
private String orderNumber;
private User user;
}
mapper接口
java">Order queryOrderWithUserByOrderNumber(@Param("orderNumber") String orderNumber);
mapper.xml,使用association元素完成子对象的映射
<resultMap id="orderMap" type="Order" autoMapping="true">
<id column="id" property="id"/>
<!-- association:完成子对象的映射,property :表示子对象的属性名
javaType :指定对象的数据类型,autoMapping="true"完成子对象的属性的自动映射-->
<association property="user" javaType="User" autoMapping="true">
<!--association中的元素参考resultMap的子标签-->
<id column="user_id" property="id"/>
<result column="user_name" property="userName"/>
</association>
</resultMap>
<select id="queryOrderWithUserByOrderNumber" resultMap="orderMap">
SELECT
*
FROM
tb_order
LEFT JOIN tb_user ON tb_order.user_id = tb_user.id
WHERE
tb_order.order_number = #{orderNumber}
</select>
测试
java">@Test
public void queryOrderWithUserByOrderNumber(){
Order order = userMapper.queryOrderWithUserByOrderNumber("20140921002");
System.out.println("order = " + order);
}
(2)一对多查询:查询订单,查询出下单人信息并且查询出订单详情,一个定单可以有多个定单详情。
在Order实体类中添加orderDetails集合变量
java">@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Order {
private Integer id;
private Long userId;
private String orderNumber;
private User user;
List<OrderDetail> orderDetails;
}
mapper接口
java">Order queryOrderWithUserAndOrderDetailByOrderNumber(@Param("orderNumber") String orderNumber);
mapper.xml,使用collection标签完成集合的映射
<resultMap id="orderMap2" type="Order" autoMapping="true">
<id column="id" property="id"/>
<!-- association:完成子对象的映射,property :表示子对象的属性名
javaType:指定对象的数据类型,autoMapping="true"完成子对象的属性的自动映射-->
<association property="user" javaType="User" autoMapping="true">
<!--association中的元素参考resultMap的子标签-->
<id column="user_id" property="id"/>
<result column="user_name" property="userName"/>
</association>
<!--collection完成集合的映射,property :表示集合的变量名
javaType:集合类型,ofType:集合中保存对象的数据类型-->
<collection property="orderDetails" javaType="list" ofType="orderDetail" autoMapping="true">
<id column="detail_id" property="id"/>
</collection>
</resultMap>
<select id="queryOrderWithUserAndOrderDetailByOrderNumber" resultMap="orderMap2">
SELECT
*,d.id AS detail_id
FROM
tb_order AS o
LEFT JOIN tb_user AS u ON o.user_id = u.id
LEFT JOIN tb_orderdetail AS d ON d.order_id = o.id
WHERE
o.order_number = #{orderNumber};
</select>
测试
java">@Test
public void queryOrderWithUserAndOrderDetailByOrderNumber(){
Order order = userMapper.queryOrderWithUserAndOrderDetailByOrderNumber("20140921002");
System.out.println("order = " + order);
}
(3)一对多+一对一查询:查询订单,查询出下单人信息并且查询出订单详情中的商品数据。
在OrderDetail实体类中添加Item对象
java">@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class OrderDetail {
private int id;
private int orderId;
private int itemId;
private double totalPrice;
private int status;
private Item item;
}
mapper接口,在collection元素中再添加一个association元素
<resultMap id="orderMap3" type="Order" autoMapping="true">
<id column="id" property="id"/>
<association property="user" javaType="User" autoMapping="true">
<id column="user_id" property="id"/>
<result column="user_name" property="userName"/>
</association>
<collection property="orderDetails" javaType="list" ofType="orderDetail" autoMapping="true">
<id column="detail_id" property="id"/>
<!--在collection元素中再添加一个association元素-->
<association property="item" javaType="Item" autoMapping="true">
<id column="item_id" property="id"/>
</association>
</collection>
</resultMap>
<select id="queryOrderWithUserAndOrderDetailAndItemByOrderNumber" resultMap="orderMap3">
SELECT*,d.id AS detail_id
FROM
tb_order AS o
LEFT JOIN tb_user AS u ON o.user_id = u.id
LEFT JOIN tb_orderdetail AS d ON d.order_id= o.id
LEFT JOIN tb_item AS i ON i.id = d.item_id
WHERE
o.order_number = #{orderNumber};
</select>
测试
java">@Test
public void queryOrderWithUserAndOrderDetailAndItemByOrderNumber(){
Order order = userMapper.queryOrderWithUserAndOrderDetailAndItemByOrderNumber("20140921001");
System.out.println("order = " + order);
//可以单独输出order对象中的子对象和集合
User user = order.getUser();
System.out.println("user = " + user);
List<OrderDetail> orderDetails = order.getOrderDetails();
for (OrderDetail orderDetail:orderDetails) {
Item item = orderDetail.getItem();
System.out.println("item = " + item);
}
}
(4)resultMap的继承,使用extends属性,值为继承的resultMap的id,可以重写继承的resultMap的子标签。
<resultMap id="orderMap2" type="Order" autoMapping="true">
<id column="id" property="id"/>
<!-- association:完成子对象的映射,property :表示子对象的属性名
javaType :指定对象的数据类型,autoMapping="true"完成子对象的属性的自动映射-->
<association property="user" javaType="User" autoMapping="true">
<!--association中的元素参考resultMap的子标签-->
<id column="user_id" property="id"/>
<result column="user_name" property="userName"/>
</association>
<!--collection完成集合的映射,property :表示集合的变量名
javaType:集合类型,ofType:集合中保存对象的数据类型-->
<collection property="orderDetails" javaType="list" ofType="orderDetail" autoMapping="true">
<id column="detail_id" property="id"/>
</collection>
</resultMap>
<resultMap id="orderMap3" type="Order" autoMapping="true" extends="orderMap2">
<!--collection完成集合的映射,property :表示集合的变量名
javaType:集合类型,ofType:集合中保存对象的数据类型-->
<collection property="orderDetails" javaType="list" ofType="orderDetail" autoMapping="true">
<id column="detail_id" property="id"/>
<association property="item" javaType="Item" autoMapping="true">
<id column="item_id" property="id"/>
</association>
</collection>
</resultMap>
1、分页插件
(1)Mybatis提供了plugin机制,允许我们在Mybatis的原有处理流程上加入自己逻辑,所有我们就可以使用这种逻辑加上我们的分页逻辑,也就是实现拦截器。
Mybatis支持的拦截的接口有4个,Executor 、 ParameterHandler 、ResultSetHandler、StatementHandler。
PageHelper实现了通用的分页查询,其支持的数据有,mysql、 Oracle、DB2、PostgreSQL等主流的数据库。该插件托管于github: https://github.com/pagehelper/Mybatis-PageHelper
在pom.xml导入依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
在mybatis-config.xml文件配置插件
<!--配置插件,这个拦截器插件放在配置环境的上面-->
<plugins>
<!--引入PageHelper类-->
<plugin interceptor="com.github.pagehelper.PageHelper">
<!--数据库方言-->
<property name="dialect" value="mysql"/>
<!--设置为true时,使用rowBounds分页会进行count查询,会查询出总数-->
<property name="rowBoundsWithCount" value="true"/>
</plugin>
</plugins>
mapper接口
java">List<User> queryAllUsers();
mapper.xml
<select id="queryAllUsers" resultType="User">
select * from tb_user
</select>
测试,在执行查询时设置分页参数
java">@Test
public void queryAllUsers(){
//执行查询时设置分页参数,startPage(第一个参数:第几页,第二个参数:显示多少条数据);
PageHelper.startPage(2,3);
List<User> users = userMapper.queryAllUsers();
users.forEach(System.out::println);//具体信息
System.out.println("users = " + users);//分页的所有信息
//PageInfo获取分页的信息
PageInfo<User> pageInfo = new PageInfo<User>(users);
System.out.println("当前页:" + pageInfo.getPageNum());
System.out.println("当前页显示多少条数据:" + pageInfo.getPageSize());
System.out.println("分页的所有信息:" + pageInfo.getList());//pageInfo.getList()与users集合相同
System.out.println("总条数:" + pageInfo.getTotal());
System.out.println("总页数:" + pageInfo.getPages());
System.out.println("具体信息:");
pageInfo.getList().forEach(System.out::println);
}
3、Mybatis使用注解
(1)如果使用注解,mapper.xml映射文件可以不用创建
mapper接口
java">public interface ItemMapper {
@Select("select * from tb_item")
List<Item> queryAllItem();
@Insert("insert into tb_item(id,item_name,item_price,item_detail) values(null,#{itemName},#{itemPrice},#{itemDetail})")
void addItem(Item item);
@Delete("delete from tb_item where id = #{id}")
void delete(int id);
@Update("update tb_item set item_name = #{itemName}, item_price = #{itemPrice}, item_detail = #{itemDetail} where id = #{id}")
void updateItem(Item item);
}
测试
java">public class ItemMapperTest {
private SqlSession sqlSession;
private ItemMapper itemMapper;
@Before
public void setUp() throws Exception {
//获取mybatis配置文件
String resource = "mybatis-config.xml";
//将配置文件读取为输入流
InputStream inputStream = Resources.getResourceAsStream(resource);
//从 XML 配置文件或一个预先配置的 Configuration 实例来构建出 SqlSessionFactory 实例
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//打开sqlSession,通过sqlSession进行增删改查
sqlSession = sqlSessionFactory.openSession();
//userMapper = new UserMapperImpl(sqlSession);
//使用动态代理得到实现类对象
itemMapper = sqlSession.getMapper(ItemMapper.class);
}
@Test
public void queryAllItem() {
List<Item> items = itemMapper.queryAllItem();
items.forEach(System.out::println);
}
@Test
public void addItem(){
Item item = new Item(3,"iPhone X",8888,"新产品");
itemMapper.addItem(item);
sqlSession.commit();
}
@Test
public void delete(){
itemMapper.delete(3);
sqlSession.commit();
}
@Test
public void updateItem(){
Item item = new Item(4,"华为",1888,"支持国产");
itemMapper.updateItem(item);
sqlSession.commit();
}
}
(2)一些其它的注解