1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165
|
package insync.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import insync.model.Contact;
public class ContactDAO extends BaseDAO
{
public List getContacts()
{
System.out.println("ContactDAO: findAll");
return getList("SELECT * FROM contact ORDER BY last_name, first_name");
}
public List getContactsByName(String name)
{
System.out.println("ContactDAO: findByName");
String likeStr = "%" + name + "%";
return getList("SELECT * FROM contact WHERE first_name LIKE ? OR last_name LIKE ? ORDER BY last_name, first_name",
new Object[] {likeStr, likeStr});
}
public List getContactsByCompany(int companyId)
{
System.out.println("ContactDAO: findByCompany");
return getList("SELECT * FROM contact WHERE company_id=? ORDER BY last_name, first_name", companyId);
}
public Object getContact(int contactId)
{
System.out.println("ContactDAO: getContact");
return getItem("SELECT * FROM contact WHERE contact_id=?", contactId);
}
public Contact create(Contact contact) throws DAOException
{
System.out.println("ContactDAO: create contact");
int contactId = createItem("INSERT INTO contact (first_name, last_name, phone, email, address, city, state, zip) VALUES (?,?,?,?,?,?,?,?)",
new Object[] {
contact.getFirstName(),
contact.getLastName(),
contact.getPhone(),
contact.getEmail(),
contact.getAddress(),
contact.getCity(),
contact.getState(),
contact.getZip()
});
contact.setId(contactId);
return contact;
}
public Contact save(Contact contact) throws DAOException, ConcurrencyException
{
System.out.println("ContactDAO: save contact");
return contact.getId() > 0 ? update(contact) : create(contact);
}
public Contact update(Contact contact) throws DAOException, ConcurrencyException
{
System.out.println("ContactDAO: update contact");
int rows = executeUpdate("UPDATE contact SET first_name=?, last_name=?, phone=?, email=?, address=?, city=?, state=?, zip=? WHERE contact_id=?",
new Object[] { contact.getFirstName(),
contact.getLastName(),
contact.getPhone(),
contact.getEmail(),
contact.getAddress(),
contact.getCity(),
contact.getState(),
contact.getZip(),
contact.getId()
});
if (rows == 0)
{
throw new ConcurrencyException("Item not found");
}
return (Contact) getContact(contact.getId());
}
public Contact update(Contact contact, Contact originalContact) throws DAOException, ConcurrencyException
{
System.out.println("ContactDAO: update contact");
/*
* The WHERE clause in this statement represents an optimistic locking implementation where
* your update will fail if the row has been changed by someone else since you read it.
* Another (simpler) way of implementing the same concurrency level without adding all the columns to the WHERE clause
* is to add a version column to the table. Every time the contact is updated, the contact's version is incremented.
* Using this approach, you can make sure a row has not been updated since you read it by building the WHERE clause
* using the primary key and the version column only.
*/
StringBuffer sql = new StringBuffer("UPDATE contact SET first_name=?, last_name=?, phone=?, email=?, address=?, city=?, state=?, zip=? ");
sql.append("WHERE contact_id=? ");
sql.append("AND (first_name=? or (? is null and first_name is null)) ");
sql.append("AND (last_name=? or (? is null and last_name is null)) ");
sql.append("AND (phone=? or (? is null and phone is null)) ");
sql.append("AND (email=? or (? is null and email is null)) ");
sql.append("AND (address=? or (? is null and address is null)) ");
sql.append("AND (city=? or (? is null and city is null)) ");
sql.append("AND (state=? or (? is null and state is null)) ");
sql.append("AND (zip=? or (? is null and zip is null))");
int rows = executeUpdate(sql.toString(), new Object[] {
contact.getFirstName(),
contact.getLastName(),
contact.getPhone(),
contact.getEmail(),
contact.getAddress(),
contact.getCity(),
contact.getState(),
contact.getZip(),
contact.getId(),
originalContact.getFirstName(),
originalContact.getFirstName(),
originalContact.getLastName(),
originalContact.getLastName(),
originalContact.getPhone(),
originalContact.getPhone(),
originalContact.getEmail(),
originalContact.getEmail(),
originalContact.getAddress(),
originalContact.getAddress(),
originalContact.getCity(),
originalContact.getCity(),
originalContact.getState(),
originalContact.getState(),
originalContact.getZip(),
originalContact.getZip()
});
if (rows == 0)
{
throw new ConcurrencyException("Item not found");
}
return (Contact) getContact(contact.getId());
}
public void deleteItem(Contact contact) throws DAOException, ConcurrencyException
{
System.out.println("ContactDAO: delete contact");
int rows = executeUpdate("DELETE FROM contact WHERE contact_id = ?", new Object[] { contact.getId() });
if (rows == 0)
{
throw new ConcurrencyException("Item not found");
}
}
protected Object rowToObject(ResultSet rs) throws SQLException
{
Contact contact = new Contact();
contact.setId(rs.getInt("contact_id"));
contact.setFirstName(rs.getString("first_name"));
contact.setLastName(rs.getString("last_name"));
contact.setPhone(rs.getString("phone"));
contact.setEmail(rs.getString("email"));
contact.setAddress(rs.getString("address"));
contact.setCity(rs.getString("city"));
contact.setState(rs.getString("state"));
contact.setZip(rs.getString("zip"));
return contact;
}
} |
Partager