Solved

What is wrong with my JDBC database?

Posted on 2009-05-15
11
328 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

706 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

18 Experts available now in Live!

Get 1:1 Help Now