Nested SELECT Query Java + MySQL

Question

I'm trying to get data from DB.

I must use to queries.

From the first query (In Loop) I get the code of Work codew and the I use it in the second query to get names.

Here's the code works without errors.

The first query fetch rows but the second is not executed.

int i=0;
c= new Connect().getCon();
try{

    java.sql.Statement st = c.createStatement();
    String sql= " SELECT  distinct(s.codew), title, nberstat, desc_w, dated,datef" +
                " FROM work w, employe e, stat s " +
                " WHERE w.codew=s.codew " +
                " and s.idemploye= e.idemploye " +
                " and stat_w=0 " +
                " and w.idcreator= 1";
     System.out.println(sql);       
    ResultSet res = (ResultSet) st.executeQuery(sql);
    String allW ="";
    while (res.next()) 
    {             
     String codew = res.getString("codew");  
     String title = res.getString("title");
     String desc_w = res.getString("desc_w");
     String dated = res.getString("dated");
     String date = res.getString("datef");
     int nberstat = res.getInt("nberstat"); String strnberstat = Integer.toString(nberstat);

    ///////////////////////////////////
        try
        {
            java.sql.Statement st2 = c.createStatement();
            q="SELECT distinct (name), lname From stat s, employe e WHERE codew LIKE '"+codew+"'";
            ResultSet res2 = (ResultSet) st2.executeQuery(q);
            while (res2.next()) 
            {
             String name = res.getString("name");
             String lname = res.getString("lname");
             allW = name + " "+lname+", "+allW;
            }
        }
        catch (SQLException s)
        {
            System.out.println("SQL code does not execute.");
        } 

     /////////////////////////////

........

}
c.close();
}
catch (SQLException s){
System.out.println("SQL code does not execute.");
}

I get this in the console for the second query! SQL code does not execute. Thanks in advance. Best regards, Ali

Try using this query SELECT DISTINCT(name), lname from stat s,employe e WHERE s.codew LIKE '"+codew+"' or SELECT DISTINCT(name), lname from stat s,employe e WHERE e.codew LIKE '"+codew+"' — Shashank_Itmaster
– Shashank_Itmaster, Commented Mar 25, 2011 at 3:43
You have no idea which catch is printing since you're outputting the same message. And because you're catching it and not printing the exception ... you have no idea what the exception is. I would change your prints to tell you which it is, and add s.getMessage() to them which will tell you what the problem is. — Brian Roach
– Brian Roach, Commented Mar 25, 2011 at 3:44

CoolBeans · Accepted Answer · 2011-03-25 03:46:45Z

2

First of all you should use PreparedStatement with sql parameters "?" to prevent Sql injections.

Secondly, you are using the wrong statement in the second loop. You should be using res2 not res.

This should be:

 String name = res2.getString("name"); //not res
 String lname = res2.getString("lname"); //not res

Thirdly, you should add a finally{} block and close your connection.

answered Mar 25, 2011 at 3:46

CoolBeans

20.9k10 gold badges88 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nested SELECT Query Java + MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related