Solved

What is wrong with my JDBC database?

Posted on 2009-05-15
11
330 Views
Last Modified: 2013-12-15
This is the original given code from my instructor.  Supposedly it is supposed to compile, however, I can't get it too and can not figure out why.

I have some things to add to it, but if I cant get it to compile in the first place then well...
I'd appreciate some help figuring out what I'm either not doing or what I'm doing wrong.

I added ojdbc14.jar to my project so I could connect to the database
I created a file in the src folder called parm.properties
here is contents:

CONN_URL=jdbc:oracle:thin:@localhost:1521:xe
DB_USERNAME=hr
DB_PASSWORD=hrpassword
DB_DRIVER=oracle.jdbc.driver.OracleDriver

package database_challenge;
 

import java.io.FileNotFoundException;

import java.io.IOException;

import java.util.Properties;
 

/**

 * Class file to pull parameters from a file

 * @author B_Acs

 *

 */

public class Parm {

	

	private static Properties props = null;

	

	static {

		

		try {

			props = new Properties();

			Class s = Class.forName("Database.Parm");

			props.load(s.getResourceAsStream("/parm.properties"));

		} catch (FileNotFoundException fnfe) {

			System.out.println(fnfe.getMessage());

		} catch (IOException ioe) {

			System.out.println(ioe.getMessage());

		} catch (ClassNotFoundException cnfe) {

			System.out.println(cnfe.getMessage());

		}

	}

	// empty constructor

	private Parm() {		

	}

	

	public static String getSystemSetting(String pVariable) {

		if (props != null) {

			return ((String) props.getProperty(pVariable));

		} else {

			System.out.println("system variables were never initialized.");

			return null;

		}

	}

}
 

//***********************************************************************************************
 

package database_challenge;
 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;
 

/**

 * Class used to connect allow for connections

 * to the Oracle database

 * @author B_Acs

 *

 */

public class ConnectionManager {

	// setting up static variables for use with the connection

	private static final String DRIVER = Parm.getSystemSetting("DB_DRIVER");

	private static final String CONN_URL = Parm.getSystemSetting("CONN_URL");

	private static final String USER_NAME = Parm.getSystemSetting("DB_USERNAME");

	private static final String PASSWORD = Parm.getSystemSetting("DB_PASSWORD");
 

	// empty constructor

	private ConnectionManager() {

	}

	

	/**

	 * Method to make a connection to the database

	 * @return

	 * @throws SQLException

	 */

	public static Connection makeConnection() throws SQLException {

		

		// initializing connection

		Connection conn = null;

		

		try {

			Class.forName(DRIVER);

			conn = DriverManager.getConnection(CONN_URL, USER_NAME, PASSWORD);

			conn.setAutoCommit(false);

		} catch (ClassNotFoundException cnfe) {

			System.out.println(cnfe.getMessage());

		}

		

		return conn;

	}
 

}
 

//*******************************************************************************************
 

package database_challenge;
 

/**

 * Wrapper class used to set and retrieve 

 * data about an employee

 * @author B_Acs

 *

 */

public class EmployeesWrapper {
 

	private String firstName;

	private String lastName;

	private String email;

	private String phoneNumber;

	private String hireDate;

	private double salary;

	

	// accessor and mutator methods (getters and setters)

	public String getFirstName() {

		return firstName;

	}

	public void setFirstName(String firstName) {

		this.firstName = firstName;

	}

	public String getLastName() {

		return lastName;

	}

	public void setLastName(String lastName) {

		this.lastName = lastName;

	}

	public String getEmail() {

		return email;

	}

	public void setEmail(String email) {

		this.email = email;

	}

	public String getPhoneNumber() {

		return phoneNumber;

	}

	public void setPhoneNumber(String phoneNumber) {

		this.phoneNumber = phoneNumber;

	}

	public String getHireDate() {

		return hireDate;

	}

	public void setHireDate(String hireDate) {

		this.hireDate = hireDate;

	}

	public double getSalary() {

		return salary;

	}

	public void setSalary(double salary) {

		this.salary = salary;

	}

		

}
 

//******************************************************************************************
 

package database_challenge;
 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;
 

/**

 * Class to communicate to the database

 * and display results.

 * @author B_Acs

 *

 */

public abstract class DatabaseService {
 

	// static variable used for our select statement

	// helps to prevent sql injection

	private static final String SELECT_EMPLOYEES;

	

	/**

	 * Method to return an array list of employees

	 * @return

	 */

	public static ArrayList getEmployees() {

		

		// initializing connection

		Connection conn = null;

		// initializing statement

		PreparedStatement stmt = null;

		// initializing result set

		ResultSet rs = null;

		// initializing return value

		ArrayList returnVal = new ArrayList();

		

		try {

			// getting connection

			conn = ConnectionManager.makeConnection();

			// setting the statement

			stmt = conn.prepareStatement(SELECT_EMPLOYEES);

			// filling the result set with results from the query

			rs = stmt.executeQuery();

			// looping through the result set

			while (rs.next()) {

				// creating an employee object

				EmployeesWrapper ew = new EmployeesWrapper();

				// setting the first name in the object

				ew.setFirstName(rs.getString("FIRST_NAME"));

				// setting the last name in the object

				ew.setLastName(rs.getString("LAST_NAME"));

				// adding the object to the array list

				returnVal.add(ew);

			}

			// closing the statement

			stmt.close();

		} catch (SQLException esql) {

			System.out.println(esql.getMessage());

		} finally {

			try {

				if(conn != null) {

					conn.close();

					conn=null;

				}

			} catch (SQLException esql) {

				System.out.println(esql.getMessage());

			}

		}

		// returning the array list

		return returnVal;

		

	}

	

	// static block of code for the sql statement

	static {

		

	StringBuffer tempBuffer = null;

	

	tempBuffer = new StringBuffer();

	tempBuffer.append("SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES");

	SELECT_EMPLOYEES = tempBuffer.toString();

	

	}

	

	/**

	 * Main method to test our retrieval of data from the database

	 * @param args

	 */

	public static void main(String[] args) {

		// initializing an array list, filling it with a list 

		// of employee objects from the database.

		ArrayList e = DatabaseService.getEmployees();

		// looping through the array list

		for(int x = 0; x < e.size(); x++) {

			// getting the employee object from the array list one at a time

			EmployeesWrapper ew = (EmployeesWrapper) e.get(x);

			// printing to the screen the employee first and last names

			System.out.println(ew.getFirstName() + " " + ew.getLastName());

		}

	}

}

Open in new window

0
Comment
Question by:b_acs
  • 5
  • 4
  • 2
11 Comments
 

Author Comment

by:b_acs
ID: 24399566
Oh yeah here is my results when I run this:

Database.Parm
Exception in thread "main" java.lang.NullPointerException
      at java.lang.Class.forName0(Native Method)
      at java.lang.Class.forName(Unknown Source)
      at database_challenge.ConnectionManager.makeConnection(ConnectionManager.java:35)
      at database_challenge.DatabaseService.getEmployees(DatabaseService.java:38)
      at database_challenge.DatabaseService.main(DatabaseService.java:91)
0
 
LVL 3

Expert Comment

by:hazgoduk
ID: 24400317
It's really easy to get content from a properties file. Create a file called test.properties in the folder containing build.xml.

Have just the following text in it.

test = abc
        Properties properties = new Properties();

        try

        {

            properties.load(new FileInputStream("test.properties"));
 

            String test = properties.getProperty("test");
 

            System.out.println("test: |"+test+"|");

        }

        catch(Exception e)

        {

            e.printStackTrace();

        }

Open in new window

0
 
LVL 3

Expert Comment

by:hazgoduk
ID: 24400388
Sorry, totally missed your bit at the top with the file contents...
        Properties properties = new Properties();

        try

        {

            properties.load(new FileInputStream("test.properties"));
 

            String CONN_URL = properties.getProperty("CONN_URL");

            System.out.println("CONN_URL: |"+CONN_URL+"|");
 

            String DB_USERNAME = properties.getProperty("DB_USERNAME");

            System.out.println("DB_USERNAME: |"+DB_USERNAME+"|");
 

            String DB_PASSWORD = properties.getProperty("DB_PASSWORD");

            System.out.println("DB_PASSWORD: |"+DB_PASSWORD+"|");
 

            String DB_DRIVER = properties.getProperty("DB_DRIVER");

            System.out.println("DB_DRIVER: |"+DB_DRIVER+"|");

        }

        catch(Exception e)

        {

            e.printStackTrace();

        }

Open in new window

0
 
LVL 16

Accepted Solution

by:
warturtle earned 400 total points
ID: 24402917
The reason for why the NullPointerException is appearing is because it cannot find the file required and hence, cannot load it to read from. Make sure that the properties file is in the correct place.

Class.forName(DRIVER); is the specific line generating the Exception.
0
 

Author Comment

by:b_acs
ID: 24404167
Where should the properties file be placed?  I have it in the same folder as the class files.....
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:b_acs
ID: 24404173
LOL   That was the problem.  I was supposed to have it the source folder but not included in the package.

It is working now, thank you!
0
 
LVL 16

Expert Comment

by:warturtle
ID: 24404207
Hahaha... all of us make some silly mistakes while coding, but its good to see that it has been resolved. Feel free to close the ticket.
0
 

Author Comment

by:b_acs
ID: 24404295
Ok I have a better question for this same assignment.
I need to put the list in alphabetical order by lastname, firstname.
Now I know I need to put in an ORDER BY clause but I am unsure as to where it would go to work correctly.
If you want, I  will post a new question.
0
 
LVL 16

Assisted Solution

by:warturtle
warturtle earned 400 total points
ID: 24404369
The statement that you need to modify is this one: tempBuffer.append("SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES");

It needs changing to:

tempBuffer.append("SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES ORDER BY LAST_NAME, FIRST_NAME ASC");

Although we don't really need to specify ASC because it will sort the records into Ascending order by default, but I did it just for clarity.

Hope it helps.
0
 

Author Closing Comment

by:b_acs
ID: 31582096
Thank you!  I just missed that Select statement..... I knew that's what I was looking for but I guess I've just been looking at too much code recently....lol
0
 
LVL 16

Expert Comment

by:warturtle
ID: 24405753
Glad to be of assistance :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Upgrading Tomcat – There are a couple of methods to upgrade Tomcat is to use The Apache Installer is to download and unzip and run the services.bat remove|install Tomcat6 Because of the App that we are working with, we can only use Tomcat 6.…
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…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now