Crud with JQuery Master-Detail 5

Underneath the servlet layer there is the DAO (Data Access Object)
layer. DAO layer is a mechanism that makes the program
independent from the data storage type. In fact I have provided
in this blog a DAO application which can access data in vector, HSQL and
MySQL formats with a single switch change. A new and better version
of this DAO application will be available in short notice, including
the availability on SourceForge.net.

WARNING:
---------
The delete functionality reorganises the id's in the DB.
This causes substantial delay when a rec is deleted.
I have corrected this design problem in the new DAO application
that I will provide in short notice. You may change the DAO implementation
files and correct the problem or wait for me to provide the new
version og this tutorial application.

The DAO is defined at an interface file:

PersonDAO.java
--------------
package daoPackage;
/**
*
* @author Ali Riza SARAL
*/
import java.util.Collection;
import mainPackage.*;

public interface PersonDAO {

public void createRec(int id, String firstName,
String lastName, String hobby)
throws PersonDAOSysException;

public Person readRec(int personId)
throws PersonDAOSysException;

public void updateRec(int id, String firstName,
String lastName, String hobby)
throws PersonDAOSysException;

public void deleteRec(int personId)
throws PersonDAOSysException;
}

mainPackage/Person.java
-------------------------
package mainPackage;

/**
*
* @author Ali Riza SARAL
*/
public class Person {

private String name;
private String last;
private String hobby;
private int id;

public Person(){}
public Person(int id, String name, String last, String hobby){
this.name = name;
this.last = last;
this.hobby = hobby;
this.id = id;
}
public String getName() {
return this.name;
}

public void setName(String name) {
this.name = name;
}

public String getLast() {
return this.last;
}

public void setLast(String last) {
this.last = last;
}

public String getHobby() {
return this.hobby;
}

public void setHobby(String hobby) {
this.hobby = hobby;
}

public int getId() {
return this.id;
}

public void setId(int id) {
this.id = id;
}
}


MySQL DAO follows:

PersonDAOMySQLDBImpl.java
-------------------------
package daoPackage;
/**
*
* @author Ali Riza SARAL
*/
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 int personNextId = 1;
private 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();
// }
Statement s = conn.createStatement();
String sQuery = " SELECT COUNT(*) AS rowcount FROM " + "persontab";
ResultSet rs = s.executeQuery(sQuery);
rs.next();
int iTotal = rs.getInt("rowcount");
rs.close();
s.close();
personNextId = iTotal + 1;
personCurrentId=1;
}

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 createRecFlat(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();
if (rs.next()==true){
person = new Person(personId,
rs.getString(1),
rs.getString(2),
rs.getString(3));
rs.close();
} else {
personCurrentId--;
personId = personCurrentId;
person = readRec(personId);
}

System.out.print(personId + " ");
// System.out.print(rs.getString(1) + " ");
// System.out.print(rs.getString(2) + " ");
// System.out.println(rs.getString(3) + "\n");


} 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--;
if (personCurrentId < 1) personCurrentId = 1;
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();
if (rs.next()==true){

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));
int rowCount = stmt.executeUpdate();
stmt.close();
if (rowCount < 1){
createRec(personNextId, firstName, lastName, hobby);
}
} 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);
createRecFlat(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
}
public int getPersonNextId() {
return personNextId;
}

public int getPersonCurrentId() {
return personCurrentId;
}
public void setPersonCurrentId(int id) {
personCurrentId = id;
}
}