Skip to content

Commit aa4dd35

Browse files
author
Tanechka
committed
Lesson14 HW13
1 parent 62705ea commit aa4dd35

9 files changed

Lines changed: 142 additions & 38 deletions

File tree

‎config/populate.sql‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
INSERT INTO resume (uuid, full_name) VALUES
2+
('7de882da-02f2-4d16-8daa-60660aaf4071', 'Name1'),
3+
('a97b3ac3-3817-4c3f-8a5f-178497311f1d', 'Name2'),
4+
('dd0a70d1-5ed3-479a-b452-d5e04f21ca73', 'Name3');

‎src/ru/javawebinar/basejava/Config.java‎

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,8 @@
11
package ru.javawebinar.basejava;
22

3+
import ru.javawebinar.basejava.storage.SqlStorage;
4+
import ru.javawebinar.basejava.storage.Storage;
5+
36
import java.io.File;
47
import java.io.FileInputStream;
58
import java.io.IOException;
@@ -10,17 +13,19 @@ public class Config {
1013
private static final File PROPS = new File("config\\resumes.properties");
1114
private static final Config INSTANCE = new Config();
1215

13-
private Properties props = new Properties();
14-
private File storageDir;
16+
private final File storageDir;
17+
private final Storage storage;
1518

1619
public static Config get() {
1720
return INSTANCE;
1821
}
1922

2023
private Config() {
2124
try (InputStream is = new FileInputStream(PROPS)) {
25+
Properties props = new Properties();
2226
props.load(is);
2327
storageDir = new File(props.getProperty("storage.dir"));
28+
storage = new SqlStorage(props.getProperty("db.url"), props.getProperty("db.user"), props.getProperty("db.password"));
2429
} catch (IOException e) {
2530
throw new IllegalStateException("Invalid config file " + PROPS.getAbsolutePath());
2631
}
@@ -29,4 +34,8 @@ private Config() {
2934
public File getStorageDir() {
3035
return storageDir;
3136
}
32-
}
37+
38+
public Storage getStorage() {
39+
return storage;
40+
}
41+
}

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
package ru.javawebinar.basejava.sql;
2+
3+
import org.postgresql.util.PSQLException;
4+
import ru.javawebinar.basejava.exception.ExistStorageException;
5+
import ru.javawebinar.basejava.exception.StorageException;
6+
7+
import java.sql.SQLException;
8+
9+
public class ExceptionUtil {
10+
private ExceptionUtil() {
11+
}
12+
13+
public static StorageException convertException(SQLException e) {
14+
if (e instanceof PSQLException) {
15+
16+
// http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html
17+
if (e.getSQLState().equals("23505")) {
18+
return new ExistStorageException(null);
19+
}
20+
}
21+
return new StorageException(e);
22+
}
23+
}

‎src/ru/javawebinar/basejava/sql/SqlExecutor.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.PreparedStatement;
4+
import java.sql.SQLException;
5+
6+
public interface SqlExecutor<T> {
7+
T execute(PreparedStatement st) throws SQLException;
8+
}

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

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
package ru.javawebinar.basejava.sql;
2+
3+
import java.sql.Connection;
4+
import java.sql.PreparedStatement;
5+
import java.sql.SQLException;
6+
7+
public class SqlHelper {
8+
private final ConnectionFactory connectionFactory;
9+
10+
public SqlHelper(ConnectionFactory connectionFactory) {
11+
this.connectionFactory = connectionFactory;
12+
}
13+
14+
public void execute(String sql) {
15+
execute(sql, PreparedStatement::execute);
16+
}
17+
18+
public <T> T execute(String sql, SqlExecutor<T> executor) {
19+
try (Connection conn = connectionFactory.getConnection();
20+
PreparedStatement ps = conn.prepareStatement(sql)) {
21+
return executor.execute(ps);
22+
} catch (SQLException e) {
23+
throw ExceptionUtil.convertException(e);
24+
}
25+
}
26+
}

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

Lines changed: 40 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -1,75 +1,88 @@
11
package ru.javawebinar.basejava.storage;
22

33
import ru.javawebinar.basejava.exception.NotExistStorageException;
4-
import ru.javawebinar.basejava.exception.StorageException;
54
import ru.javawebinar.basejava.model.Resume;
6-
import ru.javawebinar.basejava.sql.ConnectionFactory;
5+
import ru.javawebinar.basejava.sql.SqlHelper;
76

8-
import java.sql.*;
7+
import java.sql.DriverManager;
8+
import java.sql.ResultSet;
9+
import java.util.ArrayList;
910
import java.util.List;
1011

1112
public class SqlStorage implements Storage {
12-
public final ConnectionFactory connectionFactory;
13+
public final SqlHelper sqlHelper;
1314

1415
public SqlStorage(String dbUrl, String dbUser, String dbPassword) {
15-
connectionFactory = () -> DriverManager.getConnection(dbUrl, dbUser, dbPassword);
16+
sqlHelper = new SqlHelper(() -> DriverManager.getConnection(dbUrl, dbUser, dbPassword));
1617
}
1718

1819
@Override
1920
public void clear() {
20-
try (Connection conn = connectionFactory.getConnection();
21-
PreparedStatement ps = conn.prepareStatement("DELETE FROM resume")) {
22-
ps.execute();
23-
} catch (SQLException e) {
24-
throw new StorageException(e);
25-
}
21+
sqlHelper.execute("DELETE FROM resume");
2622
}
2723

2824
@Override
2925
public Resume get(String uuid) {
30-
try (Connection conn = connectionFactory.getConnection();
31-
PreparedStatement ps = conn.prepareStatement("SELECT * FROM resume r WHERE r.uuid =?")) {
26+
return sqlHelper.execute("SELECT * FROM resume r WHERE r.uuid =?", ps -> {
3227
ps.setString(1, uuid);
3328
ResultSet rs = ps.executeQuery();
3429
if (!rs.next()) {
3530
throw new NotExistStorageException(uuid);
3631
}
3732
return new Resume(uuid, rs.getString("full_name"));
38-
} catch (SQLException e) {
39-
throw new StorageException(e);
40-
}
33+
});
4134
}
4235

4336
@Override
4437
public void update(Resume r) {
45-
38+
sqlHelper.execute("UPDATE resume SET full_name = ? WHERE uuid = ?", ps -> {
39+
ps.setString(1, r.getFullName());
40+
ps.setString(2, r.getUuid());
41+
if (ps.executeUpdate() == 0) {
42+
throw new NotExistStorageException(r.getUuid());
43+
}
44+
return null;
45+
});
4646
}
4747

4848
@Override
4949
public void save(Resume r) {
50-
try (Connection conn = connectionFactory.getConnection();
51-
PreparedStatement ps = conn.prepareStatement("INSERT INTO resume (uuid, full_name) VALUES (?,?)")) {
50+
sqlHelper.<Void>execute("INSERT INTO resume (uuid, full_name) VALUES (?,?)", ps -> {
5251
ps.setString(1, r.getUuid());
5352
ps.setString(2, r.getFullName());
5453
ps.execute();
55-
} catch (SQLException e) {
56-
throw new StorageException(e);
57-
}
58-
54+
return null;
55+
});
5956
}
6057

6158
@Override
6259
public void delete(String uuid) {
63-
60+
sqlHelper.execute("DELETE FROM resume WHERE uuid=?", ps -> {
61+
ps.setString(1, uuid);
62+
if (ps.executeUpdate() == 0) {
63+
throw new NotExistStorageException(uuid);
64+
}
65+
return null;
66+
});
6467
}
6568

6669
@Override
6770
public List<Resume> getAllSorted() {
68-
return null;
71+
return sqlHelper.execute("SELECT * FROM resume r ORDER BY full_name,uuid", ps -> {
72+
ResultSet rs = ps.executeQuery();
73+
List<Resume> resumes = new ArrayList<>();
74+
while (rs.next()) {
75+
resumes.add(new Resume(rs.getString("uuid"), rs.getString("full_name")));
76+
}
77+
return resumes;
78+
});
6979
}
7080

7181
@Override
7282
public int size() {
73-
return 0;
83+
return sqlHelper.execute("SELECT count(*) FROM resume", st -> {
84+
ResultSet rs = st.executeQuery();
85+
return rs.next() ? rs.getInt(1) : 0;
86+
});
7487
}
75-
}
88+
}

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

Lines changed: 13 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,9 @@
99

1010
import java.io.File;
1111
import java.util.Arrays;
12+
import java.util.Collections;
1213
import java.util.List;
14+
import java.util.UUID;
1315

1416
import static org.junit.Assert.assertEquals;
1517
import static org.junit.Assert.assertTrue;
@@ -19,10 +21,10 @@ public abstract class AbstractStorageTest {
1921

2022
protected Storage storage;
2123

22-
private static final String UUID_1 = "uuid1";
23-
private static final String UUID_2 = "uuid2";
24-
private static final String UUID_3 = "uuid3";
25-
private static final String UUID_4 = "uuid4";
24+
private static final String UUID_1 = UUID.randomUUID().toString();
25+
private static final String UUID_2 = UUID.randomUUID().toString();
26+
private static final String UUID_3 = UUID.randomUUID().toString();
27+
private static final String UUID_4 = UUID.randomUUID().toString();
2628

2729
private static final Resume R1;
2830
private static final Resume R2;
@@ -35,7 +37,8 @@ public abstract class AbstractStorageTest {
3537
R3 = new Resume(UUID_3, "Name3");
3638
R4 = new Resume(UUID_4, "Name4");
3739

38-
/*R1.addContact(ContactType.MAIL, "mail1@ya.ru");
40+
/*
41+
R1.addContact(ContactType.MAIL, "mail1@ya.ru");
3942
R1.addContact(ContactType.PHONE, "11111");
4043
R1.addSection(SectionType.OBJECTIVE, new TextSection("Objective1"));
4144
R1.addSection(SectionType.PERSONAL, new TextSection("Personal data"));
@@ -57,7 +60,8 @@ public abstract class AbstractStorageTest {
5760
R1.addSection(SectionType.EXPERIENCE,
5861
new OrganizationSection(
5962
new Organization("Organization2", "http://Organization2.ru",
60-
new Organization.Position(2015, Month.JANUARY, "position1", "content1"))));*/
63+
new Organization.Position(2015, Month.JANUARY, "position1", "content1"))));
64+
*/
6165
}
6266

6367
protected AbstractStorageTest(Storage storage) {
@@ -99,7 +103,9 @@ public void updateNotExist() throws Exception {
99103
public void getAllSorted() throws Exception {
100104
List<Resume> list = storage.getAllSorted();
101105
assertEquals(3, list.size());
102-
assertEquals(list, Arrays.asList(R1, R2, R3));
106+
List<Resume> sortedResumes = Arrays.asList(R1, R2, R3);
107+
Collections.sort(sortedResumes);
108+
assertEquals(list, sortedResumes);
103109
}
104110

105111
@Test

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,8 @@
1515
ObjectPathStorageTest.class,
1616
XmlPathStorageTest.class,
1717
JsonPathStorageTest.class,
18-
DataPathStorageTest.class
18+
DataPathStorageTest.class,
19+
SqlStorageTest.class
1920
})
2021
public class AllStorageTest {
2122
}

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
package ru.javawebinar.basejava.storage;
2+
3+
import ru.javawebinar.basejava.Config;
4+
5+
/**
6+
* gkislin
7+
* 23.06.2016
8+
*/
9+
public class SqlStorageTest extends AbstractStorageTest {
10+
11+
public SqlStorageTest() {
12+
super(Config.get().getStorage());
13+
}
14+
}

0 commit comments

Comments
 (0)