Hi all,
I am using OJB for 1:N mapping. The code and mapping is shown below.
When i run this query the following error occur.
The column actually should be invoice_no.
[org.apache.ojb.broker.accesslayer.JdbcAccess] ERROR: SQLException
during the execution of the SQL query: Invalid column name
'PMI02/000001'.
Invalid column name 'PMI02/000001'.
String sql = "SELECT H.invoice_no, H.invoice_date, H.customer_no,
I.part_no,I.qty,I.unit_price"
+ " FROM invoice H, invoice_item I "
+ " WHERE H.invoice_no = I.invoice_no"
+ " AND H.invoice_no =\" PMI02/000001"\";
Query query = QueryFactory.newQuery(InvoiceBO.class, sql);
PersistenceBroker broker =
PersistenceBrokerFactory.createPersistenceBroker(
new PBKey("repositoryB.xml", null, null));
Collection result = broker.getCollectionByQuery(query);
Can anyone help or point out where i get wrong??
Thanks in advance.
InvoiceBO.class
-----------------------
public class InvoiceBO extends BaseBO {
private String invoiceNo;
private Timestamp invoiceDate;
private String customerNo;
private List invoiceItems;
/** Creates a new instance of Invoice */
public InvoiceBO() {
invoiceItems = new ArrayList();
}
public void addInvoiceItem(InvoiceItemBO invoiceItem){
invoiceItems.add(invoiceItem);
}
public void setInvoiceNo(String invoiceNo){
this.invoiceNo = invoiceNo;
}
public String getInvoiceNo(){
return invoiceNo;
}
public void setInvoiceDate(Timestamp invoiceDate){
this.invoiceDate = invoiceDate;
}
public Timestamp getInvoiceDate(){
return invoiceDate;
}
public void setCustomerNo(String customerNo){
this.customerNo = customerNo;
}
public String getCustomerNo(){
return customerNo;
}
public void setInvoiceItems(List invoiceItems){
this.invoiceItems = invoiceItems;
}
public List getInvoiceItems(){
return invoiceItems;
}
public String toString(){
StringBuffer sb = new StringBuffer();
sb.append("Invoice Date: " + invoiceDate.toString());
sb.append(" Customer No: " + invoiceDate.toString());
return sb.toString();
}
}
<!-- Definitions for my.com.shinyang.einout.business.bo.InvoiceBO -->
<class-descriptor
class="my.com.shinyang.einout.business.bo.InvoiceBO"
table="invoice">
<field-descriptor id="1"
name="invoiceNo"
column="invoice_no"
jdbc-type="VARCHAR"
primarykey="true"
/>
<field-descriptor id="2"
name="customerNo"
column="customer_no"
jdbc-type="VARCHAR"
/>
<field-descriptor id="3"
name="invoiceDate"
column="invoice_date"
jdbc-type="TIMESTAMP"
/>
<collection-descriptor
name="invoiceItems"
element-class-ref="my.com.shinyang.einout.business.bo.InvoiceItemBO"
auto-retrieve="true"
auto-update="false"
auto-delete="false"
orderby="invoiceNo"
sort="DESC">
<inverse-foreignkey field-id-ref="2"/>
</collection-descriptor>
</class-descriptor>
InvoiceItemBO.class
--------------------------------
public class InvoiceItemBO extends BaseBO {
private String invoiceNo;
private String itemNo;
private BigDecimal qty;
private Double unitPrice;
private InvoiceBO invoiceBO;
/** Creates a new instance of InvoiceItemBO */
public InvoiceItemBO() {
}
public void setItemNo(String itemNo){
this.itemNo = itemNo;
}
public String getItemNo(){
return itemNo;
}
public void setInvoiceBO(InvoiceBO invoiceBO){
this.invoiceBO = invoiceBO;
}
public InvoiceBO getInvoiceBO(){
return invoiceBO;
}
public void setInvoiceNo(String invoiceNo){
this.invoiceNo = invoiceNo;
}
public String getInvoiceNo(){
return invoiceNo;
}
public void setQty(BigDecimal qty){
this.qty = qty;
}
public BigDecimal getQty(){
return qty;
}
public void setUnitPrice(Double unitPrice){
this.unitPrice = unitPrice;
}
public Double getUnitPrice(){
return unitPrice;
}
}
<!-- Definitions for my.com.shinyang.einout.business.bo.InvoiceItemBO
-->
<class-descriptor
class="my.com.shinyang.einout.business.bo.InvoiceItemBO"
table="invoice_item">
<field-descriptor id="2"
name="invoiceNo"
column="invoice_no"
jdbc-type="VARCHAR"
primarykey="true"
/>
<field-descriptor id="3"
name="itemNo"
column="part_no"
jdbc-type="VARCHAR"
primarykey="true"
/>
<field-descriptor id="4"
name="qty"
column="qty"
jdbc-type="DECIMAL"
/>
<field-descriptor id="6"
name="unitPrice"
column="unit_price"
jdbc-type="DOUBLE"
/>
<reference-descriptor
name="invoiceBO"
class-ref="my.com.shinyang.einout.business.bo.InvoiceBO">
<foreignkey field-id-ref="2"/>
</reference-descriptor>
</class-descriptor>
