?
Solved

How to create a reusable connection object or connection pool ; JAVA, JDBC

Posted on 2005-03-12
19
Medium Priority
?
462 Views
Last Modified: 2012-08-14
Hi,

I have the class (sever) below that mostly performs database access and returns valures
with vectors to a client class.

As evident from the class code snippet, I used the conncetions and some common data types redundantly.

I believe I should be able to refactor the codes ... to make them robust and OO oriented with some help from the experts...

Please let me know how I can separate the connection objects..and common data structures that i used in two methods to make look more like OO patterns...

THanks...
_Esam


import java.sql.*;
import java.util.*;

public class myRMI_1
{
          // java.sql types needed for database processing
               private Connection connection;
                String url = "jdbc:odbc:Books";
                     String username = "anonymous";
                  String password = "guest";
        public myRMI_1(String name) //throws RemoteException
                {
                super();
                //try
                       //{
                        //Naming.rebind(name, this);
                        //}
                //catch(Exception e)
                        //{
                        //System.out.println("Exception occurred: " + e);
                        //}
                }
        public Vector getCoursesTaken(int ID) //throws RemoteException
                {
                                    // The URL specifying the Books database to which
                                      // this program connects using JDBC to connect to a
                                      // Microsoft ODBC database.
                                      Vector allRows = new Vector(); // a cpmmon data structure used in another method
                                      String[] aRow; // a cpmmon data structure used in another method

                                      // Load the driver to allow connection to the database
                                      try {
                                         Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

                                         connection = DriverManager.getConnection(
                                            url, username, password );
                                            String sqlQuery = "SELECT * FROM COURSES_TAKEN WHERE UST_ID =" + ID;
                                                                              Statement statement = connection.createStatement();
                                                                              ResultSet resultSet = statement.executeQuery(sqlQuery);
                                                                              int numColumns = resultSet.getMetaData().getColumnCount();
                                                                              while(resultSet.next())
                                                                              {
                                                                                aRow = new String[numColumns];
                                                                                for (int i = 0; i < numColumns; i++)
                                                                                  //ResultSet access is 1-based, arrays are 0-based
                                                                                  aRow[i] = resultSet.getString(i+1);
                                                                                allRows.addElement(aRow);
                                                                              }

                                      }
                                      catch ( ClassNotFoundException cnfex ) {
                                         System.err.println(
                                            "Failed to load JDBC/ODBC driver." );
                                         cnfex.printStackTrace();
                                         System.exit( 1 );  // terminate program
                                      }
                                      catch ( SQLException sqlex ) {
                                         System.err.println( "Unable to connect" );
                                         sqlex.printStackTrace();
                                         System.exit( 1 );  // terminate program
                                          }





                                return allRows;}
                              //}



      public Vector getRequiredCourses(int ID) //throws RemoteException
                      {
                                          // The URL specifying the Books database to which
                                            // this program connects using JDBC to connect to a
                                            // Microsoft ODBC database.
                                            //Connection connection;
                                            Vector allReqRows = new Vector();
                                            String[] aReqRow;

                                            // Load the driver to allow connection to the database
                                            try {
                                               Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

                                               connection = DriverManager.getConnection(
                                                  url, username, password );
                                                  String sqlQuery = "SELECT COURSE_ID FROM COURSES_TAKEN WHERE UST_ID =" + ID;
                                                                                    Statement statement = connection.createStatement();
                                                                                    ResultSet resultSet = statement.executeQuery(sqlQuery);
                                                                                    int numColumns = resultSet.getMetaData().getColumnCount();
                                                                                    while(resultSet.next())
                                                                                    {
                                                                                      aReqRow = new String[numColumns];
                                                                                      for (int i = 0; i < numColumns; i++)
                                                                                        //ResultSet access is 1-based, arrays are 0-based
                                                                                        aReqRow[i] = resultSet.getString(i+1);
                                                                                      allReqRows.addElement(aReqRow);
                                                                                    }

                                            }
                                            catch ( ClassNotFoundException cnfex ) {
                                               System.err.println(
                                                  "Failed to load JDBC/ODBC driver." );
                                               cnfex.printStackTrace();
                                               System.exit( 1 );  // terminate program
                                            }
                                            catch ( SQLException sqlex ) {
                                               System.err.println( "Unable to connect" );
                                               sqlex.printStackTrace();
                                               System.exit( 1 );  // terminate program
                                                }





                                return allReqRows;}

public boolean verify(int ID, String password){
      try{
            Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
            connection = DriverManager.getConnection(
            url, username, password );
            String sql = "SELECT UST_ID, PASSWORD FROM STUDENT WHERE UST_ID = ? and PASSWORD = ?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, ID);
            ps.setString(2, "password");
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                 // valid
                 System.out.println("WELCOME TO THE JUNGLE");
                  }
            else {
                // invalid
                System.out.println("GET LOST FRED");
                  }

            }


      catch ( ClassNotFoundException cnfex ) {
            System.err.println(
            "Failed to load JDBC/ODBC driver." );
            cnfex.printStackTrace();
            System.exit( 1 );  // terminate program
                                          }
      catch ( SQLException sqlex ) {
            System.err.println( "Unable to connect" );
            sqlex.printStackTrace();
            System.exit( 1 );  // terminate program
            }

            return false;

      }
                              //public Vector getOptionalCourses(){}
}
                              //public Vector getNeededRequiredCourses(){}
                              //public Vector getNeededOptionalCourses(){}




0
Comment
Question by:_Esam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 3
  • +1
19 Comments
 
LVL 29

Assisted Solution

by:bloodredsun
bloodredsun earned 80 total points
ID: 13523814
Is this a graded assignment?

As far as the connction goes, your best and most OO-orientated option would be to use a datasource. This creates a pool of re-usable connections that already exist when you request one. This eliminates the overhead for connection creation which is normally quite some time.

You also might consider making your datastuctures available to all methods and use ArrayList over a Vector for speed requirements.
0
 

Author Comment

by:_Esam
ID: 13523832
>> Is this a graded assignment?
What's this supoosed to mean?

I don't like genralized answer.
Because, perhaps I can find more on resources like this ones:
>>http://builder.com.com/5100-6388-1045042-2.html#Listing%20B

So, unless you provide some guidance, please no not exploit the question/answer

Thanks.
_Esam
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13523910
>> Is this a graded assignment?
>>What's this supoosed to mean?
I was politely asking you whether or not this was homework of some sort or a project where you will be graded on this work.

>>I don't like genralized answer.
If it is some sort of homework, a genralized answer is all I can give. We cannot give code examples for assignments. IF you can find resources like the one you've mentioned, what is stopping you from implementing this example DataSource code into your own code?

>>So, unless you provide some guidance, please no not exploit the question/answer
Two points, firstly I was offering guidance just maybe what you were expecting/wanting and secondly I hope that something was lost in the translation of your comment, as what you have posted could be construed as offensive to a professional trying to help.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:_Esam
ID: 13523936
>>what is stopping you from implementing this example DataSource code into your own code?
Of course, I didn't have all the time to go through the code and implement it.
So, I was hoping that experts can provide me with some quick guidance and illustrations.

What would be the point of asking ee if I understood and implemented codes from other resources?

_Esam.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 13523972
You can maybe look at caching to avoid frequent database access

>>public boolean verify(int ID, String password)

Can probably be applied on a cached collection


>>public Vector getCoursesTaken(int ID)

is a also good candidate for caching, as the data is not likely to be changed frequently.

Make sure you close DB resources after using them.

For connection pooling, see

http://jakarta.apache.org/commons/dbcp/
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13523984
_Esam, you haven't answered my questions about this being homework or not.

>>Of course, I didn't have all the time to go through the code and implement it.
>>What would be the point of asking ee if I understood and implemented codes from other resources?
What!? You are asking people to develop code for you rather than doing it yourself. Examples are given in the link you posted so there are your guidance and illustrations. As a one-time tutor, I can't see how you are going to learn if you do this. If you want people to code for you, I suggest you go to rentacoders.

I'm sorry but I do not feel I can help you any further as this question stands. You will have to try to understand and implement this code yourself (or at least show some evidence of effort) or wait for an expert who does not mind helping someone in your situation.

Best wishes
0
 
LVL 14

Accepted Solution

by:
sudhakar_koundinya earned 600 total points
ID: 13524059
As an example this will be helpful you to understand how we can break the connection and resultset objects.

We can improve it more. I am just giving idea


import java.sql.Connection ;
import java.sql.DriverManager ;

public class MyConnection
{
     private static Connection connection ;

     private MyConnection ()
     {
     }

     public static final Connection getConnection ( String jdbcDriver , String dataSource , String admin , String passcode )
     {
          if ( connection != null )
          {
          return connection ;
          }

          try
          {
          Class.forName ( jdbcDriver ) ;
          connection = DriverManager.getConnection ( dataSource , admin , passcode ) ;
          return connection ;
          }
          catch ( Exception ex )
          {
          ex.printStackTrace () ;
          }

          return null ;
     }

     public boolean Close ()
     {
          try
          {
          connection.close () ;
          connection = null ;
          return true ;
          }
          catch ( Exception ex )
          {
          ex.printStackTrace () ;
          connection = null ;
          return false ;
          }

     }

}





import java.sql.* ;
import java.util.* ;

public class MyResultSet
{
     private Connection connection ;

     private int numColumns = 0 ;

     private int numRows = 0 ;

     private Vector columns ;

     public MyResultSet ( Connection connection )
     {
          this.connection = connection ;
     }

     public Vector getData ( String sqlQuery )
     {
          ResultSet rs = null ;
          Statement stmt = null ;
          try
          {
          findColumnNames ( sqlQuery ) ;
          stmt = connection.createStatement () ;
          rs = stmt.executeQuery ( sqlQuery ) ;
          Vector rows = new Vector () ;
          while ( rs.next () )
          {
               Vector row = new Vector () ;
               for ( int i = 0 ; i < columns.size () ; i++ )
               {
               row.add ( rs.getObject ( ( String ) columns.get ( i ) ) ) ;
               }
               rows.add ( row ) ;

          }
          numRows = rows.size () ;
          stmt.close();
          rs.close();
          return rows;
          }
          catch ( Exception ex )
          {
         
          ex.printStackTrace () ;
          }
          return null ;
     }

     public int getColumnCount ()
     {
          return numColumns ;
     }

     public int getRowCount ()
     {
          return numRows ;
     }

     /**
      * findColumnNames
      */
     private void findColumnNames ( String sqlQuery )
         throws Exception
     {

          Statement stmt = connection.createStatement () ;
          ResultSet rs = stmt.executeQuery ( sqlQuery ) ;

          ResultSetMetaData rsmd = rs.getMetaData () ;
          numColumns = rsmd.getColumnCount () ;

          for ( int i = 1 ; i < numColumns + 1 ; i++ )
          {
          String columnName = rsmd.getColumnName ( i ) ;
          columns.add ( columnName ) ;

          }
          stmt.close();
          rs.close();
     }
}


Regards
Sudhakar
0
 

Author Comment

by:_Esam
ID: 13524076
>>(or at least show some evidence of effort)
I would suggest that you ask ee to give you access to my questions (that are answered)
- if you want to see my efforts.

_Esam
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 13524086
:))

cool down friends.
we are just here to share the knowledge. not to fight and blame each other.  

I know well abt bloodredsun and _esam. From past questions, I can say that  both are good as per my belief. Be as a friends

Brest Regards

0
 

Author Comment

by:_Esam
ID: 13524103
Sudhakar,
I respect your comment.
It's very encouraging for me.
I know that you answered few of my questions.
But, honestly, I don't know bloodredsun.
And I don't have to.

_Esam
0
 

Author Comment

by:_Esam
ID: 13524765
Hi Sudhakar,
Are you still there..??
I would like to continue with the question.
I was gone to do some errands..

_Esam
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 13525221
In order to optimise your code, it would be necessary to know in more detail how it would be used
0
 

Author Comment

by:_Esam
ID: 13525901
I had not paid much attention to connection pooling earlier...
I read the CEHJ's link on DBCP and some other resources...
But as I was developing the program....I realized that I was using many connections for just on program...not a good design...???

THe connections will be at the server program.
Clients will use access the datasources (resultsets) from the server program using RMI.
That's the reason I was using vectors since they are serializable...

_Esam
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 13525944
>>I realized that I was using many connections for just on program...not a good design...???

Not quite sure what you mean by that (even assuming that 'on program' should be 'one program')
0
 

Author Comment

by:_Esam
ID: 13526265
Yes.. I meant to say in one program...but perhaps I was wrong ...
I guess I was mixing things up with VB.NET stuffs...

Below I just have one connection used twice or how that works???


import java.sql.*;
import java.util.*;

public class myRMI_21
{
   private Connection connection;
   String url = "jdbc:odbc:Books";
   String username = "anonymous";
   String password = "guest";


   public myRMI_21(String name) //throws RemoteException
       {
       super();
       //try
       //{
       //Naming.rebind(name, this);
       //}
       //catch(Exception e)
       //{
       //System.out.println("Exception occurred: " + e);
       //}

   try{
      Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
      connection = DriverManager.getConnection(url, username, password );
      }

   catch ( ClassNotFoundException cnfex ) {
      System.err.println("Failed to load JDBC/ODBC driver." );
      cnfex.printStackTrace();
      System.exit( 1 );  // terminate program
      }

   catch ( SQLException sqlex ) {
      System.err.println( "Unable to connect" );
      sqlex.printStackTrace();
      System.exit( 1 );  // terminate program
            }

      }

   public Vector getCoursesTaken(int ID) //throws RemoteException
    {
       Vector allRows = new Vector();
       String[] aRow;

    try{
       String sqlQuery = "SELECT * FROM COURSES_TAKEN WHERE UST_ID =" + ID;
       Statement statement = connection.createStatement();
       ResultSet resultSet = statement.executeQuery(sqlQuery);
       int numColumns = resultSet.getMetaData().getColumnCount();

       while(resultSet.next())
      {
      aRow = new String[numColumns];
      for (int i = 0; i < numColumns; i++)
      aRow[i] = resultSet.getString(i+1);
      allRows.addElement(aRow);
      }
       }

      catch ( SQLException sqlex ) {
         System.err.println( "Unable to connect" );
         sqlex.printStackTrace();
         System.exit( 1 );
             }

      return allRows;
     }




  public Vector getRequiredCourses(int ID) //throws RemoteException
   {
      Vector allReqRows = new Vector();
      String[] aReqRow;

   try{
      String sqlQuery = "SELECT COURSE_ID FROM COURSES_TAKEN WHERE UST_ID =" + ID;
      Statement statement = connection.createStatement();
      ResultSet resultSet = statement.executeQuery(sqlQuery);
      int numColumns = resultSet.getMetaData().getColumnCount();

      while(resultSet.next())
        {
      aReqRow = new String[numColumns];
      for (int i = 0; i < numColumns; i++)
        aReqRow[i] = resultSet.getString(i+1);
        allReqRows.addElement(aReqRow);
        }
      }

   catch ( SQLException sqlex ) {
      System.err.println( "Unable to connect" );
      sqlex.printStackTrace();
      System.exit( 1 );  // terminate program
      }


   return allReqRows;
   }

}

//public boolean verify(int ID, String password){}
//public list getNeededRequiredCourses(){}
//public list getNeededOptionalCourses(){}

Looks little better than my previous version....

_Esam
0
 

Author Comment

by:_Esam
ID: 13570658
Hi CEHJ,
Assuming my program is like the one that i provided in the last comment.
How can I use connection pool?
How can I make the program more modular?
Let me know if i am clear on not?

Thanks.
_Esam
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 120 total points
ID: 13570677
Well the connection pool is normally used where you have multiple clients wishing to access a table. Is this the case?
0
 

Author Comment

by:_Esam
ID: 13570962
Hi CEHJ,
I understand now that I don't need to use a connection pool right now.
May be in the near future.
So, I was just trying to give it a headstart..:)

Thanks to you all.

_Esam
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 13571053
:-)
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Suggested Courses
Course of the Month13 days, 21 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question