Simple DAO Tutorial - 4

This is the 4th part of the Simple DAO Tutorial. Here the DAO is implemented using MySQL DB.


PersonDAOMySQLDBImpl.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 PersonDAOMySQLDBImpl implements PersonDAO {

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

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

try {
update("DROP TABLE IF EXISTS persontab ");
// db will automatically generate unique values for the id column
update(
"CREATE TABLE persontab("
+ "id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),"
+ "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 = conn.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 = conn.prepareStatement("delete from personTab where id = ?");
stmt.setString(1, String.valueOf(studentId));
stmt.executeUpdate();
stmt.close();
query("SELECT * FROM personTab");
} 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 = conn.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 = conn.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("FindByLastName id="+persons.toString());
return persons;
}

public int findTotalNumberOfPersons() throws PersonDAOSysException {
//System.out.println("ARSmsg: findTotalNumberOfPersons began-----------.");
int total = 0;
PreparedStatement stmt = null;
try {
getDBConnection();
stmt = conn.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();
}
System.out.println("ARSmsg: findTotalNumberOfPersons ="+total);
return total;
}

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

try {
getDBConnection();
stmt = conn.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 = conn.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 = conn.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 = conn.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 IF EXISTS persontabtemp");
update("CREATE TABLE persontabtemp ("
+ "id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),"
+ "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 = 1, j = 1; j < recCount+1; 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 IF EXISTS persontab ");
update( "CREATE TABLE persontab("
+ "id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),"
+ "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 = 1; i < recCountTemp + 1; 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 = conn.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 = conn.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 = conn.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 url = "jdbc:mysql://localhost:3306/";
String dbName = "persondao";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "3391309";

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();

url = props.getProperty("MySQLurl");
dbName = props.getProperty("MySQLdbName");
driver = props.getProperty("MySQLdriver");
userName = props.getProperty("MySQLuserName");
password = props.getProperty("MySQLpassword");
} 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(driver).newInstance();
conn = DriverManager.getConnection(url + dbName, userName, password);
//System.out.println("Connected to the database");
//conn.close();
//System.out.println("Disconnected from database");
} catch (Exception ex) {
throw new PersonDAOSysException("\\nSQLException:" + ex.getMessage() + " " + ex.toString());
}
}

private void closeDBConnection() throws PersonDAOSysException {
//System.out.println("ARSmsg: closeDBConnection began-----------.");
try {
conn.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 = conn.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 = conn.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 = conn.createStatement();

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