Lesson15 HW14 · Basicprof/basejava@5e571f2 · GitHub
Skip to content

Commit 5e571f2

Browse files
BasicprofBasicprof
committed
Lesson15 HW14
1 parent 8dcf573 commit 5e571f2

6 files changed

Lines changed: 92 additions & 23 deletions

File tree

‎config/populate.sql‎

Lines changed: 1 addition & 1 deletion

‎config/populateContact.sql‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
INSERT INTO contact (resume_uuid, type, value) VALUES
2+
('dd0a70d1-5ed3-479a-b452-d5e04f21ca73', 'PHONE','380505132950'),
3+
('dd0a70d1-5ed3-479a-b452-d5e04f21ca73', 'SKYPE', 'ip500800') ;

‎src/ru/javawebinar/basejava/sql/SqlHelper.java‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,7 @@
11
package ru.javawebinar.basejava.sql;
22

3+
import ru.javawebinar.basejava.exception.StorageException;
4+
35
import java.sql.Connection;
46
import java.sql.PreparedStatement;
57
import java.sql.SQLException;
@@ -23,4 +25,20 @@ public <T> T execute(String sql, SqlExecutor<T> executor) {
2325
throw ExceptionUtil.convertException(e);
2426
}
2527
}
28+
29+
public <T> T transactionalExecute(SqlTransaction<T> executor) {
30+
try (Connection conn = connectionFactory.getConnection()) {
31+
try {
32+
conn.setAutoCommit(false);
33+
T res = executor.execute(conn);
34+
conn.commit();
35+
return res;
36+
} catch (SQLException e) {
37+
conn.rollback();
38+
throw ExceptionUtil.convertException(e);
39+
}
40+
} catch (SQLException e) {
41+
throw new StorageException(e);
42+
}
43+
}
2644
}

‎src/ru/javawebinar/basejava/sql/SqlTransaction.java‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
package ru.javawebinar.basejava.sql;
2+
3+
import java.sql.Connection;
4+
import java.sql.SQLException;
5+
6+
public interface SqlTransaction<T> {
7+
T execute(Connection conn) throws SQLException;
8+
}

‎src/ru/javawebinar/basejava/storage/SqlStorage.java‎

Lines changed: 53 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -5,9 +5,9 @@
55
import ru.javawebinar.basejava.model.Resume;
66
import ru.javawebinar.basejava.sql.SqlHelper;
77

8-
import java.sql.DriverManager;
9-
import java.sql.ResultSet;
8+
import java.sql.*;
109
import java.util.ArrayList;
10+
import java.util.LinkedHashMap;
1111
import java.util.List;
1212
import java.util.Map;
1313

@@ -38,9 +38,7 @@ public Resume get(String uuid) {
3838
}
3939
Resume r = new Resume(uuid, rs.getString("full_name"));
4040
do {
41-
String value = rs.getString("value");
42-
ContactType type = ContactType.valueOf(rs.getString("type"));
43-
r.addContact(type, value);
41+
addContact(rs, r);
4442
} while (rs.next());
4543

4644
return r;
@@ -49,33 +47,32 @@ public Resume get(String uuid) {
4947

5048
@Override
5149
public void update(Resume r) {
52-
sqlHelper.execute("UPDATE resume SET full_name = ? WHERE uuid = ?", ps -> {
50+
sqlHelper.transactionalExecute(conn -> {
51+
try (PreparedStatement ps = conn.prepareStatement("UPDATE resume SET full_name = ? WHERE uuid = ?")) {
5352
ps.setString(1, r.getFullName());
5453
ps.setString(2, r.getUuid());
55-
if (ps.executeUpdate() == 0) {
54+
if (ps.executeUpdate() != 1) {
5655
throw new NotExistStorageException(r.getUuid());
5756
}
57+
}
58+
deleteContacts(conn, r);
59+
insertContact(conn, r);
5860
return null;
5961
});
6062
}
6163

6264
@Override
6365
public void save(Resume r) {
64-
sqlHelper.<Void>execute("INSERT INTO resume (uuid, full_name) VALUES (?,?)", ps -> {
66+
sqlHelper.transactionalExecute(conn -> {
67+
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO resume (uuid, full_name) VALUES (?,?)")) {
6568
ps.setString(1, r.getUuid());
6669
ps.setString(2, r.getFullName());
6770
ps.execute();
68-
return null;
69-
});
70-
for (Map.Entry<ContactType, String> e : r.getContacts().entrySet()) {
71-
sqlHelper.<Void>execute("INSERT INTO contact (resume_uuid, type, value) VALUES (?,?,?)", ps -> {
72-
ps.setString(1, r.getUuid());
73-
ps.setString(2, e.getKey().name());
74-
ps.setString(3, e.getValue());
71+
}
72+
insertContact(conn, r);
7573
return null;
7674
});
7775
}
78-
}
7976

8077
@Override
8178
public void delete(String uuid) {
@@ -90,13 +87,22 @@ public void delete(String uuid) {
9087

9188
@Override
9289
public List<Resume> getAllSorted() {
93-
return sqlHelper.execute("SELECT * FROM resume r ORDER BY full_name,uuid", ps -> {
90+
return sqlHelper.execute("" +
91+
" SELECT * FROM resume r\n" +
92+
"LEFT JOIN contact c ON r.uuid = c.resume_uuid\n" +
93+
"ORDER BY full_name, uuid", ps -> {
9494
ResultSet rs = ps.executeQuery();
95-
List<Resume> resumes = new ArrayList<>();
95+
Map<String, Resume> map = new LinkedHashMap<>();
9696
while (rs.next()) {
97-
resumes.add(new Resume(rs.getString("uuid"), rs.getString("full_name")));
97+
String uuid = rs.getString("uuid");
98+
Resume resume = map.get(uuid);
99+
if (resume == null) {
100+
resume = new Resume(uuid, rs.getString("full_name"));
101+
map.put(uuid, resume);
98102
}
99-
return resumes;
103+
addContact(rs, resume);
104+
}
105+
return new ArrayList<>(map.values());
100106
});
101107
}
102108

@@ -107,4 +113,31 @@ public int size() {
107113
return rs.next() ? rs.getInt(1) : 0;
108114
});
109115
}
116+
117+
private void insertContact(Connection conn, Resume r) throws SQLException {
118+
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO contact (resume_uuid, type, value) VALUES (?,?,?)")) {
119+
for (Map.Entry<ContactType, String> e : r.getContacts().entrySet()) {
120+
ps.setString(1, r.getUuid());
121+
ps.setString(2, e.getKey().name());
122+
ps.setString(3, e.getValue());
123+
ps.addBatch();
124+
}
125+
ps.executeBatch();
126+
}
127+
}
128+
129+
private void deleteContacts(Connection conn, Resume r) {
130+
sqlHelper.execute("DELETE FROM contact WHERE resume_uuid=?", ps -> {
131+
ps.setString(1, r.getUuid());
132+
ps.execute();
133+
return null;
134+
});
135+
}
136+
137+
private void addContact(ResultSet rs, Resume r) throws SQLException {
138+
String value = rs.getString("value");
139+
if (value != null) {
140+
r.addContact(ContactType.valueOf(rs.getString("type")), value);
141+
}
142+
}
110143
}

‎test/ru/javawebinar/basejava/storage/AbstractStorageTest.java‎

Lines changed: 9 additions & 2 deletions

0 commit comments

Comments
 (0)