package webapp.storage.sql;
import webapp.IStorage;
import webapp.WebAppException;
import webapp.model.ContactType;
import webapp.model.Resume;
import webapp.sql.Sql;
import webapp.sql.SqlExecutor;
import webapp.sql.SqlTransaction;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
/**
* User: gkislin
* Date: 12.05.2014
*/
public class SqlStorage implements IStorage {
@Override
public void clearAll() {
Sql.execute("DELETE FROM resume");
}
@Override
public String save(final Resume resume) {
return Sql.execute(
new SqlTransaction<String>() {
@Override
public String execute(Connection conn) throws SQLException {
try (PreparedStatement st = conn.prepareStatement("INSERT INTO resume (uuid, full_name, home_page, location) VALUES(?,?,?,?)")) {
st.setString(1, resume.getUuid());
st.setString(2, resume.getFullName());
st.setString(3, resume.getHomePage());
st.setString(4, resume.getLocation());
st.execute();
}
replaceContact(conn, resume);
return resume.getUuid();
}
}
);
}
@Override
public Resume load(final String uuid) {
return Sql.execute(
"SELECT * FROM resume r LEFT JOIN contact c ON r.uuid = c.resume_uuid WHERE r.uuid=? ",
new SqlExecutor<Resume>() {
@Override
public Resume execute(PreparedStatement st) throws SQLException {
st.setString(1, uuid);
try (ResultSet rs = st.executeQuery()) {
if (rs.next()) {
Resume resume = new Resume(uuid, rs.getString("full_name"), rs.getString("home_page"), rs.getString("location"));
addContact(rs, resume);
while (rs.next()) {
addContact(rs, resume);
}
return resume;
}
throw new WebAppException("Resume " + uuid + " is not found");
}
}
}
);
}
private void addContact(ResultSet rs, Resume r) throws SQLException {
String value = rs.getString("value");
if (value != null) {
ContactType type = ContactType.valueOf(rs.getString("type"));
r.addContact(type, value);
}
}
@Override
public void delete(final String uuid) {
Sql.execute("DELETE FROM resume WHERE uuid=?", new SqlExecutor<Void>() {
@Override
public Void execute(PreparedStatement st) throws SQLException {
st.setString(1, uuid);
if (st.executeUpdate() == 0) {
throw new WebAppException("Resume not found", uuid);
}
return null;
}
});
}
@Override
public void update(final Resume resume) {
Sql.execute(
new SqlTransaction<Void>() {
@Override
public Void execute(Connection conn) throws SQLException {
try (PreparedStatement st = conn.prepareStatement("UPDATE resume SET full_name=?, home_page=?, location=? WHERE uuid=?")) {
st.setString(1, resume.getFullName());
st.setString(2, resume.getHomePage());
st.setString(3, resume.getLocation());
st.setString(4, resume.getUuid());
if (st.executeUpdate()==0) {
throw new WebAppException("Resume not found", resume);
}
}
replaceContact(conn, resume);
return null;
}
}
);
}
@Override
public Collection<Resume> getCollection() {
return Sql.execute("SELECT r.uuid, r.full_name, r.home_page, r.location, c.type, c.value FROM resume r LEFT JOIN contact c ON r.uuid = c.resume_uuid",
new SqlExecutor<Collection<Resume>>() {
@Override
public Collection<Resume> execute(PreparedStatement st) throws SQLException {
ResultSet rs = st.executeQuery();
Map<String, Resume> map = new HashMap<>();
while (rs.next()) {
String uuid = rs.getString("uuid");
Resume resume = map.get(uuid);
if (resume == null) {
resume = new Resume(uuid, rs.getString("full_name"), rs.getString("home_page"), rs.getString("location"));
map.put(uuid, resume);
}
addContact(rs, resume);
}
return map.values();
}
}
);
}
@Override
public int size() {
return Sql.execute("SELECT count(*) FROM RESUME", new SqlExecutor<Integer>() {
@Override
public Integer execute(PreparedStatement st) throws SQLException {
ResultSet rs = st.executeQuery();
rs.next();
return rs.getInt(1);
}
});
}
private static void replaceContact(Connection conn, Resume r) throws SQLException {
String uuid = r.getUuid();
try (PreparedStatement st = conn.prepareStatement("DELETE FROM contact WHERE resume_uuid=?")) {
st.setString(1, uuid);
st.execute();
}
try (PreparedStatement st = conn.prepareStatement("INSERT INTO contact (resume_uuid, type, value) VALUES (?,?,?)")) {
for (Map.Entry<ContactType, String> e : r.getContacts().entrySet()) {
st.setString(1, uuid);
st.setString(2, e.getKey().name());
st.setString(3, e.getValue());
st.addBatch();
}
st.executeBatch();
}
}
}