Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2110
  • Last Modified:

New to Eclispe and Java - Need to create a connection Class that can then be used in all the unit testing

I have just finished the online tutorial on eclipse. I have also gone through a book or two on Java 2.
I can create test methods and test when there is no external data base connection involved.
What I want to do
Create a connection Class using the JDBC driver which could be included in all other classes.
I have created a small class with a get method and set method to retrieve records from a data base and to update one of the selected records.

I do not want to put the connection information in every class that I write. I will become a maintenance nightmare.
Once this is created I want use the Junit test script to create the various test cases and through that create the class
here is the connection class I wrote
package vgio4d10s;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Connect {
      public static void main(String[] arguments) {
        String data = "jdbc:odbc:RaxiD04";
       try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection conn = DriverManager.getConnection(
                data, "System10S", "system123m");
             Statement st = conn.createStatement();
             ResultSet rec = st.executeQuery(
                     "SELECT FNM, LNM, MIN, TITLE, MAX_EDU " +
                     "FROM pep " );
                 System.out.println("First Name /t Last name/t TITLE /t User ID " +
                     "People in PEP file ");
                 while(rec.next()) {
                     System.out.println(rec.getString("FNM")+  "\t"
                             + rec.getString("LNM")+  "\t"
                             + rec.getString("MIN")+  "\t"
                             + rec.getString("TITLE")+  "\t"
                             + rec.getString("MAX_EDU")    );
                 }
                 st.close();
           
        } catch (SQLException s) {
            System.out.println("SQL Error: " + s.toString() + " "
                + s.getErrorCode() + " " + s.getSQLState());
        } catch (Exception e) {
            System.out.println("Error: " + e.toString()
                + e.getMessage());
        }
    }

}


Now What I want to do is Make this inot a standalone Connection class with not results sets
then Write a new test case for people class
Have the various test which would force the creation of get and set methods along with others
once I have a working example I would then be able to build all the test cases and clasess needed for my application .
SO I Need help - to create a class for the connection
then an example preferably using the table pep to create a class pep (people) show how to make this class use the connection class
thanks in Advance
0
su2117
Asked:
su2117
  • 8
  • 2
  • 2
  • +1
2 Solutions
 
ebertkCommented:
I would start with a class that can manage your connections.  You will create an instatnce of this class each time you wish to connect to the database, and let it handle the connections and execution of SQL querys that you pass it.  

Here's a very simple example (note it uses JDBC ODBC driver, not your specific database driver):

import java.sql.*;

public class DatabaseConnection {
   Connection connection = null;
   Statement st = null;

      public KJEDatabase() {
        try {
       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
       connection = DriverManager.getConnection("jdbc:odbc:KJE", "", "");
             st = connection.createStatement();
        }
     catch (Exception e) {  
        System.err.print("Exception connecting to database: ");  
        System.err.println(e.getMessage());
     }  
      }

      public boolean update(String sStatement) {
        boolean bSuccess =  true;
        try {
       st.executeUpdate(sStatement);
        }
     catch (Exception e) {  
        System.err.print("Exception updating into database: ");  
             bSuccess =  false;
     }  
         return bSuccess;
      }

      public ResultSet select(String sSelect) throws SQLException{

           return st.executeQuery(sSelect);

      }
   
   public boolean close(){
        boolean bSuccess =  true;
        try {
       connection.close();
        }
     catch (Exception e) {  
        System.err.print("Exception disconnecting from database: ");  
        System.err.println(e.getMessage());  
              bSuccess = false;
     }  
        return bSuccess;

      }
}
0
 
su2117Author Commented:
Thank you for responding
Please show me what I am doing wrong in the new class called pep (for people) where I want to connect using the class created above with a name VGIConnection I am hoping to see this work so I can see where I went wrong. I have marked the errors as shown by Eclispe

Thanks once again


package vgio4d10s;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class Pep {
      //line below has an error st cannot be resolved
            ResultSet rec = st.executeQuery(
                "SELECT FNM, LNM, MIN, TITLE, MAX_EDU " + "FROM pep " );
            // line below has error multiple markers at this line - syntax error on token(s), misplaced construct(s)
            // syntax error on token(s) misplaced construct(s)
    System.out.println("First Name /t Last name/t TITLE /t User ID " +
        "People in PEP file ");
    while(rec.next()) {
        System.out.println(rec.getString("FNM")+  "\t"
                + rec.getString("LNM")+  "\t"
                + rec.getString("MIN")+  "\t"
                + rec.getString("TITLE")+  "\t"
                + rec.getString("MAX_EDU")    );
    }
    st.close();
}
}
0
 
su2117Author Commented:
I even tried the one below which I believe creates a new instance of the class VgiConnection
in the pep class. however the error on st still persists



package vgio4d10s;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class Pep {
      VgiConnection nconn = new VgiConnection();
      
      //line below has an error st cannot be resolved
            ResultSet rec = st.executeQuery(
                "SELECT FNM, LNM, MIN, TITLE, MAX_EDU " + "FROM pep " );
            // line below has error multiple markers at this line - syntax error on token(s), misplaced construct(s)
            // syntax error on token(s) misplaced construct(s)
    System.out.println("First Name /t Last name/t TITLE /t User ID " +
        "People in PEP file ");
    while(rec.next()) {
        System.out.println(rec.getString("FNM")+  "\t"
                + rec.getString("LNM")+  "\t"
                + rec.getString("MIN")+  "\t"
                + rec.getString("TITLE")+  "\t"
                + rec.getString("MAX_EDU")    );
    }
    st.close();
}
}
0
Industry Leaders: 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!

 
su2117Author Commented:
New comment was added after I tried a few things
I seem to have got it to work - but my concern is did I do it the correct way ? if not please correct the example If yes is there are still a few minor errors I have marked them with comments either above or below the code that was flagged by Eclipse as an error
Once again thank you for you help !!

package vgio4d10s;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class Pep {
      VgiConnection conn = new VgiConnection();
      Object Statement;
      Statement st = ((Connection) conn).createStatement();
      //line below has an error st cannot be resolved  - this was resolved when I added the local variable above
            ResultSet rec = st.executeQuery(
                "SELECT FNM, LNM, MIN, TITLE, MAX_EDU " + "FROM pep " );
            // line below has error multiple markers at this line - syntax error on token(s), misplaced construct(s)
            // syntax error on token(s) misplaced construct(s) - this dissapeared when I added {} around the code however it brought up a new error listed after the line of code
    //Syntax error on token "println", = expected after this token
            {System.out.println("First Name /t Last name/t TITLE /t User ID " +
        "People in PEP file ")};
            // the semi colon above is highlighted as an error
            //Syntax error on token ";", { expected after this token
    while(rec.next()) {
        System.out.println(rec.getString("FNM")+  "\t"
                + rec.getString("LNM")+  "\t"
                + rec.getString("MIN")+  "\t"
                + rec.getString("TITLE")+  "\t"
                + rec.getString("MAX_EDU")    );
    }
    st.close();
}
}



Thanks
Su2117
0
 
su2117Author Commented:
Sorry I was just looking at my code and found that one of the fields that need to be displayed was not in the list in the selection when I added it I found that it still did not show up the field is of the type nVARCHAR and is of length 20 in an oracle data base In addition to the erros listed preveiously please let me know how to fix this
Thanks
package vgio4d10s;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class Pep {
      VgiConnection conn = new VgiConnection();
      Object Statement;
      Statement st = ((Connection) conn).createStatement();
      //line below has an error st cannot be resolved
            ResultSet rec = st.executeQuery(
                "SELECT FNM, LNM, MIN, TITLE, MAX_EDU " + "FROM pep " );
            // line below has error multiple markers at this line - syntax error on token(s), misplaced construct(s)
            // syntax error on token(s) misplaced construct(s)
    //Syntax error on token "println", = expected after this token
            {System.out.println("First Name /t Last name/t Middle Initial /t TITLE /t Max Education /t User ID " +
        "People in PEP file ")};
            // the semi colon above is highlighted as an error
            //Syntax error on token ";", { expected after this token
    while(rec.next()) {
        System.out.println(rec.getString("FNM")+  "\t"
                + rec.getString("LNM")+  "\t"
                + rec.getString("MIN")+  "\t"
                + rec.getString("TITLE")+  "\t"
                + rec.getLong("PEP_ID)  + "\t"
                // the Field PEP_ID is a Nvarchar and I am not able display this on the console when
                // I run under open dialogue to the console
                + rec.getString("MAX_EDU")    );
    }
    st.close();
}
}


0
 
su2117Author Commented:
tried to resolve it still get errors  please ignore past two post they were not working I was mistaken in the way I was executing a run as
package vgio4d10s;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Pep2 {
      public static void main(String[] arguments) {
            VgiConnection conn = new VgiConnection();
            Object Statement;
            
            Statement st = null;
            ResultSet rec = null;
            
            try {
                  st = ((Connection) conn).createStatement();
            } catch (SQLException e) {
                  // TODO Auto-generated catch block
                  e.printStackTrace();
            }
            //line below has an error st cannot be resolved
                  try {
                        rec = st.executeQuery(
                                "SELECT FNM, LNM, MIN, TITLE, PEP_ID, NICK_NM, MAX_EDU FROM pep " );
                  } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                  }
                  // line below has error multiple markers at this line - syntax error on token(s), misplaced construct(s)
                  // syntax error on token(s) misplaced construct(s)
          //Syntax error on token "println", = expected after this token
                  System.out.println("First Name" + "/t"+"Last Name" + "/t" +"Middle Initial"+"/t" + "TITLE " + "/t" + "Max Education" + "/t" + "User ID People in PEP file ");
                  // the semi colon above is highlighted as an error
                  //Syntax error on token ";", { expected after this token
                  
                  try {
                        while(rec.next()) {
                            System.out.println(rec.getString("FNM")+  "\t"
                                        + " people ID"+ rec.getString("LNM")+  "\t"
                                    + rec.getString("MIN")+  "\t"
                                    + rec.getString("TITLE")+  " ALong " + "\t"
                                    + rec.getLong("PEP_ID")  + "  ADouble " + "\t"
                                    + rec.getDouble("PEP_ID")  + " ANvarchar " + "\t"
                                    + rec.getNCharacterStream("PEP_ID")  + " Nick Name " +"\t"
                                    // the Field is a Nvarchar and I am not able display this on the console when
                                    // I run under open dialogue to the console
                                    + rec.getString("MAX_EDU")   + "\t"
                                    + rec.getString("NICK_NM")    );
                        }
                  } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                  }
                try {
                        st.close();
                  } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                  }
                  
                  
      }

}

error seen on console
By the way is there a way to watch a variable and use "step trough so watch what happens  

Exception connecting to database: [Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied
Exception in thread "main" java.lang.ClassCastException: vgio4d10s.VgiConnection cannot be cast to java.sql.Connection
      at vgio4d10s.Pep2.main(Pep2.java:17)
0
 
KuldeepchaturvediCommented:
if you are using the class VgiConnection to manage your connection then you do not have to create the connection again in your main class..

you will be using the methods from VgiConnection to get the job done..

so your main class shd look something like below..

package vgio4d10s;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Pep2 {
      public static void main(String[] arguments) {
            VgiConnection conn = new VgiConnection();
            ResultSet rec = null;
           
                  try {
                        rec = conn.select(
                                "SELECT FNM, LNM, MIN, TITLE, PEP_ID, NICK_NM, MAX_EDU FROM pep " );

                  } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                  }
                  System.out.println("First Name" + "/t"+"Last Name" + "/t" +"Middle Initial"+"/t" + "TITLE " + "/t" + "Max Education" + "/t" + "User ID People in PEP file ");
                 
                  try {
                        while(rec.next()) {
                            System.out.println(rec.getString("FNM")+  "\t"
                                        + " people ID"+ rec.getString("LNM")+  "\t"
                                    + rec.getString("MIN")+  "\t"
                                    + rec.getString("TITLE")+  " ALong " + "\t"
                                    + rec.getLong("PEP_ID")  + "  ADouble " + "\t"
                                    + rec.getDouble("PEP_ID")  + " ANvarchar " + "\t"
                                    + rec.getNCharacterStream("PEP_ID")  + " Nick Name " +"\t"
                                    // the Field is a Nvarchar and I am not able display this on the console when
                                    // I run under open dialogue to the console
                                    + rec.getString("MAX_EDU")   + "\t"
                                    + rec.getString("NICK_NM")    );
                        }
                  } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                  }
                try {
                        conn.close();
                  } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                  }
                 
                 
      }

}

this code may have some syntax errors here & there but they shd be easy to fix.
0
 
su2117Author Commented:
package vgio4d10s;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Pep3 {
      public static void main(String[] arguments) {
            VgiConnection conn = new VgiConnection();
            ResultSet rec = null;
           
                  try {
                        rec = conn.select(
                                "SELECT FNM, LNM, MIN, TITLE, PEP_ID, MAX_EDU FROM pep " );

                  } catch (SQLException e) {
                        System.out.println("SQL Error: " + e.toString() + " "
                              + e.getErrorCode() + " " + e.getSQLState());e.printStackTrace();
                  }
                  System.out.println("First Name" + "/t"+"Last Name" + "/t" +"Middle Initial"+"/t" + "TITLE " + "/t" + "Max Education" + "/t" + "User ID People in PEP file ");
                 
                  try {
                        while(rec.next()) {
                            System.out.println(rec.getString("FNM")+  "\t"
                                        + " people ID"+ rec.getString("LNM")+  "\t"
                                    + rec.getString("MIN")+  "\t"
                                    + rec.getString("TITLE")+ " ANvarchar " + "\t"
                                    + rec.getString("PEP_ID")  + " Nick Name " +"\t"
                                    // the Field is a Nvarchar and I am not able display this on the console when
                                    // I run under open dialogue to the console
                                    + rec.getString("MAX_EDU")    );
                        }
                  } catch (SQLException e) {
                        System.out.println("SQL Error: " + e.toString() + " "
                              + e.getErrorCode() + " " + e.getSQLState());e.printStackTrace();
                             
                  }
                conn.close();
                 
                 
      }

}

Thanks that Worked - as the two of you have collaborated to give me the solution I would like to split the points between the two of you. Also before I close it I would like to ask you if these lines of code are considered proper or if they can be improved. Please just point to the section and what all I should consider so I can put some of my own thought into it. that way I will be able to learn

Thanks

Su2117
Please Kuldeepchaturvedi: and ebertk: please let me know your thoughts on the split ( I am leaning towards a 50/50) Thanks
0
 
su2117Author Commented:
One other thing specifically to the two of you who have answerd
How would I write a junit test for the VgiConnection class ?
Thanks a lot
Su2117
0
 
KuldeepchaturvediCommented:
Well it is pretty much a standard method for database connections for beginers. you can later on think about using connection pooling .

also make sure that every where when you are done accessing the database, call the close method of your class ( to make sure that the database connections are being closed properly and not being left hanging).

as far Junit is concerned.. here is the link that you can use.

http://junit.sourceforge.net/doc/cookbook/cookbook.htm
0
 
su2117Author Commented:
Those reviewing this to help you further
Please look at the comment from ebertk: on how to setup the connection then look at the solution given by
Kuldeepchaturvedi: the two together will give you the full solution.
The reason I am documenting this
1. to reduce the time taken by future readers to solve their connection query
2, to confirm that this solution actually worked
3. To let the Coordinators know that there are a lot (forced accept solutions in the knowledge database that are truely in complete )
Thanks
0
 
Vee_ModCommented:
Points changed by Asker request.
http://www.experts-exchange.com/Q_22892649.html



Vee_Mod
Experts Exchange Moderator
0
 
ebertkCommented:
One thing in my sample code that needs to be fixed is the public construtor should be:

DatabaseConnection

not

KJEConnection

This error was caused when I tried to make it more generic.  I would also beef up the error handling, otherwise it is a good a start!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now