Skip to content

Commit 02aee97

Browse files
author
Tanechka
committed
Lesson15 HW14
1 parent 0901c3a commit 02aee97

File tree

2 files changed

+81
-46
lines changed

2 files changed

+81
-46
lines changed

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

Lines changed: 72 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -5,10 +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.PreparedStatement;
10-
import java.sql.ResultSet;
8+
import java.sql.*;
119
import java.util.ArrayList;
10+
import java.util.LinkedHashMap;
1211
import java.util.List;
1312
import java.util.Map;
1413

@@ -31,55 +30,48 @@ public Resume get(String uuid) {
3130
" LEFT JOIN contact c " +
3231
" ON r.uuid = c.resume_uuid " +
3332
" WHERE r.uuid =? ",
34-
ps -> {
35-
ps.setString(1, uuid);
36-
ResultSet rs = ps.executeQuery();
37-
if (!rs.next()) {
38-
throw new NotExistStorageException(uuid);
39-
}
40-
Resume r = new Resume(uuid, rs.getString("full_name"));
41-
do {
42-
String value = rs.getString("value");
43-
ContactType type = ContactType.valueOf(rs.getString("type"));
44-
r.addContact(type, value);
45-
} while (rs.next());
33+
ps -> {
34+
ps.setString(1, uuid);
35+
ResultSet rs = ps.executeQuery();
36+
if (!rs.next()) {
37+
throw new NotExistStorageException(uuid);
38+
}
39+
Resume r = new Resume(uuid, rs.getString("full_name"));
40+
do {
41+
addContact(rs, r);
42+
} while (rs.next());
4643

47-
return r;
48-
});
44+
return r;
45+
});
4946
}
5047

5148
@Override
5249
public void update(Resume r) {
53-
sqlHelper.execute("UPDATE resume SET full_name = ? WHERE uuid = ?", ps -> {
54-
ps.setString(1, r.getFullName());
55-
ps.setString(2, r.getUuid());
56-
if (ps.executeUpdate() == 0) {
57-
throw new NotExistStorageException(r.getUuid());
50+
sqlHelper.transactionalExecute(conn -> {
51+
try (PreparedStatement ps = conn.prepareStatement("UPDATE resume SET full_name = ? WHERE uuid = ?")) {
52+
ps.setString(1, r.getFullName());
53+
ps.setString(2, r.getUuid());
54+
if (ps.executeUpdate() != 1) {
55+
throw new NotExistStorageException(r.getUuid());
56+
}
5857
}
58+
deleteContacts(conn, r);
59+
insertContact(conn, r);
5960
return null;
6061
});
6162
}
6263

6364
@Override
6465
public void save(Resume r) {
6566
sqlHelper.transactionalExecute(conn -> {
66-
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO resume (uuid, full_name) VALUES (?,?)")) {
67-
ps.setString(1, r.getUuid());
68-
ps.setString(2, r.getFullName());
69-
ps.execute();
70-
}
71-
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO contact (resume_uuid, type, value) VALUES (?,?,?)")) {
72-
for (Map.Entry<ContactType, String> e : r.getContacts().entrySet()) {
73-
ps.setString(1, r.getUuid());
74-
ps.setString(2, e.getKey().name());
75-
ps.setString(3, e.getValue());
76-
ps.addBatch();
77-
}
78-
ps.executeBatch();
79-
}
80-
return null;
81-
}
82-
);
67+
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO resume (uuid, full_name) VALUES (?,?)")) {
68+
ps.setString(1, r.getUuid());
69+
ps.setString(2, r.getFullName());
70+
ps.execute();
71+
}
72+
insertContact(conn, r);
73+
return null;
74+
});
8375
}
8476

8577
@Override
@@ -95,13 +87,22 @@ public void delete(String uuid) {
9587

9688
@Override
9789
public List<Resume> getAllSorted() {
98-
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 -> {
9994
ResultSet rs = ps.executeQuery();
100-
List<Resume> resumes = new ArrayList<>();
95+
Map<String, Resume> map = new LinkedHashMap<>();
10196
while (rs.next()) {
102-
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);
102+
}
103+
addContact(rs, resume);
103104
}
104-
return resumes;
105+
return new ArrayList<>(map.values());
105106
});
106107
}
107108

@@ -112,4 +113,31 @@ public int size() {
112113
return rs.next() ? rs.getInt(1) : 0;
113114
});
114115
}
115-
}
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+
}
143+
}

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

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@
55
import ru.javawebinar.basejava.Config;
66
import ru.javawebinar.basejava.exception.ExistStorageException;
77
import ru.javawebinar.basejava.exception.NotExistStorageException;
8+
import ru.javawebinar.basejava.model.ContactType;
89
import ru.javawebinar.basejava.model.Resume;
910

1011
import java.io.File;
@@ -37,9 +38,12 @@ public abstract class AbstractStorageTest {
3738
R3 = new Resume(UUID_3, "Name3");
3839
R4 = new Resume(UUID_4, "Name4");
3940

40-
/*
4141
R1.addContact(ContactType.MAIL, "mail1@ya.ru");
4242
R1.addContact(ContactType.PHONE, "11111");
43+
44+
R4.addContact(ContactType.PHONE, "44444");
45+
R4.addContact(ContactType.SKYPE, "Skype");
46+
/*
4347
R1.addSection(SectionType.OBJECTIVE, new TextSection("Objective1"));
4448
R1.addSection(SectionType.PERSONAL, new TextSection("Personal data"));
4549
R1.addSection(SectionType.ACHIEVEMENT, new ListSection("Achivment11", "Achivment12", "Achivment13"));
@@ -90,6 +94,9 @@ public void clear() throws Exception {
9094
@Test
9195
public void update() throws Exception {
9296
Resume newResume = new Resume(UUID_1, "New Name");
97+
R1.addContact(ContactType.MAIL, "mail1@google.com");
98+
R1.addContact(ContactType.SKYPE, "NewSkype");
99+
R1.addContact(ContactType.MOBILE, "+7 921 222-22-22");
93100
storage.update(newResume);
94101
assertTrue(newResume.equals(storage.get(UUID_1)));
95102
}
@@ -105,7 +112,7 @@ public void getAllSorted() throws Exception {
105112
assertEquals(3, list.size());
106113
List<Resume> sortedResumes = Arrays.asList(R1, R2, R3);
107114
Collections.sort(sortedResumes);
108-
assertEquals(list, sortedResumes);
115+
assertEquals(sortedResumes, list);
109116
}
110117

111118
@Test

0 commit comments

Comments
 (0)