package ru.job4j.servlets.userstore;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;

public class DbStore implements Store {
    private static DbStore instance = new DbStore();
    private static Connect conn = new Connect();

    public static DbStore getInstance() {
        return instance;
    }

    @Override
    public void add(User user) {
        try {
            Connection connection = conn.getSOURCE().getConnection();
            Statement st = connection.prepareStatement("INSERT INTO users(name, login, email) values(?,?,?)");
            ((PreparedStatement) st).setString(1, user.getName());
            ((PreparedStatement) st).setString(2, user.getLogin());
            ((PreparedStatement) st).setString(3, user.getEmail());
            ((PreparedStatement) st).executeQuery();
            st.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void update(int id, User user) {
        try {
            Connection connection = conn.getSOURCE().getConnection();
            Statement st = connection.prepareStatement("UPDATE users SET name='"+user.getName()+"', login='"+user.getLogin()+"', email='"+user.getEmail()+"' WHERE id='"+id+"'");
            ((PreparedStatement) st).executeQuery();
            st.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void delete(int key) {
        try {
            Connection connection = conn.getSOURCE().getConnection();
            Statement st = connection.prepareStatement("delete from users where id = '"+key+"'");
            ((PreparedStatement) st).executeQuery();
            st.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<User> findAll() {
        List<User> userStore = new CopyOnWriteArrayList<User>();
        try {
            Connection connection = conn.getSOURCE().getConnection();
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery("select * from users");
            while (rs.next()) {
                User user = new User(rs.getInt("id"), rs.getString("name"), rs.getString("login"), rs.getString("email"), new Date());
                userStore.add(user);
            }
            st.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return userStore;
    }

    @Override
    public User findById(int key) {
        User user = new User();
        try {
            Connection connection = conn.getSOURCE().getConnection();
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery("select * from users where id = " + key);
            rs.next();
            user.setName(rs.getString("name"));
            user.setLogin(rs.getString("login"));
            user.setEmail(rs.getString("email"));
            st.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return user;
    }
}
@@ -390,75 +390,449 @@
 ();%0A
-%0A    public static DbStore getInstance() %7B%0A        return instance;
+    private static Connection connection = null;%0A    private static Statement st = null;%0A%0A    public static DbStore getInstance() %7B%0A        return instance;%0A    %7D%0A%0A    private void closeQuietly() %7B%0A        try %7B%0A            this.st.close();%0A        %7D catch (Exception e) %7B%0A            e.printStackTrace();%0A        %7D%0A        try %7B%0A            this.connection.close();%0A        %7D catch (Exception e) %7B%0A            e.printStackTrace();%0A        %7D
 %0A   
@@ -904,35 +904,24 @@
             
-Connection 
 connection =
@@ -963,34 +963,24 @@
             
-Statement 
 st = connect
@@ -1316,64 +1316,8 @@
 ();%0A
-            st.close();%0A            connection.close();%0A
     
@@ -1369,32 +1369,85 @@
 ntStackTrace();%0A
+        %7D finally %7B%0A            this.closeQuietly();%0A
         %7D%0A    %7D%0A
@@ -1527,35 +1527,24 @@
             
-Connection 
 connection =
@@ -1586,34 +1586,24 @@
             
-Statement 
 st = connect
@@ -1803,64 +1803,8 @@
 ();%0A
-            st.close();%0A            connection.close();%0A
     
@@ -1856,32 +1856,85 @@
 ntStackTrace();%0A
+        %7D finally %7B%0A            this.closeQuietly();%0A
         %7D%0A    %7D%0A
@@ -2004,35 +2004,24 @@
             
-Connection 
 connection =
@@ -2063,34 +2063,24 @@
             
-Statement 
 st = connect
@@ -2200,64 +2200,8 @@
 ();%0A
-            st.close();%0A            connection.close();%0A
     
@@ -2253,32 +2253,85 @@
 ntStackTrace();%0A
+        %7D finally %7B%0A            this.closeQuietly();%0A
         %7D%0A    %7D%0A
@@ -2466,35 +2466,24 @@
             
-Connection 
 connection =
@@ -2525,34 +2525,24 @@
             
-Statement 
 st = connect
@@ -2853,64 +2853,8 @@
   %7D%0A
-            st.close();%0A            connection.close();%0A
     
@@ -2906,32 +2906,85 @@
 ntStackTrace();%0A
+        %7D finally %7B%0A            this.closeQuietly();%0A
         %7D%0A      
@@ -3118,27 +3118,16 @@
         
-Connection 
 connecti
@@ -3177,26 +3177,16 @@
         
-Statement 
 st = con
@@ -3480,64 +3480,8 @@
     
-    st.close();%0A            connection.close();%0A        
 %7D ca
@@ -3521,32 +3521,85 @@
 .printStackTrace
+();%0A        %7D finally %7B%0A            this.closeQuietly
 ();%0A        %7D%0A  

task #51922 #98787