This is an example project that can be an aide or alternative to your own class project. Based on your time availability use it to guide you as you build you own project or make a version of it as your own class project. Please only share this information with other enrolled class members. Simple Address Book Version 2.0w – Logging Simple Address Book implemented using a multi-layer architecture and interfaces to use each layer. What’s New: Logging using Log4j 1) Project Project Name: SimpleAddressBook-2-0w-logging This is an Eclipse Java Dynamic Web Project. Click here to view all JWAD versions of this project. Hit the Browser’s Back Button to return here when finish. 7) Coding PersonDaoJdbcImpl.java package com.ogbrown.simpleab.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import org.apache.log4j.Logger; import com.ogbrown.simpleab.model.Gender; import com.ogbrown.simpleab.model.Person; public class PersonDaoJdbcImpl implements PersonDao { private static final Logger logger = Logger.getLogger(PersonDaoJdbcImpl.class); @Override public void persist(Person person) { try { Class.forName("com.mysql.jdbc.Driver"); logger.trace("JDBC driver loaded"); String url = "jdbc:mysql://localhost/simpleaddress"; Connection con = DriverManager.getConnection(url, "jwad_dba", "mypassword"); logger.trace("Database connection established"); java.sql.Date sqlDate = new java.sql.Date(person.getDob().getTime()); logger.trace("java.sql.Date for dob: " + sqlDate); // Using Statement ****************************** Statement stmt1 = con.createStatement(); String insertSql = "INSERT INTO persontbl VALUES (NULL,"; insertSql += "'" + person.getFirstName() + "',"; insertSql += "'" + person.getLastName() + "',"; insertSql += "'" + (person.getGender().equals(Gender.MALE)?"M":"F") + "', "; insertSql += "'" + sqlDate + "',"; insertSql += "'" + person.getEmail() + "')"; // 20141213-004: Converted debugging sysout statements to logger.debug() logger.debug("\n SQL Statement: " + insertSql); int i = stmt1.executeUpdate( insertSql ); logger.trace("Inserted: row OK using Statement." + i + " row(s)"); logger.trace("Inserted: row OK using Prepared Statement."); stmt1.close(); con.close(); } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); } catch (SQLException sqle) { sqle.printStackTrace(); } } @Override public Person get(String firstName, String lastName) { Person person = null; final String GET_SQL = "SELECT id, firstname, lastname, gender, dob, email FROM persontbl WHERE firstname like ? and lastname like ?"; try { Class.forName("com.mysql.jdbc.Driver"); logger.trace("JDBC driver loaded"); String url = "jdbc:mysql://localhost/simpleaddress"; Connection con = DriverManager.getConnection(url, "jwad_dba", "mypassword"); logger.trace("Database connection established"); logger.debug("\n SQL Statement: " + GET_SQL + " for " + firstName + "," + lastName); PreparedStatement stmt = con.prepareStatement(GET_SQL); stmt.setString(1, firstName); stmt.setString(2, lastName); ResultSet rs = stmt.executeQuery(); logger.trace("Got some results:"); while (rs.next()) { int a = rs.getInt(1); String b = rs.getString("firstname"); String c = rs.getString(3); String d = rs.getString(4); java.sql.Date e = rs.getDate(5); String f = rs.getString("email"); Gender g; person = new Person(); person.setId(a); person.setFirstName(b); person.setLastName(c); if (d.toUpperCase().startsWith("M")) { g = Gender.MALE; } else { g = Gender.FEMALE; } person.setGender(g); person.setDob(e); person.setEmail(f); } stmt.close(); con.close(); } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); } catch (SQLException sqle) { sqle.printStackTrace(); } return person; } @Override public void update(Person person) { final String UPDATE_SQL = "INSERT INTO persontbl VALUES (?,?,?,?,?,?)"; try { Class.forName("com.mysql.jdbc.Driver"); logger.trace("JDBC driver loaded"); String url = "jdbc:mysql://localhost/simpleaddress"; Connection con = DriverManager.getConnection(url, "jwad_dba", "mypassword"); logger.trace("Database connection established"); java.sql.Date sqlDate = new java.sql.Date(person.getDob().getTime()); logger.trace("java.sql.Date for dob: " + sqlDate); logger.debug("\n SQL Statement: " + UPDATE_SQL + "\n for " + person); //Using Prepared Statement ********************** PreparedStatement stmt = con.prepareStatement(UPDATE_SQL); stmt.setInt(1, person.getId()); stmt.setString(2, person.getFirstName()); stmt.setString(3, person.getLastName()); stmt.setString(4, person.getGender().equals(Gender.MALE)?"M":"F"); stmt.setDate(5, sqlDate ); stmt.setString(6, person.getEmail()); stmt.executeUpdate(); logger.trace("Inserted: row OK using Prepared Statement."); stmt.close(); con.close(); } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); } catch (SQLException sqle) { sqle.printStackTrace(); } } @Override public void delete(Person person) { final String DELETE_SQL = "DELETE FROM persontbl WHERE id=?"; int rowCount=0; try { Class.forName("com.mysql.jdbc.Driver"); logger.trace("JDBC driver loaded"); String url = "jdbc:mysql://localhost/simpleaddress"; Connection con = DriverManager.getConnection(url, "jwad_dba", "mypassword"); logger.trace("Database connection established"); logger.debug("\n SQL Statement: " + DELETE_SQL.replace("?", person.getId()+"")); PreparedStatement stmt = con.prepareStatement(DELETE_SQL); stmt.setInt(1, person.getId()); rowCount = stmt.executeUpdate(); logger.trace("Deleted: " + rowCount + " row(s) OK using Prepared Statement."); stmt.close(); con.close(); } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); } catch (SQLException sqle) { sqle.printStackTrace(); } } @Override public List<Person> list() { ArrayList<Person> people = new ArrayList<Person>(); Person person = null; Statement stmt = null; ResultSet rs = null; final String LIST_SQL = "select * from persontbl"; try { Class.forName("com.mysql.jdbc.Driver"); logger.trace("JDBC driver loaded"); String url = "jdbc:mysql://localhost/simpleaddress"; Connection con = DriverManager.getConnection(url, "jwad_dba", "mypassword"); logger.trace("Database connection established"); logger.debug("\n SQL Statement: " + LIST_SQL); stmt = con.createStatement(); rs = stmt.executeQuery(LIST_SQL); while (rs.next()){ person = new Person(); person.setId(rs.getInt("id")); person.setFirstName(rs.getString("firstname")); person.setLastName(rs.getString("lastname")); person.setDob(rs.getDate("dob")); person.setGender(rs.getString("gender").toUpperCase().startsWith("M")?Gender.MALE:Gender.FEMALE); person.setEmail(rs.getString("email")); people.add(person); logger.trace("Adding to people: " + person.toString()); } stmt.close(); con.close(); } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); } catch (SQLException sqle) { sqle.printStackTrace(); } return people; } } AddressDaoJdbcImpl.java package com.ogbrown.simpleab.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import org.apache.log4j.Logger; import com.ogbrown.simpleab.model.Address; public class AddressDaoJdbcImpl implements AddressDao { private static final Logger logger = Logger.getLogger(AddressDaoJdbcImpl.class); @Override public void persist(Address address) { try { Class.forName("com.mysql.jdbc.Driver"); logger.trace("JDBC driver loaded"); String url = "jdbc:mysql://localhost/simpleaddress"; Connection con = DriverManager.getConnection(url, "jwad_dba", "mypassword"); logger.trace("Database connection established"); // Using Statement ****************************** Statement stmt1 = con.createStatement(); String insertSql = "INSERT INTO addresstbl VALUES ("; insertSql += "" + address.getPersonId()+ ","; insertSql += "'" + address.getStreet()+ "',"; insertSql += "'" + address.getCity() + "',"; insertSql += "'" + address.getState() + "', "; insertSql += "'" + address.getZipCode() + "')"; // 20141213-004: Converted debugging sysout statements to logger.debug() logger.debug("\n SQL Statement: " + insertSql); int i = stmt1.executeUpdate( insertSql ); logger.trace("Inserted: row OK using Statement." + i + " row(s)"); logger.trace("Inserted: row OK using Prepared Statement."); stmt1.close(); con.close(); } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); } catch (SQLException sqle) { sqle.printStackTrace(); } } @Override public Address get(int personId) { Address address = null; final String GET_SQL = "SELECT person_Id, street, city, zipcode FROM addresstbl"; try { Class.forName("com.mysql.jdbc.Driver"); logger.trace("JDBC driver loaded"); String url = "jdbc:mysql://localhost/simpleaddress"; Connection con = DriverManager.getConnection(url, "jwad_dba", "mypassword"); logger.trace("Database connection established"); logger.debug("\n SQL Statement: " + GET_SQL); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(GET_SQL); logger.trace("Got some results:"); while (rs.next()) { int a = rs.getInt(1); String b = rs.getString("street"); String c = rs.getString(3); String d = rs.getString(4); String e = rs.getString("zipcode"); address = new Address(); address.setPersonId(a); address.setStreet(b); address.setCity(c); address.setState(d); address.setZipCode(d); } stmt.close(); con.close(); } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); } catch (SQLException sqle) { sqle.printStackTrace(); } return address; } @Override public void update(Address address) { final String UPDATE_SQL = "INSERT INTO addresstbl VALUES (?,?,?,?,?)"; try { Class.forName("com.mysql.jdbc.Driver"); logger.trace("JDBC driver loaded"); String url = "jdbc:mysql://localhost/simpleaddress"; Connection con = DriverManager.getConnection(url, "jwad_dba", "mypassword"); logger.trace("Database connection established"); logger.debug("\n SQL Statement: " + UPDATE_SQL + "\n for " + address); //Using Prepared Statement ********************** PreparedStatement stmt = con.prepareStatement(UPDATE_SQL); stmt.setInt(1, address.getPersonId()); stmt.setString(2, address.getStreet()); stmt.setString(3, address.getCity()); stmt.setString(4, address.getState()); stmt.setString(5, address.getZipCode()); stmt.executeUpdate(); logger.trace("Inserted: row OK using Prepared Statement."); stmt.close(); con.close(); } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); } catch (SQLException sqle) { sqle.printStackTrace(); } } @Override public void delete(Address address) { final String DELETE_SQL = "DELETE FROM addresstbl WHERE person_id=?"; int rowCount=0; try { Class.forName("com.mysql.jdbc.Driver"); logger.trace("JDBC driver loaded"); String url = "jdbc:mysql://localhost/simpleaddress"; Connection con = DriverManager.getConnection(url, "jwad_dba", "mypassword"); logger.trace("Database connection established"); logger.debug("\n SQL Statement: " + DELETE_SQL.replace("?", address.getPersonId()+"")); PreparedStatement stmt = con.prepareStatement(DELETE_SQL); stmt.setInt(1, address.getPersonId()); rowCount = stmt.executeUpdate(); logger.trace("Deleted: " + rowCount + " row(s) OK using Prepared Statement."); stmt.close(); con.close(); } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); } catch (SQLException sqle) { sqle.printStackTrace(); } } @Override public List<Address> list() { ArrayList<Address> addresses = new ArrayList<Address>(); Address address = null; Statement stmt = null; ResultSet rs = null; final String LIST_SQL = "select * from addresstbl"; try { Class.forName("com.mysql.jdbc.Driver"); logger.trace("JDBC driver loaded"); String url = "jdbc:mysql://localhost/simpleaddress"; Connection con = DriverManager.getConnection(url, "jwad_dba", "mypassword"); logger.trace("Database connection established"); logger.debug("\n SQL Statement: " + LIST_SQL); stmt = con.createStatement(); rs = stmt.executeQuery(LIST_SQL); while (rs.next()){ address = new Address(); address.setPersonId(rs.getInt("person_id")); address.setStreet(rs.getString("street")); address.setCity(rs.getString("city")); address.setState(rs.getString("state")); address.setZipCode(rs.getString("zipcode")); addresses.add(address); logger.trace("Adding to addresses: " + address.toString()); } stmt.close(); con.close(); } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); } catch (SQLException sqle) { sqle.printStackTrace(); } return addresses; } } Page navigation ↑ Simple Address Book – Java Web Apps ← JWAD1: Project: SimpleAB 2.0w: Week 7 JWAD2: Project: SimpleAB User Registration – Rough →