lesson 14 video 2

AlbertoJava · AlbertoJava · commit 21bd48e8bbc5 · 2019-11-10T22:54:26.000+03:00
diff --git a/src/com/urise/webapp/sql/SqlHelper.java b/src/com/urise/webapp/sql/SqlHelper.java
@@ -14,6 +14,23 @@ public SqlHelper(String dbUrl, String dbUser, String dbPassword) {
         this.connectionFactory = () -> DriverManager.getConnection(dbUrl, dbUser, dbPassword);
     }
 
+    public <T> T transactionalExecute(SqlTransaction<T> executor) {
+        try (Connection conn = connectionFactory.getConnection()) {
+            try{
+            conn.setAutoCommit(false);//&#1087;&#1086;&#1089;&#1083;&#1077; &#1074;&#1099;&#1087;&#1086;&#1085;&#1077;&#1085;&#1080;&#1103; &#1085;&#1077; &#1073;&#1091;&#1076;&#1077;&#1090; &#1074;&#1099;&#1087;&#1086;&#1083;&#1085;&#1103;&#1090;&#1100;&#1089;&#1103; &#1072;&#1074;&#1090;&#1086;&#1084;&#1072;&#1090;&#1080;&#1095;&#1077;&#1089;&#1082;&#1080;&#1081; commit
+            T res =executor.execute(conn);
+            conn.commit();
+            return res;
+            }
+            catch (SQLException e){
+                conn.rollback();
+                throw e;
+            }
+
+        } catch (SQLException e) {
+            throw new StorageException(e);
+        }
+    }
     public <T> T transactionExecute(ABlockOfCode<T> aBlockOfCode, String sqlQuery) {
         try (Connection conn = connectionFactory.getConnection();
              PreparedStatement ps = conn.prepareStatement(sqlQuery)
@@ -23,5 +40,4 @@ public <T> T transactionExecute(ABlockOfCode<T> aBlockOfCode, String sqlQuery) {
             throw new StorageException(e);
         }
     }
-
 }
diff --git a/src/com/urise/webapp/sql/SqlTransaction.java b/src/com/urise/webapp/sql/SqlTransaction.java
@@ -0,0 +1,8 @@
+package com.urise.webapp.sql;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+
+public interface SqlTransaction <T>{
+    T execute (Connection conn) throws SQLException;
+}
diff --git a/src/com/urise/webapp/storage/SqlStorage.java b/src/com/urise/webapp/storage/SqlStorage.java
@@ -1,13 +1,12 @@
 package com.urise.webapp.storage;
 
-import com.urise.webapp.exception.ExistStorageException;
 import com.urise.webapp.exception.NotExistStorageException;
 import com.urise.webapp.model.ContactType;
 import com.urise.webapp.model.Resume;
 import com.urise.webapp.sql.SqlHelper;
 
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
-import java.sql.SQLException;
 import java.util.ArrayList;
 import java.util.List;
 import java.util.Map;
@@ -26,57 +25,52 @@ public void clear() {
 
     @Override
     public void save(Resume resume) {
-        sqlHelper.transactionExecute(ps -> {
-            ps.setString(1, resume.getUuid());
-            ps.setString(2, resume.getFullName());
-            try {
-                ps.execute();
-            } catch (SQLException e) {
-                if(e.getSQLState().equals("23505"))
-                    throw new ExistStorageException(resume.getUuid());
-            }
-            return null;
-        }, "INSERT INTO contact (resume_uuid, type, value) values (?,?,?)");
-        for (Map.Entry<ContactType,String> entry:resume.getContacts().entrySet()) {
-            sqlHelper.transactionExecute(ps -> {
-                ps.setString(1, resume.getUuid());
-                ps.setString(2, entry.getKey().name());
-                ps.setString(3, entry.getValue());
-                try {
-                    ps.execute();
-                } catch (SQLException e) {
-                    if (e.getSQLState().equals("23505"))
-                        throw new ExistStorageException(resume.getUuid());
-                }
+        sqlHelper.transactionalExecute(conn -> {
+                    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO RESUME (uuid, full_name) values (?,?)")) {
+                        ps.setString(1, resume.getUuid());
+                        ps.setString(2, resume.getFullName());
+                        ps.execute();
+                    }
 
-                return null;
-            }, "INSERT INTO RESUME (uuid, full_name) values (?,?)");
-        }
+                    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO contact (resume_uuid, type, value) values (?,?,?)")) {
+                        for (Map.Entry<ContactType, String> entry : resume.getContacts().entrySet()) {
+                            ps.setString(1, resume.getUuid());
+                            ps.setString(2, entry.getKey().name());
+                            ps.setString(3, entry.getValue());
+                            ps.addBatch();
+                        }
+                        ps.executeBatch();
+                    }
+                    return null;
+                }
+        );
     }
 
+
     @Override
     public Resume get(String uuid) {
         return sqlHelper.transactionExecute(ps -> {
-            ps.setString(1, uuid);
-            ResultSet rs = ps.executeQuery();
-            if (!rs.next()) {
-                throw new NotExistStorageException(uuid);
-            }
-            Resume r = new Resume(uuid, rs.getString("full_name"));
-            do{
-                String value =rs.getString("value");
-                ContactType type = ContactType.valueOf(rs.getString("type"));
-                r.addContact(type,value);
-            }while (rs.next());
-            return r;
-        },
-        "SELECT * FROM RESUME r " +
-                "LEFT JOIN CONTACT c on (r.uuid=c.resume_uuid) " +
-                "WHERE r.uuid=?");
+                    ps.setString(1, uuid);
+                    ResultSet rs = ps.executeQuery();
+                    if (!rs.next()) {
+                        throw new NotExistStorageException(uuid);
+                    }
+                    Resume r = new Resume(uuid, rs.getString("full_name"));
+                    do {
+                        String value = rs.getString("value");
+                        ContactType type = ContactType.valueOf(rs.getString("type"));
+                        r.addContact(type, value);
+                    } while (rs.next());
+                    return r;
+                },
+                "SELECT * FROM RESUME r " +
+                        "LEFT JOIN CONTACT c on (r.uuid=c.resume_uuid) " +
+                        "WHERE r.uuid=?");
     }
 
     @Override
     public void delete(String uuid) {
+        /*&#1091;&#1076;&#1072;&#1083;&#1077;&#1085;&#1080;&#1077; &#1087;&#1086; &#1082;&#1072;&#1089;&#1082;&#1072;&#1076;&#1091;*/
         sqlHelper.transactionExecute(p -> {
             p.setString(1, uuid);
             if (p.executeUpdate() == 0) {
@@ -103,7 +97,9 @@ public List<Resume> getAllSorted() {
     public int size() {
         return sqlHelper.transactionExecute(ps -> {
             ResultSet rs = ps.executeQuery();
-            if (!rs.next()) {return 0;}
+            if (!rs.next()) {
+                return 0;
+            }
             return rs.getInt(1);
         }, "SELECT count (uuid) FROM RESUME");
     }
