[CODE}
/*
* trackerDAO.java
*
* Created on July 16, 2006, 9:18 AM
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
package my.app.db;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
*
* @author John O'Conner
*/
public class trackerDAO {
/**
* Creates a new instance of trackerDAO
*/
public trackerDAO() {
this("DefaultTeamStock");
}
public trackerDAO(String teamStockName) {
this.dbName = teamStockName;
setDBSystemDir();
dbProperties = loadDBProperties();
String driverName = dbProperties.getProperty("derby.driver");
loadDatabaseDriver(driverName);
if(!dbExists()) {
createDatabase();
}
}
private boolean dbExists() {
boolean bExists = false;
String dbLocation = getDatabaseLocation();
File dbFileDir = new File(dbLocation);
if (dbFileDir.exists()) {
bExists = true;
}
return bExists;
}
private void setDBSystemDir() {
// decide on the db system directory
String userHomeDir = System.getProperty("user.dir", ".");
String systemDir = userHomeDir + "/teamstock";
System.setProperty("derby.system.home", systemDir);
// create the db system directory
File fileSystemDir = new File(systemDir);
fileSystemDir.mkdir();
}
private void loadDatabaseDriver(String driverName) {
// load Derby driver
try {
Class.forName(driverName);
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
}
private Properties loadDBProperties() {
InputStream dbPropInputStream = null;
dbPropInputStream = trackerDAO.class.getResourceAsStream("Configuration.properties");
dbProperties = new Properties();
try {
dbProperties.load(dbPropInputStream);
} catch (IOException ex) {
ex.printStackTrace();
}
return dbProperties;
}
private boolean createTables(Connection dbConnection) {
boolean bCreatedTables = false;
Statement statement = null;
try {
statement = dbConnection.createStatement();
statement.execute(strCreateAddressTable);
bCreatedTables = true;
} catch (SQLException ex) {
ex.printStackTrace();
}
return bCreatedTables;
}
private boolean createDatabase() {
boolean bCreated = false;
Connection dbConnection = null;
String dbUrl = getDatabaseUrl();
dbProperties.put("create", "true");
try {
dbConnection = DriverManager.getConnection(dbUrl, dbProperties);
bCreated = createTables(dbConnection);
} catch (SQLException ex) {
}
dbProperties.remove("create");
return bCreated;
}
public boolean connect() {
String dbUrl = getDatabaseUrl();
try {
dbConnection = DriverManager.getConnection(dbUrl, dbProperties);
stmtSaveNewRecord = dbConnection.prepareStatement(strSaveAddress, Statement.RETURN_GENERATED_KEYS);
stmtUpdateExistingRecord = dbConnection.prepareStatement(strUpdateAddress);
stmtGetAddress = dbConnection.prepareStatement(strGetAddress);
stmtDeleteAddress = dbConnection.prepareStatement(strDeleteAddress);
isConnected = dbConnection != null;
} catch (SQLException ex) {
isConnected = false;
}
return isConnected;
}
private String getHomeDir() {
return System.getProperty("user.home");
}
public void disconnect() {
if(isConnected) {
String dbUrl = getDatabaseUrl();
dbProperties.put("shutdown", "true");
try {
DriverManager.getConnection(dbUrl, dbProperties);
} catch (SQLException ex) {
}
isConnected = false;
}
}
public String getDatabaseLocation() {
String dbLocation = System.getProperty("derby.system.home") + "/" + dbName;
return dbLocation;
}
public String getDatabaseUrl() {
String dbUrl = dbProperties.getProperty("derby.url") + dbName;
return dbUrl;
}
/*
public int saveRecord(Address record) {
int id = -1;
try {
stmtSaveNewRecord.clearParameters();
stmtSaveNewRecord.setString(1, record.getLastName());
stmtSaveNewRecord.setString(2, record.getFirstName());
stmtSaveNewRecord.setString(3, record.getMiddleName());
stmtSaveNewRecord.setString(4, record.getPhone());
stmtSaveNewRecord.setString(5, record.getEmail());
stmtSaveNewRecord.setString(6, record.getAddress1());
stmtSaveNewRecord.setString(7, record.getAddress2());
stmtSaveNewRecord.setString(8, record.getCity());
stmtSaveNewRecord.setString(9, record.getState());
stmtSaveNewRecord.setString(10, record.getPostalCode());
stmtSaveNewRecord.setString(11, record.getCountry());
int rowCount = stmtSaveNewRecord.executeUpdate();
ResultSet results = stmtSaveNewRecord.getGeneratedKeys();
if (results.next()) {
id = results.getInt(1);
}
} catch(SQLException sqle) {
sqle.printStackTrace();
}
return id;
}
public boolean editRecord(Address record) {
boolean bEdited = false;
try {
stmtUpdateExistingRecord.clearParameters();
stmtUpdateExistingRecord.setString(1, record.getLastName());
stmtUpdateExistingRecord.setString(2, record.getFirstName());
stmtUpdateExistingRecord.setString(3, record.getMiddleName());
stmtUpdateExistingRecord.setString(4, record.getPhone());
stmtUpdateExistingRecord.setString(5, record.getEmail());
stmtUpdateExistingRecord.setString(6, record.getAddress1());
stmtUpdateExistingRecord.setString(7, record.getAddress2());
stmtUpdateExistingRecord.setString(8, record.getCity());
stmtUpdateExistingRecord.setString(9, record.getState());
stmtUpdateExistingRecord.setString(10, record.getPostalCode());
stmtUpdateExistingRecord.setString(11, record.getCountry());
stmtUpdateExistingRecord.setInt(12, record.getId());
stmtUpdateExistingRecord.executeUpdate();
bEdited = true;
} catch(SQLException sqle) {
sqle.printStackTrace();
}
return bEdited;
}
public boolean deleteRecord(int id) {
boolean bDeleted = false;
try {
stmtDeleteAddress.clearParameters();
stmtDeleteAddress.setInt(1, id);
stmtDeleteAddress.executeUpdate();
bDeleted = true;
} catch (SQLException sqle) {
sqle.printStackTrace();
}
return bDeleted;
}
public boolean deleteRecord(Address record) {
int id = record.getId();
return deleteRecord(id);
}
public List<ListEntry> getListEntries() {
List<ListEntry> listEntries = new ArrayList<ListEntry>();
Statement queryStatement = null;
ResultSet results = null;
try {
queryStatement = dbConnection.createStatement();
results = queryStatement.executeQuery(strGetListEntries);
while(results.next()) {
int id = results.getInt(1);
String lName = results.getString(2);
String fName = results.getString(3);
String mName = results.getString(4);
ListEntry entry = new ListEntry(lName, fName, mName, id);
listEntries.add(entry);
}
} catch (SQLException sqle) {
sqle.printStackTrace();
}
return listEntries;
}
public Address getAddress(int index) {
Address address = null;
try {
stmtGetAddress.clearParameters();
stmtGetAddress.setInt(1, index);
ResultSet result = stmtGetAddress.executeQuery();
if (result.next()) {
String lastName = result.getString("LASTNAME");
String firstName = result.getString("FIRSTNAME");
String middleName = result.getString("MIDDLENAME");
String phone = result.getString("PHONE");
String email = result.getString("EMAIL");
String add1 = result.getString("ADDRESS1");
String add2 = result.getString("ADDRESS2");
String city = result.getString("CITY");
String state = result.getString("STATE");
String postalCode = result.getString("POSTALCODE");
String country = result.getString("COUNTRY");
int id = result.getInt("ID");
address = new Address(lastName, firstName, middleName, phone,
email, add1, add2, city, state, postalCode,
country, id);
}
} catch(SQLException sqle) {
sqle.printStackTrace();
}
return address;
}
*/
public static void main(String[] args) {
trackerDAO db = new trackerDAO();
System.out.println(db.getDatabaseLocation());
System.out.println(db.getDatabaseUrl());
db.connect();
db.disconnect();
}
private Connection dbConnection;
private Properties dbProperties;
private boolean isConnected;
private String dbName;
private PreparedStatement stmtSaveNewRecord;
private PreparedStatement stmtUpdateExistingRecord;
private PreparedStatement stmtGetListEntries;
private PreparedStatement stmtGetAddress;
private PreparedStatement stmtDeleteAddress;
private static final String strCreateAddressTable =
"create table APP.ADDRESS (" +
" ID INTEGER NOT NULL PRIMARY KEY GENERATED
ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
" LASTNAME VARCHAR(30), " +
" FIRSTNAME VARCHAR(30), " +
" MIDDLENAME VARCHAR(30), " +
" PHONE VARCHAR(20), " +
" EMAIL VARCHAR(30), " +
" ADDRESS1 VARCHAR(30), " +
" ADDRESS2 VARCHAR(30), " +
" CITY VARCHAR(30), " +
" STATE VARCHAR(30), " +
" POSTALCODE VARCHAR(20), " +
" COUNTRY VARCHAR(30) " +
")";
private static final String strGetAddress =
"SELECT * FROM APP.ADDRESS " +
"WHERE ID = ?";
private static final String strSaveAddress =
"INSERT INTO APP.ADDRESS " +
" (LASTNAME, FIRSTNAME, MIDDLENAME, PHONE, EMAIL, ADDRESS1, ADDRESS2, " +
" CITY, STATE, POSTALCODE, COUNTRY) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
private static final String strGetListEntries =
"SELECT ID, LASTNAME, FIRSTNAME, MIDDLENAME FROM APP.ADDRESS " +
"ORDER BY LASTNAME ASC";
private static final String strUpdateAddress =
"UPDATE APP.ADDRESS " +
"SET LASTNAME = ?, " +
" FIRSTNAME = ?, " +
" MIDDLENAME = ?, " +
" PHONE = ?, " +
" EMAIL = ?, " +
" ADDRESS1 = ?, " +
" ADDRESS2 = ?, " +
" CITY = ?, " +
" STATE = ?, " +
" POSTALCODE = ?, " +
" COUNTRY = ? " +
"WHERE ID = ?";
private static final String strDeleteAddress =
"DELETE FROM APP.ADDRESS " +
"WHERE ID = ?";
}
[/CODE]
This is the full code for a class I retrived off the
internet. Somehow I got the program working by complete accident but then when I changed something it kicked out the same NPE. I could get it working again as it keeps popping up with a NullPointerException on the line where my properties are assigned:
dbProperties.load(dbPropInputStream);. I tried to restart from the beginning as thats how I got it working this morning but to no avail. My confirguration.properties file is in the same directory as the class. I know that it works because I actually got the entire program to run through. I tried debugging but I keep coming unstuck because I can't see where I'm going wrong. The error is occurs when in the loadDBProperties method because the dbPropInputStream does not obtain the information form the properties file.
As for setting the class path I think that the program is setting the classpath with this code:
The following information is in the properties file:
I'd like to get this working as its my first attempts at getting any database connection in
java and I can see that by using this class I could tweak it to fit into any other future application.
