Problem Brief:
I am trying to query my database where I have mapped the model as a Superclass-subclass one-to-one model. Being that, "Account" is the superclass, it is extended by one of the following subclasses and it can only be of one subclass type, not both.
The superclass holds all the "Accounts" and the subclasses are of type "User" and "Contact". I need to query from the Superclass, "Account" as I have to dynamically check for the one-to-one relationship occurrence which may be present in either of the subclasses.
Suppose this is my Superclass entity, "Account":
@Entity
@Table(name="tbl_account")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name="type", discriminatorType = DiscriminatorType.STRING)
@NamedQuery(name="Account.findAll", query="SELECT a FROM Account a")
public class Account implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@SequenceGenerator(name="TBL_ACCOUNT_ACCID_GENERATOR", sequenceName="tbl_account_acc_id_seq", allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="TBL_ACCOUNT_ACCID_GENERATOR")
@Column(name="acc_id")
private Integer accId;
//bi-directional one-to-one association to Contact
@OneToOne(mappedBy="tblAccount")
private Contact tblContact;
//bi-directional one-to-one association to User
@OneToOne(mappedBy="tblAccount")
private User tblUser;
..... /* other fields */ .....
and here are the subclasses for "User" and "Contact" which extends and inherit from "Account" as shown:
@Entity
@Table(name="tbl_user")
@DiscriminatorValue("u")
@NamedQuery(name="User.findAll", query="SELECT u FROM User u")
public class User extends Account implements Serializable {
private static final long serialVersionUID = 1L;
//bi-directional one-to-one association to Account
@OneToOne
@PrimaryKeyJoinColumn(name="acc_id")
private Account tblAccount;
..... /* other fields */ .....
.................
@Entity
@Table(name="tbl_contact")
@DiscriminatorValue("c")
@NamedQuery(name="Contact.findAll", query="SELECT c FROM Contact c")
public class Contact extends Account implements Serializable {
private static final long serialVersionUID = 1L;
//bi-directional one-to-one association to Account
@OneToOne
@PrimaryKeyJoinColumn(name="acc_id")
private Account tblAccount;
..... /* other fields */ .....
Desired Result:
I would like to Multiselect (via CriteriaBuilder) the "Account", "User" and "Contact" details so that it matches a working SQL example as follows:
SELECT tc."acc_id",
tc."name" AS "acc_name"
FROM "tbl_contact" AS tc
INNER JOIN
"tbl_account" AS ta
ON tc."acc_id" = ta."acc_id"
UNION
SELECT tu."acc_id",
CONCAT(tu."forename", ' ', tu."surname") AS "acc_name"
FROM "tbl_user" AS tu
INNER JOIN
"tbl_account" AS ta
ON tu."acc_id" = ta."acc_id"
ORDER BY "acc_id" ASC;
Namely, I select both "User" and "Contact" and combine the CONCATENATED name of user with contact (via UNION) matching an ID from "Account". This SQL returns ALL accounts with the corresponding names of each. In hindsight, I would replace both these lines:
ON tu."acc_id" = ta."acc_id" (AND) ON tc."acc_id" = ta."acc_id"
with
// insert specific account ID here to look for which subclass a particular account belongs to
ON tu."acc_id" = 120 (OR) ON tc."acc_id" = 120 (respectively)
Attempt:
Below, is what I attempted with CriteriaAPI using a JUnit Test. I use "Account" as root and declare the subclasses as Join objects, before using metamodels (thus ensuring I am using the right attributes) to pull the respective fields in my query.
(I realise that returning a List of Object[] throws a java warning for unchecked safety. This is purely temporary for a JUnit Test):
@Test
public void testJoinAccounts() {
CriteriaBuilder cBuilder = em.getCriteriaBuilder();
CriteriaQuery<Object[]> cQuery = cBuilder.createQuery(Object[].class);
Root<Account> accountRoot = cQuery.from(Account.class);
Join<Account, User> joinUser = accountRoot.join(Account_.tblUser);
Join<Account, Contact> joinContact = accountRoot.join(Account_.tblContact);
Expression<String> concatUserName = cBuilder.concat(joinUser.<String>get(User_.forename), " ");
concatUserName = cBuilder.concat(concatUserName, joinUser.<String>get(User_.surname));
Predicate tblUser = cBuilder.equal(joinUser.get(User_.accId), accountRoot.get(Account_.accId));
Predicate tblContact = cBuilder.equal(joinContact.get(Contact_.accId), accountRoot.get(Account_.accId));
cQuery.multiselect(accountRoot.get(Account_.accId), concatUserName, joinContact.get(Contact_.name))
.where(cBuilder.or(cBuilder.equal(joinUser.get(User_.accId), accountRoot.get(Account_.accId)), (cBuilder.equal(joinContact.get(Contact_.accId), accountRoot.get(Account_.accId)))));
Query qry = em.createQuery(cQuery);
List<Object[]> results = qry.getResultList();
for(Object[] result : results) {
System.out.println("-------------------------------------");
System.out.println("Account ID is: [" + result[0] + "]");
System.out.println("Account Name is: [" + result[1] + "]");
}
}
The JUnitTest executes fine without errors or problems in both the Console and JUnit windows. But has no print line results. Here is the Console trace:
[EL Fine]: sql: 2015-12-01 17:36:46.038--ServerSession(1018298342)--Connection(42338572)--Thread(Thread[main,5,main])--SELECT t0.acc_id, t2.FORENAME || ? || t2.SURNAME, t4.NAME FROM tbl_contact t4, tbl_account t3, tbl_user t2, tbl_account t1, tbl_account t0 WHERE (((t1.acc_id = t0.acc_id) OR (t3.acc_id = t0.acc_id)) AND (((t1.acc_id = t0.acc_id) AND ((t2.acc_id = t1.acc_id) AND (t1.type = ?))) AND ((t3.acc_id = t0.acc_id) AND ((t4.acc_id = t3.acc_id) AND (t3.type = ?)))))
bind => [ , u, c]
Closing Statement:
Thus suggesting my Entity models and Inheritance strategy are correct and that my problem lies within my program logic where I select from both "User" and "Contact".
This makes me come to the conclusion that this issue is only occurring when I am trying to query both my subclasses. Please advise.
I have discovered the root of my issue caused by 2 logic problems.
In Detail:
By implementing the SQL UNION code, I can effectively remove the NULL results and combine the result set with both SQL statements.
However, by trying to substitute the design with SQL JOIN instead, I omitted the JoinType strategy, thus it defaulted to INNER JOIN. Therefore, the code was trying to retrieve all records and only the records which match identically. Because both tables have different column results, this fails. To overcome this, LEFT JOIN strategy should have been implemented instead, which will accept null results, as below:
Solution:
@Test
public void testJoinAccounts() {
CriteriaBuilder cBuilder = em.getCriteriaBuilder();
CriteriaQuery<Object[]> cQuery = cBuilder.createQuery(Object[].class);
Root<Account> accountRoot = cQuery.from(Account.class);
Join<Account, User> joinUser = accountRoot.join(Account_.tblUser, JoinType.LEFT);
Join<Account, Contact> joinContact = accountRoot.join(Account_.tblContact, JoinType.LEFT);
The above code will execute and the solution will now produce results. However, the returned results gave something to the following effect on the left, but to the right of is what I was looking to achieve:
+-------+------+------+ +-------+------+
| cID| name| name| | cID| name|
+-------+------+------+ +-------+------+
|1 |JJAZ |null | |1 |JJAZ |
+-------+------+------+ +-------+------+
|2 |CCLL |null | |2 |CCLL |
+-------+------+------+ +-------+------+
|3 |OOBB |null | |3 |OOBB |
+-------+------+------+ +-------+------+
|4 |null |ABCD | |4 |ABCD |
+-------+------+------+ +-------+------+
|5 |null |BCDE | |5 |BCDE |
+-------+------+------+ +-------+------+
|6 |null |CDEF | |6 |CDEF |
+-------+------+------+ +-------+------+
|7 |JKNN |null | |7 |JKNN |
+-------+------+------+ +-------+------+
|8 |null |DEFG | |8 |DEFG |
+-------+------+------+ +-------+------+
|9 |RRLW |null | |9 |RRLW |
+-------+------+------+ +-------+------+
|10 |GNQN |null | |10 |GNQN |
+-------+------+------+ +-------+------+
|... |? |? | |... |? |
+-------+------+------+ +-------+------+
Solution:
and so, I altered the original multiselect method from:
// this
cQuery.multiselect(accountRoot.get(Account_.accId), concatUserName, joinContact.get(Contact_.name))
// to this
cQuery.multiselect(accountRoot.get(Account_.accId), cBuilder.coalesce(concatUserName, joinContact.get(Contact_.name)).alias("name"))
I used the coalesce() function which evaluates (x, y) until it discovers the first argument value which is not null. So, by substituting users and contacts into the function, this solved my problem.
Reference:
To see the coalesce() function in more detail, visit this other question I posted:
SQL: How to combine (merge) similar columns to remove NULLs via JOIN