We help IT Professionals succeed at work.

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

su2117
su2117 asked
on
2,295 Views
Last Modified: 2013-12-15
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
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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();
}
}

Author

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();
}
}

Author

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

Author

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();
}
}


Author

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)
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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

Author

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

Author

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

Commented:
Points changed by Asker request.
https://www.experts-exchange.com/Q_22892649.html



Vee_Mod
Experts Exchange Moderator

Commented:
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!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.