asked on

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

SOLUTION

ebertk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

su2117

ASKER

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

su2117

ASKER

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

su2117

ASKER

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

su2117

ASKER

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

su2117

ASKER

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)

ASKER CERTIFIED SOLUTION

Kuldeepchaturvedi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

su2117

ASKER

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

su2117

ASKER

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

Kuldeepchaturvedi

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

su2117

ASKER

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

Vee_Mod

Points changed by Asker request.
https://www.experts-exchange.com/questions/22892649/need-to-split-the-points-and-did-not-get-a-chance-to-do-so.html

Vee_Mod
Experts Exchange Moderator

ebertk

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!