need advice regarding coding technique

[Basit56]

Hi to all,
Hope you all will be fine.Actually i need advice from you people.I have a database class which execute a query.For this first i made database connection, then using connection executed query and got result.Fine. Now i have to execute 3 more queries so what i did that i put a method in which i again call the database connection method and execute the query so my code is now look like this.

public void startDatabase(){
     emailAndMailbox = emailAndMailboxRows();
 }
  public void navigatingRows(){
      try{
          //System.out.println("email\t\t\t\t\t" + "mailbox\t\t" + "fullname");
          while(emailAndMailbox.next()){
              Voicemail_conf voicemail = getEmailAndMailbox(emailAndMailbox);
              //voicemail_conf.showemailAndMailboxes();
          }
      }catch(SQLException e){
          System.out.println(e.getMessage());
      }
  }

     public String actualMailbox(String mailfrom){
         System.out.println("In database actualMalbox()");
         Connection con2 = getConnection();
          try{

            Statement s = con2.createStatement();
            String select = "SELECT mailbox FROM voicemail_conf WHERE email=" + '"' +
                    mailfrom + '"';
            System.out.println("The actual query is: " + select);
            System.out.println("just before declaring Resultset");
            ResultSet row;
            System.out.println("Just before execute query and after declaring" +
                    "Resiltset");
            row = s.executeQuery(select);
            System.out.println("query executed");
            while(row.next()){
                System.out.println("Entering while loop");
                String realMailbox = row.getString("mailbox");
                System.out.println("mailbox for basit is: " + realMailbox);
                return realMailbox;
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return null;
     }

     //Method that returns email and mailbox column from database
     private ResultSet emailAndMailboxRows(){
      
        //Method that returns datebase connection
        Connection con = getConnection();
        try{
           
            Statement s = con.createStatement();
            String select = "SELECT mailbox, email, fullname FROM voicemail_conf ORDER BY" +
                    " mailbox, email, fullname";
            System.out.println("The actual query for mailbox, email and fullname" +
                    "is: " + select);
            ResultSet rows;
            rows = s.executeQuery(select);
            return rows;
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return null;
    } // end of getEmailAndMailbox

  private Voicemail_conf getEmailAndMailbox(ResultSet emailAndMailbox){
        try{
            String email = emailAndMailbox.getString("email");
            //System.out.println("In database to check value of email: " + email );
            setEmailFromDatabase(email);
            String mailbox = emailAndMailbox.getString("mailbox");
            setMailboxFromDatabase(mailbox);
            String fullname = emailAndMailbox.getString("fullname");
            setFullnameFromDatabase(fullname);
            voicemail_conf = new Voicemail_conf(email, mailbox, fullname);
            return voicemail_conf;
            
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return null;

    } //end of getEmailAndMailbox method

And i call it trough(This is another class)

//Default Access i.e, only classes with in the same package can access to each
    //other
     String splitFullNmae(){
       String[] temp;
       temp = voice.getFullname().split(" ");
       return temp[0].substring(0, 1).toUpperCase() + temp[0].substring(1).toLowerCase();
    }

     String assignCallerID(){

        if(callerIDRetAttach.getMailFrom().contains("@netkarachi.com")){
            String callerIDmailbox = callerIDDatabase.actualMailbox(callerIDRetAttach.getMailFrom());
            System.out.println("CallerID mailbox after executing actualMailbox()" +
                    "method of database is: " + callerIDmailbox);
            return callerIDmailbox;
         }else{
            System.out.println("email not exist in NetKarachi database ");
         }

The question is, it is quite unusual that if i have to execute query, every time i constitute a method . If you looked the second code then in the split method i also suppose to write a method that call another method in the database class and execute a query that return fullname and then it will split and do the same one more time for another class.

So i want to ask how i organize my code so instead of cooking method every time, can it happen that all queries are executed only in one method and then whatever query result i need, i got it and return back it to its desired place.

Thank you.

[r035198x]

Google "DAO layer".

[RamyaSivakanth]

You take out the getConnection() method outside the emailAndMailboxRows and declare it outside during during initialization .No need to call every time...it will affect the performance.

[r035198x]

... declare it outside during during initialization .No need to call every time...it will affect the performance.

Not advisable. Get a connection, use it and close it in the smallest scope possible. Keeping connections open for a long time is not a good idea.

[Tolls]

You're quite likely going to hit db resource problems with that lot. You're returning resultsets, and therefore not closing them...or the statements associated with them.

The normal flow is:

Connection con = null;
Statement st = null;
Resultset rs = null;
try {
...
do the querying in here, including turning the resultset that's returned into something
other than a resultset.
...
} catch (Whatever exceptions need catching) {
} finally {
    close your resources (rs, then st, then con).
}
return your result(s) as some sort of business objects.