D-Net

package eu.dnetlib.openaire.user.registeredService;

import eu.dnetlib.openaire.user.pojos.RegisteredService;

import eu.dnetlib.openaire.user.store.DataSourceConnector;

import eu.dnetlib.openaire.user.store.Statement;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Component;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

@Component(value = "registeredServiceSQL")

public class RegisteredServiceSQL implements RegisteredServiceDao {

    @Autowired

    DataSourceConnector dataSourceConnector;

    private final static String INSERT = "INSERT INTO registered_service (" +

            "client_id, " +

            "owner, " +

            "name, " +

            "creation_date, " +

            "registration_access_token" +

            ") VALUES (?,?,?,?,?)";

    private final static String UPDATE = "UPDATE registered_service SET " +

            "name = ? " +

            "WHERE client_id = ?";

    private final static String DELETE = "DELETE FROM registered_service WHERE id = ?";

    private final static String SELECT = "SELECT * FROM registered_service WHERE owner = ?";

    private final static String COUNT = "SELECT COUNT(*) FROM registered_service WHERE owner = ?";

    @Override

    public int insertRegisteredService(final RegisteredService registeredService)

            throws SQLException {

        return executeUpdate(INSERT, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt)

                    throws SQLException {

                stmt.setString(1, registeredService.getClientId());

                stmt.setString(2, registeredService.getOwner());

                stmt.setString(3, registeredService.getName());

                stmt.setTimestamp(4, registeredService.getDate());

                stmt.setString(5, registeredService.getRegistrationAccessToken());

            }

        });

    }

    @Override

    public boolean update(final RegisteredService registeredService) throws SQLException {

        System.out.println("UPDATE registered_service SET " +

                        "name = " + registeredService.getName() +

                        "WHERE client_id = " +  registeredService.getClientId());

        return executeUpdate(UPDATE, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt)

                    throws SQLException {

                stmt.setString(1, registeredService.getName());

                stmt.setString(2, registeredService.getClientId());

            }

        }) > 0;

    }

    @Override

    public boolean delete(final int id)

            throws SQLException {

        System.out.println("DELETE FROM registered_service WHERE id = " + id);

        return executeUpdate(DELETE, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt)

                    throws SQLException {

                stmt.setInt(1, id);

            }

        }) > 0;

    }

    @Override

    public RegisteredService fetchRegisteredServiceById(final int id) throws SQLException {

        System.out.println("SELECT * FROM registered_service WHERE id = " +id);

        List<RegisteredService> registeredServices = executeQuery("SELECT * FROM registered_service WHERE id = ? ", new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt) throws SQLException {

                stmt.setInt(1, id);

            }

        });

        if (registeredServices.isEmpty()) {

            return null;

        }

        return registeredServices.get(0);

    }

    @Override

    public List<RegisteredService> fetchAllRegisteredServicesByOwner(final String owner) throws SQLException {

        List<RegisteredService> registeredServices = executeQuery(SELECT, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt) throws SQLException {

                stmt.setString(1, owner);

            }

        });

        return registeredServices;

    }

    protected List<RegisteredService> executeQuery(String sql, Statement.Initializer init)

            throws SQLException {

        Connection connection = dataSourceConnector.getDatasource().getConnection();

        try {

            PreparedStatement stmt = connection.prepareStatement(sql);

            init.prepare(stmt);

            ResultSet set = stmt.executeQuery();

            try {

                List<RegisteredService> results = new ArrayList<>();

                while (set.next())

                    results.add(fromResultSet(set));

                return results;

            } finally {

                set.close();

            }

        } finally {

            connection.close();

        }

    }

    protected List<RegisteredService> executeQuery(String sql)

            throws SQLException {

        return executeQuery(sql, Statement.emptyInitializer());

    }

    protected RegisteredService fromResultSet(ResultSet set) throws SQLException {

        RegisteredService registeredService = new RegisteredService();

        registeredService.setId(set.getString("id"));

        registeredService.setClientId(set.getString("client_id"));

        registeredService.setOwner(set.getString("owner"));

        registeredService.setName(set.getString("name"));

        registeredService.setDate(set.getTimestamp("creation_date"));

        registeredService.setRegistrationAccessToken(set.getString("registration_access_token"));

        return registeredService;

    }

    protected int executeUpdate(String sql, Statement.Initializer init)

            throws SQLException {

        Connection connection = dataSourceConnector.getDatasource().getConnection();

        try {

            PreparedStatement stmt = connection.prepareStatement(sql);

            init.prepare(stmt);

            return stmt.executeUpdate();

        } finally {

            connection.close();

        }

    }

    public long countRegisteredServices(String owner) throws SQLException {

        long numberOfRegisteredServices = executeCount(COUNT, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt) throws SQLException {

                stmt.setString(1, owner);

            }

        });

        return numberOfRegisteredServices;

    }

    public long executeCount(String sql, Statement.Initializer init) throws SQLException {

        Connection connection = dataSourceConnector.getDatasource().getConnection();

        try {

            PreparedStatement stmt = connection.prepareStatement(sql);

            init.prepare(stmt);

            ResultSet set = stmt.executeQuery();

            try {

                if (set.next())

                    return set.getLong(1);

                throw new IllegalArgumentException(stmt.toString());

            } finally {

                set.close();

            }

        } finally {

            connection.close();

        }

    }

}