While I accept the fact that Hibernate is executing a native SQL, it isn't true that that is all it is doing. SQL will return a table with a column for each selected item, (each column from crj.*, each column from cra.*, etc.). Hibernate then does the mapping to put all of the crj.* columns into an entity, all of the cra.* columns in an entity, etc. I suspect, but don't really know, that it is in the mapping that the failure occurs.
I looked at you templates, but unfortunately I am restricted from downloading a new environment and don't really understand how to set them up. I have, however, created a simple test case that I hope you can use to replicate the error. The case contains 3 tables, testBank, testCust, and testOrder. The SQL to create and then populate these tables is shown below:
Code:
CREATE TABLE test_bank
(
test_bank_serial serial NOT NULL,
test_bank_id integer NOT NULL,
test_bank_name character varying(40) NOT NULL,
opto_lock integer DEFAULT 0,
CONSTRAINT test_bank_key PRIMARY KEY (test_bank_serial)
)
WITH (
OIDS = FALSE
);
CREATE UNIQUE INDEX test_bank_id_key
ON test_bank (test_bank_id ASC NULLS LAST);
GRANT ALL ON TABLE test_bank TO postgres;
GRANT ALL ON TABLE test_bank TO general;
GRANT SELECT ON TABLE test_bank TO readonly;
CREATE TABLE test_cust
(
test_cust_serial serial NOT NULL,
test_cust_number integer NOT NULL,
test_cust_name character varying NOT NULL,
test_bank_serial integer NOT NULL,
opto_lock integer DEFAULT 0,
CONSTRAINT test_customer_key PRIMARY KEY (test_cust_serial)
)
WITH (
OIDS = FALSE
);
CREATE UNIQUE INDEX test_cust_num_key
ON test_cust (test_cust_number ASC NULLS LAST);
GRANT ALL ON TABLE test_cust TO postgres;
GRANT ALL ON TABLE test_cust TO general;
GRANT SELECT ON TABLE test_cust TO readonly;
CREATE TABLE test_order
(
test_order_serial serial NOT NULL,
test_order_number integer NOT NULL,
test_cust_serial integer NOT NULL,
test_order_amount numeric(11,2),
opto_lock integer DEFAULT 0,
CONSTRAINT test_order_key PRIMARY KEY (test_order_serial),
CONSTRAINT test_order_cust FOREIGN KEY (test_cust_serial) REFERENCES test_cust (test_cust_serial) ON UPDATE NO ACTION ON DELETE RESTRICT
)
WITH (
OIDS = FALSE
);
CREATE UNIQUE INDEX test_order_number_key
ON test_order (test_order_number ASC NULLS LAST);
GRANT ALL ON TABLE test_order TO postgres;
GRANT ALL ON TABLE test_order TO general;
GRANT SELECT ON TABLE test_order TO readonly;
Code:
INSERT INTO test_bank (test_bank_id, test_bank_name, opto_lock) VALUES (1, 'Bank of America', 0);
INSERT INTO test_bank (test_bank_id, test_bank_name, opto_lock) VALUES (2, 'Commerce Bank', 0);
INSERT INTO test_cust (test_cust_number, test_cust_name, test_bank_serial, opto_lock) VALUES(1, 'CustNo 1',
(SELECT test_bank_serial FROM test_bank WHERE test_bank_id = 1), 0);
INSERT INTO test_cust (test_cust_number, test_cust_name, test_bank_serial, opto_lock) VALUES(2, 'CustNo 2',
(SELECT test_bank_serial FROM test_bank WHERE test_bank_id = 1), 0);
INSERT INTO test_cust (test_cust_number, test_cust_name, test_bank_serial, opto_lock) VALUES(3, 'CustNo 3',
(SELECT test_bank_serial FROM test_bank WHERE test_bank_id = 2), 0);
INSERT INTO test_cust (test_cust_number, test_cust_name, test_bank_serial, opto_lock) VALUES(4, 'CustNo 4',
(SELECT test_bank_serial FROM test_bank WHERE test_bank_id = 1), 0);
INSERT INTO test_order (test_order_number, test_cust_serial, test_order_amount, opto_lock) VALUES (1,
(SELECT test_cust_serial FROM test_cust WHERE test_cust_number = 2), 100.00,0);
INSERT INTO test_order (test_order_number, test_cust_serial, test_order_amount, opto_lock) VALUES (2,
(SELECT test_cust_serial FROM test_cust WHERE test_cust_number = 2), 200.00,0);
INSERT INTO test_order (test_order_number, test_cust_serial, test_order_amount, opto_lock) VALUES (3,
(SELECT test_cust_serial FROM test_cust WHERE test_cust_number = 3), 300.00,0);
The SQL to retrieve all orders, customers and their bank names is:
Code:
SELECT o.*, c.*, b.test_bank_name
FROM test_order o
FULL OUTER JOIN test_cust c USING (test_cust_serial)
JOIN test_bank b USING (test_bank_serial)
ORDER BY test_cust_number ASC,
test_order_number ASC;
Entities for these tables are below:
Code:
package com.jmiinc.ejb.jpa.entities.dms3;
import java.io.Serializable;
import javax.persistence.*;
/**
* The persistent class for the test_bank database table.
*
*/
@Entity
@Table(name="test_bank")
@NamedQuery(name="TestBank.findAll", query="SELECT t FROM TestBank t")
public class TestBank implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@SequenceGenerator(name="TEST_BANK_TESTBANKSERIAL_GENERATOR", sequenceName="TEST_BANK_TEST_BANK_SERIAL_SEQ")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="TEST_BANK_TESTBANKSERIAL_GENERATOR")
@Column(name="test_bank_serial", unique=true, nullable=false)
private Integer testBankSerial;
@Column(name="opto_lock")
private Integer optoLock;
@Column(name="test_bank_id", nullable=false)
private Integer testBankId;
@Column(name="test_bank_name", nullable=false, length=40)
private String testBankName;
public TestBank() {
}
public Integer getTestBankSerial() {
return this.testBankSerial;
}
public void setTestBankSerial(Integer testBankSerial) {
this.testBankSerial = testBankSerial;
}
public Integer getOptoLock() {
return this.optoLock;
}
public void setOptoLock(Integer optoLock) {
this.optoLock = optoLock;
}
public Integer getTestBankId() {
return this.testBankId;
}
public void setTestBankId(Integer testBankId) {
this.testBankId = testBankId;
}
public String getTestBankName() {
return this.testBankName;
}
public void setTestBankName(String testBankName) {
this.testBankName = testBankName;
}
}
Code:
package com.jmiinc.ejb.jpa.entities.dms3;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.ColumnResult;
import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.SequenceGenerator;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.SqlResultSetMappings;
import javax.persistence.Table;
/**
* The persistent class for the test_cust database table.
*
*/
@Entity
@Table(name="test_cust")
@NamedQuery(name="TestCust.findAll", query="SELECT t FROM TestCust t")
@SqlResultSetMappings({
@SqlResultSetMapping(name = "TestHibernateJoin", entities = {
@EntityResult(entityClass = com.jmiinc.ejb.jpa.entities.dms3.TestOrder.class),
@EntityResult(entityClass = com.jmiinc.ejb.jpa.entities.dms3.TestCust.class)} ,
columns = {
@ColumnResult(name = "test_bank_name")
})
})
public class TestCust implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@SequenceGenerator(name="TEST_CUST_TESTCUSTSERIAL_GENERATOR", sequenceName="TEST_CUST_TEST_CUST_SERIAL_SEQ")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="TEST_CUST_TESTCUSTSERIAL_GENERATOR")
@Column(name="test_cust_serial", unique=true, nullable=false)
private Integer testCustSerial;
@Column(name="opto_lock")
private Integer optoLock;
@Column(name="test_bank_serial", nullable=false)
private Integer testBankSerial;
@Column(name="test_cust_name", nullable=false, length=2147483647)
private String testCustName;
@Column(name="test_cust_number", nullable=false)
private Integer testCustNumber;
public TestCust() {
}
public Integer getTestCustSerial() {
return this.testCustSerial;
}
public void setTestCustSerial(Integer testCustSerial) {
this.testCustSerial = testCustSerial;
}
public Integer getOptoLock() {
return this.optoLock;
}
public void setOptoLock(Integer optoLock) {
this.optoLock = optoLock;
}
public Integer getTestBankSerial() {
return this.testBankSerial;
}
public void setTestBankSerial(Integer testBankSerial) {
this.testBankSerial = testBankSerial;
}
public String getTestCustName() {
return this.testCustName;
}
public void setTestCustName(String testCustName) {
this.testCustName = testCustName;
}
public Integer getTestCustNumber() {
return this.testCustNumber;
}
public void setTestCustNumber(Integer testCustNumber) {
this.testCustNumber = testCustNumber;
}
}
Code:
package com.jmiinc.ejb.jpa.entities.dms3;
import java.io.Serializable;
import java.math.BigDecimal;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
/**
* The persistent class for the test_order database table.
*
*/
@Entity
@Table(name="test_order")
@NamedQuery(name="TestOrder.findAll", query="SELECT t FROM TestOrder t")
public class TestOrder implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@SequenceGenerator(name="TEST_ORDER_TESTORDERSERIAL_GENERATOR", sequenceName="TEST_ORDER_TEST_ORDER_SERIAL_SEQ")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="TEST_ORDER_TESTORDERSERIAL_GENERATOR")
@Column(name="test_order_serial", unique=true, nullable=false)
private Integer testOrderSerial;
@Column(name="opto_lock")
private Integer optoLock;
@Column(name="test_cust_serial", nullable=false)
private Integer testCustSerial;
@Column(name="test_order_amount", precision=11, scale=2)
private BigDecimal testOrderAmount;
@Column(name="test_order_number", nullable=false)
private Integer testOrderNumber;
public TestOrder() {
}
public Integer getTestOrderSerial() {
return this.testOrderSerial;
}
public void setTestOrderSerial(Integer testOrderSerial) {
this.testOrderSerial = testOrderSerial;
}
public Integer getOptoLock() {
return this.optoLock;
}
public void setOptoLock(Integer optoLock) {
this.optoLock = optoLock;
}
public Integer getTestCustSerial() {
return this.testCustSerial;
}
public void setTestCustSerial(Integer testCustSerial) {
this.testCustSerial = testCustSerial;
}
public BigDecimal getTestOrderAmount() {
return this.testOrderAmount;
}
public void setTestOrderAmount(BigDecimal testOrderAmount) {
this.testOrderAmount = testOrderAmount;
}
public Integer getTestOrderNumber() {
return this.testOrderNumber;
}
public void setTestOrderNumber(Integer testOrderNumber) {
this.testOrderNumber = testOrderNumber;
}
}
Business objects - testBank:
Code:
package com.jmiinc.ejb.value.object.dms3;
import com.jmiinc.ejb.jpa.entities.dms3.TestBank;
import com.jmiinc.ejb.value.object.base.AbstractDomainObject;
public class TestBankBO extends AbstractDomainObject {
private static final long serialVersionUID = DEFAULT_SERIAL_VERSION_ID;
private TestBank testBankNtty;
public TestBankBO () {
testBankNtty = new TestBank();
}
public TestBank getTestBankNtty() {
return testBankNtty;
}
public void setTestBankNtty(TestBank testBankNtty) {
this.testBankNtty = testBankNtty;
}
}
testCust:
Code:
package com.jmiinc.ejb.value.object.dms3;
import com.jmiinc.ejb.jpa.entities.dms3.TestCust;
import com.jmiinc.ejb.value.object.base.AbstractDomainObject;
public class TestCustBO extends AbstractDomainObject {
private static final long serialVersionUID = DEFAULT_SERIAL_VERSION_ID;
private TestCust testCustNtty;
public TestCustBO () {
testCustNtty = new TestCust();
}
public TestCust getTestCustNtty() {
return testCustNtty;
}
public void setTestCustNtty(TestCust testCustNtty) {
this.testCustNtty = testCustNtty;
}
}
testOrder
Code:
package com.jmiinc.ejb.value.object.dms3;
import com.jmiinc.ejb.jpa.entities.dms3.TestOrder;
import com.jmiinc.ejb.value.object.base.AbstractDomainObject;
public class TestOrderBO extends AbstractDomainObject {
private static final long serialVersionUID = DEFAULT_SERIAL_VERSION_ID;
private TestOrder testOrderNtty;
public TestOrderBO () {
testOrderNtty = new TestOrder();
}
public TestOrder getTestOrderNtty() {
return testOrderNtty;
}
public void setTestOrderNtty(TestOrder testOrderNtty) {
this.testOrderNtty = testOrderNtty;
}
}
EjbDAO for testCust:
Code:
package com.jmiinc.ejb.implementors.dms3;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import com.jmiinc.commonejb.constants.StaticReferenceInterface;
import com.jmiinc.commonejb.util.SimpleStringBuilder;
import com.jmiinc.ejb.implementors.base.GenericEjbDAO;
import com.jmiinc.ejb.jpa.entities.dms3.TestCust;
import com.jmiinc.ejb.jpa.entities.dms3.TestOrder;
import com.jmiinc.ejb.value.object.base.TestCustVO;
import com.jmiinc.ejb.value.object.dms3.TestCustBO;
import com.jmiinc.ejb.value.object.dms3.TestOrderBO;
@Stateless
@LocalBean
public class TestCustEjbDAO extends GenericEjbDAO<TestCust> implements StaticReferenceInterface {
@PersistenceContext(unitName = "dms3-persistence-unit")
private EntityManager em;
public TestCustEjbDAO() {
super(TestCust.class);
}
public List<TestCustVO> retrieveTestCust () {
SimpleStringBuilder sqlQuery = new SimpleStringBuilder(1024);
sqlQuery.append("SELECT o.*, c.*, b.test_bank_name");
sqlQuery.append(" FROM dms3.test_order o ");
sqlQuery.append(" FULL OUTER JOIN dms3.test_cust c USING (test_cust_serial)");
sqlQuery.append(" JOIN dms3.test_bank b USING (test_bank_serial)");
sqlQuery.append(" ORDER BY ");
sqlQuery.append(" c.test_cust_number ASC,");
sqlQuery.append(" o.test_order_number ASC ");
Query query = em.createNativeQuery(sqlQuery.toString(), "TestHibernateJoin");
List<TestCustVO> testCustList = new ArrayList<>();
List<?> listResult = query.getResultList();
if (listResult.size() == 0) {
return testCustList;
} else {
for (Iterator<?> iter = listResult.iterator(); iter.hasNext();) {
Object[] objects = (Object[]) iter.next();
TestCustVO testCustVObj = new TestCustVO();
TestOrderBO testOrder = new TestOrderBO();
testOrder.setTestOrderNtty((TestOrder) objects[0]);
testCustVObj.setTestOrderObj(testOrder);
TestCustBO testCustObj = new TestCustBO();
testCustObj.setTestCustNtty((TestCust) objects[1]);
testCustVObj.setTestCustObj(testCustObj);
testCustVObj.setTestBankName((String) objects[2]);
testCustList.add(testCustVObj);
}
}
return testCustList;
}
}
Execution of the following statement in any driver program will return the results shown.
Code:
// Retrieve all records from testOrder, testCust saving them in testCustVO
setTestCustList(
testCustEjb.retrieveTestCust());
OrderNo OrderAmount CustNum CustName BankName
Bank of America
1 100 2 CustNo 2 Bank of America
2 200 2 CustNo 2 Bank of America
3 300 3 CustNo 3 CommerceBank
Bank of America
Note that 1st and 5th row's customer number and name are null, while they should be 1, CustNo 1 and 5, CustNo 5
Execution of the attached SQL will correctly return this data. Hopefully this gives you enough to replicate the error in your templates without too much difficulty.
Login
FAQ
Search
