subquery in join

Question

I need to select companies from the database with their active addresses (address.address_status_id = 1). If the address is inactive the address columns should contain nulls.

The following query does exactly what I want:

select c.id, c.name, a.id, a.street
from company c
left join 
(select * from address where address_status_id = 1) a 
on c.id = a.company_id

I tried this in Java/QueryDSL:

JPAQuery query = new JPAQuery(entityManager);
query = query.from(qCompany);
query = query.leftJoin(company.addresses, new JPASubQuery().from(qAddress)    
.where(qAddress.addressStatusId.eq(AddressStatuss.ACTIVE.asBigDecimal())).list(qAddress));

However JPA (and consequently the JPAQuery in QueryDSL) doesn't support a subquery in a JOIN clause

Is there a way to rephrase the SQL statement so that it can be expressed in JPA/QueryDSL?

edit: We're using Oracle DB and Hibernate JPA provider if it makes a difference.

Similar question answer: https://stackoverflow.com/a/67254869/3308908 — GauravRatnawat, Apr 25 '21 at 15:39

score 7 · Accepted Answer · answered Jul 10 '13 at 12:27

7

Maybe like this

select c.id, c.name, a.id, a.street
from Company c
left join c.addresses a on a.addressStatusId = 1

and in Querydsl

query.from(c)
     .leftJoin(c.addresses, a)
     .on(a.addressStatusId.eq(1))
     .list(c.id, c.name, a.id, a.street)

answered Jul 10 '13 at 12:27

Timo Westkämper

21,824
5
78
111

Ouch, we're using version 2.9 and the ON method doesn't seem to exist in it. I see it's in 3.2.0. in JPAQueryBase. We tried to update some time ago but postponed it after seeing the amount of changes to be made for our code to compile. Maybe the ON method replaced the WITH method? – kosmičák Jul 10 '13 at 12:40
Yes, .with(a.addressStatusId.eq(1)) does exactly what I wanted! – kosmičák Jul 10 '13 at 13:19
Yes, `on` replaced `with`. – Timo Westkämper Jul 10 '13 at 13:34

score 1 · Answer 2 · answered Jul 10 '13 at 08:53

1

I would use the Oracle DB JDBC. You can get that here.

Depending on wether you are trying to build an Applet or Application, you will have to choose between the tiny and the OCI version of the JDBC. More information on that here.

Im going to assume that you are building an application, hence I will use OCI in this example.

Class.forName("oracle.jdbc.OracleDriver");
Connection con = null;
Statement stmt = null;
ResultSet rset = null;
try{
    con = DriverManager.getConnection("jdbc:oracle:oci:@//192.168.1.100", "user", "password");

    stmt = con.prepareStatement();

    rset = stmt.executeQuery("SELECT name, address, phonenumber FROM tbl_comanies");

    while(rset.next()){
        String name = rset.getString(1);
        String address = rset.getString(2);
        int phonenumber = rset.getInt(3);
        //The number passed to the get...()-Method is the place in the SQL query. (Here, name=1, address=2, phonenumber=3)
    }
} catch (SQLException ex) {
    System.out.println(ex.getMessage());
} finally {
    try {
        if(rset != null)
            rset.close();
        if(stmt != null)
            stmt.close();
        if(con != null)
            con.close();
    }
}

Hope this helps you out.

Regards, Andy

answered Jul 10 '13 at 08:53

AndMim

550
1
3
11

Too oldschool, JDBC is out of question in our application. :) – kosmičák Jul 10 '13 at 10:02
Can you explain why it is too oldschool or why it is out of question? – AndMim Jul 10 '13 at 11:24
1

There are lots of reasons why this is totally unsuitable for large applications and why other than ORM approach is hardly ever used anymore. You have to manually map your ResultSet to your Java business classes - an error prone nightmare. Your tables and columns names are hardcoded in strings with no compile time verification and type safety. What if the table or column name is changed in the DB - you won't know something's wrong until you run the specific code. Your code is database dependent, what if you decide to switch from Oracle to MySQL later? – kosmičák Jul 10 '13 at 11:46
If you decide to switch from Oracle to Mysql, you are in trouble anyway (twice!). – Dainius Jul 10 '13 at 13:47
And in that case, you would simply swap the JDBC drivers :) – AndMim Jul 10 '13 at 14:45
2

@AndMim: Really? So the following statement perfectly valid for MySQL will work under Oracle? SELECT * from company LIMIT 10 – kosmičák Jul 10 '13 at 14:58

subquery in join

2 Answers2