Simple DAO Tutorial - 3

This is the 3rd part of the Simple DAO Tutorial. Here he Dao is implemented using HSQLDB.


PersonDAOHSQLDBImpl.java
------------------------
package daoPackage;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.*;
import javax.naming.*;
import java.sql.*;
import javax.sql.*;
import mainPackage.*;

public class PersonDAOHSQLDBImpl implements PersonDAO {

private Connection con;
private static int personNextId = 1;
private static int personCurrentId = 1;

public PersonDAOHSQLDBImpl() throws Exception {
System.out.println("ARSmsg: PersonDAOHSQLDBImpl constructor began-----------.");
getDBConnection();

try {
update("DROP TABLE personTab IF EXISTS");
// db will automatically generate unique values for the id column
update(
"CREATE TABLE personTab ( id INTEGER IDENTITY, firstName VARCHAR(20), lastName VARCHAR(20), hobby VARCHAR(30))");
} catch (SQLException e) {
//ignore exception the second time if we create the same table
}

try {
update(
"INSERT INTO personTab(firstName,lastName,hobby) VALUES('Ali Riza', 'SARAL', 'Music Composition')");
update(
"INSERT INTO personTab(firstName,lastName,hobby) VALUES('Tamer', 'ÜNAL', 'Folk Music')");
update(
"INSERT INTO personTab(firstName,lastName,hobby) VALUES('Ekrem', 'CANBEK', 'Violin')");
update(
"INSERT INTO personTab(firstName,lastName,hobby) VALUES('Turan', 'MUTLUAY', 'Choir')");
update(
"INSERT INTO personTab(firstName,lastName,hobby) VALUES('Rengin', 'AHISKALI', 'Literature')");
// do a query
query("SELECT * FROM personTab");

shutdown();
} catch (SQLException ex3) {
ex3.printStackTrace();
}
}

public void createRec(int id, String firstName,
String lastName, String hobby) throws PersonDAOSysException {
System.out.println("ARSmsg: createRec began-----------.");
PreparedStatement stmt = null;

try {
getDBConnection();
stmt = con.prepareStatement("insert into personTab(id, firstName, lastName,"
+ " hobby) values (?, ?, ?, ?)");
stmt.setString(1, String.valueOf(id));
stmt.setString(2, firstName);
stmt.setString(3, lastName);
stmt.setString(4, hobby);
stmt.executeUpdate();
personCurrentId = personNextId;
personNextId++;
query("SELECT * FROM personTab");
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
}

public void deleteRec(int studentId) throws PersonDAOSysException {
System.out.println("ARSmsg: deleteRec began-----------.");
PreparedStatement stmt = null;

try {
getDBConnection();
stmt = con.prepareStatement("delete from personTab where id = ?");
stmt.setString(1, String.valueOf(studentId));
stmt.executeUpdate();
stmt.close();
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
reorganiseDB();
}

public boolean findByPrimaryKey(int personId) throws PersonDAOSysException {
System.out.println("ARSmsg: findByPrimaryKey began-----------.");
boolean result = false;
PreparedStatement stmt = null;

try {
getDBConnection();
stmt = con.prepareStatement("select id from personTab where id = ?");
stmt.setString(1, String.valueOf(personId));
ResultSet rs = stmt.executeQuery();
result = rs.next();
rs.close();
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException: " + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
return result;
}

public Collection findByLastName(String lastName) throws PersonDAOSysException {
System.out.println("ARSmsg: findByLastName began-----------.");
Collection persons = new ArrayList();
PreparedStatement stmt = null;

try {
getDBConnection();
stmt = con.prepareStatement("select id from personTab where lastName = ?");
stmt.setString(1, lastName);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
String personId = rs.getString(1);
persons.add(personId);
}
rs.close();
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException: " + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
System.out.println(persons.toString());
return persons;
}

public int findTotalNumberOfPersons() throws PersonDAOSysException {
System.out.println("ARSmsg: findTotalNumberOfPersons began-----------.");
int total = 111;
PreparedStatement stmt = null;
try {
getDBConnection();
stmt = con.prepareStatement("select count(id) from personTab");
ResultSet rs = stmt.executeQuery();
rs.next();
total = rs.getInt(1);
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
return total;
}

public Person readRec(int personId) throws PersonDAOSysException {
System.out.println("ARSmsg: readRec began-----------.");
Person person = null;
PreparedStatement stmt = null;

try {
getDBConnection();
stmt = con.prepareStatement("select firstName, lastName, "
+ " hobby from personTab where id=?");
stmt.setString(1, String.valueOf(personId));
ResultSet rs = stmt.executeQuery();
rs.next();

person = new Person(personId,
rs.getString(1),
rs.getString(2),
rs.getString(3));
System.out.print(personId + " ");
System.out.print(rs.getString(1) + " ");
System.out.print(rs.getString(2) + " ");
System.out.println(rs.getString(3) + "\n");
rs.close();
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
return person;
}

public Person readNextRec() throws PersonDAOSysException {
System.out.println("ARSmsg: readNextRec began-----------.");
Person person = null;
PreparedStatement stmt = null;
personCurrentId++;
int personId = personCurrentId;

try {
getDBConnection();
stmt = con.prepareStatement("select firstName, lastName, "
+ " hobby from personTab where id=?");
stmt.setString(1, String.valueOf(personId));
ResultSet rs = stmt.executeQuery();
rs.next();

person = new Person(personId,
rs.getString(1),
rs.getString(2),
rs.getString(3));
System.out.print(personId + " ");
System.out.print(rs.getString(1) + " ");
System.out.print(rs.getString(2) + " ");
System.out.println(rs.getString(3) + "\n");
rs.close();
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
return person;
}

public Person readPrevRec() throws PersonDAOSysException {
System.out.println("ARSmsg: readPrevRec began-----------.");
Person person = null;
PreparedStatement stmt = null;
personCurrentId--;
int personId = personCurrentId;

try {
getDBConnection();
stmt = con.prepareStatement("select firstName, lastName, "
+ " hobby from personTab where id=?");
stmt.setString(1, String.valueOf(personId));
ResultSet rs = stmt.executeQuery();
rs.next();

person = new Person(personId,
rs.getString(1),
rs.getString(2),
rs.getString(3));
System.out.print(personId + " ");
System.out.print(rs.getString(1) + " ");
System.out.print(rs.getString(2) + " ");
System.out.println(rs.getString(3) + "\n");
rs.close();
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
return person;
}

public void updateRec(int id, String firstName, String lastName,
String hobby) throws PersonDAOSysException {
System.out.println("ARSmsg: updateRec began-----------.");
PreparedStatement stmt = null;

try {
getDBConnection();
stmt = con.prepareStatement("update personTab set "
+ "firstName=?,lastName = ?,hobby = ? where id=?");
stmt.setString(1, firstName);
stmt.setString(2, lastName);
stmt.setString(3, hobby);
stmt.setString(4, String.valueOf(id));
stmt.executeUpdate();
stmt.close();
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
}

private void reorganiseDB() {
System.out.println("ARSmsg: reorganiseDB began-----------.");
Person person;

getDBConnection();

try {
update("DROP TABLE personTabTemp IF EXISTS");
update("CREATE TABLE personTabTemp ( "
+ "id INTEGER IDENTITY, "
+ "firstName VARCHAR(20), "
+ "lastName VARCHAR(20), "
+ "hobby VARCHAR(30))");
} catch (SQLException e) {
//ignore exception the second time if we create the same table
}
closeDBConnection();

int recCount = findTotalNumberOfPersons();

for (int i = 0, j = 0; j < recCount; i++) {

if (findByPrimaryKey(i)) {
//System.out.println(i+" exists");
person = readRec(i);
createRecTemp(j, person.getName(), person.getLast(), person.getHobby());
j++;
} else {
System.out.println(i + " does not exist");
}
}

getDBConnection();

try {
update("DROP TABLE personTab IF EXISTS");
update("CREATE TABLE personTab ( "
+ "id INTEGER IDENTITY, "
+ "firstName VARCHAR(20), "
+ "lastName VARCHAR(20), "
+ "hobby VARCHAR(30))");
} catch (SQLException e) {
//ignore exception the second time if we create the same table
}
closeDBConnection();

int recCountTemp = findTotalNumberOfPersonsTemp();

for (int i = 0; i < recCountTemp; i++) {
person = readRecTemp(i);
createRec(i, person.getName(), person.getLast(), person.getHobby());
}
}

public void createRecTemp(int id, String firstName,
String lastName, String hobby) throws PersonDAOSysException {
System.out.println("ARSmsg: createRecTemp began-----------.");
PreparedStatement stmt = null;

try {
getDBConnection();
stmt = con.prepareStatement("insert into personTabTemp(id, firstName, lastName,"
+ " hobby) values (?, ?, ?, ?)");
stmt.setString(1, String.valueOf(id));
stmt.setString(2, firstName);
stmt.setString(3, lastName);
stmt.setString(4, hobby);
stmt.executeUpdate();
personCurrentId = personNextId;
personNextId++;
query("SELECT * FROM personTabTemp");
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
}

public int findTotalNumberOfPersonsTemp() throws PersonDAOSysException {
System.out.println("ARSmsg: findTotalNumberOfPersonsTemp began-----------.");
int total = 111;
PreparedStatement stmt = null;
try {
getDBConnection();
stmt = con.prepareStatement("select count(id) from personTabTemp");
ResultSet rs = stmt.executeQuery();
rs.next();
total = rs.getInt(1);
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
return total;
}

public Person readRecTemp(int personId) throws PersonDAOSysException {
System.out.println("ARSmsg: readRecTemp began-----------.");
Person person = null;
PreparedStatement stmt = null;

try {
getDBConnection();
stmt = con.prepareStatement("select firstName, lastName, "
+ " hobby from personTabTemp where id=?");
stmt.setString(1, String.valueOf(personId));
ResultSet rs = stmt.executeQuery();
rs.next();

person = new Person(personId,
rs.getString(1),
rs.getString(2),
rs.getString(3));
System.out.print(personId + " ");
System.out.print(rs.getString(1) + " ");
System.out.print(rs.getString(2) + " ");
System.out.println(rs.getString(3) + "\n");
rs.close();
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
} finally {
closeStatement(stmt);
closeDBConnection();
}
return person;
}

private void getDBConnection() throws PersonDAOSysException {
//System.out.println("ARSmsg: getDBConnection began-----------.");
String driverClassName = "";
String db_file_name_prefix = "";

try {
Properties props = new Properties();
String curDir = System.getProperty("user.dir");
//System.out.println("curDir=" + curDir);
FileInputStream fis = new FileInputStream(curDir + "/PersonDAOARS.properties");
props.load(fis);
fis.close();

driverClassName = props.getProperty("driverName");
//System.out.println("driverClassName=" + driverClassName);
db_file_name_prefix = props.getProperty("dbFileNamePrefix");
} catch (FileNotFoundException e) {
throw new PersonDAOSysException("\\FileNotFoundException:" + e.getMessage() + " " + e.toString());
} catch (java.io.IOException e) {
throw new PersonDAOSysException("\\java.io.IOException:" + e.getMessage() + " " + e.toString());
}

try {
Class.forName(driverClassName); //org.hsqldb.jdbcDriver

con = DriverManager.getConnection("jdbc:hsqldb:"
+ db_file_name_prefix, // filenames
"sa", // username
""); // password
} catch (SQLException ex) {
throw new PersonDAOSysException("\\nSQLException:" + ex.getMessage() + " " + ex.toString());
} catch (java.lang.ClassNotFoundException ex) {
throw new PersonDAOSysException("\\nClassNotFoundException:" + ex.getMessage() + " " + ex.toString());
}
}

private void closeDBConnection() throws PersonDAOSysException {
//System.out.println("ARSmsg: closeDBConnection began-----------.");
try {
con.close();
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
}
}

private void closeStatement(PreparedStatement stmt) throws PersonDAOSysException {
//System.out.println("ARSmsg: closeStatement began-----------.");
try {
stmt.close();
} catch (SQLException ex) {
throw new PersonDAOSysException("SQLException:" + ex.getMessage());
}
}

public synchronized void update(String expression) throws SQLException {
//for CREATE, DROP, INSERT and UPDATE
System.out.println("ARSmsg: update began-----------.");
Statement st = null;

st = con.createStatement(); // statements

int i = st.executeUpdate(expression); // run the query

if (i == -1) {
System.out.println("db error : " + expression);
}
st.close();
}

public synchronized void query(String expression) throws SQLException {
//for SELECT
System.out.println("ARSmsg: query began-----------.");
Statement st = null;
ResultSet rs = null;

st = con.createStatement();
rs = st.executeQuery(expression);

dump(rs);
st.close();
}

public static void dump(ResultSet rs) throws SQLException {
System.out.println("ARSmsg: dump began-----------.");
ResultSetMetaData meta = rs.getMetaData();
int colmax = meta.getColumnCount();
int i;
Object o = null;

for (; rs.next();) {
for (i = 0; i < colmax; ++i) {
o = rs.getObject(i + 1);
System.out.print(o.toString() + " ");
}
System.out.println(" ");
}
System.out.println();
}

public void shutdown() throws SQLException {
System.out.println("ARSmsg: shutdown began-----------.");
Statement st = con.createStatement();

st.execute("SHUTDOWN"); //write out buffersand clean shut down
con.close(); // if there are no other open connection
}
}