Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

What is wrong with my JDBC database?

Posted on 2009-05-15
11
Medium Priority
?
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
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 and create keystrokes in Netbeans IDE 8.0 for Windows.
Suggested Courses

705 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