aspose file tools*
The moose likes JDBC and the fly likes Union Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Union" Watch "Union" New topic
Author

Union

George W Smith
Greenhorn

Joined: Apr 29, 2011
Posts: 4
Hi All,

I am trying to use a union to get data in a table in my java class. In the mysql database the query executes perfectly.
However when ran in the java class it says it cannot find "user2"?

I have simplified the statement to the extreme(!!!) and it still says the same? Code is below any help would be great.

SELECT admin from private union SELECT user2 from private;

(user2 is a column in the database...)
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

Welcome to the Ranch, George!

Where does it say it can't find user2? When you run the query, or when you get the field?

It can help if you show that piece of code, and the exact error.



OCUP UML fundamental and ITIL foundation
youtube channel
George W Smith
Greenhorn

Joined: Apr 29, 2011
Posts: 4
String statement = "SELECT admin from private union SELECT user2 from private";
prep = conn.prepareStatement(statement);
ResultSet rs = (ResultSet) prep.executeQuery();
while(rs.next()) {
temp1.setAdmin(rs.getString("admin"));
temp1.setMem2(rs.getString("user2"));
leagues.add(temp1);
}
conn.close();

Error:

Column 'user2' not found

The error is when I run it and it says no data to display.
There is a column user2 in the private table in the sql database, as the full statement works at command line.
amit punekar
Ranch Hand

Joined: May 14, 2004
Posts: 516
Hello,
May be when you are executing the query you are connecting to different MYSQL instance than the one you are using from Java program.



Regards,
amit
George W Smith
Greenhorn

Joined: Apr 29, 2011
Posts: 4
No its the correct one alright as when I execute a query on just the select user2 with no union it works fine.
Is their a problem with unions and jdbc or am I making a mistake in it somewhere?
Sam Falzon
Greenhorn

Joined: Apr 29, 2011
Posts: 5

With a UNION clause you only get one column back. You probably want to select both columns rather than do a UNION since they are coming from the same table. Why are doing a UNION?
You may want to code your select this way:

George W Smith
Greenhorn

Joined: Apr 29, 2011
Posts: 4
Yes I know that, the long complex statement wasnt working. Therefore I am attempting to get a simpler one working with two basic selects.
Both statements give the same error.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

How does the table look like, and what do you want to retrieve from it?
Sam Falzon
Greenhorn

Joined: Apr 29, 2011
Posts: 5

Your issue is really a SQL issue.

Here is a test table I created to mimic your data.



Your SQL should produce this:


A few things to note:
  • Only 1 column is returned and it is called 'ADMIN' - hence Java is not aware of a column called user2.
  • There is no way to no which column (admin or user2 the data came from)


  • The Java to execute the SQL looks like this:



    Output:



    Jan Cumps
    Bartender

    Joined: Dec 20, 2006
    Posts: 2516
        
      10

    Welcome to the JavaRanch, Sam!
    satish kumar jha
    Greenhorn

    Joined: May 03, 2011
    Posts: 2
    nice but you get wrong
    check this
    ================


    ------

    =================
    both sql give same result
    when you do union remember that all column name are same.
    Sam Falzon
    Greenhorn

    Joined: Apr 29, 2011
    Posts: 5

    I guess it depends on your database. I am using Oracle and your SQLs do not produce identical results in Oracle.



    Your original SQL used double-quotes, but this does not work in Oracle and gives a syntax error, so I changed it to single-quote:







    Your SQL will produce two columns, but it goes back to my original point that it was a SQL issue and the original poster was using the incorrect SQL. If he uses your SQL, that should fix his problem, but then he has to be aware that some admin and user2 variables will have blank values as the above SQL illustrates. Is this what the original poster wants? If no, then a simple SQL will do the job rather than a UNION:



    The original poster should illustrate with example data of what is in the table 'private' and what he want to populate his objects with?



    Rupesh Mhatre
    Ranch Hand

    Joined: Apr 29, 2011
    Posts: 35

    in case of Original poster's original query of union the resultset what we get is of single column named as admin as select admin from private is the first query and there is no column named user2
    thats why he was not getting that column in temp1.setMem2(rs.getString("user2"));
    satish kumar jha
    Greenhorn

    Joined: May 03, 2011
    Posts: 2
    George W Smith wrote:No its the correct one alright as when I execute a query on just the select user2 with no union it works fine.
    Is their a problem with unions and jdbc or am I making a mistake in it somewhere?


    if you using mysql than read my post but check it in your complex statement it work not for just example.

    Remember that when you try to union of two sql than both or more sql which you want to join have same column name so you use blank if necessary

    also remember that use single quote if you use oracle. and in mysql both quotes are valid.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Union