jdbc - Multiple select statements in one query

Question

Firstly, I want to say that this question is not about JOIN or UNION. Also, there are some answers on stack overflow which I've tried, but I'm still having issues. (example: Multiple queries executed in java in single statement)

Problem:
I want to retrieve data from two tables which I can't do using JOIN or UNION since they contain different types of data and I'm after different number of rows from each table. The reason why I want to do it in one go is efficiency however if somebody is able to convince me that two calls instead of one make almost no difference in overhead then I'll happily solve my problem like that (although it would still be nice to know how to use one query only)

Code:
Based on this, I wrote the following code (excerpt):

String sql = "SET @organizationId = 0;" +
                "SELECT @organizationId := Id 'Id', Name FROM Organization WHERE Id = ?;" +
                "SELECT AssociatedOrganizationId FROM OrganizationAssociations WHERE OrganizationId = @organizationId;";

        Connection conn = null;

        try {
            conn = dataSource.getConnection();

            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, organizationId);

            boolean isResultSet = cs.execute();

            if (!isResultSet) {
                logger.error("getOrganization - retrieved result is not a ResultSet");
            }

            OrganizationDto organization = null;

            ResultSet rs = cs.getResultSet();
            if (rs.next()) {
                // create organization object using retrieved data
            }

            rs.close();

Behaviour causing the issue:

cs.execute() always returns false.

Observations and actions I took to try to resolve the issue:
As stated here and in Java documentation, CallableStatement was designed to be used for calling stored procedures. Stored procedures themselves never return a value and this can only be achieved through OUT parameters. Bearing that in mind, it doesn't surprise me that .execute() returns false. I have however changed CallableStatement to PreparedStatement and Statement but that didn't change much in terms of the outcome (cs.getResultSet() still returned null)

The answers I'm seeking:
1. I'd like to know how can I achieve retrieving data from multiple tables using one query with multiple SELECT statements in jdbc. That is to be achieved without JOINS, UNIONS etc. I'd like to be able to use the query I already have if possible (query works perfectly fine - tested in HeidiSQL).
2. (Optional) If there's anybody out there who thinks that two queries would make not much difference in terms of database load and performance then I'd like to be convinced that this actually is the case.

Update #1:
To clarify, this is the data that my query returns:

ResultSet #1:
Id, Name
1, "org1_name"

ResultSet #2:
AssociatedOrganizationId
2
3

Use a proc, return two cursors and use them, what problem you faced in Callable statement ? — Amit Bhati, Jan 06 '17 at 12:40
Is it possible to use the query as is, rather than creating a stored procedure? I come from .NET background and queries like these are not uncommon. As described, CallableStatement.execute() always returns false in my case (probably because I'm not calling a stored procedure with OUT param) — JakeDiscBrake, Jan 06 '17 at 12:44
Thanks @GordThompson. If anybody else could confirm that this is indeed the only way to do it in MySQL then I'll create stored proc and that's how I'll solve this. — JakeDiscBrake, Jan 06 '17 at 21:46

score -1 · Answer 1 · answered Jan 06 '17 at 12:42

-1

First, your two queries can be written as a single query using a join:

SELECT AssociatedOrganizationId
FROM OrganizationAssociations oi JOIN
     Organization o
     ON oa.OrganizationId = o.id
WHERE o.id = ?

Second, this is not even needed, because you can simplify the whole thing to:

SELECT AssociatedOrganizationId
FROM OrganizationAssociations oi
WHERE oa.OrganizationId = ?

I would suggest that you take some time out to study SQL and learn how databases work. It would probably greatly help you with the problems you want to solve.

answered Jan 06 '17 at 12:42

Gordon Linoff

1,242,037
58
646
786

You are assuming that all I need are associated organizations ids (only one table). I need some details about organization (Organization table) and list of associated organizations ids (OrganizationAssociations table). So the results for my query are: {Id, Name} AND {AssociatedOrganizationId}. First ResultSet contains one row, the other can contain multiple rows. – JakeDiscBrake Jan 06 '17 at 12:52
seems Gordon's assumption is right. Prove us wrong by posting sample data and desired output – e4c5 Jan 06 '17 at 13:13
@e4c5 - updated my question – JakeDiscBrake Jan 06 '17 at 13:33
1

which proves that Gordon got it right – e4c5 Jan 06 '17 at 13:48
Gordon's query returns only 1 ResultSet - list of associated organizations. It doesn't return organization's details. – JakeDiscBrake Jan 06 '17 at 14:57

jdbc - Multiple select statements in one query

1 Answers1