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;
    }

    private int countItem(Connection connection) {
        int id = 0;
        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery("select * from users order by id desc limit 1");
            while (rs.next()) {
                id =  rs.getInt("id");
                id++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }

    @Override
    public void add(User user) {
        try {
            Connection connection = conn.getSOURCE().getConnection();
            int id = this.countItem(connection);
            Statement st = connection.prepareStatement("INSERT INTO users(id, name, login, email) values(?,?,?,?)");
            ((PreparedStatement) st).setInt(1,  id);
            ((PreparedStatement) st).setString(2, user.getName());
            ((PreparedStatement) st).setString(3, user.getLogin());
            ((PreparedStatement) st).setString(4, 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;
    }
}
@@ -387,17 +387,16 @@
 ect();%0A%0A
-%0A
     publ
@@ -465,450 +465,8 @@
  %7D%0A%0A
-    private int countItem(Connection connection) %7B%0A        int id = 0;%0A        try %7B%0A            Statement st = connection.createStatement();%0A            ResultSet rs = st.executeQuery(%22select * from users order by id desc limit 1%22);%0A            while (rs.next()) %7B%0A                id =  rs.getInt(%22id%22);%0A                id++;%0A            %7D%0A        %7D catch (Exception e) %7B%0A            e.printStackTrace();%0A        %7D%0A        return id;%0A    %7D%0A%0A
     
@@ -596,57 +596,8 @@
 ();%0A
-            int id = this.countItem(connection);%0A
     
@@ -666,20 +666,16 @@
 O users(
-id, 
 name, lo
@@ -702,68 +702,13 @@
 ,?,?
-,?
 )%22);%0A
-            ((PreparedStatement) st).setInt(1,  id);%0A
     
@@ -754,9 +754,9 @@
 ing(
-2
+1
 , us
@@ -821,9 +821,9 @@
 ing(
-3
+2
 , us
@@ -885,17 +885,17 @@
 tString(
-4
+3
 , user.g

task #51922#98787