need advice regarding coding technique

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.

Code:

---

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)

Code:

---

//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.

Google "DAO layer".

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.

Quote:

---

Originally Posted by RamyaSivakanth

... 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.

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:

Code:

---

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.

---