Skip to content

Commit 39775ee

Browse files
author
Tanechka
committed
Lesson16 HW15_DB
1 parent 1487eef commit 39775ee

File tree

6 files changed

+200
-87
lines changed

6 files changed

+200
-87
lines changed

‎config/init_db.sql‎

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,4 +10,13 @@ CREATE TABLE contact (
1010
value TEXT NOT NULL
1111
);
1212
CREATE UNIQUE INDEX contact_uuid_type_index
13-
ON contact (resume_uuid, type);
13+
ON contact (resume_uuid, type);
14+
15+
CREATE TABLE section (
16+
id SERIAL PRIMARY KEY,
17+
resume_uuid CHAR(36) NOT NULL REFERENCES resume (uuid) ON DELETE CASCADE,
18+
type TEXT NOT NULL,
19+
content TEXT NOT NULL
20+
);
21+
CREATE UNIQUE INDEX section_idx
22+
ON section (resume_uuid, type);

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

Lines changed: 96 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -3,16 +3,17 @@
33
import ru.javawebinar.basejava.exception.NotExistStorageException;
44
import ru.javawebinar.basejava.model.ContactType;
55
import ru.javawebinar.basejava.model.Resume;
6+
import ru.javawebinar.basejava.model.Section;
7+
import ru.javawebinar.basejava.model.SectionType;
68
import ru.javawebinar.basejava.sql.SqlHelper;
9+
import ru.javawebinar.basejava.util.JsonParser;
710

811
import java.sql.*;
912
import java.util.ArrayList;
1013
import java.util.LinkedHashMap;
1114
import java.util.List;
1215
import java.util.Map;
1316

14-
// TODO implement Section (except OrganizationSection)
15-
// TODO Join and split ListSection by `\n`
1617
public class SqlStorage implements Storage {
1718
public final SqlHelper sqlHelper;
1819

@@ -27,23 +28,34 @@ public void clear() {
2728

2829
@Override
2930
public Resume get(String uuid) {
30-
return sqlHelper.execute("" +
31-
" SELECT * FROM resume r " +
32-
" LEFT JOIN contact c " +
33-
" ON r.uuid = c.resume_uuid " +
34-
" WHERE r.uuid =? ",
35-
ps -> {
31+
return sqlHelper.transactionalExecute(conn -> {
32+
Resume r;
33+
try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM resume WHERE uuid =?")) {
3634
ps.setString(1, uuid);
3735
ResultSet rs = ps.executeQuery();
3836
if (!rs.next()) {
3937
throw new NotExistStorageException(uuid);
4038
}
41-
Resume r = new Resume(uuid, rs.getString("full_name"));
42-
do {
39+
r = new Resume(uuid, rs.getString("full_name"));
40+
}
41+
42+
try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM contact WHERE resume_uuid =?")) {
43+
ps.setString(1, uuid);
44+
ResultSet rs = ps.executeQuery();
45+
while (rs.next()) {
4346
addContact(rs, r);
44-
} while (rs.next());
47+
}
48+
}
4549

46-
return r;
50+
try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM section WHERE resume_uuid =?")) {
51+
ps.setString(1, uuid);
52+
ResultSet rs = ps.executeQuery();
53+
while (rs.next()) {
54+
addSection(rs, r);
55+
}
56+
}
57+
58+
return r;
4759
});
4860
}
4961

@@ -58,22 +70,26 @@ public void update(Resume r) {
5870
}
5971
}
6072
deleteContacts(conn, r);
61-
insertContact(conn, r);
73+
deleteSections(conn, r);
74+
insertContacts(conn, r);
75+
insertSections(conn, r);
6276
return null;
6377
});
6478
}
6579

6680
@Override
6781
public void save(Resume r) {
6882
sqlHelper.transactionalExecute(conn -> {
69-
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO resume (uuid, full_name) VALUES (?,?)")) {
70-
ps.setString(1, r.getUuid());
71-
ps.setString(2, r.getFullName());
72-
ps.execute();
73-
}
74-
insertContact(conn, r);
75-
return null;
76-
});
83+
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO resume (uuid, full_name) VALUES (?,?)")) {
84+
ps.setString(1, r.getUuid());
85+
ps.setString(2, r.getFullName());
86+
ps.execute();
87+
}
88+
insertContacts(conn, r);
89+
insertSections(conn, r);
90+
return null;
91+
}
92+
);
7793
}
7894

7995
@Override
@@ -89,22 +105,34 @@ public void delete(String uuid) {
89105

90106
@Override
91107
public List<Resume> getAllSorted() {
92-
return sqlHelper.execute("" +
93-
" SELECT * FROM resume r\n" +
94-
"LEFT JOIN contact c ON r.uuid = c.resume_uuid\n" +
95-
"ORDER BY full_name, uuid", ps -> {
96-
ResultSet rs = ps.executeQuery();
97-
Map<String, Resume> map = new LinkedHashMap<>();
98-
while (rs.next()) {
99-
String uuid = rs.getString("uuid");
100-
Resume resume = map.get(uuid);
101-
if (resume == null) {
102-
resume = new Resume(uuid, rs.getString("full_name"));
103-
map.put(uuid, resume);
108+
return sqlHelper.transactionalExecute(conn -> {
109+
Map<String, Resume> resumes = new LinkedHashMap<>();
110+
111+
try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM resume ORDER BY full_name, uuid")) {
112+
ResultSet rs = ps.executeQuery();
113+
while (rs.next()) {
114+
String uuid = rs.getString("uuid");
115+
resumes.put(uuid, new Resume(uuid, rs.getString("full_name")));
116+
}
117+
}
118+
119+
try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM contact")) {
120+
ResultSet rs = ps.executeQuery();
121+
while (rs.next()) {
122+
Resume r = resumes.get(rs.getString("resume_uuid"));
123+
addContact(rs, r);
104124
}
105-
addContact(rs, resume);
106125
}
107-
return new ArrayList<>(map.values());
126+
127+
try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM section")) {
128+
ResultSet rs = ps.executeQuery();
129+
while (rs.next()) {
130+
Resume r = resumes.get(rs.getString("resume_uuid"));
131+
addSection(rs, r);
132+
}
133+
}
134+
135+
return new ArrayList<>(resumes.values());
108136
});
109137
}
110138

@@ -116,7 +144,7 @@ public int size() {
116144
});
117145
}
118146

119-
private void insertContact(Connection conn, Resume r) throws SQLException {
147+
private void insertContacts(Connection conn, Resume r) throws SQLException {
120148
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO contact (resume_uuid, type, value) VALUES (?,?,?)")) {
121149
for (Map.Entry<ContactType, String> e : r.getContacts().entrySet()) {
122150
ps.setString(1, r.getUuid());
@@ -128,12 +156,32 @@ private void insertContact(Connection conn, Resume r) throws SQLException {
128156
}
129157
}
130158

131-
private void deleteContacts(Connection conn, Resume r) {
132-
sqlHelper.execute("DELETE FROM contact WHERE resume_uuid=?", ps -> {
159+
private void insertSections(Connection conn, Resume r) throws SQLException {
160+
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO section (resume_uuid, type, content) VALUES (?,?,?)")) {
161+
for (Map.Entry<SectionType, Section> e : r.getSections().entrySet()) {
162+
ps.setString(1, r.getUuid());
163+
ps.setString(2, e.getKey().name());
164+
Section section = e.getValue();
165+
ps.setString(3, JsonParser.write(section, Section.class));
166+
ps.addBatch();
167+
}
168+
ps.executeBatch();
169+
}
170+
}
171+
172+
private void deleteContacts(Connection conn, Resume r) throws SQLException {
173+
deleteAttributes(conn, r, "DELETE FROM contact WHERE resume_uuid=?");
174+
}
175+
176+
private void deleteSections(Connection conn, Resume r) throws SQLException {
177+
deleteAttributes(conn, r, "DELETE FROM section WHERE resume_uuid=?");
178+
}
179+
180+
private void deleteAttributes(Connection conn, Resume r, String sql) throws SQLException {
181+
try (PreparedStatement ps = conn.prepareStatement(sql)) {
133182
ps.setString(1, r.getUuid());
134183
ps.execute();
135-
return null;
136-
});
184+
}
137185
}
138186

139187
private void addContact(ResultSet rs, Resume r) throws SQLException {
@@ -142,4 +190,12 @@ private void addContact(ResultSet rs, Resume r) throws SQLException {
142190
r.addContact(ContactType.valueOf(rs.getString("type")), value);
143191
}
144192
}
193+
194+
private void addSection(ResultSet rs, Resume r) throws SQLException {
195+
String content = rs.getString("content");
196+
if (content != null) {
197+
SectionType type = SectionType.valueOf(rs.getString("type"));
198+
r.addSection(type, JsonParser.read(content, Section.class));
199+
}
200+
}
145201
}

‎src/ru/javawebinar/basejava/util/JsonParser.java‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,4 +20,15 @@ public static <T> void write(T object, Writer writer) {
2020
GSON.toJson(object, writer);
2121
}
2222

23+
public static <T> T read(String content, Class<T> clazz) {
24+
return GSON.fromJson(content, clazz);
25+
}
26+
27+
public static <T> String write(T object) {
28+
return GSON.toJson(object);
29+
}
30+
31+
public static <T> String write(T object, Class<T> clazz) {
32+
return GSON.toJson(object, clazz);
33+
}
2334
}

‎test/ru/javawebinar/basejava/TestData.java‎

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,54 @@
1+
package ru.javawebinar.basejava;
2+
3+
import ru.javawebinar.basejava.model.*;
4+
5+
import java.time.Month;
6+
import java.util.UUID;
7+
8+
public class TestData {
9+
public static final String UUID_1 = UUID.randomUUID().toString();
10+
public static final String UUID_2 = UUID.randomUUID().toString();
11+
public static final String UUID_3 = UUID.randomUUID().toString();
12+
public static final String UUID_4 = UUID.randomUUID().toString();
13+
14+
public static final Resume R1;
15+
public static final Resume R2;
16+
public static final Resume R3;
17+
public static final Resume R4;
18+
19+
static {
20+
R1 = new Resume(UUID_1, "Name1");
21+
R2 = new Resume(UUID_2, "Name2");
22+
R3 = new Resume(UUID_3, "Name3");
23+
R4 = new Resume(UUID_4, "Name4");
24+
25+
R1.addContact(ContactType.MAIL, "mail1@ya.ru");
26+
R1.addContact(ContactType.PHONE, "11111");
27+
28+
R4.addContact(ContactType.PHONE, "44444");
29+
R4.addContact(ContactType.SKYPE, "Skype");
30+
31+
R1.addSection(SectionType.OBJECTIVE, new TextSection("Objective1"));
32+
R1.addSection(SectionType.PERSONAL, new TextSection("Personal data"));
33+
R1.addSection(SectionType.ACHIEVEMENT, new ListSection("Achivment11", "Achivment12", "Achivment13"));
34+
R1.addSection(SectionType.QUALIFICATIONS, new ListSection("Java", "SQL", "JavaScript"));
35+
R1.addSection(SectionType.EXPERIENCE,
36+
new OrganizationSection(
37+
new Organization("Organization11", "http://Organization11.ru",
38+
new Organization.Position(2005, Month.JANUARY, "position1", "content1"),
39+
new Organization.Position(2001, Month.MARCH, 2005, Month.JANUARY, "position2", "content2"))));
40+
R1.addSection(SectionType.EXPERIENCE,
41+
new OrganizationSection(
42+
new Organization("Organization2", "http://Organization2.ru",
43+
new Organization.Position(2015, Month.JANUARY, "position1", "content1"))));
44+
R1.addSection(SectionType.EDUCATION,
45+
new OrganizationSection(
46+
new Organization("Institute", null,
47+
new Organization.Position(1996, Month.JANUARY, 2000, Month.DECEMBER, "aspirant", null),
48+
new Organization.Position(2001, Month.MARCH, 2005, Month.JANUARY, "student", "IT facultet")),
49+
new Organization("Organization12", "http://Organization12.ru")));
50+
51+
R2.addContact(ContactType.SKYPE, "skype2");
52+
R2.addContact(ContactType.PHONE, "22222");
53+
}
54+
}

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

Lines changed: 1 addition & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -12,61 +12,16 @@
1212
import java.util.Arrays;
1313
import java.util.Collections;
1414
import java.util.List;
15-
import java.util.UUID;
1615

1716
import static org.junit.Assert.assertEquals;
1817
import static org.junit.Assert.assertTrue;
18+
import static ru.javawebinar.basejava.TestData.*;
1919

2020
public abstract class AbstractStorageTest {
2121
protected static final File STORAGE_DIR = Config.get().getStorageDir();
2222

2323
protected Storage storage;
2424

25-
private static final String UUID_1 = UUID.randomUUID().toString();
26-
private static final String UUID_2 = UUID.randomUUID().toString();
27-
private static final String UUID_3 = UUID.randomUUID().toString();
28-
private static final String UUID_4 = UUID.randomUUID().toString();
29-
30-
private static final Resume R1;
31-
private static final Resume R2;
32-
private static final Resume R3;
33-
private static final Resume R4;
34-
35-
static {
36-
R1 = new Resume(UUID_1, "Name1");
37-
R2 = new Resume(UUID_2, "Name2");
38-
R3 = new Resume(UUID_3, "Name3");
39-
R4 = new Resume(UUID_4, "Name4");
40-
41-
R1.addContact(ContactType.MAIL, "mail1@ya.ru");
42-
R1.addContact(ContactType.PHONE, "11111");
43-
44-
R4.addContact(ContactType.PHONE, "44444");
45-
R4.addContact(ContactType.SKYPE, "Skype");
46-
/*
47-
R1.addSection(SectionType.OBJECTIVE, new TextSection("Objective1"));
48-
R1.addSection(SectionType.PERSONAL, new TextSection("Personal data"));
49-
R1.addSection(SectionType.ACHIEVEMENT, new ListSection("Achivment11", "Achivment12", "Achivment13"));
50-
R1.addSection(SectionType.QUALIFICATIONS, new ListSection("Java", "SQL", "JavaScript"));
51-
R1.addSection(SectionType.EXPERIENCE,
52-
new OrganizationSection(
53-
new Organization("Organization11", "http://Organization11.ru",
54-
new Organization.Position(2005, Month.JANUARY, "position1", "content1"),
55-
new Organization.Position(2001, Month.MARCH, 2005, Month.JANUARY, "position2", "content2"))));
56-
R1.addSection(SectionType.EDUCATION,
57-
new OrganizationSection(
58-
new Organization("Institute", null,
59-
new Organization.Position(1996, Month.JANUARY, 2000, Month.DECEMBER, "aspirant", null),
60-
new Organization.Position(2001, Month.MARCH, 2005, Month.JANUARY, "student", "IT facultet")),
61-
new Organization("Organization12", "http://Organization12.ru")));
62-
R2.addContact(ContactType.SKYPE, "skype2");
63-
R2.addContact(ContactType.PHONE, "22222");
64-
R1.addSection(SectionType.EXPERIENCE,
65-
new OrganizationSection(
66-
new Organization("Organization2", "http://Organization2.ru",
67-
new Organization.Position(2015, Month.JANUARY, "position1", "content1"))));
68-
*/
69-
}
7025

7126
protected AbstractStorageTest(Storage storage) {
7227
this.storage = storage;

‎test/ru/javawebinar/basejava/util/JsonParserTest.java‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
package ru.javawebinar.basejava.util;
2+
3+
import org.junit.Assert;
4+
import org.junit.Test;
5+
import ru.javawebinar.basejava.model.Resume;
6+
import ru.javawebinar.basejava.model.Section;
7+
import ru.javawebinar.basejava.model.TextSection;
8+
9+
import static ru.javawebinar.basejava.TestData.R1;
10+
11+
public class JsonParserTest {
12+
@Test
13+
public void testResume() throws Exception {
14+
String json = JsonParser.write(R1);
15+
System.out.println(json);
16+
Resume resume = JsonParser.read(json, Resume.class);
17+
Assert.assertEquals(R1, resume);
18+
}
19+
20+
@Test
21+
public void write() throws Exception {
22+
Section section1 = new TextSection("Objective1");
23+
String json = JsonParser.write(section1, Section.class);
24+
System.out.println(json);
25+
Section section2 = JsonParser.read(json, Section.class);
26+
Assert.assertEquals(section1, section2);
27+
}
28+
}

0 commit comments

Comments
 (0)