Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

What is wrong with my JDBC database?

Posted on 2009-05-15
11
Medium Priority
?
339 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
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!

 
LVL 16

Accepted Solution

by:
warturtle earned 1600 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
 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
Suggested Courses

824 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