Java MyBatis 映射单个对象查询, 映射多个对象查询, 模糊查询 搜索
1. 相关数据表
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`memo` varchar(2000) DEFAULT NULL,
`create_time` DATETIME NOT NULL,
`price` decimal(14,2) NOT NULL,
`seller_id` int(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `seller`;
CREATE TABLE `seller` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`memo` varchar(2000) DEFAULT NULL,
`create_time` DATETIME NOT NULL,
`phone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `order_goods`;
CREATE TABLE `order_goods` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`order_id` int(20) NOT NULL,
`goods_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
2. Seller.java, Order.java, OrderGoods.java
public class Seller {
private Integer id;
private String name;
private String memo;
protected String createTime;
private String phone;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getMemo() {
return memo;
}
public void setMemo(String memo) {
this.memo = memo;
}
public String getCreateTime() {
return createTime;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Seller{" +
"id=" + id +
", name='" + name + '\'' +
", memo='" + memo + '\'' +
", createTime=" + createTime +
", phone='" + phone + '\'' +
'}';
}
}
import java.math.BigDecimal;
import java.util.List;
public class Order {
private Long id;
private String name;
public String memo;
public String createTime;
public BigDecimal price;
public Seller seller;
private List<OrderGoods> orderGoodses;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getMemo() {
return memo;
}
public void setMemo(String memo) {
this.memo = memo;
}
public String getCreateTime() {
return createTime;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
public Seller getSeller() {
return seller;
}
public void setSeller(Seller seller) {
this.seller = seller;
}
public List<OrderGoods> getOrderGoodses() {
return orderGoodses;
}
public void setOrderGoodses(List<OrderGoods> orderGoodses) {
this.orderGoodses = orderGoodses;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", name='" + name + '\'' +
", memo='" + memo + '\'' +
", createTime='" + createTime + '\'' +
", price=" + price +
", seller=" + seller +
", orderGoodses=" + orderGoodses +
'}';
}
}
public class OrderGoods {
private Long id;
private Long orderId;
private String goodsName;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getOrderId() {
return orderId;
}
public void setOrderId(Long orderId) {
this.orderId = orderId;
}
public String getGoodsName() {
return goodsName;
}
public void setGoodsName(String goodsName) {
this.goodsName = goodsName;
}
@Override
public String toString() {
return "OrderGoods{" +
"id=" + id +
", orderId=" + orderId +
", goodsName='" + goodsName + '\'' +
'}';
}
}
3. OrderMapper.xml
<?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.test.web3.OrderMapper">
<!-- 将查询结果映射到指定字段! -->
<resultMap id="OrderResult" type="com.test.web3.entity.Order">
<result column="id" property="id" />
<result column="name" property="name" />
<result column="memo" property="memo" />
<result column="create_time" property="createTime" />
<result column="price" property="price" />
<!-- Order.java内部属性 seller, 通过参数 javaType指定类型 -->
<!-- association是用于映射单个对象的信息 -->
<association property="seller" javaType="com.test.web3.entity.Seller">
<id column="seller_id" property="id"/>
<result column="seller_name" property="name" />
<result column="seller_memo" property="memo" />
<result column="seller_create_time" property="createTime" />
<result column="phone" property="phone" />
</association>
<!-- collection是用于映射多个对象的信息 -->
<collection property="orderGoodses" ofType="com.test.web3.entity.OrderGoods">
<id column="order_goods_id" property="id"/>
<result column="id" property="orderId" />
<result column="order_goods_goods_name" property="goodsName" />
</collection>
</resultMap>
<select id="viewOrderById" resultMap="OrderResult" parameterType="java.util.Map">
SELECT `order`.`id`,`order`.`name`,`order`.`memo`,`order`.`create_time`,`order`.`price`,`order`.`seller_id`,
`seller`.`name` AS `seller_name`,`seller`.`memo` AS `seller_memo`,`seller`.`create_time` AS `seller_create_time`,`seller`.`phone`,
`order_goods`.`id` AS `order_goods_id`, `order_goods`.`goods_name` AS `order_goods_goods_name`
FROM
`order`
LEFT JOIN
`seller`
ON `order`.`seller_id` = `seller`.`id`
LEFT JOIN
`order_goods`
ON `order`.`id` = `order_goods`.`order_id`
WHERE
1
<if test="keywords !=null and keywords != ''">
AND CONCAT(`order`.`name`, `seller`.`name`) LIKE CONCAT ('%', #{keywords},'%')
</if>
ORDER BY `order`.`id` DESC, `order_goods`.`id` ASC
</select>
</mapper>
4. DAO(Data Access Object)数据访问接口 OrderDao.java和实现类 OrderDaoImpl.java
import com.test.web3.entity.Order;
import java.util.List;
public interface OrderDao {
List<Order> listByKeywords(String keywords);
}
import com.test.web3.dao.OrderDao;
import com.test.web3.entity.Order;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Repository
public class OrderDaoImpl implements OrderDao {
@Autowired
private SqlSession sqlSession;
static final String MAPPER = "com.test.web3.OrderMapper.";
public List<Order> listByKeywords(final String keywords) {
final Map<String, String> params = new HashMap<>();
params.put("keywords", keywords);
return sqlSession.selectList(MAPPER + "viewOrderById", params);
}
}
5. OrderController.java(入口)
@Controller
public class OrderController {
@Autowired
private OrderDao orderDao;
/**
* 搜索订单列表
* */
@RequestMapping(value = "/orderList", method = RequestMethod.GET)
public @ResponseBody Map<String, Object> orderList(@RequestParam(value = "keywords", defaultValue="") String keywords) {
/**
* 搜索订单列表
* */
final List<Order> order = orderDao.listByKeywords(keywords);
final Map<String, Object> result = new HashMap<>();
result.put("data", order);
return result;
}
}
6. 搜索结果
- http://127.0.0.1:8080/orderList?keywords=测试
{
"data": [{
"id": 10,
"name": "早餐超市",
"memo": "尽快发货!!",
"createTime": "2019-09-24 14:58:55.0",
"price": 10.58,
"seller": {
"id": 2,
"name": "测试商家",
"memo": "生意好!!",
"createTime": "2019-09-02 14:58:30.0",
"phone": "18812341234"
},
"orderGoodses": [{
"id": 4,
"orderId": 10,
"goodsName": "油条"
}, {
"id": 6,
"orderId": 10,
"goodsName": "馒头"
}, {
"id": 7,
"orderId": 10,
"goodsName": "包子"
}]
}, {
"id": 2,
"name": "晚餐超市",
"memo": "测试记录",
"createTime": "2019-09-24 17:48:15.0",
"price": 99.99,
"seller": {
"id": 4,
"name": "测试!",
"memo": "拉拉",
"createTime": "2019-09-23 17:47:47.0",
"phone": "18811112222"
},
"orderGoodses": [{
"id": 3,
"orderId": 2,
"goodsName": "麻花"
}, {
"id": 5,
"orderId": 2,
"goodsName": "豆浆"
}]
}]
}
如果您觉得有帮助,欢迎点赞哦 ~ 谢谢!!