public Order getOrder(int orderId) throws DataAccessException {
SQLQuery sqlQuery =
template
.newSqlQuery()
.from(qOrders)
.from(qOrderStatus)
.where(qOrders.orderid.eq(orderId).and(qOrders.orderid.eq(qOrderStatus.orderid)));
Order order =
template.queryForObject(
sqlQuery,
new MappingOrderProjection(
qOrders.billaddr1,
qOrders.billaddr2,
qOrders.billcity,
qOrders.billcountry,
qOrders.billstate,
qOrders.billtofirstname,
qOrders.billtolastname,
qOrders.billzip,
qOrders.shipaddr1,
qOrders.shipaddr2,
qOrders.shipcity,
qOrders.shipcountry,
qOrders.shipstate,
qOrders.shiptofirstname,
qOrders.shiptolastname,
qOrders.shipzip,
qOrders.cardtype,
qOrders.courier,
qOrders.creditcard,
qOrders.exprdate,
qOrders.locale,
qOrders.orderdate,
qOrders.orderid,
qOrders.totalprice,
qOrders.userid,
qOrderStatus.status));
if (order != null) {
SQLQuery lineItemsSqlQuery =
template.newSqlQuery().from(qLineItem).where(qLineItem.orderid.eq(orderId));
List<LineItem> lineItems =
template.query(
lineItemsSqlQuery,
new MappingLineItemProjection(
qLineItem.orderid,
qLineItem.linenum,
qLineItem.itemid,
qLineItem.quantity,
qLineItem.unitprice));
order.setLineItems(lineItems);
}
return order;
}
