1 2 Previous Next 27 Replies Latest reply: Sep 6, 2008 9:32 AM by 800308 RSS

    Newbie JDBC resultset help

    800610
      Hi Guys

      I am relatively new on webapp developments so go easy on me!
      I just ran a query which returns several record but I can only get the data for one record. How can I change the following code so that my code will beable process and output mulitple records:
      ResultSet rs = DB.exQuery("SELECT * FROM contacts");
      String id="";
      try {
           while(rs.next()){
                id = rs.getString("user_id");
           }
      } catch(SQLException ex){/*print error msg*/}
      
      return id;
        • 1. Re: Newbie JDBC resultset help
          807589
          rather than return a String, why not create an ArrayList<String>, add a String to this list with each iteration of the while loop, and then return the list from the method.
          • 2. Re: Newbie JDBC resultset help
            800610
            Thanks. I did manage to intgrate an arraylist. It prints single records fine but when it comes to returning multiple records the XML doesn't output anything. See code below:
            try {
                 while(rs.next()){
                      arrList.add(rs.getString("first_name"));
                 }
            } catch(SQLException ex){}
            
            
            xml = "<?xml version=\"1.0\"?>";
            xml += "<contacts>";
            
            for(int i=0;i<arrList.size();i++) {
                 xml += "<contact>";
                 xml += "<fname>"+arrList.get(i)+"</fname>";
                 xml += "</contact>";
            }
            xml += "</contacts>";
            
            return xml;
            Any suggestions on why it doesn't work properly for multiple records ?
            .
            .
            .
            .
            .

            Edited by: shaolinf on Sep 3, 2008 8:54 PM
            • 3. Re: Newbie JDBC resultset help
              807589
              Put a println statement in the loop to see if you are processing multiple records.
              • 4. Re: Newbie JDBC resultset help
                807589
                Hi in your previous code you were doing
                rs.getString("user_id");
                and in the second example you are doing
                arrList.add(rs.getString("first_name"));
                Are you sure it is not raising any SQLException ?
                catch(SQLException ex){}
                because your catch block is empty, can you put a print out or a logging statement there to find out.
                • 5. Re: Newbie JDBC resultset help
                  800610
                  Thanks, very helpful. I have managed to get it working but I have a new problem. I want to print first name into xml node and the second in the preceding node. See code below:
                  try {
                       while(rs.next()){
                            arrList.add(rs.getString("first_name"));
                            arrList.add(rs.getString("last_name"));
                       }
                  } catch(SQLException ex){}
                  
                  
                  xml = "<?xml version=\"1.0\"?>";
                  xml += "<contacts>";
                  
                  for(int i=0;i<arrList.size();i++) {
                       xml += "<contact>";
                       xml += "<fname>"+arrList.get(i)+"</fname>";
                       xml += "<lname>"+arrList.get(i)+"</lname>";
                       xml += "</contact>";
                  }
                  xml += "</contacts>";
                  
                  return xml;
                  It should print something along the lines of:
                  <contacts>
                  <contact>
                  <fname>john</fname>
                  <lname>smith</lname>
                  </contact>
                  </contacts>
                  but instead it prints:
                  <contacts>
                  <contact>
                  <fname>john</fname>
                  <fname>john</fname>
                  </contact>
                  <contact>
                  <lname>smith</lname>
                  <lname>smith</lname>
                  </contact>
                  </contacts>
                  As for the SQLException, I don't know how to log it since Im still quite new to webdevelopment in java. If someone could give me some code/insight on how to log I would be grateful.
                  .
                  .
                  .
                  .
                  .
                  .
                  Edited by: shaolinf on Sep 4, 2008 8:34 AM
                  • 6. Re: Newbie JDBC resultset help
                    807589
                    There are lots of way to get what you want with a bit of hacking, but the best way is with a bit of object-oriented programming.

                    You want to read a list of contacts from the database, right? So where are the contact objects? All you've got is a list of strings which happens to alternate first name, last name, first name, last name... (or was that last name, first name, last name, first name...)

                    1. Define a Contact class. So far it has attributes firstName and lastName.
                    2. Instead of producing a List<String>, produce a List<Contact>.
                    3. Your Contact class could even define a method toXMLString to return a string like:
                    <contact><fname>John</fname><lname>Smith</lname></contact>
                    Then code to generate the entire String would look like:
                    StringBuilder xml = new StringBuilder("<?xml version=\"1.0\"?>");
                    xml.append("<contacts>");
                    List<Contact> contacts = ...
                    for(Contact contact : contacts) {
                        xml.append(contact.toXMLString());
                    }
                    xml.append("</contacts>");
                    • 7. Re: Newbie JDBC resultset help
                      800610
                      But wont I need to create new instances of the class everytime I want to add a new contact ? The list very dynamic and constantly changing..
                      • 8. Re: Newbie JDBC resultset help
                        807589
                        shaolinf wrote:
                        But wont I need to create new instances of the class everytime I want to add a new contact ? The list very dynamic and constantly changing..
                        So? Isn't creating objects a good thing? It certainly makes my chest puff out in pride.
                        • 9. Re: Newbie JDBC resultset help
                          800610
                          lol - You java guys are really strict when it comes to adhering to the standards :P - I cannot even use a 'g' for the start of a classname but get gunned down for it.

                          I am working on a class as I type. But before I start posting my problems about that can you give me some insight/code into how I can log exceptions in tomcat?
                          • 10. Re: Newbie JDBC resultset help
                            800610
                            Right, so I am confused as hell, having no idea what Im doing because I've never tried to create an array of class instances before, so excuse my appauling code!
                            List<ContactList> arrList = new List<ContactList>();
                            rs = DB.exQuery("SELECT * FROM user_contacts WHERE user_id='1'");
                            
                            try {
                                 while(rs.next()){
                                      arrList.setDetails(rs.getString("user_id"),rs.getString("first_name"),rs.getString("last_name"),rs.getString("add1"));
                                 }
                            } catch(SQLException ex){}
                            I am getting java.util.list is abstract and cannot be instantiated and setDetails method cannot be found(Its the method defined in the contacts class).
                            • 11. Re: Newbie JDBC resultset help
                              807589
                              You just can't make up names for methods and expect them to magically exist! Where did you get the idea that there was a setDetails method in List?

                              You need to create objects the old-fashioned way: with new:
                              List<Contact> contacts = new ArrayList<Contact>();
                              ResultSet rs = ...
                              try {
                                  while (rs.next()) {
                                      String something = rs.getString("something");
                                      int somethingElse = rs.getInt("somethingElse");
                                      Contact contact = new Contact();
                                      contact.setSomething(something);
                                      contact.setSomethingElse(somethingElse);
                                      contacts.add(contact);
                                      //or just contacts.add(new Contact( rs.getString("something"),  rs.getInt("somethingElse")));
                                  }
                              } finally {
                                  rs.close();
                              }
                              return contacts;
                              I prefer using setter methods to calling a constructor that takes the attribute values, because I usually end up with a dozen attributes.

                              Note that you must define class Contact, and any and all constructors or methods you call. They don't magically exist because you make a wish!
                              • 12. Re: Newbie JDBC resultset help
                                800610
                                Wow, thats an excellent way of doing it. Thanks! But how would I step through the array to access to getter method for each object ?
                                • 13. Re: Newbie JDBC resultset help
                                  807589
                                  shaolinf wrote:
                                  But how would I step through the array to access to getter method for each object ?
                                  What array? I don't understand what you are trying to do.
                                  • 14. Re: Newbie JDBC resultset help
                                    800610
                                    I want to beable to print out the contents of the array(id,name etc). Ive spent an hour trying to figure out how but its got me baffled.
                                    1 2 Previous Next