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.

Answer 1

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)

Answer 2

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