Below is my HSQL statement,(it is a simplified version) :
Code:
StringBuffer stmt = new StringBuffer();
stmt.append("select i.order.address.county, i.order.address.township, i.origionalOrderItem.product.ean, sum(i.deliveryQuantity) ");
stmt.append("from kk.DeliveryOderItem as i where i.order.deprecated=[b]kk.options.BooleanEnum.FALSE [/b]");
stmt.append("group by i.order.address.county, i.order.address.township, i.origionalOrderItem.product.ean ");
stmt.append("order by i.order.address.county, i.order.address.township ");
The problem is stressed by bold keywords. HSQL parser misunderstood the package name as SQL alias name. BooleanEnum is a implementation of
PersistentEnum. Please see the following for extra info. Thanks for your help
Hibernate version:2.1.4
Mapping documents:Hope it won't crash your database :)
AOrder.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class
name="kk.AOrder"
table="AOrder"
proxy="kk.AOrder"
dynamic-update="false"
dynamic-insert="false"
discriminator-value="0"
>
<id
name="id"
column="id"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="increment">
</generator>
</id>
<discriminator
column="discriminator"
type="char"
length="1"
/>
<version
name="version"
type="long"
column="version"
/>
<list
name="items"
lazy="true"
inverse="false"
cascade="all"
>
<key
column="order_id"
/>
<index
column="itemIndex"
/>
<one-to-many
class="kk.AOrderItem"
/>
</list>
<property
name="printPrice"
type="kk.options.BooleanEnum"
update="true"
insert="true"
column="printPrice"
/>
<property
name="allowBackOrder"
type="kk.options.BooleanEnum"
update="true"
insert="true"
column="allowBackOrder"
/>
<many-to-one
name="customer"
class="kk.Customer"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="customer"
/>
<property
name="deliverDate"
type="java.util.Date"
update="true"
insert="true"
column="deliverDate"
/>
<property
name="deliveryMode"
type="kk.options.DeliveryModeEnum"
update="true"
insert="true"
column="deliveryMode"
/>
<property
name="emergency"
type="kk.options.BooleanEnum"
update="true"
insert="true"
column="emergency"
/>
<property
name="expectedDeliverTime"
type="kk.options.ExpectedDeliverTimeEnum"
update="true"
insert="true"
column="expectedDeliverTime"
/>
<property
name="orderSource"
type="kk.options.OrderSourceEnum"
update="true"
insert="true"
column="orderSource"
/>
<property
name="otherDeliveryMode"
type="java.lang.String"
update="true"
insert="true"
column="otherDeliveryMode"
/>
<property
name="otherOrderSource"
type="java.lang.String"
update="true"
insert="true"
column="otherOrderSource"
/>
<property
name="customerName"
type="java.lang.String"
update="true"
insert="true"
column="customerName"
length="200"
/>
<property
name="receiver"
type="java.lang.String"
update="true"
insert="true"
column="receiver"
/>
<property
name="receiverPhone"
type="java.lang.String"
update="true"
insert="true"
column="receiverPhone"
length="20"
/>
<property
name="receiverMobile"
type="java.lang.String"
update="true"
insert="true"
column="receiverMobile"
length="20"
/>
<many-to-one
name="salesman"
class="kk.Salesman"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="salesman"
/>
<property
name="specialNotice"
type="java.lang.String"
update="true"
insert="true"
column="specialNotice"
/>
<component
name="orderComponent"
class="kk.components.OrderComponent"
>
<property
name="number"
type="java.lang.String"
update="true"
insert="true"
column="number"
length="14"
/>
<many-to-one
name="organization"
class="kk.Organization"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="organization"
/>
<property
name="tax"
type="java.math.BigDecimal"
update="true"
insert="true"
column="tax"
length="0"
/>
<property
name="totalAfterTax"
type="java.math.BigDecimal"
update="true"
insert="true"
column="totalAfterTax"
length="0"
/>
<property
name="totalBeforeTax"
type="java.math.BigDecimal"
update="true"
insert="true"
column="totalBeforeTax"
length="0"
/>
<many-to-one
name="warehouse"
class="kk.Warehouse"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="warehouse"
/>
</component>
<component
name="address"
class="kk.components.AddressComponent"
>
<property
name="alley"
type="java.lang.String"
update="true"
insert="true"
column="alley"
length="100"
/>
<property
name="county"
type="java.lang.String"
update="true"
insert="true"
column="county"
/>
<property
name="lane"
type="java.lang.String"
update="true"
insert="true"
column="lane"
length="100"
/>
<property
name="num"
type="java.lang.String"
update="true"
insert="true"
column="num"
length="100"
/>
<property
name="road"
type="java.lang.String"
update="true"
insert="true"
column="road"
length="100"
/>
<property
name="section"
type="java.lang.String"
update="true"
insert="true"
column="section"
/>
<property
name="township"
type="java.lang.String"
update="true"
insert="true"
column="township"
/>
<property
name="dashOfFloor"
type="java.lang.String"
update="true"
insert="true"
column="dashOfFloor"
/>
<property
name="dashOfNum"
type="java.lang.String"
update="true"
insert="true"
column="dashOfNum"
/>
<property
name="floor"
type="java.lang.String"
update="true"
insert="true"
column="floor"
/>
<property
name="room"
type="java.lang.String"
update="true"
insert="true"
column="room"
/>
<property
name="freeInput"
type="java.lang.String"
update="true"
insert="true"
column="freeInput"
length="200"
/>
</component>
<property
name="type"
type="java.lang.String"
update="false"
insert="false"
column="discriminator"
length="1"
/>
<many-to-one
name="retrievedCustomer"
class="kk.Customer"
cascade="none"
outer-join="auto"
update="false"
insert="false"
column="customer"
/>
<property
name="transactionCode"
type="java.lang.String"
update="true"
insert="true"
column="transactionCode"
length="36"
/>
<property
name="currentState"
type="java.lang.String"
update="true"
insert="true"
column="currentState"
length="10"
/>
<property
name="toState"
type="java.lang.String"
update="true"
insert="true"
column="toState"
length="10"
/>
<property
name="remark"
type="java.lang.String"
update="true"
insert="true"
column="remark"
length="1000"
/>
<property
name="createDate"
type="java.util.Calendar"
update="true"
insert="true"
column="createDate"
/>
<many-to-one
name="createEmployee"
class="kk.Employee"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="createEmployee"
/>
<property
name="updateDate"
type="java.util.Calendar"
update="true"
insert="true"
column="updateDate"
/>
<many-to-one
name="updateEmployee"
class="kk.Employee"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="updateEmployee"
/>
<property
name="deprecated"
type="kk.options.BooleanEnum"
update="true"
insert="true"
column="deprecated"
/>
<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-AOrder.xml
containing the additional properties and place it in your merge dir.
-->
<subclass
name="kk.SalesOrder"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.SalesOrder"
discriminator-value="1"
>
</subclass>
<subclass
name="kk.GiftOrder"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.GiftOrder"
discriminator-value="2"
>
</subclass>
<subclass
name="kk.PatchOrder"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.PatchOrder"
discriminator-value="4"
>
<many-to-one
name="origionalDeliveryOrder"
class="kk.AOrder"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="origionalDeliveryOrder"
/>
</subclass>
<subclass
name="kk.MoveOrder"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.MoveOrder"
discriminator-value="5"
>
<many-to-one
name="toWarehouse"
class="kk.Warehouse"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="toWarehouse"
/>
</subclass>
<subclass
name="kk.AbstractDeliveryOrder"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.AbstractDeliveryOrder"
discriminator-value="8"
>
<property
name="deliveryFee"
type="java.math.BigDecimal"
update="true"
insert="true"
column="deliveryFee"
length="0"
/>
<property
name="extraFee"
type="java.math.BigDecimal"
update="true"
insert="true"
column="extraFee"
length="0"
/>
<subclass
name="kk.DeliveryOrder"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.DeliveryOrder"
discriminator-value="6"
>
</subclass>
<subclass
name="kk.MoveDeliveryOrder"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.MoveDeliveryOrder"
discriminator-value="7"
>
<many-to-one
name="toWarehouse"
class="kk.Warehouse"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="toWarehouse"
/>
</subclass>
</subclass>
<subclass
name="kk.LendOrder"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.LendOrder"
discriminator-value="3"
>
</subclass>
</class>
</hibernate-mapping>
------------------------------------------------------------
AOrderItem.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class
name="kk.AOrderItem"
table="AOrderItem"
proxy="kk.AOrderItem"
dynamic-update="false"
dynamic-insert="false"
discriminator-value="0"
>
<id
name="id"
column="id"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="increment">
</generator>
</id>
<discriminator
column="discriminator"
type="char"
length="1"
/>
<version
name="version"
type="long"
column="version"
/>
<many-to-one
name="order"
class="kk.AOrder"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="order_id"
/>
<property
name="discount"
type="java.math.BigDecimal"
update="true"
insert="true"
column="discount"
length="2"
/>
<property
name="subTotal"
type="java.math.BigDecimal"
update="true"
insert="true"
column="subTotal"
length="0"
/>
<property
name="accumulatedDeliveredQuantity"
type="java.math.BigDecimal"
update="true"
insert="true"
column="accumulatedDeliveredQuantity"
length="0"
/>
<property
name="preparedQuantity"
type="java.math.BigDecimal"
update="true"
insert="true"
column="preparedQuantity"
length="0"
/>
<property
name="price"
type="java.math.BigDecimal"
update="true"
insert="true"
column="price"
length="0"
/>
<many-to-one
name="product"
class="kk.Product"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="product"
/>
<property
name="quantity"
type="java.math.BigDecimal"
update="true"
insert="true"
column="quantity"
length="0"
/>
<many-to-one
name="invCostItem"
class="kk.InventoryCostItem"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="invCostItem"
/>
<property
name="costShareRate"
type="java.math.BigDecimal"
update="true"
insert="true"
column="costShareRate"
length="2"
/>
<property
name="remark"
type="java.lang.String"
update="true"
insert="true"
column="remark"
length="1000"
/>
<property
name="createDate"
type="java.util.Calendar"
update="true"
insert="true"
column="createDate"
/>
<many-to-one
name="createEmployee"
class="kk.Employee"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="createEmployee"
/>
<property
name="updateDate"
type="java.util.Calendar"
update="true"
insert="true"
column="updateDate"
/>
<many-to-one
name="updateEmployee"
class="kk.Employee"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="updateEmployee"
/>
<property
name="deprecated"
type="kk.options.BooleanEnum"
update="true"
insert="true"
column="deprecated"
/>
<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-AOrderItem.xml
containing the additional properties and place it in your merge dir.
-->
<subclass
name="kk.GiftOrderItem"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.GiftOrderItem"
discriminator-value="2"
>
</subclass>
<subclass
name="kk.PatchOrderItem"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.PatchOrderItem"
discriminator-value="4"
>
</subclass>
<subclass
name="kk.AbstractDeliveryOrderItem"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.AbstractDeliveryOrderItem"
discriminator-value="8"
>
<property
name="deliveryQuantity"
type="java.math.BigDecimal"
update="true"
insert="true"
column="deliveryQuantity"
length="0"
/>
<property
name="newDiscount"
type="java.math.BigDecimal"
update="true"
insert="true"
column="newDiscount"
length="2"
/>
<many-to-one
name="origionalOrderItem"
class="kk.AOrderItem"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="origionalOrderItem_id"
/>
<property
name="newSubTotal"
type="java.math.BigDecimal"
update="true"
insert="true"
column="newSubTotal"
length="0"
/>
<subclass
name="kk.MoveDeliveryOrderItem"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.MoveDeliveryOrderItem"
discriminator-value="7"
>
<property
name="accumulatedReceiveQuantity"
type="java.math.BigDecimal"
update="true"
insert="true"
column="accumulatedReceiveQuantity"
length="0"
/>
<property
name="moveDeliveryOrder"
type="kk.MoveDeliveryOrder"
update="false"
insert="false"
column="order_id"
/>
</subclass>
<subclass
name="kk.DeliveryOrderItem"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.DeliveryOrderItem"
discriminator-value="6"
>
</subclass>
</subclass>
<subclass
name="kk.LendOrderItem"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.LendOrderItem"
discriminator-value="3"
>
</subclass>
<subclass
name="kk.MoveOrderItem"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.MoveOrderItem"
discriminator-value="5"
>
<many-to-one
name="moveOrder"
class="kk.MoveOrder"
cascade="none"
outer-join="auto"
update="false"
insert="false"
column="order_id"
/>
</subclass>
<subclass
name="kk.SalesOrderItem"
dynamic-update="false"
dynamic-insert="false"
proxy="kk.SalesOrderItem"
discriminator-value="1"
>
</subclass>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
My implementation use reconnect()/disconnect() strategy!!
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
final String HSESSION = "hibernate.session";
HttpSession httpSession = ((HttpServletRequest)request).getSession();
Session session = (Session) httpSession.getAttribute(HSESSION);
try {
try {
if (session == null) {
session = initSession();
httpSession.setAttribute(HSESSION, session);
}
else {
if (!session.isConnected())
session.reconnect();
}
hSessionHolder.set(session);
chain.doFilter(request, response);
}
finally {
session = (Session) hSessionHolder.get();
hSessionHolder.set(null);
if (session != null && session.isConnected())
session.disconnect();
else
System.out.println("you must check why come to this line!!!");
}
}
catch (HibernateException e) {
e.printStackTrace();
throw new RuntimeException("Fail to close hibernate session", e);
}
}
Full stack trace of any exception that occurs:
Caused by: net.sf.hibernate.QueryException: undefined alias: kk [select i.order.address.county, i.order.address.township, sum(i.deliveryQuantity) from kk.DeliveryOderItem as i where i.order.deprecated=kk.options.BooleanEnum.FALSE group by i.order.address.county, i.order.address.township, i.origionalOrderItem.product.ean order by i.order.address.county, i.order.address.township ]
at net.sf.hibernate.hql.PathExpressionParser.token(PathExpressionParser.java:103)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.FromParser.token(FromParser.java:193)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1554)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1525)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at hibernate.SessionUtil.executeQuery(SessionUtil.java:144)
Name and version of the database you are using:
MySQL 4.0.20a , INNODB
Debug level Hibernate log excerpt:
Login
FAQ
Search
